用EXCEL进行学生成绩统计

用Excel进行学生成绩统计

利川市毛坝民族初级中学赵世清

摘要：Microsoft Excel是一款优秀的电子表格软件，具有强大的数据处理功能，可以用来处理各种数据报表，它是数据统计非常有用的工具。如学校教务工作中，学生成绩统计是每个学校期中、期末考试后都要进行的一项非常烦琐的工作，有时会因为方法的不当造成很大的工作被动。还可以统计教师业绩考核、进行奖励分配、筛选符合条件的教师或学生等。用Excel对学生成绩进行统计，可以大大提高工作效率。为学校工作提供准确的考试信息，为工作安排提供有力的依据。熟练应用Excel的函数功能可以节约大量的人力，使人从大量的数据计算中解放出来。统计时主要用到的Excel函数有：求和（sum）、Rank（排名）、AVERAGE（平均分）、MAX（最高分）、Nin(最低分)、COUNTIF（优分人数）、COUNTIF（及格人数）、If函数等。下面对学生成绩统计的表格设计及所用到的函数作简单介绍。

关键词：Excel 统计学生成绩函数

在学校的教学管理中，对学生成绩的统计及其分析是非常重要的，它是评价学生掌握学科知识的重要依据。对教师而言分析得准确、细致，可以找到问题所在，才能有针对性地修正今后教学的方向，改变教学措施，提高教学效率，对自己一个教学阶段的自我反思。教师还可以征对学生成绩，对某一些学生制定相应的学习计划。对学校而言，通过对质量进行分析，是学校对教师进行评价的主要依据，也成为学校管理的重要依据。所以学生成绩统计在每个学校中都成了期中、期末考试的重要工作之一。大多数对学生成绩统计工作中统计的项目主要有：对每个班而言有：班级名次、得分率。对整个年级来说有：最高分、最低分、平均分（百分制均分）、及格人数、及格率、优分人数、优分率、任课教师等。下面简单介绍一下用Excel进行学生成绩统计。

一、工作簿的设计

1、一所学校制作一个工作簿

每个工作簿应包括下列信息：全校有多少班就建多少工作表每个班一个工作表，标签就用班级名称；每个年级的一个总表，如图中的700、800、900分别是七八九年级的年级总表；每个年级的质量分析，图中的七质分、八质分、九质分；教师任课情况，图中的JS 工作表。

图1

2、一个班级制作一个工作表

班级工作表应包含下列信息：学号、姓名、性别（便于体育测试等）各个学科（每个学科占一列，还应有总分、得分率、班名（班级名次）如图2。

我们还可以对制作的班级工作表据进行条件设置，如语文、数学、英语的总分是120分，那么要达到对输入的数据不能大于120分。对全校所有班可以进行批处理。选中全部班级工作表，再选择语文、数学、英语三科的成绩，选中后打开“格式”菜单栏下的“条件格式”，如图3。我们在输入数据时，如果输入的数据大于120分将无法输入，可以设置为语音提示。其它学科根据学科总分可以进行相应的设置。以免在输入成绩时造成一些错误。

图3

3、年级总表的字段与班级一致，只是后面多一列“年级平均数”，加这一列的目的，便于每个班级与全校的平均水平比较。其中“班级名次”应为“年级名次”，计算名次公式中的单元格区域要修改。也可以另插入一列，作为年级排名，如图4。根据学校工作的需要，有时候还需要对每个班在一定名次范围内选择出某个班的学生人数。比如，我校在对班主任进行考核时，学生期末考试成绩是班主任考核的内容之一，还需要知道一个班全年级前50名内的人数、在51至100名范围内的人数、在101至150名范围内的人数。

图4

4、一个年级制作一张质量分析表。

质量分析表应包含下列信息：科目、统计项目、班级名称、年级平均数（图中只列出了语文学科统计信息）。

二、班级工作表的制作

所有班级工作表中所用到的公式都是一致的（如上图2），在制作时将各个年级的工作表上面的字段全部设计成一样的，比如七年级没有物理、化学，与八年级、九年级全部设计成一样，九年级本没有地理、生物，也设计成和七、八年级一样。这主要是为了便于操作。

在班级工作表中用到公式的地方主要是总分、得分率、班名。操作时可以将全校所有班级的工作表全部选中，进行批处理，一起输入公式。

1、每个学生总分求和。选取全校所有班级，在在任意一张工作表总分下面的N3单元格中输入求和公式：SUM（）括号中是求和的区域，如图2中第三行应是SUM(D3:M3)。然后进行公式复制。拖动公式到最后一位学生的总分单元格。即可得到全校所有学生的总分。

