有很多学校经常进行各种考试,把全年级的成绩集中在一张表上,对所有同学总分进行年级排名,并希望得出每位同学在班级的排名。这个如果分成很多表来做,耗时低效。在总表通过多次排序,填充序列,同样是耗时较长。这个问题可以通过函数来解决,简洁高效。
工具/原料
1
待计算的电子表
2
Excel,或WPS
方法/步骤
1
为了简约起见,笔者对表格进行了简化,每个班取5个人,总分很好求,用在P2处输入=SUM(D2,F2,H2,J2,L2,N2)即可。
2
总分年级排名用一个rank函数就解决了。R2处输入=RANK(P2,P:P)
3
对于班级排名,如果还想使用rank函数,就需要分段设置,数据量大时非常麻烦。很多人用rank函数惯了,可能已经忘记了排名的本质,其实排名就是统计一下比你成绩好的有多少人,而你就是下一个。算班排就是统计同一班级,比你成绩好的有多少人。两个条件,用countifs函数,在Q2输入=COUNTIFS(C:C,C2,P:P,'>'&P2)+1
4
同样方法,可以算出各学科排名,比如语文班排E2可写为=COUNTIFS(C:C,C2,D:D,'>'&D2)+1,为了可以复制方便,可以在一些地方改为绝对引用,写成=COUNTIFS($C:$C,$C2,D:D,'>'&D2)+1
5
复制到到数学班排、外语班排等
6
另外学校准备每班排名前20%的同学为优秀同学,予于表彰,只要计算每位同学在班级排名是否靠前即可,可以在S2使用公式=IF((COUNTIFS(P:P,'>'&P2,C:C,C2)+1)/COUNTIF(C:C,C2)<=20%,'优秀','')即可:
注意事项
对于这个问题也可用sumproduct等函数解决,笔者认为这个是最优美、简洁的写法。
上一篇:如何给孩子选择合适的小学