Excel函数在学生成绩数据处理中的应用

摘要：针对高校课程考核方式的变化而带来成绩处理复杂化的问题，利用Excel相关函数可大大提高工作效率和数据处理的准确性。以青岛滨海学院期末总评成绩生成及统计分析为例，使用Excel中的相关函数对成绩表进行数据处理，并使用图表和自定义图表来创建成绩直方图，实践应用证明，该方法有效提高了工作效率。

关键词：Excel；函数；期末成绩；试卷分析；直方图

中图分类号：TP3 文献标识码：A 文章编号：1009-3044（2013）26-5920-04

1 概述

随着高校内涵建设的不断推进，作为课程改革重要一环的课程考核方式在不少学校发生了较大变化，以青岛滨海学院为例，课程期末考试成绩不再只是以往的试卷分数，而是包含了课堂出勤及表现成绩、作业成绩、单元测验成绩、期中考试成绩等多个组成内容，并且根据最终折合成绩进行等级划分，还要对相关数据进行统计和分析。这给绝大多数任课教师带来了不小的困难，如何准确快速的实现相关数据的生成、统计和分析是教师们迫切想解决的问题。利用Excel相关函数可实现上述相应功能，大大提高了工作效率，具有较好的推广价值。

2 相关数据的构成及目前数据生成存在的问题

根据青岛滨海学院教务处《关于对卷二成绩管理办法的指导性意见》（青滨教字[2008]35号）文件精神：学校课程考核一般均采用“一试两卷”的考核方式，卷一考核由教务处组织，利用题库统一命题；卷二由课程组任课教师命题，卷二考核形式可根据课程的特点，采用灵活多样的形式，卷二成绩由任课教师根据学生的平时成绩（含听课考勤、章节测验、作业、实验等）、期中考试、期末考试按一定比例综合评定，具体等级由任课教师确定，但各项成绩的评定须有充分的依据及相关辅证材料，成绩分布应符合学院的指导性意见规定的等级分布。

此外，卷二须将百分制折合为等级制，学绩点设定为5个等级（A、B、C、D、E）和4个修正等级（A+ 、B+ 、C+ 、D+），各等级所对应的学绩点和成绩等级分布的指导性意见如表1所示。

根据上述要求，期末成绩数据表中需生成的数据有期末总评成绩、成绩等级、等级比例。此外，在成绩分析时还要生成能够直观反映成绩分布的直方图。

截止到目前，相关数据是通过下述的方法生成的，工作量大并且容易出错。

2.1 总评成绩

由于总评成绩的构成要素较多，在计算时，往往会根据各构成要素所占的比例不同重新修正总评成绩公式，然后进行填充，甚至一些老教师通过计算器手工计算，费时费力。

2.2 综合评定等级

在计算成绩等级时，多数人会在总评成绩的基础上降序排序，然后根据各等级的比例手工计算每个等级的人数，给出成绩等级，最后再复制到成绩表中，同样效率低下。

2.3 综合评定等级的比例及人数

总评成绩及等级确定后，还要统计各等级的人数及所占比例并将统计数据放到在表的下方相应的单元格中，如10.3%/10。目前，绝大多数教师使用计算器计算出百分比，然后手工输入。

2.4 成绩数据直方图

为了直观的反映成绩数据，我们在对试卷进行统计分析时通常会通过直方图来表示，目前任课教师制作直方图的方法是手工绘制，不仅不准确，也不美观。

为了改进传统的成绩表制作方法，该文拟使用Excel函数对成绩数据进行处理，制作成模板。任课教师只需要把基本数据输入，综合成绩、成绩等级、各等级所占的百分比及人数，试卷的各分数段人数及直方图等数据均能够自动生成，大大提高了准确率和工作效率。

3 利用EXCEL函数生成成绩表数据

3.1 总评成绩的生成

成绩表的表头中包含总评成绩的组成要素及所占的比例，如图1所示，可以使用函数从中提取各组成项所占的百分比，就可以自动计算总评成绩了，具体实现方法如下。

使用RIGHT（）函数提取出成绩所占的百分比数据（注意：此次在单元格地址引用时应使用绝对引用），如通过RIGHT（$D$4，3）就可以取出“作业情况占10%”中的“10%”，利用此数据与学生该项成绩相乘即可计算出此项占学生总评成绩的比重，使用同样的方法可以提取其他成绩组成项的比重成绩，最后求和即可。需要说明的是，如果存在缺考的学生，我们则需要使用IF（）函数来处理，即：如果是缺考，则总评成绩显示为“缺考”，否则计算出学生的总评成绩。通过上述分析可以使用公式“=IF（H5="缺考"，"缺考"，D5*RIGHT（$D$4，3）+E5*RIGHT（$E$4，3）+F5*RIGHT（$F$4，3）+G5*RIGHT（$G$3，3）+H5*RIGHT（$H$3，3））”计算出总评成绩。

