如何高效对比Excel工作表，同步旧表中Action为send的记录？

问题背景 #

• 涉及两个动态更新的工作表：Sheet1（旧表，制表符分隔，已完成记录会被系统自动删除）、Sheet2（新表，会新增记录或调整行顺序）
• 核心限制：Request编号与Barcode不唯一，此前用字符串对比法因日期格式兼容问题失效
• 需求：识别Sheet2中「在Sheet1存在且Sheet1对应记录Action字段为send」的行，给Sheet2对应行Action列填充send或用条件格式标记

解决方案 #

方法1：Excel公式法（适合中小数据量） #

假设新旧表的字段顺序为：Request（A列）、Barcode（B列）、日期（C列）、Action（D列）

填充send值

在Sheet2的D2单元格输入以下公式，下拉填充至所有行：

=IF(COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!D:D,"send")>0,"send","")

• 原理：用COUNTIFS多条件匹配，同时校验Request、Barcode和旧表的Action值，只要存在至少一条符合条件的记录，就填充send，完全避开日期格式问题。

条件格式标记

1. 选中Sheet2的Action列（D列）
2. 点击「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
3. 输入公式：

=COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!D:D,"send")>0

4. 设置格式（如填充黄色、加粗字体），完成标记

方法2：Power Query高效法（适合大数据量） #

当数据行数较多时，Power Query的批量处理效率远高于公式：

1. 依次将Sheet1和Sheet2导入Power Query（「数据」→「自表格/区域」）
2. 处理Sheet1：筛选出Action = "send"的记录，仅保留Request、Barcode列（无需保留日期和Action）
3. 处理Sheet2：点击「合并查询」→「合并查询作为新查询」，选择筛选后的Sheet1作为合并对象，匹配键选Request和Barcode，连接类型选「左外部」
4. 在合并后的查询中新增自定义列，输入公式：

= if [合并的Sheet1] <> null then "send" else null

5. 删除多余的合并列，将处理后的结果加载回Excel（替换原Sheet2或生成新表）

注意事项 #

• 若需更精准匹配（比如结合日期），需先统一新旧表的日期格式（如均设置为「短日期」），再在公式或Power Query中添加日期匹配条件
• 因Request和Barcode不唯一，上述方法会匹配所有符合组合条件的记录，只要存在一条旧表的send记录就会标记，符合需求

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