Excel 2019中按指定等级数批量分配单元格值排名的方法

核心公式 #

假设你的数值存放在A列（从A2到A57，共56个数据），在B2单元格输入以下公式，下拉填充至B57即可：

=ROUNDUP(RANK.EQ(A2,$A$2:$A$57,1)/7,0)

也可以用CEILING函数实现相同效果：

=CEILING(RANK.EQ(A2,$A$2:$A$57,1)/7,1)

公式说明 #

• RANK.EQ(A2,$A$2:$A$57,1)：对A2的数值在A2:A57区域内做升序排名，最小的数值排名为1，最大的为56。
• /7：将排名除以每组的数值数量（7个），得到分组对应的浮点值（比如排名1-7对应0.14_{1，排名8-14对应1.14}2）。
• ROUNDUP(...,0) 或 CEILING(...,1)：把浮点值向上取整，得到1-8的等级，确保最低7个值对应等级1，次低7个对应等级2，以此类推。

特殊情况处理 #

如果存在重复数值，RANK.EQ会给相同数值分配相同排名，可能导致某组数量临时超过7。若需严格保证每组7个值，可先对A列数据按升序排序，然后在B2输入以下公式下拉：

=INT((ROW()-2)/7)+1

内容的提问来源于stack exchange，提问作者Igor K