幕雪

excel通配符什么是、有哪些、在哪里、怎么用、为什么使用等实用疑问详解

什么是Excel通配符？它们有哪些类型？

Excel中的通配符是一种特殊的字符，它们不代表字面本身，而是代表一个或多个其他字符。使用通配符可以帮助用户进行灵活的文本匹配，无论是在查找、筛选数据时，还是在某些函数中设定条件时，都能极大地提高效率和灵活性。

Excel中的主要通配符类型：

Excel主要支持三种通配符：

星号 (*)

星号代表任意数量（包括零个）的任意字符。当你不知道一个文本字符串中间或两端是什么内容，但知道其中一部分固定文本时，可以使用星号作为占位符。

示例：

  • *公司：匹配所有以“公司”结尾的文本，例如“微软公司”、“苹果公司”、“XX咨询公司”等。
  • 张*：匹配所有以“张”开头的文本，例如“张三”、“张伟”、“张小丽”等。
  • *管理*：匹配所有包含“管理”两个字的文本，例如“项目管理”、“人力资源管理”、“管理部”等。
  • *：单独使用代表所有非空文本单元格。

问号 (?)

问号代表任意单个字符。当你确定某个位置只有一个不确定的字符时，可以使用问号。

示例：

  • 张?：匹配所有以“张”开头，后面紧跟一个任意字符的文本，例如“张飞”、“张伟”（如果名字只有两个字）等，但不匹配“张三丰”或“张”。
  • ？？年：匹配所有前面是两个任意字符，后面是“年”的文本，例如“20年”、“99年”、“乙亥年”等。
  • B??-XYZ：匹配以“B”开头，后面跟两个任意字符，然后是“-XYZ”的文本，例如“BAS-XYZ”、“B99-XYZ”等。

波浪号 (~)

波浪号是一个“转义字符”，它用于取消紧跟其后的通配符（*、?、~）的特殊含义，使它们被视为字面字符进行匹配。当你需要查找或匹配包含通配符本身的文本时，就必须使用波浪号。

示例：

  • ~*：查找或匹配字面上的星号“*”。
  • ~?：查找或匹配字面上的问号“?”。
  • ~~：查找或匹配字面上的波浪号“~”。

为什么要在Excel中使用通配符？

使用Excel通配符的主要原因是为了进行更灵活、更高效的数据处理。它们解决了精确匹配无法解决的问题。

提高查找和筛选效率

当你想找到或筛选出符合某种模式而不是精确文本的数据时，通配符是必不可少的工具。比如，找出所有部门名称包含“市场”的员工，或者找出所有订单编号以特定前缀开头的记录。

增强函数应用的灵活性

在许多需要基于条件进行计算或查找的函数中，通配符可以作为条件的一部分，使得函数能够处理更广泛的数据范围。例如，计算所有特定区域内，名称符合某种模式的项目的总和。

处理不精确或变化的数据

现实世界的数据往往不完全规范，可能存在细微的差异（如名称后面跟着不同的备注）。通配符可以帮助你模糊匹配这些数据，即使它们不完全一致，只要符合你的模式即可。

Excel通配符可以在哪些地方使用？

Excel通配符的应用范围非常广泛，主要集中在以下功能和函数中：

  • 查找和替换功能 (Find & Replace): 在“查找内容”或“替换为”框中使用。
  • 自动筛选和高级筛选 (AutoFilter & Advanced Filter): 在设置筛选条件时，特别是文本筛选中的“包含”、“开头是”、“结尾是”等选项。
  • 特定的Excel函数: 许多内置函数支持在它们的条件或查找参数中使用通配符。

需要注意的是：

并非所有的Excel功能都支持通配符。例如，在单元格直接输入通配符通常只会将其视为文本，而在基础数学运算、简单的单元格引用或一些不涉及文本模式匹配的函数（如SUM, AVERAGE, MAX, MIN, IF等）中，通配符也不会被解释为特殊字符。此外，某些文本函数（如FIND）也不支持通配符。

如何使用Excel通配符？

下面将通过具体的例子详细说明通配符在不同场景下的用法。

在“查找和替换”中使用通配符

这是通配符最直观的应用之一。打开“查找和替换”对话框（通常是按Ctrl+F或Ctrl+H）。

  • 查找所有包含“报表”的单元格： 在“查找内容”框输入 *报表*
  • 查找所有名字是两个字的单元格： 在“查找内容”框输入 ??
  • 查找所有以字母“A”开头，后面跟任意三个字符的编码： 在“查找内容”框输入 A???
  • 查找所有包含字面星号“*”的单元格： 在“查找内容”框输入 ~*
  • 将所有以“旧”开头的文本替换为“新”开头： 在“查找内容”框输入 旧*，在“替换为”框输入 新*。注意：替换时星号通常表示保留原星号代表的内容。例如，“旧数据”替换为“新数据”。如果你只想替换开头的“旧”，而保留后面的内容，则需要在“替换为”中引用被查找的部分，但这在简单的查找替换框中比较复杂，通常需要结合更高级的方法或函数。简单的理解是在查找替换框中，星号更多用于匹配而非直接用于替换后的文本。

在“筛选”中使用通配符

