excel常用公式图解(学会这些公式可以早早下班)
有用,就点点关注!
COUNT家族有5个成员,在上篇已经介绍了其中的三个成员,今天将介绍另一个家庭成员——COUNTIF。
一、函数语法解析
1、函数定义:统计满足某个条件的单元格个数。
2、语法格式:COUNTIF(条件区域,条件)
3、参数说明:
条件的形式可以是数字、表达式或文本,也可以使用通配符。
通配符包括问号(?)和星号(*)。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号,请在该字符前键入波形符(~)。
二、函数实例
▲
01
经典用法
公式:=COUNTIF(A15:A23,"<>"),相当于COUNTA函数。
▲
03
计算空单元格的个数
公式:=COUNTIF(A29:A37,""),相当于COUNTBLANK函数。
▲
04
通配符的使用
1、姓名4个字以6结尾的个数
公式:=COUNTIF(A43:A49,"???6")
2、姓名以6开头的个数
公式:=COUNTIF(A43:A49,"6*")
3、姓名包含6的个数
公式:=COUNTIF(A43:A49,"*6*")
4、姓名包含~的个数
公式:=COUNTIF(A43:A49,"*~~*")
通配符包括问号(?)和星号(*)。问号匹配任意单个字符,星号匹配任意一串字符。如果要查找实际的问号、星号或波形符,请在该字符前键入波形符(~)。
▲
05
求销售额大于等于4000的平均值
在F55单元格输入公式:
=SUMIF(B55:E55,">=4000")/COUNTIF(B55:E55,">=4000"),向下填充。
SUMIF(B55:E55,">=4000")部分算出大于等于4000的销售额合计,COUNTIF(B55:E55,">=4000")部分算出大于等于4000的个数,两部分相除得到大于等于4000的平均销售额。
▲
06
姓名相同显示重复,否则为空
在D67单元格输入公式:
=IF(COUNTIF(A$67:A$73,A67)>1,"重复",""),向下填充。
COUNTIF(A$67:A$73,A67)部分算出A67单元格内容在区域A67:A73中的个数,用IF函数判断,和1比较,如果大于1,那就是重复的。
▲
07
超过15个字符的做法
判断银行卡号是否重复
在D79单元格输入公式:
=IF(COUNTIF(B$79:B$85,B79&"*")>1,"重复",""),向下填充。
这题和上一题的解题思路一样,就是超过了15个字符的其条件要连接星号(如B79&"*"),否则会出错。
▲
08
求性名列不重复的个数
公式:=SUM(1/COUNTIF(A91:A99,A91:A99)),记得带上花括号哦!
或者:
=SUMPRODUCT(1/COUNTIF(A91:A99,A91:A99))
COUNTIF(A91:A99,A91:A99)部分统计出区域A91:A99中每个单元格内容在该区域中出现的个数,用1除以表示将重复数据都算作1个,然后用SUM或SUMPRODUCT函数求和就得到了总的不重复的个数。
▲
09
求成绩大于等于80小于90的个数
方法一:常规方法
公式:=COUNTIF(B105:B111,">=80")-COUNTIF(B105:B111,">=90")
用大于等于80的个数减去大于等于90的个数,就得到了大于等于80小于90的个数。
方法二:数组方法
公式:=SUM(COUNTIF(B105:B111,{">=80",">=90"})*{1,-1}),按Ctrl Shift Enter三键结束。
或者:
=SUMPRODUCT(COUNTIF(B105:B111,{">=80",">=90"})*{1,-1}),不需要三键结束。
方法三:数组方法
公式:
=SUMPRODUCT(COUNTIF(B105:B111,">="&{80,90})*{1,-1}),不需要三键结束。
或者:=SUM(COUNTIF(B105:B111,">="&{80,90})*{1,-1}),按Ctrl Shift Enter三键结束。
方法四:取巧法
公式:
=SUM(COUNTIF(B105:B111,ROW(80:89))),按Ctrl Shift Enter三键结束。
或者:
=SUMPRODUCT(COUNTIF(B105:B111,ROW(80:89))),不需要三键结束。
▲
10
中国式排名
公式:
=SUMPRODUCT((B$117:B$123>B117)/COUNTIF(B$117:B$123,B$117:B$123)) 1
或者:=SUM((B$117:B$123>=B117)*(1/COUNTIF(B$117:B$123,B$117:B$123))),按Ctrl Shift Enter三键结束。
爱上Excel合伙人
我们一直秉承简洁、优雅、高效的为读者分享工作中遇到的每一个Excel问题,不论是Excel技巧、函数、图表、VBA,甚至是有关于Excel的开发,只要你能提出来问题,我们总能给你一个满意的答案!
,
免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com