用Excel函数快速处理学生成绩

2013-11-30王美琼

山东工业技术 2013年12期

王美琼

（常州工程职业技术学院，江苏常州 213164）

0 引言

每学期期中考试或期末考试后，教师尤其是担任班主任的教师要对学生的成绩进行统计、分析，这是一项非常重要也很繁琐的工作。Excel具有强大的数据统计、分析、处理功能，如果在学生成绩管理中充分利用Excel强大的函数处理功能，就能将繁琐的成绩统计工作简单化，对学生的各项考试成绩的统计、分析和查询就会十分便捷。下面就如何利用Excel函数对实现学生成绩的几个典型的统计应用进行具体描述。

1 应用RANK函数，按成绩排名次

进行全班的成绩排名，是衡量学生学习成果与水平经常使用的方法。成绩的排名方法很多，比如根据分数从高到低以降序排序，这是最简单的，只需要单击需要排序的相关列，比如总分，然后单击“数据”面板的“排序和筛选”选项板里的降序按钮，就按总分从高分到低分排序了，在后面增加一个成绩排名数，然后用填充柄的方法从第一名到最后一名填上排名数，然后再按学号升序排序一次就可以了。

以上方法比较麻烦，如果使用RANK函数来完成排名工作则更方便快捷。在单元格I2中输入引号中的内容“=RANK(H2,H$2:H$38,0)”，拖动填充控制柄，复制公式，得到其他同学的名次。

2 用函数生成“成绩统计表”

在成绩表.xlsx的工作薄中，选择工作表“Sheet3”，并将工作表更名为“成绩统计表”。参见实例效果图的成绩统计表。B2到E12区域的数据全部由Excel函数计算得来。

2.1 利用COUNT函数计算四门课程参加考试的人数

必须引用“各科成绩汇总表”相应区域的数据，在B3单元格中输入引号中的内容“=COUNT（各科成绩表!D2:D38）”，得到科目1的参加考试人数，拖动填充控制柄，复制公式，得到其他课程的参加考试人数。

2.2 利用AVERAGE函数计算四门课程中的平均分

在B4单元格中输入引号中的内容“=AVERAGE(各科成绩汇总表!D2:D38)”，拖动填充控制柄，复制公式，得到其他课程的平均分。

2.3 利用MAX函数计算四门课程中的最高分

在B5单元格中输入引号中的内容“=MAX(各科成绩汇总表!D2:D38)”，拖动填充控制柄，复制公式，得到其他课程的最高分。

2.4 利用MIN函数计算四门课程中的最低分

在B6单元格中输入引号中的内容“=MIN(各科成绩汇总表!D2:D38)”，拖动填充控制柄，复制公式，得到其他课程的最低分。

2.5 用COUNTIF函数分别统计出各门课程中各分数段的人数

90-100 分数段的人数，=COUNTIF(各科成绩表!D2:D38,“＞=90”)；

80-89 分数段的人数，=COUNTIF(各科成绩表!D2:D38,＂＞=80＂)-COUNTIF(各科成绩表!D2:D38,＂＞90＂)

70-79 分数段的人数，=COUNTIF(各科成绩表!D2:D38,“＞=70”)-COUNTIF(各科成绩表!D2:D38,＂＞80＂)

60-79 分数段的人数，=COUNTIF(各科成绩表!D2:D38,“＞=60”)-COUNTIF(各科成绩表!D2:D38,“＞70”)

60 分以下（不及格）的人数，=COUNTIF(各科成绩表!D2:D38,“＜60”

2.6 分别计算四门课程中的及格率

在B12单元格中输入以下的公式：=(B3-B11)/B3。

2.7 分别计算四门课程中的优秀率

在B13单元格中输入以下的公式：=B7/B3。

3 用IF函数将学生成绩百分制转成五分制

插入一个新的工作表，并将工作表命名为“各科成绩等级表”。将“各科成绩等级表”中的“学号”、“姓名”、“性别”列的数据复制到“各科等级表”中的相应位置。利用If嵌套函数对“各科成绩表”工作表中的四门课程进行如下设置；成绩在90分以上的设置为“A”；成绩在 80～89 之间的设置为“B”；成绩在 70～79之间的设置为“C”；成绩在 60～69分之间的设置为“D”；成绩小于60分的设置为“E”。在单元格 D2 中输入“=IF(各科成绩汇总表!D2＞=90,“A”,IF(各科成绩汇总表!D2＞=80,“B”,IF(各科成绩汇总表!D2＞=70,“C”,IF(各科成绩汇总表!D2＞=60,“D”,“E”))))”，拖动填充控制柄，复制公式，得到其他课程的最高分。