Excel Home
全球知名的Excel资源网站之一

COUNTIFS函数基础应用

基础语法

COUNTIFS函数的基础语法为:
COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)

其中,criteria_range代表要统计的条件区域,criteria代表要统计的参数,用来定义将对哪些单元格进行计数。

每一个criteria_range参数的区域都必须具有相同的行数和列数。这里要注意,它的参数是“成对”出现的。另外,COUNTIFS支持127对条件统计,这个知识点仅作了解即可,日常的工作不会用到这么复杂的条件。

分别对字符串、数字、日期进行统计

如图19-1所示,C11:F24单元格区域是基础数据源,C列为组别,D列为姓名,E列为销售日期,F列为销售金额,接下来对这部分数据做相应的统计。

图19-1数据源区域

1.案例:统计汉字

首先来统计一组的人数。换成Excel的语言,就可以翻译成C列有多少个单元格是“一组”。如图19-2所示,在I12单元格中输入公式:=COUNTIFS(C12:C24,”一组”)

图19-2统计汉字1

在统计汉字的时候,可以直接输入相应的文字,并在文字两侧加上英文状态下的双引号。这是统计一个组的情况,要想统计多个组,不用每个参数都手动输入,可以提前在单元格中输入相应的参数,如图19-3所示,H14:H16单元格区域是需要统计的组别信息,在I14单元格中输入以下公式并向下复制到I16单元格。
=COUNTIFS($C$12:$C$24,H14)

图19-3统计汉字2

它的计算过程:首先,引用H14单元格的值,将H14变为文本字符串”一组”;其次,公式变为“=COUNTIFS($C$12:$C$24,”一组”)”;最后,进一步完成统计。
这里再次提示,只要涉及公式复制,就一定要想到“图钉”的问题。以上就是最基础的COUNTIFS函数的统计。

 

2.案例:统计数字

条件统计函数不仅可以统计汉字,还可以统计数字。下面对数据源中F列的销售金额进行统计,分别统计“大于5000”“等于5000”“小于等于5000”的条件下各有多少人。在I19单元格中输入公式:=COUNTIFS($F$12:$F$24,”>”&5000)

可以看到数据源F列中有5个是大于5000的。在统计数字的时候,通过添加比较运算符统计数字的范围。注意一个细节,这里COUNTIFS的第2个参数使用的是”>”&5000,将比较运算符和数字两部分分开,中间用“胶水”(&)连接。那么,此处是否可以不用&,直接连在一起呢?

当然可以,公式可以写成“=COUNTIFS($F$12:$F$24,”>5000″)”,但是在函数公式初学阶段,连在一起写很容易出现错误,本节后面会讲到这个问题。如果碰到比较运算符号,还是建议将它与相应的参数分开写。

在I20单元格中输入以下公式,统计等于5000的人数:=COUNTIFS($F$12:$F$24,”=”&5000)

将比较运算符和参数分开写,当然在统计“等于”的时候可以将等号去掉,变为:
=COUNTIFS($F$12:$F$24,5000)

在I21单元格中输入以下公式,统计小于等于5000的人数:=COUNTIFS($F$12:$F$24,”<=”&5000)
计算结果如图19-4所示。

图19-4统计数字1

提示:并不是只有统计数字的时候可以使用比较运算符,统计汉字的时候也是可以的,如公式“=COUNTIFS(D12:D24,”>”&”徐庶”)”,返回结果为2,因为汉字一般是根据每个字的汉语拼音读法,按照26个英文字母的顺序从小到大排列的,数据源中比“徐庶”大的有“许褚”和“张飞”,所以结果为2。不过工作中很少会用到这种方式统计汉字。
做数字统计时,不仅可以直接在公式中输入条件,还可以将条件放在单元格中,然后直接引用,如图19-5所示,H23:H25单元格区域分别为“>5000”“5000”“<=5000”。在I23单元格中输入以下公式,并向下复制到I25单元格,完成相应的数据区间的统计。=COUNTIFS($F$12:$F$24,H23)

图19-5统计数字2

可以看到与之前的统计结果完全一致,这样做有一个好处,以后如果需要修改统计条件,可以不用修改公式,直接在H23:H25的相应单元格中修改即可,既直观又快捷。在实际工作中,也尽量把问题考虑全面,做到函数公式一步到位,以后只需在表格相应的参数区域修改就可以。
继续看一种统计数据的方式,如图19-6所示,在H27单元格中输入统计的分隔点,数字5000,然后还是分别统计“大于”“等于”“小于等于”三组数字。在I27单元格中输入公式:
=COUNTIFS($F$12:$F$24,”>”&H27)

图19-6统计数字3

注意观察,这就涉及之前埋的伏笔,为什么要求大家将比较运算符和参数分开写。很多人会将公式写成“=COUNTIFS($F$12:$F$24,”>H27″)”。公式乍一看,好像没问题,可是这个公式返回的结果为0。为什么呢?这就要说一下“活性”的问题了。

