【原】再来一个你没见过的VLOOKUP函数新技巧：模糊匹配

ExcelEasy 2022-10-21 发布于北京

展开全文

今天介绍一个实际问题的解决，这个问题来自于一个真实的案例。在解决过程中，我们使用了一个VLOOKUP函数的新技巧。

匹配场景分类和问题

我们进行数据匹配的场景可以分为三类：

精确匹配
近似匹配
模糊匹配

其中，前两种都很简单，只是VLOOKUP函数（或其他LOOKUP函数）最后一个参数的设置问题。

但是第三种匹配没有类似的简单办法。

比如，我们今天需要处理的问题就是这样的：

左表是客户简称，右边是客户名称，需要将客户简称和客户名称对应起来。

对应的原则是不言而明的，就像上图中相同颜色对应的条目一样。

但是仔细分析就会发现，这些能对应上的条目各有各的特殊情况：

博众宏业 - 湖北省博众宏业商贸有限公司
这种情形比较简单，简称是在全称完全出现，并且是连续的（即中间没有插入其他字符），顺序一致。
南京康仁宁 - 江苏南京市康仁宁
简称在全称中完全出现，顺序也一致，但是不连续，中间插入了“市”
扶风德福源 - 扶风县福德源副食门店
简称在全称中完全出现，但是顺序不一致。但是根据常识判断应该是一家
绵阳永贞 - 四川永贞商贸有限公司
简称在全称中出现一部分，但是根据公司业务规则和常识判断应该是一家。

如果仔细分析完整的数据，还可以看到更多的细分情况，比如错别字，漏字等等。

在上面的各种情况中，除了第一种情况可以使用VLOOKUP的通配符匹配外，其余的都没有办法直接解决。

分析

一般来说，我们面临的所有现实问题都是这样的：

看上去是个匹配问题，应该用VLOOKUP之类的函数，但是你会的函数肯定解决不了。

其实，基本上现实问题都需要我们从两个方向努力，一方面对问题进行分解和变形，希望能用上我们已经掌握的技术，另一方面需要我们调动我们的知识积累，组合这些知识，看看是否可以将其用于解决分解和变形之后的问题。

根据上面的分析，我们可以将前两类归为一类：简称在全称中完全出现，但是不一定是连续出现。

后两类可以归为一类：简称在全称中部分出现。

第一匹配尝试

对于“博众宏业 - 湖北省博众宏业商贸有限公司”类型的匹配来说，我们可以使用通配符匹配找到类似的结果：

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

那么,如果变成“博众宏业 - 博众湖北省宏业商贸有限公司”的匹配模式，我们能否用通配符匹配的方式来完成呢？

更进一步，我们能否在完成这种匹配的同时，也可以支持“博众宏业 - 湖北省博众宏业商贸有限公司”类型的匹配呢？

可以的！！！

只要我们将“博众宏业”变成“博*众*宏*业”，就完全可以使用通配符匹配了。

这就要求我们使用公式完成从“博众宏业”到“博*众*宏*业”的转换。

只要看过我们前面讲过的各种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的通配符匹配返回结果。

这是匹配后的结果，可以看到匹配成功了很多条目，这些条目完全符合我们的预期。

第二匹配尝试

对于那些没有匹配成功的条目，一定是简称没有全部在全称里出现。此时我们一定要了解，再好的算法也只能做到尽可能匹配成功，而且，最终必须依靠人工才更加准确。我们能做的就是在人工判断时尽可能地提供方便。

所以，我们将第二匹配尝试定位为：

推荐匹配结果

因为我们无法确认匹配出的结果是否是真正的结果。所以作为推荐供人工选择确认。

至于方法，就用到了我们前面介绍的LCS（最长公共子串，具体请参见Excel这个函数功能竟然暗合孙子兵法 - 详说递归函数：什么是递归？递归能干什么？递归怎么做？）：

对于给定的简称name1，计算其与全称列表中的每一个全称ns1的LCS长度，并返回具有最长LCS的那个全称ns1。

比如，对于“绵阳永贞”，

与四川永贞商贸有限公司的LCS长度为2
与湖北省博众宏业商贸有限公司的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函数循环得到最长的LCS对应的客户全称。
LCSLENGTH - 计算LCS长度，其定义参见Excel这个函数功能竟然暗合孙子兵法 - 详说递归函数：什么是递归？递归能干什么？递归怎么做？

结果如下：

可以看到，第二匹配实际完全包含了第一匹配。可见第二匹配从原理上涵盖了VLOOKUP模糊匹配的结果。

取舍

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

一般来说，这么做是不合适的。原因有二：

使用VLOOKUP模糊匹配（即第一匹配）得到的结果可以完全确认是正确结果。但是第二匹配中就不能保证。如果分开，只对第一匹配没有得到结果的进行第二匹配，这样人工复核的工作量会小很多
仔细分析我们的算法就会知道，第二匹配实际上是对每个简称循环处理所有的全称，如果简称是800条，全称是1000条，这就是一个循环80万次的计算。性能会非常差。如果采用这个方法，我们就需要进行性能优化，改进这个算法。但是在这个例子中，因为有大量的条目可以通过第一匹配得出，所以只对没有匹配出正确结果的条目应用这个算法可以大幅地提高效率。

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

=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，结果如下：