Excel SUBTOTAL函数的高效应用与实用技巧

Excel中的SUBTOTAL函数是一个非常强大且灵活的统计函数，广泛应用于数据分析和处理。通过调整第一个参数，SUBTOTAL函数可以实现计数、求和、平均值、最大值等多种统计功能，并且可以选择是否忽略隐藏或筛选的单元格。本文将详细介绍SUBTOTAL函数的用法及一些使用技巧，帮助读者更好地掌握这一函数。

SUBTOTAL函数的基本介绍

SUBTOTAL函数的语法结构为：SUBTOTAL(function_num, ref1, [ref2], ...)，其中：

function_num（必需）：这是一个数字，用于指定要执行的汇总函数类型。这个数字可以是1到11或101到111之间的任何整数。1到11之间的数字包含手动隐藏的行，而101到111之间的数字则忽略手动隐藏的行。无论是1到11还是101到111，SUBTOTAL函数始终会忽略已筛选掉的单元格。

ref1（必需）：要进行汇总计算的第一个引用或命名区域。

[ref2]（可选）：要进行汇总计算的第二个到第254个引用或命名区域。

SUBTOTAL函数的主要功能

SUBTOTAL函数的主要功能包括：

1. 求和：使用function_num为9或109。当使用9时，包含隐藏的行；当使用109时，忽略隐藏的行。

2. 计数：使用function_num为3或103。当使用3时，包含隐藏的行；当使用103时，忽略隐藏的行。

3. 平均值：使用function_num为1或101。当使用1时，包含隐藏的行；当使用101时，忽略隐藏的行。

4. 最大值：使用function_num为4或104。

5. 最小值：使用function_num为5或105。

SUBTOTAL函数的使用示例

1. 求和

假设在D2:D22区域内有销售数据，并希望根据筛选结果动态求和。

包含隐藏行：在目标单元格中输入公式=SUBTOTAL(9, D2:D22)。

忽略隐藏行：在目标单元格中输入公式=SUBTOTAL(109, D2:D22)。

2. 计数

假设在A2:A11区域内有员工姓名，并希望统计筛选后的员工数量。

包含隐藏行：在目标单元格中输入公式=SUBTOTAL(3, $A$2:$A$11)。

忽略隐藏行：在目标单元格中输入公式=SUBTOTAL(103, $A$2:$A$11)。

3. 平均值

假设在C3:C9区域内有销售数据，并希望计算筛选后的平均值。

包含隐藏行：在目标单元格中输入公式=SUBTOTAL(1, C3:C9)。

忽略隐藏行：在目标单元格中输入公式=SUBTOTAL(101, C3:C9)。

SUBTOTAL函数的高级技巧

1. 生成连续序号

在处理数据表时，有时需要为筛选后的数据生成连续的序号。可以使用SUBTOTAL函数来实现这一点。

在目标单元格中输入公式=SUBTOTAL(103, B$2:B2)*1（或=SUBTOTAL(103, B$2:B2)-1后自行调整）。

然后拖动填充柄向下复制公式，即可生成连续的序号。

2. 筛选后的条件计数

有时需要计算筛选后符合特定条件的数据数量，可以结合SUBTOTAL函数和SUMPRODUCT函数来实现。

假设在B2:B11区域内有部门名称，C2:C11区域内有销售额数据。

希望在C14单元格中计算筛选后销售额大于等于50且属于“销售1部”的数据数量。

在C14单元格中输入公式：=SUMPRODUCT(($B$2:$B$11=B15)*(SUBTOTAL(2, OFFSET($C$2, ROW($C$2:$C$11)-2, 0))))。

3. 忽略公式计算结果

SUBTOTAL函数的一个特点是它不会统计公式计算的结果。这意味着，如果数据区域中包含由其他公式计算得到的值，SUBTOTAL函数将忽略这些公式结果。

4. 动态求和与筛选

SUBTOTAL函数可以根据筛选结果动态调整求和范围。例如，如果筛选出了某个部门的数据，SUBTOTAL函数会自动计算筛选后的数据总和。

在目标单元格中输入公式=SUBTOTAL(9, $