关键词:条件计数,条件求和 SUMPRODUCT函数案例: 假设下方是某公司大区业务员在各月的销售记录表格,其中A列是业务销售月份,B列是业务所属大区,C列是业务员姓名,D列是业务员的工资。 案例1:百变小樱共领取几次工资? 我们先要确定题中的两个条件,一个是百变小樱,一个是工资出现次数。由此可知,这是一道单条件计数问题,通常我们都是用countif函数,那如何运用Sumproduct单条件计数呢,如下所示: 案例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单条件求和呢,如下所示: 案例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月份发放工资呢?如下所示: 案例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元。 Excel800出书啦,欢迎各位支持!!! 课后划重点: ① SUMPRODUCT函数解析: SUM在函数中是求和,PRODUCT在函数中是乘积,SUMPRODUCT函数的意义为乘积之和,公式即:SUMPRODUCT(array1,array2,array3,...),其中array1,array2,array3,...为数组,将需要的各数组进行相乘并求和。 ② SUMPRODUCT函数数组参数必须具有相同维数; ③ SUMPRODUCT函数将非数值型数组元素作为0处理; ④ SUMPRODUCT函数数据区域不能整列引用; ⑤ SUMPRODUCT函数在运用时,数据中不能出现错误值#N/A,否则公式返回值为错误值#N/A。 |
|
来自: 5jia5 > 《Excle & VBA》