基于ID对比新旧工作表A-R列数据的Excel公式需求

问题分析 #

你当前使用的INDEX+MATCH公式仅对比了B列数据，且未处理旧表中ID已被删除的场景，导致匹配失效。正确的做法是基于ID精准定位旧表对应行后，对比整行（或指定列）的所有数据，同时覆盖ID不存在的情况。

可用公式方案 #

方案1：适用于Excel 365/2021（支持动态数组）

在新工作表的S2单元格输入以下公式，下拉填充：

=IFERROR(IF(AND('30/11/2023'!A2:R2=XLOOKUP(A2,'30/11/2023'!A:A,'30/11/2023'!A:R)),"No Changes","Changes"),"Changes")

• 逻辑：用XLOOKUP根据ID精准匹配旧表的整行数据，对比新旧行A-R列的所有内容；全匹配返回"No Changes"，若ID在旧表不存在或数据有差异，返回"Changes"。

方案2：适用于旧版Excel（无XLOOKUP）

若使用旧版Excel，可结合INDEX+MATCH与SUMPRODUCT实现，在S2单元格输入：

=IF(ISERROR(MATCH(A2,'30/11/2023'!A:A,0)),"Changes",IF(SUMPRODUCT(--('30/11/2023'!A$2:R$1000=OFFSET(A2,0,0,1,18)))=18,"No Changes","Changes"))

• 逻辑：
  1. 先通过MATCH判断当前ID是否存在于旧表，不存在直接标记"Changes"
  2. 存在则用SUMPRODUCT统计新旧行对应列相等的数量（A-R共18列），数量为18则全匹配，否则标记"Changes"
  3. 需将公式中的'30/11/2023'!A$2:R$1000替换为旧表实际的最大数据行范围

注意事项 #

• 确保A列的ID为唯一值，否则匹配结果会出现错误
• 若仅需对比特定列（而非整行），可修改公式中的列范围（例如将A:R改为B:F）

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