Day20:批量文件终结者!用Python一键处理100个Excel,自动生成汇总报表

收集了几十个部门的表格，要一个一个打开复制粘贴？Python让你一键合并、清洗、分析、出图！

一、先看这个真实场景

王科长每个月都要做这件事：

1. 全校30个班级发来各自的成绩表（Excel文件，格式一模一样）
2. 要把它们合并成一张全校总表
3. 要清洗异常数据（比如分数>100或<0）
4. 要计算每个班级的平均分、优秀率
5. 要生成对比图表和总结报告

以前他：

1. 打开第一个文件 → 全选复制 → 粘贴到总表 ✓
2. 打开第二个文件 → 全选复制 → 粘贴到总表 ✓
3. …重复30次，眼睛都花了 ✓
4. 用公式算平均值 → 写VLOOKUP → 画图表 ✓
5. 发现某个班级数据有误，全部重来！ 😭

三天没了！

二、Python的“批量终结者”魔法

今天我们用10行核心代码，搞定上面所有事：

import pandas as pdimport osfrom datetime import datetime# 1. 批量读取所有Excel文件文件夹 = '各班级成绩'所有文件 = [f for f in os.listdir(文件夹) if f.endswith('.xlsx')]数据列表 = []for 文件名 in 所有文件:    文件路径 = os.path.join(文件夹, 文件名)    df = pd.read_excel(文件路径)    df['来源班级'] = 文件名.replace('.xlsx', '')  # 添加班级列    数据列表.append(df)# 2. 合并所有数据全校数据 = pd.concat(数据列表, ignore_index=True)# 3. 数据清洗（过滤异常分数）for 列 in ['语文', '数学', '英语']:    全校数据 = 全校数据[(全校数据[列] >= 0) & (全校数据[列] <= 100)]# 4. 计算总分和平均分全校数据['总分'] = 全校数据[['语文', '数学', '英语']].sum(axis=1)全校数据['平均分'] = (全校数据['总分'] / 3).round(1)# 5. 按班级统计班级统计 = 全校数据.groupby('来源班级').agg({'语文': 'mean','数学': 'mean','英语': 'mean','总分': 'mean','姓名': 'count'}).round(1).rename(columns={'姓名': '人数'})# 6. 生成报告（带格式）with pd.ExcelWriter(f'全校成绩汇总_{datetime.now().strftime("%Y%m%d")}.xlsx') as writer:    全校数据.to_excel(writer, sheet_name='详细数据', index=False)    班级统计.to_excel(writer, sheet_name='班级统计')print(f"✅ 搞定！共处理{len(所有文件)}个文件，合并{len(全校数据)}条记录")

看懂了吗？

• 文件操作：os.listdir()遍历文件夹，pd.read_excel()读取每个文件
• 数据合并：pd.concat()把多个DataFrame拼起来
• 数据清洗：过滤掉异常分数
• 新增列：计算总分平均分
• 分组统计：groupby()按班级汇总
• 保存：用ExcelWriter生成多sheet的Excel报告

三、分解“魔法”：每步在干什么？

3.1 第一步：获取所有Excel文件

文件夹 = '各班级成绩'所有文件 = [f for f in os.listdir(文件夹) if f.endswith('.xlsx')]os.listdir(文件夹)：列出文件夹里所有东西列表推导式：只保留以 .xlsx 结尾的文件注意：如果你的文件在别的地方，记得改路径。可以用绝对路径，如 r'C:\Users\你的名字\Desktop\各班级成绩'

3.2 第二步：逐个读取并添加班级标识

数据列表 = []for 文件名 in 所有文件:    文件路径 = os.path.join(文件夹, 文件名)    df = pd.read_excel(文件路径)    df['来源班级'] = 文件名.replace('.xlsx', '')    数据列表.append(df)

• os.path.join()：拼接文件夹路径和文件名
• pd.read_excel()：读取Excel文件到DataFrame
• 新增一列 来源班级，记录数据来自哪个文件（去掉扩展名）
• 把每个DataFrame放进列表，准备合并

3.3 第三步：合并所有数据

全校数据 = pd.concat(数据列表, ignore_index=True)pd.concat()：把列表里的多个DataFrame纵向拼接ignore_index=True：重新生成行索引（0,1,2...），避免原索引重复

