头部广告

Excel中国式排名,3种方法,你喜欢哪一种!(数据排名专题三)

我是【桃大喵学习记】,欢迎大家关注哟~,每天为你分享职场办公软件使用技巧干货!

前面两期教程分别跟大家分享了《Excel常用排名函数RANK用法介绍》《Excel分组排名,2种方法,任你选!》。今天跟大家分享3种Excel中国式排名方法。

那么,什么是中国式排名呢?

举个例子比如说公司一共有10名员工进行成绩考核,如果9个人考核成绩都是90分,你是89分,按照国际惯用的排名法则:9 个人考核成绩并列第一,你第10名;但是,如果按中国式排名:9 个人考核成绩并列第一,你第2名。所以中国式排名就不能直接RANK函数,RANK函数只适用于美式排名,不适用于中国式排名。

如下图所示,这是一年级学生成绩,我们需要对学生成绩进行中国式排名,我们可以看到赵金龙、孙二娘都是98分,并列第2名,后面的张飞92是第2名。

1.jpg

中国式排名方法一、使用IF函数

方法:

1、开始中国式排名前,首先要对C列“成绩”数据进行降序排列,按成绩从高往低排列,如下图所示

2.gif

2、然后在D2单元格中输入数字1,代表第一行数据成绩排名是1

3.gif

3、然后在D3单元格中输入公式:

=IF(C3=C2,D2,D2+1)

然后点击回车,下拉填充数据即可

4.gif

解读:

上面的公式表示如果上下两行成绩相同,则排名相同,返回上一行成绩的排名;否则,排名就+1递增。

中国式排名方法二、使用SUMPRODUCT+COUNTIF函数组合

方法:

在目标单元格中输入公式:

=SUMPRODUCT((C$2:C$14>C2)*(1/COUNTIF(C$2:C$14,C$2:C$14)))+1

然后点击回车,下拉填充数据即可

5.gif

解读:

该组合函数公式就相当于计算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}

6.gif

因为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},这个数组公式就是统计成绩出现的次数。

7.gif

③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}

8.gif

也就是说如果成绩出现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、点击数据表格中任意单元格→然后单击【插入】-【数据透视表】→在弹出的【创建数据透视表】对话框中“请选择单元格区域”不用动,“放置数据透视表的位置”选中【现有工作表】并且选择放置的单元格→最后点击确定即可,如下图所示

9.gif

2、在右侧的数据透视表中把【字段列表】中要分类汇总字段“姓名”拖到【数据透视表区域】的【行】位置,把“成绩”字段两次拖到【值】位置,如下图所示

10.gif

3、点击透视表中的【求和项:成绩2】表头,然后在编辑栏修改成【排序】→接右键单击数据透视表值区域的任意单元格,选择【值显示方式】-【降序】→在弹出的“值显示方式”对话框中选择默认的“姓名”即可。

11.gif

以上是【桃大喵学习记】今天的干货分享~觉得内容对你有帮助,记得顺手点个赞哟~。我会经常分享职场办公软件使用技巧干货!大家有什么问题欢迎留言关注!


底部广告