Excel 中 数据计算利器——SUBTOTAL 函数

SUBTOTAL 函数是 Excel 中非常有用的函数，它能够对数据列表或数据库中的数据进行多种汇总计算（如求和、平均值、最大值等），尤其是经过筛选、隐藏或分类汇总的动态数据进行汇总的必备函数。它能智能地仅对当前可见的单元格进行计算，完美解决常规函数在筛选或隐藏后计算结果不准确的问题。

一、核心功能：动态可见单元格的汇总计算

核心机制： 根据你提供的“功能代码”，SUBTOTAL 会执行相应的汇总计算（如求和、平均值、计数等）。

核心优势：自动忽略被筛选掉的行、手动隐藏的行（取决于功能代码），仅计算当前显示在屏幕上的数据行。这是它区别于 SUM、AVERAGE、COUNT 等常规函数的最大特点。

基本语法：=SUBTOTAL(function_num, ref1, [ref2], ...)

参数说明：

 function_num (必需)： 一个数字（1 到 11 或 101 到 111），指定要执行的汇总计算类型。这是关键参数！

 ref1：(必需)： 要进行汇总计算的一个或多个单元格区域引用。

[ref2], ... （可选）：多个单元格区域引用。

常用功能代码详解 (function_num)

关键区别：

 1-11： 忽略通过 Excel 筛选功能隐藏的行，但不忽略用户手动隐藏的行（右键 -> 隐藏）。

101-111： 既忽略通过筛选隐藏的行，也忽略用户手动隐藏的行。

二、典型应用场景：何时使用 SUBTOTAL

1.筛选后的动态汇总： 这是 SUBTOTAL 最常用的场景。

• 问题： 使用 SUM(A2:A100) 对区域求和，当筛选掉部分行后，SUM 的结果仍然包含被筛选掉的数据。

• 解决方案： 使用 =SUBTOTAL(9, A2:A100) 或 =SUBTOTAL(109, A2:A100)。筛选后，结果自动更新，只计算当前显示行的和。报表标题行、总计行使用 SUBTOTAL 能确保筛选后显示正确的汇总结果。

2.创建分类汇总：

• Excel 的“数据”选项卡 -> “分级显示”组 -> “分类汇总”功能在自动插入汇总行时，默认使用的就是 SUBTOTAL 函数。它能确保在折叠/展开不同级别分类时，以及计算总计行时，只汇总当前可见的数据，避免重复计算内层汇总值。

  

3.需要忽略手动隐藏行或筛选行的汇总：

• 当你手动隐藏了某些行（可能是临时不需要看的数据），但仍希望汇总计算只基于可见数据时，使用 101-111 系列代码（如 109 求和）。

• 当你应用了筛选，并且希望汇总只反映筛选结果时，使用 1-11 或 101-111 均可（它们都忽略筛选行），根据是否需要忽略手动隐藏行选择具体代码。

4.避免重复计算：

• 在包含其他 SUBTOTAL 公式的区域上再进行大范围汇总时，SUBTOTAL 函数会自动忽略区域内嵌套的其他 SUBTOTAL 计算结果，只计算基础数据行。这避免了在计算“总计”时把小计也重复加进去的问题。

三、重要注意事项：避免踩坑

1.function_num 的选择至关重要：

• 仔细选择 1-11 还是 101-111，这决定了是否忽略手动隐藏的行。

• 务必记住常用功能的代码（特别是 9/109 求和、1/101 平均值、2/102 计数、3/103 计数非空），或者通过输入 =SUBTOTAL( 后查看 Excel 弹出的提示列表来选择。

2.对隐藏行的处理取决于隐藏方式：

• SUBTOTAL 总是忽略通过 Excel 筛选功能隐藏的行（无论使用 1-11 还是 101-111）。

  

• 对于手动隐藏的行：

• 使用 1-11：不忽略，会包含在计算中。

• 使用 101-111：忽略，不包含在计算中。

  

3.忽略嵌套的 SUBTOTAL：

• 当 SUBTOTAL 函数的计算区域 (ref1, ref2...) 中包含其他 SUBTOTAL 公式的结果单元格时，它会自动跳过这些单元格，只计算区域中的原始数据或非 SUBTOTAL 公式的结果。这是实现多级汇总（小计、总计）不出错的关键特性。

  

4.不适用于行方向或三维引用：

• SUBTOTAL 设计用于处理垂直方向的数据列表（行）。它对列方向隐藏（手动隐藏列）无效。

• 它不能直接处理三维引用（如 Sheet1:Sheet3!A1）。

5.引用中包含错误值：