python 处理 excel 合并拆分

杨明伟2026-07-02约 8 分钟主编

python 处理 excel 合并拆分 是指用 Python 的 pandas、openpyxl 或 xlwings 库自动化完成 Excel 工作表的合并（多个文件或 sheet 拼成一个）与拆分（一个文件或 sheet 按条件分出多个）。核心价值在于：当你有几十个格式相同的销售报表、月度考勤表或部门数据需要整合时，用 Python 写几行代码就能在几秒内完成，且结果可重复、不出错。

实际工作中最常见的两个场景：

合并：把 12 个月的独立 Excel 文件合并成一张年度总表。
拆分：把一张总明细表按“部门”或“地区”字段拆成多个独立工作表或文件。

如果你只需要偶尔做一次、文件不超过 3–5 个，Excel 自带的 Power Query（获取数据 → 合并文件）或手动复制粘贴反而更快。但当文件数量超过 10 个、或需要每周/每月重复操作时，Python 脚本就比功能区操作省 10 倍时间。

前置准备

环境与库

你需要 Python 3.7 以上版本，并安装以下库（推荐用 pip 安装在一个独立虚拟环境中）：

``text pip install pandas openpyxl xlrd # xlrd 用于读取旧版 .xls 文件 ``

pandas：核心数据处理库，负责读取、合并、过滤、写出。
openpyxl：pandas 写出 .xlsx 时默认使用的引擎，也提供一些底层单元格格式控制。
xlrd：仅当你有 .xls 文件需要读取时才需要（新版 Excel 默认 .xlsx 已不用）。

判断该用哪个方法

| 场景 | 推荐工具 | 原因 | |------|----------|------| | 合并多个结构相同的文件 | pandas.concat | 一行代码，简洁高效 | | 按条件拆分一个大表为多个文件 | pandas + 循环 | 灵活，条件可自定义 | | 合并时保留原格式（颜色、字体、列宽） | openpyxl 手动复制 | pandas 不保留格式 | | 拆分后保持原格式 | openpyxl + 模板复制 | 复杂但可行，尽量先考虑纯数据场景 |

经验建议：如果你的最终目的是做数据分析，用 pandas 读入、处理、写出 CSV 或 .xlsx 就够，不用在格式上纠结。如果必须保留格式（如发给客户的报表），再用 openpyxl 做格式桥接。

合并多个 Excel 文件（实操步骤）

三个结构相同的Excel文件通过箭头合并为一个文件的示意图

场景示例

假设你有三个文件：sales_Q1.xlsx、sales_Q2.xlsx、sales_Q3.xlsx，它们有完全相同的列结构：

| date | region | product | sales_amount | owner | |------|--------|---------|--------------|-------| | 2025-01-05 | East | A | 1200 | Zhang |

你要把三个文件的所有行堆成一个总表 sales_2025_all.xlsx。

代码（可直接复制使用）

```python import pandas as pd import glob from pathlib import Path

1. 定义文件路径和输出文件名 input_folder = "./raw_sales" # 放所有源文件的文件夹 output_file = "./output/sales_2025_all.xlsx"

2. 使用 glob 匹配所有 .xlsx 文件 all_files = glob.glob(f"{input_folder}/*.xlsx")

df_list = [] for file in all_files: # 读取每个文件的第一个 sheet df = pd.read_excel(file) # 可选：添加一列标记来源文件名，便于追溯 df["source_file"] = Path(file).stem df_list.append(df)

3. 纵向合并（按行堆叠） combined = pd.concat(df_list, ignore_index=True)

4. 保存结果 combined.to_excel(output_file, index=False)

print(f"合并完成，共 {len(combined)} 行，保存至 {output_file}") ```

输出验证：运行后打开 sales_2025_all.xlsx，你应该看到类似：

| date | region | product | sales_amount | owner | source_file | |------|--------|---------|--------------|-------|-------------| | 2025-01-05 | East | A | 1200 | Zhang | sales_Q1 | | 2025-02-11 | West | B | 850 | Li | sales_Q1 | | 2025-04-03 | East | A | 1550 | Wang | sales_Q2 | | ... | ... | ... | ... | ... | ... |

新手注意点：

确保所有文件的列名完全一致（包括大小写和空格）。如果列名不同，pandas 会把它们当成不同列分别存放，结果会多出很多空列。
如果文件中包含你不需要的空白标题行或汇总行，在 pd.read_excel() 中用 skiprows 跳过。
ignore_index=True 告诉 pandas 重新生成连续的行号，而不是保留原文件的行号。不设置的话结果表里会有重复的行号。

常见变体：合并同一个工作簿的多个 sheet

如果你有一个文件内有三个 sheet（Jan、Feb、Mar），结构相同：

```python file = "sales_2025.xlsx" sheet_names = ["Jan", "Feb", "Mar"] # 或使用 pd.ExcelFile 自动获取所有 sheet 名

dfs = [] for sheet in pd.ExcelFile(file).sheet_names: # 读取所有 sheet df = pd.read_excel(file, sheet_name=sheet) df["source_sheet"] = sheet dfs.append(df)

combined = pd.concat(dfs, ignore_index=True) ```

