excel SUMPRODUCT函数用法解析

Excel函数 2021-11-17 13:48:59

语法结构

SUMPRODUCT函数语法结构为：SUMPRODUCT(array1,array2,array3, ...)。函数可用于返回相应的数组或区域乘积之和。

下面用３个案例分别讲解参数均为数字型数据区域、参数均为逻辑值、参数中数字型数据区域与逻辑值混合情况下SUMPRODUCT函数的用法。

案例１：各参数为数字型数据区域

应用场景：

下图表格中有不同玫瑰品种的销量和单价，我们想在Ｆ２单元格求取所有订单的销售金额总和。

操作步骤：

选中F２单元格，输入公式：=SUMPRODUCT(C2:C4,D2:D4)，即可返回所有订单销售额总和1000。

公式说明：

SUMPRODUCT(C2:C4,D2:D4)意为将Ｃ２与Ｄ２相乘、Ｃ３与Ｄ３相乘、Ｃ４与Ｄ４相乘，得到每笔订单的销售金额，最后再将３笔订单的销售金额相加求和，从而得到所有订单的销售总额。

案例２：各参数均为判断的结果逻辑值

应用场景：

下图表格中有不同日期不同玫瑰品种的销售数据，我们想在Ｆ２单元格计算６月份香槟玫瑰的订单数量。

操作步骤：

选中F２单元格，输入公式：SUMPRODUCT((MONTH(B2:B9)=6)*1,(C2:C9="香槟玫瑰")*1)，即可返回６月份香槟玫瑰的订单数量３。

公式说明：

１、MONTH(B2:B9)=6为逻辑值，意为判断B2:B9单元格中的日期是否在６月份。（Month函数用于返回日期中的月份）；

２、C2:C9="香槟玫瑰"也为逻辑值，意为判断C2:C9单元格中的内容是否为"香槟玫瑰"；

３、因为SUMPRODUCT函数用逗号隔开的各个参数必须为数字型数据，所以上述两个逻辑值参数均要乘以１，将逻辑值转换为数字；

４、最后，SUMPRODUCT函数返回同时满足"订单日期"在６月、"玫瑰品种"为"香槟玫瑰"的订单数量３。

小提示：

在不用逗号分隔参数的情况下，上述公式也可以简化为：=SUMPRODUCT((MONTH(B2:B9)=6)*(C2:C9="香槟玫瑰"))，直接用*将各个逻辑值连接进行乘法运算，不必将各个逻辑值乘以１。

案例３：参数中数字型数据区域与逻辑值混合

应用场景：

下图表格中有不同日期不同玫瑰品种的销售数据，我们想在Ｆ２单元格计算６月份香槟玫瑰的订单总金额。

操作步骤：

选中F2单元格，输入公式：=SUMPRODUCT((MONTH(B2:B9)=6)*1,(C2:C9="香槟玫瑰")*1,D2:D9)，即可返回６月份香槟玫瑰的订单总金额324。

公式说明：

１、本案例前面两个参数与案例２相同，意为判断B2:B9单元格中的日期是否在６月份、C2:C9单元格中的内容是否为"香槟玫瑰"；

２、最后一个参数D2:D9为数字型数据区域，是要求和的区域；

３、案例２的公式最终目的是计数，案例３的公式最终目的是求和，在与案例２中计数公式判断条件相同的情况下，增加了一个求和的数据区域。

小提示：

在不用逗号分隔参数的情况下，本案例公式也可以简化为：=SUMPRODUCT((MONTH(B2:B9)=6)*(C2:C9="香槟玫瑰")*D2:D9)，直接将所有逻辑值和数字型数据区域用*连接相乘，不必将逻辑值乘以１转换为数字。