首发 公众号:EXCEl办公实战
今天我们来说点谈一下排名的问题,把经典的中式排名公式剖析一下。
我们先来看一下中式排名和美式排名的区别:
简单来说,就是美式相同的排名会占位,比如这里的美式没有第三名,直接第四名,也就是说,他没有真正的并列的意思
中式排名,并列第二,后面应该是第三名,并列不占位。
美式排名1:=RANK(B2,$B$2:$B$7)
美式排名2:=SUMPRODUCT(N($B$2:$B$7>B2))+1
中式排名: =SUMPRODUCT(($B$2:$B$7>=B2)/(COUNTIF($B$2:$B$7,$B$2:$B$7)))
![900260fe9e67a343c5574fc407658599.png](https://i-blog.csdnimg.cn/blog_migrate/4c5c15f5810ea8fe1cfeb85533ba7fbc.jpeg)
美式2
![c2f2290d6017a0d2edb65aa8dd69f2b3.png](https://i-blog.csdnimg.cn/blog_migrate/9d5599070e9afdb2adde2bbf11f8ddd4.jpeg)
你可能会发现,美式排名2的公式跟中式比较,
中国排名多出了一个COUNTIF其他基本一样。
具体我们还是来庖丁解牛:
解析1:$B$2:$B$7>$B2 到底是什么意思?B2=3,B3=4 我们使用B3>B2,大家肯定可以明白,
就是他们两个比较一下大小。
那么这里只是把B3 替换成了换成了多个单元格,结果也由一个结果变成了多个结果
想要知道自己的排名,必须和参与排名的全部数据比较一次,看看有多少比自己大的,
也就是多少是TRUE的,来确定自己的排名。
拿75举例,其他同理按下F9,我们可以看到结果是多个值这里,
75是最大的,所以只有他自己一个返回TRUE,其他都是FALSE
{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
![638511ba9cfeb6e76b63490c564a5932.gif](https://i-blog.csdnimg.cn/blog_migrate/893aae356928722c3c198d287c961bbb.gif)
普通公式看一下,就可以看出,
我们是把每个数值都和75比较了一次这个是针对75的,
但是我们其实每个数值都要和$B$2:$B$7中的全部数据比较一下大小,才能知道自己的位置有多少个TRUE,
就说明自己排名第几在EXCEL中,参与四则运算都会让TRUE和FALSE
进行转换TRUE转为1,FALSE转为0,
那么我们只需要SUM一下,看看结果就知道排名了到这里你可能就已经明白美式排名了
![200d3aca5fd90e9995afb4813b6d6710.png](https://i-blog.csdnimg.cn/blog_migrate/1385f53315d3df8c544c95def4aa0ccb.jpeg)
解析2:COUNTIF($B$2:$B$7,$B$2:$B$7) 何解?
COUNTIF($B$2:$B$7,$B$2:$B$7)难点在于是数组公式,很多新手搞不懂是怎么计算,其实比较简单,
可以拆解成
COUNTIF($B$2:$B$7,$B$2)=1
COUNTIF($B$2:$B$7,$B$3)=2
COUNTIF($B$2:$B$7,$B$4)=2
……
COUNTIF($B$2:$B$7,$B$7)=1
也就是我们把一般的一次性把每个值都计算了一遍,自己在数据中一共出现了几次,结果也是对应的6个结果,对应数组结果如下
![b7cb44018374ebb2067812c6a620fc7c.png](https://i-blog.csdnimg.cn/blog_migrate/cc04352cea852b010f0e1d334642ed17.jpeg)
也即是71出现了两次,其他都是1次和下方的普通公式无异,只是我们每个单元格都需要这样去结算所以使用数组
![163a4204e2ddab78ba83d57191a377b3.png](https://i-blog.csdnimg.cn/blog_migrate/e44d4128c922cca4c1ddc4a6afde2d6f.jpeg)
结合解析1和解析2,我们来看看。
75的第一名到底是怎么计算出来的
TRUE是1,FALSE是0,所以结果合计1,也就是第一名
![5fde6b28f63d464517d2dca45049a425.png](https://i-blog.csdnimg.cn/blog_migrate/8c861c4574e615ec951aacd3fc9874b5.jpeg)
66的第三名是怎么计算出来的?
计算都是一样的,我们就不讲了,看一下比较大小,
有3个大于等于66的同时可以看到有两个71在其中,也就变成了0.5+0.5 =1,
没有占据第三名,合计也就是3,排名第三
![67d96a091855eb19217fdb62bfc56087.png](https://i-blog.csdnimg.cn/blog_migrate/dfb9ef275d38c94effb0d7e5bbd5f1bc.jpeg)
解析3:SUMPRODUCT的用处?我们通过小案例来看,
1-6分别乘以10,再求和SUMPRODUCT的作用就是先让他们分别对应相乘,
然后再相加A1:A6*B1:B6 结算结果:{10;20;30;40;50;60}
=SUMPRODUCT({10;20;30;40;50;60}) = 210
![7ac8d1da2725948caa28afb122d376a4.png](https://i-blog.csdnimg.cn/blog_migrate/4f45e57d9e5f993c6e0957c66ae2e997.jpeg)
最后我们要计算每个数据的排名,就需要把它装到一起($B$2:$B$7>=B2)/(COUNTIF($B$2:$B$7,$B$2:$B$7))
如果相同,我们就把她变成多少分之一,保证合计是1,
不多占位最后我们使用SUMPRODUCT计算出来的多组数据进行求和,得到的结果就是排名
小结
数组函数,对于新手来说,比较难以理解,但确实函数水平的分水岭,如果想成为一名函数高手,那么数组这关必须过,排名这个公式本身并不复杂,我们只要学会去分解,一切问题就迎刃而解
Excel办公实战,高效办公,每天进步一点点!
关注小编,下次精彩不迷路!
喜欢小编的文章,一定要点赞,关注,转发
您的鼓励是小编最大的动力!
--THE END--