Excel 怎么用 SUBTOTAL 统计筛选后的可见行
筛选之后 SUM 还是把隐藏行一起加,数字对不上。这篇讲 SUBTOTAL 编号怎么选、9 和 109 差在哪,以及怎么和状态栏核对。
用筛选看某一类客户的订单,底部状态栏的合计是对的,可自己写的 SUM 却把所有行都加了进去,报表数字和眼睛看到的不一致。SUBTOTAL 就是为「只统计当前看得见的行」准备的函数,筛选条件一变,结果自动重算。
入口在哪
- 函数入口:任意空白单元格输入
=SUBTOTAL(,第一个参数选统计方式编号,第二个起选区域。 - 图形入口:选中数据区域下方一格,用「数据 > 分类汇总」会自动插入带 SUBTOTAL 的小计行。
- 核对入口:窗口底部状态栏右键,勾选「求和」「计数」,它统计的同样是筛选后的可见行,可用来对账。
具体步骤
- 先确认表格是一行表头、一行一条记录,中间没有合并单元格和空行,否则区域边界很难选准。
- 在数据下方的汇总格写
=SUBTOTAL(109,D2:D500)。109 表示求和,并且连手动隐藏的行也排除。 - 计数用 103(非空单元格个数),只数数字用 102,求平均用 101,逻辑相同。
- 横向下拉填充到其他列,编号保持不变,区域会跟着平移。
- 打开筛选切换条件,汇总格会随可见行自动重算,不需要手动刷新。
几个坑
- 9 和 109 的差别被误解:9 只排除「被筛选掉」的行,手动右键隐藏的行照样算;101 到 109 才连手动隐藏一起排除。这是最常见的算错原因。
- 嵌套小计会重复算:SUBTOTAL 会忽略区域里嵌套的 SUBTOTAL,所以分段小计再汇总不会翻倍;换成 SUM 就会把每一段都加两遍。
- 区域盖不住新增行:筛选后新加的行落在 D500 之外就不统计。把区域写成整列,或把数据转成表格用结构化引用,可以避免。
- 透视表里不能用它:数据透视表有自己的汇总方式,SUBTOTAL 对透视表区域不起作用,别硬套。
- 空行会截断分类汇总:数据中间有整行空白时,自动生成的分类汇总段会断开,需要先清掉空行。
怎么验证
- 筛选某个条件后,对比状态栏求和与 SUBTOTAL 的结果,两者应当完全一致。
- 清空筛选,数字应变大;用 SUM 减 SUBTOTAL,差值应刚好等于被筛掉那些行的合计。
- 手动隐藏两行,观察 9 与 109 的结果差是否等于这两行的值,确认自己用的到底是哪个编号。
常见问题
SUBTOTAL 和 AGGREGATE 有什么区别
AGGREGATE 函数编号更多,还能同时选择忽略隐藏行、错误值和嵌套小计,区域里有 #DIV/0! 也不会把错误带进结果。SUBTOTAL 只管隐藏和筛选,遇到错误值会原样传播。
筛选后数字为什么没变
先看公式写的是不是 SUM。其次确认筛选作用在同一区域上——筛选别的列、或数据在表格之外,都不影响 SUM 的累加。筛选只是隐藏行,值还在。
能不能只统计满足条件的可见行
可以,但写法要用 SUMPRODUCT 配合 SUBTOTAL 的可见性判断,公式长且容易错。行数多时更稳的做法是把数据做成透视表,用行标签筛选,汇总自动只算可见项。
一句话总结:统计筛选后的结果就用 SUBTOTAL,编号加 100 才连手动隐藏的行一起排除。