H27没有在双引号中,它保持了自己的“活性”,代表引用的是相应单元格,而一旦把它放在了双引号中,它就变成了一个“木乃伊”,不再具有“活性”。”>H27″统计的并不是大于H27单元格的那个数字5000,而是大于“H27”这3个字符的数据。在COUNTIFS的统计中,它先判断条件的数据类型,发现数据类型是文本,而F12:F24单元格区域中全都是数字,没有文本,所以结果为0。

我们使用函数是为了减少错误,所以在对函数尚不熟悉的情况下,把比较运算符和参数分开写,中间用“胶水”(&)粘在一起,这样能减少70%的错误。
继续完成另外两个统计,在I28单元格和I29单元格分别输入公式:=COUNTIFS($F$12:$F$24,”=”&H27)
=COUNTIFS($F$12:$F$24,”<=”&H27)

当统计修改为以3000为分隔点的时候,只需将H27单元格修改为3000,其他公式完全不用改动,就能完成工作,如图19-7所示。

图19-7修改参数

 

3.案例:统计日期

下面继续看统计日期的方式,为了方便查看页面,我们在C33:F46单元格区域建立相同的数据源,如图19-8所示。

图19-8数据源区域

统计销售日期在2016年2月的人数。先把公式写下来,再慢慢分析,如图19-9所示,在I33单元格中输入公式:=COUNTIFS($E$34:$E$46,”>=”&”2016-2-1″,$E$34:$E$46,”<“&”2016-3-1”)

图19-9统计日期1

这个函数需要注意以下几个方面。

(1)回顾下第10章讲的日期函数,日期和时间的本质就是数字。统计某一区间日期,就相当于统计两个数字之间的数量,于是用到了“掐头去尾”的方式。

(2)这种快速输入日期的方式,必须用英文状态下的双引号引起来,否则它不表示日期,而只是一个普通的数字减法。如果这种方式掌握不好,那就规规矩矩使用DATE函数,如DATE(2016,2,1),可以减少错误。

(3)这个数据源中的数据都是日期,不包含时间的部分,所以用“”>”&”2016-1-31″”“”<=”&”2016-2-29″”等不同的固定首、尾的方式都可以。但如果数据源中的数据含有时间,如“2016-1-3115:28”“2016-2-2909:07”等,则必须采用公式中“>=本月的1日<下个月1日”的日期方式,这样的统计是最准确的。就好像统计分数的时候,80分到90分之间为良,如果大家的得分都是整数,那么“>79”“<=89”等方式都可以,但当分数中包括79.5、89.5等小数的时候,只能用“>=80”“<90”来表达最准确的区间。

(4)COUNTIFS可以多次对同一区域进行引用。 有人问:“统计的公式太长了,可不可以用MONTH函数把日期的月份提取出来,然后用COUNTIFS函数统计其中有多少月份等于2?” 我们动手试一下就知道答案了。按照此方法写下公式“=COUNTIFS(MONTH(E34:E46),2)”,然后按下【Enter】键,系统出现了错误提示,如图19-10所示。 图19-10公式错误提示 公式的逻辑没有问题,那到底是哪里出错了呢? MONTH(E34:E46)的结果是{2;2;2;3;2;2;3;3;3;3;1;2;2},这是一个数组,而COUNTIFS中的第1个参数是criteria_range。注意,“range”的意思是一个区域,所以COUNTIFS的第1,3,5,7,…参数是不支持数组的,必须是区域,即必须是在Excel表格中画出来一片单元格区域。与它有相同要求的参数还有ref、reference。 有了第一个统计日期的基础,我们继续操作。统计的时候,不可能每一个月份都手动输入,更多的情况是在单元格中输入1月、2月、3月等内容,然后完成相应的统计,如图19-11所示。在I36单元格中输入以下公式,并向下复制到I38单元格:=COUNTIFS($E$34:$E$46,”>=”&DATE(2016,LEFTB(H36,2),1),$E$34:$E$46,”<“&DATE(2016,LEFTB(H36,2)+1,1))

图19-11统计日期2

公式看上去很长,我们分步解读。

公式LEFTB(H36,2)在7.5节中讲过的,从月份中提取左侧2个字节,于是只把数字提取出来,得到”1″,而这个空格并不影响DATE函数的计算。DATE(2016,”1″,1)返回结果“42370”,这个数字就相当于日期2016-1-1。最后使用COUNTIFS函数完成相应月份的统计。

在2003版本及以前,SUMPRODUCT函数是多条件统计的神器,因为SUMIF和COUNTIF在常规状态下只能完成单条件的统计,自2007版本有了“流氓三兄弟”后,SUMPRODUCT就几乎退出历史舞台了,因为计算效率太慢了。在第7篇我们就会讲解SUMPRODUCT函数。

点击图片,了解详情↑

赞(50)
未经允许不得转载:ExcelHome » COUNTIFS函数基础应用
分享到

关于我们联系我们
本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号

征信