COUNTIF函数和VLOOKUP函数使用频率很高,期间收到不少同学反馈的“BUG”!之前有同学跟我反馈这么一个“BUG”!大家发现下面的问题了吗?编码10001 一个有3个,但是有一个数值,还有两个是文本格式的(左上角'绿帽’),一般来说,我们认为他们是相同的情况,这个统计的就是对的!
但是问题就是,统计需要把他们分开统计,也就是正确的结果是1那是什么原因导致的,我们继续看下面这个图,我们会发现结果的“异常”!
其实本质就是COUNTIF他统计时是无法区分文本型数值和数值型的,统一转成数值统计,也就是23和000023是一样的!
那么如何解决这个问题呢?我们推荐大家使用更加严格的SUMPRODUCT方式处理!等于判断是区分文本类型和数值型数值的!
=SUMPRODUCT(N(A2:A15=D2))
在工作中,我们经常使用身份证号来统计人数,因为身份证号的唯一性,但是也是经常出现“BUG”,新手最容易掉坑~
我们经常通过下面这种方式来判断是否重复统计问题,但是下面两个身份证号明明是不同的,为什么统计有两次呢?
其实也是一样的道理,COUNTIF统计会把文本型数值强制换成数值,由于Excel中支持的数值长度为15位,超过部分显示为0,所以上面张三和李四,前面15相同,后面三位变成了0,所以他们就是完全一样的!在实务中,我们有一个简单的解决办法,就是把统计统计强制转为文本,这样就不会出现文本型数值的转换!
一般使用上面的写法基本可以解决身份证号的统计,但是这种写法是不准确了,只是适用特定的情况!
因为本质“*”是通配符,也就是表示一个或者多个字符串,那么只要前面部分相同的都会统计进来!
“婉儿”的身份证号和张三值相差一位,但是有统计错误,前面部分都记录相同且最后多写了1位,他们明显是不同的,但是 COUNTIF(B:B,B2&'*') 也统计进去了所以最为稳妥的方式还是使用SUMPRODUDCT函数!=SUMPRODUCT(N($B$2:$B$4=B2))
我们做一下总结,COUNTIF统计的“BUG”如何处理!1、统计条件是数值的情况,谨慎使用COUNTIF!COUNTIF统计强制把文本型数值转为数值型,也是无法区分文本型和数值型数值!
2、身份证号统计,如果长度相同,可以使用 条件&“*” 把条件转成文本,防止转数值,一般结果OK3、数值统计最为稳妥的选择SUMPRODCT,使用等号比较区分文本型和数值型数值!