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

我们这边先准备一份数据,这是随机生成的身份证号码。仅提供参考。
数据
选中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函数
这样,重复值就被标记出来了。 
countif函数结果
解析:
= 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服务。