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

发布于:2025-07-06 ⋅ 阅读:(18) ⋅ 点赞:(0)

处理“多条件”的统计和查找是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

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

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

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

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

解析这个神奇的公式:

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

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

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

总结

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

当你需要根据多个标准来筛选数据并进行计算时,请第一时间想到这个强大的 IFS家族XLOOKUP的连接技巧


网站公告

今日签到

点亮在社区的每一天
去签到