使用 RANK 和 PERCENTILE 进行排名

很多时候，您不只是想知道一个值，还想知道它在群体中的位置。这位销售人员排名第 1 还是第 12？这个分数是否位于前 10%？

有两类函数可以回答这些问题：RANK 告诉您序位，PERCENTILE 告诉您指定分位点上的值。两者结合后，可以将原始数字转化为排名和基准。

RANK.EQ 返回某个值在范围中的位置。您需要传入该值、范围和排序标志。

• 顺序为 0（或省略）时，从最大值开始排名（降序）。
• 顺序为 1 时，从最小值开始排名（升序）。

这里，最高的销售额会获得第 1 名。

请注意美元符号：$B$2:$B$20。向下复制公式时，范围必须保持固定，而值的引用 B2 会依次变为 B3、B4 等。

如果忘记美元符号，范围会随每一行移动，从而生成毫无意义的排名。在这里，范围的绝对引用至关重要。

RANK.EQ 会为并列值赋予相同的排名，然后跳过下一个排名。如果两个值并列第 2 名，它们都会显示 2，而下一个值会显示 4（没有第 3 名）。

如果您希望为并列值计算平均排名，请使用 RANK.AVG。两个并列第 2 名和第 3 名的值都会显示 2.5。

如果即使存在并列也要生成唯一排名，可以添加 COUNTIF 作为破平规则。它会计算当前行上方出现的相同值数量，并加上相应的偏移量。

这样可以确保得到整齐的 1、2、3、4 序列且没有重复项，这对排行榜很有用。

PERCENTILE.INC 用来回答：在指定百分比处对应的值是多少？传入范围和一个介于 0 到 1 之间的分数。

下面的公式返回第 90 百分位数，即 90% 的数据低于或等于的值。它非常适合设置“表现位于前 10%”之类的阈值。

四分位数会将数据分成四个部分。QUARTILE.INC 函数是一种快捷方式：第 1 四分位数等于第 25 百分位数，第 2 四分位数等于第 50 百分位数（即中位数），第 3 四分位数等于第 75 百分位数。

下面的公式会返回第 3 四分位数，其结果与 PERCENTILE.INC(A2:A101,0.75) 完全相同。

有两种形式：PERCENTILE.INC（包含边界值，可接受 0 和 1）以及 PERCENTILE.EXC（排除边界值，只接受 0 和 1 之间的值，绝不会取两个极端值）。

在大多数日常报告中，.INC 是标准选择。如果遵循排除边界点的统计惯例，请使用 .EXC。

RANK 给出位置，PERCENTILE 给出某个截断点处的值。PERCENTRANK.INC 则与百分位数相反：给定一个值，它处于第多少百分位？

如果一名学生得分为 88，而 PERCENTRANK 返回 0.92，则表示该学生的成绩超过了该群体中 92% 的人。乘以 100 即可将其显示为百分比。

RANK 和 PERCENTILE 只处理数字。范围内的文本标签和空白单元格会被忽略，因此多余的表头不会导致排名出错。

不过，请注意以文本形式存储的数字：它们会被完全跳过，从而使每个排名和百分位数发生偏移。如果某个排名看起来不对，请检查所有值是否都是真正的数字，而不是左对齐的文本。

假设您有 20 名销售人员，他们的销售额位于 B 列。您希望为每名销售人员计算排名，并判断其是否处于前四分位。

• 排名：=RANK.EQ(B2,$B$2:$B$21,0)
• 前四分位标记会将其销售额与第 75 百分位数进行比较。

下面的公式会根据截断点，为每名销售人员返回“顶部”或“标准”。

测试您对排名函数的理解。

您已经学会了如何确定值在群体中的位置：

• RANK.EQ 给出序数位置；排序方式为 0 时先列出最大值，为 1 时先列出最小值。请使用 $ 锁定范围。
• RANK.AVG 会对并列排名取平均值；结合 COUNTIF 可生成唯一排名。
• PERCENTILE.INC 返回某个截断点处的值；QUARTILE.INC 是获取第 25、50 和 75 百分位数的快捷方式。
• PERCENTRANK.INC 则反过来：返回给定值所处的百分位数。