Excel中这两类数据如何匹配?Excel中如何进行模糊匹配?Excel如何匹配模糊的两张表?这一类问题在学习群中被提及的频率非常高,主要的场景有:
- 表A的数据是精确的匹配模糊的表B;
- 表A的数据是模糊的匹配精确的表B;
- 表A和表B的数据都是模糊的进行匹配;
看着是不是非常的绕?其实99%的概率这些场景你都碰到过。
- 01 -
单向模糊匹配
首先来看下简单场景下的模糊匹配,即匹配的两张表中有一张是精确的,例如下方的「产品销量表」和「产品单价表」。
其中「产品销量表」的产品是精准缩减版的,而另外一张表的产品则附加有其他的信息:
问题来了, 如何在「产品销量表」中匹配出「单价」,其实在Excel中存在这一个通配符「*」。
我们可以利用这个符号配合VLOOKUP函数快速来实现单向的模糊匹配。
公式:=VLOOKUP("*"&A2&"*",E:F,2,0)
非常轻松就完成了,这里表示什么意思呢?其实理解也不难。
使用&连接符号将「*」和「检索关键字」连接在一起。
字符「产品A」就变成了「*产品A*」,这样就可以实现模糊匹配了。
另外来讲解下新手在这里经常会犯的一个错误就是写成下面这几种:
- =VLOOKUP(*A2*,E:F,2,0),不使用&连接;
- =VLOOKUP(A2,E:F,2,1),改变为模糊匹配就是真正的模糊匹配?
这里的「*」是字符串,连接需要使用&符号。
而VLOOKUP的最后一个参数设置为1,实际上是数值从小到大的模糊匹配。
跟文本的这种模糊匹配没有任何关系,使用限制也非常多。
- 02 -
双向模糊匹配
了解完单向的简单模糊匹配,我们使用VLOOKUP函数+星号就可以轻松完成。
如果两张表都是「模糊」的呢?就像下面这种:
「销量表」的部分产品带有前缀,而「单价表」的部分产品带有尺码信息,那么像这种表一般如何匹配呢?
这种情况下,函数一般就非常难解决了,而且兼容性差,一般情况下都使用VBA来完成,但是写代码门槛比较高。
其实在PQ中也有一项「模糊匹配」的功能,可以快速实现这个需求,首先将两张表导入到BI中,如下:
接着在任意一张表中点击「主页」选项卡下的「将查询合并为新查询」,操作两张表的匹配字段,同时勾选「模糊匹配」。
在「模糊匹配选项」中可以看到这里有一些参数需要我们配置,其中最重要的就是「阈值」。
这里的「阈值」是什么意思呢?比如「产品A」和「产品A-代码」的匹配度是多少呢?有3个字符是匹配的,而总字符是6个。
所以匹配度是50%,我们需要简单计算下两张表的匹配阈值,这里我们填写0.5。
然后点击确定,将「销量」筛选出来:
现在是不是就轻松完成了两张模糊表格之间的双向匹配,是不是非常简单呢?一行代码都没写,10秒钟不到,搞定。
这个功能只有在Power BI中才有,如果是Excel中的PQ,是没有这个功能的,仍然需要使用VBA。
另外不要因为有模糊匹配功能,就可以对源数据不加约束,依然应该尽量规范你的数据源,能保持一致最好。