并列排名不占用名次，一定要了解“中国式排名”统计！

Excel情报局

职场联盟Excel

生产挖掘分享Excel基础技能

Excel爱好者大本营

用1%的Excel基础搞定99%的职场问题

做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气

数万Excel爱好者聚集地

SUPER EXCEL MAN

    

1

职场实例

小伙伴们大家好，今天我们来学习一下如何在Excel中对数据进行“中国式排名”的统计。

有的小伙伴不假思索的直接给出Rank函数，其实Rank函数只适用于美式排名，不适用于中国式排名。那究竟什么是中国式排名呢？按照中国人的习惯，无论有几个第2名，都是并列第2名，即并列排名不占用名次，这就是中国式排名。

如下图所示：

为一个公司各个部门的销售量数据，我们想要在C列统计一下B列销售量数据的排名（中国式排名）的情况。比如2部与7部都是487分（最高分），所以分别为第一名与第一名（即并列第一名）。

2

解题思路

解决今天的这个问题，我们需要用到SUMPRODUCT与COUNTIF函数嵌套使用。

我们在C2单元格输入函数公式：

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

敲击回车下拉填充公式即可得到销售量的中国式排名结果。

此组合公式的过程相当于计算B$2:B$9单元格区域中大于等于B2单元格中数值的不重复个数。下面我们分层理解该组合函数的具体含义。

首先使用公式：

=B$2:B$9>=B2

分别比较B2:B9单元格区域中每个单元格中数值与B2单元格中数值的大小。选中公式按下F9键可查看内存数组返回结果：

={TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

在Excel的四则运算当中，逻辑值TRUE和FALSE分别相当于1和0，所以数组：

={TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

可以理解为：

{1;1;0;1;0;0;1;0}

我们继续完善C2单元格中的函数公式为：

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

COUNTIF(B$2:B$9,B$2:B$9)部分用于分别统计B2:B9单元格中每个元素出现的次数，选中该部分公式按下F9键查看内存数组返回结果：

{2;2;1;2;1;1;2;1}

最终则转换为：

{1;1;0;1;0;0;1;0}/{2;2;1;2;1;1;2;1}

用{1;1;0;1;0;0;1;0}除以COUNTIF函数返回的内存数组，也就是说，

“B$2:B$9>=B2”条件成立的时候，就对该数组中对应的元素取倒数，选中公式按下F9键查看内存数组返回结果：

{0.5;0.5;0;0.5;0;0;0.5;0}

对照B2:B9单元格中的数值可以看出，如果数值小于B2单元格中的数值，该部分计算结果为0。

如果数值大于等于B2单元格中的数值，并且仅出现一次，该部分的计算结果为1。

如果数值大于等于B2单元格中的数值，并且出现了多次，则计算出现的次数的倒数。

最后我们再使用SUMPRODUCT函数对数组元素进行求和，得到的就是中国式排名了：

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

回顾关键内容，善用图片表达，学会建立联系，拓展深度广度，浓缩关键概念，应用到行动中，善于归纳总结，尝试进行分享。