分享

成绩排名,原来有4种方法!你是不是只知道Rank函数?

2018-01-02  L罗乐


源文件如下,需要对表中的成绩进行排名。


中国式排名(例如,两个人分数一样,并列第1,两个第1,后面就是第2名)

非中国式排名(例如,两个人分数一样,并列第1,两个第1,后面就是第3名)


-01- 中国式排名


方法一:SUMPRODUCT COUNTIF 排名

=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7)) 1。 即在G2单元格输入公式后,同时按ctrl shift enter,然后下拉即可完成。


函数解析

sumproduct有两个英文单词组成,sum是和,product是积,所以是乘积之和的意思。COUNTIF是计数函数。


函数解释

= COUNTIF(B$2:B$7,B$2:B$7) 表示的是分数出现的次数,和=COUNTIF($B$1:$B$7,B1)表达意思是一样的。例如,92分出现的是2次,因此结果是2;其他分数出现的是1次,因此返回的结果是1


在SUMPRODUCT中,数组公式 B$2:B$7>B2 表示的是由TRUE和FALSE组成的逻辑数组{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}转化为1和0的数组{0;0;0;0;0;0}。

因此G2单元格中的公式,=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7)) 1 是对{0;0;0;0;0;0}/{2;1;1;1;1;2} 1求和, 即 0/2 0/1 0/1 0/1 0/1 0/2 1=1

因此G3单元格中的公式,=SUMPRODUCT((B$2:B$7>B3)/COUNTIF(B$2:B$7,B$2:B$7)) 1 是对{1;0;0;0;0;1}/{2;1;1;1;1;2} 1求和, 即 1/2 0/1 0/1 0/1 0/1 1/2 1=2

因此类推即可。

方法二:COUNTIF IF 排名


=SUM(--IF(B$2:B$7>B2,1/COUNTIF(B$2:B$7,B$2:B$7))) 1。同时按ctrl shift enter,然后下拉即可完成。

函数解释

= COUNTIF(B$2:B$7,B$2:B$7) 表示的是分数出现的次数,和=COUNTIF($B$1:$B$7,B1)表达意思是一样的。例如,92分出现的是2次,因此结果是2;其他分数出现的是1次,因此,返回的结果是1。


在if中,数组公式 B$2:B$7>B2 表示的是由TRUE和FALSE组成的逻辑数组{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}转化为1和0的数组{0;0;0;0;0;0}。

因此G2单元格中的公式,=SUM(--IF(B$2:B$7>B2,1/COUNTIF(B$2:B$7,B$2:B$7))) 1是对0 0 0 0 0 0 1=1

因此G3单元格中的公式,=SUM(--IF(B$2:B$7>B3,1/COUNTIF(B$2:B$7,B$2:B$7))) 1 即 1/2 0 0 0 0 1/2 1=2

类推即可。


方法三:MATCH ROW IF 函数



=SUM(--IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1))。同时按ctrl shift enter,然后下拉即可完成。

公式解读

B$2:B$7>=B2,表示的是数组{92; 91; 89;80;82;92}>=92, 表示的是由TRUE和FALSE组成的逻辑数组{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

MATCH(B$2:B$7,B$2:B$7,),表示的是数字第一次出现的行数,因此表示的数组为{1;2;3;4;5;1}

ROW($2:$7)-1 表示的是{2;3;4;5;6;7}-1= {1;2;3;4;5;6}

因此, IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1) 表示为

IF({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{1;2;3;4;5;1}={1;2;3;4;5;6}) =IF({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE})

注意:true表示数值1,false表示数值为0

因此=SUM(--IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1)) = 1


-02- 非中国式排名——rank函数


可以发现这个函数最简单,雷哥就不多解释。 

=RANK (B2, B:B)


各位朋友,你看懂了吗?


-03- 粉丝福利



    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 全屏 打印 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章