Excel中一共有三種類型的通配符,分別為"*","?","~".
"*":代表任何的字符。
"?":代表任何的單個字符。
"~":代表解除字符的通配性。
Excel筆記:通配符要在英文狀態下輸入。
通配符的作用是進行模糊的查找或者部分替代。
例如你要查找一個人,只記得姓」張」,最後一個字是」豐」,中間不確定的部分可以用通配符"*"代替,用"張*豐"去篩選查找。
除了記得第一個和最後一個字,還記得是四個字的名字,那麼中間兩個字可以用通配符"??"來代替,用"張??豐"去篩選查找。
通配符在Excel中的應用主要有三處:查找替換(Ctrl+F),篩選,公式。
查找中的匹配規則和篩選/公式略有不同。以如下關鍵字「北京」為例:
通配符應用於查找
分別查找以下三個值:「北京*」,「*北京」,「*北京*」,其結果完全一樣,只要包含「北京」的單元格都會被查找。
查找「北京*北京」時,只要包含2個「北京」的單元格都會被查找。
通配符應用於篩選
篩選「北京*」,匹配以北京開頭的項;
篩選「*北京」, 匹配以北京結尾的項;
篩選「*北京*」, 匹配包含北京的項。
篩選「北京*北京」,匹配以北京開頭和北京結尾的項。
注意,這和應用於查找不一樣,「離開北京的老北京人」不能被匹配,因為結構上不滿足以北京開頭,以北京結尾。
當我們要篩選「1」開頭的數據時,需要用「1*」,而不是直接用「1」.
通配符應用於函數
如下所示,COUNTIF可以直觀地看出函數中的匹配規則和篩選完全一致。
=COUNTIF(A2:A10,"北京*")
對「北京*」計數,匹配以北京開頭的3項;
=COUNTIF(A2:A10,C3)
對「*北京」計數,匹配以北京結尾的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等。
如要對包含關鍵字「成都」的數值求平均值:
=AVERAGEIF(A2:A9,"*"&"成都"&"*",B2:B9)
"?"可以進一步指定不確定部分的字符數,如下案例中,需要查詢工號5位數,等級大於9的員工數量。
=COUNTIFS(A2:A11,"AST?????",B2:B11,"GS??")
COUNTIFS的第一個條件"AST?????"限定AST後必須是5個數字,"GS??"則排除等級小於10的部分。
求和函數的應用十分廣泛,SUMIFS可以根據多個條件求和,所有條件都可以使用通配符。
如下案例中的條件是「12」月和「成都」:
=SUMIFS(C2:C13,A2:A13,"????12*",B2:B13,"*成都*")
"????12*"表示第4第5位為12,後續則不做任何限制;
"*成都*"包含關鍵字「成都「。
取消通配屬性
眾所周知,任何一個符號不可能只為一個作用單獨存在,"*"也可以表示乘法,或作為特殊的分隔符號存在。
如下案例中,VLOOKUP的查詢結果顯然是錯誤的,公式中的"*"被當作通配符使用,但實際上它只是一個分隔符而已。
=VLOOKUP("1m*2m",A2:B8,2,0)
此時需要用到另一個特殊符號:波浪號"~".
Excel筆記:」~」用於解除通配符(」*」和」?」)的統配屬性。
在上述公式中的"*"前加上"~",此時的"*"不再被當作通配符使用:
=VLOOKUP("1m~*2m",A2:B8,2,0)
通配符的本質可以概括為模糊查找。
查詢信息中部分明確,部分模糊,就要考慮通配符了。