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

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

中国式排名(例如，两个人分数一样，并列第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}

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

猜你喜欢