利用ｅｘｃｅｌ管理学生成绩
点击浏览该文件

摘要：利用Excel的函数功能，通过日常工作中的几个例子，使老师迅速完成对学生成绩管理的各项分析统计工作。
关键词：Excel、函数公式、学生成绩

在教师的日常工作中，对学生的成绩进行统计分析管理是一项非常重要也是十分麻烦的工作，如果能够利用Excel强大的数据处理功能，就可以使各位教师迅速完成对学生成绩的各项分析统计工作。最近笔者利用EXCEL轻松地对学生成绩进行了统计，效果非常好。下面就一起来看看具体操作吧！

 
首先启动Excel电子表格软件，新建一个“空白工作簿”随后在默认工作表中制作一个表格，其中包括：学号、姓名、成绩、分数等级、名次等项并相应填好（如图1）。
1、 转换学生成绩分数等级
有时我们会遇到要将学生的考试成绩按实际考试分数转换成相应的分数等级的情况，例如将考试成绩在90分以上的成绩转换成“A+”形式，85－89分的成绩转换成“A”形式……。一般情况，在EXCEL表格中大家会采用IF（）函数来设计公式进行转换，这样所设计的公式会变得很复杂，如果进行转换的成绩等级类型超过IF（）函数的最大嵌套7层时，IF（）函数就无能为力了。这时我们可以用如下的方法来简化操作。
按如图1格式内容建立好管理学生成绩表。
在H3到J13单元格录入考试成绩分数段与考试成绩分数等级对照表。

在E4单元格录入公式“=INDEX(J$4:J$13,MATCH(1,(D4>=H$4:H$13)*(D4<=I$4:I$13),0))”，由于该公式为数组公式，在录入完上述内容后，必须同时按下“Ctrl＋Shift＋Enter”键，为上述公式内容加上数组公式标志即大括号“｛｝”。该公式的作用就是，根据D4单元格中的学生成绩，在E4单元格自动将该成绩转换成相应的成绩分数等级。

 
将光标移动到E4单元格，向下拖动填充柄至E20单元格，将公式进行快速复制，这样就可以迅速完成转换学生成绩分数等级的工作（效果如图2）。
也可按照自己的喜好，将H3到J13单元格区域设置为“隐藏”，使工作表更加美观。
2、 统计学生考试成绩分布情况
在利用EXCEL管理学生考试成绩时，经常要统计各分数段学生考试成绩的分布情况，如果采用下面这种方法，就能使这项工作变得非常方便。
1、 打开管理学生成绩表（本例仍采用上例的工作表，格式内容如图1）。
2、 在J2到J5单元格录入学生考试成绩的统计分段点。在本例中采用的统计分段点为：59、69、79、89，即统计60分以下、60-69、70-79、80-89、90分以上五个学生考试成绩区段的人数分布情况，当然你也可以根据自己的实际需要在此进行不同的设置。
3、 选中要进行公式设计的单元格区域I6到I10，按下F2键，录入公式“=FREQUENCY(D4:D20,J2:J5)”，由于该公式为数组公式，在录入完上述内容后，必须同时按下“Ctrl＋Shift＋Enter”键，为上述公式内容加上数组公式标志即大括号“｛｝”。
4、 当上述操作完成后，在单元格区域I6到I10就迅速得到了正确的学生考试成绩分布情况（如图3）。

5、 要注意的是，在我们设计统计区段时，这个统计区段必须比统计分段点的数据个数多一个。这个多出来的统计区段表示超出最高间隔的数值个数。例如，在本例中，我们设计的统计分段点为59、69、79、89四个数值，这时要想取得正确的统计区段分布数据，就必须在单元格区域I6到I10五个单元格中输入FREQUENCY（）函数计算的结果，多出来的这一个单元格将返回学生成绩表中大于90分的成绩的人数。
3、 学生成绩名次排名
1、 工作表按如图1内容格式建好后，首先用鼠标选中“名次”一列中的第一个学生所在的单元格（图中为F4）。
2、 在此单元格中输入公式“=RANK(D4,$D$4:$D$20,0)”,公式中的D4表示工作表中的第一位学生成绩，$D$4:$D$20表示比较第一位学生到最后一位学生的成绩。
3、 随后用鼠标选中F4单元格向下拖动填充柄至F20单元格，将公式进行快速复制，这样就可以迅速完成学生成绩名次排名的工作（如图4）。

