想不到,COUNTIF的结果竟然不正确?
Excel在处理长数字组成的文本的统计时会出错,本文以countif为例进行讲解。实际上,这种错误在sumif,条件格式中都会出现。
01
错误的公式结果
02
原因分析
我们知道,在Excel中,身份证号必须以文本形式存放。所以,在使用COUNTIF时,想当然认为Excel是按照文本比较。其实不然,COUNTIF自动把这些由全数字组成的文本处理成了数值,而在Excel中,数值只有15位精度,那些超过15位的数值,后面部分都会变成0。
我们看郭靖和杨康的身份证号,前面15位都是一样的,只有后面才有区别。但是由于Excel的处理方式,这个区别被抹掉了。在Countif看来,他们两个的身份证号都是“330100124004011000”,所以计数结果是“6"。
03
解决方法
解决方法有两个,一个是修改这个COUTIF公式:
我们将原来的公式中的第二个参数F3,修改为F3 & "*",这样就将这个参数强制转换为文本,在比较时就不会转换为数字再进行比较了。
第二个方法是不使用COUNTIF了,我们改用SUMPRODUCT函数。
第三个方法就是使用数据透视表。
如果我们不用COUNTIF函数,而是使用数据透视表来完成这个统计,就根本不存在这个问题。
04
扩展分析
条件格式也把这两个身份证当成一样的了。
正确方法如下:
依次点击“条件格式”,“新建规则”,“使用公式确定要设置格式的单元格”,在公式框中输入公式:
=COUNTIF($C$3:$C$14,C3&"*")>1
然后点击确定。
完整的演示看下面的动图:
好啦,今天的分享就到这里了!如果你有感兴趣的问题希望了解,可以在下面留言,可能很快就可以看到你的问题的解决方法了。
点个赞
再走吧
赞 (0)