成绩分组排名轻松做
作者：王妍
来源：《科技创新导报》2011年第15期
        摘 要:在学校处理学生的考试成绩的时候,经常要反复调用函数,为了更快地处理成绩。本文阐述了RANK复合函数法、RANK+大部分数组法和SUMPRODUCT函数法三种更有效的方法和大家一起探讨。
        关键词:成绩排名RANK复合函数法RANK+大部分数组法SUMPRODUCT函数法
        中图分类号:G64 文献标识码:Ａ 文章编号:1674-098X(2011)05(c)-0205-01
        关于利用EXCEL进行学生成绩排名的问题,报刊、杂志和网上都做了非常深入而全面的探讨,很多作者给出的方法具有相当高的实用价值。有点遗憾的是,在众多解决方案中,却忽视了一个实际问题,即:如何在年级成绩表中快速排出每位考生的班名次。
        一般来讲,在正规的考试中,同年级多个班的学生成绩要统一录入到一个EXCEL工作表中进行综合处理,其中有一项内容就是给考生按成绩排名次,包括每位考生的年级排名和班内排
名。大家都知道,完成这项工作并不困难,RANK函数足以胜任。给考生进行班内排名,也只须先按班级给表格排序,再对每个班分别使用RANK函数即可。问题是,对于教学班很少的年级来说,这种方法的确很实用,然而一些规模较大的学校,一个年级动辄就达到十几个甚至二、三十个教学班rank函数怎么用,显然一味反复使用RANK函数就成了既费时又枯燥的工作了。我们能不能采用一些技巧来大幅提高操作效率呢？笔者经过多年实践,总结出了三种有效的方法供您选择。
        案例:下图是某次考试的全年级成绩表,需要在E列填入每位考生的班名次。为便于说明问题,笔者对成绩表进行了简化,删除了绝大部分数据(图1)。
        方法一:RANK复合函数法
        所谓RANK复合函数法,是指除了RANK函数外,还用到了三个函数:OFFSET,MATCH和COUNTIF。MATCH函数用来到某班第一个学生所在的单元格位置,COUNTIF函数统计该班学生人数,即占用单元格的数量。通过这两个函数,就可以确定该班所覆盖的单元格区域。OFFSET函数返回对这一区域的引用,然后RANK函数对这一区域内的数据进行排序。关于上面提到的几个函数的详细使用方法,可以到EXCEL帮助中去查。
        操作过程:(1)把整个成绩表按班级排序;(2)在E2单元格内输入公式“=RANK(D2,OFFSET(D$1,MATCH(A2,A:A,0)-1,0,COUNTIF(A:A,A2),1))”;(3)拖动填充柄,向下填充单元格,所需要的结果便跃然眼前,并且自动实现了名次按班统计。如图2所示。
        方法二:RANK+大数分组法
        这种方法需要用到一个附加的列,先把数据做一次转换。具体思路是:①把每个考生的成绩按“班号×M＋成绩”的形式进行变换后,填充到一个附加的列中,其中M是您自己任意选取的一个较大的数值,其值大于成绩列中最大的数即可;②对变换后的成绩进行排名,这样既做到了按班分组的目的,又保证了每位考生的成绩在本班内的排名不发生变化;③把排序后的名次转换成班名次。下面操作过程中的COUNTIF(A:A,">"&A2)起到了名次转换的作用,其中“&”用来构造文本型的条件表达式。
        操作过程:(1)在单元格F1中输入字符串“中间结果”;(2)在单元格F2中输入公式:“=A2*1000+D2”,或者输入公式 “=A2*(MAX(D:D)+1)+D2”,然后向下填充单元格;(3)在E2单元格中输入公式:“=RANK(F2,F:F)-COUNTIF(A:A,">"&A2)”,向下填充,排名结果如图3所示;
        １．隐藏F列。
        方法三:SUMPRODUCT函数法
        1.点击E2单元格,输入公式: “=SUMPRODUCT(($A$2:$A$13=$A2)*($D2
        2.向下填充单元格,结果如下图4所示。补充说明:上面公式的意思是,统计班级与A2单元格字符串相同且成绩小于D2中的数值的单元格的个数。“*”符号前后的括号内就是我们设置的条件。如果要设置的条件更多,就再添加“*”,并在其后添加括号,在括号内添加条件即可。