办公软件

WPS 表格核对两份名单:查编号重复、找缺失、标出不一致

约 7 分钟读完
WPS 表格核对两份名单:查编号重复、找缺失、标出不一致

摘要

面对两份名单,手工逐行对照既慢又容易漏。本文以 WPS 表格为例,先规范编号和姓名,再用 COUNTIF 标记重复编号,并用 VLOOKUP、IFERROR 与 TRIM 组合公式双向查找缺失和信息不一致,最后通过筛选与条件格式快速定位,适合老师、行政及经常整理名单的用户。

本文目录

核对名单时,最费时间的往往不是逐行查看,而是找出编号重复、一份名单漏人,以及同一编号对应的姓名不一致。名单一长,纯靠眼睛很容易遗漏。下面以 WPS 表格为例,用 COUNTIF 查重复,再用 VLOOKUP 组合公式自动标出缺失与不一致。

适用设备与系统版本

  1. 优先使用电脑端:Windows 10/11 的 WPS Office 稳定版最适合本教程,macOS 版和网页版的操作基本相同。批量输入公式、筛选与条件格式在电脑端更顺手。
  2. 手机端可以查看结果:华为 HarmonyOS、荣耀 MagicOS、小米/Redmi HyperOS、OPPO/一加 ColorOS、vivo/iQOO OriginOS 以及 iPhone 上的 WPS 客户端均可查看公式结果,但不同版本的筛选和条件格式入口不完全一致,正式核对建议在电脑端完成。
  3. 下文统一用 1000 行作示例:两份表各有 1000 行左右时,可直接使用示例公式;数据更多时,要把公式中的 $1000 改成实际最大行号。

1. 把两份名单整理成统一结构

  1. 新建 WPS 表格,在同一工作簿中建立 名单A、名单B 两个工作表。右键底部的工作表标签,选择“重命名”即可修改。
  2. 两张表都按下面的方式录入:A 列为唯一编号,B 列为姓名,C 列用于编号查重,D 列用于核对结果。不要在第一行之外添加合并单元格。
  3. 统一编号的数据类型。若编号含有前导零,如 001,应先选中 A 列,右键选择“设置单元格格式”,在“数字”选项中选择“文本”,再录入编号。已经按数字格式录入的内容,事后改格式通常无法自动补回前导零,需要重新输入。
  4. 为便于人工检查,可分别选中数据区域,进入“数据→排序”,以“编号”为主要关键字并设为升序。VLOOKUP 本身不要求排序,但重复编号排在一起更容易发现。

2. 用 COUNTIF 找出重复编号

  1. 在 名单A 的 C1 单元格输入“编号查重”,在 C2 输入下面的公式并按 Enter:
=IF(COUNTIF($A$2:$A$1000,A2)>1,"重复","")
  1. 复制 C2 单元格,再选中 C3:C1000 并粘贴。公式会检查每个编号在 A2:A1000 中出现的次数,只要出现两次以上,当前行就显示“重复”。这样首个重复项和后续重复项都会被标出,不会漏掉其中一条。
  2. 切换到 名单B,在 C2 输入同样的公式并向下填充。两张表都要检查,因为只在其中一张表查重,无法防止另一张表存在重复编号。
  3. 需要集中查看时,点击名单数据区域中的任意单元格,选择“数据→筛选”,再在“编号查重”列的下拉菜单中只勾选“重复”,即可把重复记录筛出来。

3. 用 VLOOKUP 标出缺失与信息不一致

  1. 在 名单A 的 D1 输入“核对结果”,在 D2 输入以下公式:
=IFERROR(IF(COUNTIF(名单B!$A$2:$A$1000,A2)>1,"B表编号重复",IF(TRIM(VLOOKUP(A2,名单B!$A$2:$B$1000,2,FALSE))=TRIM(B2),"一致","信息不一致")),"B表缺失")
  1. 输入后按 Enter,再把 D2 的公式向下填充到实际数据末尾。公式中,VLOOKUP(A2,名单B!$A$2:$B$1000,2,FALSE) 表示按 A2 的编号到名单 B 中精确查找,并取 B 列姓名;第四参数必须使用 FALSE,避免近似匹配造成误判。
  2. IFERROR 会把查不到编号时产生的错误转换为“B表缺失”;COUNTIF 会优先识别另一张表中的重复编号;TRIM 用来忽略姓名首尾多余空格,再判断两个姓名是否一致。
