Excel防止重复录入:COUNTIF+数据验证,从源头拦截错误

录入数据最崩溃的瞬间是什么？不是数据多，而是录完之后发现重复了。

很多时候我们的做法是：录完一列，用“条件格式”把重复项标红，再一个个去手动删。这叫什么？这叫事后诸葛亮。防重复最好的时机，不是录完之后查，而是敲键盘的那一刻就不让你输进去。

今天这篇，教你用 COUNTIF + 数据验证，给表格请个“守门员”。凡是设定了这个规则的列，只要输入重复值，Excel 直接弹窗拦截，根本连存进去的机会都没有。

先看效果：👇

这招适合用在哪？

只要是不允许出现重复值的列，都能用。最典型的三个场景：

• HR 录入员工工号（一个工号对应一个人）

• 财务录入发票号（一张发票只能报一次）

• 行政录入合同编号（一号一档）

四步设置，从源头防错

打开你的 Excel 表，跟着下面四步走：

第一步：选中要设置的区域

选中你需要防止重复录入的那一整列数据区。比如工号在 B 列，数据从第 2 行开始，你就选中 B2:B1000（范围稍微选大一点，给未来新增数据留够空间）。

⚠️ 注意：从第一个数据单元格开始选，千万别把表头选进去。 表头要是被算进验证范围，后面你输什么都会觉得不对劲。

第二步：打开数据验证

在顶部菜单栏找到 【数据】 选项卡，点击 【数据验证】（老版本的 Excel 这里叫“数据有效性”）。在弹出的窗口里，默认停在 【设置】 选项卡。

第三步：设置验证条件（核心）

在这个窗口的“允许”下拉菜单里，把默认的“任何值”改成 【自定义】。然后，在下方的“公式”框里，原封不动地输入这串代码：

=COUNTIF($B$2:$B$1000,B2)=1

这串公式是什么意思？拆开看就懂了：

• COUNTIF(范围, 条件)：在指定的范围里，数一数满足条件的单元格有几个。

• $B$2:$B$1000：这是你刚才选中的整列范围。注意里面的 $ 符号，这是绝对引用，锁死了范围，一会儿往下拖动或者复制时，这个范围不会跑偏。

• B2：这是你目前正在输入的第一个格子的相对位置（注意，这里不能加 $）。

• =1：这是给 Excel 定的规矩——在这个范围里，这个值只能出现 1 次。大于 1 次，就是违规。

第四步：设置出错警告

点开顶部的 【出错警告】 选项卡。

• 标题：填“重复录入”。

• 错误信息：填“该工号已存在，请重新输入！”（这里的字是给录入数据的人看的，写得越直白越好）。点击 【确定】。

  

搞定！现在你在 B 列随便找个格子，把上面的工号复制粘贴进去试试。回车的一瞬间，Excel 会直接弹出一个红叉警告框，把你拦在门外。

出错警告里样式有3种形式：

• 停止（默认，最严厉）：强行拦截。只要你输入的数据不符合验证条件，点回车的一瞬间，这个红叉就会弹出来。

• 警告（温和，带商量）：弹窗询问。当你输入不符合条件的数据时，它会弹出来问一句，但不强制拦截，再次按回车就可以继续输入。

• 信息（最温柔，只提醒）：只做提示。其实它根本不防错，只要你输入了不符合规则的数据，它弹出来告诉你一声。

  

⚠️ 三个容易踩坑的细节

第一，第一行的相对引用千万别写错。第三步的公式里，$B$2:$B$1000 是绝对锁定的，但最后的那个 B2 绝对不能加 $。如果你写成 $B$2，Excel 会认为你每一行都在跟第一行较劲，到时候哪怕你输入全新的数据，它也会报错不让你输。

第二，它防不住“复制粘贴”。这个机制是“守门员”，但它有个致命弱点：数据验证防不住 Ctrl+V。如果你从别的表里复制了一堆数据，直接粘贴到这一列，哪怕里面有一万个重复值，Excel 也会照单全收，根本不会弹窗。所以，如果是大段数据导入，还是得用“条件格式”标红排查。如果是手工逐个敲入，这个设置就是神器。

第三，删掉的数据，它不会再拦着你。比如你录了“001”，后来发现录错了，把这行删掉。下次你再敲“001”，它依然会放行。因为它只认当前表里存在的值，不会记仇。

博主私藏建议

我建议你做完这个设置后，顺手把它保存成模板。

比如建一个“员工信息录入模板.xlsx”，把工号列、身份证列、手机号列全用 COUNTIF 锁死，发给同事用。这比你每次在群里喊“大家录仔细点，别搞重复了”要管用一万倍。用系统规则去管人，而不是用嘴去管人。

另外，如果你觉得公式太长了记不住，其实有一个简化版：=COUNTIF(B:B,B2)=1。直接引用整列，不用写 1000，范围更大。但缺点是全列引用在极个别旧版本里运行会稍微卡顿，按 B2:B1000 这种固定范围写，是最稳妥的。

你平时录入数据，是手工敲得多，还是复制粘贴得多？评论区聊一下~