Excel 怎么比较两列找出不同的数据
两列名单对不上,靠眼睛一行行核太容易漏。讲清条件格式、COUNTIF 和表格比对三种做法,以及空白格和格式不一致这两个坑。
两张名单、两份报价要找出哪里不一样。靠眼睛一行行比对,几十行还能看,上百行一定漏。Excel 里能比两列的方法不止一种,选错了不是比不出来,而是把本来相同的行也标成不同。
入口在哪
常用的入口有三个,位置不同,适合的场景也不同。
- 条件格式:在「开始」选项卡的样式区域里,选「突出显示单元格规则」或「新建规则」,适合只用颜色标差异、不改原数据。
- COUNTIF 函数:在空白列写公式,计数为 0 就说明这个值在另一列找不到,适合要统计条数、后面还要筛选。
- Power Query:在「数据」选项卡里把两列各自建成查询再合并,适合几万行以上、需要反复更新的表。
动手前先想清楚:是比「这一列的值在另一列有没有」,还是比「同一行的两个值相不相等」。前者查存在,后者逐行比,公式写法完全不同,搞混了结果会整体错位。
具体步骤
以最常见的情况为例:A 列是上个月的名单,B 列是这个月的名单,要找出只在一边出现的名字。
- 在 C1 和 D1 各写一个标题,比如「A 列独有」「B 列独有」,别让公式和标题挤在一起。
- 在 C2 写判断 A2 在 B 列出现次数的公式:
=COUNTIF($B$2:$B$500,A2)。区域用绝对引用,往下拖才不会跑偏。 - 把 C2 填充到最后一行,结果为 0 的就是只在 A 列出现的值。
- 在 D2 写反向公式:
=COUNTIF($A$2:$A$500,B2),同样往下填充。 - 选中 C 列用筛选把 0 挑出来,这是第一份差异清单;D 列重复同样操作。
- 只想看颜色的话,选中 A 列数据区域,条件格式新建规则选「使用公式确定要设置格式的单元格」,公式写
=COUNTIF($B$2:$B$500,$A2)=0,再指定填充色。 - 最后把公式列选择性粘贴成数值,再删掉辅助列。留着公式,下次改数据结果会跟着变。
几个坑
- 末尾多一个空格。两个单元格看着一样,COUNTIF 却判为不同,先用查找替换清掉空格,或用 TRIM 包一层。
- 数字存成了文本。一边数字格式、一边文本格式,值相同也匹配不上,用「分列」一步转成常规格式。
- 区域没锁。公式往下拖时引用区域跟着下移,越往下越容易漏掉开头几行,差异就凭空出现。
- 忽略大小写和全半角。COUNTIF 默认不区分大小写,而全角括号和半角括号会被当成不同字符,比之前要统一。
- 两列行数不同还硬按行比。行数对不齐时逐行比对没有意义,先排序或改用存在性判断。
怎么验证
第一步做反向验证:拿 B 列独有清单再去 A 列查一次,两份结果的数量应该能对上,加起来再和总行数核一遍,能发现漏行。
第二步抽样手工核对。从标出的差异里随机抽五到十条肉眼确认,再抽几条被判为相同的确认确实一致。只验差异不验相同,容易漏掉误判。
第三步检查边界,公式最容易在区域首尾出错。确认后把结果粘成数值存档,表头写清比对日期和范围。
常见问题
两列顺序不一样怎么比
顺序不同就不要逐行比。给每一列单独算「在对方列里出现几次」,结果为 0 的就是差异。要按行对齐,先把两列分别排序,或用 VLOOKUP 按关键字取过来再逐行比。
想连整行一起比,不只是比一列
把几列拼成一个辅助键再比:=A2&B2&C2,两边的键分别生成后互相判断是否存在。拼接时中间加分隔符更稳妥,避免两段内容拼完碰巧相同,比完记得删掉辅助键列。
数据量很大,公式算不动怎么办
几万行以上,公式整列填充会明显变慢。改用 Power Query 把两边合并后展开,只留匹配状态这一列,计算在内存里完成,速度差别很大。也可以先把数据粘成数值,减少实时重算。
一句话总结:比两列的关键是先分清找存在还是按行比,锁好区域、清掉空格和格式差异,最后用反向计数和抽样核对收尾。