Python 自动比对 Excel 多表差异 + 标红高亮

对账对到眼酸？Python 自动比对 Excel 差异 + 标红高亮，10 秒搞定半天活!

一、戳中无数职场人的痛点：核对数据到底有多费时间？

我见过太多岗位的日常被「数据核对」占满：

• 财务月底核对银行流水与记账凭证，上千条记录逐行比对，差一毛钱就要翻遍所有单据；
• 运营每周同步渠道数据，新旧版本表格混在一起，找不出哪里改了数；
• HR 核对月度人员异动表，入职、离职、调岗信息混在一起，手动标记容易遗漏。

少则一小时，多则大半天，全是机械又费眼的重复劳动，还经常因为看漏行返工，越忙越错。

今天这篇教你写一个通用的 Excel 自动核对脚本，两个表格一键比对，差异单元格自动标红高亮，新增行、删除行精准识别，全程十几秒，准确率 100%。代码完整可复制，改个文件路径就能直接用。

二、本次实战场景说明

我们以职场最常见的场景为例：

同一个业务有两个版本的 Excel 表格（如 V1 版和 V2 版），列名完全一致，需要找出所有单元格的内容差异，同时标记出新增的行、删除的行，最终输出一份带颜色高亮的核对结果表，差异一目了然。

典型适用场景：薪资表版本核对、库存数据对账、订单数据更新核对、人员信息表比对等。

三、环境准备

依然只需要两个 Python 库，没安装的同学打开命令提示符执行即可：

pip install pandas openpyxl

• pandas：负责快速读取、比对表格数据
• openpyxl：负责操作 Excel 单元格样式，实现差异标红高亮

四、完整可运行代码（基础版：行顺序一致场景）

如果你的两个表格行顺序完全对应（比如同一份表改了部分数值），直接用这个版本，只需要修改开头的 3 个路径参数。

import pandas as pd from openpyxl import load_workbook from openpyxl.styles import PatternFill # ===================== 仅需修改此处 ===================== # 旧版文件路径 old_file_path = "C:/Users/Desktop/薪资表V1.xlsx" # 新版文件路径 new_file_path = "C:/Users/Desktop/薪资表V2.xlsx" # 核对结果输出路径 output_file = "核对结果_差异高亮版.xlsx" # 高亮颜色（浅红色填充，醒目不刺眼） highlight_color = "FFC7CE" # ======================================================== # 1. 读取两个Excel文件，默认读取第一个工作表 df_old = pd.read_excel(old_file_path, engine="openpyxl") df_new = pd.read_excel(new_file_path, engine="openpyxl") # 2. 校验列名是否一致，不一致则提前预警 if not df_old.columns.equals(df_new.columns): print("警告：两个表格列名不一致，请检查后再比对！") print(f"旧表列名：{list(df_old.columns)}") print(f"新表列名：{list(df_new.columns)}") else: print("列名校验通过，开始比对差异...") # 3. 生成差异掩码：True表示该单元格内容不同 diff_mask = df_old.ne(df_new) # 4. 统计差异信息，运行完直接知晓改动规模 diff_cells = diff_mask.sum().sum() diff_rows = diff_mask.any(axis=1).sum() print(f"比对完成：共发现 {diff_cells} 个差异单元格，涉及 {diff_rows} 行数据") # 5. 以新版数据为基准，输出到结果文件 df_new.to_excel(output_file, index=False, engine="openpyxl") # 6. 加载结果文件，给差异单元格标红 wb = load_workbook(output_file) ws = wb.active # 定义红色填充样式 red_fill = PatternFill(start_color=highlight_color, end_color=highlight_color, fill_type="solid") # 遍历所有数据单元格（跳过表头行，从第2行开始） for row in range(2, ws.max_row + 1): for col in range(1, ws.max_column + 1): # pandas索引从0开始，Excel行从1开始，表头占1行，做对应偏移 if diff_mask.iloc[row - 2, col - 1]: ws.cell(row=row, column=col).fill = red_fill # 7. 保存最终结果 wb.save(output_file) print(f"差异高亮完成，结果已保存至：{output_file}")

五、核心逻辑逐行拆解

1. 数据读取与前置校验先读取两个表格，优先校验列名是否一致。如果列名都对不上，直接给出提示，避免后续比对出错，这是很多新手容易忽略的容错步骤。

2. 全量差异比对核心是df_old.ne(df_new)这一行代码：ne是 not equal 的缩写，会逐单元格比对两个表格，内容不同就返回True，相同返回False，最终生成一个和表格尺寸一致的布尔矩阵，也就是「差异掩码」。

3. 差异数据统计提前统计差异单元格总数和涉及的行数，运行完不用点开文件，就能知道大概有多少改动，效率更高。

4. 单元格高亮标记以新版表格为基准输出，再用openpyxl打开生成的文件，遍历所有数据单元格，对照差异掩码把内容不同的单元格填充为浅红色，打开文件就能一眼定位差异。

六、进阶优化：解决职场更复杂的核对场景

基础版只适用于行顺序完全一致的情况，但实际工作里，两个表格的行顺序经常不一样，甚至有新增、删除的行。下面给 3 个高频优化方案，覆盖 90% 的核对需求。

优化 1：按主键列比对（行顺序不一致也能用）

绝大多数业务表格都有唯一主键，比如员工工号、订单编号、物料编码。只要指定主键列，就算行顺序乱了、行数不一样，也能精准对应到同一条数据比对，这是职场最实用的版本。

# ===================== 仅需修改此处 ===================== primary_key = "员工工号" # 指定唯一主键列名 # ======================================================== # 读取数据后，将主键列设为索引，自动对齐同一条数据 df_old = df_old.set_index(primary_key) df_new = df_new.set_index(primary_key) # 识别新增行和删除行 deleted_rows = df_old.index.difference(df_new.index) # 旧表有、新表没有 = 删除的行 added_rows = df_new.index.difference(df_old.index) # 新表有、旧表没有 = 新增的行 print(f"删除的行（主键）：{list(deleted_rows)}") print(f"新增的行（主键）：{list(added_rows)}") # 取两表共有的数据行，再进行内容比对 common_index = df_old.index.intersection(df_new.index) diff_mask = df_old.loc[common_index].ne(df_new.loc[common_index])

后续高亮逻辑和基础版一致，还可以扩展给新增行标绿、删除行标灰，区分更清晰。

优化 2：只比对指定列，忽略无关列

如果表格列很多，你只关心其中几列的数据（比如只核对薪资、不核对部门名称），可以在比对前筛选列，减少无效比对：

# 只比对「基本工资」「绩效工资」两列 check_columns = ["基本工资", "绩效工资"] diff_mask = df_old[check_columns].ne(df_new[check_columns])

优化 3：忽略空格、空值的无效差异

很多时候数据差异只是多了个空格、或者 NaN 和空字符串的区别，属于无效差异。比对前先做数据清洗即可过滤掉这类干扰：

# 统一去除首尾空格，空值统一填充为空字符串 def clean_data(df): return df.applymap(lambda x: str(x).strip() if pd.notna(x) else "").fillna("") df_old_clean = clean_data(df_old) df_new_clean = clean_data(df_new) diff_mask = df_old_clean.ne(df_new_clean)

七、写在最后

核对数据这件事，本质上是「低价值的重复劳动」。你花 3 小时手动对账，和别人用脚本 10 秒搞定，结果是一样的，但省下的时间，你可以用来做分析、做汇报，或者准时下班。

如果你每天都要和 Excel 打交道，想靠自动化告别无效加班，欢迎订阅专栏。每一篇都是拿来即用的实战脚本，复制就能跑，改改参数就能套进你的工作里。