利用Excel巧妙处理学生成绩

摘要：用Excel可高效处理学生成绩，介绍了表格的设计、相关公式的使用和模板的制作。

关键词：Excel 公式 模板

中图分类号： G434　文献标识码： B 文章编号：1002-2422(2008)01-0035-02

1 表格设计

工作簿中设计三个工作表，第一个工作表为参数设置工作表。不同的课程考勤成绩、作业成绩、考试成绩占综合成绩的比例不同，缺席一次所扣分数也会不同，考勤记录的符号可能不同，为适应不同的课程，设计一张工作表来设置这些参数，在计算学生成绩时，相关参数就从该表中取得。在该表中设置记录考勤的符号，如用“X”代表缺席，设置缺席一次所扣分数为10分等。

第二张表为成绩登记表，如图1所示，在该表中记录学生的考勤情况，根据考勤记录计算考勤成绩，录入学生作业成绩与考试成绩，在录入成绩时设置了数据的有效性，将数据限定在0到100之间，如果不在此范围内则报出错信息。设置的具体方法为，选择“数据”菜单中的“有效性…”，弹出“数据有效性”对话框，设置数据的有效性范围为0到100即可。计算学生的综合成绩或以等级计分的成绩，在不改变学号排序的前提下计算学生名次。学生成绩低于60分的用红色字体显示，可用“格式”菜单中的“条件格式”进行设置。

第三张表为学生成绩分析表，在该表中设计了计算学生成绩各分数段的人数、百分比、计算及格率、平均分等。

2 成绩计算

在参数设置表中将相关参数设置好后，学生成绩的计算在学生成绩登记表中进行，为了方便计算，需先对参数设置表中的相关单元格进行命名。

(1)单元格命名

对设置的参数缺席所扣分数的D5单元格命名，先选中D5单元格，在名称框中输入要命名的名字”缺席扣分”，然后回车，该单元格即被命名为“缺席扣分”，在以后的公式中可以利用该名字代替D5单元格的地址。用相同的方法可对相关参数的单元格命名，各单元格名字如表1所示。

(2)考勤成绩计算

图1中M4单元格中的公式为“=100-IF(COUNTIF(E4：L4．缺席符号)>缺席次数，100，(COUNTIF(E4：L4，缺席符号)*缺席扣分+COUNTIF(E4：L4，迟到符号)*迟到扣分))”该公式由if()函数和counllf()函数组成，courltif()函数统计缺席、迟到的次数，if()函数的结果为所扣分数，如果该生缺席次数大于设置的缺席次数，则所扣分数为100,否则为统计出缺席、迟到扣分的总和。考勤成绩默认为100分，减去if()函数的结果则可得到考勤分数。如果在成绩计算中需调整考勤缺席、迟到所扣分数，只要调整参数即可，勿需更改公式。

(3)综合成绩

图1中P4单元格中的公式为“=M4*考勤比例+N4*作业比例+04*考试比例”，若要修改各种成绩所占综合成绩的比例，只需修改参数设置表中的相关参数即可。

(4)等级成绩

图1中Q4单元格中等级成绩的生成是根据P4中的成绩来确定的，具体公式为“=IF(P4>=90，“优”，IF(P4>=80,“良”。IF(P4>=70，“中”，IF(P4>=60，“及格”，“不及格”))))”，该公式中使用了IF()函数，如果P4单元格中的成绩大于等于90,则为“优”，否则再判断其是否大于等于80,依次类推嵌套If()函数，直到将所有的分数段判断完为止。

(5)排名次

要保持现有的顺序不变，则可以利用公式完成，图1中R4单元格的公式为“=RANK(P4，$P$4：$P$26)”，rank()函数中有两个参数，第一个参数指P4中数字在区域中的排序，第二个参数$P$4：$P$26指明这个数据区域。

3 成绩分析

计算出学生综合成绩后，一般需对学生的成绩进行分析，如计算各分数段的人数、百分比，及格率和平均分等。在第三张表中进行成绩分析，因为分析的原始数据在第二张表中，所以先对成绩登记表的数据区域命名。将图1中从P4到P6单元格区域命名为“综合成绩”，在进行成绩分析计算时将用到该区域。计算出学生总人数，B2中的公式为“=count(综合成绩)”，count()函数将返回名字为“综合成绩”的单元格区域的个数，即为总人数；接下来计算各分数段的人数及百分比，先计算90-100分数段的人数，B4中公式为“=countif(综合成绩，“>=90”)”，用countif()函数统计大于等于90的学生人数，因为成绩登录表中保证了分数是O到100中之间的数，所以大于等于90的人数即为90-100之间的数。百分比相对简单，用该分数段的人数除以总人数即可，但须注意，总人数单元格地址的引用要用绝对引用。

4 制作模板

(1)按上述方法建立好相关的表格、公式等，去掉成绩登录表中的相关原始数据(公式不能去掉)；

(2)选择“另存为”按钮，系统弹出“另存为”对话框，输入文件名“学生成绩处理”，选择保存类型为“模板”，这样就把该表格存为了模板，以后就可基于该表格建立新的表格。

5 结束语

通过设置参数的方式，有效地解决了不同课程在成绩处理上的不同要求，将设计好的工作表保存为模板，大大提高了处理成绩的效率。