Excel跨工作表多值匹配：验证客户付款是否符合预期（含±10公差）

方案1：COUNTIFS函数（兼容全版本Excel） #

假设：

• 「预期收款表」（表1）：A列=客户标识（ID/名称），B列=预期金额
• 「实际付款表」（表2）：A列=客户标识，B列=实际金额，需在C列标记验证结果

在表2的C2单元格输入以下公式，下拉填充：

=IF(COUNTIFS(表1!$A:$A,$A2,表1!$B:$B,">="&$B2-10,表1!$B:$B,"<="&$B2+10)>0,"ok","不通过")

• 逻辑：同时匹配客户标识，且预期金额落在实际金额±10的区间内，只要存在至少1条符合条件的预期记录，就标记"ok"，完美处理同一客户多条预期的重复项问题。

方案2：MAXIFS/MINIFS组合（精准匹配客户全量预期范围） #

如果需要验证实际金额是否在该客户所有预期金额的整体公差区间内（比如客户有多个预期金额，实际付款只要落在任意一个预期的±10里就算通过），用以下公式：

=IF(AND($B2>=MINIFS(表1!$B:$B,表1!$A:$A,$A2)-10,$B2<=MAXIFS(表1!$B:$B,表1!$A:$A,$A2)+10),"ok","不通过")

• 逻辑：先提取该客户所有预期金额的最小值和最大值，再判断实际金额是否在「最小值-10」到「最大值+10」的范围内，适合客户有多笔分散预期的场景。

方案3：Power Query（大数据量+可追溯匹配） #

如果数据量较大，或需要保留匹配明细，用Power Query处理：

1. 依次将表1和表2导入Power Query编辑器（「数据」选项卡→「自表格/区域」）
2. 选中表2，添加自定义列，输入M语言公式：

= if Table.RowCount(Table.SelectRows(表1, each [客户标识] = [客户标识] and [预期金额] >= [实际金额]-10 and [预期金额] <= [实际金额]+10)) > 0 then "ok" else "不通过"

3. 关闭并上载到Excel，即可得到带验证标记的结果

• 优势：可视化处理重复匹配，可随时回溯哪些预期记录匹配了实际付款，适合复杂数据场景。

关键注意事项 #

• 确保客户标识完全一致：若存在空格、大小写差异，先用TRIM(UPPER(客户列单元格))统一格式后再匹配。
• 若需一对一精准匹配每笔预期与实际（而非客户维度的模糊匹配），必须添加唯一标识（如订单号）作为额外匹配条件，否则只能实现客户维度的批量验证。

内容的提问来源于stack exchange，提问作者Berkay Kerem Doğan