巧用Excel函数创建学期末总评成绩模板

李晓云

摘要： Excel电子表格软件以其强大的函数和数据分析工具以及VBA程序等功能，广泛应用于信息应用的各个领域中，该文运用Excel数组、函数等功能，创建了一个学期末总评成绩处理模板，大大提高了任课教师学期末处理成绩的效率，具有很好的实用价值。

关键词：成绩处理;Excel函数、数组;Excel模板

中图分类号：G642        文献标识码：A

文章编号：1009-3044（2020）25-0128-02

学期末任课教师所带课程的成绩处理是一项重要的基础工作，特别是目前高职院校，基本上为过程性评价，评价项目多，如果不使用ExceL函数的高级处理功能，工作量将会非常烦琐。就我校目前的情况，有许多课程是合班上课，班级人数众多，而且目前仍有一些任课教师在信息化应用技术上水平参差不齐，出现 “手工”计算成绩分析数据的情况，工作效率低，且容易出错。

Excel强大的数组、函数功能及灵活的数据调用方式可以轻松地进行各类数据的统计与逻辑处理，本文创建了一个自动化处理期末成绩的模板，任课教师只需要输入班级名称、课程名称及各评分项目成绩的基础数据，成绩单及所有评价指标数据自动生成，简单易用，在本校中推广应用良好，大大提高了任課教师处理成绩的工作效率。

1录入班级初始成绩表

本过程的主要目的是以最简单的形式组织综合评价中各项目的基础分数据，表格结构尽量简单，数据的正确性方面进行功能纠错。此表用于教师输入班级每个同学的各项评分数值。

（1）建立初始成绩表结构

创建一个班级工作簿文件，将sheet1工作表命名为“初始成绩X1班”，数据表结构有“学号、姓名、平时、期中、期末”五个字段，评价项目可根据课程评价标准自行调整。本模板的评价体系为：平时成绩30分，期中100分，期末100分，总评=平时+期中*30%+期末*40%。

（2）设置数据有效性并输入成绩

该工作表要录入每个同学的初始成绩数据，工作量较大， 为了保证数据在输入过程中逻辑上的正确性，对成绩数据区域进行 “有效性”的纠错设置，类型为“数值型”，范围为0至100，当超出范围时给予出错提示。基于本校教师任课情况，复制三张工作表，分别重命名为各班级名称。

2创建基础信息及统计数据表

将sheet2工作表命名为“基础信息及统计数据”，此表结构包括班级基本信息和分数段等统计信息，前五项基础信息项由任课教师根据各班级信息手工输入，后面的统计数据项由函数计算取得。

（1）计算期末成绩的“最高分、最低分，平均分”

分别运用MAN、MIN和AVERAGE函数求出“期末”成绩的最高分、最低分及平均分，函数示例：“=MAX（初始成绩X1班！E：E）”“=MIN（初始成绩X1班！E：E）”“=ROUND（AVERAGE（初始成绩X1班！E：E），2）”，使用拖充柄将公式复制到其他行，并对应修改公式中的班级名称。

（2）计算期末成绩的各“分数段”数据

“分数段”人数统计，本文使用了FREQUENCY数组函数功能。按期末成绩划分为5个分数段：“90分以上”“80～89分”“70～79分”“60～69分”和 “60分以下”，将分段点分别设置为99、89、79、69和59，图1中B10：C15区域，再运用FREQUENCY函数分别对各班级期末成绩按分段点返回频率分布值，即为各分数段的人数，函数示例：“{=FREQUENCY（初始成绩X1班！E：E，C14：C18）}”，注意EXCEL数组转换要使用特定的组合键“SHIFT+CTRL+ENTER”，如图1中D11：G15区域。因 FREQUENCY函数只能进行列向求频率值[1]，所以最后还要将这组数据引用到图2中J2：N5的横向数据清单对应区域。

3制作期末总评成绩模板

“期末总评成绩”表是每个教师学期末要上交存档的学生总评成绩单，主要包括三个组成部分，表头信息区，成绩评分区、总评成绩分析区，表结构如图2。

3.1 表头和成绩区数据处理

（1）制作期末总评成绩表结构调入初始数据

插入新工作表重命名为“期末总评成绩X1班”，制作学校统一的班级期末总成绩表格。

以“=”号开头公式引用的方式，将表头中的学年、班级、课程名称等标识信息从“参数与分析”表中依次调入;同样将“平时、期中、期末”三项原始数据从对应班级初始成绩工作表中调用过来，调用公式为“=IF（初始成绩X1班！B2=""，""，初始成绩X1班！B2）”，将公式拖充复制到左中两边的所有数据行，这种调用一旦原始数据发生改动，调用表中数据会自动更新保持一致。“考试/查”选项可以使用IF函数根据考试性质参数值返回“ü”信息，函数公式：“=IF（参数及统计！D2="考试"，"√"，""）”。

