1、利用 Excel 进行学生成绩管理青海卫生职业技术学院现代教育技术中心 董建伟摘要:利用 Excel 的函数功能,通过日常工作中的几个例子,使老师迅速完成对学生成绩管理的各项分析统计工作。关键词:Excel、函数公式、学生成绩在教师的日常工作中,对学生的成绩进行统计分析管理是一项非常重要也是十分麻烦的工作,如果能够利用 Excel 强大的数据处理功能,就可以使各位教师迅速完成对学生成绩的各项分析统计工作。最近笔者利用 EXCEL 轻松地对学生成绩进行了统计,效果非常好。下面就一起来看看具体操作吧!首先启动 Excel 电子表格软件,新建一个 “空白工作簿”随后在默认工作表中制作一个表格,其中包
2、括:学号、姓名、成绩、分数等级、名次等项并相应填好(如图 1) 。1、 转换学生成绩分数等级有时我们会遇到要将学生的考试成绩按实际考试分数转换成相应的分数等级的情况,例如将考试成绩在 90 分以上的成绩转换成“A+”形式,8589 分的成绩转换成“A”形式 。一般情况,在 EXCEL 表格中大家会采用 IF()函数来设计公式进行转换,这样所设计的公式会变得很复杂,如果进行转换的成绩等级类型超过 IF()函数的最大嵌套 7 层时,IF()函数就无能为力了。这时我们可以用如下的方法来简化操作。1、 按如图 1 格式内容建立好管理学生成绩表。2、 在 H3 到 J13 单元格录入考试成绩分数段与考试
3、成绩分数等级对照表。3、 在 E4 单元格录入公式“=INDEX(J$4:J$13,MATCH(1,(D4=H$4:H$13)*(D4=I$4:I$13),0)”,由于该公式为数组公式,在录入完上述内容后,必须同时按下“CtrlShiftEnter”键,为上述公式内容加上数组公式标志即大括号“ ”。该公式的作用就是,根据 D4 单元格中的学生成绩,在 E4 单元格自动将该成绩转换成相应的成绩分数等级。4、 将光标移动到 E4 单元格,向下拖动填充柄至 E20 单元格,将公式进行快速复制,这样就可以迅速完成转换学生成绩分数等级的工作(效果如图 2) 。5、 也可按照自己的喜好,将 H3 到 J1
4、3 单元格区域设置为“隐藏”,使工作表更加美观。2、 统计学生考试成绩分布情况在利用 EXCEL 管理学生考试成绩时,经常要统计各分数段学生考试成绩的分布情况,如果采用下面这种方法,就能使这项工作变得非常方便。1、 打开管理学生成绩表(本例仍采用上例的工作表,格式内容如图 1) 。2、 在 J2 到 J5 单元格录入学生考试成绩的统计分段点。在本例中采用的统计分段点为:59、69、79、89,即统计 60 分以下、60-69、70-79 、80-89 、90 分以上五个学生考试成绩区段的人数分布情况,当然你也可以根据自己的实际需要在此进行不同的设置。3、 选中要进行公式设计的单元格区域 I6
5、到 I10,按下 F2 键,录入公式“=FREQUENCY(D4:D20,J2:J5)”,由于该公式为数组公式,在录入完上述内容后,必须同时按下“CtrlShiftEnter”键,为上述公式内容加上数组公式标志即大括号“ ”。4、 当上述操作完成后,在单元格区域 I6 到 I10 就迅速得到了正确的学生考试成绩分布情况(如图 3) 。5、 要注意的是,在我们设计统计区段时,这个统计区段必须比统计分段点的数据个数多一个。这个多出来的统计区段表示超出最高间隔的数值个数。例如,在本例中,我们设计的统计分段点为59、69、79、89 四个数值,这时要想取得正确的统计区段分布数据,就必须在单元格区域 I
6、6 到 I10 五个单元格中输入 FREQUENCY()函数计算的结果,多出来的这一个单元格将返回学生成绩表中大于 90 分的成绩的人数。3、 学生成绩名次排名1、 工作表按如图 1 内容格式建好后,首先用鼠标选中“名次”一列中的第一个学生所在的单元格(图中为 F4) 。2、 在此单元格中输入公式“=RANK(D4,$D$4:$D$20,0)”,公式中的 D4 表示工作表中的第一位学生成绩,$D$4:$D$20 表示比较第一位学生到最后一位学生的成绩。3、 随后用鼠标选中 F4 单元格向下拖动填充柄至 F20 单元格,将公式进行快速复制,这样就可以迅速完成学生成绩名次排名的工作(如图 4) 。
7、4、 当前显示的学生名次,只是 Excel 对当前所输入学生的成绩进行比较之后的名次,该名次会随着输入成绩的不同而发生变化,只有我们将全部学生成绩输入后所得到的名次才是最后的名次。4、 用“条件格式” 给学生分等级通过以上操作我们已可以轻松地对全班学生进行成绩管理,除了我们可以将学生的考试成绩按实际考试分数转换成相应的分数等级外,还可以利用 Excel 中的“条件格式” 直观的查看学生成绩的等级情况。例如,我们将全部考试成绩等级定为:优、良、合格、不及格四个等级,即:90100分之间是优;75-89 分之间是良; 60-74 分之间是合格;60 分以下是不及格。下面我们分别以不同的颜色来显示各
8、个等级,例如,优等级显示为:红色;良等级显示为:蓝色、字体倾斜;合格等级显示为:黄色;不及格等级显示为:默认的白色。这样看起来就一目了然。1、 首先用鼠标选中“成绩” 一列(D4 :D20,具体区域应根据实际情况而定) ,执行“格式条件格式”命令,打开“ 条件格式” 对话框。2、 在“条件 1”项中将第一个选项设置为“ 单元格数值”、在后面的项设置为“ 介于”选项,并对后面的两个选项进行赋值,在此我们输入 100 和 90 即可。然后单击“格式”按钮,随后弹出一个“单元格格式”对话框,我们就按刚才的设定对字体的字形、颜色、图案背景等进行设置。随后单击“确定”即可返回上一级界面。这时我们看到第一
9、个条件已经设置好, “条件 1”设置好后单击下面的“ 添加”按钮,接着在下面弹出的“ 条件 2”对话框按上面的方法进行设置。 (如图 5)60 分以下为白色,我们不用进行设置。设置好后单击“确定”,我们看到该工作表中的“成绩”一列单元格区域已经变成彩色的了。 (如图6)你也可以再次选中“成绩”一列,执行“数据排序”命令进行降序排列,这样就更清楚地了解各个等级情况。5、 避免文本数据重复输入我们在管理学生成绩表中输入诸如学号、身份证号等文本数据时,为了防止输入重复的号码,我们采取了如下的预防措施。1、 同时选中如图 1 的“学号”一列(本例中是 B4:B20) ,执行“数据有效性”命令,打开“数
10、据有效性” 对话框窗口(如图 7) 。2、 在“设置”标签按钮中,单击“ 允许”右侧的下拉按钮,在随后弹出的下拉列表框中,选“自定义” 选项,然后在下面的“ 公式”方框中输入公式:“=COUNTIF(B:B,B4)=1”。3、 切换到“出错警告” 标签按钮下,设置好相关的提示信息(如图 8) ,确定退出。4、 以后,如果输入的学号出现了重复,系统会按设置的信息提示操作者进行检查(如图 9) ,并要求重新输入。6、 统计全班学生的总分、平均分我们为成绩表列出总分以及平均分是以便于很好的掌握全班学生的学习情况,在 Excel中可以轻松地计算出每次考试的总分和平均分。在求总分时,首先用鼠标选中放置总
11、分结果的单元格 G3,执行“插入函数”命令,再弹出的“粘贴函数”对话框中选择求和函数“SUM”,确定后在随后弹出的对话框的输入栏中输入单元格区域(本例中为 D4:D20),确定后就可看到在 G3 单元格出现全班学生成绩总分。在求平均分时,我们用鼠标选中放置平均分后面的 H3 单元格,随后在 Excel 的编辑栏的输入框中输入公式“=AVERAGE(D4:D20)” 后回车,这样我们就可以看到 H3 单元格会出现 D4:D20 中所有学生成绩平均分(如图 10) 。通过以上的操作我们就可以轻松地来管理学生的每次考试成绩情况了,其实利用 Excel的这些功能再加以灵活运用还可以管理学生一学期或一学年的成绩情况,如果你有兴趣就动手试试吧。参考文献:1 计算机信息技术基础许骏 主编,3 版,北京:科学出版社,20012 计算机应用基础 (IV)/计算机应用基础编写组编,北京:中央广播电视大学出版社,2000.1