今天介紹一個實際問題的解決,這個問題來自于一個真實的案例。在解決過程中,我們使用了一個VLOOKUP函數的新技巧。 我們進行數據匹配的場景可以分為三類:
其中,前兩種都很簡單,只是VLOOKUP函數(或其他LOOKUP函數)最后一個參數的設置問題。 但是第三種匹配沒有類似的簡單辦法。 比如,我們今天需要處理的問題就是這樣的: 左表是客戶簡稱,右邊是客戶名稱,需要將客戶簡稱和客戶名稱對應起來。 對應的原則是不言而明的,就像上圖中相同顏色對應的條目一樣。 但是仔細分析就會發(fā)現(xiàn),這些能對應上的條目各有各的特殊情況:
如果仔細分析完整的數據,還可以看到更多的細分情況,比如錯別字,漏字等等。 在上面的各種情況中,除了第一種情況可以使用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),"")) 其中,
這是匹配后的結果,可以看到匹配成功了很多條目,這些條目完全符合我們的預期。 對于那些沒有匹配成功的條目,一定是簡稱沒有全部在全稱里出現(xiàn)。此時我們一定要了解,再好的算法也只能做到盡可能匹配成功,而且,最終必須依靠人工才更加準確。我們能做的就是在人工判斷時盡可能地提供方便。 所以,我們將第二匹配嘗試定位為: 推薦匹配結果 因為我們無法確認匹配出的結果是否是真正的結果。所以作為推薦供人工選擇確認。 至于方法,就用到了我們前面介紹的LCS(最長公共子串,具體請參見Excel這個函數功能竟然暗合孫子兵法 - 詳說遞歸函數:什么是遞歸?遞歸能干什么?遞歸怎么做?): 對于給定的簡稱name1,計算其與全稱列表中的每一個全稱ns1的LCS長度,并返回具有最長LCS的那個全稱ns1。 比如,對于“綿陽永貞”,
其中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) 其中,
結果如下: 可以看到,第二匹配實際完全包含了第一匹配??梢姷诙ヅ鋸脑砩虾w了VLOOKUP模糊匹配的結果。 既然第二匹配涵蓋了第一匹配,能否只使用第二匹配呢? 一般來說,這么做是不合適的。原因有二:
我們可以使用下面的公式: =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,結果如下: 只要人工瀏覽第二匹配并刪除不確定的即可。 關于這個問題,最后再說幾點:
詳細解釋請看視頻 |
|