Excel SUMPRODUCT函数完全指南：从入门到精通

Excel SUMPRODUCT函数完全指南：从入门到精通

引用

CSDN

等

6

来源

1.

https://blog.csdn.net/Excel_Yun/article/details/136044861

2.

https://blog.csdn.net/weixin_44228413/article/details/139447510

3.

https://blog.csdn.net/qq_42692386/article/details/136165037

4.

https://support.microsoft.com/zh-cn/office/sumproduct-%E5%87%BD%E6%95%B0-16753e75-9f68-4874-94ac-4d2145a2fd2e

5.

https://docs.pingcode.com/baike/4250015

6.

https://docs.pingcode.com/baike/4897812

在Excel中，SUMPRODUCT函数是一个功能强大的工具，可以快速对一个或多个数组中的对应值乘积求和。通过简单的公式输入，如=SUMPRODUCT(B3:B6, C3:C6)，用户能够轻松处理大量数据，避免了繁琐的手动计算过程。无论是日常办公还是数据分析，这个功能都能大大提高工作效率，是职场人士不可或缺的高效计算神器。

SUMPRODUCT函数的基本语法如下：

=SUMPRODUCT(array1, [array2], [array3], ...)

• array1：必需，其相应元素需要进行相乘并求和的第一个数组参数。
• array2, array3, ...：可选，2到255个数组参数，其相应元素需要进行相乘并求和。

默认运算是乘法，但也可以通过替换逗号为所需的算术运算符（*、/、+、-）来执行其他算术运算。注意，如果使用算术运算符，请考虑将数组参数括在括号中，并使用括号对数组参数进行分组，以控制算术运算的顺序。

注意事项

• 数组参数必须具有相同的维数。否则，函数SUMPRODUCT将返回#VALUE!错误值#REF!。例如，=SUMPRODUCT(C2:C10, D2:D5)将返回错误，因为范围的大小不同。
• SUMPRODUCT将非数值数组条目视为零。
• 为了获得最佳性能，SUMPRODUCT不应与完整的列引用一起使用。例如，=SUMPRODUCT(A:A, B:B)会将A列中的1,048,576个单元格乘以B列中的1,048,576个单元格，然后再添加它们。

1. 计算数组的乘积和

这是SUMPRODUCT函数最基本的功能，它将对应位置的数组元素相乘，然后将所有乘积的结果相加。

示例1：简单的数组乘积和

假设我们有两个数组，A列和B列，分别存储了一些数值。我们希望计算这些数值的乘积和：

A列：1, 2, 3, 4
B列：5, 6, 7, 8

在C1单元格中输入公式：

=SUMPRODUCT(A1:A4, B1:B4)

得到的结果是15 + 26 + 37 + 48 = 70。

示例2：加权平均

假设我们有一个商品价格和对应的销售数量，我们希望计算这些商品的加权平均价格：

价格：10, 20, 30
数量：2, 4, 6

在C1单元格中输入公式：

=SUMPRODUCT(A1:A3, B1:B3) / SUM(B1:B3)

得到的结果是(102 + 204 + 30*6) / (2+4+6) = 23.33。

2. 条件求和

SUMPRODUCT函数还可以用于执行条件求和，即在满足一定条件的情况下进行数组运算。

示例1：单条件求和

假设我们有一个销售数据表，包括产品名称和销售额。我们希望计算特定产品的总销售额：

产品：A, B, A, B
销售额：100, 200, 300, 400

在C1单元格中输入公式：

=SUMPRODUCT((A1:A4="A") * (B1:B4))

得到的结果是100 + 300 = 400。

示例2：多条件求和

假设我们有一个更复杂的数据表，包括产品名称、地区和销售额。我们希望计算特定产品在特定地区的总销售额：

产品：A, A, B, B
地区：北, 南, 北, 南
销售额：100, 200, 300, 400

在C1单元格中输入公式：

