【原】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)))

美式2

你可能会发现，美式排名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}

普通公式看一下，就可以看出，我们是把每个数值都和75比较了一次

这个是针对75的，但是我们其实每个数值都要和$B$2:$B$7中的全部数据比较一下大小，才能知道自己的位置

有多少个TRUE，就说明自己排名第几

在EXCEL中，参与四则运算都会让TRUE和FALSE 进行转换

TRUE转为1，FALSE转为0，那么我们只需要SUM一下，

看看结果就知道排名了

到这里你可能就已经明白美式排名了

解析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个结果，对应数组结果如下

也即是71出现了两次，其他都是1次

和下方的普通公式无异，只是我们每个单元格都需要这样去结算所以使用数组

结合解析1和解析2，我们来看看。

75的第一名到底是怎么计算出来的

TRUE是1，FALSE是0，所以结果合计1，也就是第一名

66的第三名是怎么计算出来的？

计算都是一样的，我们就不讲了，看一下比较大小，有3个大于等于66的

同时可以看到有两个71在其中,也就变成了0.5+0.5 =1，没有占据第三名，合计也就是3，排名第三

解析3：SUMPRODUCT的用处？

我们通过小案例来看，1-6分别乘以10，再求和

SUMPRODUCT的作用就是先让他们分别对应相乘，然后再相加

A1:A6*B1:B6 结算结果：{10;20;30;40;50;60}

=SUMPRODUCT({10;20;30;40;50;60}) = 210

最后我们要计算每个数据的排名，就需要把它装到一起

($B$2:$B$7>=B2)/(COUNTIF($B$2:$B$7,$B$2:$B$7))

如果相同，我们就把她变成多少分之一，保证合计是1，不多占位

最后我们使用SUMPRODUCT计算出来的多组数据进行求和，得到的结果就是排名

小结

数组函数，对于新手来说，比较难以理解，但确实函数水平的分水岭，如果想成为一名函数高手，那么数组这关必须过，排名这个公式本身并不复杂，我们只要学会去分解，一切问题就迎刃而解