【原】再來一個(gè)你沒見過的VLOOKUP函數(shù)新技巧：模糊匹配

ExcelEasy 2022-10-21 發(fā)布于北京

展開全文

今天介紹一個(gè)實(shí)際問題的解決，這個(gè)問題來自于一個(gè)真實(shí)的案例。在解決過程中，我們使用了一個(gè)VLOOKUP函數(shù)的新技巧。

匹配場(chǎng)景分類和問題

我們進(jìn)行數(shù)據(jù)匹配的場(chǎng)景可以分為三類：

精確匹配
近似匹配
模糊匹配

其中，前兩種都很簡(jiǎn)單，只是VLOOKUP函數(shù)（或其他LOOKUP函數(shù)）最后一個(gè)參數(shù)的設(shè)置問題。

但是第三種匹配沒有類似的簡(jiǎn)單辦法。

比如，我們今天需要處理的問題就是這樣的：

左表是客戶簡(jiǎn)稱，右邊是客戶名稱，需要將客戶簡(jiǎn)稱和客戶名稱對(duì)應(yīng)起來。

對(duì)應(yīng)的原則是不言而明的，就像上圖中相同顏色對(duì)應(yīng)的條目一樣。

但是仔細(xì)分析就會(huì)發(fā)現(xiàn)，這些能對(duì)應(yīng)上的條目各有各的特殊情況：

博眾宏業(yè) - 湖北省博眾宏業(yè)商貿(mào)有限公司
這種情形比較簡(jiǎn)單，簡(jiǎn)稱是在全稱完全出現(xiàn)，并且是連續(xù)的（即中間沒有插入其他字符），順序一致。
南京康仁寧 - 江蘇南京市康仁寧
簡(jiǎn)稱在全稱中完全出現(xiàn)，順序也一致，但是不連續(xù)，中間插入了“市”
扶風(fēng)德福源 - 扶風(fēng)縣福德源副食門店
簡(jiǎn)稱在全稱中完全出現(xiàn)，但是順序不一致。但是根據(jù)常識(shí)判斷應(yīng)該是一家
綿陽永貞 - 四川永貞商貿(mào)有限公司
簡(jiǎn)稱在全稱中出現(xiàn)一部分，但是根據(jù)公司業(yè)務(wù)規(guī)則和常識(shí)判斷應(yīng)該是一家。

如果仔細(xì)分析完整的數(shù)據(jù)，還可以看到更多的細(xì)分情況，比如錯(cuò)別字，漏字等等。

在上面的各種情況中，除了第一種情況可以使用VLOOKUP的通配符匹配外，其余的都沒有辦法直接解決。

分析

一般來說，我們面臨的所有現(xiàn)實(shí)問題都是這樣的：

看上去是個(gè)匹配問題，應(yīng)該用VLOOKUP之類的函數(shù)，但是你會(huì)的函數(shù)肯定解決不了。

其實(shí)，基本上現(xiàn)實(shí)問題都需要我們從兩個(gè)方向努力，一方面對(duì)問題進(jìn)行分解和變形，希望能用上我們已經(jīng)掌握的技術(shù)，另一方面需要我們調(diào)動(dòng)我們的知識(shí)積累，組合這些知識(shí)，看看是否可以將其用于解決分解和變形之后的問題。

根據(jù)上面的分析，我們可以將前兩類歸為一類：簡(jiǎn)稱在全稱中完全出現(xiàn)，但是不一定是連續(xù)出現(xiàn)。

后兩類可以歸為一類：簡(jiǎn)稱在全稱中部分出現(xiàn)。

第一匹配嘗試

對(duì)于“博眾宏業(yè) - 湖北省博眾宏業(yè)商貿(mào)有限公司”類型的匹配來說，我們可以使用通配符匹配找到類似的結(jié)果：

=VLOOKUP(“*” & A2 & “*”, $F$2:$F$20, 1, 0)

那么,如果變成“博眾宏業(yè) - 博眾湖北省宏業(yè)商貿(mào)有限公司”的匹配模式，我們能否用通配符匹配的方式來完成呢？

更進(jìn)一步，我們能否在完成這種匹配的同時(shí)，也可以支持“博眾宏業(yè) - 湖北省博眾宏業(yè)商貿(mào)有限公司”類型的匹配呢？

可以的?。?！

只要我們將“博眾宏業(yè)”變成“博*眾*宏*業(yè)”，就完全可以使用通配符匹配了。

這就要求我們使用公式完成從“博眾宏業(yè)”到“博*眾*宏*業(yè)”的轉(zhuǎn)換。

只要看過我們前面講過的各種LET函數(shù)的案例文章，或者函數(shù)式編程的文章和視頻，這都不在話下。

完整公式如下：

=LET(    name1, A2,    names2, $F$2:$F$20,    name1arr, MID(name1, SEQUENCE(LEN(name1)),1),    name1exp,"*"& TEXTJOIN("*",1, name1arr) & "*",    IFERROR(VLOOKUP(name1exp, names2,1,0),""))

