718那天,SUT学习交流群里一位北京的小伙伴咨询了关于数据比较大小的问题,问题截图如下:
从上图可以看出,刚开始管理员小S并没有理解小猪的意思,直接给出Rank函数,其实Rank只适用于美式排名,不适用于中国式排名。那究竟什么是中国式排名呢?按照中国人的习惯,无论有几个第2名,都是并列第2名,即并列排名不占用名次,这就是中国式排名。
通过上图可以看出,C列直接使用Rank函数进行排名,得到也就是美式排名,并列排名并占用名次。D列采用连续排名,同样的分数,先找到名次就靠前,可能不太公平。E列到I列是五种方法得到的中国式排名,可以看到分数88都是第2名,78分是第3名。接下来分别为大家介绍这三种排名:
美式排名是这三种排名中最简单的,可以直接使用Rank函数得到。Rank函数的语法说明如下(截图来自微软官方,Excel中F1键):
我仿造的数据C2单元格中的公式为:=RANK(B2,$B$2:$B$100,0),采用降序排列,最后一个参数0可以省略。
问:这里为什么写100?
答:随便写的一个,只要大于数据8即可,因为数据(包含标题)有8行。
前面已经说明了连续排名会导致不公平的状况出现,但这不影响我们学习这项技能,在美式排名的技术上加上Countif函数,即可实现连续排名。如果忘记Countif函数如何使用的小伙伴可以移步【Excel技巧】- 办公常用的十大函数@COUNTIFS函数。
连续排名D2单元格公式为:=RANK(B2,$B$2:$B$100,0) COUNTIF($B$2:B2,B2)-1
这个公式不解释,相比大家也能理解,如果还不理解的可以以此单击【公式】→【公式求值】单步查看哦。
中国式排名的方法有很多,主要介绍第一个方法,其他的方法大家可以下载素材文件自行学习哦。
E2单元格的公式为:{=SUM(IF($B$2:$B$100>B2,1/COUNTIF($B$2:$B$100,$B$2:$B$100))) 1},公式两边的大括号不是手动输入的哦,是通过同时按下Ctrl Shift Enter三个键自动生成的。对数组公式了解的小伙伴一眼就能看穿。
公式这么长,到底是什么意思呢?解释如下(便于解释修改100为8):
COUNTIF($B$2:$B$8,$B$2:$B$8)部分:这是一个数组运算用法,它的运算过程是:
COUNTIF($B$2:$B$8,B2)
COUNTIF($B$2:$B$8,B3)
COUNTIF($B$2:$B$8,B4)
……
分别统计B2、B3、B4单元格在B2:B8区域中出现的次数。得到结果为:1,3,1,3,1,3,1。其中“1”代表此单元格中的内容在B2:B8区域中只出现一次,即没有重复;“3”代表此单元格中的内容在B2:B8区域里重复3次。这一步的操作,可以得到数据是否有重复和以及重复的次数。
1/COUNTIF($B$2:$B$8,$B$2:$B$8)部分:
在公式编辑栏选中这部分公式,按F9键查看运算结果为:{1;0.333333333333333;1;0.333333333333333;1;0.333333333333333;1}。
IF($B$2:$B$8>B2,……)部分:
IF第一参数:$B$2:$B$8>B2的结果是:{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},意思是B2单元格中的内容分别和B2:B8区域内的各个单元格内容进行大小比较。
“IF($B$2:$B$8>B3,1/COUNTIF($B$2:$B$8,$B$2:$B$8))”,这里IF省略了第三参数,因此当得到FALSE时,此时将返回结果“FALSE”,当得到TRUE时,此时将返回对应的结果,得到的结果是{1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。
接着SUM函数对IF函数内的结果进行加总,得到结果“1”。为什么还要再加上“1”呢?原因是IF函数内的测试条件是“>”,对于“B2:B8区域”里的最大值“99”而言,得到的结果是{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},那么SUM函数计算得到的值就是“0”,显然排名第0位,不符合常识,因此要额外加上“1”。
这个中国式排名公式的核心部分就是:“1/COUNTIF($B$2:$B$8,$B$2:$B$8)”,目的是避免重复计算相同项。
联系客服