资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

Excel筛选显示总数的多种方法

创作时间:

作者:

@小白创作中心

Excel筛选显示总数的多种方法

引用

来源

https://docs.pingcode.com/baike/4316801

在Excel中筛选显示总数的方法包括使用SUBTOTAL函数、添加辅助列、借助数据透视表等。其中，最常用的是使用SUBTOTAL函数，它可以动态地计算筛选后的数据总数。接下来，我将详细描述如何使用SUBTOTAL函数来显示筛选后的总数。

一、SUBTOTAL函数的使用

SUBTOTAL函数是一个强大的工具，它可以根据筛选条件动态地计算数据总和、平均值、计数等。SUBTOTAL函数的第一个参数指定了要执行的操作类型，例如求和、计数、平均值等。常用的代码包括9（求和）、1（平均值）、2（计数）等。

1、基本用法

在Excel中，SUBTOTAL函数的基本语法为：

  
=SUBTOTAL(函数代码, 范围)

例如，如果我们想要计算筛选后某列数据的总和，可以使用以下公式：

  
=SUBTOTAL(9, B2:B100)

这里的“9”表示求和，B2:B100是需要计算的范围。

2、动态更新

SUBTOTAL函数的一个重要特点是它会根据筛选条件动态更新。例如，如果你在数据筛选后，使用SUBTOTAL(9, B2:B100)求和，结果只会包含筛选后的可见单元格的数据。这使得它非常适合用来显示筛选后的总数。

二、添加辅助列

在某些情况下，你可能需要通过添加辅助列来进一步细化筛选和计算总数的方法。

1、创建辅助列

首先，在数据表中插入一列作为辅助列。例如，在D列中输入公式来标记符合筛选条件的行：

  
=IF(A2="条件", 1, 0)

2、使用SUMIF函数计算总数

接下来，使用SUMIF函数来计算辅助列中标记为1的行的总和：

  
=SUMIF(D2:D100, 1, B2:B100)

这样，你可以更灵活地自定义筛选条件和计算总数的方法。

三、数据透视表

数据透视表是Excel中另一个强大的工具，它可以帮助你快速汇总和分析数据，包括计算筛选后的总数。

1、创建数据透视表

首先，选择数据范围，然后点击“插入”选项卡，选择“数据透视表”。

2、设置筛选条件和总和

在数据透视表中，将需要汇总的字段拖动到“数值”区域，并将筛选条件字段拖动到“筛选”区域。然后，你可以通过调整筛选条件来动态查看筛选后的总数。

四、SUMPRODUCT函数

SUMPRODUCT函数是一个强大的数组函数，它可以在多条件下计算总数。

1、使用SUMPRODUCT函数

假设我们有两列数据A和B，并且需要计算符合条件的数据总和，可以使用以下公式：

  
=SUMPRODUCT((A2:A100="条件")*(B2:B100))

这个公式会计算A列中符合条件的行，并将对应B列中的值相加。

五、总结

使用SUBTOTAL函数、添加辅助列、数据透视表、SUMPRODUCT函数是Excel中实现筛选显示总数的主要方法。每种方法都有其独特的优势和适用场景。通过灵活应用这些方法，你可以更高效地管理和分析数据。

详细描述SUBTOTAL函数的动态更新

SUBTOTAL函数的动态更新是其最显著的特点之一。当你使用Excel的筛选功能时，SUBTOTAL函数会自动忽略被隐藏的行，只对可见行进行计算。这意味着你无需手动调整公式或数据范围，SUBTOTAL函数会自动适应你的筛选条件，实时显示筛选后的总数。例如，当你在一个大型数据集中通过筛选功能找到特定条件的数据时，SUBTOTAL函数会立即更新结果，显示符合条件的数据总数。这种动态更新大大提高了数据分析的效率和准确性，使得SUBTOTAL函数成为Excel中不可或缺的工具。

接下来，我们将深入探讨这些方法的具体操作步骤和应用场景。

一、SUBTOTAL函数的高级用法

1、不同操作类型

SUBTOTAL函数不仅可以求和，还可以进行平均值、计数、最大值、最小值等操作。以下是一些常用的操作代码：

1：平均值
2：计数
3：计数值（非空单元格）
4：最大值
5：最小值
9：求和

例如，如果你想要计算筛选后数据的平均值，可以使用以下公式：

  
=SUBTOTAL(1, B2:B100)

2、忽略隐藏行和手动隐藏行

SUBTOTAL函数还有一个重要特性：它可以选择是否忽略手动隐藏的行。具体来说，使用1-11作为函数代码时，SUBTOTAL函数会忽略通过筛选功能隐藏的行，但不忽略手动隐藏的行；而使用101-111作为函数代码时，它会忽略所有隐藏的行，包括手动隐藏的行。

例如：

  
=SUBTOTAL(109, B2:B100)

这里的“109”表示求和且忽略所有隐藏的行。

二、添加辅助列的应用场景

添加辅助列的方法适用于需要进行复杂条件筛选和计算的场景。通过在辅助列中标记符合条件的行，可以更灵活地控制数据的筛选和总数计算。

1、复杂条件筛选

在实际工作中，筛选条件往往不止一个。例如，我们需要筛选出销售额大于1000且产品类别为“电子”的数据，并计算总销售额。

首先，在D列中输入公式标记符合条件的行：

  
=IF(AND(B2>1000, C2="电子"), 1, 0)

然后，使用SUMIF函数计算总销售额：

  
=SUMIF(D2:D100, 1, B2:B100)

2、多条件统计

辅助列还可以用于多条件统计。例如，统计符合多个条件的行数，可以使用以下公式：

  
=COUNTIFS(A2:A100, "条件1", B2:B100, "条件2")

三、数据透视表的深入使用

数据透视表是Excel中非常强大的数据分析工具。通过数据透视表，你可以轻松地进行数据汇总、筛选和动态分析。

1、创建数据透视表

选择数据范围，点击“插入”选项卡，选择“数据透视表”。在弹出的窗口中，选择数据透视表的放置位置（新工作表或现有工作表）。

2、设置筛选条件和总和

在数据透视表中，将需要汇总的字段拖动到“数值”区域。例如，将“销售额”字段拖动到“数值”区域，Excel会自动计算总销售额。

将筛选条件字段拖动到“筛选”区域。例如，将“产品类别”字段拖动到“筛选”区域，然后可以通过筛选功能选择特定类别，数据透视表会动态更新结果。

四、SUMPRODUCT函数的高级应用

SUMPRODUCT函数可以在多条件下进行复杂的计算。它的优势在于可以同时处理多个条件，并计算符合条件的数据总数。

1、多条件求和

例如，计算A列中符合条件的数据的总和，可以使用以下公式：

  
=SUMPRODUCT((A2:A100="条件1")*(B2:B100="条件2")*(C2:C100))

这个公式会计算A列中符合条件1，且B列中符合条件2的行，并将对应C列中的值相加。

2、加权平均值

SUMPRODUCT函数还可以用于计算加权平均值。例如，计算某列数据的加权平均值，可以使用以下公式：

  
=SUMPRODUCT(A2:A100, B2:B100) / SUM(B2:B100)

这里，A列是数据，B列是权重。

五、实际案例分析

为了更好地理解这些方法的应用，我们来看一个实际案例。假设我们有一张销售数据表，包含以下字段：日期、销售额、产品类别、销售人员。我们需要根据不同的筛选条件计算总销售额。

1、使用SUBTOTAL函数

在总销售额单元格中输入以下公式：

  
=SUBTOTAL(9, B2:B100)

然后，通过筛选功能选择特定日期范围或产品类别，SUBTOTAL函数会动态更新总销售额。

2、添加辅助列

在E列中输入公式标记符合特定条件的行：

  
=IF(AND(C2="电子", D2="张三"), 1, 0)

然后，在总销售额单元格中输入以下公式：

  
=SUMIF(E2:E100, 1, B2:B100)

3、数据透视表

创建数据透视表，将“销售额”字段拖动到“数值”区域，将“产品类别”和“销售人员”字段拖动到“筛选”区域。通过筛选功能选择特定类别和销售人员，数据透视表会动态更新总销售额。

4、SUMPRODUCT函数

在总销售额单元格中输入以下公式：

  
=SUMPRODUCT((C2:C100="电子")*(D2:D100="张三")*(B2:B100))

这个公式会计算产品类别为“电子”且销售人员为“张三”的总销售额。

六、总结

通过使用SUBTOTAL函数、添加辅助列、数据透视表、SUMPRODUCT函数，你可以在Excel中灵活地实现筛选显示总数的功能。每种方法都有其独特的优势和适用场景。SUBTOTAL函数适用于动态更新筛选后的总数，辅助列方法适用于复杂条件筛选和计算，数据透视表适用于快速汇总和分析数据，SUMPRODUCT函数适用于多条件下的复杂计算。通过灵活应用这些方法，你可以更高效地管理和分析数据，提高工作效率和数据准确性。

希望这些详细的操作步骤和实际案例分析能帮助你更好地理解和应用这些方法。如果有任何问题或需要进一步的帮助，请随时联系。