数据模糊匹配或相似度匹配函数公式实现及思路解析

胡剑0227

模糊匹配工具-阶段备份.zip (11.89 KB, 下载次数: 9241)

2013-8-22 16:42 上传

点击文件名下载附件

【应用场景】   两列具有相同属性的数据（比如都是酒店名），由于来源不一致等原因使得同一对象在文字表述上存在不规则的差异（比如同一酒店一边的名字为“杭州开元名都大酒店”，而另一边的名字为“开元名都酒店”），即不是简单的头、尾缺失的问题。目前需要从对方一列中为“杭州开元名都大酒店”返回对应的匹配项“开元名都酒店”，这可以由本函数公式工具实现。

【使用指南】   如图2所示，打开附件工作簿后会看到【数据A】工作表和【数据B】工作表，然后在具体的区域粘贴实际需要比对的数据即可，具体操作如下。

   步骤1    在【工作表B】的【B】区域粘贴比对时需要返回的数据，对应【应用场景】中“开元名都酒店”数据所在列。需确保所有数据成为自动扩展的表格的数据部分。
   步骤2    在【工作表A】的【A】区域粘贴需要比对的数据，对应【应用场景】中“杭州开元名都大酒店”数据所在列，此时在【C】区域将自动由函数公式返回匹配上的数据。即，根据【A】区域中对应的某一数据在【B】区域中进行搜索，最后返回疑似匹配的数据。

图2 附件工作簿各工作表区域描述

       【参数说明】D1单元格，输入数字确保不小于【A】区域最长文本的字符长度，如果过小则匹配不精确，过大将影响效率。
                         D3单元格，匹配强度，数值越大匹配越严谨，实际情况可以根据【C】区域的匹配情况进行调整。
=====================================================================================
附件说明：
1楼附件是普通版本，主要是根据单个字符的权值来查找比对的，某个字符如果在【数据B】序列中出现次数很少，那么这个权值就比较大，如果只出现在一个数据中，那么权值就是1，因此比较适用于有“个性化”字符的数据序列比对。如果各个数据中的各个字符都很“普通”，那么用这种方式可能比对效果不是很好。

2楼附件是解析用的。

3楼附件加入了对比对时【关联字符个数】的控制，如果设置为1那么就是1楼附件的功能，可以设置成2、3或者更大的数值，这个方式主要用来克服1楼附件的缺陷，利用多个字符同时比较来增加“个性化”程度，从而提高比对准确率。比如要比对1和0组成的长串文本，那么无论是1还是0都没有个性，但如果把一串1和0组成的字符串进行比对那么就会大大提高比对的精确率。

胡剑0227

【基本思路】 既然不规则，那么分析某比对数据（比如“莫泰连锁酒店(孙权路店)”，记为【A1】）的各个字符在目标数据列中出现的次数是很有意义的，比如【A1】的各个字符在目标数据列的【B1】中出现了12个，在【B2】中出现了7个，在所有其他数据中出现的次数更少，那么有理由判定与【A1】匹配的数据是【B1】。以图3为例简要讲解实现步骤。

图3 基本思路的实现方式

     步骤1    将C5单元格“莫泰连锁酒店(孙权路店)”的各个字符进行拆分，生成一个12个元素的一维水平数组，如C6:N6单元格区域所示，可由以下公式实现。

1. =MID($C$5,{1,2,3,4,5,6,7,8,9,10,11,12},1)

复制代码

   步骤2    B19单元格为“富阳国际贸易中心大酒店”，分析C6:N6的各个字符在B19单元格中出现的次数可由以下公式得到，最后的效果显示在C19:N19单元格区域中。

1. =--ISNUMBER(FIND($C$6:$N$6,B19,1))

复制代码

     步骤3    将上述公式中的B19换成B7:B15，那么就能返回C6:N6的各个字符在B7:B15各个单元格中出现的次数，得到图3中的【A】区域，每一行对应B7：B15中的一个单元格。公式如下所示。

