如何用Excel函数基于连续字符相似度匹配实现数据规范化？

核心思路 #

通过计算待修正数据与每个参考数据的最长连续匹配字符长度，匹配长度最长的参考值即为最相似的正确数据，以此实现B列错误数据的替换。

公式实现（Excel 365/2021 动态数组版本） #

假设参考数据范围为A2:A100，待修正数据在B2单元格，在C2输入以下公式后下拉填充即可：

=INDEX(A:A,MODE(IF(LEN(MAX(IF(ISNUMBER(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),COLUMN(INDIRECT("1:"&LEN(B2)))),A$2:A$100)),MID(B2,ROW(INDIRECT("1:"&LEN(B2))),COLUMN(INDIRECT("1:"&LEN(B2))))))=LEN(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),COLUMN(INDIRECT("1:"&LEN(B2))))),ROW(A$2:A$100))))

公式逻辑拆解 #

• MID(B2,ROW(INDIRECT("1:"&LEN(B2))),COLUMN(INDIRECT("1:"&LEN(B2))))：生成B2单元格所有可能的连续子字符串
• SEARCH(...,A$2:A$100)：检查每个子字符串是否存在于参考数据中
• MAX(...)：筛选出存在于参考数据里的最长连续子字符串长度
• IF(...)：定位包含该最长子字符串的所有参考数据行号
• MODE(...)：若多个参考数据匹配，取出现频率最高的行；仅一个匹配时直接返回对应行号
• INDEX(A:A,...)：根据行号提取对应的正确参考数据

旧版Excel兼容方案（无动态数组） #

如果使用旧版Excel，输入上述公式后需按Ctrl+Shift+Enter以数组公式形式确认，再下拉填充到所有待修正行。

个性化优化 #

• 优先匹配开头连续字符：若错误数据多为开头正确、后续出错，可修改子字符串生成逻辑，仅提取从开头开始的连续子串：

  =INDEX(A:A,MODE(IF(LEN(MAX(IF(ISNUMBER(SEARCH(MID(B2,1,ROW(INDIRECT("1:"&LEN(B2)))),A$2:A$100)),MID(B2,1,ROW(INDIRECT("1:"&LEN(B2))))))=LEN(MID(B2,1,ROW(INDIRECT("1:"&LEN(B2))))),ROW(A$2:A$100))))
  

• 取最先匹配的参考值：若多个参考数据匹配最长子串，希望优先选择列表中最先出现的，将公式中的MODE替换为MIN即可：

  =INDEX(A:A,MIN(IF(LEN(MAX(IF(ISNUMBER(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),COLUMN(INDIRECT("1:"&LEN(B2)))),A$2:A$100)),MID(B2,ROW(INDIRECT("1:"&LEN(B2))),COLUMN(INDIRECT("1:"&LEN(B2))))))=LEN(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),COLUMN(INDIRECT("1:"&LEN(B2))))),ROW(A$2:A$100))))
  

内容的提问来源于stack exchange，提问作者Marco Spettini