Excel中实现中国式排名的两种方法（函数及数据透视表）

我们都知道Excel中有一个排序的函数RANK，它可以求某一个数值在某一区域内的排名，其使用方法也非常简单，如要对某班的考试成绩进行排名，如下：

I3单元格的函数为：=RANK(H3,$H$3:$H$21,0)，使用起来十分方便。

小贴士：

RANK函数的语法 =RANK(排序数字，区域，排序方式)。

但是细心的朋友可能会发现，该排名不符合我们中国人的排名方式，当出现相同的成绩时，下一个人的排名对自动加上了相同人数数减1，如图中的名次3、6、16都是空缺的，但是在中国人的排名习惯中，无论出现几个第2名，下一个人依然是第3名，即并列的排名不占用名次。对上述成绩表，中国式的排名结果应该如下所示：

下面介绍实现以上排名方式的两种方法：

一、函数公式法

I3单元格的函数为：=SUMPRODUCT((H$3:H$21>$H3)/COUNTIF(H$3:H$21,H$3:H$21))+1，该公式为数组公式，需要按Ctrl+Shift+Enter组合键结束。

公式解析：

SUMPRODUCT是多条件求和函数，里面的参数“H$3:H$21>$H3”返回的是一个数组在“H$3:H$21”区域内大于“$H3”数值的个数，后半部分“/COUNTIF(H$3:H$21,H$3:H$21)”可表达为“*1/COUNTIF(H$3:H$21,H$3:H$21)”，COUNTIF可以统计不重复值的个数，实现踢除重复值后的成绩排名。

另外，还可以使用FREQUENCY 函数，则I3单元格的函数为：=SUM(--(FREQUENCY(H$3:H$21,IF(H$3:H$21>=$H3,H$3:H$21))>0))。

小贴士：

FREQUENCY函数的含义是以一列垂直数组返回一组数据的频率分布，其语法为：=FREQUENCY(data_array,bins_array)

Data_array 是一组数值，然后根据Bins_array 中对 data_array 中的数值进行分组的情况，统计频率。“IF(H$3:H$21>=$H3,H$3:H$21)”得到的结果为数组，其中大于等于H3的为原值，其余的显示为FALSE。FREQUENCY函数统计出IF的结果在H$3:H$21中的分布频率，如果频率大于0，结果为TRUE，否则结果为FALSE。“--” 的作用是将文本型、逻辑型的数值转换位数字型数值，这个在函数中经常用到，大家稍微留心一下。

以上的公式都比较复杂，建议大家使用“公式”菜单下的“公式求值”功能查看一下各个步骤的结果，以加强理解。

二、数据透视表

在“插入”菜单下选择“数据透视表”，如图：

字段设置如下：

然后在求和项2中右击，选择“值显示方式”为“降序排列”。

这样排名结果就出来了，可以将排名结果复制回原来的表格。

以上两种方式，相对来说数据透视表的方式要容易理解，推荐大家使用。

如果你想要获取本示例中的演示素材，请关注微信公众号：Excel高效办公，然后回复“Excel素材”。