你会排名吗?
前言
如果你是一位老师,每次考试后,你做的第一件事可能就是计算学生在班级和级部的排名了。排名这件事看着简单,真正做起来也不容易,如果你不会几个公式,还真搞不定。
昨天就帮一位朋友计算了孩子在班级和全校的排名,看到列表后非常震惊,一个学校同一年级竟然有48个班,每个班平均50人,也就是一个级部就2400左右。不光总分排名,单科也要排名,3+3随机组合排名,如果不借助EXCEL公式,真不知道如何做了!
我们简化一下,只为说明公式的用法:
1级部排名
级部排名比较简单,我们记住一个函数就行了,那就是RANK函数
RANK(分数,分数列,0)
最后一个参数:0表示正常排名,即最高分为第一名;1表示倒排,最低分为第一名
D2=RANK(C2,$C$2:$C$27,0),即C2在区域C2:C27数据中的排名
注意:分数区域C2:C27要采用绝对引用$C$2:$C$27,或混合引用C$2:C$27,这样D2的公式才可以向下填充或复制。
2班级排名
班级排名如果还用Rank函数就会比较累,因为每个班级的分数区域是不同的,因此公式不能直接下拉填充或复制,如:
E2=RANK(C2,$C$2:$C$10,0),E2的公式可以复制到E10
E11=RANK(C11,$C$11:$C$16,0),E11的公式可以复制到E16
依次类推,如果有48个班,你就需要改动分数区域48次,每改动一次,下拉一次填充公式。
如果加大难度把班级打乱的话,RANK函数直接就无法使用。
3班级排名加强版
解决方法:
F2=SUMPRODUCT(($A$2:$A$27=A2)*($C$2:$C$27>C2))+1,然后下拉公式即可。
前面的文章曾多次讲过SUMPRODUCT这个函数,这是一个多条件计数和求和的函数
用法:
计数:SUMPRODUCT((条件1)*( 条件2)*( 条件n))
求和:SUMPRODUCT((条件1)*( 条件2)*( 条件n)*(求和区域))
公式解释:SUMPRODUCT(($A$2:$A$27=A2)*($C$2:$C$27>C2))+1
条件1:$A$2:$A$27=A2
条件2:$C$2:$C$27>C2
两个条件均成立,则返回1*1=1,否则只要有一个不满足就返回1*0,或0*1,或0*0,结果都是0,SUMPRODUCT把这些返回值逐一加起来,就是班级相同,且分数大于C2的人数,再加上1就是C2的排名。
你明白了吗?
知识点
RANK(数值,区域,1/0)
SUMPRODUCT(($A$2:$A$27=A2)*($C$2:$C$27>C2))+1
END