1
簡稱查全稱
=VLOOKUP("*"&D2&"*",A:B,2,0)=SUMIF(A:A,"*"&D2&"*",B:B)2
全稱查簡稱
既然有根據(jù)簡稱查全稱的情況，自然也就有根據(jù)全稱找簡稱的問題，這就是我要說的第二個例子。
如上圖所示，A:B列依然是某班人員姓名和某科的高考成績。D列是部分人員的全稱，現(xiàn)在，需要根據(jù)A：B列的信息，查詢D列人員的考試成績。
E2公式如下：
=LOOKUP(,-FIND(A$2:A$10,D2),B$2:B$10)公式解析▼
{#VALUE!;#VALUE!;-1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}LOOKUP忽略錯誤值，它的查找值為0，比查找范圍內的任意數(shù)值均大，因而直接返回最后的數(shù)值所對應的查找范圍（B2:B10）的數(shù)據(jù)（這個知識點可以參考LOOKUP篇函數(shù)教程）。
以D2單元格的空空女神為例，返回B4單元格的數(shù)據(jù)125。
……
說到這里，細心的同學也許已經(jīng)發(fā)現(xiàn)，我舉的兩個例子，不管是簡稱查全稱，還是全稱找簡稱，都有一個最重要的規(guī)律，也就是每個簡稱都是全稱完整的一部分，或者頭部，或者尾部，或者中間。比如，大花是【大花美女】的頭部，星光是【看見星光】的尾部。這么說，似乎有點奇怪，反正就是這么個意思。
但假設有這樣一種情況，比如看見星光的簡稱是看星光，又如何通過簡稱找到全稱看見星光呢？
之前的公式肯定是不成的。
打個響指，這就是我們要說的第三種情況。
3
亂序全字符串匹配
如上圖所示，A:B列依然是某班人員和某科的高考成績。D列是部分人員的簡稱，現(xiàn)在，需要根據(jù)A：B列的信息，在E列編寫公式，查詢D列人員的考試成績。
比如，“透視表空空”和“講透視表的那個空空”是匹配的——透 視 表 空 空，每一個字符都出現(xiàn)于字符串講透視表的那個空空中。
E2公式如下：
=INDEX(B:B,MATCH(,MMULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26)),))
公式解析▼
-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10))
MID函數(shù)從D2單元格的第1(A)個位置至第26(AZ)個位置分別截取1個字符；FIND函數(shù)判斷MID函數(shù)的返回結果在A1:A10單元格中是否存在，如果存在，返回位置序號，否則，返回錯誤值，最后通過ISERR函數(shù)搭配減法運算，將FIND函數(shù)的結果轉化為-1和0，構成一個26列10行的矩陣數(shù)組。
MMULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26))
MMULT函數(shù)對矩陣數(shù)據(jù)進行計算，當D2單元格字符串的每一個字符都在A$1:A$10區(qū)域某個單元格中存在時，該行計算結果為0。
最后通過MATCH函數(shù)，取得MMULT函數(shù)返回結果首次為0的位置，再通過INDEX函數(shù)取值即可。
這個套路化公式的思路是判斷所查詢的字符串中每一個字符是否都在查找范圍內存在，換句話說，就是玩一個文字歸屬計數(shù)游戲。
那么，思考題來了，既然有這種情況的簡稱找全稱，自然也有這種情況的全稱找簡稱，如果碰到下面這種情況，如何書寫公式呢？
根據(jù)D:E列數(shù)據(jù)，在B列編寫公式計算成績..▼
儂自己想……
4
最大近似度匹配
第4個函數(shù)套路是處理最大近似度匹配……
如下圖所示，需要根據(jù)A:B列的數(shù)據(jù)源，獲取D列相關人名的語文考試成績。
=IFERROR(VLOOKUP(D2,$A:$B,2,0),INDEX(B:B,RIGHT(MAX(MMULT(1-ISERR(SEARCH(MID(D2,TRANSPOSE(ROW(INDIRECT("1:"&LEN(D2)))),1),$A$2:$A$14)),ROW(INDIRECT("1:"&LEN(D2)))^0)/1%%+ROW($2:$14)),3)))公式解析▼
公式首先采用精確匹配的方式進行匹配VLOOKUP(D2,$A:$B,2,0），匹配不到結果之后，再采用最大近似度匹配。
SEARCH(MID(D2,TRANSPOSE(ROW(INDIRECT("1:"&LEN(D2))
使用SEARCH函數(shù)判斷D2字符串中的每一個值是否在數(shù)據(jù)源中存在（注意：SEARCH函數(shù)不區(qū)分字母大小寫），生成一個矩陣數(shù)組。
然后使用MMULT函數(shù)統(tǒng)計矩陣中每一個字符串中字符出現(xiàn)的個數(shù)，再使用加權法，除以10000，同時用+ROW($2:$14)標記行號，末了使用MAX函數(shù)從中獲取最大值。
最后使用INDEX函數(shù)根據(jù)行號按圖索驥獲取最終結果。
公式稍加修改，也可以實現(xiàn)指定標準的近似度匹配，比如80%的近似度匹配
……
別皺眉頭，笑一笑。復雜的數(shù)組公式理解與否其實并不重要，重要的是……知道有這么回事，遇見問題直接復制公式粘貼使用——你開心就好，認真臉。
打個響指，最后再說兩句，這年代拖堂的好老師真是不多了，給自己筆芯。
不管是采用模糊匹配，還是最大近似度匹配，都優(yōu)先推薦先使用完全匹配查詢，在查無結果的基礎上，再選擇其他匹配方式，以避免數(shù)據(jù)源存在完全匹配的結果，卻優(yōu)先匹配了模糊結果的問題。
此外，函數(shù)公式從來都不是萬能的，VBA代碼亦如是，正則也還是如此，關于簡稱和全稱的查詢和統(tǒng)計，最理想的情況自然還是制作一張匹配表，規(guī)范數(shù)據(jù)源，從源頭上解決問題。
比如碰到下面這樣的情況，若是沒有個匹配表，那是真沒轍。
看見星光 簡稱 帥哥
你說，如果沒有個匹配表，誰知道看見星光和帥哥匹配呢？攤手，聳肩，看見星光自己天天照鏡子都不知道??？
拱手作別，下期再見。
聯(lián)系客服
