Excel表格设置重复值时，为何前面数字一样而后三位数字不一样的数据却显示为重复？

这个就牵扯到了excel对与数字的对比之精确到15位，而15位之后的都会忽略为0所以在使用条件格式设置格式的时候会将位数不同的数据统计为相同数据，对于这样的数据我们可以使用countif函数来统计

首先我们输入公式

=IF(COUNTIF($A$2:A7,A7&"*")>1,"重复","")

将a2单元个中的值&上*号将它强制转换为文本

在这里结果为1的为不重复

这个问题一般都出现在身份证号码中

如下图，要统计A列身份证号码是否有重复

在B2单元格输入公式：=IF(COUNTIF($A$2:$A$5,A2)>1,"重复","")

结果显示A2单元格的身份证和A4单元格的身份证号码是重复，也就是说这两个单元格的身份证号码是相同的

但是我们仔细观察一下就能发现，A2和A4只是前15位数字相同，而后三位数字是不相同的。

那么，为什么用公式得出的结果是重复的呢？

其实原因在于excel中只能保留15位有效数字，如果你在单元格不设置文本格式的前提下输入超过15位数字，那么超过15位后的数字都会显示0

但即便你在单元格中设置了文本格式，那也只能保证在单元格里面显示出完整数字，这时候用COUNTIF函数运算时，则会将文本型数字当做数值来处理，所以统计时就会将身份证后三位当做0

那如何统计才能得出正确结果呢？

我们只需要在B2单元格中输入公式：=IF(COUNTIF($A$2:$A$5,A2&"*")>1,"是","否")

在COUNTIF函数第二个参数后面加上“*”（通配符）将它强制转为文本来统计即可。

我是小螃蟹，如果您想学习更多excel知识，请关注我的头条号【excel函数教程】

这是软件本身对数值精确度的一个判定问题，简而言之就是一串数值，EXCEL他只能读到第15位数字，之后的数字它自动忽略掉当0看待了。

一般身份证号是18位数字，从第15位开始，后面的三位自然是忽略掉了，所以题主的问题一般是出在身份证号重复值的判定上面。

比如下面这样，前面15位一样，后面3位一样，但函数的判定公式：=IF(COUNTIF($A$1:$A$5,A1)>1,"重复","")，最终的结果显示都是重复。

这里虽然你的单元格格式转换成了文本，但在COUNTIF这个函数中，还是将这串文本数字当成数值来处理。

解决的方法也是很简单，就是将函数内部需要比较的单元格的数值强制转为文本即可。我们只需要将公式写成：=IF(COUNTIF($A$1:$A$5,A1&"*")>1,"重复","")即可完成正确的判断了。

解答完毕，喜欢的同学记得加个关注点个赞哦~！

晓得不晓得呀 • 2022-12-31 21:05:16 • 生活 • 904 • 0 • 0

职场 Excel

内容仅供参考，如果您需解决具体问题(尤其法律、医学等领域)，建议您详细咨询相关领域专业人士。