嗨玩手游网

Excel身份证号码查重,这才是正确的方法

今天跟大家分享下我们如何正确地对身份证号码查重,这个也是一个学员遇到的问题,他发现在利用条件格式标记重复身份证号码的时候,两个完全不一样的身份证号码也会被标记颜色,如下图所示,还跟我说发现了一个Excel的bug,这个其实并不是bug,它与Excel的精度有关,下面我们就来了解下有关的内容吧

想要从零学习Excel,可以点击上方

一、错误原因

Excel的精度只有15位数据,在默认情况下(常规格式)如果我们输入的数据超过了15位,15位之后的数据都会被舍去,设置为0来显示。且无法恢复

这也就是我们在录入身份证号码的时候,必须将单元格的格式设置为文本格式的原因,如果不将格式设置为文本,身份证号码的后3位会被舍去,就会被设置为0来显示,这个过程是不可逆的,只有重新输入

当我们使用条件格式对数据进行查重的时候,Excel会自动将文本格式的数值转换为常规格式,后面的3个数字就会被忽略掉,仅仅只会比对身份证号码前15位的数据,所以身份证号码的前15位相同,条件格式就会为其标注颜色,这个就是错误原因。

那么对于这样的情况我们应该如何解决呢?

二、解决方法

最简单的方法就是利用countif函数,我们只需要将公式设置为:=COUNTIF($D$2:$D$10,D2&"*"),然后向下填充即可,结果大于1的就是重复数据。

跟大家简单地介绍下这个函数的计算原理

第一参数:$D$2:$D$10第二参数:D2&"*"

在这里函数中关键的是第二参数

&:是一个链接符号,它结果的格式一般默认是文本格式

*:它是一个通配符,可以表示没有任何字符,也可以表示有无穷多个字符,在这里它就表示没有字符

使用链接符号将身份证与星号连接后,身份证号码就会保持文本格式代入公式中计算,这样的话就可以得到正确的结果了

三、辅助列

如果你觉得上面的方法比较难理解,跟大家分享一个更加简单的方法,不过需要设置一个辅助列。

首先我们使用链接符号为身份证号码的后面连接一个相同的汉字,比如在这里连接一个我字,只需要将函数设置为:=C2&"我"向下填充公式即可

最后我们选择数据区域,使用条件格式中的【突出显示重复值】即可,可以看到这个时候数据并没有被标记颜色

这么做的原因是因为,我们在身份证号码中链接了一个文本,因为有文本存在,单元格的格式是会一直保持为文本格式,这样的的话就能得到正确的结果了

以上就是今天分享的全部内容,关键是与Excel的精度有关,如果大家觉得麻烦,就选择制作一个辅助列来查重

我是Excel从零到一,关注我,持续分享更多Excel技巧

Excel身份证号码查重,这才是正确的方法

今天跟大家分享下我们如何正确地对身份证号码查重,这个也是一个学员遇到的问题,他发现在利用条件格式标记重复身份证号码的时候,两个完全不一样的身份证号码也会被标记颜色,如下图所示,还跟我说发现了一个Excel的bug,这个其实并不是bug,它与Excel的精度有关,下面我们就来了解下有关的内容吧

想要从零学习Excel,可以点击上方

一、错误原因

Excel的精度只有15位数据,在默认情况下(常规格式)如果我们输入的数据超过了15位,15位之后的数据都会被舍去,设置为0来显示。且无法恢复

这也就是我们在录入身份证号码的时候,必须将单元格的格式设置为文本格式的原因,如果不将格式设置为文本,身份证号码的后3位会被舍去,就会被设置为0来显示,这个过程是不可逆的,只有重新输入

当我们使用条件格式对数据进行查重的时候,Excel会自动将文本格式的数值转换为常规格式,后面的3个数字就会被忽略掉,仅仅只会比对身份证号码前15位的数据,所以身份证号码的前15位相同,条件格式就会为其标注颜色,这个就是错误原因。

