上周帮运营同事整理一份客户名册时我发现同一个公司被录入了三种互不相同的写法“北京智联天下科技有限公司”“智联天下北京科技有限公司”以及一句简短的“智联天下”。Excel自带的“删除重复项”一个都没抓到因为它们并不完全相同。当时我就意识到Excel数据清洗里最磨人的问题不是“完全重复”而是“看起来重复、又不完全重复”。这次借着一个办公自动化的实战需求我把这套思路完整沉淀下来怎么用字符串相似度算法把Excel中这种“隐形重复数据”成批找出来。文章既讲算法原理也给可直接运行的Python脚本适合经常和Excel表打交道、想用自动化代替手工肉眼查重的朋友参考。1. 一版“看起来重复”的客户表Excel自带查重为什么抓不到1.1 三种最常见的“伪不同”数据在真实表格里两条记录明明是同一个实体字符串层面却完全不同通常逃不出下面几类情况格式差异多余空格、全角半角混用、标点符号位置不同。比如“智联天下科技”和“智联天下 科技”肉眼能看出来COUNTIF却算它们不重复。文字差异错别字、简繁体混用、同音字替换。比如“张叁”和“张三”“王静”和“王婧”字符层面不是一回事。结构差异简称与全称、公司名带不带地区/括号/行业描述。比如“智联天下”和“智联天下北京科技有限公司”长度差出一大截。这三种情况Excel原生功能都很难处理因为它们本质上属于“模糊匹配”范畴而不是“精确匹配”。1.2 Excel内置去重功能的三个盲区我经常看到有人拿“删除重复项”按钮硬扛这类数据不是说按钮没用而是它的定位是处理完全一致的记录。在遇到上述三种情况时它会暴露三个明显盲区第一它只能做逐字段全等判断。两行数据只要任一字符不同就会被判定为两条新记录。第二高级筛选和COUNTIF虽然支持通配符但通配符能力有限。*和?只能处理位置固定的缺字或加字处理不了“顺序不同但意思相同”的字符串。第三它没有“相似度”概念。Excel不知道“北京分公司”和“北京分部”有多像只知道“不相等”。要做模糊查重必须跳出Excel函数引入算法层面“量化相似”的思维。1.3 为什么相似度算法比“等号判断”更接近人的判断人对“两条数据是否重复”的判断靠的是语义而不是字符。看到“智联天下”和“智联天下北京科技有限公司”你会自动忽略修饰成分提取核心词“智联天下”然后觉得它们指代同一家。字符串相似度算法做的事情其实就是把这种“主观感受”翻译成可计算的数字给两个字符串打分越像分越高。当分数超过某个阈值就判定为疑似重复。到这里思路就清晰了用相似度得分替代“等号”把肉眼比对替换成批量计算。2. 三种常用的字符串相似度算法各自解决哪一类重复字符串相似度算法不止一种《索引》里最常用的是以下三种它们的视角各不相同适配的重复形态也不同。2.1 编辑距离错别字与短文本场景的首选编辑距离Levenshtein Distance衡量的是“把一个字符串变成另一个字符串最少需要多少次插入、删除、替换操作”。比如“张三”变成“张叁”需要1次替换编辑距离就是1。距离越小文本越像。实际使用时通常会把它转成一个0到1之间的相似度分数similarity 1 - (编辑距离 / max(len_a, len_b))“北京分公司”和“北京分部”编辑距离是1替换“公”为“部”max_len是5相似度就是0.8。可以看出编辑距离对错别字、短文本特别友好。但它也有一点局限对词语顺序不敏感的问题处理不太好。“智联天下科技”和“科技智联天下”需要多次移动操作实际是多次删除插入编辑距离会给出很低的分尽管语义是一样的。2.2 n-gram与杰卡德系数应对顺序变化和插入冗余词n-gram的基本思路是把字符串切成连续的片段集合。以bigram两个字符的连续片段为例“智联天下”的bigram集合是{智联, 联天, 天下}。再算两个集合的交集大小除以并集大小就是杰卡德相似系数。这种视角天然地容忍文字顺序变化和中间插入冗余词。“智联天下科技”的bigram是{智联, 联天, 天下, 科技}“科技智联天下”的bigram是{科技, 技智, 智联, 联天, 天下}。交集是{智联, 联天, 天下}并集有6个元素相似度0.5。换成编辑距离这两个字符串的相似度很可能不到0.3。所以当数据里出现大量“词语重组”和“中间夹带无关内容”时n-gram 杰卡德的组合明显更稳。2.3 TF-IDF与余弦向量化长文本与语义层面的进阶选择如果处理的是长文本比如商品描述、公告正文、地址详情字符级别的算法会显得吃力。这时可以先对文本分词再用TF-IDF把词转成向量最后算余弦相似度。两个文本在向量空间里越接近说明用词越重合。但在Excel查重场景里大部分字段是短字符串公司名、人名、物料名、地址分词和向量化的收益有限还容易引入额外误差。所以我在短文本场景下很少用它更多把它当作一个边界说明——算法管得越来越宽但对应的实现成本和调参难度也在上升。算法核心思路优势短板最适场景编辑距离最小编辑次数对错别字敏感直觉清晰不擅长词语乱序人名、短编号n-gram 杰卡德字符片段集合重合度容忍乱序与冗余词短文本噪音较大公司名、品名TF-IDF 余弦词向量空间距离能发现语义近似短文本上提效有限长描述、正文3. 用Python把算法和Excel串起来完整可跑的查重脚本3.1 准备环境与读取Excel实际项目中我用的组合是pandas openpyxl rapidfuzz。rapidfuzz是专门做模糊匹配的库兼容difflib和python-Levenshtein的常见写法但底层是C实现速度更快安装也更省心。读取Excel时有一个细节能帮你避开大量后续问题读进来的所有列强制转成字符串。import pandas as pd df pd.read_excel(客户表.xlsx, sheet_nameSheet1, dtypestr) print(df.head())如果不加dtypestr编号列和手机号很容易被读成数值型前导零丢失、长编码变成科学计数法。这类问题在查重阶段会变成“假差异”干扰算法判断。3.2 字符串归一化查重前必须做的一次“大扫除”很多人拿到数据直接跑相似度效果往往很差。原因在于原始数据里混着大量格式噪音这些噪音会拉低分数。正确的做法是先做归一化把“明知道是格式差异”的部分提前抹平。import re import unicodedata def normalize_text(text): if pd.isna(text): return # 统一全角半角 text unicodedata.normalize(NFKC, str(text)) # 转小写对英文、编码有用 text text.lower() # 去除所有空格和常见标点 text re.sub(r[\s。、()【】\[\]\-—_/\\,.!?;:], , text) return text.strip()这段代码做了三件事全角转半角、英文转小写、去掉空格和常见标点。做完之后“智联天下北京科技有限公司”和“智联天下-北京-科技有限公司”会在同一个基准上参与比较而不是被标点和括号干扰。要注意去括号这一步要结合业务判断。如果括号里的内容代表不同分公司或不同用途比如“张三临时”和“张三正式”去括号可能会把本来不该合并的数据合并掉。我会建议先把括号内容单独拆列保存再做归一化这样后面需要时还能拿回来。3.3 核心代码逐对计算相似度并分组归一化之后就可以计算相似度了。这里我建议同时拿两个指标做聚合不要迷信单一算法from rapidfuzz import fuzz def combined_similarity(text_a, text_b): if not text_a or not text_b: return 0 # ratio 基于编辑距离token_set_ratio 对词序不敏感 return max( fuzz.ratio(text_a, text_b), fuzz.token_set_ratio(text_a, text_b) )fuzz.ratio拿编辑距离做底适合抓错别字fuzz.token_set_ratio会先分词、去重、排序再比较适合抓“词语乱序”和“多词少词”的情况。取两者的最大值相当于让两种算法互相补位。然后是批量比较的部分。我通常不直接删除数据而是把疑似重复的对子全部筛出来输出成一张待审清单交给业务方确认后再清洗。这样做有两个好处避免算法误判造成不可逆的数据损失方便追溯清洗规则做完之后还能向团队解释“为什么这两行被合并了”。def find_duplicate_pairs(df, text_colname, threshold85): texts df[text_col].fillna().tolist() n len(texts) pairs [] for i in range(n): for j in range(i 1, n): a, b texts[i], texts[j] # 长度差超过一半的大概率不是同一实体跳过可以大幅提速 if abs(len(a) - len(b)) max(len(a), len(b)) * 0.5: continue score combined_similarity(a, b) if score threshold: pairs.append({ row_a: i, row_b: j, score: score, text_a: a, text_b: b, }) return pd.DataFrame(pairs) result find_duplicate_pairs(df, text_col客户名称, threshold85) result.to_excel(疑似重复清单.xlsx, indexFalse)这里面有一个小的优化点长度差超过百分之五十的直接跳过。“智联天下科技”和“智联天下北京科技有限公司”虽然长度差很大但只要一个是另一个的近似子串还是会被保留下来而“abc”和“某大型集团股份有限公司”这种长度差离谱的组合根本不用计算编辑距离省时间。3.4 为什么用“分组编号”而不是直接标记重复上面输出的是两两配对表但实际使用中会碰到更复杂的情况A和B相似B和C相似A和C只是勉强相似。如果只输出两两关系整理起来依然是多对多的乱麻。更实用的做法是引入“并查集”把互相连通的疑似重复记录合并成同一个组然后输出一个带组编号的清单class UnionFind: def __init__(self, n): self.parent list(range(n)) def find(self, x): while self.parent[x] ! x: self.parent[x] self.parent[self.parent[x]] x self.parent[x] return x def union(self, x, y): rx, ry self.find(x), self.find(y) if rx ! ry: self.parent[ry] rx uf UnionFind(len(df)) for _, peak in result.iterrows(): uf.union(int(peak[row_a]), int(peak[row_b])) df[group_id] df.index.to_series().apply(lambda idx: uf.find(idx)) df.to_excel(分组结果.xlsx, indexFalse)这样每一行都会有一个group_id同一个组里的人互相之间就是“疑似重复家族”。后续人工复核时只看组内记录即可不用再翻两两配对表。4. 阈值不是拍脑袋定的用数据分布校准相似度门槛4.1 从分数分布里找拐点threshold85这个值是我常用的起点但它不是一个“万能真理”。不同表的书写习惯差异太大正确做法是先让算法算一遍分数看分数分布再决定阈值。scores [] for i in range(min(200, n)): for j in range(i 1, min(200, n)): scores.append(combined_similarity(texts[i], texts[j])) pd.Series(scores).describe(percentiles[.5, .75, .9, .95, .99])这个分布的规律通常会呈现两极分化大部分记录对的分数在0到40之间极小部分在95到100之间中间地带60到85比较稀疏。如果分布真是这样选85作为阈值就很稳但如果中间地带很密集就说明数据书写风格差异很大85可能会漏掉一批需要调低。我习惯的做法是输出分数区间和对应的对数人工抽查每个区间的10对记录判断它们是否真的重复分数区间对数人工抽查结论90-100132基本全是重复80-8945大概七成是重复70-7923不到三成是重复60以下1987基本不相关如果忘了抽查直接删很容易把大量“仅是长得像但不是同一个”的记录合并掉。比如“北京金隅集团”和“北京金隅股份有限公司”可能是同一家但“北京金隅大厦”却是一个建筑物分数接近也不该合并。4.2 先严后松的两轮策略面对一张完全没清洗过的表我不建议一上来就追求“沙尽水清”。正确节奏是第一轮用高阈值比如90跑一遍把最明显的重复先拎出来。这些数据人工审核成本最低。审核确认后删除或合并这批确定重复项。第二轮再把阈值降到80再跑一遍这时候选集小了很多人工核对的压力也就小了。这个策略的核心是控制信用成本。算法输出的候选名单最终还是要靠人点头的先输出100%确定的再慢慢放权给低分数区间整体风险更可控。4.3 阈值参数也是业务规则我后来发现阈值本质上是一个业务规则而不是技术参数。比如银行客户姓名里出现同音不同字几乎可以断定是误录阈值可以放宽但产品批次号里“A-1002”和“A-102”完全不相同哪怕相似度再高也不能合并。所以在跑算法之前一定要先和业务方确认“这条字段里多少相似度才允许判定为重复”否则技术养出来的模型业务是不敢接的。5. 数据量上来之后性能优化与工程化改造5.1 先做精确碰撞再谈模糊匹配Excel里常见的几千条数据用双循环还能扛得住一旦到几万、几十万行O(n²)的双循环就完全不可行了。以5万行为例双循环要比较12.5亿对这不现实。第一步优化是先用归一化后的字符串做一轮精确匹配。由于很多“看起来很像”的记录在归一化后会变成完全相同的字符串那它们根本不需要走模糊计算直接归到一个组就行。df[key] df[客户名称].apply(normalize_text) df[group_id_exact] df[key].groupby(df[key]).ngroup()之后对非精确重复的行再按组进行模糊匹配候选集瞬间缩小。5.2 用n-gram索引剪枝把O(n²)变成近似线性模糊匹配这步还能用“n-gram倒排索引”进一步做剪枝。思路很简单只有共享至少一个n-gram的字符串才可能存在高相似度。以bigram为例“智联天下”包含{智联, 联天, 天下}它只需要和同样包含这三个片段中任意一个的字符串计算编辑距离完全不共享片段的记录可以直接排除。from collections import defaultdict def build_ngram_index(texts, n2): index defaultdict(list) for idx, text in enumerate(texts): if len(text) n: continue ngrams set() for i in range(len(text) - n 1): ngrams.add(text[i:in]) for g in ngrams: index[g].append(idx) return index实际跑下来剪枝之后真正需要计算相似度的对子通常只剩原来的百分之几几万行数据也能在几分钟内跑完。5.3 数据量再大就要换工具了几十万行以上Python双循环加索引也扛不住这时候我一般会切换成两个方向一是用SQLite加spellfix1扩展把字符串放进数据库里模糊匹配可以借助B-tree索引和并行查询处理百万级数据也相对从容。二是直接上RapidFuzz的多进程模式或者把字符串向量化成n-gram集合后用HNSW之类的向量索引做最近邻搜索。这已经是模糊匹配的工程化玩法了普通办公场景一般用不到但遇到需要全量跑相似度的场景时值得作为备选方案。6. 这套方案在真实办公场景中的延伸与边界6.1 除了客户表还能拿来干嘛同样的思路我在考勤表、库存表、发票台账里都用过。典型例子物料名称清洗“304不锈钢螺丝M4*10”和“304不锈钢螺丝M4X10”编辑距离很低但n-gram算法能识别出它们的高度重合合并后会显著减少库存表里的重复物料。地址查重“北京市朝阳区建国路88号”和“北京朝阳建国路88号”在归一化并去掉“市”“区”后缀后相似度能达到90以上对做地理位置归类很有帮助。发票抬头去重企业开票信息表里经常混着“某某公司”和“某某有限责任公司”用这套脚本跑一遍开票系统就能少弹出一堆重复抬头。关键是抽象的“字符串相似度”能力是通用的换一张表只需要换列名算法逻辑不用动。6.2 算法管不了的重复语义重复与规范不统一相似度算法有一个明显边界它处理不了表面不同、语义相同的文本。“中国移动通信集团北京有限公司”和“北京移动”从字符角度看几乎没有任何重合但人在阅读时一眼就知道是同一家。这类重复只能依赖业务词典、正则规则和知识库映射字符串算法在这个场景下能做的贡献很有限。所以我的建议是把这套方案定位成“帮你把99%的简单重复拎出来”的自动化工具剩下1%的语义级重复单独交给基于词典或人工规则的系统去处理。两者配合而不是指望一个算法搞定所有场景。6.3 一次最值得投入的自动化改造在各式各样的Excel数据清洗需求中字符串相似度查重是我个人觉得性价比极高的一次自动化改造。原因是它的逻辑清晰、可解释性强同时能直接替代大量机械式的人工比对。比起写复杂Excel公式和宏用Python脚本处理后还能自动输出审核清单和分组编号全过程可追溯。如果你手头正压着一批看着“乱七八糟”的Excel数据先从一个小样本跑一遍分数分布开始可能就有惊喜。