Excel 排名公式：包含示例的全面指南

排名可以让您比较数值并识别数据集中的模式。Excel 提供了强大的工具，如RANK()、RANK.EQ() 和 RANK.AVG() 来简化排名工作。无论是按积分为体育赛事的参赛者排名，还是基于排序后的销售数据分析市场趋势，这些函数都很有用。

本文将介绍 Excel 中的三种排名函数，并通过上手示例解释如何使用它们。我们还将探讨将排名函数与其他 Excel 函数结合使用以及如何计算百分位排名。

快速解答：如何在 Excel 中计算排名

要在目标数据集中计算排名，请使用以下公式：

=RANK(number, ref, [order])

理解 Excel 中的 RANK() 函数

假设您组织了一场学生竞赛，现在需要根据表现为上千名学生排名。

您会花数小时手动整理数据吗？不必，因为 Excel 可以帮您。其众多强大函数之一是 RANK()，只需轻点一下就能确定某人的名次。

要计算某个数值在一组数据中的排名，输入公式：

RANK(number, ref, [order])

在上述公式中：

• number：要排名的数值。
• ref：进行排名时考虑的数值范围。
• order：指定排序方式。升序用“1”；降序用“0”（默认）。

如何在 Excel 中使用 RANK() 公式

下面通过实际示例来应用 RANK() 函数：

学生成绩排名

当您有成千上万名学生时，计算成绩会既困难又耗时。假设您正在准备期末成绩单，并希望根据分数为学生排名。

为此，先准备好数据集。

Excel 中的学生示例数据集。来源：作者配图。

在一个单元格中输入公式 =RANK(B3,($B$3:$B$7)) ，然后向下拖动填充到最后一个有数据的单元格即可得到排名。

使用 RANK() 函数计算学生排名。来源：作者配图。

现在您只需一次点击就得到了学生的名次。

但是您是否注意到，两位分数相同的学生都得到第 4 名？下一位 23 分的学生直接是第 6 名，跳过了第 5 名。这意味着该公式会为重复分数分配相同的名次。

这可能会打乱接近分数的排序。但您可以使用 COUNTIF() 函数来打破并列。输入以下公式：

=RANK(B3,($B$3:$B$8)) + COUNTIF($B$3:B3, B3)-1

该 COUNTIF() 函数会针对重复值调整名次。它会统计当前学生的分数在其上方出现了多少次。

在 Excel 中使用 COUNTIF() 打破并列。来源：作者配图。

数值 28 出现了两次，COUNTIF() 会跟踪出现次数。该次数用于调整名次：公式将“出现次数减一”加到原始名次上，从而确保每个重复值获得唯一的名次。

为数据集中的销售额排名

我们再看一个示例，展示如何以升序或降序使用 Excel 的 RANK() 函数。

如果您想评估销售团队的表现，可以使用 Excel 的 RANK() 函数为他们的月度销售额排名。

这里我们有每位销售的月销售额数据集。我们将使用 RANK() 函数，按贡献的销售额从高到低为他们排名。

需要排名的销售额列表。来源：作者配图。

若要按升序排名，我们使用了公式 =RANK(B2, ($B$2:$B$6), 1)。其中，参数 order 的值为“1”，表示按升序进行排名。

按升序为数值排名。来源：作者配图。

输入公式后，按住光标，拖动单元格角到列尾，将其应用到其余销售数据。

同样地，若要按降序排名，请将公式中的 order 参数值写为“0”，例如：=RANK(B2, ($B$2:$B$6), 0)

您可以看到结果的差异：

按降序为数值排名。来源：作者配图。

您可能注意到引用 ($B$2:$B$6) 中的美元符号。这称为绝对引用。它确保公式在向下复制时始终引用相同的销售数据范围，避免出错。

如果不使用绝对引用，例如 =RANK(A2, A2:A10)，当您向下拖动时，范围会发生变化：

• G3 中的公式： =RANK(G3,(G3:G7), 1)
• G4 中的公式： =RANK(G4,(G4:G8), 1)

为防止该问题，请按 “F4” 使用绝对引用。此时公式为： =RANK(G3,($G$3:$G$7), 1)

