Excel中的通配符的使用技巧大全
今天为大家介绍一下Excel中的通配符的使用技巧。其中的一些例子在某些场景下有非常巧妙的应用,特别是一些使用通配符的函数。
本文的主要内容包括:
Excel中的通配符
使用通配符进行查找替换
在筛选中使用通配符
在公式中使用通配符
如何查找真正的"*"和"?"
01
Excel中的通配符
通配符就是一个字符,这个字符用来代替其他的任意字符。在Excel中有两个通配符:
星号(*)——用来代替任意多的任意字符
问号(?)——用来代替一个任意字符
下面是一些使用通配符的例子和相应的含义:
"a*"——代表以“a”开头的任意长度的字符串,例如"a","abcd","a1","a_134bca"等都可以匹配“a*”
"*ab"——代表以"ab"结尾的任意长度的字符串,例如"ab","1dab","abab"."2a 3b ab"都可以匹配"*ab"
"*ab*"——代表所有包含"ab"的字符串,例如"ab","2ab","ab3","earab431"都可以匹配"*ab*"
"a?"——代表以"a"开头的两位长度的字符串,例如"a1","aa","ab"都匹配"a?",但是"a","abc"都不能匹配成功
"?a"——代表以"a"结束的两位长度的字符串,例如"ba","aa","3a"都可以匹配"?a",但是"a","aaa","bba"都不能匹配成功
特别的,可以只使用通配符。比如:
?——单独使用一个问号,表示任意一个字符
??——表示任意两个字符
*——表示任意长度的字符串(可以为空字符串)
02
使用通配符进行查找替换
在查找替换功能中可以使用通配符:
例如,在上面的例子中,我们通过"??-?"匹配所有的四位字符串,并且第三位是“-”。
而如果使用“*”来进行替换操作:
会把所有的单元格中的内容替换成为“E学会”。
03
在筛选中使用通配符
在筛选中同样可以使用通配符:
在这里,我们就可以筛选出那些包含“a"的产品名称。
要强调的是无论查找,替换,还是筛选,都是大小写不敏感的。
04
在公式中使用筛选
Excel有非常多的公式中支持通配符。下面是大部分常用的支持通配符的函数:
SUMIFS, SUMIF, COUNTIFS, COUNTIF, AVERAGEIFS AVERAGEIF, VLOOKUP, MATCH, SEARCH
下面是一些使用通配符的公式的例子。
=COUNTIF(B2:B9,"*a*")
计算所有包含"a"的单元格的个数。
注 在计算是否有重复的身份证的例子中,我们就必须使用带通配符的COUNTIF公式。(参见【UN】Excel中身份证处理场景和方法全集!)
有比如,公式:
=VLOOKUP(B2&"*",E3:F5,2,0)
MATCH函数同样可以这么用。比如:
Match函数和通配符结合还可以解决一个特殊的查找场景:
如何返回第一条非空的行号:
非空就是包含任意的值,而任意的值可以使用通配符“*”,因此我们可以尝试下面的公式:
=MATCH("*",C:C,0)
结果OK✌。
但是这个公式是有缺陷的。我们前面讲过,星号代表任意长度的任意字符串,也就是如果是个零长度的字符串,比如我们把C4单元格改成公式:
=""
从而,C4单元格被插入了一个零长度的字符串。从通常意义下,我们认为C4是空单元格,但是返回结果仍然表示第四行是非空的:
这个需求应该使用公式:
=MATCH("?*",C:C,0)
之所以不适用"?",是因为?代表一个字符组成的字符串。而?*就表示所有多于1个字符的字符串,也就是所有非空的单元格。
05
如何查找真正的?和*
有时我们的文本中会包含真正的*和?,他们不能当作通配符使用。但是由于Excel已经把它们当成通配符了,就会出现意料之外的结果:
实际上我们只想找到第一个(C2)单元格。但是由于*是通配符,导致另外一个a-1也被找到了。
在这种情况下,我们可以使用一个特殊符号:
~
可以使用
这样就可以得到想要的结果了。
如果你的文本中包含~,就在查找中使用两个~:
如果在使用公式的场景下,出现这种情况,也可以使用这个方法:
好了,今天的分享就到这里了,赶紧用起来吧!