Excel是我们常见的办公软件Office的三驾马车之一，另外两架自然是Word与PowerPoint了。日常办公中，基本上都离不开这3个软件的支持了。方便了工作，也方便了生活，但今天小A遇到了一个小小的问题，就是EXCEL中可能存在的真假重复项的问题。毕竟大家使用Excel的时候经常会使用条件格式中突出显示单元格规则的重复值，来让重复的项目突显出来，避免重复或者进行计数。这个问题是Excel本身设计上的一个问题，但也是比较容易解决的。

如图则是使用的时候想通过突显单元格的方式找到重复的工单项，但是明显可以看到，第1项与第6项被Excel自动标记为重复项了。而实际上，这2个单元格，肉眼可见，最后的几位数字并不一样，显眼并非重复的。那为什么Excel会自动标记为重复呢？


图1.Excel表格中出现的重复项

原来Excel对于数字格式，默认只比对前15位，从第16位开始，全部视同为0。这样子，只要前面15位数字相同的，Excel就当成了重复项了。这是Excel精度上的设定，暂时不能通过设置来改变，所以得想其他的办法来解决。


图2.验证Excel对非数字的重复项判断

初步验证，该“假重复”只会对全是数字的单元格，或者以文本方式显示的全数字单元格判定重复项的时候出现。一旦其中加了其他字符则不会出现。

这15位跟科学计数法是一样的，精度在15位数字上了，推测可能跟早期Excel版本支持精度有关。具体不提，先看看这个问题怎么来解决。

既然知道为什么会出现“假重复”，那么要解决就可以通过保持15位以上的精度来解决，使得Excel在自动比较的时候，不仅仅只看前面15位，而是将整个单元格对比。

方式一、通过辅助列完成：

在原表格右侧E列，新建公式为：=D2&”_” ，其中的_ 可以替换成其他任意的字符。这个公式的简单含义就是，将左侧的单元格跟_这个符号进行合并得到新的单元格，再使用重复项的判断。明显，已经没有了重复项。用完之后，再把E列删除。


图3.使用辅助列方式判断是否有重复项

方式二：使用条件格式中其他规则

上面的方法一，一方面是需要占用辅助列，另外一方面如果需要真正的一直突显重复项，因为某些时候确实会存在重复项的情况，如果把辅助列删除，表格到了别人手上，又什么都没有了。所以，需要另外一种方式，继续在单元格上进行突显，而不借助其他资源。

通过方式一，也知道了将单元格与一个固定的符号合并即可让Excel判断出真实的重复，那么我们就可以在规则中手动设定相应的公式，使得Excel按公式计算的结果判断。

选择-条件格式-突出显示单元格规则-其他规则，选择规则类型为使用公式确定要设置格式的单元格。

这里需要利用Excel的一个函数，countifs，通过在此列中查找该单元格出现的次数，如果大于1次，2次以上的话，则为重复项。另外函数countifs也是对数值型仅比对前15位的，所以也同样需要对单元格进行运算。


图4.自定义规则的设置

这里设定的公式为=COUNTIFS(D:D,D1&”*”)>1，经测试，这里的”*”设定为其他字符如-，_，+等都无法生效，目测仅对*、?这2个通配符有效。所以务必需要注意下。因为公式经过运算，原单元格的数值会在后面加上这个符号，仅当为通配符的情况下，例如43000352019045710变成了43000352019045710*，这样相当于在D列查找43000352019045710 这个数字。因为*代表了任意字符，甚至为空。


图5.使用自定义规则判定的重复项情况

这里特意修改了第4、5列的数字使之真正重复，来判断公式的有效。

顺便提一下，EXACT函数是可以完完全全的比较2个单元格的字符是完全一致，不受15位数字的限制。但仅限于2个单元格的对比。

原文地址：https://www.iappi.cn/2019_06/2019827.html