1. =--ISNUMBER(FIND($C$6:$N$6,B7:B15,1))

复制代码

     步骤4   将【A】区域进行按行汇总就得到【B】区域，【B】区域中最大的数值所对应的数据即是目标匹配数据。对【A】区域C7:N15进行按行汇总的公式如下：

1. =MMULT(C7:N15,{1;1;1;1;1;1;1;1;1;1;1;1})

复制代码

   步骤5   使用MAX函数可以求得P7：P15中的最大值，该最大值并结合MATCH函数即可返回该最大值在P7：P15中的位置，根据这个位置在B7：B15中返回目标“莫泰连锁酒店(富阳孙权路店)”，具体公式如下。
=INDEX(B7:B15,MATCH(MAX(P7:P15),P7:P15,0))

于是“莫泰连锁酒店(孙权路店)”  匹配上 “莫泰连锁酒店(富阳孙权路店)”

【改进思路】以上方式的缺陷是没有考虑到各个字符的价值是不同的，比如在为“莫泰连锁酒店(孙权路店)”搜索匹配项时字符【莫】、【泰】、【孙】、【权】的价值要远比字符【酒】、【店】的价值高。这是因为本例中字符【酒】、【店】是极常见的字符，于是某目标包含了这两个字符对于确认是否匹配没有过多的帮助，而【莫】、【泰】等字符在本例是不常见的字符，因此某目标出现了这两个字符对于确认匹配是非常有帮助的。

因此，需要将各个字符的出现频次进行考虑，频次高的降低其匹配价值（图3【A】区域的每个数字代表了匹配价值），这就是【改进思路】。

图4 改进思路的实现方式

步骤1  将图3【A】区域进行按“列”汇总就可以得到各个字符共出现在几个目标数据中，如图4【CC】区域所示，具体公式如下。

1. =MMULT({1,1,1,1,1,1,1,1,1},C7:N15)

复制代码

步骤2  将图3【A】区域除以图4【CC】区域得到图4的【AA】区域，此时可以看到对于第32行，【莫】、【泰】的权值为1，而【酒】、【店】的权值仅仅为0.13，这样最后得到的【BB】区域中最大值为7.375，次大值为2.375。这远比图3中的最大值12，次大值为7的区分度要好得多。
   

      余下实现与【基本方式】如出一辙，有兴趣可以下载附件，创意在于为每个字符构造的权值。当然这种思路还是可以再次提升的，于是有了下面的【进一步提升】。

胡剑0227

更一步思路

更进一步的思路就是两个两个字符同时考虑，这个要比一个字符一个字符考虑更加有效，当然还没有公式实现过，想想应该简单的，凑空实现一下。

不拘小杰

胡版沙　　　　　　　发

kuangben8

胡版又出精品！收藏细细品味！

jpowe

胡版利害，要好好收藏起来理解

shaowu459

减轻“公司”或“省”，“市”这样的字眼权重是个办法，但是对于无序无规律的原始数据还要去观察。

我前些天也帮别人做了个这样的核对，和胡版想的车一样，也做了一个匹配强度的输入单元格。

当时的情况原始数据中还有很多空格，我先全部在辅助列删除，用辅助列去匹配。另外，我还想了一个办法，mid(字符，row(1:文本长度-1），2）这样。这样的考虑是，如果单个单个字符去比较可能重复的比较多，增加权重还需要更复杂的公式。如果两个字符两个字符的去查找，或者3个字符3个字符的去查找匹配，那样就很大程度上降低重复的可能，也一定程度上取得了降低通用关键字的权重目的。最后呢，再将匹配出来的数据不止一个的在一行中都列示出来，不只列出一个疑似的匹配，列出两三个，供人工核对使用。

上述仅供大家参考。

天地有雪

胡版太强大了！收藏起来，好好消化！！

jysvip

很强大..............

CheryBTL

支持胡版新作
这种模糊查询有点VBA的感觉了