当您将上述公式拖到最后一个有数据的单元格时，相对引用会根据各单元格位置改变。使用绝对引用后，您可以看到结果的不同：

使用与不使用绝对引用时的排名差异。来源：作者配图。

高级排名函数：RANK.EQ() 与 RANK.AVG()

当您在 Excel 中遇到重复值时，事情会变得棘手。这时我更喜欢使用 RANK.EQ() 和 RANK.AVG()。它们在处理方式上略有不同。下面看看它们如何工作。

RANK.EQ() 函数

RANK.EQ() 的用法与常规 RANK() 相同，但命名更精确。当存在重复值时，RANK.EQ() 会为它们分配相同的名次，并跳过后续名次。

语法： =RANK.EQ(number, ref, [order])

在上述公式中：

• number：要排名的数值。
• ref：进行排名时考虑的数值范围。
• order：指定排序方式。升序用“1”；降序用“0”（默认）。

通过一个示例来理解该公式：我有一个包含多个国家人口的数据集，想根据人口为这些国家排名。

以下是我使用 RANK.EQ() 的做法：

使用 RANK.EQ() 函数。来源：作者配图。

在上图中，人口最多的国家排名为 1，依次类推。现在，您可以看到 RANK.EQ() 如何处理并列。

由于美国与印度尼西亚的人口相同，它们共享第 2 名。这清楚地说明了 RANK.EQ() 会为重复值分配相同名次，并跳过下一个名次。

RANK.AVG() 函数

与处理并列不同，RANK.AVG() 采取另一种方式：它计算这些重复值各自应得名次的平均值，而不是为它们分配相同名次。

语法： RANK.AVG(number, ref, [order])

在上述公式中：

• number：要排名的数值。
• ref：进行排名时考虑的数值范围。
• order：指定排序方式。升序用“1”；降序用“0”（默认）。

通过一个示例来理解：我有一组分数数据，于是应用了 RANK.AVG() 来看看它如何处理并列分。

使用 RANK.AVG() 函数。来源：作者配图。

通过 =RANK.AVG(D3,$D$3:$D$6)，分数 88 得到的名次是 2.5。这意味着该函数对本应分别赋予的名次取平均值。

这种方法能在存在频繁并列的数据集中保持更均衡的名次分布。

将 RANK() 与其他 Excel 函数结合

将 RANK() 与其他 Excel 函数配合，在某些情境下非常有用。我们来看看几个常见用例。

结合 RANK() 和 ROUNDUP() 对数据分组

以下展示如何将 ROUNDUP() 与 RANK() 结合：

在 Excel 中结合 RANK() 与 ROUNDUP()。来源：作者配图。

我使用了公式 =ROUNDUP(RANK(B2, $B$2:$B$7, 1)/3, 0)，它会在 B2:B7 范围内按升序计算 B2 的排名，然后将该名次除以 3，并向上取整为最接近的整数。

使用上述公式，您可以有效地将数据划分为不同的组或层级，有助于识别与分析不同的表现水平，或为进一步分析进行分类。

结合 RANK.EQ() 和 COUNTIFS() 打破并列

我们已经讨论了使用 Excel 的 RANK() 函数处理并列的几种方法。不过，假设您只想按价格为产品排名。当多件商品价格相同时，这可能显得武断。

因此，我们引入次要条件来打破并列，从而建立更客观的排名体系。

以购买耳机的客户列表为例。我们希望先按价格（主要条件）排名，再用客户评分（次要条件）在出现并列时区分高下。

客户示例列表。来源：作者配图 

为实现上述目标，我们将把 RANK.EQ() 与 COUNTIFS() 结合使用。步骤如下：

=RANK.EQ($B2,$B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">&"& $C2)

使用 RANK.EQ() 与 COUNTIFS() 计算名次。来源：作者配图。

其中 COUNTIFS($B$2:$B$9,$B2) 用于统计当前价格在列表中出现的次数。我们对范围 $B$2:$B$9 使用绝对引用以保持一致性，同时保留条件单元格 $B2 为相对引用，以便公式逐行检查。

第二部分 $C$2:$C$9,">"&$C2 用于查找比当前耳机评分（C2）更高的客户评分数量。

