如何按特定规则使用Excel Rank函数实现密集排名？

需求说明 #

需要实现密集排名：相同数值获得相同排名，后续排名不跳跃。例如数值序列4,3,3,2对应排名1,2,2,3。

通用公式（适用于所有Excel版本） #

假设待排名的数据存放在A1:A4单元格，在B1单元格输入以下公式，下拉填充至B4即可：

=SUMPRODUCT((A$1:A$4>A1)/COUNTIF(A$1:A$4,A$1:A$4))+1

公式解释

• A$1:A$4>A1：生成数组，判断区域内每个值是否大于当前单元格值，大于返回1，否则返回0。
• COUNTIF(A$1:A$4,A$1:A$4)：计算每个值在区域内的出现次数，消除重复值对排名的干扰。
• (A$1:A$4>A1)/COUNTIF(...)：按重复值分组统计大于当前值的数量，得到不重复的"更大值"个数。
• SUMPRODUCT(...)：求和得到比当前值大的不重复数值总数，加1后即为当前值的密集排名。

Excel 365/2021专属简化公式（动态数组） #

若使用支持动态数组的Excel版本，只需在B1单元格输入以下公式，结果会自动填充至所有对应单元格：

=MAP(A1:A4,LAMBDA(x,SUMPRODUCT((A1:A4>x)/COUNTIF(A1:A4,A1:A4))+1))

验证示例 #

将4,3,3,2依次输入A1至A4，使用上述公式后，B1至B4将得到1,2,2,3，完全匹配需求。

内容的提问来源于stack exchange，提问作者Majedul Haq