在如下图所示的身份证号码中,已知可能存在重复,如何能够快速将其找出呢?使用条件格式,是一个很好的选择。选中标题下方的所有数据,在“开始”选项卡,单击“条件格式”下拉按钮,在菜单中单击“突出显示单元格规则” → “重复值”,在弹出的对话框中,直接单击“确定”,就可以了。
下面是得到的结果,可以看到有3组重复值(此前对数据已经进行了排序)。原来如此简单!但不要高兴过早,仔细检查发现,第26和27行的两个号码,其实并不相同,但Excel也将其标记成为了重复值。Excel这样的经典工具,竟然会犯这样的低级错误吗?原因在于,当身份证号码全部是数字的时候,虽然已经是文本格式,但Excel 中的函数依然会将其当做数字来判断,因此只能比较前15位,而本例子中的第26和27行数据的差异位于第17位,这样就导致判断为相同了。
那么,该如何避免这个问题呢?我们现在将刚刚设置的条件格式清除,在“开始”选项卡,单击“条件格式”,在菜单中选择“清除规则” →“清除整个工作表的规则”,删除之前的条件格式规则。然后选中所有数据,重新在 “开始”选项卡, 单击“条件格式” ,在菜单中 选择 “新建规则 ”,在打开的对话框中,规则类型设置为“使用公式确定要设置格式的单元格”,并输入公式“=COUNTIF($A$2:$A$54,$A2&"*")>1”,然后适当设置格式,例如此处的蓝底白字。这个公式的含义是判断每个单元格在该列数据中出现的次数,如果大于1次,则作为重复值,为避免Excel将单元格作为数值判断,只比较前15位,我们在Countif()函数的第二个参数,也就是条件参数那里,设置为$A2&"*",星号代表任意的字符,这样公式就会将单元格作为文本处理了。需要注意的是2前面一定不要有美金符号,这样才能对整列进行判断。最终的完成结果如下图所示,可以看到第26和27行已经不会再被作为重复值处理了,问题完美解决。如需下载案例素材,请关注公众号,并回复 “ ID ” ,下载和解压密码均为 “ 51ds ” 。