Excel两个版本怎么对比?增、删、改一次全揪出来

表格不翻车 · 05

小编｜努力追赶时代

　　采购明细又发来一版：顺序换了，数量改了，还有项目不见了。把两张表并排，照着第几行往下看，很容易把“挪了位置”当成“内容变了”。

　　真正的改动反而可能漏掉：已移除的项目继续往下办，新加的记录重复处理，修改过的数量仍沿用旧值。两版行数一样，也挡不住这些问题。

　　上一篇讲每月费用明细怎么追加，这回小编和你核对同一批采购明细的前后版本。先认出同一条记录，再问它哪里变了。 以下面向支持XLOOKUP的桌面版Excel，如Microsoft 365、Excel 2021或2024；2016、2019不适用这套公式。

— ◆ —

　　保留两份原文件，把工作副本放进同一工作簿，分别叫“旧版”“新版”，另记来源文件名和原始行位置。先确认两份都是约定范围的完整明细，不是筛选后少导出的节选。

　　有稳定的明细编号，就用它。没有时，可以把“采购单号＋明细行号”组合成比较键。这里的明细行号来自业务记录，不是Excel左边的行序号；换版后重新编号，就得先找对应表。

　　演示表A列单号、B列明细行号，C到F列依次是品名、规格、数量、单价。两版都在G2输入，向下填充：

=IF(OR(A2="",B2=""),"",A2&":"&B2)

　　本例单号为CG-A这类大写编码，明细行号是文本01、02，两个字段都不含冒号。不能把数量、单价这类会变的值拼进键，否则一次改数量就可能被误报成“一删一增”。

　　演示两版各10条，占第2到11行。旧版H2数本版、I2数新版，向下填充：

=IF(G2="","",COUNTIF($G$2:$G$11,G2))=IF(G2="","",COUNTIF(新版!$G$2:$G$11,G2))

　　新版也做一遍，对版范围换成旧版。实际数据更多时，两版范围都要覆盖完整记录。

键为空：进“待确认”。

任一版计数大于1：这个键涉及的两版记录全部进“待确认”，不能只把重复的那一边拿走。

本版1条、对版0条：记“仅旧版”或“仅新版”。

两边都是1条：才进入字段比较。

　　为什么要先数？XLOOKUP查到重复键，会返回首个匹配项，后面的冲突可能被遮住。两份记录都缺号，也不能凭品名相似就认成同一条。完整的状态公式放在演示文件里。

　　只有两版都能唯一对应的记录，才有条件判断“修改了什么”。不能唯一对应的，保留原值和来源，等核实后重跑。

　　把对应状态放J列。对于“可比较”行，在旧版K2取回新版品名：

=IF($J2="可比较",XLOOKUP($G2,新版!$G$2:$G$11,新版!C$2:C$11&"","未匹配",0),"")

　　末尾的0指定精确匹配。横向填到N列，就依次取回规格、数量、单价。返回范围后的&""把值转成比较用文本，保留空白与零的区别。

　　再用=EXACT(C2&"",K2)比较品名，TRUE为相同，FALSE为不同；规格、数量、单价照此逐列比，仅对“可比较”行判结果。本例非空数量、单价均已确认为数值；真实表若混有文本数字或错误值，先核实类型。

　　演示里，CG-A:02数量从20变30；CG-B:02规格和单价都变了；CG-C:01单价从4变成空白。要把这些字段的前后值写出来，不能只涂一个“有修改”的颜色。空白也不能填成0，否则就把缺值改成了一个有含义的数。

　　本例比较的是字段值，字号、底色或单价显示成两位小数，不算内容修改。若你的表还要核对单位、交期等字段，也要纳入比较，不能只查数量就说整行没变。

　　旧版筛“仅旧版”，得到删除项；新版筛“仅新版”，得到新增项；旧版筛字段不同的行，得到修改项。共同记录只从旧版输出一次，避免重复计入。另汇总两版的待确认记录。

　　差异清单至少留：比较键、差异类型、变化字段、旧值、新值、两版来源位置、确认意见。一条记录有两处改动，就列两条字段明细。

　　这里的“删除”只表示新版不再出现，不等于业务上已批准取消；改号、导出遗漏也可能造成“一删一增”，仍要核实原因。

— ◆ —

　　依据微软XLOOKUP、COUNTIF与EXACT官方说明整理；完整公式、构造数据及验证范围见同包《演示数据》。本文由AI辅助整理。