Excel 如何进行多条件查找或求和？

原创于 2025-07-04 14:54:55 发布 · 2.6k 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#excel

Excel 专栏收录该内容

22 篇文章

订阅专栏

处理“多条件”的统计和查找是Excel数据分析中非常高频的场景。Excel提供了一系列强大的“IFS”家族函数来专门解决这类问题。

这些函数的设计思想非常相似，一旦你学会一个，其他的就能触类旁通。

SUMIFS: 多条件求和
COUNTIFS: 多条件计数
AVERAGEIFS: 多条件求平均值

此外，对于多条件查找，最强大的工具依然是 XLOOKUP。

一、多条件求和：`SUMIFS`

SUMIFS函数可能是这个系列里最常用的。它的作用是：当满足所有指定条件时，对某个区域进行求和。

语法：
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

大白话翻译：
=SUMIFS(你要对哪个区域求和, 条件区域1, 条件1, 条件区域2, 条件2, ...)

sum_range: 最终要求和的那个数字区域（比如“销售额”列）。
criteria_range1: 第一个条件的判断区域（比如“区域”列）。
criteria1: 第一个条件的具体内容（比如“华北”）。
后续的 criteria_range2, criteria2… 可以不断添加，支持多达127个条件对。

实战场景：
假设我们有以下销售数据表：

	A	B	C	D
1	区域	产品	销售员	销售额
2	华北	A产品	张三	5,000
3	华南	B产品	李四	8,000
4	华北	B产品	张三	6,500
5	华北	A产品	王五	7,200
6	华南	A产品	李四	9,100

问题1：计算“华北”区域的总销售额？ (这是一个单条件问题，可以用SUMIF，但SUMIFS也能做)

公式：=SUMIFS(D:D, A:A, "华北")
解析：
- D:D：最终要求和的是“销售额”列。
- A:A：条件判断的区域是“区域”列。
- "华北"：具体的条件是等于“华北”。
结果：18,700 (5000 + 6500 + 7200)

问题2：计算“华北”区域 “A产品” 的总销售额？ (这是典型的多条件问题)

公式：=SUMIFS(D:D, A:A, "华北", B:B, "A产品")
解析：
- D:D：求和区域还是“销售额”。
- A:A, "华北"：第一个条件对，区域是“华北”。
- B:B, "A产品"：第二个条件对，产品是“A产品”。
结果：12,200 (5000 + 7200)

问题3：计算“张三”在“华北”区域的总销售额？

公式：=SUMIFS(D:D, C:C, "张三", A:A, "华北")
结果：11,500 (5000 + 6500)

二、多条件计数：`COUNTIFS`

COUNTIFS函数的逻辑更简单，因为它不需要“求和区域”，只需要告诉它一系列的条件区域和条件即可。

语法：
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

大白话翻译：
=COUNTIFS(条件区域1, 条件1, 条件区域2, 条件2, ...)

实战场景 (使用相同数据表)：

问题1：统计“华北”区域有多少条销售记录？

公式：=COUNTIFS(A:A, "华北")
结果：3

问题2：统计“华北”区域 “A产品” 的销售次数（订单数）？

公式：=COUNTIFS(A:A, "华北", B:B, "A产品")
结果：2

问题3：统计销售额大于6000的订单有多少笔？

公式：=COUNTIFS(D:D, ">6000")
- 重要技巧：当条件是大于、小于等比较运算时，需要将运算符和数值放在英文双引号内。
结果：4

问题4：统计“李四”销售额大于8000的订单有多少笔？

公式：=COUNTIFS(C:C, "李四", D:D, ">8000")
结果：1

三、多条件求平均：`AVERAGEIFS`

AVERAGEIFS的语法结构和SUMIFS完全一样。

语法：
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

大白话翻译：
=AVERAGEIFS(你要对哪个区域求平均, 条件区域1, 条件1, 条件区域2, 条件2, ...)

实战场景 (使用相同数据表)：

问题：计算“华北”区域“A产品”的平均销售额？

公式：=AVERAGEIFS(D:D, A:A, "华北", B:B, "A产品")
解析：找到所有满足“华北”和“A产品”的记录（有2条，销售额分别是5000和7200），然后计算它们的平均值。
结果：6,100 ((5000 + 7200) / 2)

四、多条件查找：`XLOOKUP`

虽然VLOOKUP和INDEX+MATCH也能通过一些技巧实现多条件查找，但通常比较复杂（比如需要创建辅助列或使用数组公式）。而XLOOKUP虽然本身不直接支持多条件，但可以通过一个非常聪明的技巧来实现，而且非常直观。

技巧核心：将多个条件用 & 连接成一个唯一的“辅助键”。

场景：我想查找“华北”区域，“A产品”的第一个销售员是谁？

公式：
=XLOOKUP("华北" & "A产品", A:A & B:B, C:C, "未找到")

解析这个神奇的公式：

"华北" & "A产品": 这是我们要查找的目标值。我们手动将两个条件合并成一个文本字符串 "华北A产品"。
A:A & B:B: 这是查找区域。这个操作会在内存中动态地创建一个新的“虚拟列”。这一列的每一行都是由A列和B列对应单元格合并而成的。
- 第2行是 “华北A产品”
- 第3行是 “华南B产品”
- 第4行是 “华北B产品”
- …
C:C: 这是要返回结果的列（“销售员”列）。
"未找到": 找不到时的提示。

XLOOKUP现在就在这个虚拟列里查找"华北A产品"，当它在第2行找到匹配项时，就会返回同一行C列的值，也就是“张三”。

这种方法极其强大和灵活，可以扩展到任意多个条件。

总结

需求	推荐函数	核心思想
多条件求和	`SUMIFS`	先指定求和区域，再罗列一堆“条件区域”和“条件”。
多条件计数	`COUNTIFS`	直接罗列一堆“条件区域”和“条件”。
多条件求平均	`AVERAGEIFS`	和SUMIFS一样，先指定求平均区域，再罗列条件。
多条件查找	`XLOOKUP`	用 `&` 将多个条件和条件区域连接成一个，进行查找。