⽤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。
excel利用rank排名次公式图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给成绩统计、教学管理评估、新⽣编班都带来了极⼤的⽅便，在提⾼⼯作效率和全⾯提⾼教学质量中都有着很⼤的应⽤价值。