利用Excel实现成绩的自动统计

韩松喜

秦皇岛市山海关区第三中学 河北秦皇岛 066200

笔者担任本校学生成绩统计的工作已经有好多年了，在成绩的统计过程中，计算器使用过，成绩统计软件也使用过，但是好多的功能都和本学校要求的不太一致，在利用软件进行统计后，还需要再进行大量繁琐的工作。因此，笔者经过一段时间的摸索，利用Excel这个软件，制作出符合学校要求的成绩统计模版，每次只要输入学生的考试成绩就可以了，其他的内容全部是自动生成的，剩下的工作就是直接去打印室打印出来就完成了。虽然各个学校的要求不太一致，但是大体上都相同，希望笔者的方法可以对大家有所帮助和启发。

此Excel文件包含了所有初中学科、8个班，每班50人的成绩统计功能。下面笔者就将制作过程介绍给大家，希望能给大家一些启发，更好地完成学校交给的工作。

1 设计输入成绩的名单模块

首先打开2003版Excel，将Sheet1重新命名为打印名单，然后根据自己的学校的需要将学生的名单复制进来，并对表头进行输入。这个表是将来输入成绩的时候由任课教师打印的成绩填写单（根据学校情况自定人数），任课教师将成绩输入完成后，将直接按照这个表单输入成绩。如果有学生的名单变动，可直接更改这个表中的学生名字，其他的不需修改。

2 设计成绩统计的设置模块

将Sheet2改名为设置，然后根据需要将各个学科的成绩的优秀率、及格率、差生率的成绩输入，将每次考试的名称输入，将每个班的任课教师名单输入，另外设置上哪个学年度的，这样以后只需要修改设置里面的内容，其他的就会自动更改。设计这个模块的最主要的作用就是为了某些学科为120分，这样的优秀率、及格率和差生率就和100分的不同了。为了以后不必频繁地更改设置，故此设计了此模块。

3 设计成绩统计的录入模块

将Sheet3改名为成绩输入，下面要对学生的成绩的输入进行设计。在这里，所有的学生成绩输入后，不需要更改任何内容，其他的统计表就完全自动生成了。另外，在介绍一个输入成绩的小窍门，就是在Excel2003以后新增加的一个功能“文本到语音”，利用这个功能，可以在输入成绩后按回车键将输入的成绩朗读出来，这样就可以方便地进行输入成绩的校对。

具体做法：1）在输入成绩表中按照每科成绩一列进行输入成绩表的设置，每个班预留50人，8个班级；2）表头为=设置!F1&“考试输入成绩表”；3）将打印名单表中的姓名按照每个班的顺序进行复制，然后到输入成绩中将学生名单选择性粘贴过来（粘贴链接），这样当修改打印名单表中的学生姓名的时候，输入成绩表中的学生姓名也相应修改；4）根据粘贴过来的学生名单，利用自动填充功能，将班级填充上；5）以上工作全部完成后，就可以输入本次学生的成绩了，缺考的输入0或为空。

4 设计年级成绩的统计模块

在完成各班的成绩输入后，就要进入到全年级的成绩统计中。先插入一个新的工作表，改名为年级成绩，将成绩录入表中的数据进行选择性粘贴（粘贴链接）。

1）姓名、班级、语文、数学、英语、物理、化学、政治、历史、生物、地理字段为从成绩录入表中粘贴过来的。其他各学科的名次为粘贴完成绩后插入新的列。除各学科的分数、姓名、班级以外均为函数字段，需要手工设置。

2）各科名次、班级名次、年级名次应用到的函数为RANK。比如语文名次在编辑栏输入=RANK(C3,$C$3:$C$399,0)，C3是当前单元格，$C$3:$C$399是指从C3开始到C399结束，然后自动填充到C399。这样语文的全年级的排名就出来了，其他排名类似，换成相应的成绩就可以了。

3）总分=C3+E3+G3+I3+K3+M3+O3+Q3+S3，这样的话不容易混乱，然后向下自动填充到U399；班级的名次只需要自动填充到本班的最后一个人就可以了，其他依次类推。

4）表头为=设置!F1&“班级成绩总表”。

5）在单元格中输入到函数后一般都需要按Enter，但是在某些时候还需要按Ctrl+Enter，这样才能使函数生效。

5 设计各班成绩统计模块