其中，

name1 - 當(dāng)前客戶簡(jiǎn)稱的單元格
names2 - 客戶全稱的列表
name1arr - 將name1拆分為單字符的數(shù)組
name1exp - 將name1arr合并成單一文本，用"*"作為分隔符，并且在首尾各添加一個(gè)“*”
最后用VLOOKUP的通配符匹配返回結(jié)果。

這是匹配后的結(jié)果，可以看到匹配成功了很多條目，這些條目完全符合我們的預(yù)期。

第二匹配嘗試

對(duì)于那些沒有匹配成功的條目，一定是簡(jiǎn)稱沒有全部在全稱里出現(xiàn)。此時(shí)我們一定要了解，再好的算法也只能做到盡可能匹配成功，而且，最終必須依靠人工才更加準(zhǔn)確。我們能做的就是在人工判斷時(shí)盡可能地提供方便。

所以，我們將第二匹配嘗試定位為：

推薦匹配結(jié)果

因?yàn)槲覀儫o法確認(rèn)匹配出的結(jié)果是否是真正的結(jié)果。所以作為推薦供人工選擇確認(rèn)。

至于方法，就用到了我們前面介紹的LCS（最長(zhǎng)公共子串，具體請(qǐng)參見Excel這個(gè)函數(shù)功能竟然暗合孫子兵法 - 詳說遞歸函數(shù)：什么是遞歸？遞歸能干什么？遞歸怎么做？）：

對(duì)于給定的簡(jiǎn)稱name1，計(jì)算其與全稱列表中的每一個(gè)全稱ns1的LCS長(zhǎng)度，并返回具有最長(zhǎng)LCS的那個(gè)全稱ns1。

比如，對(duì)于“綿陽永貞”，

與四川永貞商貿(mào)有限公司的LCS長(zhǎng)度為2
與湖北省博眾宏業(yè)商貿(mào)有限公司的LCS長(zhǎng)度為0
江蘇永盛商貿(mào)有限公司的LCS長(zhǎng)度為1

其中LCS最長(zhǎng)為2，所以返回“四川永貞商貿(mào)有限公司”作為推薦結(jié)果。

=LET(    name1, A2,    names2, $F$2:$F$498,    matchname, REDUCE("",names2,        LAMBDA(acc,a,            IF(                LCSLENGTH(a,name1)>LCSLENGTH(acc,name1),                a,                acc            )        )    ),    matchname)

其中，

name1 - 當(dāng)前處理的客戶簡(jiǎn)稱
names2 - 全稱列表
matchname - 通過REDUCE函數(shù)循環(huán)得到最長(zhǎng)的LCS對(duì)應(yīng)的客戶全稱。
LCSLENGTH - 計(jì)算LCS長(zhǎng)度，其定義參見Excel這個(gè)函數(shù)功能竟然暗合孫子兵法 - 詳說遞歸函數(shù)：什么是遞歸？遞歸能干什么？遞歸怎么做？

結(jié)果如下：

可以看到，第二匹配實(shí)際完全包含了第一匹配。可見第二匹配從原理上涵蓋了VLOOKUP模糊匹配的結(jié)果。

取舍

既然第二匹配涵蓋了第一匹配，能否只使用第二匹配呢？

一般來說，這么做是不合適的。原因有二：

使用VLOOKUP模糊匹配（即第一匹配）得到的結(jié)果可以完全確認(rèn)是正確結(jié)果。但是第二匹配中就不能保證。如果分開，只對(duì)第一匹配沒有得到結(jié)果的進(jìn)行第二匹配，這樣人工復(fù)核的工作量會(huì)小很多
仔細(xì)分析我們的算法就會(huì)知道，第二匹配實(shí)際上是對(duì)每個(gè)簡(jiǎn)稱循環(huán)處理所有的全稱，如果簡(jiǎn)稱是800條，全稱是1000條，這就是一個(gè)循環(huán)80萬次的計(jì)算。性能會(huì)非常差。如果采用這個(gè)方法，我們就需要進(jìn)行性能優(yōu)化，改進(jìn)這個(gè)算法。但是在這個(gè)例子中，因?yàn)橛写罅康臈l目可以通過第一匹配得出，所以只對(duì)沒有匹配出正確結(jié)果的條目應(yīng)用這個(gè)算法可以大幅地提高效率。

我們可以使用下面的公式：

=IF(B7<>"","",    LET(        name1, A7,        names2, $F$2:$F$498,        matchname, REDUCE("",names2,            LAMBDA(acc,a,                IF(                    LCSLENGTH(a,name1)>LCSLENGTH(acc,name1),                    a,                    acc                )            )        ),        matchname    ))

只是在前一個(gè)公式外面套用了IF，結(jié)果如下：