如何提取两个单工作表Excel文件的差异行？无唯一标识BOM场景

方法1：Power Query批量对比（最省心） #

Excel自带的Power Query完全能搞定这类带重复行的BOM对比，操作可视化且无需编码：

• 将两个工作表的数据分别导入Power Query：点击「数据」选项卡→「自表格/区域」，勾选「我的表格有标题」
• 给两个数据集分别添加索引列：「添加列」选项卡→「索引列」→「从1开始」（用来区分内容完全相同的重复行）
• 选择其中一个查询，点击「合并查询」→「合并为新查询」，选择另一个表作为合并对象，选中所有BOM相关列（如物料编码、名称、用量等）作为匹配条件，合并类型选完全外部
• 展开合并后的列，筛选出其中一个表索引列为空的行：空值对应的就是另一个表的新增行，反之则是被删除的行
• 最后将结果关闭并上载到Excel，就能得到完整的差异清单

方法2：COUNTIFS多条件计数（简化版函数方案） #

不用拼接整行，直接用COUNTIFS多条件计数，比单COUNTIF更精准：

• 假设旧BOM在Sheet1，新BOM在Sheet2，数据从第2行开始，涉及列A到D
• 在Sheet1的E2单元格输入公式，下拉填充：

  =COUNTIFS(Sheet2!$A:$A,$A2,Sheet2!$B:$B,$B2,Sheet2!$C:$C,$C2,Sheet2!$D:$D,$D2)
  

  结果为0的行，就是旧BOM中被删除的行
• 在Sheet2的E2单元格输入公式，下拉填充：

  =COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B2,Sheet1!$C:$C,$C2,Sheet1!$D:$D,$D2)
  

  结果为0的行，就是新BOM中新增的行
• 若要统计重复行的数量差异（比如旧BOM有3个相同行，新BOM剩1个），可以用：

  =COUNTIFS(Sheet2!$A:$A,$A2,...) - COUNTIFS(Sheet1!$A:$A,$A2,...)
  

  负数代表旧BOM多了对应数量的重复行，正数则是新BOM新增了重复行

方法3：条件格式快速标记差异 #

如果只需快速查看差异，不用提取数据，用条件格式即可：

• 选中Sheet1的所有数据行，点击「开始」→「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
• 输入公式，设置填充色（比如红色）：

  =SUMPRODUCT(--(MMULT(--(Sheet2!$A$2:$D$1000=$A2:$D2),ROW($A$1:$D$1)^0))=0)
  

  这样Sheet1中不在Sheet2的行会被标记
• 同理给Sheet2设置公式，标记不在Sheet1的行：

  =SUMPRODUCT(--(MMULT(--(Sheet1!$A$2:$D$1000=$A2:$D2),ROW($A$1:$D$1)^0))=0)
  

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