完成年级的成绩统计设计以后，只需要将相应的班级的内容选择性地粘贴到一个新的表中就可以了，可以依次取名为一班、二班、三班、四班等，为将来的成绩打印提供方便。

另外，还有各个班级的各科的总分和平均分的统计，每个班的各个学科的总分用到了SUM，人数用到了COUNTIF。例如：=COUNTIF(C3:C49,">0")，统计C3到C49分数大于0的人数，平均分用总分除以人数。因为0分为缺考，这里就不做统计了。

打印的时候只需要将没有考试的学科和相应的学科名次直接进行隐藏，另外利用自动筛选功能将班级设置为相应的班级就可以打印各个班的成绩表了。

6 设计年级统计模块

先插入一个新的工作表，改名为小表，整个年级的统计信息就在这个表中，其中学生的统计信息是以各个班为单位的。

1）表头的优秀率可以写成=“优秀率(“&设置!B2&”分以上）”，这样只需要修改设置表中的分数，这里就直接修改了过来，及格率、差生率同理。

2）班级人数为直接调用各个班级中的最下面统计出来的数据。

3）各个分数段的人数利用到函数SUM，例如一班语文成绩的110到120的人数为：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50<120)*(一班!$C$3:一班!$C$50>=110))，一班语文成绩的100到109的人数为：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50<=109)*(一班!$C$3:一班!$C$50>=100))，其中A5为班级，一班为一班的成绩表，$B为班级，$C为语文成绩，其他的分数段和其他的班级也同理可以得到。

4）优秀率、及格率、差生率的人数求法和各个分数段的求法基本相同，只不过是将上面的分数段修改成设置表中的优秀率、及格率、差生率相对应的分数。例如一班语文的优秀率为：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50>(设置!$B$2)))；及格率为：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50>(设置!$C$2)))；差生率为：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50<(设置!$D$2))*(一班!$C$3:一班!$C$50>0))。

5）各个率的百分比为人数除以班级参考人数，然后将该单元格设置为百分比格式。

6）总分和平均分可以调用各个班表中相对应的数据，进行选择性粘贴。

7）每个班的单科最高分相对应的学生姓名的求法利用到INDEX，例如一班的语文最高分的例子为：=INDEX(一班!$A$3:$A$50,MATCH(MAX(一 班!$C$3:$C$50),一班!$C$3:$C$50,0))。其他班的类似。

8）每个班的单科最高分和最低分应用到MAX和MIN函数，例如一班语文最高分为：=MAX(一班!$C$3:一班!$C$50)，最低分为：=MIN(IF(一班!$C$3:一班!$C$50>0,一班!$C$3:一班!$C$50))。这里只求分数大于0的的最低分。

9）将上面内容设置好表格样式，调整好大小和距离后直接复制，修改相对应的学科，直到所有学科都修改完毕。

7 设置全年级各学科第一名模块

将小表中的各个学科的最高分的名字和分数以链接的方式复制到这个表中，根据需要设置好表格样式和大小就可以了。

8 设置全年级总成绩前50名模块

将年级名次表进行复制，创建一个新的表，表名为前50，然后利用Excel中的自动筛选功能将年级名次为小于或等于50的数据筛选出来，进行排序。

9 冻结窗口，保护文件

模版整体制作完成后，利用Excel的冻结窗口（窗口菜单下的冻结命令）将不需要改动的内容冻结起来，然后再将不需要改动的文件保护起来，省得出现错误。

10 设置打印

完成了以上的工作，就可以对Excel进行表格设置、页面设置、字体设置（宋体）、字号设置（10）、行高设置，完成后就可以直接到打印室进行成绩的打印了，到此圆满完成学校交给的工作。

自从用Excel做了这个成绩统计模板后，每次只需要在考完试后将学生的名单打印出来交给各个学科的教师，教师录完成绩后，笔者根据名单和成绩将学生的成绩进行输入，这样基本上只需要输入完成绩就可以直接去打印室打印了。由于本统计介绍是初中的，如果小学想使用的话，可直接将班级改成年级，其他方法基本相同。

以上只是笔者的一点儿经验，希望和其他的老师分享一下，也希望大家对此提出建议，以便更好地设计统计模板，更加方便快捷地完成成绩统计工作，更好地的完成学校交给的工作。