首页 > Excel专区 > Excel技巧 >

excel 2020如何实现姓名查重、身份证号查重实例教程及技巧

Excel技巧 2021-11-17 15:24:04

这篇文章跟大家分享两个Excel工作表数据查重案例。

案例一、姓名查重

应用场景:查找下图工作表A列中的重复姓名,并进行突出显示。

Excel办公技巧:姓名查重、身份证号查重案例分享

操作步骤:选中A2到A8单元格区域,在"开始"选项卡"样式"功能组中单击"条件格式"按钮,在下拉列表中选择"突出显示单元格规则",在弹出的级联菜单中选择"重复值",在接下来弹出的"重复值"对话框中保持默认设置,单击"确定"按钮,这样A列中的重复姓名就被突出显示了。

Excel办公技巧:姓名查重、身份证号查重案例分享

案例二、身份证号查重

应用场景:查询下图工作表A列中的身份证号是否有重复。

Excel办公技巧:姓名查重、身份证号查重案例分享

身份证号比较特殊,如果用案例一中设置条件格式的办法可能无法返回正确的查重结果,比如我们按案例一中的办法查询A列中的身份证号,会返回下图所示的错误结果。A2和A3两个单元格中的身份证号被认定为重复值进行突出显示。其实这两个身份证号最后一位数字是不同的(一个是"1",一个是"2"),并非重复身份证号。

Excel办公技巧:姓名查重、身份证号查重案例分享

出现上述情况的原因在于用条件格式进行查重时,只能对身份证号的前15位数字进行正确识别,最后面3位数字无法识别。由于A2和A3两个单元格中身份证号前15个数字是一样的,所以身份证号被认定为重复值。

解决办法:

用计数函数countif进行查重,如果某个身份证号出现的次数等于1,说明没有重复;如果次数大于等于2,说明身份证号有重复。选中B2单元格,输入公式=COUNTIF($A$2:$A$7,A2&"*"),然后回车确认,将公式向下填充即可。

Excel办公技巧:姓名查重、身份证号查重案例分享 Excel办公技巧:姓名查重、身份证号查重案例分享

公式说明:

第一个参数$A$2:$A$7是查询范围,$符号代表绝对引用,将查询范围锁定;

第二个参数A2&"*"是查询内容,在A2后面加上&"*"的作用是使A2单元格身份证号中所有18位数字都能被完整识别出来。

注意事项:如果第二个参数直接写成A2,A2单元格中身份证号只有前面15位数字能正确识别,最后面3位数字无法识别,向下填充公式后,就会出现用条件格式查重一样的问题,A2和A3单元格中的身份证号会被认定重复(参见下图)。

Excel办公技巧:姓名查重、身份证号查重案例分享

Excel办公技巧:姓名查重、身份证号查重案例分享

A2和A3单元格中的身份证号被认定重复,countif函数返回错误计数结果“2”


标签: excel姓名查重excel身份证号查重

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.