两张Excel表怎么快速核对数据?3种方法一次讲清楚

摘要：

用一个实际案例讲清两张Excel表如何核对数据，包括COUNTIF判断数据是否存在、XLOOKUP核对字段变化，以及整表差异对比的使用场景。

作中经常会碰到这种情况：

昨天发出去一份员工名单，今天同事又发回来一个“最新版”。

文件名可能只差一个“最终版”，打开以后看着也差不多。

但真正麻烦的问题来了：

到底改了哪里？

少了哪些人？

多了哪些数据？

原来的部门、金额、状态有没有被改过？

如果只有二三十行，把两张表放在一起看还能应付。

可一旦到了几百行、上千行，再靠眼睛一行一行找，不仅慢，还很容易漏。

这篇就用一个简单的员工表案例，讲讲我比较常用的3种核对方法。

先准备两张需要核对的表

假设现在有两张员工信息表。

这里有一个很重要的前提：

每个员工都有唯一工号。

所以我们不需要根据姓名去猜谁是谁，而是直接用“工号”对应两张表的数据。

实际工作里，这个唯一字段也可能是订单号、物料编号、身份证明编号、产品编码等。

方法一：用 COUNTIF，先判断“这条数据还在不在”

这是最简单的一种情况。

比如我们现在只想知道：

Sheet1里的员工，在更新后的Sheet2里还能不能找到？

可以在 Sheet1 新增一列“是否存在”，然后在 D2 输入：

=COUNTIF(Sheet2!A:A,A2)

第一次看到这个公式可能有点懵，其实拆开以后非常简单。

Sheet2!A:A 是什么意思？

它代表：

Sheet2里的整个A列。

而我们前面规定，Sheet2的A列放的是“工号”。

所以这部分就是告诉Excel：去新表的工号列里找。

A2 又是什么？

A2是 Sheet1 当前这一行的工号。

假设 A2 的内容是：

1001

那么整个公式实际上就是在问：

Sheet2的工号列里，一共有几个1001？

如果结果是：

1 → 找到了

0 → 没找到

因此，如果1003在Sheet2里已经不存在，COUNTIF返回的就是0。

写完第一行以后，把公式向下填充，就能批量检查整张名单。

COUNTIF适合什么情况？

它特别适合回答：

“有没有？”

比如：

员工还在不在？

订单号有没有出现？

物料编号有没有登记？

两份名单有没有缺少某个人？

但它有一个明显的局限：

它只能告诉你“有没有”，不能直接告诉你具体内容改了什么。

这就要用到第二种方法。

方法二：用 XLOOKUP，核对具体字段有没有变化

还是刚才的员工表。

现在1001这个员工在两张表里都存在，但我们还想知道：

他的部门有没有变化？

可以先把 Sheet2 里的最新部门匹配到 Sheet1。

在 Sheet1 的 D2 输入：

=XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"未找到")

这个公式稍微长一点，但还是不要死记。

把它拆成4部分就很好理解。

① A2：我要找谁？

A2里放的是当前员工的工号。

比如：

1001

所以第一步就是告诉Excel：

我要找工号1001。

② Sheet2!A:A：去哪里找？

Sheet2的A列是新表的“工号”。

也就是说：

去新表的工号列里找1001。

③ Sheet2!C:C：找到以后返回什么？

Sheet2的C列是“部门”。

所以找到1001以后，Excel会把同一行的部门返回来。

假设：

Sheet1原来的部门是：

销售部

而XLOOKUP从Sheet2匹配回来的结果是：

市场部

不用继续看，我们已经知道部门发生变化了。

④ "未找到"：没有这个工号怎么办？

如果Sheet2里根本没有这个员工，公式就显示：

未找到

比直接出现一串错误提示更容易看懂。

还可以让Excel自动判断“有没有变化”

如果：

C2 = 原来的部门

D2 = XLOOKUP匹配回来的新部门

那么可以在 E2 输入：

=C2=D2

Excel会直接返回：

TRUE → 两边一样

FALSE → 两边不一样

例如：

销售部 = 市场部

结果自然就是：

FALSE

这样把公式向下填充以后，就不用自己一行一行比较了。

如果你的Excel版本比较旧，没有XLOOKUP，也可以使用 VLOOKUP 完成类似的匹配。

方法三：字段很多时，直接做整表差异对比

到这里可能有人会发现一个问题：

如果我只核对“部门”，XLOOKUP确实很好用。

但如果一张员工表有十几列：

姓名、部门、岗位、工资、状态、入职日期、联系方式……

这些字段全部都要核对怎么办？

当然可以继续写公式。

部门匹配一次，岗位匹配一次，工资再匹配一次……

但列越来越多以后，公式也会越来越多。

更麻烦的是，有些需求不只是“字段有没有变化”，而是需要同时知道：

哪些数据是新增的？

哪些数据被删除了？

哪些员工还在，但某些字段被修改了？

这种情况我就不太建议继续堆公式了。

我自己开发的微信小程序表格工具箱Lite里，专门做了一个Excel对比功能，也是为了解决这种整表核对的问题。

它可以选择两份Excel，再根据实际情况选择对比方式。

比如两张表行顺序完全一致，可以直接按位置比较。

如果行顺序发生变化，也可以根据员工ID、订单号之类的字段，先找到两张表里的同一条数据，再比较其他字段。

甚至可以：

按ID找到同一个员工，只比较“部门”这一列。

最后把差异整理成：

新增 / 删除 / 修改

不用自己给十几列分别写匹配公式。

对比结果还可以导出成新的Excel报告，原来的两个文件不会被覆盖；文件处理也在本地完成，不上传服务器。

这3种方法，到底应该怎么选？

其实没必要纠结哪个方法“最高级”。

我自己的判断很简单：

只想知道一条数据有没有 → COUNTIF

比如核对名单、订单号、物料编号。

想核对一两个具体字段 → XLOOKUP

比如根据工号检查部门、工资、岗位有没有变化。

十几列甚至整张表都要核对 → 表格工具箱Lite

这种时候继续堆公式虽然也能做，但未必是最省时间的办法。

Excel真正提高效率的地方，不是记住多少公式，而是知道：

什么问题，用什么方法最省事。

如果你现在手里刚好有两个版本的Excel，不妨先看看自己的需求到底属于哪一种。

数据不复杂，公式完全够用。

真的到了大量、重复、多字段的核对工作，再考虑交给工具处理。