每当学期结束，需要汇总学生的各科考试成绩，教务处和班主任都会忙得头晕脑涨。而这一工作总是每个学校、每个年级乃至每个班级都要做的事情，成为对学校、班主任、任课教师、学生进行考核评定的重要参考资料。在这个过程中，不但要计算每名学生的考试成绩的总分，并排出名次，还要统计每一学科的最高分、最低分、平均分、优秀率、及格率等，在给学生填写成绩报告单时，只填等级（A、B、C、D、E等），不填分数。用手工计算起来，甚是麻烦。不过这些工作都可以让Excel替我们自动完成，我们所要做的，只是输入学生的各科考试分数。输入成绩时，还可以让多位教师同时进行，协同操作，因而，复杂繁重的工作也可以变得轻松起来。具体步骤如下（这里仅以某校七年级两个班为例）：

 

1.成绩分析表雏形 

 

新建一个工作薄，插入工作表，使工作表个数要比班级数多出两张，其中一张是“年级汇总”，一张是“成绩分析”，把各工作表标签依次改为1班、2班、年级汇总、成绩分析。成绩分析表雏形如图1所示，并将其保存为“通用模板”，方法是在“另存为…”对话框中，单击保存类型下拉按钮，选择“通用模板”，输入文件名（学生成绩汇总）后单击“保存”即可。以后用的时候，只需打开“Excel”，单击“文件”菜单，选择“新建”，单击“本机上的模板(Excel 2003)”，选择“学生成绩汇总”即可。

 

 

2.班级汇总工作表的制作 

 

2.1 将1班第一行分别复制到“2班”及“年级汇总”工作表中的第一行。单击1班工作表标签，假设各班人数为50人，单击j2单元格，输入公式：

 

=IF(D2:I2<>"",SUM(D2:I2),"")

 

IF函数功能介绍：执行真假值判断，根据你指定的条件进行逻辑判断的真假而返回不同的结果。函数格式：IF(logical_test,value_if_true,value_if_false)，其中logical_test表示任何一个可评价为true或false的数值或表达式；value_if_true表示当logical_test为true时的返回值，如果忽略则返回true，if函数最多可以嵌套七层；value_if_false表示当logical_test为false时的返回值，如果忽略则返回false。该公式说明IF函数先判断条件D2:I2<>””的真假，为真时返回sum(d2:i2)，即计算出各科成绩的总分，为假时返回为“空”，即缺考的学生成绩在总分栏显示为“空”，下同。

 

输入完成后按下Shift+Ctrl+Enter组合键，Excel会自动在公式两端添加“{}”。选中j2单元格，将鼠标移到填充柄（活动单元格右下角的小方块）上，待鼠标指针变成黑色十字箭头时，按下鼠标左键拖动鼠标向下填充到j51（下文中有关单元格填充都指此方法）。

 

2.2 再单击K2单元格，输入公式：

 

=IF(D2:I2<>"",AVERAGE(D2:I2),"")

 

按下Shift+Ctrl+Enter组合键，这里，函数AVERAGE是计算各科的算术平均值。这样，就得到了第一个学生的平均分了，再向下填充至K51单元格。

 

2.3 单击L2单元格，输入如下公式：

 

=if(j2<>"",RANK(J2,$J$2:$J$51,0),"")

 

按下Shift+Ctrl+Enter组合键，这里，函数RANK是返回某数字在一列数字中相对于其它数字的大小排位，数字区域要使用“绝对引用”。再向下填充至L51单元格。至此，班级汇总结束，将“1班”工作表复制到“2班”工作表中去即可。

 

3.年级汇总工作表的制作

 

