★ FEATURED ARTICLE 先说一个我印象很深的场景。某次单位组织技能比武80个选手、5位评委评分表收上来之后全堆在我这里。领导站在旁边说“下班前把排名给我。”我当时用Excel手动处理先给每个选手去掉最高分和最低分再算平均然后手工填名次、手工划奖项稍微眼一花就得回头核对改一个评委的分数又得连锁重算。折腾到晚上九点才交差领导还问了一句“这个名次核实过吗”。那个场面相信搞过比赛统计的人都懂。后来我用Python写了个自动评分系统核心就是三件事读取原始评分Excel、自动去掉最高最低分算出均分、再按名次生成带奖项的成绩单。80个选手从运行到出结果1秒都不到。领导看我用终端跑了一下表格直接生成愣了一下问这是不是“一键生成工具”。这篇文章把整套源码、设计思路、实操踩坑全部放出来。如果你经常跟Excel里的打分表打交道或者想用Python解决实际工作里的小问题可以直接照着用。1. 场景还原办公室统计比赛成绩这件事到底难在哪1.1 80个选手、5个评委人工统计崩溃在哪80个选手不算多5个评委也不算多但组合在一起工作量是量级的。每个选手要检查5个分数去掉最高最低计算平均分80个人就是400个打分格子的判读再乘上“检查有没有填错、有没有空白、有没有写了个斜杠”这类脏数据问题手工操作极易出错。更麻烦的是这类统计往往不是只算一次。比如某个评委发现自己给某位选手的分数打错了要求更正。人工流程里你改完这个人的分数还要担心排名是否被影响、奖项划分是否要调整。我用Excel的时候为了防错把所有公式铺了一整行看起来密密麻麻。结果某次复制公式时行错位3个选手的均分算错直到排名出来后跟原始分核对才发现。那种心情比加班还难受。后来总结下来这类统计有三个痛点过程不可见Excel公式链太长出了问题不好定位数据易脏评委可能填文本、留空、加备注手工清理容易漏结果难复核领导问“这个名次怎么来的”你要么翻公式要么重新算一遍。1.2 为什么用Python而不是Excel公式硬顶有人会问Excel不是也有平均分、RANK函数吗确实有。但实际用下来Excel方案在一次性统计里还行一旦要反复调整、多人协作、结果要好看就有点吃力。这里不是贬低Excel而是要看场景。对比项Excel公式方案Python脚本方案逻辑可见性公式藏在格子里容易误改代码统一管理逻辑外显数据改动后重算要重新检查公式范围重新运行一次即可错误定位靠肉眼查单元格报错会指出哪一步出问题导出结果需要另做排版直接生成规范成绩单维护成本别人看不懂你的表脚本注释写清楚维护有据我不是说Excel不行我自己也用Excel做大量数据处理。但这种“要出最终排名、要适配评分规则、要反复给领导汇报”的场景脚本的稳定性和可重复性明显更优。尤其当原始数据有几十上百行时代码跑一遍和手工核对一遍可靠性完全不一样。1.3 自动评分系统的核心链路这套系统的逻辑其实不复杂核心链路可以概括成读取原始评分表 - 把所有分数转成数字处理脏数据 - 逐行去掉最高分和最低分 - 计算均分 - 排名 - 按比例分配奖项 - 导出Excel成绩单。听起来简单但里面的细节有不少坑。比如某个评委打了空值怎么办有效分只有2个时还去不去极值同分的时候怎么排名奖项比例按人数算还是按名次算。这些在写代码前就得想清楚。下面我先把数据格式和评分规则讲明白再放完整源码最后讲实测中遇到的坑。2. 动手前先定规矩评分表格式与评分规则2.1 一份标准评分表的格式约定写代码之前必须先约好输入表格长什么样。这不是教条而是防止后面浪费大量时间做数据清洗。我按下面这种格式准备原始评分表选手编号选手姓名评委1评委2评委3评委4评委5A01参赛者019.29.58.89.49.0A02参赛者029.69.19.39.59.2这里有几个约定要提前讲清楚每一行只有一名选手不要合并单元格不要在一个格子里写多个分数表头字段名尽量固定为“选手编号、选手姓名、评委1……评委N”分数统一填数字不要加“分”字不要用“9-”这类省略写法缺考或未评分的地方留空即可不要填“无”“/”这些字符否则后续要额外处理。实际操作里原始表第一行经常是活动主题的大标题比如“XX技能大赛评分表”第二行才是真正的字段名。这种情况我建议在代码里指定读第二行作为表头也就是header1。我在源码里默认header0如果你的表是两行表头改成header1就行了。判断方法很简单运行后如果列名出现Unnamed: 0那多半是表头行没选对。2.2 评分规则如何定代码就怎么写评分规则要在代码之前敲定。我见过很多比赛规则使用最普遍的是截尾均值法去掉一个最高分、去掉一个最低分剩下分数取平均。这样做的目的是消除个别评委的极端打分对最终成绩的影响。听起来有点学术其实很直观——就像评委打分时去掉那个明显偏高或偏低的分数选手成绩才更稳。我这次按以下规则处理5个评委都打分去掉最高分和最低分剩下3个分数取平均有效分只有3个或2个不再去极值直接取平均因为再砍分数就没意义了有效分只有1个或0个按0分或实际分数处理同时结果表里人工复核排名按均分从高到低排同分并列后续名次跳过奖项前10%为一等奖接下来20%为二等奖再接下来30%为三等奖。奖项比例我设成了一个字典你完全可以改成自己比赛的规则比如一等奖5人、二等奖10人这种固定名额甚至改成“一等奖前5名二等奖6到15名”也没问题。代码只需要相应调整分配逻辑。2.3 防脏数据的设计先约束表格再写代码我吃过一次大亏某次统计评委在一个分数后面加了备注“9.5表现好”整个单元格变成文本手工没看出来结果那位选手的均分被算成0。后来再遇到类似情况我学会了两件事。第一给评委发打分表的时候就用Excel的数据有效性把分数范围限制好同时列名写死让评委只填数字。这样从源头上减少脏数据。第二代码里做防御性处理用pd.to_numeric(errorscoerce)把所有分数列强制转成数值转不过去的统统变成空值。这样即使有脏数据也不会导致整个程序崩溃最多是某个选手数据异常方便事后单独核查。还有一点很重要不要把原始评分表覆盖掉。脚本生成的最终成绩单另存为一个新文件这样随时可以追溯。我习惯在输出文件名里加时间戳见源码。3. Python自动评分系统源码与关键逻辑拆解3.1 完整源码可直接复制运行下面是完整源码。运行环境是Python 3.8及以上需要安装两个库pandas和openpyxl。安装命令是pip install pandas openpyxl把脚本和原始评分表.xlsx放在同一个目录直接运行python score_system.py即可。# -*- coding: utf-8 -*- 自动评分系统 功能读取“原始评分表.xlsx”自动去掉最高最低分计算均分、排名、奖项生成最终成绩单 依赖pandas、openpyxl 运行python score_system.py import pandas as pd from datetime import datetime INPUT_FILE 原始评分表.xlsx OUTPUT_FILE 最终成绩单_{}.xlsx.format(datetime.now().strftime(%Y%m%d_%H%M%S)) SCORE_COLS [评委1, 评委2, 评委3, 评委4, 评委5] DECIMALS 2 # 奖项比例前10%一等奖接着20%二等奖再接着30%三等奖 AWARD_RATIO {一等奖: 0.1, 二等奖: 0.2, 三等奖: 0.3} def load_scores(path): # 如果表头不在第一行改成 header1并确认列名 df pd.read_excel(path, header0) for col in SCORE_COLS: if col not in df.columns: raise ValueError(找不到评委列请检查列名是否为{}实际列名{}.format(SCORE_COLS, list(df.columns))) df[col] pd.to_numeric(df[col], errorscoerce) return df def calc_avg(row): # 拿到该选手所有评委打分去掉 NaN scores row[SCORE_COLS].dropna() if len(scores) 0: return 0.0 # 有效分少于3个时不再去极值直接取平均 if len(scores) 3: return round(scores.mean(), DECIMALS) # 去掉一个最高分、一个最低分再取平均 trimmed scores.sort_values().iloc[1:-1] return round(trimmed.mean(), DECIMALS) def main(): df load_scores(INPUT_FILE) df[均分] df.apply(calc_avg, axis1) # 名次降序同分并列后序名次跳过 df[名次] df[均分].rank(ascendingFalse, methodmin).astype(int) # 按奖项比例分配 total len(df) df[奖项] rank_sorted df.sort_values(均分, ascendingFalse).reset_index(dropTrue) start 0 for name, ratio in AWARD_RATIO.items(): end start int(total * ratio) if end start: rank_sorted.loc[rank_sorted.index[start:end], 奖项] name start end result rank_sorted.sort_values(名次) result.to_excel(OUTPUT_FILE, indexFalse) print(计算完成共 {} 名选手结果已保存到{}.format(total, OUTPUT_FILE)) print(result[[选手编号, 选手姓名, 均分, 名次, 奖项]].head(10).to_string(indexFalse)) if __name__ __main__: main()如果你不希望用固定的“评委1”到“评委5”作为列名可以把SCORE_COLS改成你表里实际的列名。比如你的表头是“A评委、B评委、C评委”就改成SCORE_COLS [A评委, B评委, C评委]。代码会主动检查列名是否存在不存在会报错并把实际列名打印出来方便你对照。3.2 关键代码拆解读表、清洗、去极值load_scores函数是本系统第一道防线。pd.read_excel本身只负责把Excel内容读进来真正关键的是后面两件事检查列名、强制转数值。检查列名这块我见过很多情况是Excel里列名带了空格比如“评委1 ”尾部多一个空格代码直接取“评委1”就会报错。所以我用了一个if col not in df.columns判断一旦找不到立刻抛出异常避免后面算出一堆空值还不知道原因。这里多写两行能省下很多排查时间。pd.to_numeric(df[col], errorscoerce)是把某列转成数字转不了的置为NaN。这个API是关键它不会因为某个单元格是个“/”就中断程序而是把那个位置当成缺失值。这样处理的好处是一条脏数据不会拖垮整个统计流程顶多让某个选手的成绩需要人工复核。calc_avg函数是去极值的核心。它的逻辑是先把该选手的所有评委分数取出来去掉空值如果有效分少于3个就不再去极值直接平均如果有效分等于3个或以上就排序后取中间部分。这里有个细节scores.sort_values().iloc[1:-1]意思是去掉排序后的第一个元素和最后一个元素。如果刚好5个有效分取出来就是第2、3、4个分数等价于去掉最高最低。为什么有效分3个以下就不再砍因为3个分再去掉一个就只剩下一个分了那这个平均分过于敏感不如直接平均来得稳。3.3 关键代码拆解排名、奖项、导出排名用的是df[均分].rank(ascendingFalse, methodmin)。这个methodmin的意思是同分时取最小名次。比如两名选手都是9.50分他们都会显示第3名下一个人直接是第5名而不是第4名。这是比赛里最常见的并列排名方式和Excel的RANK函数行为一致。奖项分配这部分要先说清楚逻辑。总人数80人一等奖比例10%也就是8人二等奖20%16人三等奖30%24人。代码先把选手按均分从高到低排序然后从开头依次截取索引0到7给一等奖8到23给二等奖24到47给三等奖。这里的int(total * ratio)是为了把比例换算成具体人数。这里有一个实际中要考虑的点如果第8名和第9名同分第8名拿一等奖第9名拿二等奖从程序上看是“按名次截断”的必然结果但从比赛公平性上看可能需要评审组人工决议。这种情况代码处理不了也不该自动处理。我在末尾会专门讲这个问题。导出用的是result.to_excel(OUTPUT_FILE, indexFalse)。indexFalse很关键否则Excel里会多出一列行号看着很业余。输出文件名带了时间戳比如最终成绩单_20250607_153022.xlsx这样既能避免覆盖历史文件也能保留每次运行的记录。3.4 运行输出与验证正常跑一次终端会打印类似这样的结果计算完成共 80 名选手结果已保存到最终成绩单_20250607_153022.xlsx 选手编号 选手姓名 均分 名次 奖项 A01 参赛者01 9.52 1 一等奖 A02 参赛者02 9.48 2 一等奖 A03 参赛者03 9.45 3 一等奖 ...head(10)只打印前10行目的是快速确认排名情况。第一次跑完我建议先不要急着发给领导而是自己抽查几个人打开原始评分表手算一下某个选手的分数和生成的成绩单对比。确认无误后再对外公布。这是最稳妥的验证方式。4. 实操过程实录跑数据时踩过的三个大坑4.1 第一个坑有选手缺赛评委没有全部打分实测跑第一次我以为数据很干净结果排名出来后有一个选手的均分是0。查了半天发现那位选手缺赛只有两个评委给了分其余三个位置都是空白。理论上这不该算0分而是应该按“缺赛”单独标记。我当时的处理方式很简单就是在calc_avg里加了有效分数量判断如果有效分少于3个要么直接取平均要么返回0。这位选手只有2个有效分按照前面的规则应该取这两个分的平均而不是直接判0。但如果所有分数都为空那确实只能返回0并在结果表里由人工确认是否为缺赛。这个坑的价值在于代码不仅要处理“正常数据”更要处理“部分缺失数据”。如果当时没有这个判断统计结果就会悄悄出错。建议所有做类似统计的朋友在写处理函数时先问自己一个问题这一行的数据不完整时应该输出什么4.2 第二个坑表头有两行读进来全错位原始表为了好看第一行用了合并单元格写活动主题第二行才是真正的字段名。我用默认的pd.read_excel一读列名全变成了Unnamed: 0、Unnamed: 1代码当场报错“找不到评委列”。解决办法就是改用header1读取。但这里有一个易错点如果别人给你的表有时候是单行表头、有时候是双行表头代码就没法自动判断。我的建议是在代码里保留一个变量或者直接打印前几行数据确认表头在第几行再手动调整参数。后来我还发现有些Excel表格的列名不在第一行却在第二行带了一堆合并单元格的残留边框读进来会出现Unnamed列。遇到这种情况不要急先打印df.columns看实际列名是什么再决定怎么处理。4.3 第三个坑并列名次引发的争议有一轮测试两位选手均分都是9.50按照methodmin的规则两人并列第3名下一位直接从第5名开始。这个规则本身没错但发放奖项时出现了尴尬第3名有两个人但一等奖名额只有3个导致其中一人是一等奖另一人是二等奖。这种并列名次的问题在真实比赛里特别容易引发争议。程序自动算的只是“按规则截断”但奖项的最终归属通常需要评审组裁定。我的建议是比赛规则里提前写明同分情况如何处理比如“同分时比较去掉最高最低后的总分若仍相同则由评审组合议”。这样代码只能帮你快速算出大部分结果边界情况给人工留出决策空间。如果要加“小分破平”核心逻辑很简单在df里加一列“小分”也就是去掉最高最低后的剩余分数总和然后排序时用两列df[小分] df.apply( lambda row: row[SCORE_COLS].dropna().sort_values().iloc[1:-1].sum(), axis1 ) df df.sort_values([均分, 小分], ascending[False, False])注意一旦你采用这个规则名次就必须重新定义不能再用纯均分并列了。我在实际项目里更倾向保留“并列名次”因为这样透明度更高选手也更容易接受。5. 常见问题排查与避坑速查表5.1 常见问题速查表我整理了使用这个自动评分系统时最常遇到的几类问题做成速查表直接对着看。现象可能原因解决办法FileNotFoundError脚本和Excel不在同一目录把两个文件放同一目录或修改INPUT_FILE为完整路径列名出现Unnamed: 0表头行没选对把read_excel的header改成对应行号KeyError: 评委1列名不一致可能带空格或名称不同打印df.columns修改SCORE_COLS某选手均分变成0该行所有分数不是数字或全为空打开原表看是否有文本、空值或“/”去不掉最高最低分分数列还是文本类型检查to_numeric是否生效先转数值名次出现跳号但同分这可能是正确行为确认规则是并列名次跳过导出Excel后打开提示格式异常Excel文件版本冲突用.xlsx后缀安装openpyxl打印中文乱码系统终端编码问题在IDE里运行或调整终端编码为UTF-85.2 两个独家小技巧技巧一先造10行假数据试跑。不要一上来就用80人的真实数据。我会先复制一份表只留10行给一些故意造出来的异常值比如空值、文本分数、同分情况跑一遍看结果是否符合预期。这样能把逻辑问题全部暴露在小样本里而不是等大表跑完才发现名次算错。技巧二输出文件永远不覆盖原表。我在代码里加了时间戳每次运行都会生成新文件。这样每次跑出来的结果都留底一旦领导问“上次的名次和这次怎么不一样”你可以直接对比两次输出文件找到差异原因。否则文件被覆盖了想复盘都无从下手。6. 这套系统还能扩展什么我的经验与建议6.1 扩展方向加权评分、异常评委检测、多维评分如果比赛规则更复杂这套系统也可以继续扩展。比如评委权重不同资深评委的分数权重高一些普通评委权重低一些那calc_avg里的均值就要改成加权平均。再比如评委打分有系统性偏差某个评委给所有人分数都比别人低你可以计算每个评委的平均给分和整体平均比较偏差过大的评委数据单独标注。还有一个在技能比赛里很常见的需求是多维评分比如技术分占50%、表现分占30%、创意分占20%。这时候不能直接对总分去极值而是先对每个维度分别去极值、分别算均分再做加权求和。代码核心还是复用calc_avg只是要循环处理多个维度列最后合成一个最终得分。从实现上看不会比单维度复杂太多。6.2 我在实际使用中的体会这套自动评分系统本身技术含量说不上高但它解决了一个很实际的问题把重复、易错、耗时的人工统计变成一次可验证的自动化操作。从我个人的经验来看这类办公脚本最大的价值不在于代码多漂亮而在于三点输入数据是否规范、输出结果是否能被验证、边界情况是否处理清楚。最后分享一个小技巧如果你要把脚本交给不懂代码的同事用可以在主程序前面加一句input让他启动后手动输入文件名或者直接把程序打包成可执行文件双击就能运行。这样这套工具才算是真正落地而不是只存在于你自己的电脑里。我用这个方式把统计脚本交给过好几位同事他们反馈最频繁的一句话是早知道有这个上次就不用加班了。
阅读完成 · 觉得有帮助？
