Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

薛奔
薛奔
薛奔
15
文章
0
评论
2020-04-1802:05:00 评论 96 1473字
摘要

SUMPRODUCT的官方介绍:在给定的几组数组(array)中,将数组间对应的元素相乘,并返回乘积之和,即相乘之后再求和。为什么说这个函数多管闲事,同时又是函数中的璀璨星星,下面用几个实例来证明他的双重身份。

一. 本职工作

1.本职工作乘积求和在K2单元格中输入公式=SUMPRODUCT($D$2:$D$10,$E$2:$E$10),回车,得出结果12900,本职工作,乘积和。

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

2.本职工作延伸

(1)单条件乘积求和,求叫"老默"的员工总计拿多少钱,在K4单元格输入公式=SUMPRODUCT(($B$2:$B$10="老默")*($D$2:$D$10*$E$2:$E$10)),回车,得出老默总计的金钱4200.

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

(2)($D$2:$D$10*$E$2:$E$10)),回车,得出1950.

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

以上的3个实例,是SUMPRODUCT的本职工作,以及本职工作的延伸,后两个的应用,在官方的介绍中无。下面我们来说说SUMPRODUCT的璀璨之处。

二. 超级工作

1.不重复计数,求不重名的员工数量,在K8单元格输入公式=SUMPRODUCT(1/COUNTIF($B$2:$B$10,$B$2:$B$10)),回车,得出不重名人员数量4.

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

2.不排序,给同类项目中的数据进行排名,本实例中求各人员在数据中获得金额的排序,如"薛奔",在表中出现了3次,金额分别是1900/300/1300,在G2单元格中输入公式=SUMPRODUCT(($B$2:$B$10=$B2)*($F$2:$F$10>$F2))+1,向下复制公式,可以看到,各个同名人员,都按所获得的金额进行了排序

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

3.按条件,区域求和,求表中老默的总工时,在K12单元格输入=SUMPRODUCT(($A$15:$A$22="老默")*$B$15:$G$22),回车,得到老默4月1日——4月6日,总工时546小时。

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

以上不是SUMPRODUCT的全部超级工作,还有的应用等待你的探索,下面老默来说说SUMPRODUCT的不务正业,抢别人工作的应用。

三.多管闲事,抢工作

1.抢SUM的工作,求表中工时总和,在K2,输入=SUM($D$2:$D$10),得到258,同样在K2,输入=SUMPRODUCT($D$2:$D$10),也得得到258,这不抢了sum的工作。

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

2.抢COUNTIFS的工作,多条件计数,通过COUNTIFS的计算,可以算出部门"Excel成长课堂"中有几个叫"薛奔"的员工,的出结果为3(包含重复),同样在K6中输入=SUMPRODUCT(($A$2:$A$10="Excel成长课堂")*($B$2:$B$10="薛奔")),也可以得到结果为3,这又抢了COUNTIFS的工作了。

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

3.抢SUMIFS的工作,多条件求和,通过SUMIFS的计算,可以算出部门"Excel成长课堂"中叫"隔壁老王"的员工工时总计为65,同样,在K9输入=SUMPRODUCT(($A$2:$A$10="Excel成长课堂")*($B$2:$B$10="隔壁老王"),D2:D10),也可以得到结果65,这里又抢了SUMIFS的工作了。下面继续抢。

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

4. 抢COUNTA的工作,计数非空,通过COUNTA的,可以得出总员工数位9(包含重复),这时候,SUMPRODUCT又来了,在K12单元格中输入=SUMPRODUCT(($B$2:$B$10<>"")*($B$2:$B$10<>"")),又得到了结果9,又抢了别人的工作。

Excel技巧:最多管闲事的函数,同时又是函数中的璀璨星星

上面4个实例,SUMPRODUCT连续了抢了四位同仁的工作,不能在说下去了,再说下去,估计很多函数同仁都要给炒鱿鱼了。

SUMPRODUCT一个璀璨的星星,功能很强大,函数中的劳模,多管闲事的典范,值得大家的学习。

End.作者:Excel报表顾问来源:微博本文均已和作者授权,如转载请与作者联系。

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: