你会排名吗?

前言

如果你是一位老师,每次考试后,你做的第一件事可能就是计算学生在班级和级部的排名了。排名这件事看着简单,真正做起来也不容易,如果你不会几个公式,还真搞不定。

昨天就帮一位朋友计算了孩子在班级和全校的排名,看到列表后非常震惊,一个学校同一年级竟然有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

(0)

相关推荐