条件格式也有不靠谱的时候？

我们这边先准备一份数据，这是随机生成的身份证号码。仅提供参考。

选中A列数据，点击【开始】选项卡下的【条件格式】下拉菜单的“突出显示单元格规则”里的“重复值”。 

在【重复值】窗口进行设置。 

结果出来了。 很快我就发现不对了。按住Ctrl+F打开【查找和替换】窗口，查找A2单元格的值，但是系统提示只有“1个单元格被找到”。可是标记红色的不是重复值吗？ 

这是什么原因呢？我研究了一下发现：EXCEL默认数据只有15位，所以长于15位的部分在数据比对时全部视作0。

由于这列数据长度是16位，那么第16位的数据系统统一都会认为是 0，因此出现了这么多重复值。这就属于EXCEL中的假重复。这种情况下，直接按COUNTIF公式常规用法=COUNTIF($A$2:$A$17,A2)查重肯定失手，这就是小伙伴认为自己被COUNTIF坑的原因。

那长于15位的数据如何判断重复值呢？接下来给大家介绍几个使用公式的条件格式来解决这个问题。

COUNTIF标注重复

选中要判断重复项的数据区域A2:A547, 点击【开始】选项卡下的【条件格式】下拉菜单的“新建规则”。 

在【新建格式规则】窗口选择“使用公式确定要设置格式的单元格”，在下方的“编辑规则说明”里输入公式“= COUNTIF($A$2:$A$17,A2&"*")>1”，点击“格式”，选择填充色为蓝色。点击确定。 

这样，重复值就被标记出来了。 

解析：
= COUNTIF($A$2:$A$17,A2&"*")>1

COUNTIF（指定区域，条件）对指定区域中符合条件的单元格进行计数。指定区域是对单元格进行计数的区域。条件的形式可以是数字、表达式或文本等。"*"是通配符，代表任意多个字符的文本。

由于EXCEL中超过15位的数字只能保留15位有效数字，后面全部视为0，所以“110101199003075752”和“110101199003075750”会被认为是重复值，因为它们都被当成了数字“110101199003075752”。这里使用 &"*"将单元格数字后面统一添加*符号强行当做文本进行识别统计，就可以准确地通过计数值是否大于1识别出数字是否重复。

需要注意的是，该条件格式应用的区域必须从A2开始，同时由于应用的是整列单元格所以COUNTIF指定区域必须加绝对应用，而A2则为相对引用。 

假如你学习到了这个新技能不妨转发推荐给你的小伙伴。并动动小指头收藏，以免下次走丢。

我们将定期更新Word、Excel、PPT等操作技巧。pdf转换器供在线免费的PDF转word、PDF转Excel、PDF转PPT服务。