Excel两张名单怎么找不同?COUNTIF筛出新增和缺失(WPS适用)
- 2026-11-07 01:23:02
上周一份名单，这周又来一份。顺序变了，总人数却没变：到底新增了谁，又少了谁？
分别拿新、旧名单的编号，到另一份名单里数次数，再筛选0。顺序不用相同，也不需要一行一行对齐。前提是两份名单使用同一套人员编号；不要只靠可能重名的姓名判断。
先看完成后要得到什么
下面都是虚构练习编号。旧名单与新名单各有5条，但新名单新增了U006、U007，同时缺少旧名单中的U002、U004。人数相同，不代表成员相同。这里的“新增”和“缺失”仅相对这两份名单，不直接代表入职、离职或其他业务结论。
操作路线只有三段：准备两张编号表 → 分别在对方表里计数 → 两边各筛选一次0。只查一个方向，只能得到一类结果。
一、把两份名单放进同一个工作簿
先备份原文件。在同一个工作簿中新建两个工作表，分别命名为Old和New，表示旧名单和新名单；下面公式使用这两个名称。
两张表都在A1写“编号”，数据放在A2:A6。Old按顺序填写U001、U002、U003、U004、U005；New按顺序填写U003、U001、U006、U005、U007。
本例编号都不重复，也没有空白。实际操作时，先排除空编号，确认两边都是同一含义、同一书写规则的编号，保留应保留的前导零。若业务只能用“部门＋编号”区分成员，应先确定完整标识，不能只查编号这一列。
二、在新名单里找“新增”
第1步：打开New，在B1写“在旧名单出现次数”，然后在New的B2输入：
=COUNTIF(Old!$A$2:$A$6,A2)普通回车即可。它会拿New当前行的A2，到Old的A2:A6中计数。$A$2:$A$6固定查找区域，最后的A2没有锁行，向下填充时会跟着变为A3、A4等。
第2步：选中New的B2:B6，用向下填充把公式填满。对应U003、U001、U006、U005、U007，结果应依次是1、1、0、1、0。
第3步：在New选中A1:B6，打开“数据”中的筛选，在B列表头的筛选菜单里只保留数字0。此时显示U006、U007，它们在旧名单里没有出现，是相对旧名单的新增编号。筛选时把编号与辅助列一起选中，不只选B列。
三、回到旧名单找“缺失”
第1步：打开Old，在B1写“在新名单出现次数”，然后在Old的B2输入：
=COUNTIF(New!$A$2:$A$6,A2)第2步：将Old的B2:B6向下填充。对应U001、U002、U003、U004、U005，结果应依次是1、0、1、0、1。
第3步：在Old选中A1:B6，打开筛选，只保留B列的0。得到U002、U004：旧名单有，新名单找不到。Old与New是两张独立工作表，各自在自己的表内筛选。
两条公式的区别是查哪张表。在New查Old，找新增；在Old查New，找缺失。不要把公式里的工作表名都改成当前表，否则可能只是在查自己。
四、结果不对，先检查这几件事
次数大于0，只说明对方有这个编号。出现2、3等数字，表示对方有重复记录。它不能证明两边重复次数一致，也不能核对金额、职位等其他字段。不要为了让结果变成1就删除原始明细；若要输出不重复的编号清单，可在结果副本中按编号去重，原始记录保留。
区域必须覆盖全部数据。本例只有5条，所以范围到第6行；你的旧名单和新名单长度可以不同，两条公式要分别覆盖对方实际的完整范围。新增记录后同步检查查找范围、公式填充范围和筛选范围。
看着一样，也要核对原始值。多余空格、不可见字符、前导零或数据类型差异都可能影响匹配。先弄清编号规则再整理，不要随手删除编号中的有效字符；纯数字长编号已丢失的位数也不能靠本方法恢复。
COUNTIF不是严格区分大小写的逐字符比较。条件中的星号、问号有通配含义，波浪号用于转义。本例只用U开头的短编号；如果编号包含这些符号、超过255个字符，或业务要求区分大小写，不直接套用这两条示例。
本文按Windows桌面Excel 2016、2019、2021、2024、Microsoft 365，以及支持COUNTIF和筛选的WPS表格操作；WPS Office 2019 PC版已有相应功能。不同版本按钮名称可能略有不同。无需动态数组，也不用Ctrl+Shift+Enter；手机端不直接照搬桌面菜单。
做完后，先用U006、U002各抽查一次，再把结果用于自己的业务。你遇到的更常见情况，是新名单多了编号，还是两边都有同名或重复编号？
