Excel RANK函数和其他计算排名的方法

该教程解释了Excel排名函数的特殊性，并展示了如何在Excel中根据多个标准进行排名，按组进行数据排名，计算百分位数排名等。

当你需要确定一个数字在一个数字列表中的相对位置时，最简单的方法是按升序或降序对数字进行排序。 如果由于某种原因无法进行排序，Excel中的Rank公式是完成这项工作的完美工具。

在Excel中，RANK函数返回一个数值与同一列表中其他数值相比的顺序（或等级）。 换句话说，它告诉你哪个数值是最高的，哪个是第二高的，等等。

在一个排序的列表中，某个数字的等级就是它的位置。 Excel中的RANK函数可以从最大的数值（如按降序排序）或最小的数值（如按升序排序）开始确定其等级。

Excel RANK函数的语法如下。

在哪里？

数量 (必填)--你想找到其等级的值。

参考资料 (required) - 一个要进行排名的数值列表。 它可以作为一个数字数组或数字列表的引用来提供。

秩序 (可选) - 一个数字，指定如何对数值进行排序。

RANK.EQ是RANK函数的改进版，在Excel 2010中引入。 它的语法与RANK相同，工作逻辑也相同：如果几个值的排名相同，则将最高排名分配给所有这些值。 EQ代表 "相等"）。

在Excel 2007和更低版本中，你应该始终使用RANK函数。 在Excel 2010、Excel 2013和Excel 2016中，你可以使用RANK或RANK.EQ。然而，使用RANK.EQ是明智的，因为RANK随时可能被停止使用。

RANK.AVG是另一个在Excel中查找排名的函数，只在Excel 2010、Excel 2013、Excel 2016及以后的版本中可用。

它的语法与其他两个函数相同。

不同的是，如果一个以上的数字具有相同的等级，则 平均等级 被返回（AVG代表 "平均"）。

要想对Excel中的数据排名有更多的了解，请看一下这个截图。

这三个公式都将B列中的数字排在了 降序 (该 秩序 论点省略）。

在所有版本的Excel 2003 - 2016中。

=rank($b2,$b$2:$b$7)

在Excel 2010 - 2016中。

=rank.eq($b2,$b$2:$b$7)

=rank.avg($b2,$b$2:$b$7)

区别在于这些公式如何处理重复的数值。 如你所见，同一个分数在单元格B5和B6中出现了两次，这影响了后续的排名。

因此，为了更好地学习如何在Excel中单独或与其他函数结合使用RANK函数，让我们来解决几个现实生活中的问题。

如上例所示，要将数字从高到低排序，你可以使用Excel排名公式中的一个与 秩序 参数设置为0或省略（默认）。

要有 数 与其他数字相比，排序为 升序 ，在可选的第三个参数中放入1或任何其他非零值。

例如，要对学生的100米短跑时间进行排名，你可以使用以下任一公式。

=rank(b2,$b$2:$b$7,1)

=rank.eq(b2,$b$2:$b$7,1)

请注意，我们将该范围锁定在 参考文献 参数使用绝对的单元格引用，这样，当我们把公式复制到下一列时，它就不会改变。

结果，最低的值（最快的时间）被排在第1位，最大的值（最慢的时间）得到最低的排名6。相同的时间（B2和B7）被赋予相同的排名。

正如前面所指出的，所有的Excel排名函数对于同等价值的项目都返回相同的排名。 如果这不是你想要的，使用以下公式之一来解决平局的情况，并给出一个 独特的等级 到每个数字。

要将我们的学生的数学分数按降序排列，请使用这个公式。

=rank.eq(b2,$b$2:$b$7)+countif（$b$2:b2,b2）-1

要将100米比赛的结果按升序排列，并且没有重复的，请使用这个公式。

=rank.eq(b2,$b$2:$b$7,1) + countif($b$2:b2,b2)-1

这些公式是如何工作的

正如你可能已经注意到的，两个公式之间的唯一区别是 秩序 RANK.EQ函数的参数：省略是将数值降序排列，1是升序排列。

在这两个公式中，都是COUNTIF函数巧妙地使用了相对和绝对单元格引用，简而言之，你用COUNTIF来找出被排定的数字在上述单元格中的出现次数，包括该数字的单元格。 在你输入公式的最上面一行，范围包括一个单元格（$B$2：B2）。 但由于你只锁定了因此，对于第7行，范围扩大到$B$2:B7，B7中的值将与上述每个单元格进行比较。

因此，对于所有第1次出现的情况，COUNTIF返回1；你在公式的最后减去1以恢复原来的等级。

对于第二次出现的情况，COUNTIF返回2。 通过减去1，你的排名增加了1分，从而防止重复。 如果碰巧有3个相同的值出现，COUNTIF()-1将在他们的排名中增加2，以此类推。

另一种在Excel中对数字进行唯一排序的方法是将两个COUNTIF函数相加。

例如，如果要将数字从高到低进行唯一的排名，你会使用这个公式。

=COUNTIF($B$2:$B$7,";";$B2)+COUNTIF($B$2:B2,B2)

如下面的截图所示，平局被成功解决，并为每个学生分配了一个独特的等级。

前面的例子已经展示了Excel RANK平局情况下的两种有效解决方案。 然而，仅仅根据他们在列表中的位置，对相同的数字进行不同的排名，这似乎是不公平的。 为了改善你的排名，你可能想增加一个标准，在平局情况下考虑。

在我们的样本数据集中，让我们把C列中的总分相加，并计算出排名，如下所示。

要做到这一点，我们将使用常规的RANK/RANK.EQ公式来寻找排名，并使用COUNTIFS函数来打破平局。

=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7, ";";$C2)

