最新

Excel 怎么用 SUBTOTAL 统计筛选后的可见行

筛选之后 SUM 还是把隐藏行一起加,数字对不上。这篇讲 SUBTOTAL 编号怎么选、9 和 109 差在哪,以及怎么和状态栏核对。

用筛选看某一类客户的订单,底部状态栏的合计是对的,可自己写的 SUM 却把所有行都加了进去,报表数字和眼睛看到的不一致。SUBTOTAL 就是为「只统计当前看得见的行」准备的函数,筛选条件一变,结果自动重算。

入口在哪

  • 函数入口:任意空白单元格输入 =SUBTOTAL(,第一个参数选统计方式编号,第二个起选区域。
  • 图形入口:选中数据区域下方一格,用「数据 > 分类汇总」会自动插入带 SUBTOTAL 的小计行。
  • 核对入口:窗口底部状态栏右键,勾选「求和」「计数」,它统计的同样是筛选后的可见行,可用来对账。

具体步骤

  1. 先确认表格是一行表头、一行一条记录,中间没有合并单元格和空行,否则区域边界很难选准。
  2. 在数据下方的汇总格写 =SUBTOTAL(109,D2:D500)。109 表示求和,并且连手动隐藏的行也排除。
  3. 计数用 103(非空单元格个数),只数数字用 102,求平均用 101,逻辑相同。
  4. 横向下拉填充到其他列,编号保持不变,区域会跟着平移。
  5. 打开筛选切换条件,汇总格会随可见行自动重算,不需要手动刷新。

几个坑

  • 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 才连手动隐藏的行一起排除。