3.1 单击年级汇总工作表标签，单击A2单元格，输入“=IF('1班'!A2<>“",'1班'!A2,"")”，按下Shift+Ctrl+Enter组合键，再向右填充到K2单元格，选中A2到K2单元格，按住填充柄向下填充到K51单元格。单击A52单元格，输入“=IF('2班'!A2<>"",'2班'!A2,"")”，按下Shift+Ctrl+Enter组合键，用同样的填充方法，一直填充到K101。

 

3.2 单击L2单元格，输入

 

=if(j2<>"",RANK(J2,$J$2:$J$101,0),"")

 

同样，按下Shift+Ctrl+Enter组合键，再向下填充至L101即可。

 

3.3 下面进行各科等级公式的编写，分A、B、C、D、E五个等级，A级为15%，B级为25%，C级为30%，D级为25%，E级为5%。利用“IF、LARGE、COUNTIF、COUNTA”等函数进行嵌套，可以完成这个要求。“COUNTA”是计数函数，也就是班级人数；“COUNTIF”是计算某个区域中满足给定条件的单元格数目；“LARGE”的本意是返回数据区域里第K个最大值，而“IF”函数进行逻辑判断，当条件为true（真）时，返回第二个参数条件，当条件为false（假）时，返回下一个条件继续判断，直至结束。在“年级汇总”工作表中的M1∶R1单元格中依次输入“语文等级”、“数学等级”、“英语等级”、“科学等级”、“社会等级”、“政治等级”。单击M2单元格，输入：

 

=IF(D2<>" ",IF(D2>=LARGE(D$2:D$101,COUNT(D$2:D$101)*15%),"A",IF(D2>=LARGE(D$2:D$101,COUNT(D$2:D$101)*40%),"B",IF(D2>=LARGE(D$2:D$101,COUNT(D$2:D$101)*70%),"C",IF(D2>=LARGE(D$2:D$101,COUNT(D$2:D$101)*95%),"D",IF(D2>=LARGE(D$2:D$101,COUNT(D$2:D$101)*100%),"E")))))," ")

 

按下Shift+Ctrl+Enter组合键，将M2单元格中的公式向右填充至R2，选中M2∶R2区域，拖动填充柄向下填充至R101。至此，年级汇总工作表制作完毕。如图2所示：

 

 

4.成绩分析工作表的制作 

 

年级成绩分析主要内容有：最高分、最低分、平均分、优秀率、及格率、E级率等内容。格式如图3：

 

4.1 最高分统计：在各科相应的单元格内分别输入（这里仅以“1班”为例，下同）：

 

=IF(′年级汇总′!$D$2:$D$101<>"",MAX(′年级汇总′!$D$2:$D$101),"")

 

=IF(′年级汇总′!$E$2:$E$101<>"",MAX(′年级汇总′!$E$2:$E$101),"")

 

=IF(′年级汇总′!$F$2:$F$101<>"",MAX(′年级汇总′!$F$2:$F$101),"")

 

……

 

4.2 最低分统计：复制最高分项目的相应单元格，只需将其中的“MAX”换成“MIN”即可（可使用“替换…”命令）。

 

4.3 平均分统计：在各科相应的单元格内分别输入：

 

=IF(年级汇总!D$2:D$101<>"",AVERAGE(年级汇总!D$2:D$101),"")

 

=IF(年级汇总!E$2:E$101<>"",AVERAGE(年级汇总!E$2:E$101),"")

 

=IF(年级汇总!E$2:E$101<>"",AVERAGE(年级汇总!E$2:E$101),"")

 

……

 

4.4 优秀率统计：在各科相应的单元格内分别输入：

 

=IF(年级汇总!D$2:D$101<>"",COUNTIF(年级汇总!D$2:D$101,">=90")/COUNTA(年级汇总!D$2:D$101)*100,"")

 

=IF(年级汇总!E$2:E$101<>"",COUNTIF(年级汇总!E$2:E$101,">=90")/COUNTA(年级汇总!E$2:E$101)*100,"")

 

=IF(年级汇总!F$2:F$101<>"",COUNTIF(年级汇总!F$2:F$101,">=90")/COUNTA(年级汇总!F$2:F$101)*100,"")

 

……

 

4.5 及格率统计：在各科相应的单元格内分别输入：

 

=IF(年级汇总!D$2:D$101<>"",COUNTIF(年级汇总!D$2:D$101,">=60")/COUNTA(年级汇总!D$2:D$101)*100,"")

 

=IF(年级汇总!E$2:E$101<>"",COUNTIF(年级汇总!E$2:E$101,">=60")/COUNTA(年级汇总!E$2:E$101)*100,"")

 

=IF(年级汇总!F$2:F$101<>"",COUNTIF(年级汇总!F$2:F$101,">=60")/COUNTA(年级汇总!F$2:F$101)*100,"")

 

……

 

4.6 A级率统计：在相应的单元格内分别输入：

 

 =IF(年级汇总!M$2:M$101<>"",COUNTIF(年级汇总!M$2:M$101,"A")/COUNTA(年级汇总!M$2:M$101)*100,"")

 

=IF(年级汇总!N$2:N$101<>"",COUNTIF(年级汇总!N$2:N$101,"A")/COUNTA(年级汇总!N$2:N$101)*100,"")

 

=IF(年级汇总!O$2:O$101<>"",COUNTIF(年级汇总!O$2:O$101,"A")/COUNTA(年级汇总!O$2:O$101)*100,"")

 

……

 

4.7 E级率统计：只需将A级率单元格中的内容复制到E级率相应的单元格中，将“A”改为“E”即可。 

 

这样，整个工作簿函数的编辑工作已经完成。

 

5.后续工作 

 

现在我们可以测试一下，将1班、2班以往成绩复制到相应班级中去，看一下“年级汇总”和“成绩分析”工作表中的数据生成是否正确，如有问题，可追综错误，查出原因，进行修改。若无问题，单击保存即可。结果如图4所示。

 

 

由于此工作簿用到了许多公式，稍不注意，可能造成误删或其他老师不注意弄错，就很麻烦，因此，要对此工作表进行保护。选定1班、2班的A2：H51单元格区域，单击格式菜单中的“单元格…”命令，单击“保护”选项卡，把“锁定”前面的“√”去掉。再选定J、K、L三列，单击格式菜单中的“单元格…”命令，再单击“保护”选项卡，选中“锁定”和“隐藏”两项，单击“确定”退出。选定“年级汇总”和“成绩分析”两张工作表的全部单元格，同样打开“保护”选项卡，选中“锁定”和“隐藏”两项，单击“确定”。最后单击“工具”菜单，选择“保护”，单击“保护工作表”，可以输入密码，或直接单击“确定”，对每张工作表进行保护。单击“保存”。 

 

经过一番努力，以后考试结束，只需将各班成绩直接输入相应班级的工作表中，其它的事情就交给Excel了，再也不用感到头痛了，一劳永逸，轻松自如。若是某个学区对每个学校进行评价，只需将“1班”、“2班”改成某学校的名字就可以了，这里当然要注意人数的变化，对里面涉及到的参数要注意修改。