在数据区域启用自动筛选（“数据”选项卡 -> “筛选”）。点击列标题旁的下拉箭头，选择“文本筛选”，然后选择合适的条件，如“包含”、“开头是”、“结尾是”等。

  • 筛选出所有部门名称包含“销售”的记录： 选择“文本筛选” -> “包含”，输入 销售。实际上，Excel在这些文本筛选选项中会自动在你的输入内容前后加上通配符，所以输入销售即可。如果你需要更复杂的模式，例如“以A开头，以部结尾”，则需要选择“自定义筛选”并输入 =A*部 或 =A* 且 = *部。
  • 筛选出所有编码以“ABC”开头，后面跟任意两个字符的记录： 选择“文本筛选” -> “开头是”，输入 ABC??。
  • 筛选出所有恰好是5个字符长度的名称： 选择“文本筛选” -> “自定义筛选”，输入 =?????
  • 筛选出所有包含字面问号“?”的记录： 选择“文本筛选” -> “包含”，输入 ~?

在函数中使用通配符（重点及示例）

许多函数在它们的条件或查找参数中支持通配符，这使得这些函数的功能大大扩展。

COUNTIF / COUNTIFS / SUMIF / SUMIFS / AVERAGEIF / AVERAGEIFS

这些条件函数在它们的 `criteria` (条件) 参数中完美支持通配符。

  • 示例 1：计算所有以“张”开头的姓名个数

    假设姓名列表在A1:A100。

    =COUNTIF(A1:A100, "张*")

  • 示例 2：计算所有包含“有限”两字的公司销售总额

    假设公司名称在B1:B200，销售额在C1:C200。

    =SUMIF(B1:B200, "*有限*", C1:C200)

  • 示例 3：计算所有名字恰好是三个字的平均分数

    假设姓名在D1:D50，分数列在E1:E50。

    =AVERAGEIF(D1:D50, "???", E1:E50)

  • 示例 4：统计包含字面星号“*”的记录数量

    假设数据在F1:F50。

    =COUNTIF(F1:F50, "~*")

VLOOKUP / HLOOKUP / MATCH

这些查找和匹配函数可以在 `lookup_value` (查找值) 参数中使用通配符，但有一个重要前提：必须使用精确匹配模式。

  • 示例 5：在产品列表中查找第一个名称以“AA”开头的产品价格

    假设产品名称在A1:A10，价格在B1:B10。

    =VLOOKUP("AA*", A1:B10, 2, FALSE)

    注意： 这里的 FALSE (或 0) 参数是强制性的，表示精确匹配。虽然使用了通配符，但查找过程是找到与模式精确匹配的第一个项。如果省略 FALSE 或使用 TRUE，VLOOKUP会进行近似匹配，且不支持通配符。

  • 示例 6：查找列表C1:C50中与模式“B??-XYZ”匹配的第一个单元格位置（行号）

    =MATCH("B??-XYZ", C1:C50, 0)

    注意： 这里的 0 参数是强制性的，表示精确匹配。

SEARCH / FIND

这两个函数用于在文本字符串中查找子字符串的位置。

  • 重要区别： SEARCH 函数支持通配符，而 FIND 函数不支持通配符，它总是进行字面查找。

  • 示例 7：查找单元格A1中是否包含任意字符后紧跟“公司”的模式，并返回第一次出现的位置。

    =SEARCH("?公司", A1)

    如果A1是“XX公司”，则返回3。如果A1是“A公司”，则返回2。如果A1不包含这种模式，函数将返回错误值 #VALUE!。

  • 示例 8：查找单元格A1中第一个字面星号“*”的位置。

    必须使用转义字符波浪号。

    =SEARCH("~*", A1)

    或者，如果使用FIND（它不支持通配符）：

    =FIND("*", A1)

    注意：FIND在此例中可行是因为它不将星号视为通配符。但在你需要 *利用* 通配符模式匹配时，必须用SEARCH。

  • 示例 9：查找单元格A1中第一个字面问号“?”的位置。

    =SEARCH("~?", A1)

    或者：

    =FIND("?", A1)

  • 示例 10：查找单元格A1中第一个字面波浪号“~”的位置。

    需要用两个波浪号表示一个字面波浪号。

    =SEARCH("~~", A1)

    或者：

    =FIND("~", A1)

如何查找或使用字面上的通配符字符本身 (~的使用)

如前所述，当你需要查找或匹配包含星号（*）、问号（?）或波浪号（~）本身的数据时，必须在这些字符前面加上波浪号（~）作为转义字符。

  • 在查找/替换中查找字面“*”： 查找内容输入 ~*
  • 在筛选中包含字面“?”： 在文本筛选条件框输入 ~?
  • 在COUNTIF中统计包含字面“~”的单元格数量： 条件输入 "~~"
  • 在SEARCH中查找字面“*”的位置： 查找文本输入 "~*"

总结

Excel通配符（*、?、~）是进行灵活文本匹配的强大工具。掌握它们的用法，可以让你在处理数据时更加高效，无论是快速查找、筛选特定模式的数据，还是在条件函数和查找函数中构建灵活的条件。理解每个通配符的含义以及转义字符“~”的使用，是充分发挥它们潜力的关键。

excel通配符