如何用Excel公式比对含部分匹配数据的两张表格？

核心思路 #

针对近似文本+数字的匹配需求，通过文本标准化+模糊匹配+多条件校验的组合公式实现，无需VBA即可批量处理超100行数据。

具体公式方案 #

1. 近似文本匹配返回ID #

假设：

• 表1：A列存目标文本，B列存对应ID（范围A2:B101）
• 表2：D列存近似待匹配文本，需在E列返回对应ID

在E2单元格输入公式后下拉：

=XLOOKUP("*"&TRIM(LOWER(D2))&"*", TRIM(LOWER(A:A)), B:B, "无匹配", 2)

• TRIM(LOWER())：统一文本大小写、清除多余空格，解决近似文本的格式差异
• "*"&...&"*"：通配符匹配，允许文本前后存在无关字符
• 最后参数2：启用通配符匹配模式

2. 多变量（文本+数字）匹配返回ID #

如果需要同时匹配近似文本和近似数字（比如允许数字微小误差）：

• 表1：A列文本，B列数值，C列ID（范围A2:C101）
• 表2：E列近似文本，F列数值，需在G列返回对应ID

在G2单元格输入公式（旧版Excel需按Ctrl+Shift+Enter触发数组计算，新版自动支持）：

=INDEX(C:C, MATCH(1, (TRIM(LOWER(E2))=TRIM(LOWER(A:A)))*(ABS(F2-B:B)<=0.01), 0))

• ABS(F2-B:B)<=0.01：允许数字存在±0.01的误差，可根据实际调整阈值
• (条件1)*(条件2)：实现多条件同时满足的逻辑

3. 匹配后比对总和 #

如果需要比对两表中匹配项的数值总和：

• 表1：A列文本，B列数值
• 表2：D列近似文本，E列对应总和

在F2单元格输入公式后下拉，返回两表总和的差值：

=SUMIFS(B:B, A:A, "*"&TRIM(LOWER(D2))&"*") - E2

• 差值为0说明总和一致，非0则存在差异

批量处理技巧 #

• 所有公式输入后直接下拉填充，即可自动应用到100+行数据
• 若文本差异较大（比如错别字），可先在辅助列用SUBSTITUTE()替换常见错误字符，再进行匹配

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