生活工作中我们经常需要进行排名。Excel中有一个专门用于排名的函数：Rank函数。但当存在相同名次时，Rank函数的排名结果并不符合我们中国人的习惯。下图为使用Rank函数排名的结果。有两人并列第2名，第3名排位被占用，成绩“70”排名第4。
在我们的排名习惯习惯中，并列排名不占用名次，因此成绩“70”应该排名第3。
本教程就为大家分享如何进行中国式排名。
一、案例
如下图所示为一张成绩表，现在要求根据B2：B10的成绩进行排名。
方法一、sumproduct+countif函数
在C2单元格输入公式
=SUMPRODUCT(($B$2:$B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1
下拉复制公式。
以B2单元格为例，计算成绩“80”的排名，其实是计算大于80的不重复值的个数。sumproduct+countif组合就是依据这个思路构建公式。
（1）公式 =SUMPRODUCT(1/COUNTIF($B$2:$B$10,$B$2:$B$10))是统计非重复值的个数的经典用法。当数据区域有n个重复值时，1/countif针对这n个重复值会生成n个1/n，相加后结果等于1，这n个重复值只统计一次。
（2）要获得B2单元格成绩的排名，就需要计算大于B2单元格数值的非重复值个数，因此在（1）中经典公式用法的基础上加入逻辑判断公式“$B$2:$B$10>B2”。
（3）当有3个数值大于B2单元格成绩时，B2排名是第4，因此在统计大于B2非重复值个数的基础上加1。
方法二、sum+frequency函数
在C2单元格输入公式
=SUM(--(FREQUENCY($B$2:$B$10,IF($B$2:$B$10>=B2,$B$2:$B$10))>0))
按Ctrl+Shift+Enter结束公式输入，向下拖动填充柄复制公式。
Frequency函数用于统计数据的频率分布。以B2单元格成绩“80”为例，大于80的成绩有85、86、89。以80、85、86、89作为分段点，统计各分数区间人数，如下图所示：
可以看到，每个分数区间至少有1人，当分数相同时，分数区间超过1人。本例有2人成绩为85，因此80-85分数区间人数为2。
当各分数区间人数（即E2：E5单元格）与0相比较时，结果为True(5>0)、True(2>0)、True(1>0)、True(1>0)，4个True进行相加运算时，结果为4，即成绩“80”的排名第4名。
以上解释了使用Frequency+sum函数得出排名的原理。
那么当计算某个成绩的排名时，如何得到大于等于该成绩的分段点呢？IF函数的目的就是生成分段点。IF($B$2:$B$10>=B2,$B$2:$B$10)，当大于等于B2单元格成绩时，取单元格内的值，否则为FALSE，其结果是由大于等于80的数值及FALSE构成的一组数值。
方法三、数据透视表法
1、选中数据区域内任意单元格，点击【插入】-【数据透视表】，将“姓名”字段拖动到【行】区域，将“成绩”字段拖动到【值】区域两次，结果如下：
2、点击“求和项：成绩2”，选择【值字段设置】，在打开的对话框中，选择“值显示方式”为“降序排列”。
点击确定后，即可对成绩排序，结果如下：
这个数据透视表的字段名称不符合要求，比如F列是成绩排名，但字段名称是“求和项：成绩2”，会造成误解，因此需要对透视表字段名进行修改。
3、取消数据透视表汇总，修改字段名称。
数据透视表按照姓名的拼音顺序对成绩进行排序，如果要求不能破坏源数据的排列顺序进行排名，数据透视表法不适用。
联系客服
