EP07. “SUBTOTAL Function SUBTOTAL 函数”
🔒 登录后可标记已读- 用 SUM 对一份已经筛选或手动隐藏了部分行的数据求和，SUM 其实还是把隐藏的行也算进去了——这是很多人不知道的坑
- SUBTOTAL 函数专门解决这个问题，可以选择「只计算看得见的行」
- 这篇笔记教你 SUBTOTAL 的参数规则和两种自动生成小计的方式
- 前置知识是 EP01/EP02 的筛选操作和 EP06 的大纲分组
- 学完能避免筛选后统计数字算错的问题
重点内容
适用版本
桌面版通用（Excel 365 / 2021 / 2019 等）。
问题：筛选后 SUM 算错
筛选隐藏的行，SUM 函数仍然会把它们计算进去——因为 SUM 不管一行是不是被筛选隐藏，只要还在数据范围内就照算。
SUBTOTAL 解决筛选隐藏行的问题
公式：=SUBTOTAL(109,range)
- 第一个参数 109 相当于 SUM（求和），忽略「被筛选隐藏」的行
- 应用筛选后，SUBTOTAL 的结果会自动只计算目前显示出来的行，SUM 则不会更新
[截图：筛选后同一份数据，SUM 公式结果和 SUBTOTAL(109,...) 公式结果并排对比，数字不一样]
第一参数对照表（筛选隐藏 vs 手动隐藏）
📌 关键区别：数字 1~11 和 101~111 这两组功能相同（1=AVERAGE、9=SUM、2=COUNT...，101=AVERAGE、109=SUM、102=COUNT... 依此类推），但对「手动隐藏的行」处理方式不同：
- 筛选隐藏的行：不管用 1~11 还是 101~111，SUBTOTAL 都会自动忽略，两组数字在这一点上没有差别
- 手动隐藏的行（自己选中行右键 Hide）：101~111 会忽略手动隐藏的行，但 1~11 仍然会把手动隐藏的行算进去
函数速查
| 函数 | 语法 | 用途 | 例子 |
|---|---|---|---|
| SUBTOTAL | =SUBTOTAL(function_num, ref1, ...) | 汇总计算，可选择是否忽略隐藏行 | =SUBTOTAL(109,B2:B20) |
| SUM | =SUM(range) | 求和，不管行是否隐藏都会计算 | =SUM(B2:B20) |
自动生成小计的两种方式
方式一：表格总计行
- 把数据转成 Table（Insert → Table 或
Ctrl + T） - 勾选 Table 设计选项卡的 Total Row（总计行）
[截图：Table 设计选项卡勾选 Total Row 后，表格底部自动出现的总计行]
结果：Excel 会自动在表格底部加一行，并且用的正是 SUBTOTAL 函数，不用手打公式。
方式二：大纲小计功能 Data → Outline → Subtotal（详见 EP06），Excel 在插入小计行时用的也是 SUBTOTAL 函数。
学完你会
- ✅ 用 SUBTOTAL 代替 SUM，避免筛选或手动隐藏行后统计数字算错
- ✅ 分清 1~11 和 101~111 两组参数在「手动隐藏行」上的差别
- ✅ 用表格总计行或大纲小计，不用手打 SUBTOTAL 公式
常见错误
- 筛选数据后还在用 SUM 算总和，以为看到的数字已经排除了被筛选掉的行，实际上没有
- 参数用错，比如想忽略手动隐藏的行却用了 9（而不是 109），结果手动隐藏的行还是被算进去
- 以为 SUBTOTAL 会自动忽略「其他 SUBTOTAL 公式产生的小计行」——这其实是它的另一个特性（避免小计行被重复计入总计），但容易被误解成能处理所有嵌套情况
Sources
Blog / Website：