夜雨聆风学习资料网

ARTICLE · 1377015

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

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

每天收到好几份 Excel,你是否做过这样的工作?

  • 把多份日报复制到一张总表;
  • 检查日期和数字是否规范;
  • 用“实际值减去计划值”计算偏差;
  • 按日期、部门或单位分类汇总;
  • 再制作一张方便查看的透视表。

如果只做一次,手工处理也许不麻烦;如果每天都要重复,既耗时间,也容易复制错行、漏掉文件。

这篇文章将带你用 Python 完成一套简单的 Excel 批处理流程:

读取多个 Excel → 合并数据 → 检查数据 → 计算偏差 → 生成汇总表

你不需要先掌握复杂的编程知识,跟着下面的示例操作即可。


一、先看看最终能得到什么

假设你有两份日报:

text复制

日报1.xlsx日报2.xlsx

每份日报中都有下面几列:

时间
电厂名称
计划值(MW)
实际值(MW)
2025-04-01
甲电厂
100
110
2025-04-01
乙电厂
80
75

运行程序后,会得到一个新的文件:

text复制

结果总表.xlsx

文件中包含四张工作表:

工作表
内容
原始合并
每份日报中的四个必要字段,以及来源文件、来源行号和计算结果
按日汇总
按日期和电厂汇总计划值、实际值、偏差
透视表
行是电厂、列是日期、交叉位置显示实际值合计
异常数据
日期、名称或数字不符合要求的记录

程序不会修改原始日报。 如果结果文件已经存在,程序会停止,提醒你先重命名或移走旧文件,避免意外覆盖。

本文以 Windows 和 PowerShell 为例。Python 代码本身也可用于 macOS 和 Linux,但虚拟环境中的 Python 路径需要调整。


二、开始前:认识两个工具

1. Python 是什么?

可以把 Python 理解为一种“告诉电脑如何一步步做事”的工具。

例如:

  1. 找到 data 文件夹;
  2. 读取里面的 Excel;
  3. 把表格拼在一起;
  4. 算出偏差;
  5. 保存结果。

这些操作写成程序后,以后换一批数据,就可以重复使用。

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列
B列
修改前的内容
100

请注意:这里的 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

如果使用记事本保存:

  1. 选择“另存为”;
  2. 文件名输入 入门示例.py;
  3. 保存类型选择“所有文件”;
  4. 编码选择 UTF-8;
  5. 保存到 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)

分别是:

  1. 读入
     Excel;
  2. 修改
     表格中的值;
  3. 保存
     为新的 Excel。

其中,loc[0, "A列"] 表示:数据中的第一行、表头为 A列 的那一列。由于 Excel 第一行是表头,它通常对应 Excel 中的第二行数据。

这里说的是“三个核心操作”,不是说所有实际需求都能只用三行代码安全完成。完整脚本还需要处理文件不存在、表头不对等情况。


五、正式案例:批量合并多份日报

下面开始实现原文的主要目标。

1. 创建 data 文件夹

在 Excel练习 文件夹中,新建一个文件夹:

text复制

data

以后需要批量处理的日报,都放进这个文件夹。


2. 创建两份示例日报

在 data 文件夹中创建:

text复制

日报1.xlsx日报2.xlsx

两份文件都只需要使用第一张工作表。

日报1.xlsx 的内容:

时间
电厂名称
计划值(MW)
实际值(MW)
2025-04-01
甲电厂
100
110
2025/04/01
乙电厂
80
75

日报2.xlsx 的内容:

时间
电厂名称
计划值(MW)
实际值(MW)
2025-04-01
甲电厂
40
42
2025-04-02
乙电厂
60
64
4月1日
丙电厂
—
50

最后一行是故意设置的异常数据:

  • 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.xlsx
4
丙电厂
日期为空或格式不符合要求;计划值为空或不是有效数字

这样,发现问题后可以直接回到对应文件、对应行检查。


2. 按日汇总

程序按“日期 + 电厂名称”分组,并对有效数据求和。

上面的示例应得到:

日期
电厂名称
计划合计(MW)
实际合计(MW)
偏差合计(MW)
2025-04-01
甲电厂
140
152
12
2025-04-01
乙电厂
80
75
-5
2025-04-02
乙电厂
60
64
4

例如,甲电厂在 2025 年 4 月 1 日有两条有效记录:

text复制

计划合计:100 + 40 = 140实际合计:110 + 42 = 152偏差合计:152 - 140 = 12

