做数据核对的时候最折磨人的不是数据量太大而是两批文本“看起来是同一个、又不完全一样”。我在一次供应商主数据清理中拿着三千行各分公司报上来的供应商名称去对系统里的几十万条正式登记名称用VLOOKUP精确匹配一票否决用CtrlF肉眼检索搜到半夜。后来我在WPS表格里写了几个自定义公式把编辑距离和相似度百分比封装成可以直接拖拽的函数才把这类模糊匹配从“手工活”变成了“公式活”。这篇教程就是这次实战的完整复盘讲清楚WPS自定义公式怎么做相似度匹配、为什么这么做、踩了哪些坑。1. 什么场景必须用相似度匹配精确匹配失灵的日子1.1 客户主数据去重三千行名字对十万行档案我先说最典型的场景多部门上报的数据跟系统里的正式主数据对不上。采购部发来的供应商可能叫“阿里巴巴中国网络技术有限公司”系统里的登记名却是“阿里巴巴中国网络技术公司”中间差一个括号、少一个“有限”VLOOKUP就返回#N/A。更常见的是“北京百度网讯科技有限公司”和“北京市百度网络科技有限公司”这种多一个“市”字、换了一个词肉眼一看就知道是同一家但表格公式不认。这种问题的本质是业务系统的主数据是强规则的但人录入时是自由的。只要有一个全角括号、一个半角空格、一个不统一的简称精确匹配就全线崩溃。我见过有人在Excel里为了绕开这个问题把名称列清洗了七八遍恨不得把所有可能的写法都列成映射表最后依然漏掉一批。1.2 跨表人员名单比对连空格和括号都在较劲除了企业名称人员名单的比对同样痛苦。两个系统导出的员工名单同一个人的姓名可能有细微差异Excel导出的叫“张三丰”另一套系统里叫“张三 丰”中间多了个空格还有人名里的点号不一样有的用“迪丽热巴·买买提”有的用“迪丽热巴.买买提”。如果你用精确匹配去查重恭喜你每一行都是“新员工”。还有地址匹配。仓库地址、收货地址、办公地址同一个地方可以有七八种缩写方式“上海市浦东新区张江高科园区”和“张江高科园区浦东新区上海市”词序完全颠倒语义却一样。这类数据如果不做相似度匹配就只能靠人一页页翻时间全搭进去。1.3 在线API和本地函数的取舍数据安全决定路线有些同学可能第一反应是调用网上的文本相似度API把数据传上去返回一个分数。这确实省事但我个人的态度很明确涉及客户名单、员工信息、财务数据这类敏感内容不要随便往外传。哪怕内部数据管理没那么严数据出去了就会留下痕迹一旦出问题责任在你自己。更关键的是在线API是一次性的这次用完了下次还得复制粘贴、上传下载。如果能把相似度匹配做成WPS里的一个自定义公式等于把能力沉淀到了表格本身。数据不出本地函数随文件走谁拿到这个文件谁就能用同一套规则全部门统一。这才是做数据清洗应该有的状态。2. 三种相似度算法实测为什么我选编辑距离打底2.1 编辑距离Levenshtein数一数要改几次编辑距离的思路特别朴素一个字符串变成另一个字符串最少需要多少次插入、删除、替换操作。比如“公司”变“有限责任公司”需要在最前面插入“有限责任”四个字距离就是4“WPS”变“WPO”把最后一个字母S替换成O距离是1。这个算法用动态规划实现核心是一个二维状态表。dp[i][j]表示A的前i个字符变成B的前j个字符需要的最少操作次数。每次比较最后一个字符如果相等代价继承左上角如果不相等就看删除、插入、替换三种方案哪个代价最小。循环填完整个表右下角的值就是答案。编辑距离最友好的地方在于它天然处理“局部差异”多一个字、少一个字、改一个字都能算出合理的距离值不会被一个标点整段判死刑。2.2 Jaccard相似系数看交集占比Jaccard把文本拆成集合算交集大小除以并集大小。拿“百度”和“百度网讯”来说拆成字符集合后交集是{百, 度}并集是{百, 度, 网, 讯}相似度就是2/40.5。它的优点是对词序完全无感“上海浦东”和“浦东上海”拆成字符集合后是一样的Jaccard会给出1.0的高分但缺点也很明显短文本里两个字符组成的字符串和三个字符组成的字符串区分度很粗糙。而且“AB”和“BA”这种明显不同的字符串Jaccard也会判成100%相似这在业务上是不能接受的。2.3 余弦相似度适合长文本不适合短词余弦相似度一般先把文本转成词袋向量再算两个向量夹角的余弦值取值在-1到1之间越接近1越相似。它在长文本去重、文章查重、搜索召回里表现很好但在表格里比对企业名称、人名这类短文本时特征向量极其稀疏结果反而不直观。你想一下“北京百度网讯科技有限公司”和“北京市百度网络科技有限公司”拆成词以后向量长度也就十几个维度稍微不规则一点余弦值就大幅波动反而不如编辑距离那种“错几个字就说错几个字”的逻辑好解释。2.4 用真实公司名跑一组对比分我把三类典型情况用编辑距离跑了一遍结果很能说明问题对比内容预处理前相似度预处理后相似度说明阿里巴巴中国网络技术有限公司 vs 阿里巴巴中国网络技术公司约63%接近100%全角括号和公司后缀干扰严重北京百度网讯科技有限公司 vs 北京市百度网络科技有限公司约75%约85%多字、换词场景分数越界需要复核上海市浦东新区张江高科园区 vs 张江高科园区浦东新区上海市约40%约40%词序颠倒编辑距离无能为力看到没有同一个算法在不同场景下表现差别非常大。第一类差异靠预处理基本能消灭第二类需要设阈值让人工介入第三类是编辑距离的硬伤只能靠其他手段补充。我最后选择“编辑距离预处理”作为主力方案是因为它简单、稳定、每个分数都能讲得出理由对表格里最常见的轻微差异处理得最好。2.5 归一化成百分比距离怎么变成0到100分编辑距离是一个整数业务同事很难直观理解“距离4”意味着什么于是要把它归一化成百分比。常用的公式是相似度 1 - 编辑距离 / 两个字符串中较长的长度举个例子A长度为20B长度为18编辑距离是1那么相似度就是1 - 1/20 95%。如果两个都是空字符串我直接返回100%这是唯一的分支特判。乘以100以后得到的就是表格里常见的“匹配度分数”。注意除以最大长度而不是平均长度是为了让“插入几个字符”这种行为对分数的惩罚更线性。比如一个短名称里多了一个空格用最大长度做分母不至于把分数拉得太狠。3. WPS里自定义公式的两条路JS宏与VBA宏的取舍3.1 WPS宏生态的现状JSA与VBA并存很多从Excel转过来的老用户第一反应是写VBA函数。WPS为了兼容Office的老文件确实保留了对VBA的支持但并不是所有版本都内置了VBA运行环境。个人版想用VBA经常得额外处理组件问题这会让方案推广受阻。相比之下WPS近几个版本主推的JS宏JSAJavaScript for Application是原生的个人版就能直接打开“开发工具”面板进入JS宏编辑器。它的语法是JavaScript生态上有很多前端和脚本经验可以直接迁移过来而且跨平台性更好在Linux版WPS里也能运行。3.2 开发工具与环境准备动手之前先把开发工具面板调出来。操作路径是文件 - 选项 - 自定义功能区在右侧主选项卡里勾选“开发工具”确定后在菜单栏就能看到。打开“开发工具”选项卡里面有两个入口一个是“切换到 JS 宏”一个是“VB宏”或者叫“WPS宏”取决于你的版本。两者在同一时间只能选一个作为当前文档的宏环境。点击JS宏进入编辑器后左侧能看到工程资源管理器一般会有一个“模块”节点右键插入新模块代码就写在这里。3.3 两条路线对比我把两个路线的差异列个表方便你根据自己情况判断维度JS宏JSAVBA宏内置程度较新版本WPS原生支持个人版可用专业版/部分版本支持个人版可能需要组件语法风格JavaScriptES5为主VB/VBA跨平台Windows、Linux都可用Linux/Mac支持较弱学习成本会点前端或脚本语言就能快速上手Office老用户更熟悉社区资料相对少但热度在涨资料多但WPS环境下有兼容差异适合场景新文件、新团队、跨部门分发存量VBA模板迁移、维护老资产3.4 我的选型建议我的建议很直接新项目一律用JS宏不要犹豫。原因不只是个人版可用更重要的是WPS后续迭代的重心明显在JS宏上很多新功能、新API都先给JSA。VBA在WPS里更像一个“兼容模式”能用但遇到莫名其妙的bug时你连性能优化的动力都没有。如果你手上有大量历史VBA代码也别急着扔。可以继续用但新写的函数、新做的模板优先用JS宏慢慢把核心能力迁移过来。我现在的做法是老文件打开不破坏新文件全部JSA两套环境尽量不混在一个文件里免得宏类型切换出问题。4. 手写一个编辑距离公式JS宏源码与单元格调用全流程4.1 JS宏版编辑距离与相似度函数源码下面是一份我实际在WPS里用过的完整代码包含预处理函数、编辑距离函数和最终的相似度函数。新建一个模块把代码整段粘贴进去就行。// 预处理全角转半角、去空白、统一括号、剥离部分公司后缀 function _clean(s) { if (s null || s undefined) return ; s s.toString(); var out ; for (var i 0; i s.length; i) { var c s.charCodeAt(i); if (c 0xFF01 c 0xFF5E) { out String.fromCharCode(c - 0xFEE0); // 全角转半角 } else if (c 0x3000) { out ; // 全角空格转普通空格 } else { out s.charAt(i); } } out out.replace(/\s/g, ); // 去掉所有空白 out out.replace(/[(]/g, (); // 全角括号统一为半角 out out.replace(/[)]/g, )); out out.replace(/[()]/g, ); // 去掉括号 out out.replace(/股份有限公司|集团有限公司|有限责任公司|有限公司/g, ); return out; } // 编辑距离核心动态规划 function _lev(a, b) { var m a.length; var n b.length; if (m 0) return n; if (n 0) return m; var dp []; for (var i 0; i m; i) { dp[i] []; dp[i][0] i; } for (var j 0; j n; j) { dp[0][j] j; } for (var i 1; i m; i) { for (var j 1; j n; j) { var cost a.charAt(i - 1) b.charAt(j - 1) ? 0 : 1; dp[i][j] Math.min( dp[i - 1][j] 1, dp[i][j - 1] 1, dp[i - 1][j - 1] cost ); } } return dp[m][n]; } // 对外暴露的相似度函数返回0到100的数值 function SIMTEXT(a, b) { var pa _clean(a); var pb _clean(b); if (pa.length 0 pb.length 0) return 100; var maxLen Math.max(pa.length, pb.length); if (maxLen 0) return 100; var d _lev(pa, pb); return Math.round((1 - d / maxLen) * 10000) / 100; }4.2 注册与调用让公式出现在单元格里代码写完后在JS宏编辑器里按CtrlS保存然后回到WPS表格工作表。找一个空单元格直接输入SIMTEXT(A2, B2)如果A2和B2分别写的是“阿里巴巴中国网络技术有限公司”和“阿里巴巴中国网络技术公司”回车后就会得到接近100的数值。这里有个关键动作第一次写公式时WPS可能会提示宏被禁用或者在单元格里返回#NAME?。这是因为文档的宏安全级别默认不允许执行宏。你需要到“文件 - 选项 - 信任中心 - 宏设置”里选择“启用宏”建议同时勾选“信任访问VBA项目对象模型”之类选项按你的版本提示来。启用后公式就能正常出结果。提示如果你的文档是发给别人用的别在“宏设置”里选“启用所有宏”然后不管更好的做法是把文件放到受信任位置或者给文档签名。启用所有宏对来源不明的文件风险很大特别是你可能会打开别人的文档。4.3 多行批量比对实战辅助列加条件格式单条比对只是起步实际业务里都是几百上千行。我的用法是加辅助列在C2输入SIMTEXT(A2, $B$2)这里把B2锁定成绝对引用A2相对引用。下拉填充到C101C列就是每一行与目标名称的相似度分数。然后选中C2到C101用条件格式开始 - 条件格式 - 突出显示单元格规则 - 大于填一个阈值比如90把高于90的单元格标成绿色。这样一批名字里哪些可以直接判定为同一家公司、哪些需要人工复核一眼就能扫完。更严谨一点还可以在D列写判断逻辑IF(C295, 直接合并, IF(C285, 待复核, ))有了这个辅助判断业务同事就不需要理解相似度算法他们只需要看“直接合并”和“待复核”这两个词就够了。4.4 核心代码逐段解读为什么这样写我一开始写这个函数时没放预处理直接拿原始字符串算距离结果惨不忍睹全角括号直接当成一个字符差异“有限公司”这种高频后缀把分数拖低一大截。后来才明白自定义公式不等于算法本身它应该是一个“业务规则封装”。_clean函数看起来只是在清洗字符串实际上是在把业务领域的判断标准固化进公式里。编辑距离部分用二维数组dp做动态规划空间复杂度是O(m*n)。对十几个字符的短文本来说完全够用但如果你要匹配长文本后面会讲到怎么优化。SIMTEXT返回的是百分比数值而不是0到1的小数原因是业务场景里大家习惯看“90分”而不是看“0.9”。保留两位小数也是为了让阈值判断更精确比如“90”和“96”之间的差异在视觉上更明显。还需要注意一点自定义函数的名称最好不要与WPS内置函数重名也不要带下划线开头。我测试时发现有些版本对下划线开头的函数支持不稳定直接用大写字母开头的普通函数名最稳妥。5. 匹配效果不是算法一个说了算预处理、阈值和提速5.1 预处理三板斧全半角、空格和括号相似度匹配的准确率预处理比算法本身更影响结果。我总结了最有效的三板斧全半角统一、去空白、去括号。全半角不统一是最隐蔽的坑。中文输入法打出来的括号是“”英文输入法是“(”肉眼几乎看不出来但按字符比较就是不同。全半角的字母、数字、符号同理。最粗暴的做法是把全角字符统一转半角循环里通过charCodeAt判断字符码在0xFF01到0xFF5E之间就减去0xFEE0转成半角这是标准做法。去空白要小心。把名字中间的空格去掉对“张三 丰”这种名字是有用的但不能对地址数据一样处理否则“上海市 浦东新区”和“上海市浦东新区”确实一致但“上海市 张江路”里的空格夹在中间去掉后没问题如果地址里本身有语义空格就得按业务判断。我的建议是给人名、公司名这种“非空白的语义单元”去空格地址类数据最好只去首尾空格中间空格保留再做特殊处理。5.2 公司名后缀与简称剥离之后再匹配“有限公司”“股份有限公司”“集团有限公司”这些后缀频率极高对相似度计算的干扰也最大。因为它们长度占了整个名称的三分之一左右一旦一边有“有限公司”一边没有编辑距离就会被大大拉高而业务上这两个名称可能指向的确实是同一主体。我的方案是在_clean里把常见的公司后缀词直接用正则去掉。但这里有个度的问题去掉“有限公司”可以去掉“科技”“信息”“网络”就要慎重因为这些词往往是商号的一部分比如“百度网络科技有限公司”和“百度科技有限公司”就是两家不同公司。简称问题更麻烦。“中国石油化工股份有限公司”到了业务人员嘴里就是“中石化”编辑距离算出来可能只有三成相似度但确实是同一个东西。这种场景靠算法解决不了我的做法是维护一张“简称映射表”在原数据里先做一次查找替换把简称换成规范全称再进相似度公式。公式负责“模糊”映射表负责“指路”。注意预处理规则不是越狠越好。剥离太多会把原本有区分度的词也给抹掉导致完全不同的名字算出高分。每加一条规则都要拿一批真实样本跑一遍看误判率是否上升。5.3 阈值拍多少经验值与方法论相似度分数出来以后最大问题就是“多少分算同一”。我的经验参考值是这样的相似度判定建议适用场景98-100直接判定为同一短名称、预处理充分的情况90-98高度匹配可自动处理多数客户名/公司名去重场景80-90人工复核涉及合同、资金、合并没有把握时70-80可能相关需调查简称、别称、词序颠倒时低于70默认不是同一但要注意倒序或简称的极端情况阈值不是死的。我更推荐的做法是先用一部分历史数据做训练集比如抽200条你已经知道正确答案的数据跑一遍相似度看哪些分数段会混入明显的错误结果再把阈值调到那个分界带以下。没有条件做训练时宁可把自动处理的阈值调高一点让更多结果进入人工复核避免合并错数据。5.4 性能优化长文本预判、手动计算与缓存编辑距离是O(m*n)的算法两个100字符的文本要算一万次操作几百行数据就觉得卡。实际使用中我做了三层优化。第一层是长度预判。如果两个字符串的长度差太大相似度天花板会非常低可以直接返回0。比如A长100B长10就算前面9个字符全对上相似度最高也只有10%直接跳过计算。判断公式是Math.abs(pa.length - pb.length) / Math.max(pa.length, pb.length)如果这个值已经大于1 - 阈值/100直接返回0。第二层是手动重算。公式数量多的时候把WPS的计算模式调成“手动”公式选项卡 - 计算选项 - 手动。数据改完以后按快捷键触发重新计算避免每动一个单元格全表公式跟着抖一遍。第三层是尽量避免在公式内部循环遍历一个区域。有的人会在自定义公式里写循环去扫一整列Range然后在单元格里调用这样每次重算都会做一次全表扫描性能极差。我的建议是用一行一个公式做两两比对批量匹配交给辅助列筛选而不是把“查找整列”写进公式里。如果实在要全表扫描用宏写一次性计算算完只把结果值贴回单元格不保留公式。6. 公式上线后的坑#NAME?、宏禁用与跨设备失效6.1 #NAME?错误的第一排查链自定义公式最常见的问题就是单元格里显示#NAME?。我遇到过的情况大概有四种。第一宏被禁用了。文件打开时顶部有黄色安全警告条点击“启用宏”公式马上就活过来。第二函数名拼写错误。自定义函数没有智能提示拼错一个字母它就变成未知名称。第三函数写错了地方。有些人把代码写到了工作表代码区而不是模块区工作表事件代码和自定义函数在WPS的解析优先级上会出问题。第四当前文档不是支持宏的格式保存时把宏弄丢了。排查顺序我建议是先看安全警告条再看代码位置再核对函数名最后重新保存一次并关闭重开。6.2 JS宏环境不是浏览器fetch不存在的真正原因很多人初学JS宏习惯性地用浏览器的API然后报fetch is not defined以为自己代码写错了其实方向就错了。WPS的JSA宿主环境是表格应用不是浏览器没有window、document、fetch这一套Web API。它提供的是WPS的对象模型操作的是单元格、工作表、工作簿。这对自定义公式来说反而是好事。公式应该是一个纯函数输入两个值返回一个分数不依赖网络不依赖外部状态。如果有人在公式里用fetch去拉在线接口每次重算都会发网络请求轻则慢到崩溃重则被安全软件拦下来。我在团队里定的规矩是自定义公式一律不允许联网所有计算必须在本地完成。6.3 文件保存格式与宏分发同事打开后怎么不报警写完函数以后保存格式一定要选对。普通xlsx格式不保留宏你辛辛苦苦写的SIMTEXT函数关掉重开就没了。WPS里需要另存为“Excel启用宏的工作簿.xlsm”或者WPS本身的表格格式.et宏才会保留下来。发给同事时他们第一次打开会看到安全提示“已禁止使用宏”或“是否启用宏”。这是正常的安全机制不要嫌麻烦。要让同事顺利使用有几个办法一是把文件放到公司内部受信任的共享目录然后在“宏设置”里把这个目录加进受信任位置二是文件本身来源要可追溯不要随便从网上下载带宏的文件三是实在不行就在打开时手动点“启用宏”。如果你希望自己以后新建的每个表格都能直接用SIMTEXT可以把模块代码复制出来在目标文件里重新插入模块粘贴代码。WPS目前没有像Excel个人宏工作簿那么顺滑的全局方案我一般就是留一个模板文件每次从模板抄一份。6.4 WPS更新后函数失效与跨平台限制WPS自动更新以后偶尔会出现宏环境变化的情况。我遇到过两次一次是更新后宏安全级别被重置成了“禁用宏”公式全变#NAME?另一次是JSA引擎升级后某些ES5写法里的字符串方法行为有细微变化。解决办法很简单更新完以后先验证一遍核心函数出问题就去“信任中心”查看宏设置再看代码里有没有用到已经被调整的API。跨平台的问题也要提前说。Linux版WPS能打开JS宏文件但部分WPS对象模型API在Linux上支持得并不完整。如果你只在Windows上测试过别指望换到Linux环境一定还能跑尤其是涉及文件操作、外部程序调用这类功能。纯计算类的自定义公式跨平台的风险相对小一些。6.5 弹出自定义项安装时的处理思路用过WPS的应该都遇到过“自定义项安装”弹窗打开一个文件突然提示要安装什么自定义项。这东西在带宏的文档里出现频率更高因为它通常指向文档引用的一些加载项组件。我的处理原则是来源不明的自定义项一律不装先点取消然后到开发工具 - 加载项列表里查看当前文档引用了哪些东西把不需要的加载项取消勾选。加载项不是越多越好尤其是从第三方下载的宏插件很可能夹带私货。如果装错了WPS的加载项目录里已经存在垃圾文件最稳妥的办法是在“加载项”窗口里禁用然后在WPS的安装配置里重置自定义项。这一步不要用非官方工具处理走正常卸载重装或修复流程就好。我自己实际用下来最大的体会是相似度公式不是写完一个函数就结束了它更像一套小规则引擎预处理规则怎么定阈值怎么划匹配结果谁来复核才决定这套工具能不能被团队接受。把SIMTEXT放到共享模板里之后同事从最开始怀疑“这函数算的数准不准”到后来已经习惯直接看分数做初筛前后也就磨合了两周。如果你们团队也在跟脏数据较劲不妨先复制代码跑一条数据再加自己的业务规则进去。
WPS自定义公式实战：用编辑距离实现文本相似度匹配
做数据核对的时候最折磨人的不是数据量太大而是两批文本“看起来是同一个、又不完全一样”。我在一次供应商主数据清理中拿着三千行各分公司报上来的供应商名称去对系统里的几十万条正式登记名称用VLOOKUP精确匹配一票否决用CtrlF肉眼检索搜到半夜。后来我在WPS表格里写了几个自定义公式把编辑距离和相似度百分比封装成可以直接拖拽的函数才把这类模糊匹配从“手工活”变成了“公式活”。这篇教程就是这次实战的完整复盘讲清楚WPS自定义公式怎么做相似度匹配、为什么这么做、踩了哪些坑。1. 什么场景必须用相似度匹配精确匹配失灵的日子1.1 客户主数据去重三千行名字对十万行档案我先说最典型的场景多部门上报的数据跟系统里的正式主数据对不上。采购部发来的供应商可能叫“阿里巴巴中国网络技术有限公司”系统里的登记名却是“阿里巴巴中国网络技术公司”中间差一个括号、少一个“有限”VLOOKUP就返回#N/A。更常见的是“北京百度网讯科技有限公司”和“北京市百度网络科技有限公司”这种多一个“市”字、换了一个词肉眼一看就知道是同一家但表格公式不认。这种问题的本质是业务系统的主数据是强规则的但人录入时是自由的。只要有一个全角括号、一个半角空格、一个不统一的简称精确匹配就全线崩溃。我见过有人在Excel里为了绕开这个问题把名称列清洗了七八遍恨不得把所有可能的写法都列成映射表最后依然漏掉一批。1.2 跨表人员名单比对连空格和括号都在较劲除了企业名称人员名单的比对同样痛苦。两个系统导出的员工名单同一个人的姓名可能有细微差异Excel导出的叫“张三丰”另一套系统里叫“张三 丰”中间多了个空格还有人名里的点号不一样有的用“迪丽热巴·买买提”有的用“迪丽热巴.买买提”。如果你用精确匹配去查重恭喜你每一行都是“新员工”。还有地址匹配。仓库地址、收货地址、办公地址同一个地方可以有七八种缩写方式“上海市浦东新区张江高科园区”和“张江高科园区浦东新区上海市”词序完全颠倒语义却一样。这类数据如果不做相似度匹配就只能靠人一页页翻时间全搭进去。1.3 在线API和本地函数的取舍数据安全决定路线有些同学可能第一反应是调用网上的文本相似度API把数据传上去返回一个分数。这确实省事但我个人的态度很明确涉及客户名单、员工信息、财务数据这类敏感内容不要随便往外传。哪怕内部数据管理没那么严数据出去了就会留下痕迹一旦出问题责任在你自己。更关键的是在线API是一次性的这次用完了下次还得复制粘贴、上传下载。如果能把相似度匹配做成WPS里的一个自定义公式等于把能力沉淀到了表格本身。数据不出本地函数随文件走谁拿到这个文件谁就能用同一套规则全部门统一。这才是做数据清洗应该有的状态。2. 三种相似度算法实测为什么我选编辑距离打底2.1 编辑距离Levenshtein数一数要改几次编辑距离的思路特别朴素一个字符串变成另一个字符串最少需要多少次插入、删除、替换操作。比如“公司”变“有限责任公司”需要在最前面插入“有限责任”四个字距离就是4“WPS”变“WPO”把最后一个字母S替换成O距离是1。这个算法用动态规划实现核心是一个二维状态表。dp[i][j]表示A的前i个字符变成B的前j个字符需要的最少操作次数。每次比较最后一个字符如果相等代价继承左上角如果不相等就看删除、插入、替换三种方案哪个代价最小。循环填完整个表右下角的值就是答案。编辑距离最友好的地方在于它天然处理“局部差异”多一个字、少一个字、改一个字都能算出合理的距离值不会被一个标点整段判死刑。2.2 Jaccard相似系数看交集占比Jaccard把文本拆成集合算交集大小除以并集大小。拿“百度”和“百度网讯”来说拆成字符集合后交集是{百, 度}并集是{百, 度, 网, 讯}相似度就是2/40.5。它的优点是对词序完全无感“上海浦东”和“浦东上海”拆成字符集合后是一样的Jaccard会给出1.0的高分但缺点也很明显短文本里两个字符组成的字符串和三个字符组成的字符串区分度很粗糙。而且“AB”和“BA”这种明显不同的字符串Jaccard也会判成100%相似这在业务上是不能接受的。2.3 余弦相似度适合长文本不适合短词余弦相似度一般先把文本转成词袋向量再算两个向量夹角的余弦值取值在-1到1之间越接近1越相似。它在长文本去重、文章查重、搜索召回里表现很好但在表格里比对企业名称、人名这类短文本时特征向量极其稀疏结果反而不直观。你想一下“北京百度网讯科技有限公司”和“北京市百度网络科技有限公司”拆成词以后向量长度也就十几个维度稍微不规则一点余弦值就大幅波动反而不如编辑距离那种“错几个字就说错几个字”的逻辑好解释。2.4 用真实公司名跑一组对比分我把三类典型情况用编辑距离跑了一遍结果很能说明问题对比内容预处理前相似度预处理后相似度说明阿里巴巴中国网络技术有限公司 vs 阿里巴巴中国网络技术公司约63%接近100%全角括号和公司后缀干扰严重北京百度网讯科技有限公司 vs 北京市百度网络科技有限公司约75%约85%多字、换词场景分数越界需要复核上海市浦东新区张江高科园区 vs 张江高科园区浦东新区上海市约40%约40%词序颠倒编辑距离无能为力看到没有同一个算法在不同场景下表现差别非常大。第一类差异靠预处理基本能消灭第二类需要设阈值让人工介入第三类是编辑距离的硬伤只能靠其他手段补充。我最后选择“编辑距离预处理”作为主力方案是因为它简单、稳定、每个分数都能讲得出理由对表格里最常见的轻微差异处理得最好。2.5 归一化成百分比距离怎么变成0到100分编辑距离是一个整数业务同事很难直观理解“距离4”意味着什么于是要把它归一化成百分比。常用的公式是相似度 1 - 编辑距离 / 两个字符串中较长的长度举个例子A长度为20B长度为18编辑距离是1那么相似度就是1 - 1/20 95%。如果两个都是空字符串我直接返回100%这是唯一的分支特判。乘以100以后得到的就是表格里常见的“匹配度分数”。注意除以最大长度而不是平均长度是为了让“插入几个字符”这种行为对分数的惩罚更线性。比如一个短名称里多了一个空格用最大长度做分母不至于把分数拉得太狠。3. WPS里自定义公式的两条路JS宏与VBA宏的取舍3.1 WPS宏生态的现状JSA与VBA并存很多从Excel转过来的老用户第一反应是写VBA函数。WPS为了兼容Office的老文件确实保留了对VBA的支持但并不是所有版本都内置了VBA运行环境。个人版想用VBA经常得额外处理组件问题这会让方案推广受阻。相比之下WPS近几个版本主推的JS宏JSAJavaScript for Application是原生的个人版就能直接打开“开发工具”面板进入JS宏编辑器。它的语法是JavaScript生态上有很多前端和脚本经验可以直接迁移过来而且跨平台性更好在Linux版WPS里也能运行。3.2 开发工具与环境准备动手之前先把开发工具面板调出来。操作路径是文件 - 选项 - 自定义功能区在右侧主选项卡里勾选“开发工具”确定后在菜单栏就能看到。打开“开发工具”选项卡里面有两个入口一个是“切换到 JS 宏”一个是“VB宏”或者叫“WPS宏”取决于你的版本。两者在同一时间只能选一个作为当前文档的宏环境。点击JS宏进入编辑器后左侧能看到工程资源管理器一般会有一个“模块”节点右键插入新模块代码就写在这里。3.3 两条路线对比我把两个路线的差异列个表方便你根据自己情况判断维度JS宏JSAVBA宏内置程度较新版本WPS原生支持个人版可用专业版/部分版本支持个人版可能需要组件语法风格JavaScriptES5为主VB/VBA跨平台Windows、Linux都可用Linux/Mac支持较弱学习成本会点前端或脚本语言就能快速上手Office老用户更熟悉社区资料相对少但热度在涨资料多但WPS环境下有兼容差异适合场景新文件、新团队、跨部门分发存量VBA模板迁移、维护老资产3.4 我的选型建议我的建议很直接新项目一律用JS宏不要犹豫。原因不只是个人版可用更重要的是WPS后续迭代的重心明显在JS宏上很多新功能、新API都先给JSA。VBA在WPS里更像一个“兼容模式”能用但遇到莫名其妙的bug时你连性能优化的动力都没有。如果你手上有大量历史VBA代码也别急着扔。可以继续用但新写的函数、新做的模板优先用JS宏慢慢把核心能力迁移过来。我现在的做法是老文件打开不破坏新文件全部JSA两套环境尽量不混在一个文件里免得宏类型切换出问题。4. 手写一个编辑距离公式JS宏源码与单元格调用全流程4.1 JS宏版编辑距离与相似度函数源码下面是一份我实际在WPS里用过的完整代码包含预处理函数、编辑距离函数和最终的相似度函数。新建一个模块把代码整段粘贴进去就行。// 预处理全角转半角、去空白、统一括号、剥离部分公司后缀 function _clean(s) { if (s null || s undefined) return ; s s.toString(); var out ; for (var i 0; i s.length; i) { var c s.charCodeAt(i); if (c 0xFF01 c 0xFF5E) { out String.fromCharCode(c - 0xFEE0); // 全角转半角 } else if (c 0x3000) { out ; // 全角空格转普通空格 } else { out s.charAt(i); } } out out.replace(/\s/g, ); // 去掉所有空白 out out.replace(/[(]/g, (); // 全角括号统一为半角 out out.replace(/[)]/g, )); out out.replace(/[()]/g, ); // 去掉括号 out out.replace(/股份有限公司|集团有限公司|有限责任公司|有限公司/g, ); return out; } // 编辑距离核心动态规划 function _lev(a, b) { var m a.length; var n b.length; if (m 0) return n; if (n 0) return m; var dp []; for (var i 0; i m; i) { dp[i] []; dp[i][0] i; } for (var j 0; j n; j) { dp[0][j] j; } for (var i 1; i m; i) { for (var j 1; j n; j) { var cost a.charAt(i - 1) b.charAt(j - 1) ? 0 : 1; dp[i][j] Math.min( dp[i - 1][j] 1, dp[i][j - 1] 1, dp[i - 1][j - 1] cost ); } } return dp[m][n]; } // 对外暴露的相似度函数返回0到100的数值 function SIMTEXT(a, b) { var pa _clean(a); var pb _clean(b); if (pa.length 0 pb.length 0) return 100; var maxLen Math.max(pa.length, pb.length); if (maxLen 0) return 100; var d _lev(pa, pb); return Math.round((1 - d / maxLen) * 10000) / 100; }4.2 注册与调用让公式出现在单元格里代码写完后在JS宏编辑器里按CtrlS保存然后回到WPS表格工作表。找一个空单元格直接输入SIMTEXT(A2, B2)如果A2和B2分别写的是“阿里巴巴中国网络技术有限公司”和“阿里巴巴中国网络技术公司”回车后就会得到接近100的数值。这里有个关键动作第一次写公式时WPS可能会提示宏被禁用或者在单元格里返回#NAME?。这是因为文档的宏安全级别默认不允许执行宏。你需要到“文件 - 选项 - 信任中心 - 宏设置”里选择“启用宏”建议同时勾选“信任访问VBA项目对象模型”之类选项按你的版本提示来。启用后公式就能正常出结果。提示如果你的文档是发给别人用的别在“宏设置”里选“启用所有宏”然后不管更好的做法是把文件放到受信任位置或者给文档签名。启用所有宏对来源不明的文件风险很大特别是你可能会打开别人的文档。4.3 多行批量比对实战辅助列加条件格式单条比对只是起步实际业务里都是几百上千行。我的用法是加辅助列在C2输入SIMTEXT(A2, $B$2)这里把B2锁定成绝对引用A2相对引用。下拉填充到C101C列就是每一行与目标名称的相似度分数。然后选中C2到C101用条件格式开始 - 条件格式 - 突出显示单元格规则 - 大于填一个阈值比如90把高于90的单元格标成绿色。这样一批名字里哪些可以直接判定为同一家公司、哪些需要人工复核一眼就能扫完。更严谨一点还可以在D列写判断逻辑IF(C295, 直接合并, IF(C285, 待复核, ))有了这个辅助判断业务同事就不需要理解相似度算法他们只需要看“直接合并”和“待复核”这两个词就够了。4.4 核心代码逐段解读为什么这样写我一开始写这个函数时没放预处理直接拿原始字符串算距离结果惨不忍睹全角括号直接当成一个字符差异“有限公司”这种高频后缀把分数拖低一大截。后来才明白自定义公式不等于算法本身它应该是一个“业务规则封装”。_clean函数看起来只是在清洗字符串实际上是在把业务领域的判断标准固化进公式里。编辑距离部分用二维数组dp做动态规划空间复杂度是O(m*n)。对十几个字符的短文本来说完全够用但如果你要匹配长文本后面会讲到怎么优化。SIMTEXT返回的是百分比数值而不是0到1的小数原因是业务场景里大家习惯看“90分”而不是看“0.9”。保留两位小数也是为了让阈值判断更精确比如“90”和“96”之间的差异在视觉上更明显。还需要注意一点自定义函数的名称最好不要与WPS内置函数重名也不要带下划线开头。我测试时发现有些版本对下划线开头的函数支持不稳定直接用大写字母开头的普通函数名最稳妥。5. 匹配效果不是算法一个说了算预处理、阈值和提速5.1 预处理三板斧全半角、空格和括号相似度匹配的准确率预处理比算法本身更影响结果。我总结了最有效的三板斧全半角统一、去空白、去括号。全半角不统一是最隐蔽的坑。中文输入法打出来的括号是“”英文输入法是“(”肉眼几乎看不出来但按字符比较就是不同。全半角的字母、数字、符号同理。最粗暴的做法是把全角字符统一转半角循环里通过charCodeAt判断字符码在0xFF01到0xFF5E之间就减去0xFEE0转成半角这是标准做法。去空白要小心。把名字中间的空格去掉对“张三 丰”这种名字是有用的但不能对地址数据一样处理否则“上海市 浦东新区”和“上海市浦东新区”确实一致但“上海市 张江路”里的空格夹在中间去掉后没问题如果地址里本身有语义空格就得按业务判断。我的建议是给人名、公司名这种“非空白的语义单元”去空格地址类数据最好只去首尾空格中间空格保留再做特殊处理。5.2 公司名后缀与简称剥离之后再匹配“有限公司”“股份有限公司”“集团有限公司”这些后缀频率极高对相似度计算的干扰也最大。因为它们长度占了整个名称的三分之一左右一旦一边有“有限公司”一边没有编辑距离就会被大大拉高而业务上这两个名称可能指向的确实是同一主体。我的方案是在_clean里把常见的公司后缀词直接用正则去掉。但这里有个度的问题去掉“有限公司”可以去掉“科技”“信息”“网络”就要慎重因为这些词往往是商号的一部分比如“百度网络科技有限公司”和“百度科技有限公司”就是两家不同公司。简称问题更麻烦。“中国石油化工股份有限公司”到了业务人员嘴里就是“中石化”编辑距离算出来可能只有三成相似度但确实是同一个东西。这种场景靠算法解决不了我的做法是维护一张“简称映射表”在原数据里先做一次查找替换把简称换成规范全称再进相似度公式。公式负责“模糊”映射表负责“指路”。注意预处理规则不是越狠越好。剥离太多会把原本有区分度的词也给抹掉导致完全不同的名字算出高分。每加一条规则都要拿一批真实样本跑一遍看误判率是否上升。5.3 阈值拍多少经验值与方法论相似度分数出来以后最大问题就是“多少分算同一”。我的经验参考值是这样的相似度判定建议适用场景98-100直接判定为同一短名称、预处理充分的情况90-98高度匹配可自动处理多数客户名/公司名去重场景80-90人工复核涉及合同、资金、合并没有把握时70-80可能相关需调查简称、别称、词序颠倒时低于70默认不是同一但要注意倒序或简称的极端情况阈值不是死的。我更推荐的做法是先用一部分历史数据做训练集比如抽200条你已经知道正确答案的数据跑一遍相似度看哪些分数段会混入明显的错误结果再把阈值调到那个分界带以下。没有条件做训练时宁可把自动处理的阈值调高一点让更多结果进入人工复核避免合并错数据。5.4 性能优化长文本预判、手动计算与缓存编辑距离是O(m*n)的算法两个100字符的文本要算一万次操作几百行数据就觉得卡。实际使用中我做了三层优化。第一层是长度预判。如果两个字符串的长度差太大相似度天花板会非常低可以直接返回0。比如A长100B长10就算前面9个字符全对上相似度最高也只有10%直接跳过计算。判断公式是Math.abs(pa.length - pb.length) / Math.max(pa.length, pb.length)如果这个值已经大于1 - 阈值/100直接返回0。第二层是手动重算。公式数量多的时候把WPS的计算模式调成“手动”公式选项卡 - 计算选项 - 手动。数据改完以后按快捷键触发重新计算避免每动一个单元格全表公式跟着抖一遍。第三层是尽量避免在公式内部循环遍历一个区域。有的人会在自定义公式里写循环去扫一整列Range然后在单元格里调用这样每次重算都会做一次全表扫描性能极差。我的建议是用一行一个公式做两两比对批量匹配交给辅助列筛选而不是把“查找整列”写进公式里。如果实在要全表扫描用宏写一次性计算算完只把结果值贴回单元格不保留公式。6. 公式上线后的坑#NAME?、宏禁用与跨设备失效6.1 #NAME?错误的第一排查链自定义公式最常见的问题就是单元格里显示#NAME?。我遇到过的情况大概有四种。第一宏被禁用了。文件打开时顶部有黄色安全警告条点击“启用宏”公式马上就活过来。第二函数名拼写错误。自定义函数没有智能提示拼错一个字母它就变成未知名称。第三函数写错了地方。有些人把代码写到了工作表代码区而不是模块区工作表事件代码和自定义函数在WPS的解析优先级上会出问题。第四当前文档不是支持宏的格式保存时把宏弄丢了。排查顺序我建议是先看安全警告条再看代码位置再核对函数名最后重新保存一次并关闭重开。6.2 JS宏环境不是浏览器fetch不存在的真正原因很多人初学JS宏习惯性地用浏览器的API然后报fetch is not defined以为自己代码写错了其实方向就错了。WPS的JSA宿主环境是表格应用不是浏览器没有window、document、fetch这一套Web API。它提供的是WPS的对象模型操作的是单元格、工作表、工作簿。这对自定义公式来说反而是好事。公式应该是一个纯函数输入两个值返回一个分数不依赖网络不依赖外部状态。如果有人在公式里用fetch去拉在线接口每次重算都会发网络请求轻则慢到崩溃重则被安全软件拦下来。我在团队里定的规矩是自定义公式一律不允许联网所有计算必须在本地完成。6.3 文件保存格式与宏分发同事打开后怎么不报警写完函数以后保存格式一定要选对。普通xlsx格式不保留宏你辛辛苦苦写的SIMTEXT函数关掉重开就没了。WPS里需要另存为“Excel启用宏的工作簿.xlsm”或者WPS本身的表格格式.et宏才会保留下来。发给同事时他们第一次打开会看到安全提示“已禁止使用宏”或“是否启用宏”。这是正常的安全机制不要嫌麻烦。要让同事顺利使用有几个办法一是把文件放到公司内部受信任的共享目录然后在“宏设置”里把这个目录加进受信任位置二是文件本身来源要可追溯不要随便从网上下载带宏的文件三是实在不行就在打开时手动点“启用宏”。如果你希望自己以后新建的每个表格都能直接用SIMTEXT可以把模块代码复制出来在目标文件里重新插入模块粘贴代码。WPS目前没有像Excel个人宏工作簿那么顺滑的全局方案我一般就是留一个模板文件每次从模板抄一份。6.4 WPS更新后函数失效与跨平台限制WPS自动更新以后偶尔会出现宏环境变化的情况。我遇到过两次一次是更新后宏安全级别被重置成了“禁用宏”公式全变#NAME?另一次是JSA引擎升级后某些ES5写法里的字符串方法行为有细微变化。解决办法很简单更新完以后先验证一遍核心函数出问题就去“信任中心”查看宏设置再看代码里有没有用到已经被调整的API。跨平台的问题也要提前说。Linux版WPS能打开JS宏文件但部分WPS对象模型API在Linux上支持得并不完整。如果你只在Windows上测试过别指望换到Linux环境一定还能跑尤其是涉及文件操作、外部程序调用这类功能。纯计算类的自定义公式跨平台的风险相对小一些。6.5 弹出自定义项安装时的处理思路用过WPS的应该都遇到过“自定义项安装”弹窗打开一个文件突然提示要安装什么自定义项。这东西在带宏的文档里出现频率更高因为它通常指向文档引用的一些加载项组件。我的处理原则是来源不明的自定义项一律不装先点取消然后到开发工具 - 加载项列表里查看当前文档引用了哪些东西把不需要的加载项取消勾选。加载项不是越多越好尤其是从第三方下载的宏插件很可能夹带私货。如果装错了WPS的加载项目录里已经存在垃圾文件最稳妥的办法是在“加载项”窗口里禁用然后在WPS的安装配置里重置自定义项。这一步不要用非官方工具处理走正常卸载重装或修复流程就好。我自己实际用下来最大的体会是相似度公式不是写完一个函数就结束了它更像一套小规则引擎预处理规则怎么定阈值怎么划匹配结果谁来复核才决定这套工具能不能被团队接受。把SIMTEXT放到共享模板里之后同事从最开始怀疑“这函数算的数准不准”到后来已经习惯直接看分数做初筛前后也就磨合了两周。如果你们团队也在跟脏数据较劲不妨先复制代码跑一条数据再加自己的业务规则进去。