COUNTIFS() 遵循“与（AND）”逻辑：只统计同时满足所有条件的单元格。如果在相同价格下没有评分更高的耳机，COUNTIFS() 将返回 0。因此由 RANK.EQ() 确定的名次保持不变。

在 Excel 中计算百分位排名

由于 RANK() 函数为数值进行排名，您可以使用Excel 百分位排名公式，将某数值在数据集中的排名表示为占整个数据集的百分比。

Excel 提供了两个用于计算百分位排名的函数。我们分别来看一下。

PERCENTRANK.INC() 函数

PERCENTRANK.INC() 会计算数值的百分位排名，并将数据集中的最小值与最大值包含在内。

通过一个示例来理解：我有一个包含 10 位学生考试成绩的数据集，希望计算每个分数的百分位排名，并包含最小值与最大值。

因此我使用了公式 =PERCENTRANK.INC($A$2:$A$11, A2)：

使用 PERCENTRANK.INC() 计算百分位排名。来源：作者配图

在上例中，您可以看到 PERCENTRANK.INC() 将最小值与最大值包含在数据集中。分数 45（最小值）的百分位排名为 0，而分数 90（最大值）的百分位排名为 1。

PERCENTRANK.EXC() 函数

PERCENTRANK.EXC() 会计算数值的百分位排名，但排除数据集中的最小值和最大值。

通过一个示例来理解：  我在上一节的同一学生数据集上应用了 PERCENTRANK.EXC() 函数：

使用 PERCENTRANK.EXC() 计算百分位排名。来源：作者配图

在上例中，PERCENTRANK.EXC() 会排除数据集中的最小值与最大值。这意味着分数 45（最小值）的百分位不再是 0，分数 90（最大值）的百分位也不再是 1。

Excel RANK() 的常见错误及规避方法

Excel 函数便捷易用，适用于各类业务工作。但有时，即便是熟练用户也会犯一些常见错误。下面分享几个需要留意的错误，以及如何避免：

复制粘贴公式

当您在单元格间复制排名公式时，请确保不要误用相对引用。每个单元格都可能引用到不同的数据范围，从而扰乱您的排名。

为避免此问题，请在设置公式时使用绝对引用，例如 $A$1:$A$10，以便在所有排名中锁定正确的范围。

未处理重复值

假设有两位销售员并列最高销售额。如何处理并列会带来很大差异： RANK() 与 RANK.EQ() 对并列的处理相似。

因此，您需要决定并列值是否应当拥有相同或不同名次，或是否应使用 RANK.AVG() 对名次取平均值。然后相应选择正确的函数。

排序方向错误

看似简单，但如果忘记设置函数中的 order 参数，可能会导致排名方向（升序或降序）出错。

因此请务必再次确认该参数：降序使用“0”（或留空），升序使用“1”。确保排序方向与您的排名需求一致。

函数用错

在处理不同数据集时，选择正确的排名函数至关重要，因为使用不合适的函数会得到不理想的结果。

为避免问题，标准排名使用 RANK.EQ()；当并列处理会影响结果（如统计分析）时，使用 RANK.AVG()。

公式未更新

随着数据增长，您必须更新公式中的范围引用。否则新增数据不会被纳入排名，您也可能因此困惑为什么结果不对。

因此应定期检查并更新公式范围，确保所有有价值的数据都被纳入排名。

结语

现在，您已经掌握了如何使用 RANK()、RANK.EQ() 和 RANK.AVG() 等函数来处理并列并解决各种排名问题。这些函数适用于多种场景，例如分析学生成绩或对销售数据进行排序。

如果您想进一步提升 Excel 技能，强烈建议查看 Datacamp 上的一些优质课程与资源：

• 从 Introduction to Excel 课程打好基础，再进阶到 Data Analysis in Excel 和 Excel Fundamentals。
• 考虑学习 Data Visualization in Excel 课程，增强您的数据可视化能力。 
• Introduction to PowerQuery in Excel 课程对更高级的数据处理大有裨益。
• 如果希望系统认证您的 Excel 专业能力，请查看这份详尽指南：How to Earn a Microsoft Excel Certification。