4、 当前显示的学生名次，只是Excel对当前所输入学生的成绩进行比较之后的名次，该名次会随着输入成绩的不同而发生变化，只有我们将全部学生成绩输入后所得到的名次才是最后的名次。
4、 用“条件格式”给学生分等级
通过以上操作我们已可以轻松地对全班学生进行成绩管理，除了我们可以将学生的考试成绩按实际考试分数转换成相应的分数等级外，还可以利用Excel中的“条件格式”直观的查看学生成绩的等级情况。
例如，我们将全部考试成绩等级定为：优、良、合格、不及格四个等级，即：90－100分之间是优；75-89分之间是良；60-74分之间是合格；60分以下是不及格。下面我们分别以不同的颜色来显示各个等级，例如，优等级显示为：红色；良等级显示为：蓝色、字体倾斜；合格等级显示为：黄色；不及格等级显示为：默认的白色。这样看起来就一目了然。
首先用鼠标选中“成绩”一列（D4：D20，具体区域应根据实际情况而定），执行“格式－条件格式”命令，打开“条件格式”对话框。

 
在“条件1”项中将第一个选项设置为“单元格数值”、在后面的项设置为“介于”选项，并对后面的两个选项进行赋值，在此我们输入100和90即可。然后单击“格式”按钮，随后弹出一个“单元格格式”对话框，我们就按刚才的设定对字体的字形、颜色、图案背景等进行设置。随后单击“确定”即可返回上一级界面。这时我们看到第一个条件已经设置好，“条件1”设置好后单击下面的“添加”按钮，接着在下面弹出的“条件2”对话框按上面的方法进行设置……。（如图5）60分以下为白色，我们不用进行设置。设置好后单击“确定”，我们看到该工作表中的“成绩”一列单元格区域已经变成彩色的了。（如图6）你也可以再次选中“成绩”一列，执行“数据－排序”命令进行降序排列，这样就更清楚地了解各个等级情况。

5、 避免文本数据重复输入
我们在管理学生成绩表中输入诸如学号、身份证号等文本数据时，为了防止输入重复的号码，我们采取了如下的预防措施。
1、 同时选中如图1的“学号”一列（本例中是B4：B20），执行“数据－有效性”命令，打开“数据有效性”对话框窗口（如图7）。
2、 在“设置”标签按钮中，单击“允许”右侧的下拉按钮，在随后弹出的下拉列表框中，选“自定义”选项，然后在下面的“公式”方框中输入公式：“=COUNTIF(B：B,B4)=1”。
3、 
 

 
切换到“出错警告”标签按钮下，设置好相关的提示信息（如图8），确定退出。
4、 
 
以后，如果输入的学号出现了重复，系统会按设置的信息提示操作者进行检查（如图9），并要求重新输入。
6、 统计全班学生的总分、平均分

 
我们为成绩表列出总分以及平均分是以便于很好的掌握全班学生的学习情况，在Excel中可以轻松地计算出每次考试的总分和平均分。在求总分时，首先用鼠标选中放置总分结果的单元格G3，执行“插入－函数”命令，再弹出的“粘贴函数”对话框中选择求和函数“SUM”,确定后在随后弹出的对话框的输入栏中输入单元格区域（本例中为D4：D20）,确定后就可看到在G3单元格出现全班学生成绩总分。在求平均分时，我们用鼠标选中放置平均分后面的H3单元格，随后在Excel的编辑栏的输入框中输入公式“=AVERAGE(D4：D20)”后回车，这样我们就可以看到H3单元格会出现D4：D20中所有学生成绩平均分（如图10）。
通过以上的操作我们就可以轻松地来管理学生的每次考试成绩情况了，其实利用Excel的这些功能再加以灵活运用还可以管理学生一学期或一学年的成绩情况，如果你有兴趣就动手试试吧。

参考文献：
〔1〕《计算机信息技术基础》许骏 主编，－3版，－北京：科学出版社，2001
〔2〕《计算机应用基础》（IV）/《计算机应用基础》编写组编，－北京：中央广播电视大学出版社，2000.1