SUBTOTAL函数使用方法详解

SUBTOTAL函数是Excel中一个功能强大的工具，主要用于筛选和隐藏后的数据统计。这个函数不仅能够帮助用户快速得到所需的数据汇总，还能在处理包含隐藏数据的表格时表现出其独特的灵活性。以下是SUBTOTAL函数的详细使用方法及实例解析，旨在帮助那些对SUBTOTAL函数使用感兴趣的读者。

SUBTOTAL函数怎么用？

SUBTOTAL函数在Excel中的语法结构为：SUBTOTAL(function_num, ref1, [ref2], ...)，其中function_num用于指定分类汇总时要使用的函数类型，如求和、计数、求平均值等，而ref1, [ref2], ...则是需要进行分类汇总计算的单元格区域或命名区域。值得注意的是，function_num的取值范围有两个：1至11和101至111。这两个范围的区别在于，当function_num取1至11时，SUBTOTAL函数会对筛选后的数据进行统计，包括手动隐藏的数据；而当function_num取101至111时，SUBTOTAL函数仅对可见的数据进行统计，忽略筛选掉的数据和手动隐藏的数据。

1. SUBTOTAL函数的基本功能

SUBTOTAL函数支持多种函数类型，具体对应关系如下：

1 (101): AVERAGE，求平均值

2 (102): COUNT，计数非空单元格

3 (103): COUNTA，计数非空单元格（包括字母）

4 (104): MAX，求最大值

5 (105): MIN，求最小值

6 (106): PRODUCT，求乘积

7 (107): STDEV，基于样本的标准偏差值（忽略逻辑值和文本）

8 (108): STDEVP，基于总体的标准偏差值

9 (109): SUM，求和

10 (110): VAR，基于样本的方差（忽略逻辑值和文本）

11 (111): VARP，基于总体的方差

2. 使用SUBTOTAL函数进行计数和求和

假设A1:C11区域存储了各业务员的销售额数据，我们希望通过SUBTOTAL函数来进行计数和求和操作。

计数实例：

在F1单元格输入公式：=SUBTOTAL(3, $A$2:$A$11)，用于计算A列中非空单元格的数量。当未筛选数据时，SUBTOTAL函数返回值为“10”。当筛选出“销售1部”的数据时，SUBTOTAL函数返回值为“4”，仅计算筛选后非空单元格的数量。

求和实例：

在F1单元格输入公式：=SUBTOTAL(9, $C$2:$C$11)，用于计算C列中销售额的总和。当未筛选数据时，SUBTOTAL函数返回值为“550”。当筛选出“销售1部”的数据时，SUBTOTAL函数返回值为“220”，仅计算筛选后数据的总和。

3. 隐藏行后的计数和求和

在某些情况下，数据表中的某些行可能被隐藏。SUBTOTAL函数在处理隐藏数据时表现出不同的行为，取决于function_num的取值。

隐藏后计数实例：

在F1单元格输入公式：=SUBTOTAL(103, $A$2:$A$11)，此时所有数据均未隐藏，SUBTOTAL函数返回值为“10”。当隐藏第2、3行数据后，SUBTOTAL函数返回值为“8”，说明它忽略了隐藏的行。

隐藏后求和实例：

在F1单元格输入公式：=SUBTOTAL(109, $C$2:$C$11)，此时所有数据均未隐藏，SUBTOTAL函数返回值为“550”。当隐藏第2、3行数据后，SUBTOTAL函数返回值为“520”，忽略了隐藏行中的销售额。

4. 生成筛选后仍连续的序号

在数据表中，有时需要为筛选后的数据生成连续的序号。通过SUBTOTAL函数可以实现这一点。

在A2单元格输入公式：=SUBTOTAL(103, $B$2:B2)*1，然后拖动填充柄向下复制公式，在A2:A11生成连续序号。当筛选出“销售1部”的数据时，序号仍保持连续，这是因为SUBTOTAL函数在生成序号时忽略了隐藏或筛选掉的行。

5. 对筛选后的数据进行条件计数

通过SUBTOTAL函数与其他函数的组合，可以实现更复杂的统计需求，如对筛选后的数据进行条件计数。

假设在B2:B11区域存储了部门信息，C2:C11区域存储了各业务员的销售额。我们希望计算筛选出销售额大于等于50的各部门