最新

Excel 怎么比较两列找出不同的数据

两列名单对不上,靠眼睛一行行核太容易漏。讲清条件格式、COUNTIF 和表格比对三种做法,以及空白格和格式不一致这两个坑。

两张名单、两份报价要找出哪里不一样。靠眼睛一行行比对,几十行还能看,上百行一定漏。Excel 里能比两列的方法不止一种,选错了不是比不出来,而是把本来相同的行也标成不同。

入口在哪

常用的入口有三个,位置不同,适合的场景也不同。

  • 条件格式:在「开始」选项卡的样式区域里,选「突出显示单元格规则」或「新建规则」,适合只用颜色标差异、不改原数据。
  • COUNTIF 函数:在空白列写公式,计数为 0 就说明这个值在另一列找不到,适合要统计条数、后面还要筛选。
  • Power Query:在「数据」选项卡里把两列各自建成查询再合并,适合几万行以上、需要反复更新的表。

动手前先想清楚:是比「这一列的值在另一列有没有」,还是比「同一行的两个值相不相等」。前者查存在,后者逐行比,公式写法完全不同,搞混了结果会整体错位。

具体步骤

以最常见的情况为例:A 列是上个月的名单,B 列是这个月的名单,要找出只在一边出现的名字。

  1. 在 C1 和 D1 各写一个标题,比如「A 列独有」「B 列独有」,别让公式和标题挤在一起。
  2. 在 C2 写判断 A2 在 B 列出现次数的公式:=COUNTIF($B$2:$B$500,A2)。区域用绝对引用,往下拖才不会跑偏。
  3. 把 C2 填充到最后一行,结果为 0 的就是只在 A 列出现的值。
  4. 在 D2 写反向公式:=COUNTIF($A$2:$A$500,B2),同样往下填充。
  5. 选中 C 列用筛选把 0 挑出来,这是第一份差异清单;D 列重复同样操作。
  6. 只想看颜色的话,选中 A 列数据区域,条件格式新建规则选「使用公式确定要设置格式的单元格」,公式写 =COUNTIF($B$2:$B$500,$A2)=0,再指定填充色。
  7. 最后把公式列选择性粘贴成数值,再删掉辅助列。留着公式,下次改数据结果会跟着变。

几个坑

  • 末尾多一个空格。两个单元格看着一样,COUNTIF 却判为不同,先用查找替换清掉空格,或用 TRIM 包一层。
  • 数字存成了文本。一边数字格式、一边文本格式,值相同也匹配不上,用「分列」一步转成常规格式。
  • 区域没锁。公式往下拖时引用区域跟着下移,越往下越容易漏掉开头几行,差异就凭空出现。
  • 忽略大小写和全半角。COUNTIF 默认不区分大小写,而全角括号和半角括号会被当成不同字符,比之前要统一。
  • 两列行数不同还硬按行比。行数对不齐时逐行比对没有意义,先排序或改用存在性判断。

怎么验证

第一步做反向验证:拿 B 列独有清单再去 A 列查一次,两份结果的数量应该能对上,加起来再和总行数核一遍,能发现漏行。

第二步抽样手工核对。从标出的差异里随机抽五到十条肉眼确认,再抽几条被判为相同的确认确实一致。只验差异不验相同,容易漏掉误判。

第三步检查边界,公式最容易在区域首尾出错。确认后把结果粘成数值存档,表头写清比对日期和范围。

常见问题

两列顺序不一样怎么比

顺序不同就不要逐行比。给每一列单独算「在对方列里出现几次」,结果为 0 的就是差异。要按行对齐,先把两列分别排序,或用 VLOOKUP 按关键字取过来再逐行比。

想连整行一起比,不只是比一列

把几列拼成一个辅助键再比:=A2&B2&C2,两边的键分别生成后互相判断是否存在。拼接时中间加分隔符更稳妥,避免两段内容拼完碰巧相同,比完记得删掉辅助键列。

数据量很大,公式算不动怎么办

几万行以上,公式整列填充会明显变慢。改用 Power Query 把两边合并后展开,只留匹配状态这一列,计算在内存里完成,速度差别很大。也可以先把数据粘成数值,减少实时重算。

一句话总结:比两列的关键是先分清找存在还是按行比,锁好区域、清掉空格和格式差异,最后用反向计数和抽样核对收尾。