excel查找重复数据公式(统计员常用excel技巧)

excel查找重复数据公式(统计员常用excel技巧)我们曾经介绍过一个方便又好用的工具,就是【开始】选项卡-【条件格式】中的【突出显示重复值】。

一秒高亮重复值,清晰明了~但是,平时都很好用的条件格式查重,遇到身份证查重就翻车了。像张三、李四、王五和赵六身份证后三位并不相同,却被判定为重复。

这是因为对于由数字组成的身份证,无论我们是否将单元格的格式设置为文本,条件格式在进行重复判断的时候,都会将其判定为数字。而 Excel 中数字的有效位数是 15 位,所以 18 位的身份证,后面三位数就直接被判定为 0 了。这就和单元格为数值的状态下录入身份证,后面三位总是会变成 0 一样。

(末尾为 X 的身份证由于加入了字母,会被认定为文本,因此不会出现上述情况。)那有什么方法可以对身份证进行查重呢?

公式法?? 使用 COUNTIF 函数,统计身份证区域中与当前身份证相同的个数。语法规则如下:=COUNTIF(计数区域,计数的条件)(COUNTIF 用法详解见往期文章:Excel 中的神器 COUNTIF 函数,你真的会用吗?)新建一列辅助列 C 列,在 C2 输入公式后向下填充。公式:=COUNTIF($B$2:$B$7,B2)

诶,不对啊,居然有 5 个人和张三身份证重复?这是因为 COUNTIF 仍然把身份证当作数字进行统计,又把 15 位后面的数字给砍了。我们需要再加点小处理。?? 使用通配符「*」,将数字格式的身份证转为文本格式。通配符「*」代表任意字符,可以是数字,也可以是文本。任意数字或文本,使用连接符「&」加「*」后,都会将这个整体做作为文本格式进行计算。在原来的公式 B2 后增加【&”*”】,这时公式变成下面这样。公式:=COUNTIF($B$2:$B$7,B2&”*”)

现在只有真正重复的张三和张八显示重复次数为 2,即重复。其他人员重复次数为 1,即不重复。

那有没有不写公式的做法?当然有!方方格子可以不用写公式就完成长数字的精确查重哦~

什么?觉得安装插件也很麻烦?如果你是 WPS 用户,那么你连插件也不用装,WPS 的【数据】选项卡,就自带精确匹配 15 以上的长数字的功能~

这三种方法不知道大家喜欢那种?第一种,公式法没有什么使用限制,只要知道公式即可;第二种,装个插件就行,也不占多少空间;第三种,一开始对 WPS 表示抗拒的我,到现在被迫用 WPS 的时间比 Excel 多了之后,表示 ……

注意事项:?? 本案例中提到的方法同样适用于其他的 15 以上长数字,例如银行卡。?? 方方格子进行查重的方式是通过 VBA,因此结果不会随着数据的更改而变化,如果要重新查重,需要再执行一次命令。

身份证等长数字经常会有各种各样的坑,你还遇到过什么呢?可以在留言区中,跟大家吐槽一下~

欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel,互帮互助。现在进群,还会掉落各种学习资源,助力大家提升办公效率

发表评论

登录后才能评论