想不到,COUNTIF的结果竟然不正确?

Excel在处理长数字组成的文本的统计时会出错,本文以countif为例进行讲解。实际上,这种错误在sumif,条件格式中都会出现。

01

错误的公式结果

下图展示了四个人的登录数据,我们需要统计每个人的登录次数,统计用的是COUNTIF函数。为了避免重复,我们没有使用姓名作为条件,而是使用了身份证号。
可以看到,公式本身没有错误,而且就张三丰和乔峰来说,结果是正确的。只有郭靖和杨康的计算结果是错误的。

02

原因分析

造成这个现象的原因是COUNTIF的条件比较方式造成的。

我们知道,在Excel中,身份证号必须以文本形式存放。所以,在使用COUNTIF时,想当然认为Excel是按照文本比较。其实不然,COUNTIF自动把这些由全数字组成的文本处理成了数值,而在Excel中,数值只有15位精度,那些超过15位的数值,后面部分都会变成0。

我们看郭靖和杨康的身份证号,前面15位都是一样的,只有后面才有区别。但是由于Excel的处理方式,这个区别被抹掉了。在Countif看来,他们两个的身份证号都是“330100124004011000”,所以计数结果是“6"。

根据这个分析,凡是长数字组成的文本都会有这个问题。身份证是一个常见的 例子,银行卡号,银行账号,全数字的合同编号都会有这个问题。

03

解决方法

解决方法有两个,一个是修改这个COUTIF公式:

我们将原来的公式中的第二个参数F3,修改为F3 & "*",这样就将这个参数强制转换为文本,在比较时就不会转换为数字再进行比较了。

第二个方法是不使用COUNTIF了,我们改用SUMPRODUCT函数。

第三个方法就是使用数据透视表。

如果我们不用COUNTIF函数,而是使用数据透视表来完成这个统计,就根本不存在这个问题。

04

扩展分析

这个错误当然也会在COUNTIFS,SUMIF,SUMIFS中出现。除此之外,这个错误在条件格式中也会出现。看下面的动图

条件格式也把这两个身份证当成一样的了。

正确方法如下:

依次点击“条件格式”,“新建规则”,“使用公式确定要设置格式的单元格”,在公式框中输入公式:

=COUNTIF($C$3:$C$14,C3&"*")>1

然后点击确定。

完整的演示看下面的动图:


好啦,今天的分享就到这里了!如果你有感兴趣的问题希望了解,可以在下面留言,可能很快就可以看到你的问题的解决方法了。

点个赞

再走吧

(0)

相关推荐