核对结果 含义
一致 编号相同,姓名也相同
B表缺失 编号在名单 A 中存在,但名单 B 中没有
B表编号重复 名单 B 中同一编号出现多次,需要先人工核实
信息不一致 编号相同,但两边姓名不同

4. 再反向检查名单 B 多出的记录

只检查名单 A,无法发现“名单 B 有、名单 A 没有”的情况。在 名单B 的 D2 输入下面的公式,并向下填充:

=IFERROR(IF(COUNTIF(名单A!$A$2:$A$1000,A2)>1,"A表编号重复",IF(TRIM(VLOOKUP(A2,名单A!$A$2:$B$1000,2,FALSE))=TRIM(B2),"一致","信息不一致")),"A表缺失")

这里显示“A表缺失”,表示该编号只在名单 B 中出现。两张表都完成公式填充后,缺失记录就能从两个方向全部找出。

5. 用筛选和条件格式标出待处理记录

  1. 选中 D 列结果区域,进入“数据→筛选”,分别筛选“B表缺失”“A表缺失”“信息不一致”和重复编号,便于分批处理。
  2. 需要颜色提示时,选中 C、D 列,进入“开始→条件格式→新建规则”。部分版本会显示“仅为包含以下内容的单元格设置格式”,选择“文本包含”。
  3. 可分别设置规则:缺失和重复使用红色填充,信息不一致使用黄色填充,一致使用绿色填充。颜色只是辅助,最终仍应按文字状态处理,以免打印或复制时混淆。
  4. 对筛出的记录,应回到原始名单核对来源,再修改正确的一侧。不要为了对格式而直接删除记录,尤其是同一编号对应多个姓名的情况。

注意事项

  1. 先处理重复编号:VLOOKUP 遇到多个相同编号时只会返回第一个匹配结果,因此本教程先用 COUNTIF 标出重复,避免把第一条记录误当成唯一记录。
  2. 保持编号类型一致:不要一张表把 001 存成文本,另一张表存成数字 1;录入前也要清除编号首尾空格,否则精确匹配可能失败。
  3. 检查公式范围:新增记录超出第 1000 行后,要同步扩大 $A$2:$A$1000 和 $A$2:$B$1000 的范围。公式中的 $ 用于锁定范围,向下填充时不会整体偏移。
  4. 不要把姓名当唯一键:重名人员应始终按编号核对。若还要核对班级、电话等字段,可在两张表中保持字段顺序一致,再为每个字段分别建立 VLOOKUP 结果列。
  5. 筛选不等于删除:筛选只是暂时隐藏不符合条件的行,不会删除原始数据。名单可能包含个人信息,使用本机 WPS 完成核对,协作时优先使用单位授权的 WPS 或企业空间,不要上传到来历不明的在线表格。

小结

完整流程可以概括为三步:先用 COUNTIF 找出两张表中的重复编号,再用 VLOOKUP 按编号精确查找另一张表的姓名,通过 IFERROR 识别缺失、通过 TRIM 比较姓名,最后用筛选和条件格式集中处理异常。照着示例操作后,两份名单中“谁多谁少、谁重谁不一致”都会自动显示出来。

常见问题

为什么重复编号的每一条都显示“重复”?

这是有意设计的。COUNTIF 会统计每个编号出现的总次数,因此首个重复项和后续重复项都会标出,避免只显示第二条而漏掉同号的第一条。

编号“001”在 VLOOKUP 中查不到怎么办?

先确认两张表中的编号类型一致。含前导零的编号应全部按文本录入,并把 A 列设为文本格式;同时检查编号首尾是否有空格。

两份名单的姓名列顺序不同,还能直接使用公式吗?

可以,但 VLOOKUP 的查找范围必须先放编号列、再放要返回的姓名列。例如范围为 A:B 时,第二列就是姓名;如果列顺序不同,应先调整范围,或把姓名列单独复制到便于查找的位置。

参考来源