3.4 第四步：数据清洗

for 列 in ['语文', '数学', '英语']:    全校数据 = 全校数据[(全校数据[列] >= 0) & (全校数据[列] <= 100)]循环三科，筛选出分数在0-100之间的记录（过滤异常值）注意：这里会覆盖原数据，只保留符合条件的数据

3.5 第五步：计算总分和平均分

全校数据['总分'] = 全校数据[['语文', '数学', '英语']].sum(axis=1)全校数据['平均分'] = (全校数据['总分'] / 3).round(1)sum(axis=1)：按行求和（axis=1表示横向）round(1)：保留1位小数

3.6 第六步：按班级统计

班级统计 = 全校数据.groupby('来源班级').agg({'语文': 'mean','数学': 'mean','英语': 'mean','总分': 'mean','姓名': 'count'}).round(1).rename(columns={'姓名': '人数'})

• groupby('来源班级')：按班级分组
• .agg({...})：对不同的列用不同的聚合函数
• 语文、数学、英语、总分用平均值
• 姓名用计数（统计人数）
• .round(1)：结果保留一位小数
• .rename(columns={'姓名': '人数'})：把“姓名”列改名为“人数”

3.7 第七步：保存报告

with pd.ExcelWriter(f'全校成绩汇总_{datetime.now().strftime("%Y%m%d")}.xlsx') as writer:    全校数据.to_excel(writer, sheet_name='详细数据', index=False)    班级统计.to_excel(writer, sheet_name='班级统计')

• pd.ExcelWriter：创建Excel写入器，可以写多个sheet
• 文件名用 datetime.now().strftime("%Y%m%d") 添加当天日期，避免覆盖
• to_excel(..., sheet_name='...')：分别写入不同sheet

四、自己动手试试！

4.1 第一步：创建测试文件夹和文件

import osimport pandas as pdimport random# 创建测试文件夹测试文件夹 = '各班级成绩'os.makedirs(测试文件夹, exist_ok=True)# 生成5个班级的模拟数据班级名 = ['三(1)班', '三(2)班', '三(3)班', '三(4)班', '三(5)班']for 班级 in 班级名:    数据 = pd.DataFrame({'姓名': [f'学生{i}'for i in range(1, 11)],  # 每班10个学生'语文': [random.randint(60, 100) for _ in range(10)],'数学': [random.randint(60, 100) for _ in range(10)],'英语': [random.randint(60, 100) for _ in range(10)]    })    数据.to_excel(os.path.join(测试文件夹, f'{班级}.xlsx'), index=False)print(f"✅ 已创建{len(班级名)}个测试文件在「{测试文件夹}」文件夹")

4.2 第二步：运行批量处理脚本

把第二节的核心代码复制下来，修改 文件夹 = '各班级成绩' 即可运行。

运行后你会得到：

• 一个Excel文件，名字类似 全校成绩汇总_20250221.xlsx
• 里面有两个sheet：       

• 详细数据：所有班级的原始数据合并，带“来源班级”列

• 班级统计：每个班级的各科平均分、总分平均分、人数

五、办公实战1：自动计算优秀率并美化

import pandas as pdimport osfrom datetime import datetime文件夹 = '各班级成绩'所有文件 = [f for f in os.listdir(文件夹) if f.endswith('.xlsx')]数据列表 = []for 文件名 in 所有文件:    df = pd.read_excel(os.path.join(文件夹, 文件名))    df['来源班级'] = 文件名.replace('.xlsx', '')    数据列表.append(df)全校数据 = pd.concat(数据列表, ignore_index=True)# 计算各科优秀（≥90）for 科 in ['语文', '数学', '英语']:    全校数据[f'{科}优秀'] = 全校数据[科] >= 90# 按班级统计班级统计 = 全校数据.groupby('来源班级').agg(    人数=('姓名', 'count'),    语文平均=('语文', 'mean'),    数学平均=('数学', 'mean'),    英语平均=('英语', 'mean'),    语文优秀人数=('语文优秀', 'sum'),    数学优秀人数=('数学优秀', 'sum'),    英语优秀人数=('英语优秀', 'sum')).round(1)# 计算优秀率for 科 in ['语文', '数学', '英语']:    班级统计[f'{科}优秀率'] = (班级统计[f'{科}优秀人数'] / 班级统计['人数'] * 100).round(1)# 排序（按语文平均分降序）班级统计 = 班级统计.sort_values('语文平均', ascending=False)# 美化并保存with pd.ExcelWriter(f'班级优秀率统计_{datetime.now().strftime("%Y%m%d")}.xlsx', engine='openpyxl') as writer:    全校数据.to_excel(writer, sheet_name='详细数据', index=False)    班级统计.to_excel(writer, sheet_name='班级统计')print("✅ 优秀率统计完成！")

