|
你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天接着来聊countif函数。虽然它很好用，也深受小伙伴的喜爱，但是坑也不少，需小心避免。 今天就简单分享一下它坑在什么地方，具体的表现就是会区分数据类型。countif第2参数的格式（数据类型）会限定第1参数的统计范围。如果第2参数的数据类型是数值，countif只在第1参数的数值单元格中进行统计，而忽略其他类型的数据，比如文本、逻辑值、错误值等。简单来说，就是第1参数和第2参数的数据类型要匹配才会统计。当然这不是绝对的，需要自己慢慢总结。
下图是我列举的countif的一些用法，对理解区分数据类型很有用，而且还有一些统计真空和假空以及通配符的用法。 其中A2:A13是数据源，包含不同类型的数据，有数值，文本，逻辑值和错误值，以及真空和假空。A4单元格的6是通过公式产生，是文本型的数字。A6单元格是真空单元格，A8单元格是假空单元格，也是通过公式产生的，是一个空文本。
说一下=COUNTIF(A2:A13,"<9")这个公式（上图红框标记）的结果为什么是3。通常我们认为小于9的个数是4，因为有5，4，6，7四个数。但统计出来的结果就是3。因为第2参数是数值，只在第1参数的数值单元格中统计小于9的个数。在第1参数的区域中，数值单元格有A2、A3、A5三个单元格，所以只在这3个单元格中统计小于9的个数。而A4单元格的数据类型是文本，与第2参数的数据类型不匹配，所以把它忽略掉。下面来看几个案列吧。 在C3单元格输入公式=COUNTIF(B$3:B$12,">"&B3)+1，向下填充，发现结果都是1。正常来说，这个公式是没有问题的。那原因是什么呢？ 
原因是B列的成绩是文本型的数字。在公式编辑栏中选中countif的第2参数，按F9，结果是">63"。 这样第2参数的数据类型是数值，第1参数的数据类型是文本，不匹配。所以countif最后返回的结果是0，再加1就是1。当把B列的文本型数字转为数值的时候，结果就对了。
2.判断身份证号是否重复 首先要判断各身份证号出现的次数，如果大于1，就重复了；否则没有重复。在C18单元格输入公式=COUNTIF(B$18:B$27,B18)，向下填充，发现结果是不对的。蓝色的应该是1次，黄色的应该是2次，结果却都是3次。为什么？
在Excel中，数字的精度是15位，15位以后的数字都会显示为0。这也是为什么身份证号要以文本的形式录入。 如果两个身份证号的前15位是相同的，后三位是不同的，那么它们经countif的运算都会被转化为XXXXXXXXXXXXXXX000，这样它们就变成相同的了。 正确的公式为=COUNTIF(B$18:B$27,B18&"*")，将第2参数的身份证号连接通配符*，强制转为文本，这样经countif的运算就不会将15位之后的数字变为0了。最后用if函数判断一下，如果countif的结果大于1，那么重复，否则不重复。当统计的区域或条件中包含*或?时，用countif的时候要小心了。在C32单元格输入公式=COUNTIF(B$32:B$36,B32)，向下填充。发现结果又㕛叒叕出错了。实际每个型号的数量都是1，20*30的型号却算出3个。为什么？ 
实际上面的公式就是=COUNTIF(B$32:B$36,"20*30")，第2参数的条件是以20开头30结尾的文本字符串，在B32:B36这个区域中符合条件的确实有3个。剩下的2个如上图红色框所示。 正确的公式为=COUNTIF(B$32:B$36,SUBSTITUTE(B32,"*","~*"))，先用substitute函数将*替换为~*，然后再用countif函数统计。*代表通配符，~*代表*本身。（你让*代表通配符，那谁来代表*本身呢，用~*）。下图左表是某公司员工的信息表，尽管各部门是排列在一起的，但为了看的更明显，需要用条件格式标出不同部门的数据，结果如右表所示。由于时间关系，今天不能详细说明了，想学习的可以下载文件查看。https://pan.baidu.com/s/1h9jY3GhCNC7fhjTs8vtOOw
|