3.2 综合评定等级的生成

由于综合评定等级中不同等级的比例基本固定，所以综合评定等级的确定不是以总评成绩的分值界定的，而是依据总评成绩名次和相关等级的比例确定。

在具体实现时，为了方便操作，可以先对若干个成绩区域进行定义名称。本例中把A5：J47单元格区域定义为“综合成绩”，如图2所示；把H5：H47单元格区域定义为“卷二卷面成绩”；把I5：I47单元格区域定义为“总评”；把J5：J47单元格区域定义为“等级”。

然后，使用RANK（）函数计算总评成绩的名次，再对求出的名次按百分比计算出个数，最后，使用IF（）函数对相应的数目确定等级。如IF（RANK（I5，总评）

3.3 综合评定等级的比例及人数的生成

总评成绩及等级确定后，在统计各等级的人数及所占比例时要用到多个函数。首先使用LEFT（）函数提取相应单元格内容的前两个（或一个）字符，如“A+”或“A”，然后使用COUNTIF（）函数统计出等级数据中相应等级的人数并除以参考人数得到相应等级的比例（小数形式），接下来需要使用TEXT（）函数把求出的比例转换成百分比，最后使用字符串连接符&把与该比例的人数进行字符串连接，如图3所示。

考虑到某等级人数为零的情况下相应的单元格显示为空白，可使用IF（）函数进行判断。具体实现公式为：“=IF（OR（$G$48=0，$G$48=""），""，TEXT（COUNTIF（等级，LEFT（A49，2））/$G$48，"0.0%"）&"/"&COUNTIF（等级，LEFT（A49，2）））”。

3.4 成绩数据直方图的生成

在试卷统计分析中，通过软件生成的直方图能够比较直观的反映成绩分布情况。我们使用Excel的COUNTIF（）函数跨工作表引用“期末考试卷面成绩”来统计各分数段的人数，公式为：=COUNTIF（卷二卷面成绩，">=90"），=COUNTIF（卷二卷面成绩，">=80"）-COUNTIF（卷二卷面成绩，">=90"），=COUNTIF（卷二卷面成绩，"

根据统计出各分数段的人数，制作直方图：先选中数据区域，如B8：B14，如图4所示，然后插入簇状柱形图图表，在图表向导步骤之3中设置“标题”选项卡中“图表标题”处输入“成绩直方图”，在“当值（Y）轴”处输入“人数”，在“坐标轴” 选项卡中“主坐标轴”中取消“分类（X）轴”，在“图例”选项卡中取消“显示图例”的勾选，（创建出的图表只有纵坐标，横坐标没有刻度，并且默认的图表系列柱之间有一定距离）；为了更好的表示出直方图，要对图表各项进行修改，右单击图表系列，使用“数据系列格式”对话框中的“选项”选项卡中“分类间距”处设置为“0”，在“图案”选项卡中右侧“内部”处选择“无”；右击绘图区，设置“区域”的颜色为“无”；右击“网格线”设置刻度及图案，随后插入一个文本框，在其中输入“100 90 80 70 60 40 0”，并把文本框拖动到横坐标下面合适的位置，根据柱面的宽度设置数据间合适的间隔，接下来要修改图表类型，使用自定义类型，选中“自定义”，在“示例”中显示设置好的图表，单击“确定”，图表就设置好了，如图5所示。

最后，再使用“工具”菜单中的“选项”命令，在“视图”选项卡取消“网格线”的显示，这样在试卷分析工作表中就只显示设置好的内容了，如图6所示。

4 结束语

使用Excel函数处理成绩表数据，解决了原来大部分由人工计算繁琐而容易出错的问题，大大提高了处理数据的效率。设计完成的相关文件可以保存为模板，用户修改起来也非常方便。此外，为防止入门用户在试用时错误修改相关公式，可以将相关单元格进行保护设置。

参考文献：

[1] 闫建华，刘宇，杨春慧，孙连三.Excel高效办公——数据处理与分析[M].人民邮电出版社，2006.7.

[2] 李泽江，付子霞.Excel在信息管理中的应用[M].中国水利水电出版社，2008.3.