六、办公实战2：自动生成各班成绩图表

import pandas as pdimport osimport matplotlib.pyplot as pltfrom datetime import datetime文件夹 = '各班级成绩'所有文件 = [f for f in os.listdir(文件夹) if f.endswith('.xlsx')]数据列表 = []for 文件名 in 所有文件:    df = pd.read_excel(os.path.join(文件夹, 文件名))    df['来源班级'] = 文件名.replace('.xlsx', '')    数据列表.append(df)全校数据 = pd.concat(数据列表, ignore_index=True)# 计算总分全校数据['总分'] = 全校_data[['语文', '数学', '英语']].sum(axis=1)# 按班级统计平均分班级平均 = 全校数据.groupby('来源班级')[['语文', '数学', '英语', '总分']].mean().round(1)# 画柱状图班级平均.plot(kind='bar', figsize=(12, 6))plt.title('各班级平均分对比')plt.ylabel('分数')plt.xlabel('班级')plt.xticks(rotation=45)plt.tight_layout()plt.savefig('班级平均分对比图.png')plt.show()# 保存数据with pd.ExcelWriter(f'成绩分析_{datetime.now().strftime("%Y%m%d")}.xlsx') as writer:    全校数据.to_excel(writer, sheet_name='详细数据', index=False)    班级平均.to_excel(writer, sheet_name='班级平均')print("✅ 分析完成，图表已保存！")

七、进阶技巧：处理文件名中的日期并筛选

有些文件命名包含日期，比如 2025-05-12 三(1)班.xlsx，我们可以提取日期，只处理特定月份的数据：

import redef 提取日期(文件名):    match = re.search(r'(\d{4})-(\d{2})-(\d{2})', 文件名)if match:return datetime(int(match.group(1)), int(match.group(2)), int(match.group(3)))return None# 在读取文件时，记录日期df['文件日期'] = 提取日期(文件名)# 之后可以筛选某月数据 = 全校数据[全校数据['文件日期'].dt.month == 5]  # 只保留5月的

八、重点总结：今天你学会了什么？

✅ 核心技能

1. 批量读取文件：os.listdir() + pd.read_excel()
2. 合并多个表格：pd.concat()
3. 添加来源标识：在循环中新增列
4. 批量清洗：循环检查多列
5. 分组统计：groupby().agg()
6. 多sheet保存：pd.ExcelWriter
7. 生成图表：matplotlib

✅ 办公应用场景

1. 合并各班级成绩 → 全校总表 + 班级统计
2. 合并各部门报表 → 公司汇总 + 部门对比
3. 合并各月销售 → 年度销售分析
4. 合并学生档案 → 全校花名册

✅ 效率对比

九、今日挑战：动手做！

任务1：按班级统计总分最高分和最低分

# 修改上面的代码，在班级统计中添加“最高分”和“最低分”列

任务2：筛选出有学生不及格的班级

# 找出语文、数学、英语任一科<60的学生，然后按班级统计人数

任务3：给生成的Excel添加数据条和颜色（复习第17天）

# 在保存前，对班级统计表用.style添加数据条和颜色

十、明日预告

明天学自动化邮件发送！

• 如何用Python把生成的报表自动发送邮件给领导？
• 如何定时每天/每周自动运行脚本并发送？
• 如何添加多个附件、编写正文？

真正的“无人驾驶”办公，从明天开始！

回复「Py-Day」获取今日挑战题解及完整代码。

评论区作业：分享你批量处理过的文件数量，或者晒出你的汇总报表截图！👇