首页/资讯中心/详情

Excel模糊匹配实战：从通配符到Power Query的完整解决方案

Excel模糊匹配实战：从通配符到Power Query的完整解决方案
📅 发布时间：2026/8/3 4:54:15

1. 从“找不同”到“找相似”：为什么我们需要模糊匹配？

做数据分析或者日常办公，谁还没在Excel里遇到过这种头疼事呢？手里有两份名单，一份是供应商全称“北京某某科技有限公司”，另一份是财务系统导出的简称“北京科技”；或者一份是客户地址“上海市浦东新区张江路123号”，另一份是“上海浦东张江路123号”。肉眼一看就知道是同一个，但Excel的VLOOKUP或MATCH函数用精确匹配一查，直接给你返回个“#N/A”，告诉你查无此人。

这就是精确匹配的局限：它要求两个单元格的内容必须像双胞胎一样，连一个空格、一个标点、一个大小写都不能差。但在现实世界里，数据录入的随意性、系统间的差异、人工手误，导致“相同”的事物在表格里往往以“相似”的面目出现。这时候，“模糊匹配”就成了救命稻草。它的核心思想不是“找一模一样”，而是“找最像的那个”。这不仅仅是省去了手动比对的繁琐，更是将数据处理的逻辑从僵硬的“是非题”升级为灵活的“选择题”，让Excel能像人一样，理解数据的“意图”而非仅仅比较字符。

从你提供的热搜词也能看出，大家的需求非常具体且迫切：从“在一个表中找出另一个表出现的数据”到“excel数据清洗”，模糊匹配是其中绕不开的核心技能。它不仅是函数公式的简单应用，更是一种结合了文本处理、逻辑判断和概率思维的数据整合方法。接下来，我们就抛开那些华而不实的理论，直接进入实战，看看如何用Excel里现成的工具和函数，把“模糊匹配”这件事办得明明白白。

2. 核心武器库：Excel内置的模糊匹配三板斧

在深入具体函数之前，我们得先理清Excel实现模糊匹配的几种底层思路。它们各有适用场景，就像工具箱里的不同工具，用对了事半功倍。

2.1 通配符匹配：最直接的模式查找

这是最基础、最直观的模糊匹配方式，主要用在VLOOKUP、MATCH、COUNTIF、SUMIF等支持通配符的函数里。

  • 星号*：代表任意数量的任意字符（包括零个字符）。比如，查找以“科技”结尾的公司，条件可以写成"*科技"。
  • 问号?：代表单个任意字符。比如，查找第二个字是“东”的三字人名，条件可以写成"?东?"。

实战场景：你有一份产品清单，产品编号规则是“品类代码+序号”，比如“A001”、“B205”。现在需要统计所有A类产品的总销售额。你可以用SUMIF函数：=SUMIF(产品编号列, "A*", 销售额列)。这里的"A*"就模糊匹配了所有以A开头的产品编号。

注意：通配符匹配本质上是“模式匹配”，它不计算相似度。"张*"会匹配到“张三”、“张伟”、“张三丰的剑”，但它无法判断“张小三”和“章三”哪个更像“张三”。对于包含通配符本身的文本进行查找时，需要在通配符前加波浪号~进行转义，例如查找包含“重要”的文本，条件应写为"*~*重要~**"。

2.2 函数组合拳：文本处理+逻辑判断

当通配符不够用，比如需要处理错别字、简繁体、空格不一致时，我们就需要祭出函数组合。核心思路是：先将文本“标准化”，再进行比较。

  1. 清理与统一：

    • TRIM(): 移除文本首尾的所有空格（对中间多余空格无效，这是常踩的坑）。
    • CLEAN(): 移除文本中所有不可打印字符（通常来自系统导出）。
    • LOWER()/UPPER(): 将所有文本转换为统一的小写或大写，消除大小写差异。
    • SUBSTITUTE(): 替换或删除特定字符。例如，统一删除所有空格、横杠“-”、下划线“_”：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", ""), "-", ""), "_", "")。

  2. 提取关键部分：

    • LEFT()/RIGHT()/MID(): 当标识符有固定位置时，直接提取。比如从身份证号中提取出生日期码。
    • FIND()/SEARCH(): 结合MID使用，当标识符位置不固定但有关键分隔符（如“-”、“#”）时，定位并提取。FIND区分大小写，SEARCH不区分。

