函数COUNT家族讲解及应用

函数COUNT家族有五兄弟,函数COUNT、COUNTA、COUNTBLANK、COUNTIF和COUNTIFS。

一、COUNT、COUNTA、COUNTBLANK

函数COUNT:计算区域中包含数字的单元格的个数。
函数COUNTA:计算区域中非空单元格的个数。
函数COUNTBLANK:计算区域中空单元格的个数。
1、计算包含数字的单元格的个数 函数COUNT家族讲解及应用 输入公式:=COUNT(A2:A10) 英文字母、文本型数字、空白单元格、空文本、文本、错误值、逻辑值都不计算在内。
2、计算非空单元格的个数 函数COUNT家族讲解及应用 输入公式:=COUNTA(A2:A10) 参数值可以是任何类型,包括空文本(“”),但不包括空白单元格,空白单元格不是非空单元格,所以结果返回8。
3、计算空单元格的个数 函数COUNT家族讲解及应用 输入公式:=COUNTBLANK(A2:A10) 空白单元格和空文本(””)会被计算在内。
4、计算业绩大于等于8000的个数 函数COUNT家族讲解及应用 输入公式: =COUNT(0^(B2:B8>=8000)) 数组公式,按<Ctrl+Shift+Enter>三键结束。
(B2:B8>=8000)部分条件成立返回逻辑值TRUE,条件不成立返回逻辑值FALSE,该部分返回{FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}; 发生四则运算时,TRUE相当于1,FALSE相当于0,运用0的任何次方等于0,0的1次方返回错误值的特性,0^(B2:B8>=8000)部分返回{#NUM!;0;#NUM!;#NUM!;0;0;#NUM!}; 用函数COUNT统计,错误值不计算在内,只统计有数字的单元格个数,结果返回3。
5、合并单元格填充序号 函数COUNT家族讲解及应用 选中单元格区域A2:A9 输入公式:=COUNTA(A$1:A1) 按<Ctrl+Enter>键结束,注意单元格的引用方式 函数COUNTA计算当前公式单元格之前的非空单元格个数。 函数COUNT家族讲解及应用6、计算缺勤率
没有上班打卡记录的为缺勤,现在要计算缺勤率,给怎么做呢? 函数COUNT家族讲解及应用 输入公式: =COUNTBLANK(B2:B8)/COUNTA(A2:A8) COUNTBLANK(B2:B8)部分计算出缺勤人数3; COUNTA(A2:A8)部分计算出总人数7; 两者相除,缺勤人数3/总人数7,缺勤率为42.86%。 COUNTIF 函数COUNTIF:统计某个区域中满足给定条件的单元格数量。

COUNTIF(条件区域,条件)

1、统计空单元格的个数
函数COUNT家族讲解及应用
输入公式:
=COUNTIF(B2:B9,””)
函数COUNTIF的这种用法相当于函数COUNTBLANK。
2、统计非空单元格的个数
函数COUNT家族讲解及应用输入公式:
=COUNTIF(B12:B19,”<>”)
函数COUNTIF的这种用法相当于函数COUNTA。
3、统计销售额大于等于10000的个数
函数COUNT家族讲解及应用输入公式:
=COUNTIF(D22:D28,”>=10000″)
4、统计除“雨夜”之外的业务员个数
函数COUNT家族讲解及应用输入公式:
=COUNTIF(C31:C37,”<>雨夜”)
5、统计“产品A”的出现次数
函数COUNT家族讲解及应用输入公式:
=COUNTIF(B40:B46,”产品A”)
6、使用通配符
统计业务员以“小”开头的个数:函数COUNT家族讲解及应用输入公式:
=COUNTIF(C49:C55,”小*”)
星号(*)匹配任意一串字符。
统计业务员为2个字的个数:
函数COUNT家族讲解及应用输入公式:
=COUNTIF(C49:C55,”??”)
问号(?)匹配任意单个字符,两个字符所以用”??”。
统计业务员中带“*”的个数:
函数COUNT家族讲解及应用输入公式:
=COUNTIF(C49:C55,”*~**”)
为什么第二参数是”*~**”呢?
“*”作为通配符,在查找其本身时需要在前面加“~”,那就是“~*”
包含“*”,那“*”可能在前面,可能在后面,也可能在中间,字符数不确定,星号(*)匹配任意一串字符,所以是”*~**”。
7、统计在销售额[10000,15000]之间的个数
函数COUNT家族讲解及应用输入公式:
=COUNTIF(D58:D64,”>=10000″)-COUNTIF(D58:D64,”>15000″)
COUNTIF(D58:D64,”>=10000″)部分算出销售额大于等于10000的个数4;
COUNTIF(D58:D64,”>15000″)部分算出销售额大于15000的个数1;
前者减后者就是大于等于10000、小于等于15000的个数,即4减1返回3。
常量数组的方法:
函数COUNT家族讲解及应用输入公式:
=SUM(COUNTIF(D58:D64,{“>=10000″,”>15000″})*{1,-1})
COUNTIF(D58:D64,{“>=10000″,”>15000″})部分统计出的是销售额大于等于10000和大于15000的个数,返回结果{4,1};
需要用大于等于10000的个数减去小于等于15000的个数;
用*{1,-1}来解决,{4,1}*{1,-1}返回{4,-1};
用函数SUM求和即返回3。
也可以用函数ROW的方法来构造序列:
函数COUNT家族讲解及应用输入公式:
=SUM(COUNTIF(D58:D64,ROW(10000:15000)))
注意是数组公式,要按<Ctrl+Shift+Enter>三键结束。
8、判断银行卡号是否重复
函数COUNT家族讲解及应用输入公式:
=IF(COUNTIF(A$67:A$73,A67&”*”)>1,”重复”,””)
COUNTIF(A$67:A$73,A67&”*”)部分统计A列每个银行卡号在区域A67:A73中出现的次数;
因为银行卡号超15位数了,15位数后都以0显示,即使是文本格式,当函数COUNTIF进行计算时,文本型数字也会被当成数值来处理,所以&”*”将其强行识别成文本;
将COUNTIF部分与1进行比较,大于1就是重复的;
用函数IF来判断,如果COUNTIF部分大于1条件成立,就返回”重复”,否则返回空。
9、统计不重复的产品个数
函数COUNT家族讲解及应用输入公式:
=SUMPRODUCT(1/COUNTIF(B76:B82,B76:B82))
COUNTIF(B76:B82,B76:B82)部分统计出区域B76:B82中每个单元格内容在该区域中出现的个数,返回{4;4;4;3;4;3;3}
用1除以表示将重复数据都算作1个,1/{4;4;4;3;4;3;3}返回
函数COUNT家族讲解及应用然后用函数SUMPRODUCT求和,统计出不重复的产品个数。
10、中式排名
函数COUNT家族讲解及应用输入公式:
=SUMPRODUCT((D$85:D$91>D85)/COUNTIF(D$85:D$91,D$85:D$91))+1

COUNTIFS

函数COUNTIFS不仅能够单条件计数,还能够多条件计数。
函数COUNTIFS:对满足多个条件的单元格计数。
COUNTIFS(条件区域1,条件1,……,条件区域n,条件n)
1、统计空单元格的个数
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(B2:B9,””)
2、统计非空单元格的个数
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(B12:B19,”<>”)
3、统计销售额大于等于10000的个数
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(D22:D28,”>=10000″)
条件”>=10000″也可以写成”>=”&10000
函数COUNT家族讲解及应用4、统计除“雨夜”之外的业务员个数
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(C31:C37,”<>雨夜”)
5、统计在销售额[10000,15000]之间的个数
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(D40:D46,”>=10000″,D40:D46,”<=15000″)
6、统计销售一部大于等于10000的销售额之和
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(A49:A55,”销售一部”,D49:D55,”>=10000″)
7、通配符的使用
统计业务员为3个字的个数:
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(C58:C64,”???”)
问号(?)匹配任意单个字符。
统计业务员以“小”开头的个数:
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(C58:C64,”小*”)
星号(*)匹配任意一串字符。
统计包含*的业务员的个数:
函数COUNT家族讲解及应用
输入公式:
=COUNTIFS(C58:C64,”*~**”)
星号(*)作为通配符,在查找包含其本身时,需要在前面加~。
8、统计销售一部大于平均销售额的个数
函数COUNT家族讲解及应用输入公式:
=COUNTIFS(A67:A73,”销售一部”,D67:D73,”>”&AVERAGE(D67:D73))
用函数AVERAGE算出平均销售额,作为COUNTIFS的条件。
9、数组常量用法
函数COUNT家族讲解及应用输入公式:
=SUMPRODUCT(COUNTIFS(A76:A84,{“销售一部”;”销售三部”},B76:B84,{“产品A”;”产品B”}))
COUNTIFS(A76:A84,{“销售一部”;”销售三部”},B76:B84,{“产品A”;”产品B”})部分返回的是销售一部产品A的次数2,销售三部产品B的次数3,即{2;3};
用函数SUMPRODUCT求和返回5。

分享到:

评论抢沙发

评论前必须登录!