与上面的例子相比，这个排名公式更加客观：Timothy排名第二，因为他的总分比Julia的高。

公式中的RANK部分是显而易见的，COUNTIFS函数的作用如下。

因为COUNTIFS是用AND逻辑工作的，也就是说，只计算满足所有指定条件的单元格，所以对Timothy来说，它返回0，因为没有其他数学成绩相同的学生的总分更高。 因此，Timothy由RANK.EQ返回的排名不变。 对Julia来说，COUNTIFS函数返回1，因为有一个数学成绩相同的学生的总分更高，所以她的排名数字为如果再有一个学生的数学成绩相同，但总分低于蒂莫西和朱莉娅，他/她的排名就会增加2，以此类推。

你可以使用COUNTIF来检查主要标准，并使用COUNTIFS或SUMPRODUCT来解决平局问题，而不是使用RANK或RANK.EQ函数。

=COUNTIF($B$2:$B$7,";";$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7, "; ";$C2)+1

=COUNTIF($B$2:$B$7,";";B2)+SUMPRODUCT(--（$C$2:$C$7=C2）,--（$B$2:$B$7;B2）)+1

这些公式的结果与上面所示完全相同。

在统计学中，一个 百分值 (或 百分位数 例如，如果70%的学生等于或低于你的考试分数，你的百分位数排名就是70。

要想在Excel中获得百分位数排名，请使用RANK或RANK.EQ函数，并将非零的 秩序 参数对数字从小到大进行排序，然后用该排序除以数字的数量。 因此，通用的 Excel百分位数排名公式 情况如下。

为了计算我们的学生的百分位数排名，公式采取以下形式。

=rank.eq(b2,$b$2:$b$7,1)/count($b$2:$b$7)。

为使结果正确显示，请确保将 百分比 格式到公式单元格。

在你需要对不相邻的单元格进行排序的情况下，直接在 参考文献 例如，你的Excel等级公式的参数以引用联盟的形式出现，用$符号锁定引用。

=rank(b2,($b$2,$b$4,$b$6))

为了防止非排名单元格的错误，在IFERROR函数中包裹RANK，像这样。

=iferror(rank(b2,($b$2,$b$4,$b$6)), "" )

请注意，虽然单元格B5不包括在公式中，但重复的数字也被赋予一个等级。

如果你需要对多个不相邻的单元格进行排序，上述公式可能会变得太长。 在这种情况下，一个更优雅的解决方案是定义一个命名的区域，并在公式中引用该名称。

=IFERROR(RANK(B2,range), "")

当处理组织成某种数据结构的条目时，数据可能属于不同的组，你可能想对每组中的数字进行单独排名。 Excel的RANK函数不能解决这个难题，所以我们要使用一个更复杂的SUMPRODUCT公式。

按组排名，在 下降 秩序。

=SUMPRODUCT((A2=$A$2:$A$7)*（C2;$C$2:$C$7）)+1

按组排名，在 升序 秩序。

=SUMPRODUCT((A2=$A$2:$A$7)*(C2; $C$2:$C$7))+1

在哪里？

在这个例子中，我们用第一个公式将每组的数字从大到小排列。

这个公式如何运作

基本上，该公式评估了2个条件。

因为在微软的Excel术语中，TRUE=1，FALSE=0，所以将两个数组相乘得到一个1和0的数组，其中只有两个条件都满足的行才会返回1。

然后，SUMPRODUCT将1和0数组中的元素相加，因此每组中最大的数字返回0。 然后你在结果中加上1，从1开始排队。

将组内的数字从小到大排序的公式( 升序 秩序 不同的是，SUMPRODUCT对某组中最小的数字返回0，因为该组中没有数字符合第二条件(C2;$C$2:$C$7)。 同样，你通过在公式结果中添加1来用第一等级取代零等级。

你可以使用SUM函数将数组元素相加，而不是SUMPRODUCT，但是这需要使用数组公式，通过Ctrl + Shift + Enter完成。 例如：:

=SUM((A2=$A$2:$A$7)*(C2;$C$2:$C$7))+1

如果你的数字列表中同时包含正数和负数，Excel的RANK函数会马上对它们进行排名。 但如果你想让正数和负数分别进行排名呢？

对于A2至A10单元格中的数字，使用以下公式之一来获得正值和负值的单独排名。

=IF($A2;0,COUNTIF($A$2:$A$10,";";A2)+1,"")

=IF($A2;0,COUNTIF($A$2:$A$10,";0")-COUNTIF($A$2:$A$10,";";$A2)，""

=IF($A2;0,COUNTIF($A$2:$A$10,";0")-COUNTIF($A$2:$A$10,";";$A2)，""

=IF($A2;0,COUNTIF($A$2:$A$10,";";$A2)+1,"")

结果将看起来与此类似。

这些公式是如何工作的

首先，让我们来分析一下正数的排名公式，在 下降 秩序。

在这个例子中，A2包含第二高的正数，COUNTIF返回1，这意味着只有一个数字比它大。 为了从1而不是0开始排名，我们在公式结果中加上1，所以A2的排名是2。

对正数进行排序的公式是 升序 顺序的工作方式有点不同。

如果这个数字大于0，第一个COUNTIF得到数据集中正数的总数，第二个COUNTIF找出比这个数字高的数值。 然后，你用后者减去前者，得到想要的等级。 在这个例子中，有5个正数，其中1个大于A2。 所以，你从5中减去1，从而得到A2的等级为4。

对负数进行排序的公式也是基于类似的逻辑。

注意：上述所有公式 忽略零值 因为0既不属于正数集合，也不属于负数集合。 要在你的排名中包括零，在公式逻辑要求的地方分别替换;0和=0以及;=0。

例如，将正数和零从大到小排序，使用这个公式。 =IF($A2;=0,COUNTIF($A$2:$A$10,";";A2)+1,"")

你已经知道，Excel的RANK公式可以处理所有的数字：正数、负数和零。 但在某些情况下，我们只想对忽略0值的数据的单元格进行排序。 在网上，你可以找到一些可能的解决方案，但Excel的RANK IF公式，我认为是最通用的一种。

=IF($B2=0,"",IF($B2;0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))

=IF($B2=0,"",IF($B2;0,RANK($B2,$B$2:$B$10,1) - COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1))

其中B2:B10是要进行排名的数字范围。

这个公式最好的一点是，它对正数和负数都能很好地工作，把零值排除在排名之外。

这个公式如何运作

乍一看，这个公式可能有点棘手。 仔细一看，其逻辑非常简单。

下面是Excel RANK IF公式如何将数字从大到小排列，忽略零。

if($b2=0,"", ...)

IF($B2;0,RANK($B2,$B$2:$B$10),...)

rank($b2,$b$2:$b$10)-countif（$b$2:$b$10,0）)

是的，就这么简单！将数字从小到大排列的公式也是类似的方式，推导其逻辑可能是一个很好的脑力锻炼 :)

当处理一个正负值的列表时，可能需要按照数字的绝对值进行排序，忽略符号。

这个任务可以用下面的一个公式来完成，其核心是ABS函数，它返回一个数字的绝对值。

=SUMPRODUCT((ABS(A2);=ABS(A$2:A$7)) * (A$2:A$7"")) - SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7"") )+1

=SUMPRODUCT((ABS(A2);=ABS(A$2:A$7)) * (A$2:A$7"")) - SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7"") )+1

结果是，负数的排名就像正数一样。

如果你希望得到一个实际的N个最大或最小的值，而不是它们的排名，请分别使用LARGE或SMALL函数。

例如，我们可以用这个公式得到学生的前3个分数。

=large($b$2:$b$7, $d3)

其中B2:B7是分数列表，D3是期望的排名。

此外，你可以通过使用INDEX MATCH公式来检索学生的名字（前提是前三名中没有重复的分数）。

=index($a$2:$a$7,match(e3,$b$2:$b$7,0))

同样地，你可以使用SMALL函数来拉出底部的3个值。

=small($b$2:$b$7, $d3)

这就是在Excel中做排名的方法。 为了更好地理解并可能逆向设计本教程中讨论的公式，欢迎下载我们的Excel样本排名工作簿。

我感谢你的阅读，希望下周在我们的博客上看到你!