如何对比两同ID表格的差异值并按指定格式输出至新标签页？

针对Google Sheets中两个含相同ID的表格，要生成Id, sheet1_field, sheet2_field, sheet1_data, sheet2_data格式的差异表，推荐两种最优实现方案，按需选择：

方案一：用内置函数快速实现（无需编程） #

适合数据量不大、字段顺序一致或需要快速出结果的场景。

步骤： #

1. 在当前表格新建标签页，命名为Diff，在A1-E1输入表头：Id、sheet1_field、sheet2_field、sheet1_data、sheet2_data。
2. 在A2单元格粘贴以下公式（假设原表名为Sheet1和Sheet2，ID列是A列，字段从B列开始到Z列）：

=ARRAYFORMULA(
  IFERROR(
    SPLIT(
      FLATTEN(
        IF(
          Sheet1!B2:Z <> Sheet2!B2:Z,
          Sheet1!A2:A & "|" & Sheet1!B1:Z1 & "|" & Sheet2!B1:Z1 & "|" & Sheet1!B2:Z & "|" & Sheet2!B2:Z,
          ""
        )
      ),
      "|"
    )
  )
)

3. 如果两表字段顺序不一致，改用适配字段匹配的公式：

=ARRAYFORMULA(
  LET(
    ids, UNIQUE({Sheet1!A2:A; Sheet2!A2:A}),
    sheet1_fields, Sheet1!B1:Z1,
    sheet2_fields, Sheet2!B1:Z1,
    all_fields, UNIQUE({sheet1_fields; sheet2_fields}),
    diff_array, LAMBDA(id, field, 
      LET(
        val1, IFERROR(VLOOKUP(id, Sheet1!A:Z, MATCH(field, sheet1_fields, 0)+1, FALSE), ""),
        val2, IFERROR(VLOOKUP(id, Sheet2!A:Z, MATCH(field, sheet2_fields, 0)+1, FALSE), ""),
        IF(val1<>val2, {id, field, field, val1, val2}, {})
      )
    ),
    results, FLATTEN(MAKEARRAY(ROWS(ids), COLUMNS(all_fields), 
      (r,c) => diff_array(ids[r], all_fields[c]))),
    FILTER(results, INDEX(results,,1)<>"")
  )
)

该公式会自动遍历所有ID和字段，筛选出值不同的记录并按格式输出。

方案二：用Google Apps Script实现（灵活自动化） #

适合数据量大、需要自定义逻辑（如忽略空值、定时同步差异）的场景。

步骤： #

1. 打开Google Sheets，点击菜单栏扩展程序→Apps 脚本。
2. 删除默认代码，粘贴以下脚本：

function findSheetDifferences() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('Sheet1');
  const sheet2 = ss.getSheetByName('Sheet2');
  const diffSheet = ss.getSheetByName('Diff') || ss.insertSheet('Diff');
  
  // 清空差异表内容（保留表头）
  diffSheet.clearContents();
  diffSheet.getRange(1, 1, 1, 5).setValues([['Id', 'sheet1_field', 'sheet2_field', 'sheet1_data', 'sheet2_data']]);
  
  // 获取两表数据和表头
  const sheet1Data = sheet1.getDataRange().getValues();
  const sheet2Data = sheet2.getDataRange().getValues();
  const sheet1Headers = sheet1Data[0];
  const sheet2Headers = sheet2Data[0];
  
  // 构建ID到行数据的映射，加快查询速度
  const idMap1 = new Map();
  sheet1Data.slice(1).forEach(row => idMap1.set(row[0], row));
  
  const idMap2 = new Map();
  sheet2Data.slice(1).forEach(row => idMap2.set(row[0], row));
  
  // 收集所有唯一ID，避免遗漏单边存在的ID
  const allIds = new Set([...idMap1.keys(), ...idMap2.keys()]);
  const diffRows = [];
  
  allIds.forEach(id => {
    const row1 = idMap1.get(id) || [];
    const row2 = idMap2.get(id) || [];
    
    // 遍历所有字段，对比值差异
    const maxFields = Math.max(sheet1Headers.length, sheet2Headers.length);
    for (let i = 1; i < maxFields; i++) {
      const val1 = row1[i] || '';
      const val2 = row2[i] || '';
      const header1 = sheet1Headers[i] || '';
      const header2 = sheet2Headers[i] || '';
      
      if (val1 !== val2) {
        diffRows.push([id, header1, header2, val1, val2]);
      }
    }
  });
  
  // 将差异结果写入Diff标签页
  if (diffRows.length > 0) {
    diffSheet.getRange(2, 1, diffRows.length, 5).setValues(diffRows);
  }
}

3. 点击脚本编辑器的运行按钮，授权脚本访问表格权限。
4. 运行完成后，自动生成Diff标签页并填充差异数据。

扩展优化： #

• 可添加定时触发器，让脚本定期自动对比更新差异；
• 可修改代码，添加忽略空值、指定对比字段等自定义逻辑。

方案对比 #

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