排名,原来有4种方式!你只知道秩函数吗?
源文件如下,表中分数需要排序。
中国式排名(例如,两个得分相同的人并列第一名,两个第一名之后是第二名)
非中文排名(例如,两个人得分相同,并列第1名,两个第1名,然后是第3名)
-01-中国排名
方法一:SUMPRODUCT+ COUNTIF排名
= sum product((B $ 2:B $ 7 & gt;B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 .即在单元格G2输入公式后,同时按ctrl+shift+enter,然后下拉完成。
功能分析
Sumproduct由两个英文单词组成,sum是sum,product是product,所以表示乘积的和。COUNTIF是一个计数函数。
功能解释
= COUNTIF(B$2:B$7,B$2:B$7)表示分数出现的次数,与= COUNTIF($ B $ 1:B $ 7,B1)相同。比如92分出现两次,所以结果是2;其他分数出现一次,所以返回的结果是1。
在SUMPRODUCT中,数组公式b $2: b $7 >: B2表示TRUE和FALSE的逻辑数组{ FALSE假的;假的;假的;假的;FALSE}}转换为1和0的数组{ 0;0;0;0;0;0}。
因此,单元格G2 = sum product((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/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+0/1+1/2+1 = 2。
所以可以类比。
方法二:COUNTIF+ IF排名
= SUM(& # 8211;IF(B $ 2:B $ 7 & gt;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;其他分数出现一次,所以返回的结果是1。
在if中,数组公式b $2: b $7 >: B2表示TRUE和FALSE的逻辑数组{ FALSE假的;假的;假的;假的;FALSE}}转换为1和0的数组{ 0;0;0;0;0;0}。
所以G2单元格中的公式,= SUM(& # 8211;IF(B $ 2:B $ 7 & gt;B2,1/countif (b $2: b $7,b $2: b $7))+1表示0+0+0+0+0+1 = 1。
因此,单元格G3中的公式,= SUM(& # 8211;IF(B $ 2:B $ 7 & gt;B3,1/countif (b $2: b $7,b $2: b $7))+1表示1/2+0+0+0+1/2+1 = 2。
以此类推。
方法三:匹配+行+IF函数
= SUM(& # 8211;IF(B $ 2:B $ 7 & gt;=B2,MATCH(B$2:B$7,B$2:B$7,)= ROW($ 2:7)-1).同时按ctrl+shift+enter,然后下拉完成。
公式的解释
B $ 2:B $ 7 & gt;=B2,表示数组{ 92;91;89;80;82;92 } & gt=92,表示由TRUE和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}
因此,如果(b $2: b $7 >: = B2,match (b $2: b $7,b $2: b $7,)= row ($2: $7)-1)表示为
IF({ TRUE;假的;假的;假的;假的;TRUE}、{ 1;2;3;4;5;1}={1;2;3;4;5;6 })= IF({ TRUE;假的;假的;假的;假的;TRUE}、{ TRUE真实;真实;真实;真实;FALSE})
注意:true表示值1,false表示值0。
因此= SUM(& # 8211;IF(B $ 2:B $ 7 & gt;=B2,MATCH(B$2:B$7,B$2:B$7,)= ROW($ 2:7)-1))= 1
-02-非中文排名-排名函数
可以发现这个函数是最简单的,所以格雷就不多解释了。
=等级(B2,B:B)
朋友,你明白吗?