Excel两表数据核对技巧

本文系统介绍了在Excel中高效比对两个表格数据一致性的五种实用方法——从零代码的条件格式高亮和EXACT函数辅助列，到Power Query全外联接处理乱序与缺失记录，再到SUMPRODUCT快速统计与定位首差异，最后进阶至VBA脚本一键标红整行差异，覆盖从新手到资深用户的全场景需求；无论你正被行列错位、空值干扰、格式不统一或手动核对耗时易错所困扰，这里总有一种精准、稳定、可复用的解决方案助你瞬间锁定问题根源。

如果您需要在Excel中比对两个表格的数据一致性，并快速定位差异项，则可能是由于手动逐行检查效率低下、易出错，或原始数据结构存在列顺序不一致、空值干扰、格式不统一等问题。以下是解决此问题的步骤：

一、使用条件格式高亮差异行

该方法通过为两表中相同位置但内容不同的单元格施加视觉标记，实现即时、直观的差异识别，适用于结构完全一致（行列顺序、标题、数据范围均相同）的两张工作表。

1、选中第一个表格中需比对的数据区域（例如Sheet1的A2:F1000）。

2、在“开始”选项卡中点击“条件格式”→“新建规则”→“使用公式确定要设置格式的单元格”。

3、在公式框中输入：=A2Sheet2!A2（假设比对Sheet1与Sheet2对应位置，且首行为标题，从第2行开始）。

4、点击“格式”，设置填充色为浅红色，确定后应用规则。

5、重复步骤1–4，将Sheet2中对应区域（如Sheet2!A2:F1000）也应用相同公式，仅将公式改为：=A2Sheet1!A2。

二、利用EXACT函数配合辅助列标识差异

EXACT函数严格区分大小写与文本格式，可精准识别字符级差异（如空格、不可见符、数字与文本型数字），避免常规等号判断因自动类型转换导致的误判。

1、在Sheet1空白列（如G列）第一数据行（G2）输入公式：=IF(EXACT(A2,Sheet2!A2)*EXACT(B2,Sheet2!B2)*EXACT(C2,Sheet2!C2), "一致", "差异")（根据实际比对列数扩展EXACT项并用*连接）。

2、双击填充柄向下复制至末行。

3、筛选G列中值为“差异”的行，即可定位全部不匹配记录。

4、如需定位具体哪一列不同，可在H列起分别输入：=IF(EXACT(A2,Sheet2!A2),"✓","✗")，依次类推至各列。

三、通过Power Query合并查询识别唯一差异记录

当两张表格结构相同但行序混乱、存在重复或缺失行时，Power Query可基于指定键列执行全外联接，生成含“仅在左表”“仅在右表”“两者均有”三类状态的结果集，彻底规避位置依赖。

1、分别选中Sheet1和Sheet2的数据区域，按Ctrl+T转为表格，并确保每张表均有唯一标识列（如“订单号”“ID”）。

2、点击“数据”→“从表格/区域”，将两张表导入Power Query编辑器，命名查询为“表1”和“表2”。

3、在“表1”查询中，点击“主页”→“合并查询”→“合并查询为新查询”，选择“表1”与“表2”，匹配列为共同键字段，联接种类选“全外联接”。

4、展开合并列，勾选“表2”的所有字段，取消勾选“使用原始列名作为前缀”。

5、添加自定义列，公式为：=if [表2.键列] = null then "仅在表1" else if [表1.键列] = null then "仅在表2" else "两者均有"（替换“键列”为实际列名）。

四、借助数组公式SUMPRODUCT统计差异总数并定位首处差异

SUMPRODUCT函数可跨区域批量比对并返回数值结果，适合快速验证整体一致性；配合MATCH与INDEX可定位首个差异位置，节省排查时间。

1、在空白单元格输入统计公式：=SUMPRODUCT(--(Sheet1!A2:A1000Sheet2!A2:A1000))，回车后即得A列差异总行数。

2、若需定位A列首个差异行号，在另一单元格输入：=MATCH(TRUE,Sheet1!A2:A1000Sheet2!A2:A1000,0)+1（按Ctrl+Shift+Enter作为数组公式确认）。

3、结合INDEX函数提取该行内容：例如获取Sheet1中首个差异行的B列值，公式为：=INDEX(Sheet1!B:B,MATCH(TRUE,Sheet1!A2:A1000Sheet2!A2:A1000,0)+1)。

五、使用VBA脚本一键标红所有差异单元格（支持多列动态比对）

该方案绕过手动设置限制，自动遍历指定范围内的所有单元格，对不一致项整行标红，适用于高频、大批量、多工作表比对场景，且可保存为宏反复调用。

1、按Alt+F11打开VBA编辑器，插入新模块。

2、粘贴以下代码（已预设比对Sheet1与Sheet2的A1:Z1000区域）：

Sub HighlightDifferences()

Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range, cell As Range

Set ws1 = ThisWorkbook.Sheets("Sheet1"): Set ws2 = ThisWorkbook.Sheets("Sheet2")

Set rng = ws1.Range("A1:Z1000")

For Each cell In rng

If Not IsEmpty(cell) Or Not IsEmpty(ws2.Cells(cell.Row, cell.Column)) Then

If cell.Value ws2.Cells(cell.Row, cell.Column).Value Then

cell.EntireRow.Interior.Color = RGB(255, 200, 200)

End If

End If

Next cell

End Sub

3、关闭编辑器，返回Excel，按Alt+F8运行“HighlightDifferences”宏。

以上就是《Excel两表数据核对技巧》的详细内容，更多关于的资料请关注golang学习网公众号！