SUMPRODUCT函数三大经典案例 图文

2019年8月12日14:35:34 评论 631 views

SUMPRODUCT函数三大经典案例

关键词:条件计数,条件求和

SUMPRODUCT函数案例:

假设下方是某公司大区业务员在各月的销售记录表格,其中A列是业务销售月份,B列是业务所属大区,C列是业务员姓名,D列是业务员的工资。

SUMPRODUCT函数三大经典案例

案例

案例1:百变小樱共领取几次工资?

我们先要确定题中的两个条件,一个是百变小樱,一个是工资出现次数。由此可知,这是一道单条件计数问题,通常我们都是用countif函数,那如何运用Sumproduct单条件计数呢,如下所示:

SUMPRODUCT函数三大经典案例

案例1

案例1解析:=SUMPRODUCT(($C$2:$C$7="百变小樱")*1)

首先我们判断$C$2:$C$7是否是“百变小樱”,如果是则返回逻辑值TRUE,不是则返回逻辑值FALSE,此时 $C$2:$C$7="百变小樱" 计算后结果为:{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE};因SUMPRODUCT函数的特性是将非数值数组元素作为0处理,故此时我们需要将其在后方 *1 ,将逻辑值转化为数值,则为{0;1;0;0;0;1},然后SUMPRODUCT函数计算其乘积和,结果为2。

此处有个小知识点:当逻辑值(TRUE、FALSE)参与计算时会转化为数值。TRUE=1,FALSE=0,TRUE*TRUE=1,TRUE*FALSE=FALSE*TRUE=0,FALSE*FALSE=0

案例2:灰原哀共领取工资多少钱?

我们仍先确定题中的两个条件,一个是灰原哀,一个是共领取工资多少。由此可知,这是一道单条件求和问题,通常我们都是用sumif函数,那如何运用Sumproduct单条件求和呢,如下所示:

SUMPRODUCT函数三大经典案例

案例2

案例2解析:=SUMPRODUCT(($C$2:$C$7="灰原哀")*$D$2:$D$7)

首先我们判断$C$2:$C$7是否是“灰原哀”,如果是则返回逻辑值TRUE,不是则返回逻辑值FALSE,此时 $C$2:$C$7="灰原哀" 计算后结果为:{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE};SUMPRODUCT函数将其逻辑值与$D$2:$D$7的值相对应乘积求和,则为{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}*{5923;5463;10123;13393;9566;12641}= FALSE *5923+ FALSE *5463+ TRUE *10123+FALSE*13393+ TRUE *9566+FALSE*12641=0+0+10123+0+9566+0,求出结果为19689元。

案例3:3月份华北区和华南区共发放工资多少钱?

我们仍先确定题中的三个条件,一个是3月份,一个是华北区和华南区,还有一个条件是共发放工资的钱数。在前面案例中我们学习了在SUMPRODUCT函数中计算单条件求和,此题中我们遇到了多个大区的多条件求和那如何计算出两大区的3月份发放工资呢?如下所示:

SUMPRODUCT函数三大经典案例

案例3

案例3解析:

方法1:=SUMPRODUCT(($A$2:$A$7="3月")*($B$2:$B$7="华北区"),$D$2:$D$7)+SUMPRODUCT(($A$2:$A$7="3月")*($B$2:$B$7="华东区"),$D$2:$D$7)

方法2:=SUMPRODUCT(($A$2:$A$7="3月")*($B$2:$B$7={"华北区","华东区"})*$D$2:$D$7)

方法1中首先判断$A$2:$A$7是否是3月, $B$2:$B$7是否是华北区,然后与求和区域相对应成绩求和 + 判断$A$2:$A$7是否是3月, $B$2:$B$7是否是华东区,然后与求和区域相对应成绩求和 ,求出答案为28687元。

上述公式是SUMPRODUCT函数最经典常见的用法:=SUMPRODUCT((条件1)*(条件2)……,求和区域)

但是,如果只有两个大区时,我们可以这么写,如果计算五个大区,十个大区呢,如果还如方法1这样书写,不仅电脑的数据计算量加大,而且我们要套上又多又冗长的公式。此时,我们就可以使用另一个SUMPRODUCT函数的经典用法:

=SUMPRODUCT((条件1)*( 条件区域={“条件,条件…”})*求和区域)

即方法2中的答案,求出答案为28687元。

课后划重点:

① SUMPRODUCT函数解析:

SUM在函数中是求和,PRODUCT在函数中是乘积,SUMPRODUCT函数的意义为乘积之和,公式即:SUMPRODUCT(array1,array2,array3,...),其中array1,array2,array3,...为数组,将需要的各数组进行相乘并求和。

② SUMPRODUCT函数数组参数必须具有相同维数;

③ SUMPRODUCT函数将非数值型数组元素作为0处理;

④ SUMPRODUCT函数数据区域不能整列引用;

⑤ SUMPRODUCT函数在运用时,数据中不能出现错误值#N/A,否则公式返回值为错误值#N/A。

SUMPRODUCT函数的三大经典案例,小伙伴er萌,你萌学斐了嘛~

  • 微信扫码免费学习
  • 免费学习48小时
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin

发表评论

您必须才能发表评论!