EXCEL|以一敌十的 “万能” 函数:SUBTOTAL 函数

工作中不仅会有对普通数据处理、统计的需求，还会有对动态数据处理、统计的需求，如在报表筛选状态下仅对筛选出来的目标数据进行统计。

由于当筛选条件每次变动后筛选出来的数据行是不同的，所以大部分的 Excel 函数无法排除隐藏行，仅对筛选结果进行统计，下面结合一个案例具体说明。

可见此公式排除了隐藏行，仅对按条件筛选出来的目标数据求和。

SUBTOTAL 函数是 Excel 中非常经典的分类汇总函数，用于按照指定的功能参数对数据进行统计，能够自动无视被筛选或隐藏掉的行，不考虑隐藏列的影响。

SUBTOTAL 函数的语法结构如下：

SUBTOTAL (功能参数，统计区域)

当用户根据统计需求指定功能参数后，该函数则按照第一参数指定的类型对第二参数的统计区域的数据进行统计。

SUBTOTAL 函数第一参数的功能参数分为 1 至 11 和 101 至 111 两类，不同的功能参数对应不同的函数功能，如下图：

两类功能参数的区别和联系如下：

①功能参数为 1 至 11 时，统计时包含手动隐藏行，排除筛选隐藏行；

②功能参数为 101 至 111 时，统计时排除手动隐藏行，排除筛选隐藏行。

可见，在 Excel 中隐藏行的方式不同，可以通过字段筛选隐藏行，也可以通过手动隐藏行。无论使用哪一类功能参数，SUBTOTAL 函数始终排除筛选隐藏行进行统计，两类功能参数的区别在于是否排除手动隐藏行进行统计。

对于上表中的需求，由于要求对目标数据求和，所以使用功能参数 9 或 109，又因为数据中采用筛选隐藏行的方式，所以使用功能参数 9 或者 109 都可以得到正确结果，如下图：

当我们对数据中的行采用手动隐藏的方式隐藏行时，两种公式的结果就有差异了，下面具体操作对比一下。

首先选中 6:10 行，然后单击鼠标右键，选择 “隐藏”，

手动隐藏 6:10 行以后，在 SUBTOTAL 函数第一参数中使用功能参数 9，统计时依然包含手动隐藏行数据，结果如下：

若在 SUBTOTAL 函数第一参数中使用功能参数 109，统计时会排除手动隐藏行数据，仅对可视状态下的数据求和，结果如下图：

SUBTOTAL 函数除了可以进行求和，还可以进行上图所示另外 10 种分类汇总计算，其中最常用的是计算平均值、最大值、最小值。

如果要求排除隐藏行计算数据平均值，可以使用以下公式：

=SUBTOTAL(101,E2:E11)

如果要求排除隐藏行计算数据最大值，可以使用以下公式：

=SUBTOTAL(104,E2:E11)

如果要求排除隐藏行计算数据最小值，可以使用以下公式：

=SUBTOTAL(105,E2:E11)

综上，只要根据实际需求调整 SUBTOTAL 函数的第一参数，就可以用这个函数实现 11 种不同的统计功能，还可以排除隐藏行仅对显示出来的数据进行统计，希望大家能在工作中灵活应用。

                                以上内容来自李锐的书《跟李锐学Excel数据分析》