=SUMPRODUCT((A1:A4="A") * (B1:B4="北") * (C1:C4))

得到的结果是100。

3. 多条件逻辑处理

SUMPRODUCT函数还可以用于处理多条件逻辑，例如与运算、或运算等。

示例1：与运算

假设我们有一个数据表，包括产品名称、地区和销售额。我们希望计算特定产品在特定地区且销售额大于某个值的总销售额：

产品：A, A, B, B
地区：北, 南, 北, 南
销售额：100, 200, 300, 400

在C1单元格中输入公式：

=SUMPRODUCT((A1:A4="A") * (B1:B4="北") * (C1:C4>150) * (C1:C4))

得到的结果是0，因为没有满足所有条件的记录。

示例2：或运算

假设我们有一个数据表，包括产品名称、地区和销售额。我们希望计算特定产品在特定地区或销售额大于某个值的总销售额：

产品：A, A, B, B
地区：北, 南, 北, 南
销售额：100, 200, 300, 400

在C1单元格中输入公式：

=SUMPRODUCT(((A1:A4="A") + (B1:B4="北")) * (C1:C4))

得到的结果是100 + 200 + 300 = 600。

4. 高级应用

示例1：动态数组求和

假设我们有一个动态数据表，数据的行数会不断变化。我们希望计算这些数据的乘积和，但不希望每次都手动调整公式的范围。我们可以使用OFFSET函数来创建动态范围。例如，假设数据在A列和B列，我们可以在C1单元格中输入公式：

=SUMPRODUCT(OFFSET(A1, 0, 0, COUNTA(A:A)), OFFSET(B1, 0, 0, COUNTA(B:B)))

这个公式会根据A列和B列的数据行数自动调整范围。

示例2：数组常量

SUMPRODUCT函数还可以使用数组常量来进行计算。例如，我们希望计算以下数组的乘积和：

数组1：{1, 2, 3}
数组2：{4, 5, 6}

我们可以在C1单元格中输入公式：

=SUMPRODUCT({1, 2, 3}, {4, 5, 6})

得到的结果是14 + 25 + 3*6 = 32。

SUM函数 vs. SUMPRODUCT函数

• SUM函数：是最基本的函数之一，用于计算一组数值的总和。只能计算一组数值的总和。
• SUMPRODUCT函数：是一个更强大的函数，可以用于计算多个数值数组的乘积之和。可以同时处理多个数字，并将它们相乘再相加。

使用场景

• 当只需要计算一组数值的总和时，可以使用SUM函数。
• 当需要计算多个数值数组的乘积之和时，可以使用SUMPRODUCT函数。

缺点

• SUM函数不能处理空白单元格，会将其视为0，导致计算结果不准确。
• SUMPRODUCT函数在处理大量数据时计算速度较慢。

1. 数组大小必须相同：SUMPRODUCT函数要求所有参与运算的数组大小必须相同。如果数组大小不同，Excel会返回错误。

2. 性能问题：由于SUMPRODUCT函数会对数组中的每个元素进行计算，因此在处理非常大的数据集时，可能会导致性能问题。建议在可能的情况下，使用其他函数或方法来优化性能。

3. 逻辑运算的优先级：在使用SUMPRODUCT函数进行逻辑运算时，需要注意逻辑运算的优先级。例如，在公式：

=SUMPRODUCT((A1:A4="A") * (B1:B4="北") + (C1:C4>150) * (C1:C4))

中，括号内的逻辑运算会优先进行，然后再进行乘法和加法运算。

通过以上介绍，可以看出SUMPRODUCT函数是Excel中非常强大的工具，可以用于计算数组的乘积和、执行条件求和、处理多条件逻辑等。通过灵活应用SUMPRODUCT函数，可以大大简化复杂的数据计算过程，提高工作效率。在实际应用中，我们需要根据具体需求，合理设计公式，充分发挥SUMPRODUCT函数的优势。