Excel全攻略 | SUBTOTAL函数:筛选后序号自动更新,这才是真自动!

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开始

操作：

1. A2输入公式：=SUBTOTAL(3, B$2:B2)

2. 向下填充

3. 任意筛选，序号自动重新编号

用法2：筛选后求和/求平均

痛点：SUM会把隐藏行也算进去

普通公式：=SUM(C:C) → 包含隐藏行SUBTOTAL公式：=SUBTOTAL(109, C:C) → 只统计可见行

常用统计：

• 可见行求和：=SUBTOTAL(109, C:C)

• 可见行计数：=SUBTOTAL(103, A:A)

• 可见行平均值：=SUBTOTAL(101, C:C)

用法3：创建可折叠的汇总行

场景：对数据分组后，手动隐藏明细行，只显示汇总

操作：

1. 在汇总行使用=SUBTOTAL(109, C2:C100)

2. 手动隐藏明细行时，汇总自动更新

三、实战案例

案例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(9,区域)和SUM的区别，就是SUBTOTAL只看得到、SUM什么都看得到。