筛选后序号全断？同事手动重填1小时，1个SUBTOTAL 公式 10秒搞定
你辛苦编好的序号，筛选一下就稀碎。看完这篇，从此告别手动重填。

上个月帮HR小李审核工资表，她发给我的表格里序号列是这样的：
序号
姓名
部门
1
张三
技术部
2
李四
财务部
4
赵六
技术部
7
周八
财务部
我说："你这序号怎么跳着的？"
她说："我筛选了'在职'员工就变成这样了，每筛一次就要手动重填一次，烦死了。"
这个场景，99%的Excel用户都遇到过。 你以为序号就是拖一下的事，但一筛选，1,2,3,4就变成了1,3,5,7。打印出来交给领导，对方第一句话就是"你这表是不是删了数据"。

今天教你一招，用 SUBTOTAL函数 替代手动序号，筛选后自动连续，删行也不断，永远不用重填。


核心技巧
招1：SUBTOTAL(3,…) — 筛选后序号自动连续
📌 场景：HR筛选"在职"员工、财务筛选"已付款"记录后，序号全部断开，需要手动重填。

🔧 怎么做：
很简单，3步搞定——
第1步：把你的序号列清空，选中第一个数据行（如A2）。
第2步：输入这个公式：
=SUBTOTAL(3,$B$8:B8)
第3步：双击A8右下角的填充柄，公式自动铺满整列。
完事。现在你随便筛选任意列——筛选"技术部"、筛选"已付款"、筛选"本月入职"——序号列永远是 1,2,3,4……连续不断。
📐 公式拆解：
SUBTOTAL(3,区域) 的意思是：统计区域内有多少个非空单元格——和 COUNTA 做的事一样。但它有个神奇的特性：只看可见单元格，被筛选隐藏的行直接跳过。
$B$8:B8 这个区域写法是关键中的关键：
前面的$B$8用了两个$，把起点钉死在 B8
后面的B8没加$，下拉后会变成$B$8:B9、$B$8:B10……
每一行统计的范围都比上一行多一格，自然形成 1,2,3,4 的递增
兼容性：所有Excel版本均支持（从Excel 2003到365）。

🔴 踩坑提醒：
我见过不少人写成 =SUBTOTAL(3,B$8:B8)，漏了列前面的 $。这样公式向右拖到C列时，区域会从B列偏移成C列，统计结果全乱。记住：$B$8 才是真正锁定起点的写法，行列都要锁。


招2：SUBTOTAL(103,…) — 手动隐藏行也不断号
📌 场景：除了筛选，你还手动右键"隐藏"了一些敏感行（如高管薪资行）。用招1的 =SUBTOTAL(3,…)，手动隐藏的行仍然被计数，序号会断。
🔧 怎么做：
把公式里的 3 改成 103 就行：
=SUBTOTAL(103,$B$8:B8)
数字一变，效果完全不同：现在不管是筛选隐藏的，还是你手动右键隐藏的，SUBTOTAL 统统跳过。
📐 原理：
SUBTOTAL 的第一个参数叫 function_num，它分两档：
function_num
含义
忽略筛选隐藏
忽略手动隐藏
3
COUNTA
✅
❌
103
COUNTA
✅
✅
9
SUM
✅
❌
109
SUM
✅
✅

规律很简单：1-99 只管筛选，101-199 筛选手动全管理
兼容性：所有Excel版本均支持（SUBTOTAL从2003版开始就支持101-111系列功能码）。

🔴 踩坑提醒：
有同事问：既然103更全面，为什么不全用103？因为大部分人只需要应付筛选场景，用3就够了。如果表格有手动隐藏需求且数据敏感（如薪资表、绩效表），才需要用103。 日常使用3完全够用。


招3：配合条件格式 — 当前可见序号自动高亮
📌 场景：数据量大时（如1000行员工表），筛选后你要找"当前筛选结果一共多少项"，眼睛扫序号列很累。让条件格式自动把最后一项标黄。

🔧 怎么做：
第1步：选中序号列 A8:A15。
第2步：开始 → 条件格式 → 新建规则 → "使用公式确定要设置格式的单元格"。
第3步：输入公式：
=A8=MAX($A$8:$A$15)
第4步：点"格式"，设置填充黄色、字体加粗，确定。
选完成后，序号列最后一行会自动变黄——一眼看到"当前共 XX 项"。

📐 原理：
筛选后，SUBTOTAL 只给可见行编了连续序号。由于隐藏行的 SUBTOTAL 值不会超过它上面最近一个可见行的序号，所以 MAX 取整个序号列的最大值时，拿到的恰好是最后一个可见行的序号。
兼容性：所有Excel版本均支持。

🔴 踩坑提醒：
⚠️ 序号列必须是数字格式。 如果你用了招4的 TEXT 补零（如 "001""002"），序号变成了文本，MAX 对文本无效，高亮会失效。解决方法：要么不用 TEXT 补零，要么在条件格式中用辅助列存原始数字序号。
另外 $A$8:$A$15 的范围宁大勿小——如果你的数据有5000行，写1000就只统计前1000行，后面的高亮不准。


招4：序号增强 — 前缀、补零、显示总数
📌 场景：领导要求序号格式统一（"第X项"），或者序号要对齐排版（"001"），或者想同时看到"当前是第几项/总共几项"。

