Excel 通配符：COUNTIF、VLOOKUP 等函数中的 *、? 和 ~

Excel 在条件和查找中有三个通配符：* 匹配任意数量的字符（包括零个），? 匹配恰好一个字符，~ 把它后面的 * 或 ? 还原成普通字符。=COUNTIF(A2:A7,"*apple*") 统计任意位置包含 apple 的单元格。

公式重置

D2fx

=COUNTIF($A$2:$A$7,C2)

	A	B	C	D
1	Product		Pattern	Count
2	Apple juice		*apple*	4
3	Green apple		apple*	2
4	Pineapple		*juice	2
5	Orange juice		?????	0
6	Pear		*e	4
7	Apples

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

• *apple* 表示包含 apple：有 4 个匹配，因为 Pineapple 也算。
• apple* 表示以 apple 开头：只有 Apple juice 和 Apples。COUNTIF 不区分大小写。
• *juice 表示以 juice 结尾。
• ????? 表示恰好五个字符。这些产品中没有五个字符的，所以结果是 0；在 A6 中输入 Peach，计数就变成 1。
• *e 表示以 e 结尾。

在 C 列中输入你自己的模式，比如 *an* 或 P*，计数会随之更新。

VLOOKUP 和 XLOOKUP 中的模糊匹配

VLOOKUP 在精确匹配模式下（最后一个参数为 FALSE）支持通配符。在公式中把通配符和值连接起来，这样 D2 中只需输入开头的几个字母：

公式重置

E2fx

=VLOOKUP(D2&"*",A2:B6,2,FALSE)

	A	B	C	D	E
1	Product	Price		Starts with	Price
2	Apple juice	3.5		Pin	4
3	Green apple	1.2			4
4	Pineapple	4
5	Orange juice	3.2
6	Pear	0.9

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

两个公式都找到了 Pineapple。把 D2 改成 juice：VLOOKUP 的模式 juice* 要求文本以 juice 开头，所以返回 #N/A；而 XLOOKUP 的模式 *juice* 找到第一个包含它的产品 Apple juice。和所有精确匹配一样，通配符查找返回第一个符合条件的行，所以模式要写得足够具体。

只有当第五个参数 match_mode 为 2 时，XLOOKUP 才把 * 和 ? 当作通配符。否则它会按字面查找星号。MATCH 在 match_type 为 0 时支持通配符，XMATCH 则和 XLOOKUP 一样，在 match_mode 为 2 时支持。其余参数请看 VLOOKUP。

公式重置

D2fx

	A	B	C	D
1	Product			Phone products
2	Smartphone
3	Headphones
4	Phone stand
5	Laptop bag
6	Charger

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 在 D2 中统计名称任意位置包含 phone 的产品数量。

检查显示答案

用通配符求和与求平均

所有接受条件的函数都以相同的方式读取条件，所以同样的模式在 SUMIF、SUMIFS、AVERAGEIF、AVERAGEIFS、MAXIFS 和 MINIFS 中都适用：

公式重置

E2fx

=SUMIF(A2:A7,D2,B2:B7)

	A	B	C	D	E
1	Region	Sales		Pattern	Total
2	North-East	120		North*	285
3	North-West	95		*West	155
4	South	80		?????	150
5	South-West	60
6	East	110
7	North	70

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

North* 把所有以 North 开头的地区相加，包括 North 本身，因为 * 也可以匹配零个字符。????? 把恰好五个字符的地区相加：South 和 North。

公式重置

E2fx

	A	B	C	D	E
1	Region	Sales			West total
2	North-East	120
3	North-West	95
4	South	80
5	South-West	60
6	East	110
7	North	70

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 在 E2 中汇总名称以 West 结尾的所有地区的销售额。

检查显示答案

用 ~ 查找真正的星号或问号

要统计包含真正的 * 或 ? 的文本，就在它前面加一个波浪号。波浪号本身写作 ~~。

公式重置

C2fx

=COUNTIF(A2:A5,"*~**")

	A	B	C
1	Note		Count
2	Rated 5*		1
3	Why?		1
4	Done		4
5	5 stars

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

C2 统计包含字面星号的单元格（只有 A2），C3 统计包含问号的单元格。C4 展示了另一面：单独的 "*" 匹配任何文本，所以它统计所有文本单元格，这里是 4 个。它会跳过数字和空单元格，所以 COUNTIF(range,"*") 是统计文本单元格的常用方法。

哪些函数支持通配符

SEARCH 在公式中也支持通配符：在 Excel 中，=SEARCH("b?d","a bad day") 返回 3（详见 FIND 和 SEARCH）。对于 FILTER，请用 ISNUMBER(SEARCH(...)) 作为条件，而不是模式。

常见错误：在 = 后面使用通配符

= 运算符从不识别通配符：=A2="*apple*" 判断的是 A2 中是否正好是 *apple* 这七个字符。在 Excel 中，A2 为 Green apple 时：

=A2="*apple*"                    FALSE
=IF(A2="*apple*","yes","no")     no

请改为把判断放进 COUNTIF，它对单个单元格返回 1 或 0，或者使用 SEARCH：

公式重置

B2fx

=IF(COUNTIF(A2,"*apple*"),"contains apple","no")

	A	B	C
1	Product	COUNTIF test	SEARCH test
2	Green apple	contains apple	contains apple
3	Pear	no	no

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

IF 把 COUNTIF 返回的 1 当作 TRUE，把 0 当作 FALSE。SEARCH 的写法完全不需要通配符，因为 SEARCH 本身就会在单元格的任意位置查找文本。