实战案例：匹配公司名称。表1是“阿里巴巴（中国）网络技术有限公司”，表2是“阿里巴巴网络技术有限公司”。直接匹配肯定失败。 我们可以创建一个辅助列，使用公式：=SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A2)), "（中国）", ""), "有限公司", "")。 这个公式依次做了：去空格、转小写、删除“（中国）”、删除“有限公司”。处理后的两个名称都变成了“阿里巴巴网络技术”，此时再用VLOOKUP精确匹配，成功率就大大提升了。

2.3 相似度匹配的“神器”与“平替”

这是模糊匹配的进阶阶段，目标是量化两个文本的相似程度。Excel本身没有直接计算字符串相似度（如编辑距离、余弦相似度）的内置函数，但我们可以通过一些巧妙的方法逼近。

  • “神器”思路：Fuzzy Lookup插件微软官方提供了一个强大的免费插件，就叫“Fuzzy Lookup”。它专门用于在Excel表格间进行模糊匹配。你只需要指定要匹配的两列数据，它可以设置相似度阈值（比如85%），并返回匹配结果和置信度。这对于处理大量、杂乱的数据非常高效。安装后它在“数据”选项卡下，界面友好，是解决复杂模糊匹配问题的首选方案。

  • “平替”函数：COUNTIF+ 通配符的极限应用在没有插件的情况下，我们可以用COUNTIF模拟一个简单的“包含即匹配”逻辑。例如，判断A2单元格的内容是否包含于B列某个单元格中：=IF(COUNTIF($B$2:$B$100, "*" & A2 & "*")>0, "匹配", "不匹配")这个公式会在B列中查找任何包含A2内容的单元格。反过来，查找B列内容是否包含于A2，则用"*" & B2 & "*"。这种方法在匹配关键词、型号部分字段时非常有用，但它无法区分“包含”的程度，也无法处理顺序错乱（如“技术网络” vs “网络技术”）。

3. 实战拆解：多场景下的模糊匹配公式设计与避坑

理解了核心思路，我们来看几个具体的、高频的实战场景，并给出可直接套用的公式和必须留意的坑。

3.1 场景一：根据不完整的关键词查找并返回完整信息

需求：你有一个产品型号库（表1），型号是“iPhone 13 Pro Max 256GB 深空灰”。现在有一份销售清单（表2），只写了简略型号“13 Pro Max”。你需要从表1中匹配出完整信息并填充到表2。

公式设计： 在销售清单（表2）的B2单元格（假设型号简写在A2），输入以下数组公式（输入后需按Ctrl+Shift+Enter结束，新版Excel动态数组下直接按Enter）：=INDEX(表1!$B$2:$B$1000, MATCH(TRUE, ISNUMBER(SEARCH(A2, 表1!$A$2:$A$1000)), 0))然后向右、向下填充，获取其他信息（如价格、颜色）。

公式拆解：

  1. SEARCH(A2, 表1!$A$2:$A$1000): 在表1的完整型号列中搜索表2的简写型号。如果找到，返回位置数字；如果找不到，返回错误值#VALUE!。SEARCH不区分大小写且支持通配符行为（这里不需要显式使用）。
  2. ISNUMBER(...): 将上一步的结果转换为TRUE/FALSE。找到（是数字）为TRUE，找不到（是错误）为FALSE。结果是一个TRUE/FALSE数组。
  3. MATCH(TRUE, ..., 0): 在TRUE/FALSE数组中查找第一个TRUE的位置，即找到第一个包含简写型号的完整型号所在的行号。
  4. INDEX(..., ...): 根据找到的行号，从表1的信息列（如价格列）中返回对应的值。

避坑指南：

  • 匹配唯一性风险：如果简写“13 Pro”可能匹配到“iPhone 13 Pro”和“iPad Pro 13寸”，这个公式会返回第一个匹配到的。这不是公式的错，而是数据简写本身有歧义。解决方案是在简写中增加更多限定词，或使用更精确的匹配逻辑（如必须同时包含“13”和“Pro Max”）。
  • 性能问题：在数据量极大（数万行）时，这种数组公式或大量使用SEARCH/FIND的公式会显著拖慢计算速度。此时应考虑使用Fuzzy Lookup插件，或将数据导入Power Query进行处理。
  • 绝对引用与相对引用：公式中的表1!$A$2:$A$1000使用了绝对引用（$），是为了在向下填充公式时，查找范围不会错乱。这是新手最容易忽略导致#REF!错误的地方。

