通常,报表都少不了排名,比如销售业绩排名、学生分数排名等等。今天就来分享一下EXCEL里花样百出的排名公式。
EXCEL常规的排名函数用RANK,它可以称为西式排名法,与之相对的是“中国式排名”。除此之外,还有限定条件的排名,有单个条件也有多个条件。
一、RANK函数公式写法:=RANK(要排名的数字,一组数字,降序or升序)公式说明:返回某数字在一列数字中相对于其他数值的大小排名。第3个参数若为0或忽略表示降序,非零值表示升序。
例1:A1的分数在年级的排名=RANK(C2,$C$2:$C$10)
二、SUMPRODUCT+COUNTIF函数嵌套中国式排名:无论有几个并列第2名,之后的排名仍是第3名,即并列排名不占用名次。
例如:对数列{3,2,2,1}从大到小进行排名,按国际通行的惯例是{第一名,第二名,第二名,第四名},比如奥运会某场比赛出现并列银牌,则不发铜牌。
而按中国式排名,结果是{第一名,第二名,第二名,第三名}
例2:A1的分数在年级按中国式排名
方法1:=SUMPRODUCT(($C$2:$C$10>C2)/COUNTIF($C$2:$C$10,$C$2:$C$10))+1
公式解读:此例中SUMPRODUCT筛选出大于90的个数,COUNTIF统计各个分数出现的次数。
方法2:=SUM(--(FREQUENCY(C$2:C$10,IF(C$2:C$10>=C2,C$2:C$10))>0))
数组公式按CTRL+SHIFT+ENTER三键结束。
三、数据透视表法
EXCEL 2010及以上版本可用数据透视表快递实现中国式排名,无需使用公式。
四、SUMPRODUCT条件排名:
利用SUMPRODUCT多条件计数
=SUMPRODUCT((条件1)*(条件2)*(条件3)*…*(条件n))
例3:A1的分数在自己班级里的排名
=SUMPRODUCT(($A$2:$A$10=A2)*($C$2:$C$10>C2))+1
(就算表中数据未按班级排序也同样能实现)
小编有话说:先分享这些啦,要是你有其他“花样”要排名,欢迎给萌二留言,共同研究更多排名方法。
原创不易,无论内容还是排版都是萌二用心琢磨的,欢迎收藏、分享到朋友圈。
转载请按以下格式标明出处
文章开头请注明以下信息: