真没想到！如果不是看了这篇文章，小编绝想不到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位的数据如何判断重复值呢？接下来给大家介绍几个使用公式的条件格式来解决这个问题。

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，自然不会被标注为重复：

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不会出现假重复？

欢迎加入QQ群：264539405交流学习下载素材。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

快速统计不重复数《1分钟搞定不重复数统计》

5个查重复的案例《countif函数的使用方法以及countif函数查重复等5个案例分享》