如何将多个Excel工作表的行按年份对应合并至单个工作表？

需求描述 #

我有5个行列数相同的Excel工作表，需要将这些工作表的行按年份对应合并为单个工作表——即每个年份的各表行依次排列。

输入示例 #

工作表1（SHEET 1）：

工作表2（SHEET 2）：

期望输出 #

实现方法 #

一、Excel原生操作（Power Query，高效适合多表） #

1. 打开目标工作簿，新建空白工作表作为输出表。
2. 点击「数据」选项卡 → 「获取数据」→ 「自文件」→ 「自工作簿」，选择当前工作簿。
3. 在导航器中勾选所有需要合并的工作表，点击「转换数据」。
4. 在Power Query编辑器中，对每个表执行：
   • 保留Year列和数据列（删除多余列）；
   • 统一数据列名称（例如将所有表的第2、3列重命名为x1、x2）。
5. 点击「开始」→ 「追加查询」→ 「追加查询作为新查询」，选择「将多个表追加到一起」并添加所有处理后的表。
6. 对追加后的表按Year列排序，点击「关闭并上载」将结果加载到新工作表。

二、R语言实现 #

使用readxl读取Excel，dplyr处理数据：

# 首次运行安装依赖包
install.packages(c("readxl", "dplyr", "writexl"))

# 加载包
library(readxl)
library(dplyr)
library(writexl)

# 定义文件路径
workbook_path <- "你的文件路径.xlsx"

# 读取所有工作表并统一列名
sheets <- excel_sheets(workbook_path)
df_list <- lapply(sheets, function(sheet) {
  read_excel(workbook_path, sheet = sheet) %>%
    rename(x1 = 2, x2 = 3) # 按列位置重命名数据列
})

# 合并数据并按Year排序
combined_df <- bind_rows(df_list) %>% arrange(Year)

# 保存结果到Excel
write_xlsx(combined_df, "合并结果.xlsx")

三、Python（Pandas）实现 #

使用pandas处理数据，openpyxl读写Excel：

import pandas as pd

# 定义文件路径
workbook_path = "你的文件路径.xlsx"

# 读取所有工作表并统一列名
xls = pd.ExcelFile(workbook_path)
df_list = []
for sheet_name in xls.sheet_names:
    df = pd.read_excel(xls, sheet_name)
    # 自动生成统一列名：Year + x1、x2...
    df.columns = ['Year'] + [f'x{i}' for i in range(1, len(df.columns))]
    df_list.append(df)

# 合并数据并按Year排序
combined_df = pd.concat(df_list).sort_values(by='Year').reset_index(drop=True)

# 保存结果到Excel
combined_df.to_excel("合并结果.xlsx", index=False, engine='openpyxl')

内容的提问来源于stack exchange，提问作者tom