excel条件求和包含某个字符：SUMIF函数与SUMIFS函数灵活运用详解

excel条件求和包含某个字符：SUMIF函数与SUMIFS函数灵活运用详解

当您需要在 Excel 中对包含特定字符的单元格进行求和时，最常用的方法是利用 SUMIF 或 SUMIFS 函数。这两个函数能够根据设定的条件，对符合条件的数值单元格进行汇总。对于“包含某个字符”的条件，通常需要结合通配符（如星号 `*`）来构建条件表达式。

一、理解 Excel 条件求和的基本原理

在 Excel 中进行条件求和，核心在于定义一个或多个条件，然后让 Excel 扫描指定的数据区域，找出满足这些条件的所有单元格，并对这些单元格对应的数值进行加总。最基础的条件求和函数是 SUMIF，它适用于单个条件。当需要同时满足多个条件时，则需要使用 SUMIFS 函数。而当条件涉及到文本的包含关系时，则需要借助通配符。

1. SUMIF 函数简介

SUMIF 函数的语法是：

SUMIF(range, criteria, [sum_range])

• range：必需参数。用于评估条件的单元格区域。
• criteria：必需参数。定义求和依据的条件，可以是数字、表达式、单元格引用或文本。
• sum_range：可选参数。实际要求和的单元格区域。如果省略，则对 range 参数中的单元格进行求和。

2. SUMIFS 函数简介

SUMIFS 函数的语法是：

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range：必需参数。实际要求和的单元格区域。
• criteria_range1：必需参数。第一个用于评估条件的单元格区域。
• criteria1：必需参数。第一个条件，其格式与 SUMIF 函数的 criteria 参数相同。
• criteria_range2, criteria2, ...：可选参数。可以指定多个条件区域和对应的条件，最多可达127组。

二、利用 SUMIF 函数实现“包含某个字符”的条件求和

当我们需要对一个区域内的单元格进行求和，而求和的依据是另一个区域的单元格中包含特定的字符时，SUMIF 函数配合通配符 `*` 是非常有效的。星号 `*` 代表任意数量的任意字符（包括零个字符）。

1. 基础场景：对包含特定文本的单元格求和

假设我们有一个销售记录表，A列是产品名称，B列是销售额。我们想计算所有产品名称中包含“苹果”的销售总额。

例如，数据如下：

• A1: 苹果手机, B1: 1000
• A2: 香蕉, B2: 200
• A3: 苹果派, B3: 300
• A4: 橙子, B4: 150
• A5: 进口苹果, B5: 500

在 C1 单元格输入以下公式：

=SUMIF(A1:A5, "*苹果*", B1:B5)

公式解释：

• A1:A5：这是条件区域，即我们要检查产品名称的单元格。
• "*苹果*"：这是条件。使用星号 `*` 包围“苹果”，表示任何以“苹果”开头、中间包含“苹果”或以“苹果”结尾的文本都会被匹配。例如，“苹果手机”、“苹果派”、“进口苹果”都会被匹配。
• B1:B5：这是要求和的区域，即销售额。

执行此公式后，C1 单元格将显示 1000 + 300 + 500 = 1800。

2. 匹配以特定字符开头的单元格

如果我们只想计算产品名称以“苹果”开头的销售总额，可以使用 `"*苹果*"` 中的第一个星号来表示。

公式示例：

=SUMIF(A1:A5, "苹果*", B1:B5)

这将匹配“苹果手机”、“苹果派”、“苹果”，但不会匹配“进口苹果”。

3. 匹配以特定字符结尾的单元格

如果我们只想计算产品名称以“苹果”结尾的销售总额，可以使用 `"*苹果*"` 中的第二个星号来表示。

公式示例：

=SUMIF(A1:A5, "*苹果", B1:B5)

这将匹配“进口苹果”、“苹果”，但不会匹配“苹果手机”、“苹果派”。

4. 匹配包含特定字符但忽略大小写

