Excel技巧：超100单元格数据如何生成1-100稳定排名

要实现行顺序调整后排名仍保持正确，公式必须基于K列所有数据的相对大小，而非相邻单元格或行顺序。以下两种方法均可满足需求：

方法1：基于百分位映射（推荐） #

利用PERCENTRANK.EXC函数计算数值在数据集中的相对百分位，再映射到1-100区间。公式如下：

=ROUND(100 - PERCENTRANK.EXC($K:$K, K3, 2)*99, 0)

• 细节说明：
  • PERCENTRANK.EXC($K:$K, K3, 2)：计算K3的值在K列所有数据中的百分位（返回0到1之间的小数，保留2位精度），最大值返回接近1，最小值返回接近0。
  • *99：将0-1的范围转换为0-99，避免最大值超过100。
  • 100 - ...：让最大值对应100，最小值对应1；若需最小值对应100、最大值对应1，去掉100 -即可。
  • ROUND(...,0)：将结果取整为整数排名。

方法2：基于RANK转换 #

如果不习惯百分位函数，可先用RANK.EQ得到原始排名（1到N，N为数据总数），再压缩到1-100区间：

=ROUND(100 - ((RANK.EQ(K3, $K:$K, 0)-1)/(COUNT($K:$K)-1))*99, 0)

• 细节说明：
  • RANK.EQ(K3, $K:$K, 0)：得到K3的降序原始排名（最大值为1，最小值为N）。
  • ((RANK.EQ(...) -1)/(COUNT(...) -1))：将原始排名转换为0-1的相对比例，避免最小值映射为0。
  • *99 +1：映射到1-100区间，再用ROUND取整。

关键注意事项 #

• 确保$K:$K为绝对引用，行调整后公式仍会引用整个K列。
• 若K列存在空白单元格，COUNT($K:$K)会自动忽略；如需将空白视为0参与排名，改用COUNTA($K:$K)。
• 两种方法均不依赖行顺序，无论如何调整行位置，每个单元格的排名都会根据其在K列的实际大小自动更新。

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