用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。
图2
我们还可以对制作的班级工作表据进行条件设置，如语文、数学、英语的总分是120分，那么要达到对输入的数据不能大于120分。对全校所有班可以进行批处理。选中全部班级工作表，再选择语文、数学、英语三科的成绩，选中后打开“格式”菜单栏下的“条件格式”，如图3。我们在输入数据时，如果输入的数据大于120分将无法输入，可以设置为语音提示。其它学科根据学科总分可以进行相应的设置。以免在输入成绩时造成一些错误。
图3
3、年级总表的字段与班级一致，只是后面多一列“年级平均数”，加这一列的目的，便于每个班级与全校的平均水平比较。其中“班级名次”应为“年级名次”，计算名次公式中的单元格区域要修改。也可以另插入一列，作为年级排名，如图4。根据学校工作的需要，有时候还需要对每个班在一定名次范围内选择出某个班的学生人数。比如，我校在对班主任进行考核时，学生期末考试成绩是班主任考核的内容之一，还需要知道一个班全年级前50名内的人数、在51至100名范围内的人数、在101至150名范围内的人数。
图4
4、一个年级制作一张质量分析表。
图5
质量分析表应包含下列信息：科目、统计项目、班级名称、年级平均数（图中只列出了语文学科统计信息）。
二、班级工作表的制作
所有班级工作表中所用到的公式都是一致的（如上图2），在制作时将各个年级的工作表上面的字段全部设计成一样的，比如七年级没有物理、化学，与八年级、九年级全部设计成一样，九年级本没有地理、生物，也设计成和七、八年级一样。这主要是为了便于操作。
在班级工作表中用到公式的地方主要是总分、得分率、班名。操作时可以将全校所有班级的工作表全部选中，进行批处理，一起输入公式。
1、每个学生总分求和。选取全校所有班级，在在任意一张工作表总分下面的N3单元格中输入求和公式：SUM（）括号中是求和的区域，如图2中第三行应是SUM(D3:M3)。然后进行公式复制。拖动公式到最后一位学生的总分单元格。即可得到全校所有学生的总分。
rank函数的用法
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的学生人数)。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。