2、求每个学生的得分率。选取一个年级的所有班级。这家里不能选择全校所有班级，因为不同年级总分是不一样的。如图2中在O3单元格中输入=N3/总分。拖动复制柄到最后一名学生的得分率处即可。

3、求每个学生在班级中的排名。选取全校所有班级，在P3单元格中输入：Rank(N3,$N$3:$N$62，0)（假设全校所有班级中人数最多的一个班为60人）。

RANK函数用法介绍：RANK函数是Excel中计算序数的函数，它的语法为：RANK(Number,Ref,Order)，其中Number为参与计算的数字或含有数字的单元格，Ref是对参与计算的数字单元格区域的绝对引用，N3：N62为相对引用，$N$3:$N$62为绝对引用，Order是用来说明排序方式的数字(如果Order为零或省略，则以降序方式给出结果，如果Order为1，则以升序方式给出结果。)如图2中：在N3: N62单元格区域中存放着某一个班的总分，那么计算总分名次的方法是：在P3单元格中输入“=RANK(N3,$N$3:$N$62，0)”按回车键可算出N2单元格内总分在班内的名次，我们再选定F2单元格，把鼠标指针移动到填充柄上按下鼠标左键向下拖动鼠标即可算出其他总分在班内的名次。在计算的过程中我们需要注意：用RANK函数计算班级或年级排名时一定要使用绝对引用。

三、质量分析表的制作

图5是一个年级质量分析表的一部分，图4分别选取了两个班和两门学科的质量分析所用到的公式。下面对上图中所用到函数或公式进行详细说明。

1、最高分：MAX('801'!$D$3:$D$65)，MAX是选取一组数值中的最大值函数公式，可以用MAX求出一个班每门学科成绩中的最高分。其中801是指801班，$D$3:$D$65是指一个班的所有语文成绩。即MAX参数所指的一组数值。这里利用了绝对引用，其目的是为了班级表格的行栅除后不会造成的公式变化。由于MAX用法的用法比较简单，这里就不介绍了。

2、最低分：MIN('801'!$D$3:$D$65)，MIN是选取一组数值中的最小值函数公式。其用法与MAX函数完全相同。

3、均分：AVERAGE('801'!$D$3:$D$65)，AVERAGE是一组数值中的平均数函数公式，本例就是求取801班，语文学科，D3单元格到D65单元格内所有数据的平均值。

4、百分制均分：C4*10/12百分制均分，在学校评价中，为了便宜于不同学科由于分值的不同无法进行比较，把所有不同分数的都转化为百分制均分。在公式C4*10/12中，C4

是存放语文均分的单元格，它是120分制时的均分，转化为百分制时要除1.2。

图5

5、优分人数：COUNTIF('801'!$D$3:$D$65,">=96")，COUNTIF是选取区域内单元格内满足条件的单元格个数。COUNTIF('801'!$D$3:$D$65,">=96")是指在801工作表中D列从第3行到第65行内数据满足大于或等于96（语文、数学、外语均为96分及以上为优分）的单元格个数。

6、优分率：C14/COUNTIF('801'!$E$3:$E$65,">=0")，C14是指存放优分人数的单元格，分母COUNTIF('801'!$E$3:$E$65,">=0")是指参加考试的学生中大于或等于0分以上的个数。注意：如果在班级工作表中不输入数据则没有计算在内。如果要将该考生也纳入考核计算，则在考生的语文成绩单元格内要输入0。

7、及格人数和及格率与优分人数、优分率的计算一样，不同的是及格分数的值不同（在初中考试中语文、数学、英语及格人数一般为72分）

8、任课老师：是指引用的“JS”工作表中对应班级对应学科的任课老师。“JS！$D$11”是指在JS工作表中D11单元格为801班的语文任课教师。

9、在质量分析的最后一列还有一列年级平均数，它是统计的全年级的平均值，所运用的公式或函数与班级一样，但是要将“班级名称”改为“年级总表名称”复制后将“801”改为“800”（800为年级总表），单元格区域如：$E$3:$E$65要修改成$E$3:$E$432（其中$E$3:$E$65中65-2=63为该班人数，$E$3:$E$432中432-2=430为年级学生总数）。

在完成一个学科的每一项公式设置后，然后复制到不同班和不同学科，要修改的内容有：（1）不同班级之间只需要修改公式参数前面的班级名称即可，在制作802班语文分析项目时只需将“801”全部替换成“802”即可，可用“替换”命令，选中802语文质量分析区域，再将所有的“801”替换成“802”。（2）不同学科只需修改存放单元格的列号即可。如语文在D列，数学在E列，将语文复制到数学这一区域内时只需将D全部“替换”成E即可，注意：有时函数的英语表达示中也含有字母D，这时用“$E”替换“$D”即可。当这张表制作完成后，当所有原始成绩输入班级工作表后，它们将全部显示出计算结果，得出正确的数据。

