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

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

1. 精確匹配

2. 近似匹配

3. 模糊匹配

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

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

比如，我們今天需要處理的問(wèn)題就是這樣的：

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

對(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ù)的（即中間沒(méi)有插入其他字符），順序一致。

• 南京康仁寧 - 江蘇南京市康仁寧
  簡(jiǎn)稱在全稱中完全出現(xiàn)，順序也一致，但是不連續(xù)，中間插入了“市”

• 扶風(fēng)德福源 - 扶風(fēng)縣福德源副食門(mén)店
  簡(jiǎn)稱在全稱中完全出現(xiàn)，但是順序不一致。但是根據(jù)常識(shí)判斷應(yīng)該是一家

• 綿陽(yáng)永貞 - 四川永貞商貿(mào)有限公司
  簡(jiǎn)稱在全稱中出現(xiàn)一部分，但是根據(jù)公司業(yè)務(wù)規(guī)則和常識(shí)判斷應(yīng)該是一家。

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

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

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

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

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

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

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

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

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

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

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

可以的！?。?/span>

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

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

只要看過(guò)我們前面講過(guò)的各種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ì)于那些沒(méi)有匹配成功的條目，一定是簡(jiǎn)稱沒(méi)有全部在全稱里出現(xiàn)。此時(shí)我們一定要了解，再好的算法也只能做到盡可能匹配成功，而且，最終必須依靠人工才更加準(zhǔn)確。我們能做的就是在人工判斷時(shí)盡可能地提供方便。

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

推薦匹配結(jié)果

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

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

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

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

• 與四川永貞商貿(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 - 通過(guò)REDUCE函數(shù)循環(huán)得到最長(zhǎng)的LCS對(duì)應(yīng)的客戶全稱。

• LCSLENGTH - 計(jì)算LCS長(zhǎng)度，其定義參見(jiàn)Excel這個(gè)函數(shù)功能竟然暗合孫子兵法 - 詳說(shuō)遞歸函數(shù)：什么是遞歸？遞歸能干什么？遞歸怎么做？

結(jié)果如下：

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

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

一般來(lái)說(shuō)，這么做是不合適的。原因有二：

1. 使用VLOOKUP模糊匹配（即第一匹配）得到的結(jié)果可以完全確認(rèn)是正確結(jié)果。但是第二匹配中就不能保證。如果分開(kāi)，只對(duì)第一匹配沒(méi)有得到結(jié)果的進(jìn)行第二匹配，這樣人工復(fù)核的工作量會(huì)小很多

2. 仔細(xì)分析我們的算法就會(huì)知道，第二匹配實(shí)際上是對(duì)每個(gè)簡(jiǎn)稱循環(huán)處理所有的全稱，如果簡(jiǎn)稱是800條，全稱是1000條，這就是一個(gè)循環(huán)80萬(wàn)次的計(jì)算。性能會(huì)非常差。如果采用這個(gè)方法，我們就需要進(jìn)行性能優(yōu)化，改進(jìn)這個(gè)算法。但是在這個(gè)例子中，因?yàn)橛写罅康臈l目可以通過(guò)第一匹配得出，所以只對(duì)沒(méi)有匹配出正確結(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é)果如下：

只要人工瀏覽第二匹配并刪除不確定的即可。

關(guān)于這個(gè)問(wèn)題，最后再說(shuō)幾點(diǎn)：

• 對(duì)于這類問(wèn)題，最好的結(jié)果也必須依靠人工參與

• 我們能做的是盡可能減少人工參與的工作量。這里還有改進(jìn)空間，比如，根據(jù)LCS長(zhǎng)度給出相應(yīng)的“相似度”

• 在最后一個(gè)公式中，其實(shí)還可以在全稱列表中去掉大寫(xiě)匹配成功的條目，又可以大幅提高效率。

• 也有完全基于LCS的方案，不過(guò)需要另外的設(shè)計(jì)，因?yàn)樾阅艽_實(shí)是個(gè)繞不過(guò)去的坎。

詳細(xì)解釋請(qǐng)看視頻