EXCEL统计学生成绩中一些函数的应用技巧.docVIP

EXCEL统计学生成绩中一些函数的应用技巧 河池民族农校 陈启才 摘要：在建立成绩处理模板时，掌握LOOKUP（）、RANK（）等一些函数在不同条件下的使用技巧，使建立的模板更具实用价值。 关键字：函数、公式、排序、VBA 学生考试结束，教师一项工作就是迅速科成绩各项指标，如平均分、最高分、优秀率、及格率以及各学科分数的频率等掌握学生学习现状，教学质量分析的据函数公式制作一个能够自动统计成绩的电子表格模板要在空白模板中填上学生成绩，统计数据就立刻显现出来，班级的教学质量数据一目了然，提高了工作效率。因为EXCEL是一个处理静态表格数据软件，缺乏象其他数据库语言那样具有生成动态查询视图的功能，对于一些无明确分类的数据，要提取出来进行处理只能间接地完成。以高中会考成绩为例，考试结束后学校都要对会考成绩进行总结评比，作为检验教学质量和考核教师的一个依据。会考成绩我们可以从上级教育主管部门获取，如我校高中部08级学生会考成绩（见表1）。 表1 从这个表中可看出，这些资料只是全校综合性的成绩，并没有班级信息。学校对教学成绩的考核是以班级为单位进行的，如果不将表1中的考生所属何班级分类清楚，成绩的统计分析就无法进行了。就一个学校的某个年级而言，学生人数少的几百人，多则可上至一、两千人，如果要将考生的成绩重新录入再进行分析，因数据量庞大而费时费力。 为了节省存储空间，减少数据冗余，教育主管部门在处理学生会考成绩时，每位考生只设置一会考号作为唯一标识，显然是不能简单地利用排序、汇总功能来完成分班处理的。一个较好的方法是利用函数LOOKUP（）进行，具体的操作如下： 插入一表格，表名命名为“会考成绩”。导入全年级会考成绩后，对A列会考号（hk1）按升序排序。 在工作簿中插入多个工作表，将表依次按班级命名，如“高42班”、……、等。将“会考成绩”表表头“hk1 hk2 地理 C1 历史 C2 ……”，复制到各班成绩的第一行，作为分班成绩表表头。 在“高42班”表格中的B2单元格输入公式：“=LOOKUP($A2,会考成绩!$A$2:$A$601,会考成绩!B$2:B$81)”。注意公式中的参数601和81，表示有600个同学会考成绩，要筛选的班级“高42班”有80个同学。拖动填充公式至T列81行，这样就制作成了“高42班”会考成绩表。此时因“高42班”成绩表A2至A81等单元格尚未输入会考号，查找函数LOOKUP（）会显示出错信息：“#N/A”。 从“高42班”成绩表A2单元格开始输入第一位同学谭莉沙的会考号，该同学的成绩即自动从“会考成绩”表中复制到“高42班”表中第二行中。依次往下输入其他同学的会考号，即可将“高42班”全班同学成绩自动复制完成，整个工作就是仅输入“高42班”各同学的会考号，会考成绩则自动由公式复制到“高42班”的成绩表中。 使用LOOKUP()要注意的问题是首先“会考成绩”总表中的必按会考号升序排列，其次是如果“会考成绩”表中的会考号是字符型的，在“高42班”表中的A列输入的会考号也要求是字符型的，即要在会考号前加英文单引号。 二、成绩排序 制作好了的学生成绩单模板，使用时不要用排序菜单或快捷排序工具进行操作，会造成表格混乱，一般需用RANK（）函数编写出如公式：“=RANK(K4,K4:K78,0)”来解决这个问题 ㈠、两栏成绩的同时排序 当一个班学生人数过多，制作各班成绩表时通常把学生的姓名分两栏排列（见表2），相应的成绩也就不处于同一列中，要把位于不同列中的成绩同时排序，这时要把公式中的多区域参数用括号引用才能得出正确的结果，如公式：=RANK(K4,(K4:K78,S4:S78),0) 公式计算的结果是K4单元格的成绩在（K4：K78）、（S4：S78）两个区域数据的总排名。 表2 ㈡、不同页面成绩总排序 各班级开设的某一课程，因班级不同其成绩并不处于同一页面上，这时函数RANK()也无法解决统一排序问题了，下面用表3列出的两组数据，假设它们是不同班级并分处于不同的页面上的成绩，给出排序方法。 对这些不同页面上的数组A、数组B统一按升序排列，它的序号可以这样去求出，例如要找出81这个数的排序位置，实现的原理是在这两数组中找出大于81的数值个数有2个，再加1，所以这个数的序号为3。 改用EXCEL公式排序(降序)写法为： “=IF(OR(A5=,A5=0),,COUNTIF(A3:A77,A5)+COUNTIF(45班!A3:A77,A5) +1)” 公式中用到“OR(A5=,A5=0”，则是为屏蔽所有数据为0或空时，所给出的排序却为1这样的错误。 数组A 排序（升序） 数组B 排序（升序） 42 7 88 2 67 5 64 6 39 8 76 4 96 1 27 9 81