3.2 场景二：对比两列数据，找出“可能相同”的项

需求：有两列客户名称，需要找出哪些是可能重复的（即模糊相同的）。

方法1：使用条件格式高亮显示

  1. 选中第一列数据（例如A列）。
  2. 点击“开始” -> “条件格式” -> “新建规则”。
  3. 选择“使用公式确定要设置格式的单元格”。
  4. 输入公式：=COUNTIF($B$2:$B$100, "*"&A2&"*")+COUNTIF($B$2:$B$100, "*"&SUBSTITUTE(A2, " ", "")&"*")>0
  5. 设置一个高亮格式（如填充黄色）。 这个公式的含义是：如果B列中，存在某个单元格完全包含A2的内容，或者包含A2去掉空格后的内容，则高亮A2。你可以根据需要叠加更多的SUBSTITUTE来去除“公司”、“有限公司”等字样。

方法2：使用辅助列标识在C2输入公式：=IF(SUMPRODUCT(--ISNUMBER(SEARCH(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), B2)))>LEN(A2)*0.6, "可能重复", "")这是一个简化版的字符重叠度检查。它检查A2中超过60%的字符是否在B2中出现。这只是一个启发式方法，并不精确，但对于快速筛查很有帮助。更严谨的做法需要用到VBA自定义函数来计算编辑距离或相似度。

3.3 场景三：处理包含数字和单位的混合文本匹配

需求：物料清单中，规格可能是“螺栓 M10*50”，库存表中是“螺栓 M10 x 50”。需要匹配。

公式设计： 核心是使用SUBSTITUTE统一分隔符和单位，并用TRIM清理空格。=VLOOKUP(TRIM(SUBSTITUTE(SUBSTITUTE(A2, "x", "*"), " ", "")), TRIM(SUBSTITUTE(SUBSTITUTE(表2!$A$2:$A$100, "x", "*"), " ", "")), 1, FALSE)这个公式先将“x”和空格都处理掉，统一成“M1050”的格式再进行精确查找。关键在于找出文本中“变”与“不变”的部分。数字和字母“M”通常不变，而分隔符“”, “x”, “X”, “×”和空格是变化的。统一它们即可。

4. 当函数遇到瓶颈：Power Query与VBA的进阶解决方案

当数据量庞大、模糊规则复杂，或者需要批量化、自动化处理时，Excel函数会显得力不从心。这时就需要请出更强大的工具。

4.1 使用Power Query进行智能模糊合并

Power Query（Excel中在“数据”选项卡下的“获取和转换数据”）是处理模糊匹配的利器，尤其是其“模糊匹配”合并功能。

操作步骤：

  1. 将你的两个表格分别加载到Power Query编辑器。
  2. 选择需要合并的查询，点击“合并查询”。
  3. 在合并对话框中，选择用于匹配的两个字段。
  4. 最关键的一步：勾选“使用模糊匹配执行合并”。
  5. 点击“模糊匹配选项”展开详细设置：
    • 相似度阈值：拖动滑块，例如设置为0.8（80%）。这是控制匹配“松紧度”的核心。
    • 忽略大小写、忽略标点符号、忽略字符类型：根据需求勾选，能极大提高匹配成功率。
    • 最大匹配数：设定一个值返回最相似的N个结果，而不是第一个。
  6. 确定后，Power Query会进行匹配并生成一个新列，你可以展开它来获取匹配到的所有信息。

优势：Power Query的模糊匹配算法比简单的函数组合强大得多，且处理过程可记录、可重复。一旦设置好，后续数据更新只需一键刷新。它特别适合每月、每周都需要进行的固定格式数据清洗与合并任务。

4.2 利用VBA自定义函数实现编辑距离算法

对于有编程基础的用户，VBA可以提供终极的灵活性。你可以编写一个自定义函数来计算两个字符串的“编辑距离”（Levenshtein Distance），即把一个字符串转换成另一个所需的最少单字符编辑（插入、删除、替换）次数。相似度可以用1 - 编辑距离 / 最大字符串长度来估算。

下面是一个经典的VBA编辑距离函数示例：

