长文详解 令人头痛的Excel通配符 wps通配符

391 次浏览

免费提问加Q群341401932 加微信EXCEL880B领取免费教程

什么是通配符

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

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

“?”:代表任何的单个字符。

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

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

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

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

长文详解 令人头痛的Excel通配符 wps通配符插图

通配符在Excel中的应用主要有三处:查找替换(Ctrl+F),筛选,公式。

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

长文详解 令人头痛的Excel通配符 wps通配符插图1

通配符应用于查找

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

长文详解 令人头痛的Excel通配符 wps通配符插图2

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

长文详解 令人头痛的Excel通配符 wps通配符插图3

通配符应用于筛选

筛选“北京*”,匹配以北京开头的项;

筛选“*北京”, 匹配以北京结尾的项;

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

长文详解 令人头痛的Excel通配符 wps通配符插图4

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

长文详解 令人头痛的Excel通配符 wps通配符插图5

当我们要筛选1开头的数据时,不能直接用1,而要用”1*”

长文详解 令人头痛的Excel通配符 wps通配符插图6

通配符应用于函数

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

=COUNTIF(A2:A10,”北京*”)

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

=COUNTIF(A2:A10,C3)

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

剩下两个同理可推。

长文详解 令人头痛的Excel通配符 wps通配符插图7

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

  • 查询类

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

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

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

长文详解 令人头痛的Excel通配符 wps通配符插图8

条件类

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

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

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

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

=AVERAGEIF(A2:A9,"*"&"成都"&"*",B2:B9)

长文详解 令人头痛的Excel通配符 wps通配符插图9

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

=COUNTIFS(A2:A11,"AST?????",B2:B11,"GS??")

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

长文详解 令人头痛的Excel通配符 wps通配符插图10

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

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

=SUMIFS(C2:C13,A2:A13,"????12*",B2:B13,"*成都*")

“????12*”表示第4第5位为12,后续则不做任何限制;

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

长文详解 令人头痛的Excel通配符 wps通配符插图11

取消通配属性

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

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

=VLOOKUP("1m*2m",A2:B8,2,0)

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

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

=VLOOKUP("1m~*2m",A2:B8,2,0)

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

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

好了,今天的Excel小课堂就到此结束,大家赶紧实际操作联系一下吧,有不懂的问题可以留言问我!感谢关注Excel880,还请多多转发,持续关注我们呀!

买课程可进永久答疑群,课程可免费试学点击下方链接即可

郑广学Excel实战教程