那么,什么是中国式排名呢?
举个例子比如说公司一共有10名员工进行成绩考核,如果9个人考核成绩都是90分,你是89分,按照国际惯用的排名法则:9 个人考核成绩并列第一,你第10名;但是,如果按中国式排名:9 个人考核成绩并列第一,你第2名。所以中国式排名就不能直接RANK函数,RANK函数只适用于美式排名,不适用于中国式排名。
如下图所示,这是一年级学生成绩,我们需要对学生成绩进行中国式排名,我们可以看到赵金龙、孙二娘都是98分,并列第2名,后面的张飞92是第3名。
中国式排名方法一、使用IF函数
方法:
1、开始中国式排名前,首先要对C列“成绩”数据进行降序排列,按成绩从高往低排列,如下图所示
2、然后在D2单元格中输入数字1,代表第一行数据成绩排名是1
3、然后在D3单元格中输入公式:
=IF(C3=C2,D2,D2+1)
然后点击回车,下拉填充数据即可
解读:
上面的公式表示如果上下两行成绩相同,则排名相同,返回上一行成绩的排名;否则,排名就+1递增。
中国式排名方法二、使用SUMPRODUCT+COUNTIF函数组合
方法:
在目标单元格中输入公式:
=SUMPRODUCT((C$2:C$14>C2)*(1/COUNTIF(C$2:C$14,C$2:C$14)))+1
然后点击回车,下拉填充数据即可
解读:
该组合函数公式就相当于计算C$2:C$14单元格区域中大于等于C2单元格中数值的不重复个数,下面我们分步解读该组合函数公式的具体含义。
①组合公式中(C$2:C$14>C2)意思就是分别比较C2:C14单元格区域中每个单元格中数值与C2单元格中数值的大小。选中公式按下F9键可查看内存数组返回的结果是一个由TRUE和FALSE组成的逻辑数组:
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
因为TRUE相当于1,FALSE相当于0,也就相当于一组1和0组成的数组,也可以把返回的结果理解为:
{0;0;1;0;0;0;0;0;0;0;0;0;0}
②COUNTIF(C$2:C$14,C$2:C$14),用于分别统计C2:C14单元格中每个元素出现的次数,然后按F9键,我们可以看到获得是一个数组{1;2;2;1;1;1;1;1;2;2;1;1;1},这个数组公式就是统计成绩出现的次数。
③1/COUNTIF(C$2:C$14,C$2:C$14) 实际获得的数组是{1;0.5;0.5;1;1;1;1;1;0.5;0.5;1;1;1}
也就是说如果成绩出现1次,那我们就计数为1;
如果成绩出现2次,那我们就计数为1/+1/2=1;
如果成绩出现3次,那我们就计数为1/3+1/3+1/3=1,这样依此类推,也就是不管重复几次,最后结果都只统计1次
④最后用SUMPRODUCT函数对数组元素进行求和,最后加1,就是比自己大的个数 +1,即自己的排名
大家可能觉得这个公式逻辑比较复杂不容易理解,大家可以直接套用,把里面的参数替换成自己的,需要主要是成绩区域C$2:C$14是锁行不锁列,就是选择成绩区域后按2次F4建即可。
中国式排名方法三、使用数据透视表
方法:
1、点击数据表格中任意单元格→然后单击【插入】-【数据透视表】→在弹出的【创建数据透视表】对话框中“请选择单元格区域”不用动,“放置数据透视表的位置”选中【现有工作表】并且选择放置的单元格→最后点击确定即可,如下图所示
2、在右侧的数据透视表中把【字段列表】中要分类汇总字段“姓名”拖到【数据透视表区域】的【行】位置,把“成绩”字段两次拖到【值】位置,如下图所示
3、点击透视表中的【求和项:成绩2】表头,然后在编辑栏修改成【排序】→接右键单击数据透视表值区域的任意单元格,选择【值显示方式】-【降序】→在弹出的“值显示方式”对话框中选择默认的“姓名”即可。