期末统考结束班主任扔给我三个年级的 Excel 成绩表让我半天内把所有班级的总分、排名、及格率理清楚。手动拖公式不是不行但这十几个文件的结构还不完全一样有的班级加了一列“平时成绩”有的把缺考的学生直接留空光清理格式就能花掉小半天。事后我花了一个多小时写了个 Pandas 脚本跑完所有文件只花了十几秒还顺手生成了带颜色标注的分析报告。这套事情干完唯一的想法就是这种重复劳动早该交给脚本。这篇教案是 Python 系列的第 5 课主题就是用 Pandas 把零散的 Excel 成绩分析流程统一成自动化脚本。先声明这份内容不是给你的代码加一点注释就算完而是要让你弄明白一个成绩分析脚本应该拆成哪些环节每个环节 Pandas 提供了什么工具为什么这些工具能大幅减少你的手工操作。适合正在带班统计成绩的老师、需要批量处理 Excel 表格的运营人员也适合刚学完 Pandas 基础语法但不知道从哪里下手做实际项目的同学。1. 成绩分析场景里最容易被低估的痛数据不规整很多人以为自动化成绩分析难点在于“算平均分”和“排名”这种计算逻辑。实际干过一回就知道计算反而是最简单的一步。真正磨人的是原始表格里的数据乱象。1.1 真实成绩表里会遇到的典型脏数据以我上课时反复教学生处理的案例来说一张真实的 Excel 成绩表通常面临这几类状况表头不在第一行。有些工作表第 1 行是“××班期末考试成绩表”第 2 行才是字段名第 3 行才是数据。read_excel 默认把第 0 行当成列名直接读进来字段名就全乱了。存在合并单元格。标题行跨列合并、某个学生缺考被合并成一行备注用 Excel 肉眼看没事Pandas 读进来就是一堆 “Unnamed: 1”、“Unnamed: 2” 这种幽灵列。同一门课程在文件里的列名不完全一致。比如一班叫“语文”二班叫“语文成绩”三班可能叫“语文满分150”。缺考学生的单元格是空值还有个别格子是“不及格”“免考”这种文本混着数字列一起计算会直接报错或产生 NaN。一旦你意识到这些才是主要矛盾就会明白为什么要用 Pandas 而不是继续用 Excel 函数。Pandas 的定位不是“速度快一点的表格工具”而是给你一套完整的数据清洗、转换、聚合、输出编程接口。你不需要在单元格里写钩稽公式而是用代码定义数据变换的流水线。1.2 自动化成绩分析能省掉的重复操作清单拿普通 6 个班、每班 50 人来算传统手动流程大致需要6 遍复制粘贴把各班名单汇总在同一张表里。每列插入 SUM、AVERAGE、COUNTIF 公式再逐班核对区域范围。按总分降序排列给排名列填 1、2、3……之后还得撤销排序恢复学号顺序。统计各科及格人数、优秀人数再把结果誊抄到汇总表。最后根据总分级差手动画色标出前 10 名和不及格学生。这一套流程熟练操作也需要 40 分钟到 1 小时误差还随班级数量增加。换成 Pandas 脚本后数据处理全部由 DataFrame 对象流转完成你只写一遍逻辑循环处理多少班都行而且每一步都有中间结果可以检查。从效率上讲这不是“提升 20%”的级别而是完全改变工作方式。2. 先把 Pandas 读取 Excel 的内部逻辑理顺用 Pandas 做 Excel 成绩分析第一步就是读文件但 read_excel 的参数远不止“指定文件路径”这么简单。2.1 read_excel 的关键参数组合在动手之前先安装好依赖环境。我用的是 Python 3.10 加 pandas 2.x读取 Excel 需要底层引擎 openpyxl 的支持安装命令是pip install pandas openpyxl打开一个目录下的成绩单最常用的读取参数如下import pandas as pd # 读取指定 sheet并跳过标题行 df pd.read_excel( 期末成绩表.xlsx, sheet_name一班, header1, # 字段名在第 2 行 usecolsA:F, # 只读取 A 到 F 列 dtype{学号: str} # 防止学号被当成数值后丢失前导 0 )这里我要多说一句header1的含义。Pandas 读取 Excel 时header 指的是“把第几行当作列名”行号从 0 开始计数。所以第 2 行当列名就写 header1。绝大多数真实的成绩表都不会规规矩矩地把字段名放在第 1 行前面往往压一行“XX 学校XX考试”的大标题这个参数必须用起来。dtype{学号: str}是一个很容易被忽略但其实很重要的参数。学号如果以数字形式存储Excel 会去掉前导 0Pandas 读进来默认也是 int64。等到后面和另一个文件里的学号做匹配时一个字符串“20240101”和一个数值 20240101merge 会失败。提前用 dtype 强制转成字符串可以规避这一类问题。2.2 一次性读取一个目录里的所有 Excel 文件成绩分析自动化的进阶操作是不用手动指定文件直接用 pathlib 遍历目录from pathlib import Path data_dir Path(./成绩表) all_sheets [] for xlsx_file in data_dir.glob(*.xlsx): # 每个文件可能包含多个 sheet全部读取进来 sheets pd.read_excel(xlsx_file, sheet_nameNone, header1) for sheet_name, df in sheets.items(): df[班级] f{xlsx_file.stem}_{sheet_name} all_sheets.append(df) raw_df pd.concat(all_sheets, ignore_indexTrue)这段代码的价值在于sheet_nameNone会返回一个字典键是 sheet 名值是对应的 DataFrame。我顺便把“来源于哪个文件、哪个 sheet”变成了一列班级后续统计就能按这一列分组不再需要手动维护文件清单。以后再来一张表丢进目录里重新跑一遍脚本就行这是“自动化”的第一层体现。运行完上面的代码不妨先打印一下raw_df.columns和raw_df.head(3)确认列名是否一致。如果不同班级的列名不统一比如有的叫“语文成绩”有的叫“语文”就需要动手做列名归一化方法在第 4 节详谈。3. 用 DataFrame 的思维方式替代手工公式很多从 Excel 转到 Pandas 的人卡住的不是语法而是思维方式。在 Excel 里所有操作都是“选中区域 - 应用公式/功能”在 Pandas 里所有操作都是“构造新列/筛选行/分组聚合”。两种思维的转换是整个自动化的关键。3.1 新增总分和平均分列的三种写法对成绩表追加“总分”“平均分”至少要会三种典型写法。写法一按列相加score_columns [语文, 数学, 英语, 物理, 化学, 生物] df[总分] df[score_columns].sum(axis1) df[平均分] df[总分] / len(score_columns)写法二剔除值之后再合计如果有的学生缺考成绩单元格是 NaN直接用 sum(axis1) 会默认跳过空值。缺考等于“没参加考试”计 0 分还是不计入总分业务口径要先定清楚。常见做法是把 NaN 先填充为 0 再求和df[总分] df[score_columns].fillna(0).sum(axis1)写法三分科目计算加权总分部分考试会有“平时成绩 30% 期末成绩 70%”这类规则在 Pandas 里就是构造加权表达式df[综合成绩] df[平时成绩] * 0.3 df[期末成绩] * 0.7注意这里的权重系数写在代码里比写在 Excel 公式里更清晰。一旦评分规则变化只需要改动这一行重新运行脚本即可不需要再挨个班级查找修改公式范围。3.2 筛选和排序背后的逻辑组合成绩分析里高频操作是“选出前 10 名”“找出不及格学生”。在 Pandas 里筛选和排序通常连着用# 按班级分组取每个班总分前 10 名 top10_per_class ( df.sort_values(总分, ascendingFalse) .groupby(班级, group_keysFalse) .head(10) )我把排序放在 groupby 之前。sort_values保证全表按总分降序然后按班级分组每个组取前 10 行。这个顺序是固定的不能反过来如果先 groupby 再 sort 每个组就得用 apply 写 lambda可读性差很多。至于“不及格”这种筛选和 Excel 里的自动筛选本质是一样的failed df[df[数学] 60]这句代码要拆开看。df[数学] 60返回的是一个布尔 Seriesdf[布尔 Series]会保留所有对应位置为 True 的行。这种布尔索引是 Pandas 里最重要、也最常用的能力后面看到的所有“按条件筛行”的操作底层都是它。4. 核心统计指标的拆解与聚合计算基础列追加完成后真正的“分析”环节才开始。这一节我把成绩分析里最常见的统计指标全部拆出来逐个讲清楚。4.1 描述性统计一行看清各科整体水平Pandas 自带 describe 方法可以直接看数值列的四个统计量stats_df df[score_columns].describe().T stats_df.columns [数量, 平均分, 标准差, 最低分, 25%, 50%, 75%, 最高分]如果你第一次用 describe需要注意它统计的是非空值数量不是全班人数。如果全班 50 人某科只有 48 个有效成绩count 会显示 48。这时候要在报告里说明“参考人数”不要让人误读成缺考。当然单纯依赖 describe 不够。成绩分析还要算优秀率、及格率和各分数段人数分布。这几个指标可以写成一个小函数def subject_summary(series): total series.notna().sum() passed (series 60).sum() excellent (series 90).sum() return pd.Series({ 参考人数: total, 平均分: round(series.mean(), 2), 最高分: series.max(), 最低分: series.min(), 及格率: f{(passed / total * 100):.1f}%, 优秀率: f{(excellent / total * 100):.1f}%, })把函数应用到每一列就可以得到一张科目汇总表subject_report df[score_columns].apply(subject_summary)apply在这里的语义是对 DataFrame 的每一列调用一次 subject_summary返回结果拼成新的 DataFrame。这是 Pandas 高阶操作里最实用的一个。4.2 按班级分组的聚合逻辑前面拿到了全校数据折raw_df但如果只看总体汇报班主任大概会追问你“各班情况怎么样”。所以分组计算是刚需。groupby的基本用法是class_group df.groupby(班级)[总分].agg( [mean, max, min, count] )但这张表的可读性不够好列名是英文。更专业一点的做法是用 dict 指定字段和聚合函数class_summary df.groupby(班级).agg( 参考人数(学号, count), 总分平均(总分, mean), 语文平均(语文, mean), 数学平均(数学, mean), 英语平均(英语, mean), ).round(1)agg配合元组 (列名,聚合函数名) 的写法在 pandas 2.x 里非常清晰。你不需要记住 agg 函数参数的各种别名只要把“对哪一列做什么操作”说清楚就行。4.3 各分数段人数分布的透视表如果想看“每个班 90 分以上多少个、80 到 89 分多少个”最顺手的是 cut 把分数分箱然后用 crosstab 做交叉表bins [-1, 59, 69, 79, 89, 100] labels [不及格, 60-69, 70-79, 80-89, 90-100] for subject in score_columns: df[f{subject}_分段] pd.cut(df[subject], binsbins, labelslabels) # 以班级为行、数学分段为列统计人数 math_dist pd.crosstab(df[班级], df[数学_分段])crosstab 返回的结果是一个比较规整的 DataFrame行是班级列是分数段交叉点是人数。这个结构直接可以作为汇报表格使用也方便以后用 matplotlib 或 pyecharts 画堆叠柱状图。注意bins的边界它代表的是区间 (-1, 59]、(59, 69]…… 所以边界取 -1 而不是 0是为了确保 0 分也落在“不及格”区间不产生 NaN 分段。这个细节很容易踩坑。4.4 排名的实现逻辑排名是一个表面简单、实则有坑的需求。直接用rank默认是“平均名次”也就是并列的成绩会共享名次后一名次跳过。例如两个 95 分并列第 1下一个 94 分是第 3 名。这不是所有学校都接受的规则。有些学校希望并列占用名额后下一个仍然是第 2 名的“紧凑型排名”做法是df[总分排名] df.groupby(班级)[总分].rank( methodmin, ascendingFalse ).astype(int)method 参数有四个可选值average并列取平均名次、min并列取较小序号即紧凑型、max并列取较大序号、dense完全紧凑且不跳号。在年级大排名时常用 average在班级内部排名时用 min 更符合日常习惯。至于到底用哪种建议在脚本里定义一个变量比如rank_method min方便按学校要求一键切换。5. 把分析结果输出成一份真正能交差的 Excel 报告分析做完了结果躺在 pandas 的 DataFrame 里还不够它得变成一个别人能直接查看、不需要安装 Python 环境的文件。这里需要用到 ExcelWriter把多个 DataFrame 写到同一个工作簿的不同 sheet再给指定单元格套上颜色和格式。这一节讲输出报告的具体做法。5.1 多 sheet 输出的基础结构用 pd.ExcelWriter 方式写 Excel 时需要一个 engine。openpyxl 支持样式设置xlsxwriter 支持更多图表类型。我这里用 openpyxl 方案演示它在社区里维护最稳定。output_path ./成绩分析报告.xlsx with pd.ExcelWriter(output_path, engineopenpyxl) as writer: df.sort_values([班级, 总分], ascending[True, False]).to_excel( writer, sheet_name学生成绩明细, indexFalse ) subject_report.T.to_excel(writer, sheet_name科目汇总) class_summary.to_excel(writer, sheet_name班级汇总) math_dist.to_excel(writer, sheet_name数学分数段分布)这种用 with 块管理 writer 的方式最大的好处是文件在代码块结束后会自动保存不用记 writer.save()也避免中途抛异常导致文件未关闭。每个 DataFrame 写一个 sheet名称起得直观一点打开报告的人一眼就能找到想看的内容。5.2 用 openpyxl 给报告加颜色和边框原始数据写进去了但从可读性来讲没有颜色和边框的 Excel 报告跟一堆纯文本没什么两样。打开 writer 的 book 属性操作工作表的样式from openpyxl.styles import PatternFill, Font, Alignment from openpyxl.utils import get_column_letter with pd.ExcelWriter(output_path, engineopenpyxl) as writer: df.head(50).to_excel(writer, sheet_name成绩明细, indexFalse) ws writer.sheets[成绩明细] # 设置表头背景色和字体 header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) for cell in ws[1]: cell.fill header_fill cell.font Font(boldTrue, colorFFFFFF) cell.alignment Alignment(horizontalcenter, verticalcenter) # 给“总分排名”列添加条件填充 rank_col_index df.columns.get_loc(总分排名) 1 # 列索引转 Excel 列号 for row in ws.iter_rows(min_row2, min_colrank_col_index, max_colrank_col_index): for cell in row: if isinstance(cell.value, (int, float)) and cell.value 3: cell.fill PatternFill(start_colorFFC000, end_colorFFC000, fill_typesolid)这里有个容易错的点df.columns.get_loc(总分排名)返回的是从 0 开始的列号而 Excel 列号从 1 开始所以要在后面 1。而且要注意 DataFrame 写入 Excel 后默认索引列占用第 1 列如果你设置 indexFalse就不会有这一行偏移用 get_loc 1 也是对的。如果你因为后续处理需要保留索引那列号偏移就得多加 1建议输出时统一 indexFalse让列号计算更简单。5.3 自动生成一段“分析结论”文本报告里除了数据表还应该有结论文字。用 f-string 把统计结果拼进去会产生……“分析意见”一样的效果worst_subject subject_report.T[平均分].idxmin() best_class class_summary[总分平均].idxmax() fail_students (df[总分] 0) # 占位实际写作 df[df[英语] 60] 的学号列表 conclusion ( f本次考试共统计 {df[学号].nunique()} 名学生 f整体平均分 {df[总分].mean():.1f} 分 f最高分 {df[总分].max()} 分。 f班级平均分最高的是 {best_class} f各科里平均分相对最低的科目是 {worst_subject} f建议后续教学重点关注。 )这段文本可以直接写到 Excel 最顶部也可以存成一个 txt 文件。重点是不要让结论内容散落在代码里没人看到数据算出来就是要给人做判断参考的。5.4 超长表格冻结首行和自动调整列宽报告表一大打开 Excel 往下滚动时表头消失是最影响体验的问题。用 openpyxl 冻结首行很方便ws.freeze_panes A2同时给列宽设置一个合适的值for i, col in enumerate(df.columns, start1): max_len max( df[col].astype(str).map(len).max(), len(col) ) ws.column_dimensions[get_column_letter(i)].width min(max_len 4, 20)max_len这个逻辑是从该列所有值里取字符串最大长度再和列名长度比较取较大的那个加 4 作为间距上限 20 防止“学生姓名特别长导致列无限宽”。列宽设置完视觉效果会好很多这点虽然不影响结果但对观感提升非常明显值得做进脚本里。6. 踩坑复盘写成绩分析脚本最容易翻车的四个场景这部分是我在多个项目里反复碰过的问题单独列出来讲透因为每个坑的排查链路都很有代表性。6.1 科目列名不统一导致的列“消失”第一次拿两个班的表合并时我发现一班的列名是“语文”二班是“语文成绩”读进 DataFrame 后就出现了两列不同的字段。我拿score_columns去做 sum 时其中一个班的分数全是 NaN因为列名对不上。解决思路可以是读取文件后先做一个别名映射把常见的“语文成绩”“语文满分 150”“语文150”全部映射到统一的“语文”rename_mapping { 语文成绩: 语文, 语文满分150: 语文, 语文(150): 语文, } df df.rename(columnsrename_mapping)规范的脚本里我会在读取循环里对所有 DataFrame 统一执行 column_rename然后打印一次清洗后的列名确认没有遗漏。这是成本最低且最稳妥的“列名归一化”阶段。6.2 空字符串被读成了 NaN 但没被发现Excel 里的空格、空字符串在 Pandas 里表现很微妙。read_excel 默认会把空单元格解析成 NaN但如果你在 read_excel 之后手动改列或者用 fillna 填充过一部分值就可能出现 “nan” 字符串和真正的 NaN 混在一起。在做数值计算时含字符串的列会被当成 object 类型调用 sum、mean 会抛异常或产生 0 结果。判断办法很简单我基本每次都会跑print(df.dtypes) print(df.isna().sum())dtypes能一眼看出哪些列是 object、哪些是 float。一个正常的成绩列应该是 float64 或 int64如果发现是 object大概率有“缺考”“免考”这类文本或者空字符串。这时候调用pd.to_numeric(column, errorscoerce)把非法值统一转成 NaN是常规处理手段。6.3 学号和日期类型被误判前面提过学号会被读成数值还有一个隐藏坑是学号列被读成 float比如 202401.0。当你用 merge 连接另一个表时怎么匹配都对不上。这个坑的排查链路通常如下写 merge 代码发现NaN的数量巨大。打印两个 DataFrame 的 dtypes。发现一个学号列是 int64另一个是 object 或 float64。统一 dtypestr 后重新 merge。所以我在读文件时就用dtype{学号: str}如果之前已经读进来了就用df[学号] df[学号].astype(str).str.replace(.0, , regexFalse)来清理。.0是 float 转字符串之后的残留不处理干净会导致关联不上。6.4 输出 Excel 时中文编码和字体问题用 pandas 的 to_excel 输出中文不会有编码问题因为 openpyxl 内部用 XML 存储天然支持 Unicode。但如果你把报告导出成 CSV再发给别人用老版 Excel 打开就会遇到中文乱码。处理办法是df.to_csv(成绩.csv, indexFalse, encodingutf-8-sig)utf-8-sig会在文件开头写入 BOM老版本 Excel 才能正确识别 UTF-8 的中文这个细节在交付 CSV 文件时尤其重要。另外Excel 的单元格字体可以用 openpyxl 的 Font 设置比如统一Font(微软雅黑, size10)别用默认的 Calibri 字体显示中文观感会差很多。7. 把脚本封装成命令行工具让它真正融入日常工作流写到这里基础的自动化成绩分析已经跑通了。但仅仅一个能跑的 .py 文件还谈不上“自动化”。自动化应该是一句话就能执行、参数可以按需传入、输出文件命名有规律的状态。所以我把它做成了命令行脚本放进了课程示例里。7.1 用 argparse 接收输入参数import argparse parser argparse.ArgumentParser(descriptionPandas 成绩分析自动化脚本) parser.add_argument(--data-dir, typestr, default./成绩表, help存放原始成绩 Excel 文件的目录) parser.add_argument(--output, typestr, default./成绩分析报告.xlsx, help输出报告文件名) parser.add_argument(--rank-method, typestr, defaultmin, choices[min, average, dense, max], help排名并列处理方式) args parser.parse_args()这样以后运行只需要python analyze_scores.py --data-dir ./考试A --output ./考试A_分析.xlsx --rank-method average对不同考试启用不同排名规则的时候不需要改代码逻辑只需要换一个命令行参数。把这段命令写进一个 .batWindows或 .shmacOS/Linux脚本里以后双击就能跑真正常用的同事也不用学 Python 了。7.2 把核心函数拆成可测试的单元我把整个流程拆成 load_raw_data、clean_columns、compute_score、build_reports、write_output 这五个函数。每个函数都返回 DataFrame 或接收 DataFrame互相之间没有隐藏依赖。这样的结构有几个好处报表数据出问题时能定位到具体函数排查。Jupyter Notebook 里可以直接调用其中一步做试验。后续第 7 课要讲“用 unittest 测试 Pandas 代码”时直接拿这些纯函数当测试对象。初学者写自动化脚本最容易犯的错就是把所有逻辑塞在一个 200 行的 main 函数里。代码能跑但调试和复用都很痛苦。按“读入 - 清洗 - 计算 - 汇总 - 输出”的分层思路更清楚。7.3 定时任务的衔接如果学校月考几乎一个月一次你可以把脚本用 cronmacOS/Linux或“任务计划程序”Windows定期执行。虽然每次考试的表格格式不一定完全一样但只要统一模板定时跑完全没问题。这一步最底层的要求是脚本输入目录固定、输出文件名带日期。日期可以这样生成from datetime import datetime today_str datetime.now().strftime(%Y%m%d) output_path f./成绩分析报告_{today_str}.xlsx跑完自动生成以日期为后缀的报告历史记录留存也好找。这个“文件名带日期”的习惯放在几乎所有自动化脚本里都适用不要直接用固定的“report.xlsx”否则下一次跑任务会覆盖上一次的结果。8. 课程里留给学生的三个练习方向这套教案讲解完了 Pandas 自动化 Excel 成绩分析的完整链路但如果只是照着抄一遍学习效果有限。我在课程末尾留了三个难度递增的练习建议想真正上手的读者也做一遍。练习一给脚本增加“各科成绩最高分学生姓名”的汇总信息让学生学会用idxmax()定位索引再反向取数据。练习二把分析结果用 matplotlib 画出各班级平均分的柱状图并把图片嵌入到 Excel 报告中。这涉及 openpyxl 的 add_image 模块是一次很好的交叉练习。练习三处理一个故意构造的“脏数据”成绩表里面包含合并单元格、空字符串、文本分数和重复行要求写完清洗逻辑后能自动生成一份不至于误导别人的分析报告。这三个练习分别对应“索引操作”“结果可视化”“数据清洗”三个核心能力。做完之后你会发现 Pandas 处理 Excel 的套路基本打通了以后再遇到别类型的 Excel 自动化需求思路是相通的。至于我自己使用的后续扩展方向是用 Pandas 的 styler API 在写入前直接生成条件格式列这样就不用每次手动操作 openpyxl 的单元格样式不过那是另一个话题了等课程推进到可视化报告那一课我再单独写一篇分享。