编按：真没想到！如果不是看了这篇文章，小编绝想不到Excel里还有真假重复。一直被大家喜欢的查重利器COUUTIF函数这次失手了，把不是重复的当成了重复统计……
前两天有个小伙伴给我发了个文档，说自己被COUNTIF坑了，弄错重复被罚了，让我帮他找出正确的重复值。总共有547行数据，如图：
我一看这不是很简单吗？选中A列数据，点击【开始】选项卡下的【条件格式】下拉菜单的“突出显示单元格规则”里的“重复值”。
在【重复值】窗口进行设置。
结果出来了。
很快我就发现不对了。按住Ctrl+F打开【查找和替换】窗口，查找A2单元格的值，但是系统提示只有“1个单元格被找到”。可是标记红色的不是重复值吗？
这是什么原因呢？我研究了一下发现：EXCEL默认数据只有15位，所以长于15位的部分在数据比对时全部视作0。由于这列数据长度是16位，那么第16位的数据系统统一都会认为是 0，因此出现了这么多重复值。这就属于EXCEL中的假重复。这种情况下，直接按COUNTIF公式常规用法=COUNTIF($A$2:$A$547,A2)查重肯定失手，这就是小伙伴认为自己被COUNTIF坑的原因。
那长于15位的数据如何判断重复值呢？接下来给大家介绍几个使用公式的条件格式来解决这个问题。
1、COUNTIF标注重复
选中要判断重复项的数据区域A2:A547, 点击【开始】选项卡下的【条件格式】下拉菜单的“新建规则”。
在【新建格式规则】窗口选择“使用公式确定要设置格式的单元格”，在下方的“编辑规则说明”里输入公式“= COUNTIF($A$2:$A$547,A2&"*")>1”，点击“格式”，选择填充色为蓝色。点击确定。
这样，重复值就被标记出来了。
解析：
= COUNTIF($A$2:$A$547,A2&"*")>1
COUNTIF（指定区域，条件）对指定区域中符合条件的单元格进行计数。指定区域是对单元格进行计数的区域。条件的形式可以是数字、表达式或文本等。"*"是通配符，代表任意多个字符的文本。
由于EXCEL中超过15位的数字只能保留15位有效数字，后面全部视为0，所以“3771000100001197”和“3771000100001192”会被认为是重复值，因为它们都被当成了数字“3771000100001190”。这里使用 &"*"将单元格数字后面统一添加*符号强行当做文本进行识别统计，就可以准确地通过计数值是否大于1识别出数字是否重复。
需要注意的是，该条件格式应用的区域必须从A2开始，同时由于应用的是整列单元格所以COUNTIF指定区域必须加绝对应用，而A2则为相对引用。
不过这个公式存在一个缺陷，就是当所在区域里有空格的时候，也会自动识别为重复。
这是由于当单元格为空,如上图A9，A9&"*"的结果为"*"，公式就变成在$A$2:$A$547区域中查找"*"，*作为通配符代表任意一个字符，所以使用COUNTIF会统计出所有不为空的单元格的计数，当然结果会大于1 ，被标注为重复。
那有没有什么方法可以屏蔽空格呢？我们在原有公式的基础上乘以A2""，把条件格式的公式修改成“=(COUNTIF($A$2:$A$547,A2&"*")*(A2"")>1)”。若单元格为空，A2""返回FALSE，原有结果跟逻辑值FALSE相乘等于0，0不大于1，自然不会被标注为重复：
2、SUMPRODUCT标注重复
选中要判断重复项的数据区域A2:A547, 点击【开始】选项卡下的【条件格式】下拉菜单的“新建规则”。
在【新建格式规则】窗口 选择“使用公式确定要设置格式的单元格”，在下方的“编辑规则说明”里输入公式“=SUMPRODUCT(($A$2:$A$547=A2)*(A2""))>1”，点击“格式”，选择填充色为蓝色。点击确定。
完成结果如下：
解析：
=SUMPRODUCT(($A$2:$A$547=A2)*(A2""))>1
SUMPRODUCT返回多个参数乘积之和，参数可以是数组或者对应的区域。第一个参数是$A$2:$A$547=A2，表示从A2到A547如果等于A2返回TRUE否则返回FALSE，第二个参数是A2""，同样为空则返回FALSE否则返回TRUE，而TRUE代表1，FALSE代表0，多项乘积之和就是最后得到的该单元格在该区域的重复个数。
同样该条件格式应用的区域从A2开始，同时由于应用的是整列单元格所以$A$2:$A$547必须加绝对应用，而A2则为相对引用。
这就是EXCEL中的真假重复问题，不知道的小伙伴们很容易被系统所迷惑，觉得有用的话赶紧收藏起来吧！
小编的疑惑：为何数字超过15位后用COUNTIF出现假重复，而用SUMPRODUCT没有出现？两个函数，都是建立在$A$2:$A$547的值是否等于A2的判断上进行的，为何COUNTIF会出现假重复，而SUMPRODUCT不会出现假重复？
＊＊＊＊部落窝教育－excel长数值的有效位数＊＊＊＊
原创：夏雪/部落窝教育（未经同意，请勿转载）
更多教程：部落窝教育（www.itblw.com）
微信公众号：exceljiaocheng
版权声明：CosMeDna所有作品（图文、音视频）均由用户自行上传分享，仅供网友学习交流。若您的权利被侵害，请联系删除！
本文链接：//www.cosmedna.com/article/228448783.html
