首段结论 rank 函数作用是给一组数值做排名,可以按从大到小降序,也可以从小到大升序,核心语法是 RANK 数字 排名区域 排序方式,最关键要点是排名区域必须加绝对引用符号 $,下拉填充公式的时候,排名范围不会跑偏,这也是新手最容易出错的地方,新版 Excel 已经推荐使用 RANK.EQ 和 RANK.AVG,旧版 rank 函数依然可以正常使用。
一 函数语法
旧版 rank 函数
=RANK (number,ref,[order])
参数解释
number 要计算排名的那个单元格数值
ref 参与排名的全部数据区域,这个区域必须锁定绝对引用
[order] 排序方式,是可选参数,写 0 或者省略,代表降序,数值越大排名越靠前,写 1 代表升序,数值越小排名越靠前
新版函数区分
RANK.EQ 同分占用相同名次,下一名次跳过,比如两个第 2 名,下一个直接是第 4 名,和老版 rank 效果一致
RANK.AVG 同分取平均名次,两个并列第 2 名,名次都会显示 2.5
二 实操案例 降序排名 成绩表
假设数据 B2 到 B10 是学生分数,要在 C2 单元格算出 B2 的排名
输入公式
=RANK (B2,$B$2:$B$10,0)
解释
B2 是当前要排名的分数
$B$2:$B$10 是全部成绩区域,加美元符号锁定区域
0 代表降序,高分排第一名
输入完成按回车,然后鼠标放在单元格右下角,出现十字填充柄,下拉填充,所有学生排名自动生成
如果想要升序排名,也就是数值最小排第一名,公式改成
=RANK (B2,$B$2:$B$10,1)
三 绝对引用重点讲解
很多新手写公式忘记加 $,写成 =RANK (B2,B2:B10,0)
下拉之后,区域会自动变成 B3:B11 B4:B12,排名范围不断偏移,排名结果全部错乱
$BB2 只锁定列,B$2 只锁定行,rank 函数这里需要整段区域都锁定,所以写成 $B2:B$10
四 同分排名特点
rank 函数默认同分同名次,比如两个学生都是 90 分,都显示第 3 名,后面那个学生就是第 5 名,中间跳过第 4 名
如果不希望跳号,想要连续排名,rank 函数本身做不到,需要搭配 COUNTIF 函数写复合公式
五 常见拓展 多条件排名
rank 原生函数不支持多条件排名,如果需要先按班级分组,班级内部再排名,就要结合 SUMPRODUCT 写复合公式
示例 同班级内成绩排名
=SUMPRODUCT (($A$2:$A$10=A2)($B$2:$B$10>B2))+1
FAQ
问题 1 表格 rank 函数下拉之后排名全部不对是什么原因
回答 绝大多数是排名区域没有加绝对引用 $,下拉的时候排名区域发生偏移,把 ref 区域改成 $B2:B$10 这种锁定格式就可以修复。
问题 2 表格 rank 函数同分为什么名次会跳号
回答 这是 rank 函数本身规则,同分共享同一个名次,后续名次自动跳过,如果需要平均名次,可以换成 RANK.AVG 函数。
问题 3 表格 rank 函数能不能跨工作表排名
回答 可以,跨表的时候区域写法带上工作表名称,例如 =RANK (B2,Sheet2!$B$2:$B$10,0),注意工作表名称后面加感叹号。
免责声明
本教程基于 Excel 通用版本讲解,WPS 表格 rank 函数用法基本一致,不同版本函数兼容情况存在差异,复杂表格运算前建议备份原始数据,公式计算结果仅供参考。
