Excel全攻略 | SUBTOTAL函数:筛选后序号自动更新,这才是真自动!
- 2026-06-02 15:27:22
Excel SUBTOTAL函数：筛选后自动重新编号的万能统计神器
筛选数据后，序号变成1、5、9不连续？求和把隐藏行也算进去了？SUBTOTAL专治这两个问题！
一、SUBTOTAL函数基础
语法：=SUBTOTAL(功能代码, 引用区域1, [引用区域2]...)
功能代码表（核心代码）：
核心规律：
代码1-11：包含手动隐藏的行
代码101-111：排除手动隐藏的行
筛选状态：两者都会排除筛选隐藏的行
二、3大核心用法
用法1：筛选后序号自动重新编号
痛点：筛选后，用ROW函数得到的序号是1、5、9...不连续
公式：=SUBTOTAL(3, B$2:B2)
原理：
代码3（COUNTA）统计非空单元格个数
B$2:B2只统计到当前行
筛选后，SUBTOTAL只统计可见行，序号自动从1开始
操作：
A2输入公式：
=SUBTOTAL(3, B$2:B2)向下填充
任意筛选，序号自动重新编号
用法2：筛选后求和/求平均
痛点：SUM会把隐藏行也算进去
普通公式：=SUM(C:C) → 包含隐藏行SUBTOTAL公式：=SUBTOTAL(109, C:C) → 只统计可见行
常用统计：
可见行求和：
=SUBTOTAL(109, C:C)可见行计数：
=SUBTOTAL(103, A:A)可见行平均值：
=SUBTOTAL(101, C:C)
用法3：创建可折叠的汇总行
场景：对数据分组后，手动隐藏明细行，只显示汇总
操作：
在汇总行使用
=SUBTOTAL(109, C2:C100)手动隐藏明细行时，汇总自动更新
三、实战案例
案例1：销售筛选报表
需求：筛选不同地区后，序号从1开始，合计只算当前筛选结果
A列序号：=SUBTOTAL(3, B$2:B2)底部合计：=SUBTOTAL(109, E:E)底部计数：=SUBTOTAL(103, A:A)
案例2：忽略手动隐藏行的统计
区别示例：
=SUBTOTAL(9, C:C)：筛选隐藏不算，手动隐藏算=SUBTOTAL(109, C:C)：筛选隐藏不算，手动隐藏也不算
四、SUBTOTAL vs AGGREGATE
SUBTOTAL局限性：
不能忽略错误值（有#N/A会报错）
不支持更多统计方式
AGGREGATE更强大（但语法复杂）：=AGGREGATE(9, 6, C:C) → 求和且忽略错误值
五、常见问题
问题1：为什么我的SUBTOTAL把隐藏行算进去了？原因：用了代码1-11，应改用101-111
问题2：筛选后公式结果不更新？检查：计算选项是否为“自动”
问题3：序号公式下拉后全是1？原因：B$2:B2没有正确锁定起始行
六、总结要点
=SUBTOTAL(3, B$2:B2) | ||
=SUBTOTAL(109, C:C) | ||
=SUBTOTAL(103, A:A) | ||
=SUBTOTAL(101, D:D) |
记住：SUBTOTAL(9,区域)和SUM的区别，就是SUBTOTAL只看得到、SUM什么都看得到。
