Excel中真假重复的一个简单处理

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个单元格的对比。