核心答案就是 excel 怎么进行排名次,最主流方案就是使用 RANK 函数,它可以根据分数或者数值,自动在指定数据区域里面生成名次,同时支持降序排名也就是分数越高名次越靠前,也支持升序排名也就是数值越小名次越靠前,很多新手会遇到同分同名次,后面名次跳号的情况,这是 RANK 函数默认特性,如果业务要求同分也要连续名次,不跳号,就需要改用其他复合公式,另外还要区分普通 RANK 和新版 RANK.EQ RANK.AVG,RANK.EQ 就是旧版 RANK,同分并列,下一名跳过,RANK.AVG 同分取平均名次,适合特殊统计需求,下面会把基础排名、同分处理、跨表排名、筛选状态排名、异常排查全部深度讲解,Excel 2016 2019 365 版本操作逻辑通用。
排名函数对比表
| 函数 | 排名规则 | 特点 | 适用场景 |
|---|---|---|---|
| RANK / RANK.EQ | 同分并列,后续名次跳号 | 和旧版 RANK 效果一致 | 考试成绩排名,最常用 |
| RANK.AVG | 同分并列,取平均名次 | 同分名次是平均值 | 专业统计报表 |
| SUMPRODUCT 复合公式 | 同分不跳号,名次连续 | 自定义排名逻辑 | 竞赛要求同分之后名次连续 |
方式一 RANK 函数基础排名(降序,高分在前)
1 假设分数在 B 列,B2 是当前要排名的分数,数据范围是 B2:B10
2 在名次单元格 C2 输入公式:=RANK (B2,$B$2:$B$10,0)
3 参数说明
- B2:当前参与排名的数值
- $B$2:$B$10:全部排名的数据区域,一定要加美元符号绝对引用,下拉填充的时候区域不会偏移
- 最后参数 0:代表降序,数值越大排名越靠前,写 1 就是升序,数值越小排名越靠前
4 按下回车得到第一名第二名结果,选中 C2 单元格,拖动右下角填充柄向下填充整列,批量生成全部名次
重点提醒:不加 $ 绝对引用,下拉的时候排名区域会变成 B3:B11,排名结果直接错乱,这是新手最高频错误
方式二 RANK.AVG 同分平均名次
1 公式写法:=RANK.AVG (B2,$B$2:$B$10,0)
2 举例:两个人都是 90 分并列第 2,RANK.EQ 会显示都是 2,下一名直接是 4;RANK.AVG 会把同分的名次显示成 2.5,适合需要精确统计平均排名的场景,普通考试一般不用这个
方式三 同分连续名次(不跳号)
RANK 默认同分之后名次跳号,如果业务要求同分之后名次连续,使用 SUMPRODUCT 公式
公式:=SUMPRODUCT (($B$2:$B$10>B2)1)+SUMPRODUCT (($B$2:$B$10=B2)1(ROW ($B$2:$B$10)<=ROW (B2)))+1
这个公式逻辑是,大于当前数值的个数,加上同分数里面排在本行之前的数量,实现同分并列,但是后面名次不跳号
进阶要点与异常排查
第一种,排名结果全部一样,检查排名区域有没有加B2:B$10,0),注意工作表名称后面加感叹号
第五种,想要排名之后按名次排序,可以选中全部数据区域,以名次列为关键字升序排序,就可以按照名次从小到大重新排列整行数据
第六种,重复排名出错,确认数据区域里面不要混入错误值,N/A DIV/0! 这类错误值会让 RANK 公式返回错误
问题 1 excel 怎么进行排名次,为什么同分之后下一名直接跳号
回答 1 excel 怎么进行排名次,RANK 和 RANK.EQ 默认就是同分并列,后面名次跳号,这是默认规则,如果需要同分名次连续,改用 SUMPRODUCT 复合公式实现不跳号排名。
问题 2 excel 怎么进行排名次,下拉填充之后排名全部错乱是什么原因
回答 2 excel 怎么进行排名次,下拉错乱大多是排名区域没有加B2:B$10,0),锁定排名区域就可以解决。
问题 3 excel 怎么进行排名次,RANK 最后一个参数 0 和 1 区别是什么
回答 3 excel 怎么进行排名次,参数 0 是降序排名,数值越高名次越靠前,参数 1 是升序排名,数值越小名次越靠前,成绩排名一般使用参数 0。
问题 4 excel 怎么进行排名次,文本型数字可以正常排名吗
回答 4 excel 怎么进行排名次,RANK 函数只识别真实数值,文本型数字无法参与排名,需要先把文本数字转换为数值,再使用 RANK 生成名次。
本文教程适用于主流 Windows 版本 Excel,RANK.AVG 新版函数在旧版本 Office 可能不支持,公式结果会随原始分数变动自动更新,重要表格操作前建议备份原始数据。


