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”宏。
