当前位置:首页 > 办公 > Excel教程 > 正文内容

COUNTIF与SUMPRODUCT函数过招!

酷网1个月前 (10-25)Excel教程29

关于使用分隔符号-会出错的问题,这个是去年无意间发现的,这点希望所有人记住。详见文章:这是我此生见过COUNTIF函数,最奇葩的错误!

COUNTIF和COUNTIFS函数很好用,SUMPRODUCT函数也不错。究竟谁更好用,一起来看看。

1.下面请看第一场比赛:如何统计值班经理的值班次数?

COUNTIF函数首先应战,在H2单元格输入公式,并向下填充。

=COUNTIF(A:A,G2)

COUNTIF函数语法:

=COUNTIF(条件区域,条件)

SUMPRODUCT函数也不甘示弱,在I2单元格输入公式,并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*1)

SUMPRODUCT函数单条件计数语法:

=SUMPRODUCT((条件1)*1)

或者

=SUMPRODUCT(–(条件1))

2.第一场比赛可谓势均力敌,不分胜负。下面请看第二场比赛:统计值班经理在中午时间段的值班次数。

两个条件?COUNTIF函数顿时傻眼了,多条件计数是COUNTIF函数心里永远的痛。然而,SUMPRODUCT函数却气定神闲,在H2单元格输入公式,并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*($B$2:$B$10=$H$1))

SUMPRODUCT函数多条件计数语法:

=SUMPRODUCT((条件1)*(条件2)*(条件n))

“打虎亲兄弟,上阵父子兵”,看到兄弟COUNTIF函数有难,擅长多条件计数的COUNTIFS函数果断出手了,在I2单元格输入公式,并向下填充。

=COUNTIFS(A:A,G2,B:B,$H$1)

COUNTIFS函数语法:

=COUNTIFS(条件区域1,条件1,条件区域2,条件2,条件区域n,条件n)

3.第二场比赛的结果大家有目共睹,胜利属于SUMPRODUCT函数。下面请看第三场比赛:值班经理都用了一个字作为自己的简称,如何根据简称统计值班次数?

SUMPRODUCT函数没有了之前的淡定从容,陷入了沉思中。而COUNTIF函数却露出了久违的笑容,它拿出了自己的绝活,在H2单元格输入公式,并向下填充。

=COUNTIF(A:A,”*”&G2&”*”)

在这里,“*”代表通配符,表示任意一个或者多个字符。在Excel函数中,能与通配符配合使用的函数并不多,COUNTIF函数是其中的一个,当然也包括了COUNTIFS函数,SUMIF函数,SUMIFS函数,VLOOKUP函数,MATCH函数等等。

SUMPRODUCT函数想破了脑袋,借助其他函数,终于也统计出来了。

=SUMPRODUCT(–ISNUMBER(FIND(G2,$A$2:$A$10)))

这个公式比较复杂,下面我们按步骤来分析。

Step 01 先看最里层的FIND函数, FIND函数的语法:

=FIND(查找的字符,查找的地方)

在I2,I3单元格分别输入公式:

=FIND(“风”,”风清扬”)

=FIND(“风”,”东方不败”)

在第一个公式中,因为字符“风”在字符串“风清扬”的第一个位置,所以结果返回1。而第二个公式中,因为字符“风”没有在字符串“东方不败”中,所以结果返回错误值。

Step 02 熟悉了FIND函数的基本运用后,我们在I2单元格输入公式:

=FIND(G2,$A$2:$A$10)

我们知道,在“A2:A10”区域中,存在了两个“风清扬”,按道理,字符“风”是能查找到的,应该返回数字才对啊,但是却返回错误值,这究竟是为什么呢?

FIND函数的第二个参数是一个区域,所以返回的结果是若干个数据,多个数据放在一个单元格中,当然会出错了。这个时候,我们需要借助一个神器:独孤九剑,也就是F9键。选择公式所在单元格,点击编辑栏,按F9键。

Step 03 带有红色方框的数字个数就代表了该值班经理的值班次数。那么怎么统计数字的个数呢?可以使用ISNUMBER函数,如果是数字就返回TRUE,否则就返回FALSE。

=ISNUMBER(FIND(G2,$A$2:$A$10))

Step 04 再结合SUMPRODUCT函数,结果便出来了,公式在上面已经给出。

第三场比赛,虽然SUMPRODUCT函数最后完成了任务,但评委的眼睛是雪亮的,这一次,评委把票投给了COUNTIF函数。

比赛的结果并不重要,重要的是,在什么时候该使用什么函数。怎么简单怎么来,作为这次比赛的吃瓜观众,你们说呢?

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40449.html

分享给朋友:

相关文章

excel筛选之(一)——数值筛选基础

excel筛选之(一)——数值筛选基础

将excel表中符合或者不符合条件的数据筛选出来,是日常工作应用用比较常见的应用,下面就来给大家系统介绍。 今天介绍的是筛选的最基础知识。 1、筛选指定某值 这是筛选中最简单的方式,比如,筛选销售量为10000的数据:...

LOOKUP函数实用终极帖

LOOKUP函数实用终极帖

在EXCEL的查找函数中,除了我们都知道的大名鼎鼎的VLOOKUP函数外,还有一个非常重要的函数–LOOKUP函数。它应用广泛,灵活性强,可以说是“三千宠爱在一身”。下面我们就来总结一下这个函数的一些主要用法...

excel2007调整表格行列宽度步骤方法

excel2007调整表格行列宽度步骤方法

  excel2007如何调整表格行列宽度?很多用户在调整excel表格行列宽度时,都是一行一列进行调整,不知道如何选择区域进行调整,下面小编小编就来和大家分享下excel2007调整...

Excel怎么改变单元格文字方向?

Excel怎么改变单元格文字方向?

  Excel怎么改变单元格文字方向?乍一听好像是左对齐右对齐?其实不是的,你可以歪着头想像一下旋转的文字,和那个差不多,我们接下来要例举的操作便是这整个过程,具体步骤请看下文。...

Excel中如何快速合并多个单元格

Excel中如何快速合并多个单元格

  Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。当我们制作表格中重复信息过多,通常会把重复信息的单元格进行合...

领导不喜欢看“间断”的excel排名,RANK换成SUMPRODUCT!

领导不喜欢看“间断”的excel排名,RANK换成SUMPRODUCT!

前几天了Excel RANK.EQ(RANK)总排名、SUMPRODUCT分类排名,有朋友说,RANK和RANK.EQ排出来的名次,如果有相同的数值会出现相同的排名,再继续排下去,会出现名次“间断”的情况,比如,两个第三...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。