返回目录:excel表格制作
熟练运用excel函数公式是提高办公效率、增强业务解决能力的重要方法,也是提高职场竞争力的有效途径。本号精选62个excel常用函数,结合示例详细解析功能和用法,助力提高函数应用水平,欢迎关注收藏。
第五篇:COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS函数
COUNTIF函数
功能:得到符合条件的数据个数
语法: COUNTIF(range,cri)
说明: COUNTIF函数对指定单元格区域中符合指定条件的单元格进行计数的函数,第一个参数range只能是单元格区域不能是数组,cri是以数字、表达式或文本形式指定的条件。是日常工作中使用频率非常高的一个函数。
示例:
1、得到小于60的单元格数量:COUNTIF(RANGE,"<60")
2、得到小于平均值的单元格数量:COUNTIF(RANGE,"<"&AVERAGE(RANGE))
3、得到以AB开头字符的单元格数量:COUNTIF(RANGE,"AB*")
4、得到包含AB字符的单元格数量:COUNTIF(RANGE,"*AB*")
5、得到以AB结尾字符的单元格数量:COUNTIF(ARNGE,"*AB")
6、得到包含文本内容的单元格数量:COUNTIF(RANGE,"*"),此时若单元格内有公式,但公式生成的结果的为空格,也会被统计进去,与COUTNA(RANGE)结果相同。有时候会造成所见与所得不相符的感觉。可用下面的公式。
7、得到文本单元格数量,不包含数值和空格:COUNTIF(RANGE,"><"),这个用法比较少见,但用在动态调整数据有效性下拉选项方面效果挺好。如下案例:
上图是工程行业招投标常用的现场演示表,其中J、K列为辅助列,通过在A列空白单元格右侧点击下拉三角符号,用来选取还未被选过的投标人(即K列)。需要实现当剩余如图中仅有三家单位还未登记到A列时,在点击下拉进行选择时,只出现这三家单位名称的弹出框。解决方法为:在设置A列数据有效性时,在来源处输入公式:=OFFSET($K$2,1,,COUNTIF($K$3:$K$9,"><"),1)
该处就用COUNTIF($K$3:$K$9,"><")得到K列剩余单位个数,单元格区域有公式但结果为空的不予统计进来,等同于COUNTA(K3:K9)-COUNTBLANK(K3:K9)。再配合OFFSET函数就实现了上图中只选择3家公司名称作为下拉选项的效果。
8、按两个条件求个数,如按分数区段统计80—90分的人数:
(1)上包型:大于80,小于等于90:=SUM(COUNTIF(RANGE,">"&{80,90})*{1,-1})
(2)下包型:大于等于80,小于90:=SUM(COUNTIF(RANGE,">="&{80,90})*{1,-1})
9、得到单元格区域中不重复数据个数:
=SUMPRODUCT(1/COUNTIF(RANGE,RANGE))
如下图:
即求出A列共有7门课。
10、特别提醒,COUNTIF函数只针对单元格区域求数,不能对过程中其他函数生成或直接在公式里输入的数组求数。而有许多函数比如SUMPRODUCT函数是数组或区域都可以针对求值的。如下图案例:
求统计C等(含)以上的次数,I3单元格公式输入:COUNTIF(CODE(E3:H3),"<=67")时,提醒不符合函数编写规则,就是因为该处COUNTIF第一个参数不是单元格区域,而是CODE函数生成的数组,导致的出错。公式修改为{=SUM(IF(IFERROR(CODE(E3:H3),100)<=67,1,0))}(数组函数),可得到正确结果。
COUNTIFS函数
功能: 统计多个区域中同时满足给定条件的单元格的个数。
语法: countifs(range1,cri1,range2,cri2,…)
说明:为countif函数的扩展。用法与countif类似,但countif针对单一条件,而countifs可以实现多个条件同时求结果。
示例:
案例公式为:=COUNTIFS(C3:C17,">60",D3:D17,">60",E3:E17,">60",B3:B17,"男")
COUNT函数
功能:得到参数中数字项的个数
语法:COUNT(val1,val2...)
说明:参数val1、val2...可以是数组也可以是引用单元格区域,只返回数组或单元格区域中的数字或能转化为数字的空值、 逻辑值 、日期或以文字代表的数计算进去,错误值或其他无法转化成数字的文字则被忽略,不报错。
示例:
图示I7单元格公式:=COUNT(C:C),求出C列中数字的个数为15。
J7单元格公式:=COUNTA(C:C),求出C列中文本个数为16。
K7单元格公式:=COUNTBLANK(L:L),因L列无内容,则求出表格系统最大行为1048576。
COUNTA函数
功能:得到参数列表中非空单元格个数
语法:COUNTA(val1,val2...)
说明:参数可以是 单元格区域或数组,结果与COUNTIF(RANGE,"*")相同。
示例:见COUNT函数示例。
COUNTBLANK函数
功能:得到单元格区域中空单元格个数
语法:COUNTBLANK(RANGE)
说明:改函数对单元格有公式但结果为空""的情况也统计在内,这点与COUNTA不同,与COUNTIF(RANGE,"")结果相同。