Excel中一条语句,秒查万行数据是否缺失
- 2026-11-06 16:44:13
Excel中一条语句,秒查万行数据是否缺失· Excel数据处理第 1篇 Excel 公式验编号连续性 
内容摘要 1.这个公式在干什么 先说清楚它到底在验什么。公式是： 拆解成四步：取整（INT 把每行截成整数）→ 去重（UNIQUE 拿掉重复值）→ 数个数（COUNTA 数去重后剩几个）→ 比跨度（MAX 减 MIN 再加 1，就是把 MIN 到 MAX 这段整数全数一遍）。 逻辑是：如果「去重后的整数个数」刚好等于「MIN 到 MAX 的跨度」，说明从 MIN 到 MAX 每个整数都至少出现了一次，也就是没有缺号。 思路没问题，能跑，出结果快。问题出在它对「干净数据」假设得太满。 2.盲区一：空单元格会被当成 0 Excel 里，空单元格一旦进到 INT(E1:E1500) 这种算术数组，会被当成 0。这不是猜测，是 Excel 的硬规则，多个官方文档都写过「blank cells become 0 in UNIQUE」。 后果有两个级别： 整列空：全变 0，UNIQUE={0}、COUNTA=1、跨度=1，原公式返回 V。空表也能「通过」。
混了空行：空变 0 把 MIN 拉到 0，跨度虚增，原公式照样返回 V，缺口被 0 补上。 误区：以为「公式返回 V 就等于编号没问题」。 正确：空单元格会污染 MIN，让缺号被悄悄掩盖，空列甚至能直接骗过校验。 3.盲区二：它根本不查重复 这是更隐蔽的一个。原公式只看「去重后个数是否等于跨度」，根本没验证「每个号恰好出现一次」。 实测：1、2、2、3 这组，UNIQUE 去重后是 {1,2,3}，个数 3；跨度 3-1+1=3。3=3，返回 V。但数据里明明有个重复号 2，真实的编号序列早就不对了。 重点：校验「连续编号」必须同时成立三件事——无缺号、无重复、无脏数据。原公式只验了其中一件，是必要非充分。 4.还有两个小坑 小数被截断：INT(1.5) 和 INT(1.9) 都变成 1。如果编号本该是纯整数却混进了小数，错误会被掩盖。
版本门槛：UNIQUE 只在 Excel 365 / 2021 才有。老版本（2019 及更早）打开直接 #NAME?。很多公司电脑还是老版本，发过去同事根本跑不了。 5.健壮版：三个条件一次补齐 把盲区全堵上，用 COUNT（只数数字，忽略空，空不会再变 0）替换 COUNTA，并补上「无重复」「无混入 0」两个判断： 三个条件含义： COUNT = 跨度：无缺号
COUNT = 去重个数：无重复
MIN > 0：没混进 0（你的编号若本身就含 0，删掉这一行即可） 同一组模拟数据下，健壮版对「整列空」「混空」「有重复」三种情况全部返回 X，只有真正干净的连续序列才给 V。 
示意图 · PlantUML 渲染停下来想一想：你的编号里会不会有 0？比如从 0 开始的流水号。有的话，上面第三行要拿掉，不然会误判。 6.想直接看缺了哪几个号？ V/X 只能告诉你「有没有问题」。要列出具体缺号，Excel 365 用 LET 一行搞定——先造出完整序列，再把原数据里没出现过的筛出来： 1、2、4、5 喂进去，它直接吐出 3。对账时这比只看 V/X 有用得多。 校验只是告诉你「坏了」，列出缺号才告诉你「坏在哪」。 7.避坑与迁移 先清脏数据：文本编号先转成数字，否则 INT 整列报错。
老版本替代：用 COUNTIF 辅助列标重复、IF 标断档，或上 Power Query。
边界：允许跳号时「连续」无解，得换业务规则。 这套公式我整理成可直接套用的版本，复制改下区间就能用，不用转发、不用付费。 *本文为作者个人经验整理，仅供参考。* 点个「在看」鼓励一下 💬 评论区聊两句 #Excel#缺号核对#数据校验#办公自动化#对账 本文为作者个人经验整理，仅供参考。
一个 IF+UNIQUE 公式能一秒判断 1500 行编号是否连续，但空单元格会被当成 0、且不查重复，导致空列也能显示 V 通过。本文用实测数字拆穿两个盲区，并给出生 365 与老版本都稳的健壮版和「直接列出缺号」的公式。
=IF(COUNTA(UNIQUE(INT(E1:E1500)))=MAX(INT(E1:E1500))-MIN(INT(E1:E1500))+1,"V","X")
混了空行：空变 0 把 MIN 拉到 0，跨度虚增，原公式照样返回 V，缺口被 0 补上。
版本门槛：UNIQUE 只在 Excel 365 / 2021 才有。老版本（2019 及更早）打开直接 #NAME?。很多公司电脑还是老版本，发过去同事根本跑不了。
=IF(AND(
COUNT(E1:E1500)=MAX(INT(E1:E1500))-MIN(INT(E1:E1500))+1,
COUNT(E1:E1500)=ROWS(UNIQUE(INT(E1:E1500))),
MIN(INT(E1:E1500))>0
),"V","X")
COUNT = 去重个数：无重复
MIN > 0：没混进 0（你的编号若本身就含 0，删掉这一行即可）
示意图 · PlantUML 渲染=LET(list,E1:E1500,
full,SEQUENCE(MAX(list)-MIN(list)+1,,MIN(list)),
FILTER(full,COUNTIF(list,full)=0,"没有缺号"))
老版本替代：用 COUNTIF 辅助列标重复、IF 标断档，或上 Power Query。
边界：允许跳号时「连续」无解，得换业务规则。
你在开发中遇到哪些问题，或者对今天的内容有不同看法？欢迎在评论区聊聊，咱们一起讨论 👇
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