四、对学生成绩进行统计工作还有很多

1.对学生总成绩进行分数段统计：用Excel在进行学生成绩统计时，还可以统计很多的东西，每年上一级教育教学管理部门要对一个学校整体进行考查、评价，其依据是学生成绩统计表。如可以统计按分数段统计出在一个分数段内的人数。用count if函数就可以计算各分数段的人数。

=COUNTIF(C$2:C$21,">=500") (计算出总分大于或等于500分的学生人数)；

=COUNTIF(C$2:C$21,">=450")-COUNTIF(C$2:C$21,">=500") (用总分大于或等于450分的学生人数减去总分大于或等于500分的学生人数)；

=COUNTIF(D$2:D$21,">=400")-COUNTIF(D$2:D$21,">=450") (同上)；

=COUNTIF(E$2:E$21,">=350")-COUNTIF(E$2:E$21,">=400") (同上)；

=COUNTIF(C$2:C$21,">=300")-COUNTIF(C$2:C$21,">=350") (同上)；

=COUNTIF(D$2:D$21,"<=300")(总分小于或等于300的学生人数)。

2.用If函数选取优秀学生

学校对取得优异成绩的学生进行表彰时，往往要根据学生成绩和在校表现等进行筛选，有时只要满足一个条件就行了，有的要同时满足两个条件。这时就要用到Excel中的If函数来进行筛选。如：我校每学期都要根据成绩对学生进行表彰，得分率大于或等于85%的表彰为“特优生”；得分率大于或等于80%的表彰为“优秀生”；得分率大于或等于75%的表彰为“三好生”；在筛选这三类学生时用下列函数来选择：

=IF(O3>=85%,"特优生",IF(O3>=80%,"优秀生",IF(O3>=75%,"三好生","")))

If函数用法介绍：IF(logical_test,value_if_true,value_if_false)

第一个参数（Logical_test）表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如，A10=100 就是一个逻辑表达式，如果单元格 A10 中的值等于 100，表达式即为 TRUE，否则为 FALSE。本参数可使用任何比较运算符。

第二个参数（Value_if_true）当第一个参数为 TRUE 时返回的值。例如，如果本参数为文本字符串“预算内”而且第一个参数值为 TRUE，则 IF 函数将显示文本“预算内”。如果第一个参数为 TRUE 而第二个参数为空，则本参数返回 0（零）。如果要显示 TRUE，则请为本参数使用逻辑值 TRUE。Value_if_true 也可以是其他公式。

第三个参数（Value_if_false）当第一个参数为 FALSE 时返回的值。例如，如果本参数为文本字符串“超出预算”而且第一个参数（logical_test）值为 FALSE，则 IF 函数将显示文本“超出预算”。如果第一个参数（logical_test）为FALSE 且忽略了Value_if_false（即value_if_true 后没有逗号），则会返回逻辑值FALSE。如果logical_test 为 FALSE 且 Value_if_false 为空（即 value_if_true 后有逗号，并紧跟着右括号），则本参数返回 0（零）。Value_if_false 也可以是其他公式。函数IF可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。

If函数是Excel中最基本的一个，也是应用最灵活、最广泛的函数之一。能够灵活应用该函数与其它函数、引用的嵌套与组合，可以解决许多实际问题，大大提高工作效率，发挥Excel强大的函数功能。

3.进步生筛选方法

当得分率比前一次考试提高20%以上的表彰为“进步生”（在进行这一项统计时还需要在年级总表后面加入两列，Q列为上次考试得分率，R列为两次考试得分率差值，即用这次考试的得分率减去上次考试的得分率，选取得分率差值大于或等于20%的学生并显示为“进步生”，否则不显示，其函数公式为：=IF(R3>=20%,"进步生","")。

在详细制作了一个成绩统计工作表后，每个学期都可以利用前面制作的工作簿，如果没有错误的话，等到下次考试的时候，我们只要改变各科目的原始分数，各个项目的统计也就立刻完成了，而且绝不会出错。成绩统计再也不是让人头疼的工作了，但是对其中的某些内容要进行修改。可以大大节约时间，提高效率。修改的内容主要有：函数参数中单元格区域、班级学生人数、班级、各班任课教师等。

由此可见，Excel给成绩统计、教学管理评估、新生编班都带来了极大的方便，在提高工作效率和全面提高教学质量中都有着很大的应用价值。