Excel高手必学：SUMPRODUCT函数10大绝技！
Excel高手必学：SUMPRODUCT函数10大绝技！
在Excel数据处理中，SUMPRODUCT函数堪称万能工具。它不仅能实现简单的乘积求和，还能应对复杂的条件计数、模糊匹配等场景。掌握其十大绝技，可以让你的工作效率大幅提升。
基础绝技：乘积之和
这是SUMPRODUCT最基础的功能，用于计算多个数组中对应元素的乘积之和。
场景描述
假设你有一张销售表，需要计算所有商品的总销售额。
公式示例
=SUMPRODUCT(单价范围, 销量范围)
数据案例
商品 | 单价 | 销量 |
|---|---|---|
A | 10 | 5 |
B | 20 | 3 |
C | 15 | 4 |
计算总销售额：=SUMPRODUCT(B2:B4, C2:C4) 结果为 140
绝技一：单条件求和
当需要对满足特定条件的数据进行求和时，可以使用SUMPRODUCT配合逻辑判断。
场景描述
统计某个部门的总销售额。
公式示例
=SUMPRODUCT((部门范围=指定部门)*销售额范围)
数据案例
部门 | 销售额 |
|---|---|
A | 100 |
B | 200 |
A | 150 |
计算部门A的总销售额：=SUMPRODUCT((A2:A4="A")*B2:B4) 结果为 250
绝技二：多条件求和
当需要同时满足多个条件时，可以将多个逻辑判断相乘。
场景描述
统计某个月份某个部门的总销售额。
公式示例
=SUMPRODUCT((月份范围=指定月份)*(部门范围=指定部门)*销售额范围)
数据案例
月份 | 部门 | 销售额 |
|---|---|---|
1月 | A | 100 |
2月 | B | 200 |
2月 | A | 150 |
计算2月份部门A的总销售额：=SUMPRODUCT((A2:A4="2月")*(B2:B4="A")*C2:C4) 结果为 150
绝技三：模糊匹配求和
当需要对包含特定关键词的数据进行求和时，可以结合FIND和ISNUMBER函数。
场景描述
统计包含特定关键词的产品销售额。
公式示例
=SUMPRODUCT(ISNUMBER(FIND(关键词, 产品名称范围))*销售额范围)
数据案例
产品名称 | 销售额 |
|---|---|
苹果手机 | 500 |
华为手机 | 600 |
小米手机 | 400 |
计算所有手机的总销售额：=SUMPRODUCT(ISNUMBER(FIND("手机", A2:A4))*B2:B4) 结果为 1500
绝技四：交叉表查询
在交叉表中，可以根据行和列的条件进行数据查询。
场景描述
在销售数据表中查询特定日期和产品的销售额。
公式示例
=SUMPRODUCT((日期范围=指定日期)*(产品范围=指定产品)*销售额范围)
数据案例
1月 | 2月 | 3月 | |
|---|---|---|---|
A | 100 | 150 | 200 |
B | 200 | 250 | 300 |
查询产品A在2月份的销售额：=SUMPRODUCT((B1:D1="2月")*(A2:A3="A")*B2:D3) 结果为 150
绝技五：不重复计数
可以用来统计一个范围内不重复值的数量。
场景描述
统计客户名单中唯一值的数量。
公式示例
=SUMPRODUCT(1/COUNTIF(客户名单范围, 客户名单范围))
数据案例
客户 |
|---|
A |
B |
A |
C |
计算唯一客户数量：=SUMPRODUCT(1/COUNTIF(A2:A5, A2:A5)) 结果为 3
绝技六：加权评分
可以用来计算加权平均值或加权总分。
场景描述
计算学生的加权总分。
公式示例
=SUMPRODUCT(权重范围, 分数范围)
数据案例
科目 | 权重 | 分数 |
|---|---|---|
数学 | 0.4 | 80 |
英语 | 0.3 | 75 |
语文 | 0.3 | 85 |
计算加权总分：=SUMPRODUCT(B2:B4, C2:C4) 结果为 79.5
绝技七：多条件计数
可以用来统计同时满足多个条件的记录数量。
场景描述
统计满足特定条件的订单数量。
公式示例
=SUMPRODUCT((条件1)*(条件2))
数据案例
金额 | 状态 |
|---|---|
100 | 已完成 |
200 | 未完成 |
150 | 已完成 |
统计已完成且金额大于100的订单数量：=SUMPRODUCT((A2:A4>100)*(B2:B4="已完成")) 结果为 1
绝技八：处理错误值
当数据中存在错误值时，可以使用数组运算避免错误。
场景描述
计算包含错误值的销售额总和。
公式示例
=SUMPRODUCT(IF(ISNUMBER(销售额范围), 销售额范围, 0))
数据案例
销售额 |
|---|
100 |
#N/A |
150 |
计算总销售额：=SUMPRODUCT(IF(ISNUMBER(A2:A4), A2:A4, 0)) 结果为 250
绝技九：动态范围求和
可以结合OFFSET函数实现动态范围的求和。
场景描述
计算最近N天的销售总额。
公式示例
=SUMPRODUCT(OFFSET(销售额范围, 0, 0, N))
数据案例
日期 | 销售额 |
|---|---|
1月1日 | 100 |
1月2日 | 150 |
1月3日 | 200 |
计算最近2天的总销售额：=SUMPRODUCT(OFFSET(B2:B4, 0, 0, 2)) 结果为 350
绝技十：多表汇总
可以用来汇总多个工作表中的数据。
场景描述
汇总多个部门的销售数据。
公式示例
=SUMPRODUCT(表1销售额范围, 表2销售额范围)
数据案例
表1：
销售额 |
|---|
100 |
150 |
表2：
销售额 |
|---|
200 |
250 |
汇总总销售额：=SUMPRODUCT(Sheet1!A2:A3, Sheet2!A2:A3) 结果为 700
使用注意事项
- 数组维度一致：所有数组的行列数必须相同，否则返回
#VALUE!错误。 - 性能优化：避免使用整列引用（如A:A），数据量大时会影响性能。
- 逻辑值处理：条件判断生成的TRUE/FALSE需要通过乘法转换为数值。
- 文本处理：非数值元素（如文本）默认视为0。
通过以上十大绝技，你可以看到SUMPRODUCT函数的强大功能。无论是简单的乘积求和，还是复杂的条件计数，这个函数都能帮你轻松应对。掌握这些技巧，可以让你在数据处理工作中事半功倍。