Function LevenshteinDistance(ByVal String1 As String, ByVal String2 As String) As Integer Dim i As Integer, j As Integer Dim len1 As Integer, len2 As Integer Dim matrix() As Integer len1 = Len(String1) len2 = Len(String2) ReDim matrix(0 To len1, 0 To len2) For i = 0 To len1 matrix(i, 0) = i Next i For j = 0 To len2 matrix(0, j) = j Next j For i = 1 To len1 For j = 1 To len2 If Mid(String1, i, 1) = Mid(String2, j, 1) Then matrix(i, j) = matrix(i - 1, j - 1) Else matrix(i, j) = Application.WorksheetFunction.Min( _ matrix(i - 1, j) + 1, _ ' Deletion matrix(i, j - 1) + 1, _ ' Insertion matrix(i - 1, j - 1) + 1) ' Substitution End If Next j Next i LevenshteinDistance = matrix(len1, len2) End Function Function FuzzyMatchSimilarity(ByVal str1 As String, ByVal str2 As String) As Double Dim dist As Integer Dim maxLen As Integer dist = LevenshteinDistance(str1, str2) maxLen = Application.WorksheetFunction.Max(Len(str1), Len(str2)) If maxLen = 0 Then FuzzyMatchSimilarity = 1 Else FuzzyMatchSimilarity = 1 - dist / maxLen End If End Function

将这段代码放入VBA编辑器（ALT+F11，插入模块），你就可以在工作表中像使用普通函数一样使用=FuzzyMatchSimilarity(A2, B2)，它会返回一个0到1之间的相似度分数。你可以基于这个分数用IF函数判断是否匹配（例如 >0.8）。

注意事项：VBA自定义函数在大量计算时可能较慢，且需要启用宏的工作簿才能使用。它提供了最高的匹配精度和灵活性，但牺牲了一定的便捷性和安全性（需信任宏）。

5. 模糊匹配的“最后一公里”：策略选择与结果校验

掌握了所有技术工具，最后决定成败的往往是策略和细节。模糊匹配不是一劳永逸的魔法，而是一个“配置-执行-校验”的循环过程。

策略选择流程图：

  1. 评估数据质量：先人工浏览样本数据，看看不匹配的主要原因是空格/大小写，还是缩写/简称，或是错别字/多字少字。
  2. 选择匹配方法：
    • 简单不一致（空格、横杠、大小写）：首选SUBSTITUTE、TRIM、LOWER等函数清洗后精确匹配。
    • 包含关系（关键词匹配）：首选COUNTIF+ 通配符*，或SEARCH/FIND函数。
    • 复杂文本相似（名称、地址）：首选Fuzzy Lookup插件或Power Query模糊合并。
    • 需要极高自定义精度：考虑VBA自定义相似度函数。
  3. 设置并测试阈值：如果使用插件或自定义函数，一定要用已知的样本数据测试不同的相似度阈值（如85%， 90%），观察匹配结果的准确率和召回率，找到一个平衡点。
  4. 结果校验与人工复核：这是最关键的一步。任何模糊匹配的结果，尤其是相似度在阈值边缘的（比如82%匹配度），必须进行人工抽样复核。可以按相似度排序，重点检查匹配度最低的那一批和匹配度非100%的那一批。没有人工校验的模糊匹配，很容易产生严重的错误关联。

常见陷阱与心得：

  • 过度匹配：比如用“公司”去匹配，可能会把“公司大楼”也匹配进来。尽量使用更精确的右侧匹配（“*公司”）或结合其他字段（如地区代码）进行复合匹配。
  • 性能黑洞：在数万行数据上使用数组公式或大量SEARCH函数，会导致Excel卡顿甚至崩溃。对于大数据量，务必转向 Power Query 或 VBA，它们处理循环和数组的效率更高。
  • 数据预处理的重要性：我个人的经验是，花在数据预处理（清洗、标准化）上的时间，往往能换来匹配成功率翻倍的提升。在尝试复杂的模糊匹配算法前，先用简单的替换和清理函数过一遍数据，常常能解决大部分问题。
  • 保留原始数据：所有用于模糊匹配的公式或操作，务必在原始数据的副本上进行，或者新增辅助列来存放清洗后的数据。永远不要直接覆盖原始数据列。

模糊匹配的本质，是在数据的不完美中寻找规律和联系。它没有唯一的正确答案，只有最适合当前场景的解决方案。从简单的通配符到复杂的算法，工具在升级，但核心思路不变：理解你的数据，定义清晰的“模糊”规则，然后用合适的工具去执行，最后用你的业务判断力去验收。这个过程本身，就是数据分析能力从“操作工”到“解决者”的一次重要跃迁。