展开知识库目录
两张 Excel 名单怎样核对缺失、重复和不一致
用 7 行示例说明怎样先检查主键，再用 COUNTIF 和 XLOOKUP 找出仅 A、仅 B、重复和字段不一致；数据量大时改用 Power Query。
对账前先确认主键，不要直接拿姓名匹配
姓名、公司名和商品名通常都不适合做唯一键：会重名、会改写，也可能混入空格。优先使用员工编号、订单号等业务主键；如果必须组合多个字段，要先让业务负责人确认组合规则。
不要在原文件上直接清洗。分别复制 A 表和 B 表，保留原始编号、原表行号和原值；新增标准化列时，把编号统一为文本并处理首尾空格，但不要覆盖可能带前导零的原始值。
这 7 行同时包含重复、缺失和字段变化
以下姓名和数据均为演示用虚构内容。A 表第 4、5 行故意使用同一个员工编号。
| 来源 | 原表行 | 员工编号 | 姓名 | 部门 |
|---|---|---|---|---|
| A 表 | 2 | E001 | 张三 | 销售 |
| A 表 | 3 | E002 | 李四 | 财务 |
| A 表 | 4 | E003 | 王五 | 研发 |
| A 表 | 5 | E003 | 王五 | 研发 |
| B 表 | 2 | E001 | 张三 | 销售 |
| B 表 | 3 | E002 | 李四 | 运营 |
| B 表 | 4 | E004 | 赵六 | 研发 |
把两组数据转成表格后，按这个顺序加列
选中两组数据并分别转换为 Excel 表格，把表格名称改成 A表 和 B表。以下公式假设两表都有 员工编号 与 部门 列；不同地区设置可能使用分号而不是逗号作为参数分隔符。
A 表新增列
主键检查
=IF([@员工编号]="","主键为空",IF(COUNTIF(A表[员工编号],[@员工编号])>1,"主键重复","可比较"))
A 表新增列
判断是否存在于 B 表
=IF(COUNTIF(B表[员工编号],[@员工编号])=0,"仅A表","两表都有")
A 表新增列
比较部门字段
=IF([@匹配状态]<>"两表都有","",IF([@部门]=XLOOKUP([@员工编号],B表[员工编号],B表[部门]),"一致","部门不一致"))
B 表新增列
反向找仅 B 表的记录
=IF(COUNTIF(A表[员工编号],[@员工编号])=0,"仅B表","两表都有")
XLOOKUP 不适用于 Excel 2016 和 Excel 2019。旧版本可以改用 INDEX/MATCH；数据量大或需要重复刷新时，直接使用 Power Query 更合适。主键重复未处理前，不要接受“取第一条”的匹配结果。
差异应该被拆成四类，而不是一个“不同”
| 员工编号 | 差异类型 | A 表位置/值 | B 表位置/值 | 处理动作 |
|---|---|---|---|---|
| E001 | 一致 | 第 2 行 / 销售 | 第 2 行 / 销售 | 无需处理 |
| E002 | 字段不一致 | 第 3 行 / 财务 | 第 3 行 / 运营 | 找业务负责人确认哪一版有效 |
| E003 | A 表主键重复；仅 A 表 | 第 4、5 行 / 研发 | 无 | 先处理重复，再确认是否应补入 B 表 |
| E004 | 仅 B 表 | 无 | 第 4 行 / 研发 | 确认是新增记录还是 A 表漏项 |
总行数不能代替对账。这个例子里 A、B 行数接近，但仍同时存在重复、缺失和字段变化。
数据量大或每月都要对账，改用 Power Query 全外连接
- 01
分别从 A 表和 B 表创建查询
通过“数据 -> 从表格/区域”导入，明确把主键类型设为文本。
- 02
先在每个查询里检查重复和空主键
主键不合格时先输出异常查询，不继续合并。
- 03
用员工编号执行完全外部连接
Full Outer 会保留两边所有记录，才能同时看见仅 A 和仅 B。
- 04
展开 B 表字段并增加条件列
分别标记仅 A、仅 B、字段差异和一致，不把空值自动当作相等。
- 05
加载到新的结果工作表
保留查询步骤和刷新结果，不覆盖两个原始数据源。
匹配结果不对，先检查主键格式和公式范围
出现大量仅 A 和仅 B
- 原因
- 一边是数字、一边是文本，或编号包含前导零、空格和不可见字符。
- 怎么改
- 保留原值，新增标准化列；抽查 10 个应当匹配的编号后再全量计算。
XLOOKUP 返回第一条，但业务说结果错
- 原因
- 主键在查找表中重复。
- 怎么改
- 先用 COUNTIF 输出全部重复键；重复未解决前不要接受字段比较结果。
差异数为 0，但肉眼能看到变化
- 原因
- 公式引用错列、计算模式为手动，或新行没有进入范围。
- 怎么改
- 检查表格名称和结构化引用，强制重算，并用一个已知差异做阳性对照。