你有没有遇到过这种崩溃瞬间——

月底汇总，发现同一个员工工号出现了两次。或者对账时，同一张发票号录进去了三遍。翻回去找，密密麻麻全是数据，根本不知道哪行是多出来的那个。

这不是偶尔手抖，这是Excel没设防线。

今天这篇，就来把这道防线彻底建起来。


前置步骤：先把历史重复清干净

新设防线之前，先把现有的烂摊子收拾好。

选中整列数据 → 「开始」→「条件格式」→「突出显示单元格规则」→「重复值」→ 设置红色填充 → 确认。


这时候所有重复值都变红了。按颜色筛选，把红色的行核对一遍，该删的删，该留的留。

清完之后，把条件格式删掉——后面我们要用公式来设防，不用这个了。


第一步：用 COUNTIF + 数据验证，把重复挡在门外

核心逻辑就一句话：每次录入新数据前，先数一数这个值在已有数据里出现了多少次。超过0次，就不让过。

具体操作：


选中你要设防的列（比如 A2:A1000）

点「数据」→「数据验证」

允许条件选「自定义」

公式填：=COUNTIF($A$2:$A2,A2)=1（注意：第一个参数锁定起始行，第二个参数随行变化，这样每行检查的范围是动态的）

切换到「出错警告」，填提示语，比如：该工号已存在，请核实后再录入

确认


验证一下：故意输入一个已有的值，应该弹出警告并拒绝录入。没弹出来就回去检查公式里的绝对引用。

一个大家容易忽略的盲区：COUNTIF 不区分大小写。ABC001 和 abc001 对它来说是同一个值，会被拦下来。如果你的数据大小写敏感（比如系统编码区分大小写），这个方法就拦不住，后面进阶部分会给出对应方案。


第二步：粘贴进来的数据，数据验证管不了

这是一个很多人踩过的坑：数据验证只能拦住手动键入，粘贴进来的数据直接绕过去。

如果有人从其他表格直接复制粘贴了一批数据，重复的照样进来，防线形同虚设。

怎么补救？还是用条件格式：

选中整列 → 「条件格式」→「新建规则」→「使用公式确定格式」→ 填：=COUNTIF($A$1:$A1,A1)>1 → 设置红色背景。

这样一旦有重复值出现（不管是手动录还是粘贴进来的），单元格立刻变红，一眼就能看到。

注意：这个只是补救，不是拦截。它不会阻止重复录入，只会告诉你哪里出了问题。配合筛选使用——按颜色筛红色，手动核对删除。


高频场景：文本格式这个坑必须提前踩

场景1：HR录工号

工号一般是纯数字（比如 001234），但 Excel 默认会把前缀 0 吃掉，变成 1234。

先把整列设置为文本格式，再开始录数据。顺序不能反——先录数据再改格式，前导零已经没了，格式改了也没用。

COUNTIF 的公式不用改，因为文本格式下 "001234" 和 "001234" 依然精确匹配。

场景2：财务录发票号

发票号通常是20位以上的长数字。Excel 对超过15位的数字会自动用科学计数法处理，末位变成0，123456789012345678 和 123456789012345679 在 Excel 看来是同一个数。

解决方法同上：整列提前设为文本格式，再录入，数字才会原样保存。


进阶技巧：还不够？再加两把锁

技巧一：多列联合唯一性（COUNTIFS）

如果你需要的不是单列唯一，而是"A列+B列组合唯一"——比如同一个供应商可以有多张合同，但同一个供应商的同一份合同号不能重复——就要换成 COUNTIFS：

=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)=1

提示：该供应商合同编号重复

逻辑一样，只是同时判断两列。

技巧二：大小写敏感匹配（SUMPRODUCT + EXACT）

如果你的数据区分大小写，用这个：

=SUMPRODUCT((EXACT($A$2:$A2,A2))*1)=1

EXACT 函数区分大小写，ABC001 和 abc001 会被视为不同值。

性能提示：SUMPRODUCT 是数组公式，数据量大时会变慢。超过5000行，建议用辅助列或换用 VBA 方案，否则每次录入都要等一下，用起来很难受。


新手避坑

坑1：设完防线忘了备份原始数据，COUNTIF 公式里的范围一改就全乱了。操作前先另存一份。

坑2：选区范围选错——如果你只选了 A2:A100 设验证，A101 以后录入的数据根本没有防线。建议直接选到 A2:A10000，宁可多设也别漏。


写在最后

数据验证这个功能很多人知道，但大多数人只用过「整数范围」或「下拉列表」。拿它来做唯一性校验，配合 COUNTIF，才算真正把这个功能用起来了。

下次有人问你"怎么防止重复录入"，把这篇发给他，省得他再去数一遍重复了多少行。

关注「华杰科技工作室」，工作里那些 Excel 小麻烦，我们慢慢帮你清。

如果你的表格里有某一列特别容易录重——工号、发票号、合同号——欢迎评论区说出来，下期专门讲你们遇到的那种场景。
最后说个实在的，我做了一个免费大礼包《6个基础模板》+《Excel快捷键大全速查卡》**，里面整理了今天讲的快捷键和常用公式。需要的朋友，关注「华杰科技工作室」