Excel改写SUMPRODUCT函数公式所引用的单元格数据发生变化，缩短简化下列公式所引用的单元格数据发生变化，且保持原有全部功能？

来源：蜘蛛抓取(WebSpider) 时间：2023-03-17 06:45 标签： 如何一次性在所有单元格中输入内容

关键词：条件计数，条件求和
SUMPRODUCT函数案例：
假设下方是某公司大区业务员在各月的销售记录表格，其中A列是业务销售月份，B列是业务所属大区，C列是业务员姓名，D列是业务员的工资。
先来个灵魂三连问!
案例1：百变小樱共领取几次工资？
案例2：灰原哀共领取工资多少钱？
案例3：3月份华北区和华南区共发放工资多少钱？
以上问题是否可以轻松愉快的解答，如果不可以就跟着小编进入Sumproduct条件求和基础到进阶的大门吧~此处不仅有答案，更有超细致的详解和课后知识点哦~
案例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元。
课后划重点：
① SUMPRODUCT函数解析：
SUM在函数中是求和，PRODUCT在函数中是乘积，SUMPRODUCT函数的意义为乘积之和，公式即：SUMPRODUCT（array1,array2,array3,…）,其中array1,array2,array3,…为数组，将需要的各数组进行相乘并求和。
② SUMPRODUCT函数数组参数必须具有相同维数；
③ SUMPRODUCT函数将非数值型数组元素作为0处理；
④ SUMPRODUCT函数数据区域不能整列引用;
⑤ SUMPRODUCT函数在运用时，数据中不能出现错误值#N/A，否则公式返回值为错误值#N/A。
SUMPRODUCT函数的三大经典案例，小伙伴er萌，你萌学斐了嘛~

我要回帖

更多关于 如何一次性在所有单元格中输入内容 的文章

 

随机推荐