↓Skip to main content
  1. Teches/

学习 SUMPRODUCT 函数

·2 mins

上一篇文章里用到了强大的 SUMPRODUCT 函数， 如果只看该函数的介绍，会有点不明所以，虽然不影响照猫画虎地使用，但搞清楚它的原理才能更好地使用，所以有必要专门研究一番。
在看过微软官方介绍和网上其他人的讲解后，基本明白了它的使用，记录于此。

SUMPRODUCT 函数——返回相应范围或数组的乘积的总和（Sum 求和，Product 乘积）。 默认运算是乘法，但也可以加法、减法和除法。
语法：=SUMPRODUCT(array1, [array2], [array3], ...) 使用默认运算 (乘法)

参数：

  • array1 ——必需，​​其相应元素需要进行相乘并求和的第一个数组参数。
  • [array2]、[array3],… ——可选，2 到 255 个数组参数，其相应元素需要进行相乘并求和。
  • 执行其他算术运算：将分隔数组参数的逗号替换为所需的算术运算符 (*、/、+、-) 。 执行所有操作后，对结果进行求和。
    注意：如果使用算术运算符，请将数组参数括在括号中，并使用括号对数组参数进行分组，以控制算术运算的顺序。

备注：

  1. 数组参数必须具有相同的维数。 否则，函数将返回 #VALUE! 错误值 #REF!。 例如 SUMPRODUCT(C2:C10,D2:D5) 两组数据范围的大小不同也即维数不同就会报错；
  2. SUMPRODUCT 将非数值数组条目视为零；
  3. 为避免影响运算性能，SUMPRODUCT 不应与完整的列引用一起使用。如 SUMPRODUCT(A:A,B:B) ，此函数会将 A 列中的 1,048,576 个单元格乘以 B 列中的 1,048,576 个单元格，然后再求和它们，导致运算速度会变很慢。

运算示例：

  1. 只有 array1 一个数组，因为没有其它要相乘的数组，所以只对本数组内数据进行求和运算。此时等效于 SUM 函数求和。

    单数组示例
    单数组示例

  2. array1、array2 两个数组，第一个数组中数据与第二个数组中相应数据（同行、同列、行与列——视数据组织形式而不同）进行相乘，然后对乘积进行求和运算。

    两数组示例
    两数组示例

  3. 三个数组。此例中，我们想要返回由给定名字购买的特定商品数量的总和。 小鸡买了多少谷子呢？公式首先将小鸡的出现次数乘以匹配的谷子出现次数， 然后它会对数量列中相应行的值求和。相当于代替 SUMIF / SUMIFS 函数进行条件求和。语法说明就是：SUMPRODUCT((包含条件1的区域=条件1)*(包含条件2的区域=条件2),需要计算的区域)

    三数组示例

  4. 两个数组。示例使用 SUMPRODUCT 按商家返回总净收入。 公式返回单元格 H11 中列出的商家的所有收入和支出的总和。

    两数组示例2

  5. 不用辅助列快速汇总销售额，示例公式 =SUMPRODUCT(--(B2:B8=C14),C2:C8,D2:D8)

    三数组示例2
    如果将此公式后面的数组去掉，变成 =SUMPRODUCT(--(B2:B8=C14))，计算结果为 2，也即“毛虫”在此列中出现的次数。假如实际使用场景是一份人员名册，“毛虫”一列是性别，就可以快速统计出男性或女性的人数。
    此例相当于是代替 COUNTIF / COUNTIFS 函数进行条件计数。

数组内的条件可以根据实际需要用“> = <”符号的其中之一或两个组合来设置。
某些案例在参数中“*1“或者前缀“–”是为了将逻辑值转换成数值，但似乎大部分情况下没必要。
用“> = <”设置的判断条件，其运算结果是真或假的一个逻辑值。
SUMPRODUCT 函数不支持通配符。如果是类似“A1 A2 A3 B1”的一组数据，要提取出所有编号包含 A 的数据，公式可以是 =SUMPRODUCT(--NOT(ISERROR(FIND("A",A22:A25))),C22:C25)，FIND 函数在 A22:A25 区间查找包含 A 的数据，如果找不到将产生一个错误值返回 true，再使用 NOT 求反来判断包含 A 的单元格，用 – 将这组逻辑值转换成数值，与 C22:C25 相乘并汇总——只是看到别人这样用，似乎有点复杂化了，是不是可以有更简洁的函数代替？

总结来说就是 SUMPRODUCT 函数的条件判断更灵活，其参数可以是其他函数或公式返回的值。SUMIF / SUMIFS / COUNTIF / COUNTIFS 的条件区域、求和区域只能设置为单元格区域。

  1. 或关系运算——嵌套 SIGN 函数
    SIGN 函数会把大于 0 的数值变成 1，0 变为 0，负数变成 -1。
    仍以第三张图“三数组示例“中的数据为例说明。
    目标：求和表内满足条件价格>4 或者数量>7 的所有商品的销售额
    公式：=SUMPRODUCT(SIGN((C2:C8>4)+(D2:D8>7)),C2:C8,D2:D8)
    结果：128
    程序计算过程：

    1. 逻辑判断 1. 逻辑判断 2. 逻辑运算 3. SIGN 取值 4. 乘积运算 5. 求和运算
    (C2:C8>4) (D2:D8>7) (C2:C8>4)+(D2:D8>7) SIGN(()+()) SIGN(),C2:C8,D2:D8 SUMPRODUCT
    FALSE FALSE 0+0 0 –> 0 0*2*1=0 0
    FALSE FALSE 0+0 0 –> 0 0*2*3=0 +0
    TRUE FALSE 1+0 1 –> 1 1*7*5=35 +35
    TRUE FALSE 1+0 1 –> 1 1*5*7=35 +35
    FALSE TRUE 0+1 1 –> 1 1*2*9=18 +18
    TRUE TRUE 1+1 2 –> 1 1*5*8=40 +40=128

  2. 另一种日期判断运算
    以上一篇文章的数据为例，计算 2024.10.19 以后的动物数量，目标单元格输入 =SUMPRODUCT((A2:A18>DATE(2024,10,19)),C2:C18)

  3. 计算加权平均值
    以第三张图“三数组示例“中的数据为例，目标单元格输入 =SUMPRODUCT(C2:C7,D2:D7)/SUM(D2:D7)

  4. 权重求和计算且权重是纵向表格
    例如计算考试成绩时的加权平均数，目标单元格输入 =SUMPRODUCT(I2:K2,TRANSPOSE($P$3:$P$5))

    权重求和示例

—— CC BY-NC-ND 4.0