国外做问卷网站好,公司工商查询,商业网站,自己做商务网站有什么利弊SUMPRODUCT函数是Excel中功能比较强大的一个函数#xff0c;可以实现sum,count等函数的功能#xff0c;也可以实现一些基础函数无法直接实现的功能#xff0c;常用来进行分类汇总#xff0c;分组排序等
SUMPRODUCT 函数基础
SUMPRODUCT函数先计算多个数组的元素之间的乘积…SUMPRODUCT函数是Excel中功能比较强大的一个函数可以实现sum,count等函数的功能也可以实现一些基础函数无法直接实现的功能常用来进行分类汇总分组排序等
SUMPRODUCT 函数基础
SUMPRODUCT函数先计算多个数组的元素之间的乘积再求和。SUMPRODUCT函数的语法为
SUMPRODUCT(array1,array2,array3, …)其中Array为数组用于指定包含构成计算对象的值的数组或单元格区域SUMPRODUCT函数的使用注意事项
(1).数组参数必须具有相同的维数即行数相同,否则,函数SUMPRODUCT将返回错误值#VALUE! (2).数据区域引用不能整列引用.如:A:A、B:B必须是A2:A100这种
SUMPRODUCT 函数用法
用法1乘积求和
如下数据需要统计公司整体的花费需要将数量乘以单价并汇总 这里使用SUMPRODUCT函数可以直接得到结果。输入公式SUMPRODUCT(C2:C8*D2:D8)或者SUMPRODUCT(C2:C8,D2:D8)即可得到总计花费
这里用到了两写方法第一个公式中用*乘号连接参数。第二个公式中用,逗号连接参数。本例中的数据源都是数值所以两种方法返回的结果一致。如果当数据源中包含文本数据值使用公式2仍然可以返回正确结果SUMPRODUCT将非数值型的元素作为0处理。但如果用公式1则会导致数值和文本相乘返回错误值#VALUE!。
乘积求和是SUMPRODUCT 函数最基础的用法也可以解释SUMPRODUCT 函数的原理即将选取区域的数组相乘再相加
用法2条件求和
SUMPRODUCT函数中加入逻辑判断即可以实现sumif、sumifs函数的功能条件求和。例如想要求市场部的总花费即单条件求和公式如下SUMPRODUCT((A2:A8市场部)*C2:C8*D2:D8) 其中A2:A8市场部即为对应的条件判断如果为市场部则返回结果为True对应值为1并与后续数组中的数量和单价相乘如果不为市场部则返回结果为False对应值为0与后续数组中的数量和单价相乘后返回的结果为0从而实现了条件求和。 当有多个条件求和时也可以按照同样的原理增加逻辑判断。例如求市场部笔记本总花费对应公式为SUMPRODUCT((A2:A8市场部)*(B2:B8笔记本)*C2:C8*D2:D8)
用法3:条件计数
条件计数的原理即为将SUMPRODUCT函数中的参数全部设为条件判断判断结果为True的返回数值1相加后即为满足条件的计数 例如求市场部对应的记录数量公式为SUMPRODUCT(N(A2:A8市场部))其中部门列是文本形式则需嵌套N函数表示返回转化为数值后的值从而统计对应的记录数。 如果需要对多条件进行计数例如统计市场部单价大于5的记录数则公式为SUMPRODUCT((A2:A8市场部)*(D2:D85))
用法4:分组排序
SUMPRODUCT函数也常用于分组排序例如需要将不同部门的商品单价排序则对应公式为SUMPRODUCT(($A$2:$A$8A2)*($D$2:$D$8D2)) 其中$A$2:$A$8A2表示条件区域列判断是否等于A2,返回对应的判断结果True和False。$D$2:$D$8D2判断单价区域列是否大于等于D2同样返回一组判断结果True和False通过SUMPRODUCT函数将两组数组相乘得到的就是部门列为市场部且单价大于等于10的相乘结果为1其他结果为0,再相加后即可得到整个分组中大于D2的记录数从而得到对应的排名。 省流版公式套路SUMPRODUCT((条件区域1条件1*(要进行排名的区域数值))1
用法5:多权重计算
上述几种用法中SUMPRODUCT 函数中的数组区域都是一列的列数组同样的一行的行数组也是可以的。例如下面需要根据笔试面试和其他成绩及权重计算总成绩
总成绩计算公式为SUMPRODUCT($B$17:$D$17,B18:D18),其中$B$17:$D$17为权重区域B18:D18为成绩区域注意两个区域的绝对引用和相对引用。SUMPRODUCT 函数对两个区域进行乘积求和。在权重项目比较多时使用SUMPRODUCT比一个一个乘要方便很多
用法6:隔列条件求和
如下例中需要计算每一种产品的计划采购数量和实际采购数量 汇总笔记本计划数量的公式为SUMPRODUCT(($B$33:$I$33J$33)*$B34:$I34),其中$B$33:$I$33J$33为条件判断区域找到对应为计划的列$B34:$I34为值区域符合条件的列条件判断区域返回1然后与值区域相乘再相加。 对于所有产品的总计数量对应公式为SUMPRODUCT(($B$33:$I$33J$33)*$B34:$I37) 将值区域设为的产品区域相乘相加后即得到所有产品所有季度的汇总、 从这个例子可以看得出来SUMPRODUCT函数的array数组使用很灵活但原理是一致的掌握原理才可以更好的使用SUMPRODUCT函数
示例文件下载 https://download.csdn.net/download/qq_42692386/88855138
