中国式排名

在生活或工作中
我们经常需要
对某些数据进行排名
特别是在学校中
老师经常需要
对学生的成绩进行排名

而在EXCEL中
有一个专门用于
计算排名的RANK函数
但是它的计算结果
却不符合中国人的排名习惯

例如以下数据
在C2中输入公式
=RANK(B2,B$2:B$13)
出现下列结果

我们可以看到
上述排名是不连续的
由于出现了3个相同的第7名
因此没有了第8名和第9名
直接出现第10名

而这就是RANK函数排名的特征
相同数值在排名中具有相同的名次
并且会占据名次的数字位置

但在中国的排名习惯是
即使有3个并列的第7名
之后的排名仍应是第8名
即并列排名不占用名次
称之为“中国式排名”
就像下图

那么下面
我们就来看看
能够实现中国式排名的
几个公式吧
公式一：
=SUMPRODUCT((B$2:B$13>B2)/COUNTIF(B$2:B$13,B$2:B$13))+1

公式解析：
SUMPRODUCT是多条件求和函数
公式的前半部分“(B$2:B$13>B2)”是返回一个数组：
在B2:B13区域内大于B2单元格数值的个数，这里为1
公式后半部分“/COUNTIF(B$2:B$13,B$2:B$13)”
可表述为“1/COUNTIF(B$2:B$13,B$2:B$13)”
COUNTIF(B$2:B$13,B$2:B$13)的值永远为一个固定值1
所以这个公式可简化为11+1=2
意思就是说在B2:B13区域内比B2大的数有1个
所以排名为2
以此类推
公式一还可写成
=SUMPRODUCT((B$2:B$13>B2)*(1/COUNTIF(B$2:B$13,B$2:B$13)))+1

和
=SUMPRODUCT((B$2:B$13>=B2)/COUNTIF(B$2:B$13,B$2:B$13))

以上3个公式是同一个意思
只是写法不同而已
公式二：
=SUMPRODUCT((B$2:B$13>=B2)*(MATCH(B$2:B$13,B$2:B$13,0)=ROW($1:$12)))

公式解析：
公式的前半部分“(B$2:B$13>=B2)”
判断B2:B13区域中大于等于B2的单元格值有几个
在这里有可能有重复的值
公式后半部分通过
MATCH(B$2:B$13,B$2:B$13,0)=ROW($1:$12)
把重复的值去掉
只保留一个重复的值
这样计算它的排名时就不会重复计算了

公式三：
=SUM(IF(B$2:B$13>=B2,1/COUNTIF(B$2:B$13,B$2:B$13)))

或
=SUM(IF(B$2:B$13>B2,1/COUNTIF(B$2:B$13,B$2:B$13)))+1

公式四：
=COUNT(1/FREQUENCY(IF(B$2:B$13>=B2,B$2:B$13),B$2:B$13))

或
=COUNT(1/FREQUENCY(IF(B$2:B$13>B2,B$2:B$13),B$2:B$13))+1

公式五:
=SUM(—(FREQUENCY(B$2:B$13,IF(B$2:B$13>=B2,B$2:B$13))>0))

公式六：
=SUM(—IF(B$2:B$13>=B2,MATCH(B$2:B$13,B$2:B$13,)=ROW($2:$13)-1))

以上就是
中国式排名的所有公式
特别要注意的是
公式三到公式六都为数组公式
所以输入公式后
要按【Ctrl+Shift+Enter】三键确定