一文读懂Excel通配符

Excel中一共有三种类型的通配符，分别为“*”，“？”，“~”。

“*”：代表任何的字符。

“？”：代表任何的单个字符。

“~”：代表解除字符的通配性。

Excel笔记：通配符要在英文状态下输入

通配符的作用是进行模糊的查找或者部分替代。

例如你要查找一个人，只记得姓”张”，最后一个字是”丰”，中间不确定的部分可以用通配符“*”代替，用“张*丰”去筛选查找。

除了记得第一个和最后一个字，还记得是四个字的名字，那么中间两个字可以用通配符”??”来代替，用“张??丰”去筛选查找，中间是一个字的这部分被排除掉。

通配符在Excel中的应用主要有三处：查找替换（Ctrl+F）,筛选，公式。

查找中的匹配规则和筛选/公式略有不同。以如下关键字“北京”为例：

通过查找对话框分别查找以下三个值：“北京*”，“*北京”，“*北京*”，其结果完全一样，只要包含“北京”的单元格都会被查找。

查找“北京*北京”时，只要包含2个“北京”的单元格都会被查找。

筛选“北京*”，匹配以北京开头的项；

筛选“*北京”， 匹配以北京结尾的项；

筛选“*北京*”， 匹配包含北京的项。

筛选“北京*北京”，匹配以北京开头和北京结尾的项。注意，这和应用于查找不一样，“离开北京的老北京人”不能被匹配，因为结构上不满足以北京开头，以北京结尾。

要筛选1开头的数据，不能直接用1，而要用”1*”

如下所示，COUNTIF可以直观地看出函数中的匹配规则和筛选完全一致。

对“北京*”计数，匹配以北京开头的3项；

对“*北京”计数，匹配以北京结尾的3项；

剩下两个同理可推。

支持通配符的函数大概有以下几类：

查询类

VLOOKUP,HLOOKUP,XLOOKUP,MATCH,XMATCH,SEARCH,SEARCHB.

Excel笔记：XLOOKUP和XMATCH使用通配符时需对特定参数进行设置。

如下案例中，XLOOKUP的第五参数需要设置为”2”.

=XLOOKUP("察布"&"*",A2:A15,A2:A15,,2)

条件类

诸如条件求和，条件计数，条件平均等函数，“条件”中可以包含通配符进行模糊匹配运算。

包括：SUMIF,SUMIFS,COUNTIF,COUNTIFS,AVERAGEIF,

AVERAGEIFS,DPRODUCT,DSTDEVP,DSUM,MAXIFS,MINIFS等。

如要对包含关键字“成都”的数值求平均值：

“?”可以进一步指定不确定部分的字符数，如下案例中，需要查询工号5位数，等级大于9的员工数量。

COUNTIFS的第一个条件“AST?????”限定AST后必须是5个数字，”GS??”则排除等级小于10的部分。

求和函数的应用十分广泛，SUMIFS可以根据多个条件求和，所有条件都可以使用通配符。

如下案例中的条件是“12月“和”成都“：

"????12*"表示第4第5位为12，后续则不做任何限制；

"*成都*"包含关键字“成都“。

众所周知，任何一个符号不可能只为一个作用单独存在，*也可以表示乘法，或作为特殊的分隔符号存在。

如下案例中，VLOOKUP的查询结果显然是错误的，公式中的”*”被当作通配符使用，但实际上它只是一个分隔符而已。

此时需要用到另一个特殊符号：波浪号”~”.

Excel笔记：”~”用于解除通配符（”*”和”?”）的统配属性。

在上述公式中的”*”前加上”~”,此时的”*”不再被当作通配符使用:

统配的本质可以概括为模糊查找。

查询信息中部分明确，部分模糊，就要考虑通配符了。