​Excel——SUMPRODUCT 函数

SUMPRODUCT 是 Excel 中最强大的函数之一，可以用于 ​多条件求和、加权计算、数组运算​ 等复杂场景。下面通过 ​基础语法 + 实用案例​ 彻底讲透它的用法！

​一、基础语法​

=SUMPRODUCT(数组1, [数组2], [数组3], ...)

• ​功能​：将多个数组的对应元素相乘后求和。
• ​核心规则​：
  • 所有数组必须 ​大小相同​（行数、列数一致）。
  • 非数值（如文本、逻辑值）在逗号分隔时视为 0，用运算符连接时可能报错。

​二、6大经典用法​

​1. 基本用法：两列相乘求和​

▸ ​场景​：计算总销售额（单价 × 数量）。

=SUMPRODUCT(B2:B4, C2:C4)  

​结果​：100×2 + 200×1 + 50×4 = 600

✅ ​等效写法​：

=SUMPRODUCT(B2:B4 * C2:C4)  

​2. 多条件求和（替代SUMIFS）​​

▸ ​场景​：统计“销售部”且“销售额>5000”的总金额。

=SUMPRODUCT((A2:A4="销售部") * (B2:B4>5000) * B2:B4)  

​结果​：7000（仅第3行符合条件）
🔍 ​逻辑分解​：

1. (A2:A4="销售部") → {1,0,1}
2. (B2:B4>5000) → {0,1,1}
3. 相乘后筛选：{0,0,1} * {3000,6000,7000} = {0,0,7000}
4. 求和：7000

​3. 加权平均计算​

▸ ​场景​：计算3种产品的加权平均单价（权重=销量）。

=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)  

​计算过程​：

• 分子：10×100 + 20×50 + 30×30 = 2900
• 分母：100 + 50 + 30 = 180
• 结果：2900 / 180 ≈ 16.11

​4. 多列混合运算（加减乘除）​​

▸ ​场景​：计算 (A列+B列) × C列 的总和。

=SUMPRODUCT((A2:A3 + B2:B3) * C2:C3)  

​结果​：(1+2)×3 + (4+5)×6 = 9 + 54 = 63

​5. 条件计数（替代COUNTIFS）​​

▸ ​场景​：统计“销售部”且“销售额>5000”的订单数。

=SUMPRODUCT((A2:A4="销售部") * (B2:B4>5000))  

​结果​：1（只有第3行符合）

​6. 处理复杂条件（OR逻辑）​​

▸ ​场景​：统计“销售部”或“技术部”的销售额总和。

=SUMPRODUCT(((A2:A4="销售部") + (A2:A4="技术部")) * B2:B4)  

​关键技巧​：用 + 表示 ​OR，* 表示 ​AND。

​三、常见错误及解决​

​四、性能优化技巧​

1. ​避免整列引用​：用 A2:A100 替代 A:A，减少计算量。
2. ​预计算辅助列​：复杂运算可先在其他列计算，再用SUMPRODUCT求和。
3. ​替代方案​：
   • 多条件求和 → SUMIFS
   • 简单相乘求和 → MMULT（矩阵运算）

​五、总结​

• ​SUMPRODUCT = 条件筛选 + 数组运算 + 自动求和​
• ​运算符选择​：
  • 逗号（,）→ 自动忽略非数字
  • 星号（*）→ 严格计算，需处理错误
• ​适用场景​：加权平均、多条件求和、复杂数组运算。

六、案例

=SUMPRODUCT(E33:K33, VLOOKUP($E$32:$K$32, $D$23:$M$30, 10, FALSE))

1. VLOOKUP部分：查找权重值​

VLOOKUP($E$32:$K$32, $D$23:$M$30, 10, FALSE)

• ​查找值​：$E$32:$K$32 → 销售阶段名称（"潜在","接触",...,"成交"）
• ​查找范围​：$D$23:$M$30 → 上方权重表（含阶段名称和权重百分比）
• ​返回列​：10 → 权重百分比所在列（第10列，即M23:M30）
• ​匹配方式​：FALSE → 精确匹配

​输出结果​：
{3.57%, 7.14%, 10.71%, 14.29%, 17.86%, 21.43%, 25.00%}

​2. SUMPRODUCT部分：计算加权和​

SUMPRODUCT(E33:K33, 上述VLOOKUP结果)

• ​数组1​：E33:K33 → 1Q各阶段数值（200,205,210,215,220,225,230）
• ​数组2​：VLOOKUP返回的权重数组
• ​计算过程​：

  200×3.57% + 205×7.14% + 210×10.71% + 215×14.29% + 
  220×17.86% + 225×21.43% + 230×25.00% = 220