核对名单时,最费时间的往往不是逐行查看,而是找出编号重复、一份名单漏人,以及同一编号对应的姓名不一致。名单一长,纯靠眼睛很容易遗漏。下面以 WPS 表格为例,用 COUNTIF 查重复,再用 VLOOKUP 组合公式自动标出缺失与不一致。
适用设备与系统版本
- 优先使用电脑端:Windows 10/11 的 WPS Office 稳定版最适合本教程,macOS 版和网页版的操作基本相同。批量输入公式、筛选与条件格式在电脑端更顺手。
- 手机端可以查看结果:华为 HarmonyOS、荣耀 MagicOS、小米/Redmi HyperOS、OPPO/一加 ColorOS、vivo/iQOO OriginOS 以及 iPhone 上的 WPS 客户端均可查看公式结果,但不同版本的筛选和条件格式入口不完全一致,正式核对建议在电脑端完成。
- 下文统一用 1000 行作示例:两份表各有 1000 行左右时,可直接使用示例公式;数据更多时,要把公式中的
$1000改成实际最大行号。
1. 把两份名单整理成统一结构
- 新建 WPS 表格,在同一工作簿中建立
名单A、名单B两个工作表。右键底部的工作表标签,选择“重命名”即可修改。 - 两张表都按下面的方式录入:A 列为唯一编号,B 列为姓名,C 列用于编号查重,D 列用于核对结果。不要在第一行之外添加合并单元格。
- 统一编号的数据类型。若编号含有前导零,如
001,应先选中 A 列,右键选择“设置单元格格式”,在“数字”选项中选择“文本”,再录入编号。已经按数字格式录入的内容,事后改格式通常无法自动补回前导零,需要重新输入。 - 为便于人工检查,可分别选中数据区域,进入“数据→排序”,以“编号”为主要关键字并设为升序。VLOOKUP 本身不要求排序,但重复编号排在一起更容易发现。
2. 用 COUNTIF 找出重复编号
- 在
名单A的 C1 单元格输入“编号查重”,在 C2 输入下面的公式并按 Enter:
=IF(COUNTIF($A$2:$A$1000,A2)>1,"重复","")
- 复制 C2 单元格,再选中 C3:C1000 并粘贴。公式会检查每个编号在 A2:A1000 中出现的次数,只要出现两次以上,当前行就显示“重复”。这样首个重复项和后续重复项都会被标出,不会漏掉其中一条。
- 切换到
名单B,在 C2 输入同样的公式并向下填充。两张表都要检查,因为只在其中一张表查重,无法防止另一张表存在重复编号。 - 需要集中查看时,点击名单数据区域中的任意单元格,选择“数据→筛选”,再在“编号查重”列的下拉菜单中只勾选“重复”,即可把重复记录筛出来。
3. 用 VLOOKUP 标出缺失与信息不一致
- 在
名单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表缺失")
- 输入后按 Enter,再把 D2 的公式向下填充到实际数据末尾。公式中,
VLOOKUP(A2,名单B!$A$2:$B$1000,2,FALSE)表示按 A2 的编号到名单 B 中精确查找,并取 B 列姓名;第四参数必须使用FALSE,避免近似匹配造成误判。 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. 用筛选和条件格式标出待处理记录
- 选中 D 列结果区域,进入“数据→筛选”,分别筛选“B表缺失”“A表缺失”“信息不一致”和重复编号,便于分批处理。
- 需要颜色提示时,选中 C、D 列,进入“开始→条件格式→新建规则”。部分版本会显示“仅为包含以下内容的单元格设置格式”,选择“文本包含”。
- 可分别设置规则:缺失和重复使用红色填充,信息不一致使用黄色填充,一致使用绿色填充。颜色只是辅助,最终仍应按文字状态处理,以免打印或复制时混淆。
- 对筛出的记录,应回到原始名单核对来源,再修改正确的一侧。不要为了对格式而直接删除记录,尤其是同一编号对应多个姓名的情况。
注意事项
- 先处理重复编号:VLOOKUP 遇到多个相同编号时只会返回第一个匹配结果,因此本教程先用 COUNTIF 标出重复,避免把第一条记录误当成唯一记录。
- 保持编号类型一致:不要一张表把
001存成文本,另一张表存成数字1;录入前也要清除编号首尾空格,否则精确匹配可能失败。 - 检查公式范围:新增记录超出第 1000 行后,要同步扩大
$A$2:$A$1000和$A$2:$B$1000的范围。公式中的$用于锁定范围,向下填充时不会整体偏移。 - 不要把姓名当唯一键:重名人员应始终按编号核对。若还要核对班级、电话等字段,可在两张表中保持字段顺序一致,再为每个字段分别建立 VLOOKUP 结果列。
- 筛选不等于删除:筛选只是暂时隐藏不符合条件的行,不会删除原始数据。名单可能包含个人信息,使用本机 WPS 完成核对,协作时优先使用单位授权的 WPS 或企业空间,不要上传到来历不明的在线表格。
小结
完整流程可以概括为三步:先用 COUNTIF 找出两张表中的重复编号,再用 VLOOKUP 按编号精确查找另一张表的姓名,通过 IFERROR 识别缺失、通过 TRIM 比较姓名,最后用筛选和条件格式集中处理异常。照着示例操作后,两份名单中“谁多谁少、谁重谁不一致”都会自动显示出来。






