返回目录：excel表格制作
会让小编更有动力输出高价值的内容
在生活或工作中
我们经常需要
对某些数据进行排名
特别是在学校中
老师经常需要
对学生的成绩进行排名
而在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
所以这个公式可简化为1*1+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】三键确定
因为以上的公式
解析其实都差不多
在这就不再一一解释了
不过
相信聪明的你们
看了前面的解析
后面的也应该都知道了吧
如果还不清楚的话
那就留言告诉小编吧
那这么全的
中国式排名公式
你还不赶快收藏起来慢慢看
关注转发收藏
会让小编更有动力输出高价值的内容