重要业务前提: 这个示例假设两条记录是可以相加的,例如代表同一时刻不同机组的数据。如果两份日报是同一数据的重复上报,直接相加就会重复统计;程序不会自动去重。

还要注意单位:MW 是功率,MWh 是电量。把一天中不同时刻的 MW 数字直接相加,并不能直接得到当天的 MWh。计算电量需要结合各时段的功率和持续时间。正式使用前,应由业务人员确认汇总规则。


3. 透视表

透视表把数据换一种方式展示:

电厂名称
2025-04-01
2025-04-02
甲电厂
152
乙电厂
75
64

读法是:

  • 每一行代表一个电厂;
  • 每一列代表一天;
  • 交叉位置是该电厂当天的实际值合计。

没有对应有效数据的位置留空,而不是自动填成 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)

请检查:

  1. 表头是否在第一行;
  2. 是否读取了正确的第一张工作表;
  3. 是否使用了中文括号;
  4. 表头中间是否多了空格;
  5. 是否将 MW 写成了其他内容。

特别注意:

text复制

计划值(MW)

与:

text复制

计划值(MW)

看起来相似,但对程序来说是不同的文字。


问题四:提示没有 .xlsx 文件

请确认:

  • 文件确实放在 data 文件夹中;
  • 文件扩展名确实是 .xlsx;
  • 不是 .xls、.xlsm 或其他格式;
  • 没有把文件放进 data 下的子文件夹。

不要只靠修改文件名后缀来转换格式。如果原文件是 .xls,应在 Excel 或其他兼容软件中用“另存为”生成真正的 .xlsx 文件。


问题五:提示结果文件已存在

这不是程序故障,而是为了避免覆盖旧结果。

请先:

  1. 关闭正在打开的 结果总表.xlsx;
  2. 把它重命名或移走;
  3. 再次运行程序。

问题六:数字看起来正确,为什么还进入了异常表?

常见原因包括:

  • 单元格是空的;
  • 内容是 —、NA 等文字;
  • 数字中包含无法直接解析的单位或符号;
  • 例如文本形式的 1,000 没有被本示例自动当成 1000。

处理原则是:先确认原始值的含义,再修正数据,不要不加判断地把所有异常都替换为 0。


问题七:原 Excel 的颜色、公式和图片为什么没有保留?

这篇文章使用 pandas 的方式是:

读取表格数据 → 生成一份新的工作簿。

它适合处理数据,不保证保留原文件的格式、公式、图表、图片、宏或其他复杂内容。

特别是包含公式的单元格:读取时可能得到 Excel 上次保存的计算结果;如果缓存缺失或过期,读到的值也可能为空或不是最新值。程序不会替你重新计算 Excel 公式。

如果非常依赖原表样式或公式,需要使用其他专门的处理方式,并针对原文件充分测试。


十一、正式使用前,再检查这四件事

1. 先备份原文件

虽然这个示例不会主动覆盖输入文件,但实际办公中仍建议先备份,再对副本运行。

2. 先用少量数据试运行

第一次不要直接处理几十份重要报表。可以先复制两份文件,核对:

  • 读取了几行;
  • 异常行有几行;
  • 汇总数字是否正确;
  • 是否存在重复数据。

3. 确认业务规则

程序会按“日期 + 电厂名称”直接求和,但它不会判断这些数据是否应该相加,也不会自动识别重复上报。

如果你的业务需要按机组、时段、项目编号等维度去重或汇总,需要调整规则。

4. 不要直接把示例当作财务核算程序

这个案例使用普通数值类型演示计算。涉及金额、税额、舍入和对账的场景,还需要明确精度、舍入方式、业务口径和复核流程,再设计相应程序。


十二、总结

通过这个案例,我们完成了一条常见的 Excel 自动化流程:

text复制

准备多份格式一致的 Excel          ↓批量读取并记录来源          ↓检查日期、名称和数字          ↓将异常数据单独列出          ↓计算偏差并按日期、电厂汇总          ↓输出结果工作簿

它最重要的价值,不是“一次只写三行代码”,而是把原来需要反复手工执行的步骤,整理成一套可以检查、可以重复运行、也更容易发现错误的流程。

参考来源

如有疑问可添加小助手咨询

欢迎关注“税表先生”

每周不定期更新表格实操和企业所得税、个人所得税、印花税、增值税、财务报表等等干货

👈加微信——免费领取Excel练习文件

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料