ARTICLE · 1377015
用 Python 批量处理 Excel:从合并日报到自动生成汇总表

每天收到好几份 Excel,你是否做过这样的工作?
把多份日报复制到一张总表; 检查日期和数字是否规范; 用“实际值减去计划值”计算偏差; 按日期、部门或单位分类汇总; 再制作一张方便查看的透视表。
如果只做一次,手工处理也许不麻烦;如果每天都要重复,既耗时间,也容易复制错行、漏掉文件。
这篇文章将带你用 Python 完成一套简单的 Excel 批处理流程:
读取多个 Excel → 合并数据 → 检查数据 → 计算偏差 → 生成汇总表
你不需要先掌握复杂的编程知识,跟着下面的示例操作即可。
一、先看看最终能得到什么
假设你有两份日报:
text复制
日报1.xlsx日报2.xlsx每份日报中都有下面几列:
运行程序后,会得到一个新的文件:
text复制
结果总表.xlsx文件中包含四张工作表:
程序不会修改原始日报。 如果结果文件已经存在,程序会停止,提醒你先重命名或移走旧文件,避免意外覆盖。
本文以 Windows 和 PowerShell 为例。Python 代码本身也可用于 macOS 和 Linux,但虚拟环境中的 Python 路径需要调整。
二、开始前:认识两个工具
1. Python 是什么?
可以把 Python 理解为一种“告诉电脑如何一步步做事”的工具。
例如:
找到 data文件夹;读取里面的 Excel; 把表格拼在一起; 算出偏差; 保存结果。
这些操作写成程序后,以后换一批数据,就可以重复使用。
2. pandas 和 openpyxl 是什么?
Python 本身不直接提供我们需要的所有 Excel 功能,因此要安装两个工具包:
- pandas
:负责读取和处理表格,例如合并、筛选、计算和汇总。 - openpyxl
:帮助 pandas 读取和写入 .xlsx文件。
原文还安装了 xlsxwriter。它适合制作更复杂的 Excel 输出,但本文示例没有使用它,因此不需要安装。
三、安装 Python 和必要的工具包
第一步:安装 Python
如果电脑上还没有 Python,可以从 Python 官网下载并安装。
建议使用 Python 3.11 或 3.12。安装过程中,如果看到“Add Python to PATH”之类的选项,可以勾选,方便之后在终端中运行 Python。
安装完成后,重新打开一个 PowerShell 窗口,输入:
bash复制
python --version如果看到类似下面的内容,说明 Python 已经可以运行:
text复制
Python 3.12.12你的具体版本号可能不同。
如果提示找不到 python,也可以试试:
bash复制
py -3 --version如果这个命令可以运行,后面创建虚拟环境时,可将 python 换成 py -3。
第二步:创建练习文件夹
在桌面上新建一个文件夹,例如:
text复制
Excel练习打开这个文件夹,在文件资源管理器的地址栏输入 powershell,按回车。
弹出的 PowerShell 窗口默认就在这个文件夹中。接下来的命令都在这个窗口里执行。
第三步:创建独立的 Python 环境
输入:
bash复制
python -m venv .venv如果前面只有 py -3 命令可用,则改为:
bash复制
py -3 -m venv .venv完成后,文件夹中会出现一个 .venv 目录。
它是专门给这次练习使用的 Python 环境,可以避免把依赖直接安装到全局环境里。
不需要手动“激活”环境;下面直接使用其中的
python.exe。
第四步:安装 pandas 和 openpyxl
输入:
bash复制
.\.venv\Scripts\python.exe -m pip install pandas openpyxl等待安装完成。
如果访问默认下载源不稳定,可以尝试原文使用的阿里云镜像:
bash复制
.\.venv\Scripts\python.exe -m pip install pandas openpyxl -i https://mirrors.aliyun.com/pypi/simple/如果安装失败,先检查网络和报错信息,不建议为了安装而关闭 HTTPS 证书校验。
四、先做一个最简单的例子:打开、修改、保存
在处理一堆文件之前,我们先完成一件小事:
打开一个 Excel,修改一格内容,再保存成新文件。
第一步:准备 test.xlsx
在 Excel练习 文件夹中创建一个 Excel 文件,命名为:
text复制
test.xlsx第一张工作表填写:
请注意:这里的 A列 是第一行的表头文字,不是仅仅指 Excel 界面中的 A 列位置。
第二步:创建 Python 文件
用记事本或其他文本编辑器,新建一个文件,粘贴以下代码:
python复制
from pathlib import Pathimport pandas as pd目录 = Path(__file__).resolve().parent输入文件 = 目录 / "test.xlsx"输出文件 = 目录 / "test_new.xlsx"if 输出文件.exists(): raise SystemExit("test_new.xlsx 已存在,请先重命名或移走。")表格 = pd.read_excel(输入文件)if "A列" not in 表格.columns: raise SystemExit("找不到名为“A列”的表头,请检查 test.xlsx。")if 表格.empty: raise SystemExit("表格中没有数据行,请先填写一行数据。")表格.loc[0, "A列"] = "已由 Python 修改"表格.to_excel(输出文件, index=False)print(f"处理完成,新文件:{输出文件}")保存为:
text复制
入门示例.py如果使用记事本保存:
选择“另存为”; 文件名输入 入门示例.py;保存类型选择“所有文件”; 编码选择 UTF-8; 保存到 Excel练习文件夹。
注意不要误存成 入门示例.py.txt。
第三步:运行程序
回到刚才的 PowerShell 窗口,输入:
bash复制
.\.venv\Scripts\python.exe 入门示例.py运行成功后,文件夹中会出现:
text复制
test_new.xlsx打开它,第一条数据的 A列 会变成:
text复制
已由 Python 修改原来的 test.xlsx 不会被修改。
这段代码中,最关键的是哪三步?
可以简化理解为:
python复制
表格 = pd.read_excel(输入文件)表格.loc[0, "A列"] = "已由 Python 修改"表格.to_excel(输出文件, index=False)分别是:
- 读入
Excel; - 修改
表格中的值; - 保存
为新的 Excel。
其中,loc[0, "A列"] 表示:数据中的第一行、表头为 A列 的那一列。由于 Excel 第一行是表头,它通常对应 Excel 中的第二行数据。
这里说的是“三个核心操作”,不是说所有实际需求都能只用三行代码安全完成。完整脚本还需要处理文件不存在、表头不对等情况。
五、正式案例:批量合并多份日报
下面开始实现原文的主要目标。
1. 创建 data 文件夹
在 Excel练习 文件夹中,新建一个文件夹:
text复制
data以后需要批量处理的日报,都放进这个文件夹。
2. 创建两份示例日报
在 data 文件夹中创建:
text复制
日报1.xlsx日报2.xlsx两份文件都只需要使用第一张工作表。
日报1.xlsx 的内容:
日报2.xlsx 的内容:
最后一行是故意设置的异常数据:
4月1日没有年份,无法可靠判断是哪一年; —不是可直接计算的数字。
为了确保 Excel 不自动给 4月1日 补年份,可以先把该单元格设置为“文本”,或者输入时在前面加一个英文单引号:
text复制
'4月1日Excel 显示的内容仍然是 4月1日。
如果 Excel 在输入时已经把
4月1日自动转换成某一年的真实日期,程序只能读到转换后的日期,无法知道你原来没有填写年份。因此,实际处理前仍要检查源文件中的年份是否正确。
3. 检查表头
每份日报的第一行,都需要包含以下四个表头:
text复制
时间电厂名称计划值(MW)实际值(MW)注意:
括号是英文括号: (和);MW的大小写要一致; 表头放在第一行; 不要使用合并单元格充当表头; 同一份表中不要出现重复表头。
程序会去掉表头前后的空格,但不会自动把 计划值(MW) 改成 计划值(MW)。
本例只读取这四列,其他列不会写入结果文件。
六、复制完整代码
在 Excel练习 文件夹中,新建一个文件:
text复制
批量处理.py将下面的代码完整复制进去,并以 UTF-8 编码保存。
python复制
from datetime import date, datetimefrom pathlib import Pathimport reimport pandas as pd根目录 = Path(__file__).resolve().parent数据目录 = 根目录 / "data"输出目录 = 根目录 / "output"结果文件 = 输出目录 / "结果总表.xlsx"必需列 = ["时间", "电厂名称", "计划值(MW)", "实际值(MW)"]def 解析日期(值): """只接收真正的日期或明确包含年月日的日期文本。""" if pd.isna(值): return pd.NaT if isinstance(值, (datetime, date)): return pd.Timestamp(值).normalize() 文本 = str(值).strip() if not re.fullmatch(r"\d{4}[-/]\d{1,2}[-/]\d{1,2}", 文本): return pd.NaT 格式 = "%Y-%m-%d" if "-" in 文本 else "%Y/%m/%d" return pd.to_datetime(文本, format=格式, errors="coerce")def 检查问题(行): 问题 = [] if pd.isna(行["日期"]): 问题.append("日期为空或格式不符合要求") if not 行["电厂名称"]: 问题.append("电厂名称为空") if pd.isna(行["计划数值(MW)"]): 问题.append("计划值为空或不是有效数字") if pd.isna(行["实际数值(MW)"]): 问题.append("实际值为空或不是有效数字") return ";".join(问题)if not 数据目录.is_dir(): raise SystemExit(f"找不到数据目录:{数据目录},请先创建 data 文件夹。")文件列表 = sorted( 文件 for 文件 in 数据目录.glob("*.xlsx") if 文件.is_file() and not 文件.name.startswith("~$"))if not 文件列表: raise SystemExit(f"{数据目录} 中没有可处理的 .xlsx 文件。")if 结果文件.exists(): raise SystemExit(f"结果文件已存在:{结果文件}。请先重命名或移走,避免覆盖。")数据集合 = []for 文件 in 文件列表: 数据 = pd.read_excel( 文件, sheet_name=0, dtype=object, keep_default_na=False, ) 数据.columns = [str(列名).strip() for 列名 in 数据.columns] if 数据.columns.duplicated().any(): raise SystemExit(f"{文件.name} 的表头存在重复列名,请先检查。") 缺少的列 = [列名 for 列名 in 必需列 if 列名 not in 数据.columns] if 缺少的列: raise SystemExit(f"{文件.name} 缺少列:{', '.join(缺少的列)}") 数据 = 数据[必需列].copy() 数据["来源文件"] = 文件.name 数据["来源行号"] = 数据.index + 2 数据集合.append(数据)合并表 = pd.concat(数据集合, ignore_index=True)if 合并表.empty: raise SystemExit("Excel 文件中没有数据行,请检查首行表头和数据。")合并表["电厂名称"] = 合并表["电厂名称"].fillna("").astype(str).str.strip()日期结果 = 合并表["时间"].map(解析日期)合并表["日期"] = pd.to_datetime( 日期结果, errors="coerce",).dt.strftime("%Y-%m-%d")for 原列, 数值列 in [ ("计划值(MW)", "计划数值(MW)"), ("实际值(MW)", "实际数值(MW)"),]: 合并表[数值列] = pd.to_numeric(合并表[原列], errors="coerce") 合并表[数值列] = 合并表[数值列].replace( [float("inf"), float("-inf")], float("nan"), )合并表["偏差(MW)"] = ( 合并表["实际数值(MW)"] - 合并表["计划数值(MW)"])合并表["问题说明"] = 合并表.apply(检查问题, axis=1)有效数据 = 合并表[合并表["问题说明"] == ""].copy()异常数据 = 合并表[合并表["问题说明"] != ""].copy()if 有效数据.empty: 按日汇总 = pd.DataFrame( columns=[ "日期", "电厂名称", "计划合计(MW)", "实际合计(MW)", "偏差合计(MW)", ] ) 透视表 = pd.DataFrame(columns=["电厂名称"])else: 按日汇总 = ( 有效数据.groupby(["日期", "电厂名称"], as_index=False) [["计划数值(MW)", "实际数值(MW)", "偏差(MW)"]] .sum() .rename( columns={ "计划数值(MW)": "计划合计(MW)", "实际数值(MW)": "实际合计(MW)", "偏差(MW)": "偏差合计(MW)", } ) ) 透视表 = 按日汇总.pivot( index="电厂名称", columns="日期", values="实际合计(MW)", ).reset_index()输出目录.mkdir(exist_ok=True)with pd.ExcelWriter(结果文件, engine="openpyxl") as 写入器: 合并表.to_excel(写入器, sheet_name="原始合并", index=False) 按日汇总.to_excel(写入器, sheet_name="按日汇总", index=False) 透视表.to_excel(写入器, sheet_name="透视表", index=False) 异常数据.to_excel(写入器, sheet_name="异常数据", index=False)print(f"处理完成:读取 {len(文件列表)} 个文件,共 {len(合并表)} 行。")print(f"参与汇总 {len(有效数据)} 行;异常 {len(异常数据)} 行。")print(f"结果文件:{结果文件}")七、运行批量处理程序
运行前,文件夹结构应大致如下:
text复制
Excel练习/├─ .venv/├─ data/│ ├─ 日报1.xlsx│ └─ 日报2.xlsx├─ 入门示例.py├─ 批量处理.py├─ test.xlsx└─ test_new.xlsx在 Excel练习 对应的 PowerShell 窗口中运行:
bash复制
.\.venv\Scripts\python.exe 批量处理.py使用上面的示例数据时,你应该能看到类似提示:
text复制
处理完成:读取 2 个文件,共 5 行。参与汇总 4 行;异常 1 行。结果文件:……\Excel练习\output\结果总表.xlsx随后,打开新生成的:
text复制
output/结果总表.xlsx如果以后需要重新运行,先关闭结果文件,并将旧的
结果总表.xlsx重命名或移走。程序不会直接覆盖同名结果文件。
八、怎么看生成的四张表?
1. 原始合并
这张表将两份日报中的必要字段放在一起,并新增:
- 来源文件
:这一行来自哪个 Excel; - 来源行号
:这一行在原 Excel 中的行号; - 日期
:程序统一后的日期; - 计划数值、实际数值
:转换后的数字; - 偏差
:实际值减计划值; - 问题说明
:这一行是否有需要检查的问题。
比如:
这样,发现问题后可以直接回到对应文件、对应行检查。
2. 按日汇总
程序按“日期 + 电厂名称”分组,并对有效数据求和。
上面的示例应得到:
例如,甲电厂在 2025 年 4 月 1 日有两条有效记录:
text复制
计划合计:100 + 40 = 140实际合计:110 + 42 = 152偏差合计:152 - 140 = 12重要业务前提: 这个示例假设两条记录是可以相加的,例如代表同一时刻不同机组的数据。如果两份日报是同一数据的重复上报,直接相加就会重复统计;程序不会自动去重。
还要注意单位:MW 是功率,MWh 是电量。把一天中不同时刻的 MW 数字直接相加,并不能直接得到当天的 MWh。计算电量需要结合各时段的功率和持续时间。正式使用前,应由业务人员确认汇总规则。
3. 透视表
透视表把数据换一种方式展示:
读法是:
每一行代表一个电厂; 每一列代表一天; 交叉位置是该电厂当天的实际值合计。
没有对应有效数据的位置留空,而不是自动填成 0。因为“没有数据”和“实际值确实为 0”是两回事。
4. 异常数据
只要一行记录存在以下任一问题,就会进入“异常数据”工作表:
日期为空或不符合要求; 电厂名称为空; 计划值不是有效数字; 实际值不是有效数字。
异常行整行不参与汇总。例如,一行的计划值正确、但实际值缺失时,这一行的计划值也不会被计入按日汇总。
这样做是为了避免拿不完整的数据直接计算偏差,但也意味着:如果有异常行,汇总值可能不完整,必须先处理异常再使用结果。
九、这段代码实际上做了什么?
如果你不想逐行研究,可以先记住下面五个动作。
第一步:找到文件
python复制
数据目录.glob("*.xlsx")意思是:
找到
data文件夹下的.xlsx文件。
它不会递归查找子文件夹,也不会读取 .xls 或 .xlsm 文件。
程序还会跳过 Excel 常见的 ~$ 临时文件。
第二步:一份一份读进来
python复制
数据 = pd.read_excel(文件, sheet_name=0)sheet_name=0 表示读取第一张工作表。
本例假设第一行是表头,后续每一行是一条数据。
第三步:把多张表接在一起
python复制
合并表 = pd.concat(数据集合, ignore_index=True)可以想象成把第二张表的内容,接在第一张表的下方。
ignore_index=True 表示重新排列数据行的编号。
第四步:检查并计算
程序将日期和数字转换成适合计算的形式,再计算:
text复制
偏差 = 实际值 - 计划值无法可靠识别的日期、无效数字等,不会被猜测成某个正确值,而是进入异常数据表。
本例接受:
Excel 中真正的日期单元格; 2025-04-01这样的文本; 2025/4/1这样的文本。
本例不自动处理:
4月1日这类没有年份的文本; 2025年4月1日这类中文日期文本; 普通数字单元格中的 Excel 日期序列号。
如果数据中包含这些情况,请先在源文件中明确转换。
另外,结果表中的 日期 列被统一写成 2025-04-01 这样的文本,方便阅读和透视;如果后续需要在 Excel 中按真正的日期类型计算,还需要转换或调整代码。
第五步:写入不同工作表
python复制
with pd.ExcelWriter(结果文件, engine="openpyxl") as 写入器: 合并表.to_excel(写入器, sheet_name="原始合并", index=False) 按日汇总.to_excel(写入器, sheet_name="按日汇总", index=False)这里相当于打开一个新的 Excel 文件,再往里面写入不同的工作表。
index=False 的作用是:不把 pandas 自动生成的行编号额外写入 Excel。
十、常见问题
问题一:提示找不到 python
先确认是否安装了 Python,并重新打开 PowerShell。
也可以尝试:
bash复制
py -3 --version如果 py -3 可以用,创建环境时运行:
bash复制
py -3 -m venv .venv之后仍然使用:
bash复制
.\.venv\Scripts\python.exe来运行环境中的 Python。
问题二:提示 No module named pandas
通常是工具包装到了另一个 Python 环境里。
在 Excel练习 文件夹中重新运行:
bash复制
.\.venv\Scripts\python.exe -m pip install pandas openpyxl然后也使用同一个环境运行脚本:
bash复制
.\.venv\Scripts\python.exe 批量处理.py问题三:提示缺少某一列
例如:
text复制
日报1.xlsx 缺少列:计划值(MW)请检查:
表头是否在第一行; 是否读取了正确的第一张工作表; 是否使用了中文括号; 表头中间是否多了空格; 是否将 MW写成了其他内容。
特别注意:
text复制
计划值(MW)与:
text复制
计划值(MW)看起来相似,但对程序来说是不同的文字。
问题四:提示没有 .xlsx 文件
请确认:
文件确实放在 data文件夹中;文件扩展名确实是 .xlsx;不是 .xls、.xlsm或其他格式;没有把文件放进 data下的子文件夹。
不要只靠修改文件名后缀来转换格式。如果原文件是 .xls,应在 Excel 或其他兼容软件中用“另存为”生成真正的 .xlsx 文件。
问题五:提示结果文件已存在
这不是程序故障,而是为了避免覆盖旧结果。
请先:
关闭正在打开的 结果总表.xlsx;把它重命名或移走; 再次运行程序。
问题六:数字看起来正确,为什么还进入了异常表?
常见原因包括:
单元格是空的; 内容是 —、NA等文字;数字中包含无法直接解析的单位或符号; 例如文本形式的 1,000没有被本示例自动当成1000。
处理原则是:先确认原始值的含义,再修正数据,不要不加判断地把所有异常都替换为 0。
问题七:原 Excel 的颜色、公式和图片为什么没有保留?
这篇文章使用 pandas 的方式是:
读取表格数据 → 生成一份新的工作簿。
它适合处理数据,不保证保留原文件的格式、公式、图表、图片、宏或其他复杂内容。
特别是包含公式的单元格:读取时可能得到 Excel 上次保存的计算结果;如果缓存缺失或过期,读到的值也可能为空或不是最新值。程序不会替你重新计算 Excel 公式。
如果非常依赖原表样式或公式,需要使用其他专门的处理方式,并针对原文件充分测试。
十一、正式使用前,再检查这四件事
1. 先备份原文件
虽然这个示例不会主动覆盖输入文件,但实际办公中仍建议先备份,再对副本运行。
2. 先用少量数据试运行
第一次不要直接处理几十份重要报表。可以先复制两份文件,核对:
读取了几行; 异常行有几行; 汇总数字是否正确; 是否存在重复数据。
3. 确认业务规则
程序会按“日期 + 电厂名称”直接求和,但它不会判断这些数据是否应该相加,也不会自动识别重复上报。
如果你的业务需要按机组、时段、项目编号等维度去重或汇总,需要调整规则。
4. 不要直接把示例当作财务核算程序
这个案例使用普通数值类型演示计算。涉及金额、税额、舍入和对账的场景,还需要明确精度、舍入方式、业务口径和复核流程,再设计相应程序。
十二、总结
通过这个案例,我们完成了一条常见的 Excel 自动化流程:
text复制
准备多份格式一致的 Excel ↓批量读取并记录来源 ↓检查日期、名称和数字 ↓将异常数据单独列出 ↓计算偏差并按日期、电厂汇总 ↓输出结果工作簿它最重要的价值,不是“一次只写三行代码”,而是把原来需要反复手工执行的步骤,整理成一套可以检查、可以重复运行、也更容易发现错误的流程。
参考来源
原文:用 Python 批量处理 Excel:三行代码搞定重复工作 pandas 文档:读取 Excel pandas 文档:ExcelWriter Python 文档:在 Windows 上使用 Python 美国能源信息署:功率与电量的区别
如有疑问可添加小助手咨询


每周不定期更新表格实操和企业所得税、个人所得税、印花税、增值税、财务报表等等干货
👈加微信——免费领取Excel练习文件
















































