🔧 怎么做：
在招1公式外面套一层格式函数即可。三种场景，三种写法：
① 加前缀"第X项"（适合汇报场景）：
="第"&SUBTOTAL(3,$D$8:D8)&"项"
② 补零对齐（适合长列表，如100+行，"001,002…099,100"不会出现"1和10"对不齐的尴尬）：
=TEXT(SUBTOTAL(3,$D$8:D8),"000")
③ 显示"当前项/总数"（适合发给领导审阅）：
=SUBTOTAL(3,$D$8:D8)&"/"&COUNTA($D$8:$D$13)

兼容性：所有Excel版本均支持。
🔴 踩坑提醒：
方式③中 COUNTA 统计全部行（含隐藏行），千万别手滑写成 SUBTOTAL(3,…)——那样筛选后总数会缩水（如50条数据筛剩10条，总数显示10），完全失去参考价值。记住：显示"总数"用 COUNTA，显示"当前序号"用 SUBTOTAL。



进阶联动：4招串联，打造智能序号系统
单独每招都好用，但把它们组合起来才是王炸。咱们按这个逻辑搭一个完整系统：
为什么要组合？ 现实中不是"选一招用"，而是"做一张表同时需要":序号连续 + 手动隐藏也生效 + 最后一行高亮 + 格式好看。四招联用，一次搭好永久省心。
5步搭建：
步骤
操作
涉及招式
关键细节
1
A8输入 =SUBTOTAL(103,$B$8:B8)
招1+招2
用103覆盖筛选+手动隐藏
2
改为 =TEXT(SUBTOTAL(103,$B$8:B8),"000")
招4
补零对齐，注意此时序号为文本
3
在辅助列（如H列）输入 =SUBTOTAL(103,$B$8:B8)
招3适配
条件格式引用H列数字序号（非A列文本），公式 =H2=MAX($H$8:$H$1000)
4
冻结首行：视图 → 冻结窗格 → 冻结首行
—
滚动时不丢表头
5
测试：筛选→手动隐藏→检查序号是否连续、高亮是否准确
全招验证
交差前必测

最终效果：表格筛选/隐藏后，序号始终"001,002,003……"连续三位数，最后一个序号自动标黄，首行固定。打印、导出、截图发给领导，再也没有断号尴尬。

高频场景

场景1：HR员工花名册
每个月都要筛选不同部门/状态的员工做报表。用招1 =SUBTOTAL(3,$B$8:B8)，筛选"在职-技术部"后序号自动1,2,3……不再断开。

场景2：财务付款清单
账期到了，筛选"已付款"记录导出给审计。用招4方式③ =SUBTOTAL(3,$B$8:B8)&"/"&COUNTA($B$8:$B$500)，审计算账时一眼看到"第X项/共X项"，效率翻倍。

场景3：销售客户名单
销售每天跟进不同状态的客户，习惯右键隐藏已成交客户行。用招2 =SUBTOTAL(103,$B$8:B8)，手动隐藏也不断号，配合招3条件格式自动高亮最后一个客户。

避坑指南
坑1：B列有空单元格，序号会断开
SUBTOTAL(3,…) 统计的是非空单元格数。如果B列有空白行，那一行序号不会递增，出现1,2,2,3（第3行B列为空，序号和第2行一样）。
✅ 正确做法：引用一个永远不会为空的列（如员工ID列、日期列）。如果实在没有，在表格最右侧加一个辅助列，全部填1。
坑2：SUBTOTAL(3) vs SUBTOTAL(9) 用混
SUBTOTAL(9,…) 是 SUM（求和），不是 COUNTA（计数）。如果你引用的B列是文本，"9"会返回0。
✅ 正确做法：序号统计用 3 或 103（COUNTA），数字求和才用 9 或 109（SUM）。
坑3：公式只写到当前数据范围
有同学把SUBTOTAL公式只写到A2:A50，后面新加的数据行就没有公式。
✅ 正确做法：预置公式到足够大的范围（如A2:A1000）。更好的方法是把数据区域转成"表格"（Ctrl+T），插入表格后公式自动扩展到新行。注意表格中引用会变成结构化引用（[@列名]），写法略有不同。
坑4：序号列本身被筛选，SUBTOTAL可能异常
如果筛选的是序号列（A列）本身，SUBTOTAL的引用基准（B列某行）可能被一起筛掉。
✅ 正确做法：永远不要筛选序号列。 序号列是输出结果，筛选应该在数据列（部门、状态、日期等）上进行。
坑5：复制粘贴时忘了"只粘值"
SUBTOTAL公式的序号是动态计算的。如果你复制序号列到另一个表，粘贴的是公式而非结果，新表中没有原始数据区域，公会报错或显示异常。
✅ 正确做法：复制序号列后，右键 → 选择性粘贴 → 数值，把动态序号固化为静态数字。
📥 想要一键套用？
我把本文 4 套智能序号公式、条件格式预设模板、超级表完整示例打包成《Excel 智能序号专用模板》，自带 HR 花名册、财务付款清单两套业务样表，打开直接套用。

点击下方「华杰办公助手」小程序卡片，免费一键下载全套模板，配套往期全部 Excel 函数速查表。
华杰办公助手