ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 215142|回复: 181

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

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-8-22 16:42 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:数据查询和匹配
本帖最后由 胡剑0227 于 2017-7-19 20:52 编辑

使用动画.gif
模糊匹配工具-阶段备份.zip (11.89 KB, 下载次数: 9171)

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

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

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

1+2的图.png
图2 附件工作簿各工作表区域描述



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

2楼附件是解析用的。

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


微信公众号.jpg



微信公众号.jpg

模糊匹配工具-阶段备份(2003).zip

6.19 KB, 下载次数: 9551

评分

15

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-22 16:43 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 胡剑0227 于 2013-8-22 22:35 编辑

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

思路解析1.png
图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】区域的每个数字代表了匹配价值),这就是【改进思路】。

思路解析2.png
图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的区分度要好得多。
   

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




模糊匹配工具-阶段备份(用于思路解析).zip

8.37 KB, 下载次数: 4092

评分

3

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-22 16:44 | 显示全部楼层
本帖最后由 胡剑0227 于 2013-8-27 10:37 编辑

更一步思路

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


模糊匹配工具-可控关联字符数.zip

13.92 KB, 下载次数: 5808

模糊匹配工具-可控关联字符数(2003).zip

8.67 KB, 下载次数: 3287

点评

1楼太长了,以至于我没有看到3楼。。呵呵  发表于 2013-8-23 13:50

TA的精华主题

TA的得分主题

发表于 2013-8-22 23:56 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-8-23 07:18 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-8-23 08:10 来自手机 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-8-23 09:50 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 shaowu459 于 2013-8-23 09:54 编辑

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

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

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

上述仅供大家参考。

点评

恩,这个思路就是我上面3楼的思路...添加关联性  发表于 2013-8-23 12:35

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-8-23 10:05 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-8-23 17:01 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-8-23 20:26 | 显示全部楼层
支持胡版新作
这种模糊查询有点VBA的感觉了
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 01:53 , Processed in 0.062639 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表