（2）计算期中、期末折合成绩和总评成绩

根据规定的折合比例，在“期中折合”和“期末折合”的第一个数据行中分别输入“=IF（C6=""，""，ROUND（C6*30%，0））”和“=IF（E6=""，""，ROUND（E6*40%，0））”函数公式，复制到其数据行;“总评”成绩为“平时+期中折合+期末折合”，但要判断如果期中未考，则总评成绩按“平时+期末*70%”计算，如果期末未考，则总评成绩为0，使用两级IF嵌套函数“=IF（A6<>""，IF（AND（C6=""，E6<>""），ROUND（B6+E6*70%，0），IF（E6=""，""，B6+D6+F6）），""）”返回最后结果，拖动拖充柄复制到其他数据行，完成成绩计算。

（3）进行缺考标识和不及格标识

根据总评成绩数据值用IF函数判断，总评无成绩者为“缺考”，在“备注”栏输入公式“=IF（AND（G6=""，A6<>""），"缺考"，""）”，复制到其他数据行，标识出所有缺考的学生行。

成绩表中需要将总评成绩不及格的数据做出突出标点，以便观察，具体步骤是：（1）选中工作表“总评”数据区域;（2）“开始”菜单找到“条件格式”单击;（3）选中“突出显示单元格规则小于” 图标，对话框中输入60并设置“文本红色”，单击“确认”按钮。

3.2 总评成绩分析区域数据处理

（1）班级平均分计算

班级平均分的数据源是本表左右两栏中折算出来的G列和O列中的总评成绩，在分析区域中J32单元格中输入“=ROUND（AVERAGE（G6：G34，O6：O25），2）”， ROUND函数对数值四舍五入，保留2小数位数。

（2） “分数段”人数统计

各“分数段”人数的统计仍使用上述所说的FRFREQUENCY数组函数，但注意成绩数据是本表的“总评”成绩列，“分段点”参数值在“参数及分析”工作表中提取，在图3中选定J27：J31单元格区域， 输入公式“=FREQUENCY （高职2013级某班XX课成绩表！ F：F，G2：G6）”，然后按“Ctrl+Shift+Enter”转换为数组的形式，即可相应单元格直接取得各分类段的人数结果。

（3）各“分数段”人数所占比例统计

统计学中单项占全体的结构比较分析法，便于总体上表明全班考试成绩分布的基本情况，使用刚得出的各分数段的人数与总人数相除取得，如“90分以上”的人数比例值在图3L27单元格中输入 “=ROUND（J27/SUM（$J$27：$J$31），3）*100”后回车，再将公式拖动填充其他人数比例计算单元格即可。

（4）参加考试人数和缺考人数统计

本文使用公式“="期末实际参加考核（"&SUM（J27：J31）&" ）人""计算取得;“缺考”人数也可以有多种计算方法，本文使用COUNTIF函数直接从本表“备注”栏中的缺考信息计数取得，图3的I34单元中输入“="期末缺考（"&COUNTIF（H6：H34，"缺考"）+COUNTIF（P6：P25，"缺考"）&"）人"”。直接从本表中引用单元格进行统计计算，虽然函数复杂，但好处是后面复制制作其他班级成绩表时，这些公式不需要修改参数。

至些，数据计算完成，期末总评表自动生成，复制三张，用同样方法将对应班级的基础信息和原始成绩调用过来，其他项目不需要改动，数据自动更新，这样多个班级的数据全部处理完成。

4数据的保护

本模板使用了大量公式和函数计算，之间的调用关系较复杂，为了以防任课老师对函数公式的误修改，造成数据错误，要启用Excel的保护功能[2]。方案是：四张“总评成绩”表通过 “审阅”菜单下的“保护工作表”设置默认参数保护，不允许任何操作; 而“参数及分析”工作表进行部分数据保护，即允许班级基本信息数据区域可以输入新的内容，其他项目不可动，工作表保护前注意需要将这些区域的“单元格格式设置”中的“锁定”取消。另外可将此文档保存为Excel模板文件，使用时直接新建调用该模板即可。

5结束语

本模板充分考虑到任课教师一个学期所带班级多，所教授课数量多的情况，使用一个Excel文件即可进行多个班级不同课程的成绩处理，改变了原版中一个文件只能处理一个班级成绩的情况，本模板只需要教师输入各班级最初始的成绩及基础数据，期末总评成績表就会全部自动生成。该模板在本校的成绩处理工作中已广泛成功推广，解决了烦琐的重复数据处理问题，大大提高了教师学期成绩分析统计工作的效率。

参考文献：

[1]李盛兰，吴庆祥.学生成绩管理中Excel函数的应用技术研究[J].数字技术与应用，2017（5）：239.

[2]金龙海，姜楠.Excel函数在统计学期成绩中的应用[J].中国新通信，2017，19（20）：133-134.

【通联编辑：代影】