那么对于这样的情况我们应该如何解决呢?

二、解决方法

最简单的方法就是利用countif函数,我们只需要将公式设置为:=COUNTIF($D$2:$D$10,D2&"*"),然后向下填充即可,结果大于1的就是重复数据。

跟大家简单地介绍下这个函数的计算原理

第一参数:$D$2:$D$10第二参数:D2&"*"

在这里函数中关键的是第二参数

&:是一个链接符号,它结果的格式一般默认是文本格式

*:它是一个通配符,可以表示没有任何字符,也可以表示有无穷多个字符,在这里它就表示没有字符

使用链接符号将身份证与星号连接后,身份证号码就会保持文本格式代入公式中计算,这样的话就可以得到正确的结果了

三、辅助列

如果你觉得上面的方法比较难理解,跟大家分享一个更加简单的方法,不过需要设置一个辅助列。

首先我们使用链接符号为身份证号码的后面连接一个相同的汉字,比如在这里连接一个我字,只需要将函数设置为:=C2&"我"向下填充公式即可

最后我们选择数据区域,使用条件格式中的【突出显示重复值】即可,可以看到这个时候数据并没有被标记颜色

这么做的原因是因为,我们在身份证号码中链接了一个文本,因为有文本存在,单元格的格式是会一直保持为文本格式,这样的的话就能得到正确的结果了

以上就是今天分享的全部内容,关键是与Excel的精度有关,如果大家觉得麻烦,就选择制作一个辅助列来查重

我是Excel从零到一,关注我,持续分享更多Excel技巧

核对两个表格的数据是否一致,你还在一一查看?试试这3种方法

对于办公一族来说,核对数据是一项基本工作,也是一项不可缺少的重要工作,那如何核对两个(或多个)表格的数据是否一致呢?除了一一查看之外,有没有更好的方法呢?今天,小编给大家介绍3种方法,总有一个适用于你!

一、核对数据:选择性粘贴法。

目的:核对表1和表2的数据是否一致。

方法:

1、选择表1中的目标单元格区域,复制。

2、在表2的目标单元格区域左上角的第一个单元格处右键-【选择性粘贴】,打开【选择性粘贴】对话框,选择【运算】中的【减】并【确定】。

3、非“0”区域的值即为有差异的值。

解读:

此方法只适用于数值的比较。

二、核对数据:合并计算法。

目的:核对表1和表2的数据是否一致。

方法:

1、新建空白工作表,单击【数据】菜单【数据工具】组中的【合并计算】,打开【合并计算】对话框。

2、选择【函数】中的【标准偏差】。

3、单击【引用位置】右侧的箭头选择表1中的目标单元格区域,然后单击【所有引用位置】右侧的【添加】。

4、重复第3步,添加表2的目标单元格区域。

5、选中【标签位置】的【首行】和【最左列】,并【确定】。

6、非“0”区域的值即为有差异的值。

解读:

此方法只适用于数值的比较。

三、核对数据:条件格式法。

目的:核对表1和表2的数据是否一致。

方法:

1、选中表1的目标单元格区域,单击【开始】菜单中的【样式】组中的【条件格式】-【新建规则】,打开【新建格式规则】对话框。

2、单击【选择规则类型】中的【使用公式确定要设置格式的单元格】。

3、单击【为符合此公式的值设置格式】右侧的箭头,选择表1中的目标单元格区域并修改为相对引用,然后返回,紧接着输入符号:<>。

4、继续单击【为符合此公式的值设置格式】右侧的箭头,选择表2中的目标单元格区域并修改为相对引用,然后返回。

5、单击右下角的【格式】打开【设置单元格格式】对话框,单击【填充】标签并选择填充色。

6、【确定】-【确定】。

解读:

此方法除了数值比较外,还可以比较文本。

最美尾巴:

在实际的工作中,如果只是数据的比较,可以使用方法一或方法二,如果既要比较数值,还有比较文本,则必须使用方法三。要灵活对待哦!