SUMIF 函数在匹配文本时，默认是区分大小写的。如果需要忽略大小写，可以结合其他函数，但对于简单的包含关系，Excel 通常能智能处理。如果确实遇到大小写敏感问题，可以考虑将原始数据转换为统一的大小写（例如使用 LOWER 函数），然后再进行条件求和。但是，对于“包含某个字符”的常见需求，直接使用通配符基本可以满足。

5. 匹配不包含特定字符的单元格

有时候，我们也需要计算不包含某个特定字符的单元格的总和。这可以通过 SUMIF 函数结合通配符来实现，但需要巧妙设计条件。

例如，计算销售额总和，但不包含产品名称中有“香蕉”的记录：

=SUMIF(A1:A5, "<>*香蕉*", B1:B5)

这里的 `<>` 是“不等于”的运算符。当与通配符结合使用时，表示“不包含”。

三、利用 SUMIFS 函数实现多条件下的“包含某个字符”的求和

SUMIFS 函数允许您指定多个条件，并且所有条件都必须同时满足才会进行求和。这在更复杂的场景中非常有用。

1. 场景：根据产品类别和产品名称包含字符进行求和

假设我们有更详细的数据，A列是产品类别，B列是产品名称，C列是销售额。

例如，数据如下：

• A1: 水果, B1: 苹果手机, C1: 1000
• A2: 水果, B2: 香蕉, C2: 200
• A3: 零食, B3: 苹果派, C3: 300
• A4: 水果, B4: 橙子, B4: 150
• A5: 电子产品, B5: 进口苹果, C5: 500
• A6: 零食, B6: 薯片, C6: 100

我们想计算“水果”类别中，产品名称包含“苹果”的销售总额。

在 D1 单元格输入以下公式：

=SUMIFS(C1:C6, A1:A6, "水果", B1:B6, "*苹果*")

公式解释：

• C1:C6：这是要求和的区域（销售额）。
• A1:A6, "水果"：这是第一个条件区域和条件。要求 A 列（产品类别）等于“水果”。
• B1:B6, "*苹果*"：这是第二个条件区域和条件。要求 B 列（产品名称）包含“苹果”。

执行此公式后，D1 单元格将显示 1000（A1:B1 匹配），因为 A3 的“苹果派”不在“水果”类别，A5 的“进口苹果”也不是“水果”类别。

2. 组合多个“包含”条件

可以组合多个“包含”条件。例如，计算产品类别包含“果”且产品名称包含“苹果”的销售总额。

公式示例：

=SUMIFS(C1:C6, A1:A6, "*果*", B1:B6, "*苹果*")

这将匹配 A1（水果，苹果手机）和 A5（电子产品，进口苹果），但 A3（零食，苹果派）不匹配第一个条件。结果是 1000 + 500 = 1500。

3. 结合“包含”与其他精确条件

SUMIFS 的强大之处在于可以灵活组合各种条件。

例如，计算产品类别为“水果”，并且产品名称包含“苹果”的销售总额（假设我们已经知道“苹果”是其中一个具体的产品名称，但仍然想用包含的方式来搜索，例如怕漏掉“苹果汁”等）：

=SUMIFS(C1:C6, A1:A6, "水果", B1:B6, "*苹果*")

这与上面的例子结果相同。此处强调的是，即使知道具体产品名称，使用“包含”也可以增加健壮性，避免因输入略微差异而漏算。

四、高级技巧与注意事项

在使用 SUMIF 和 SUMIFS 函数处理包含特定字符的条件求和时，有几个高级技巧和注意事项需要了解。

1. 使用问号 `?` 作为通配符

除了星号 `*`，Excel 还支持问号 `?` 作为通配符。问号 `?` 匹配单个任意字符。这在需要匹配特定长度的文本模式时很有用。

例如，如果产品名称是“苹果A”、“苹果B”、“苹果C”，我们想计算所有以“苹果”开头，后面跟一个任意字符的产品销售总额。

