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

我們進行數據匹配的場景可以分為三類：

1. 精確匹配

2. 近似匹配

3. 模糊匹配

其中，前兩種都很簡單，只是VLOOKUP函數（或其他LOOKUP函數）最后一個參數的設置問題。

但是第三種匹配沒有類似的簡單辦法。

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

左表是客戶簡稱，右邊是客戶名稱，需要將客戶簡稱和客戶名稱對應起來。

對應的原則是不言而明的，就像上圖中相同顏色對應的條目一樣。

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

• 博眾宏業(yè) - 湖北省博眾宏業(yè)商貿有限公司
  這種情形比較簡單，簡稱是在全稱完全出現(xiàn)，并且是連續(xù)的（即中間沒有插入其他字符），順序一致。

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

• 扶風德福源 - 扶風縣福德源副食門店
  簡稱在全稱中完全出現(xiàn)，但是順序不一致。但是根據常識判斷應該是一家

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

如果仔細分析完整的數據，還可以看到更多的細分情況，比如錯別字，漏字等等。

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

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

看上去是個匹配問題，應該用VLOOKUP之類的函數，但是你會的函數肯定解決不了。

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

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

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

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

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

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

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

可以的?。?！

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

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

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

完整公式如下：

=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 - 當前客戶簡稱的單元格

• names2 - 客戶全稱的列表

• name1arr - 將name1拆分為單字符的數組

• name1exp - 將name1arr合并成單一文本，用"*"作為分隔符，并且在首尾各添加一個“*”

• 最后用VLOOKUP的通配符匹配返回結果。

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

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

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

推薦匹配結果

因為我們無法確認匹配出的結果是否是真正的結果。所以作為推薦供人工選擇確認。

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

對于給定的簡稱name1，計算其與全稱列表中的每一個全稱ns1的LCS長度，并返回具有最長LCS的那個全稱ns1。

比如，對于“綿陽永貞”，

• 與四川永貞商貿有限公司的LCS長度為2

• 與湖北省博眾宏業(yè)商貿有限公司的LCS長度為0

• 江蘇永盛商貿有限公司的LCS長度為1

其中LCS最長為2，所以返回“四川永貞商貿有限公司”作為推薦結果。

=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 - 當前處理的客戶簡稱

• names2 - 全稱列表

• matchname - 通過REDUCE函數循環(huán)得到最長的LCS對應的客戶全稱。

• LCSLENGTH - 計算LCS長度，其定義參見Excel這個函數功能竟然暗合孫子兵法 - 詳說遞歸函數：什么是遞歸？遞歸能干什么？遞歸怎么做？

結果如下：

可以看到，第二匹配實際完全包含了第一匹配?？梢姷诙ヅ鋸脑砩虾w了VLOOKUP模糊匹配的結果。

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

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

1. 使用VLOOKUP模糊匹配（即第一匹配）得到的結果可以完全確認是正確結果。但是第二匹配中就不能保證。如果分開，只對第一匹配沒有得到結果的進行第二匹配，這樣人工復核的工作量會小很多

2. 仔細分析我們的算法就會知道，第二匹配實際上是對每個簡稱循環(huán)處理所有的全稱，如果簡稱是800條，全稱是1000條，這就是一個循環(huán)80萬次的計算。性能會非常差。如果采用這個方法，我們就需要進行性能優(yōu)化，改進這個算法。但是在這個例子中，因為有大量的條目可以通過第一匹配得出，所以只對沒有匹配出正確結果的條目應用這個算法可以大幅地提高效率。

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

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

只是在前一個公式外面套用了IF，結果如下：

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

關于這個問題，最后再說幾點：

• 對于這類問題，最好的結果也必須依靠人工參與

• 我們能做的是盡可能減少人工參與的工作量。這里還有改進空間，比如，根據LCS長度給出相應的“相似度”

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

• 也有完全基于LCS的方案，不過需要另外的設計，因為性能確實是個繞不過去的坎。

詳細解釋請看視頻