Excel函数系列：非常好用统计函数Subtotal，必须掌握

在使用Excel做数据统计时，常见有关于求和、平均值、计数和最大值、最小值等操作，我们需要使用SUM、AVERAGE、MAX和MIN等函数，但如果统计中涉及条件筛选变换统计口径或者统计除隐藏数据之外的数据时，如果只使用上述几种函数，可能会增加很多的工作量，那有没有一种更简便的方法啊？那当然有啦，SUBTOTAL便可以实现这些功能。今天我们便介绍SUBTOTAL函数。SUBTOTAL函数以一己之力实现常用的统计功能，包括刚刚提到的求和、平均值、计数和最大值、最小值等，而且可以在计算时只统计筛选结果中的行，也就是说函数计算结果会随着筛选结果的变化而变化，只统计筛选出来的可见部分，是不是感觉它很强大啊。

一、SUBTOTAL功能及语法结构。

功能：在指定的范围内根据指定的分类汇总函数进行计算。

语法结构：SUBTOTAL(function_num, ref1,[ref2],...)

1、Function_num必需；数字 1-11 或 101-111，用于指定要为分类汇总使用的函数。如果使用 1-11，将包括手动隐藏的行，如果使用 101-111，则排除手动隐藏的行；始终排除已筛选掉的单元格。

2、Ref1必需；要对其进行分类汇总计算的第一个命名区域或引用。

3、Ref2,...可选；要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。

二、对隐藏值的计算和忽略。

目的：计算某些数据的求和、平均值等。

方法：

在目标单元格输入公式=SUBTOTAL(9,D2:D9)和=SUBTOTAL(109,D3:D9)。

备注：

1、公式=SUBTOTAL(9,D2:D9)中的第一个参数为9，故包含隐藏的行；公式=SUBTOTAL(109,D3:D9)中的第一个参数为109，故不包含隐藏的行。

2、当没有隐藏行时，两个公式的计算结果相同，当有隐藏行时，公式=SUBTOTAL(109,D3:D9)的计算结果发生改变。

三、对筛选值的忽略。

目的：统计当前值的平均值。

方法：

1、在目标单元格中输入公式：

=SUBTOTAL(9,D2:D9)和=SUBTOTAL(109,D3:D9)。

2、筛选数据，我们发现计算的结果在发生变化，而且只对当前显示的数值负责。具体详见上图

备注：

通过筛选数据，我们可以发现不管是何种类型的统计，计算的结果只对当前筛选保留的数据复制。

四、永远连续的序号。

目的：隐藏行，序号保持连续。

方法：

1、选定目标单元格。

2、在目标单元格中输入公式：=SUBTOTAL(103,B$2:B3)*1。

3、下拉填充。

4、隐藏或取消隐藏行，可以发现行号都是连续的。

备注：

1、参数103所对应的函数为：Counta。统计非空单元格的个数。当参数为1XX时，忽略隐藏的行。

2、所以公式=SUBTOTAL(103,B$3:B3)统计的就是从B3开始到当前单元格累计非空单元格数。