=SUMIF(A1:A5, "苹果?", B1:B5)

这将匹配“苹果A”、“苹果B”、“苹果C”，但不匹配“苹果”或“苹果手机”。

2. 匹配包含特殊字符的文本

如果需要匹配的文本本身就包含通配符（`*` 或 `?`），则需要使用波浪号 `~` 进行转义。例如，要查找包含文本“*”的单元格，条件应为 `"~*"`。

公式示例：查找产品名称中包含星号 `*` 的销售总额。

=SUMIF(A1:A5, "~*", B1:B5)

如果产品名称是“苹果*”，那么这个公式就可以正确地匹配到它。

3. 性能考虑

当处理非常大的数据集时，SUMIF 和 SUMIFS 函数可能会对 Excel 的性能产生影响。如果需要极高的效率，可以考虑使用数据透视表（Pivot Table）或 Power Query。数据透视表可以轻松地对数据进行分组和汇总，并且支持文本过滤，包括包含特定文本的过滤。Power Query 则是一个更强大的数据处理工具，可以执行复杂的数据清洗和转换操作，并在后台处理，对 Excel 本身的影响较小。

4. 配合其他函数使用

在某些情况下，可能需要结合其他函数来构建更复杂的条件。例如，如果需要根据一个单元格的输入来动态改变包含的字符，可以将单元格引用与文本连接符 `` 结合使用。

假设 E1 单元格输入了“果”，我们想计算产品类别包含 E1 内容的销售总额。

=SUMIF(A1:A6, "*"E1"*", C1:C6)

这将动态地匹配 A 列中包含“果”的单元格，并对 C 列进行求和。

5. SUMPRODUCT 函数作为替代

SUMPRODUCT 函数在某些情况下也可以用来实现条件求和，特别是当条件逻辑比较复杂时。虽然它不是专门为条件求和设计的，但其数组处理能力非常强大。

使用 SUMPRODUCT 实现“包含某个字符”的求和（与 SUMIF 类似）：

=SUMPRODUCT((ISNUMBER(SEARCH("苹果", A1:A5)))*(B1:B5))

公式解释：

• SEARCH("苹果", A1:A5)：在 A1:A5 区域的每个单元格中搜索“苹果”。如果找到，返回其起始位置（数字）；否则，返回错误值 #VALUE!。
• ISNUMBER(...)：检查 SEARCH 函数的返回值是否为数字。如果是数字（表示找到了“苹果”），则返回 TRUE；否则返回 FALSE。
• (ISNUMBER(...))*(B1:B5)：Excel 在进行数学运算时，会将 TRUE 视为 1，FALSE 视为 0。所以这一步相当于将 A 列符合条件的行（结果为 1）与 B 列对应的数值相乘，不符合条件的行（结果为 0）则乘积为 0。
• SUMPRODUCT(...)：对相乘后的数组进行求和。

SUMPRODUCT 在处理多条件时也非常灵活，可以实现 SUMIFS 的功能，并且在某些复杂的文本匹配和条件组合上可能提供更多的控制。但通常情况下，对于“包含某个字符”的简单条件求和，SUMIF 和 SUMIFS 函数更为直观和易用。

五、总结

在 Excel 中实现“包含某个字符”的条件求和，核心是掌握 SUMIF 和 SUMIFS 函数，并善于利用通配符 `*` 和 `?`。SUMIF 适用于单个条件，而 SUMIFS 适用于多个条件组合。通过将通配符置于待匹配文本的两侧（`"*字符*"`）可以实现包含匹配，置于一侧则实现开头或结尾匹配。当需要匹配包含通配符本身的文本时，记得使用波浪号 `~` 进行转义。对于大规模数据，可以考虑数据透视表或 Power Query。理解这些函数和技巧，将极大地提升您在 Excel 中进行数据分析和汇总的效率和准确性。