6种Excel表格排名,一个比一个厉害!

来自:Excel技巧精选(微信号:ExcelSkill),作者:技巧妹 

今天技巧妹跟大家分享6种排名操作,非常好用,赶紧收藏备用!


1、降序排名


通常情况下,我们在表格中进行排名是按照数值的大小进行排序,数值大的名次靠前,但如果是对赛跑成绩进行排名呢?我们都知道,赛跑成绩是计算时间的,谁用的时间少,谁的排名就靠前。如下表格,我们要统计比赛名次,这里需要按降序排列,输入公式:=RANK(C2,$C$2:$C$10,1)



说明:RANK函数语法结构为=RANK(数据,引用区域或列表,排序方式),公式里经常省略掉第3参数,省略不写代表升序排列,如果为1则代表降序排列。且RANK函数主要用于西式排名,比如有两人并列第1,接下来就是第3,而中式排名则仍为第2。


2、多表排名


如果数据分布在多张同样结构的表格中,那我们该如何统计比赛名次呢?依次在这些表格内输入公式:=RANK(C2,表1:表3!$C$2:$C$8,1)



说明:公式中输入第2个参数时,先按Shift键选中所有表格再框选数据区域,如果表格里的记录行数不一,可以选取记录行数最多的表格数据区域作为第2个参数的数据区域。


3、多列数据排名


如果数据在同一表格内多列分布,我们又该如何进行排名呢?这里还是可以用RANK函数来解决,C2单元格中输入公式:=RANK(B2,($B$2:$B$5,$E$2:$E$5,$H$2:$H$5)),复制C列公式到F列和I列。



说明:这里利用RANK函数支持联合引用的特性,将三个单元格区域以联合引用运算符(逗号)相隔,并用括号括起来作为RANK函数的第2个参数,统计出得分排名。


4、分组排名


表格内数据按项目组归类排布,如果进行分组排名,可输入公式:

=SUMPRODUCT(($B$2:$B$14=B2)*($C$2:$C$14>C2))+1



说明:这里利用SUMPRODUCT函数进行多条件计数,$B$2:$B$14=B2求出当前单元格所在小组的个数,$C$2:$C$14>C2求出单元格区域中大于当前单元格的个数。统计完成后在得出的数字基础上加上1,就得到了当前单元格在所属组别中的名次。


如果项目组之间数据打乱,也可以添加分组名称进行详细标注,这时我们输入公式:

=B2&"第"&SUMPRODUCT(($B$2:$B$14=B2)*($C$2:$C$14>C2))+1&"名"



5、中国式分组排名:


上面例子中,我们对分组进行排名,出现并列名次时是按照西式的排名方式。如果要根据中国式的分组排名,我们可以输入数组公式:(Ctrl+Shift+Enter 三键输入)

=SUMPRODUCT(($B$2:$B$14=B2)*($C$2:$C$14>=C2)/

COUNTIFS($B$2:$B$14,$B$2:$B$14,$C$2:$C$14,$C$2:$C$14))



说明:这里用到了SUMPRODUCT函数的多条件求和,=SUMPRODUCT((条件1)*(条件2)*…*求和区域)。这里我们可以把公式看成:=SUMPRODUCT(($B$2:$B$14=B2)

*($C$2:$C$14>=C2)*1/COUNTIFS($B$2:$B$14,$B$2:$B$14,$C$2:$C$14,$C$2:$C$14))


  • ($B$2:$B$14=B2)*($C$2:$C$14>=C2)这部分为条件1和条件2,在B列和C列中,对等于B2且C列对应单元格大于C2进行条件判断,满足的话为TRUE,否则为FALSE,两者相乘为1或0,得到是数组结果;

  • COUNTIFS($B$2:$B$14,$B$2:$B$14,$C$2:$C$14,$C$2:$C$14),这里是查找B列到C列中,每一行出现的次数,得到数组结果是{1;1;1;1;1;1;2;1;2;1;1;1;1};

  • 1/COUNTIFS($B$2:$B$14,$B$2:$B$14,$C$2:$C$14,$C$2:$C$14)得到的结果是

       {1;1;1;1;1;1;0.5;1;0.5;1;1;1;1},0.5+0.5=1,得出的结果可以视为区域中不重复项的个数。


符合($B$2:$B$14=B2)*($C$2:$C$14>=C2)这两个条件并统计区域中不重复项的个数,即可得到排名结果。


6、多个权重排名


有时我们也会遇到这样的情况,按照权重统计最终得分再进行排名,如下图所示,输入公式:

=SUMPRODUCT(N($B$2:$B$11*20%+$C$2:$C$11*40%+$D$2:$D$11*40%>=B2*20%+C2*40%+D2*40%))



说明:$B$2:$B$11*20%+$C$2:$C$11*40%+$D$2:$D$11*40%:根据权重统计最终得分,得到数组结果,接着以>=当前行统计的得分为条件进行判断,得到逻辑值TURE或者FALSE,用N函数把逻辑值转化为数值,最后进行求和,即可得出排名结果。

推荐↓↓↓
Excel技巧精选