SUMPRODUCT 是 Excel 中最强大的函数之一,可以用于 多条件求和、加权计算、数组运算 等复杂场景。下面通过 基础语法 + 实用案例 彻底讲透它的用法!
一、基础语法
=SUMPRODUCT(数组1, [数组2], [数组3], ...)
- 功能:将多个数组的对应元素相乘后求和。
- 核心规则:
- 所有数组必须 大小相同(行数、列数一致)。
- 非数值(如文本、逻辑值)在逗号分隔时视为
0
,用运算符连接时可能报错。
二、6大经典用法
1. 基本用法:两列相乘求和
▸ 场景:计算总销售额(单价 × 数量)。
产品 | 单价 | 数量 |
---|---|---|
产品A | 100 | 2 |
产品B | 200 | 1 |
产品C | 50 | 4 |
=SUMPRODUCT(B2:B4, C2:C4)
结果:100×2 + 200×1 + 50×4 = 600
✅ 等效写法:
=SUMPRODUCT(B2:B4 * C2:C4)
2. 多条件求和(替代SUMIFS)
▸ 场景:统计“销售部”且“销售额>5000”的总金额。
部门 | 销售额 |
---|---|
销售部 | 3000 |
技术部 | 6000 |
销售部 | 7000 |
=SUMPRODUCT((A2:A4="销售部") * (B2:B4>5000) * B2:B4)
结果:7000
(仅第3行符合条件)
🔍 逻辑分解:
(A2:A4="销售部")
→{1,0,1}
(B2:B4>5000)
→{0,1,1}
- 相乘后筛选:
{0,0,1} * {3000,6000,7000} = {0,0,7000}
- 求和:
7000
3. 加权平均计算
▸ 场景:计算3种产品的加权平均单价(权重=销量)。
产品 | 单价 | 销量 |
---|---|---|
产品A | 10 | 100 |
产品B | 20 | 50 |
产品C | 30 | 30 |
=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列
的总和。
A列 | B列 | C列 |
---|---|---|
1 | 2 | 3 |
4 | 5 | 6 |
=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。
三、常见错误及解决
错误类型 | 原因 | 解决方法 |
---|---|---|
#VALUE! | 数组大小不一致 | 检查所有数组的行列数是否相同 |
#N/A | 数据含错误值 | 用 IFERROR 处理:=SUMPRODUCT(IFERROR(数组,0)) |
结果为零 | 条件无匹配或数据为文本 | 用 COUNTIFS 验证条件是否成立 |
四、性能优化技巧
- 避免整列引用:用
A2:A100
替代A:A
,减少计算量。 - 预计算辅助列:复杂运算可先在其他列计算,再用SUMPRODUCT求和。
- 替代方案:
- 多条件求和 →
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