拆分 Excel 文件（实操步骤）

场景示例

你有一张总明细表 orders_all.xlsx，包含以下列：

| order_id | customer | region | amount | status | |----------|----------|--------|--------|--------| | 1001 | Alpha | East | 250 | shipped |

你要按 region 列拆分成多个文件：orders_East.xlsx、orders_West.xlsx、orders_North.xlsx。

代码（可直接复制使用）

```python import pandas as pd

1. 读取总表 df = pd.read_excel("orders_all.xlsx")

2. 确定拆分依据列 split_column = "region"

3. 获取该列的所有唯一值 unique_values = df[split_column].dropna().unique()

for value in unique_values: # 筛选出该组的行 subset = df[df[split_column] == value].copy() # 生成输出文件名 output_file = f"orders_{value}.xlsx" # 保存 subset.to_excel(output_file, index=False) print(f"已生成 {output_file}，共 {len(subset)} 行") ```

输出验证：运行后在当前目录下应看到：

`` orders_East.xlsx (含 region 为 "East" 的所有行) orders_West.xlsx (含 region 为 "West" 的所有行) orders_North.xlsx (含 region 为 "North" 的所有行) ``

变体：拆分成同一个文件内的多个 sheet

如果你希望输出只有一个 .xlsx，但每个 region 一个 sheet：

``python with pd.ExcelWriter("orders_by_region.xlsx", engine="openpyxl") as writer: for value in df[split_column].dropna().unique(): subset = df[df[split_column] == value] # sheet 名不能超过 31 个字符，不能包含特殊符号 sheet_name = str(value)[:31] subset.to_excel(writer, sheet_name=sheet_name, index=False) ``

注意：Excel 工作表名最多 31 个字符，且不允许出现 [ ] : * ? / \ 等字符。如果你的分类值包含这些，需要先做替换或截断处理。

常见错误与排查

1. 数值被存成了文本

现象：合并后的 sales_amount 列显示为文本（单元格左上角有绿色三角），无法求和。

原因：原文件中有部分单元格的数值被 Excel 保存为文本格式，pandas 读入时自动按文本处理。

检查方法： ``python print(df["sales_amount"].dtype) # 显示 object 说明是文本 `` 修复方法（两种选一种）：

在读取时指定类型：pd.read_excel(file, dtype={"sales_amount": float})
读取后用 pd.to_numeric 转换：df["sales_amount"] = pd.to_numeric(df["sales_amount"], errors="coerce")（无法转换的会变成 NaN）

2. 区域引用没有锁定，合并后结果错位

现象：拆分后的文件里，amount 列出现 #REF! 或计算值不对。

原因：拆分用的筛选条件写成了变量名而不是实际值，比如误把 value 写成了 region。

常见写法错误： ``python # 错误：df[df[split_column] == region] # region 是列名，不是当前循环的值 # 正确：df[df[split_column] == value] # value 是具体分类值 ``

3. 合并后行数不对

原因：最常见的是源文件包含多余的合并单元格、空行或小计行。

解决方法：读取时明确指定 sheet_name 和 skiprows，或者在合并前加一步行数验证：

``python for f in all_files: df = pd.read_excel(f) print(f"{f}: {len(df)} 行") ``

如果某个文件行数异常多，打开它检查是否有过多空行或注释行。

4. 写出后文件里出现“Unnamed: 0”列

原因：你在读取或写出时没有处理索引列。

解决方法：在 pd.read_excel() 中加 index_col=0（如果原文件第一列是索引）；在 df.to_excel() 中一定要写 index=False。

5. 文件路径或编码问题

现象：FileNotFoundError 或 UnicodeDecodeError。

常见原因与修复：

路径中有中文：使用原始字符串 r"路径" 或 Path 对象。
Excel 文件被其他程序打开：关闭后再运行。
.xls 文件：确保装了 xlrd 库，并用 engine="xlrd" 参数。

与 Excel 内置功能的对比（什么时候用哪个）

| 方法 | 适合 | 不适合 | |------|------|--------| | Excel Power Query（获取数据→合并文件） | 1–5 个文件、格式一致、不需要代码 | 文件超过 10 个时设置繁琐；无法自动按类拆分 | | Excel 手动复制粘贴 | 单个文件、临时一次 | 容易漏行或贴错；超过 3 个文件就累 | | VBA 宏 | 需要高频在同一个 Excel 环境中运行 | 维护成本高；对新同事不友好 | | Python pandas | 文件数量多（10+）、需要重复运行、或合并后要做进一步数据分析 | 不保留原格式；需要装 Python 环境 |

一个常见取舍：如果你的合并操作只需要做一次、文件只有 3 个，用 Power Query 的“从文件夹合并”功能（数据 → 获取数据 → 从文件 → 从文件夹）更快，不需要写代码。但如果你需要每周跑一次，或者合并后要继续做透视分析，Python 更值得写。

FAQ