浏览 AI使用教程
两个 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 | 赵六 | 研发 |
把两组数据转成表格后，按这个顺序加列
将两份数据分别按Ctrl+T转为表格，表格名称设为A表和B表，原始列至少含员工编号、部门。以下新增列名必须按标题填写，避免引用不存在的“匹配状态”。先填两边主键检查，再填匹配状态，最后比较字段。
先查A侧空键与重复
=IF([@员工编号]="","主键为空",IF(COUNTIF(A表[员工编号],[@员工编号])>1,"主键重复","可比较"))
B侧同样检查
=IF([@员工编号]="","主键为空",IF(COUNTIF(B表[员工编号],[@员工编号])>1,"主键重复","可比较"))
仅两侧键都唯一时才放行
=IF([@主键检查]<>"可比较",[@主键检查],IF(COUNTIF(B表[员工编号],[@员工编号])=0,"仅A表",IF(COUNTIF(B表[员工编号],[@员工编号])>1,"B表主键重复","两表唯一")))
反向找缺失并阻断A侧重复
=IF([@主键检查]<>"可比较",[@主键检查],IF(COUNTIF(A表[员工编号],[@员工编号])=0,"仅B表",IF(COUNTIF(A表[员工编号],[@员工编号])>1,"A表主键重复","两表唯一")))
不对重复键运行XLOOKUP比较
=IF([@匹配状态]<>"两表唯一","",IF([@部门]=XLOOKUP([@员工编号],B表[员工编号],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 行数接近，但仍同时存在重复、缺失和字段变化。
额外加一行B侧重复，比较列必须停止
| 改动 | A表E002匹配状态 | 部门比较 |
|---|---|---|
| 在B表新增E002/市场；原E002/运营保留 | B表主键重复 | 空白，不能把运营当唯一答案 |
| 删除刚添加的教学测试行，恢复原始7行样例 | 两表唯一 | 部门不一致 |
这是独立反例练习，操作副本。主键异常优先返回异常状态；结果表中的“仅A”等存在性描述还需分别查看两表，不能把重复记录直接合并。
数据量大或每月都要对账，改用 Power Query 全外连接
- 01
分别从 A 表和 B 表创建查询
通过“数据 -> 从表格/区域”导入，明确把主键类型设为文本。
- 02
先在每个查询里检查重复和空主键
主键不合格时先输出异常查询，不继续合并。
- 03
用员工编号执行完全外部连接
Full Outer 会保留两边所有记录，才能同时看见仅 A 和仅 B。
- 04
展开 B 表字段并增加条件列
分别标记仅 A、仅 B、字段差异和一致，不把空值自动当作相等。
- 05
加载到新的结果工作表
保留查询步骤和刷新结果，不覆盖两个原始数据源。
匹配结果不对，先检查主键格式和公式范围
出现大量仅 A 和仅 B
- 原因
- 一边是数字、一边是文本，或编号包含前导零、空格和不可见字符。
- 怎么改
- 保留原值，新增标准化列；抽查 10 个应当匹配的编号后再全量计算。
XLOOKUP 返回第一条，但业务说结果错
- 原因
- 主键在查找表中重复。
- 怎么改
- 先用 COUNTIF 输出全部重复键；重复未解决前不要接受字段比较结果。
差异数为 0，但肉眼能看到变化
- 原因
- 公式引用错列、计算模式为手动，或新行没有进入范围。
- 怎么改
- 检查表格名称和结构化引用，强制重算，并用一个已知差异做阳性对照。
让 AI 先设计两张表的核对规则，再处理全部数据
适合在主键和字段口径已经确认后使用。
让 AI 先设计两张表的核对规则，再处理全部数据
我想核对名单 A 和名单 B，不要先把两张表合并。先验证匹配键是否可靠，再分别找出四类差异。 A 表字段/样例【】；B 表字段/样例【】；唯一键定义【】；要比较的字段及允许格式差异【】；Excel 版本/数据量【】。 先检查键是否空缺、重复、前导零或格式不一致。用手工可核算的小样例演示：只在 A、只在 B、两边都有但字段相同、两边都有但字段不同。返回公式或 Power Query 步骤，并保留原表行号和原值以便复核。疑似同人不要自动合并，重复行不删除，匹配阈值不由模型猜。
使用范围：适合在主键和字段口径已经确认后使用。不直接合并疑似同人，不删除重复行，不覆盖原表。