CONUTIF函数的这些坑一不留神就栽跟头

  小伙伴们好啊,又是我啊,我是您的老朋友流浪铁匠。我就问问有多少人用COUNTIF核对身份证号时出错了呢。

  除非看过公众号文章,新手经常会奇怪=COUNTIF(A:A,A2)这种对长数字编码(如身份证号)计数的结果为什么会异常。

  这涉及到COUNTIF这类IF后缀条件统计函数的特殊统计机制,而这类函数的统计机制之复杂坑死了一堆新手,因此借这机会详细介绍下。

  1,数字(日期)在EXCEL里有文本型和数值型两种,前者属于文本,后者才能直接统计计算。

  2,单元格格式和数据类型是两码事,后者不能用检查单元格格式方式检测而要用ISTEXT/ISNUMBER等函数检测出来,与单元格格式无关。

  第二参数内若内容是纯文本(不能为12这种能转为数值的纯数字文本)以及含有通配符时,统计时将忽略统计区域内的数值内容。但统计区域中的文本型数字会被统计。

  第2参数当以=作为比较运算符或者为纯字符串时,若内容为真数值/文本型数字。会把统计区域内的所有真数值/文本型数值(以及数字两端存在空格等情况)等全部识别为线参数数字的数据类型。

  当第2参数内有,等比较运算符时,若统计区域含真数值/文本型数字,只统计统计区域内的真数值部分,统计区域内的文本型数字将被忽略。此时需要使用 &* 方式强制识别为文本时才会对引用区域的文本型数字统计。

  而这就是COUNTIF这类函数统计身份证号等超过15位的纯数字编码出错的原因(直接=COUNTIF(A:A,A2)这种写法。

  统计时由于2参数无通配符等,1/2参数的数字编码全部被识别为数值,而EXCEL数值有15位精度限制,超过15位的数字会被全部识别为0。

  所以统计时第1和第2参数超过15位的数字编码会全部被COUNTIF识别为0计数。

  所以看完上面的机理介绍就应该知道为什么公式要改成=COUNTIF(A:A,A2&*)了

  因为&*后2参数就不是纯数字的比较了而被识别为文本,是文本的统计规则,此时忽略统计区域的数值,只统计文本 。

  另外注意这个函数的统计内容可以被识别为表达式的情况下请添加=& ,避免内容被识别为条件。

  如下图中,有一些带有比较符号的字符,现在要统计字符“4”的个数。

  COUNTIF函数有自身的统计机制,特别是由于新手不会分辨文本型数字和真数值,涉及数字的统计在不清楚这类函数的统计规则的前提下,就会经常掉进这类函数的陷阱里。

  通过本篇的介绍,小伙伴们可以了解COUNTIF函数的这些性质,避免在工作中被坑了。

TAG标签: 复杂函数
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。