1

簡(jiǎn)稱(chēng)查全稱(chēng)

=VLOOKUP("*"&D2&"*",A:B,2,0)

=SUMIF(A:A,"*"&D2&"*",B:B)

2

全稱(chēng)查簡(jiǎn)稱(chēng)

既然有根據(jù)簡(jiǎn)稱(chēng)查全稱(chēng)的情況，自然也就有根據(jù)全稱(chēng)找簡(jiǎn)稱(chēng)的問(wèn)題，這就是我要說(shuō)的第二個(gè)例子。

如上圖所示，A:B列依然是某班人員姓名和某科的高考成績(jī)。D列是部分人員的全稱(chēng)，現(xiàn)在，需要根據(jù)A：B列的信息，查詢D列人員的考試成績(jī)。

E2公式如下：

=LOOKUP(,-FIND(A$2:A$10,D2),B$2:B$10)

公式解析▼

{#VALUE!;#VALUE!;-1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

LOOKUP忽略錯(cuò)誤值，它的查找值為0，比查找范圍內(nèi)的任意數(shù)值均大，因而直接返回最后的數(shù)值所對(duì)應(yīng)的查找范圍（B2:B10）的數(shù)據(jù)（這個(gè)知識(shí)點(diǎn)可以參考LOOKUP篇函數(shù)教程）。

以D2單元格的空空女神為例，返回B4單元格的數(shù)據(jù)125。

……

說(shuō)到這里，細(xì)心的同學(xué)也許已經(jīng)發(fā)現(xiàn)，我舉的兩個(gè)例子，不管是簡(jiǎn)稱(chēng)查全稱(chēng)，還是全稱(chēng)找簡(jiǎn)稱(chēng)，都有一個(gè)最重要的規(guī)律，也就是每個(gè)簡(jiǎn)稱(chēng)都是全稱(chēng)完整的一部分，或者頭部，或者尾部，或者中間。比如，大花是【大花美女】的頭部，星光是【看見(jiàn)星光】的尾部。這么說(shuō)，似乎有點(diǎn)奇怪，反正就是這么個(gè)意思。

但假設(shè)有這樣一種情況，比如看見(jiàn)星光的簡(jiǎn)稱(chēng)是看星光，又如何通過(guò)簡(jiǎn)稱(chēng)找到全稱(chēng)看見(jiàn)星光呢？

之前的公式肯定是不成的。

打個(gè)響指，這就是我們要說(shuō)的第三種情況。 

3

亂序全字符串匹配

如上圖所示，A:B列依然是某班人員和某科的高考成績(jī)。D列是部分人員的簡(jiǎn)稱(chēng)，現(xiàn)在，需要根據(jù)A：B列的信息，在E列編寫(xiě)公式，查詢D列人員的考試成績(jī)。

比如，“透視表空空”和“講透視表的那個(gè)空空”是匹配的——透 視 表 空 空，每一個(gè)字符都出現(xiàn)于字符串講透視表的那個(gè)空空中。

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)個(gè)位置至第26(AZ)個(gè)位置分別截取1個(gè)字符；FIND函數(shù)判斷MID函數(shù)的返回結(jié)果在A1:A10單元格中是否存在，如果存在，返回位置序號(hào)，否則，返回錯(cuò)誤值，最后通過(guò)ISERR函數(shù)搭配減法運(yùn)算，將FIND函數(shù)的結(jié)果轉(zhuǎn)化為-1和0，構(gòu)成一個(gè)26列10行的矩陣數(shù)組。

MMULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26))

MMULT函數(shù)對(duì)矩陣數(shù)據(jù)進(jìn)行計(jì)算，當(dāng)D2單元格字符串的每一個(gè)字符都在A$1:A$10區(qū)域某個(gè)單元格中存在時(shí)，該行計(jì)算結(jié)果為0。

最后通過(guò)MATCH函數(shù)，取得MMULT函數(shù)返回結(jié)果首次為0的位置，再通過(guò)INDEX函數(shù)取值即可。

這個(gè)套路化公式的思路是判斷所查詢的字符串中每一個(gè)字符是否都在查找范圍內(nèi)存在，換句話說(shuō)，就是玩一個(gè)文字歸屬計(jì)數(shù)游戲。

那么，思考題來(lái)了，既然有這種情況的簡(jiǎn)稱(chēng)找全稱(chēng)，自然也有這種情況的全稱(chēng)找簡(jiǎn)稱(chēng)，如果碰到下面這種情況，如何書(shū)寫(xiě)公式呢？

根據(jù)D:E列數(shù)據(jù)，在B列編寫(xiě)公式計(jì)算成績(jī)..▼

儂自己想……

4

最大近似度匹配

第4個(gè)函數(shù)套路是處理最大近似度匹配……

如下圖所示，需要根據(jù)A:B列的數(shù)據(jù)源，獲取D列相關(guān)人名的語(yǔ)文考試成績(jī)。

          

=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)))

公式解析▼

公式首先采用精確匹配的方式進(jìn)行匹配VLOOKUP(D2,$A:$B,2,0），匹配不到結(jié)果之后，再采用最大近似度匹配。

SEARCH(MID(D2,TRANSPOSE(ROW(INDIRECT("1:"&LEN(D2))

使用SEARCH函數(shù)判斷D2字符串中的每一個(gè)值是否在數(shù)據(jù)源中存在（注意：SEARCH函數(shù)不區(qū)分字母大小寫(xiě)），生成一個(gè)矩陣數(shù)組。

然后使用MMULT函數(shù)統(tǒng)計(jì)矩陣中每一個(gè)字符串中字符出現(xiàn)的個(gè)數(shù)，再使用加權(quán)法，除以10000，同時(shí)用+ROW($2:$14)標(biāo)記行號(hào)，末了使用MAX函數(shù)從中獲取最大值。

最后使用INDEX函數(shù)根據(jù)行號(hào)按圖索驥獲取最終結(jié)果。

公式稍加修改，也可以實(shí)現(xiàn)指定標(biāo)準(zhǔn)的近似度匹配，比如80%的近似度匹配

……

別皺眉頭，笑一笑。復(fù)雜的數(shù)組公式理解與否其實(shí)并不重要，重要的是……知道有這么回事，遇見(jiàn)問(wèn)題直接復(fù)制公式粘貼使用——你開(kāi)心就好，認(rèn)真臉。

打個(gè)響指，最后再說(shuō)兩句，這年代拖堂的好老師真是不多了，給自己筆芯。

不管是采用模糊匹配，還是最大近似度匹配，都優(yōu)先推薦先使用完全匹配查詢，在查無(wú)結(jié)果的基礎(chǔ)上，再選擇其他匹配方式，以避免數(shù)據(jù)源存在完全匹配的結(jié)果，卻優(yōu)先匹配了模糊結(jié)果的問(wèn)題。

此外，函數(shù)公式從來(lái)都不是萬(wàn)能的，VBA代碼亦如是，正則也還是如此，關(guān)于簡(jiǎn)稱(chēng)和全稱(chēng)的查詢和統(tǒng)計(jì)，最理想的情況自然還是制作一張匹配表，規(guī)范數(shù)據(jù)源，從源頭上解決問(wèn)題。

比如碰到下面這樣的情況，若是沒(méi)有個(gè)匹配表，那是真沒(méi)轍。

看見(jiàn)星光   簡(jiǎn)稱(chēng)   帥哥

你說(shuō)，如果沒(méi)有個(gè)匹配表，誰(shuí)知道看見(jiàn)星光和帥哥匹配呢？攤手，聳肩，看見(jiàn)星光自己天天照鏡子都不知道??？

拱手作別，下期再見(jiàn)。