excel 对数据排名次最主流的方法是使用 RANK 函数,它可以自动按照数值大小生成对应的名次,适合成绩表、销售业绩、积分排行榜这类表格,核心区分降序排名也就是数值越大名次越高,升序排名也就是数值越小名次越高,很多新手遇到同分并列名次、后续名次跳号、下拉填充排名区域偏移这些问题,本质是没有掌握函数参数,还有没有对排名区域做绝对引用,掌握 RANK 函数写法,同分处理方案,以及新版 RANK.EQ 和 RANK.AVG 区别之后,就可以稳定完成自动排名,原始分数修改之后,名次会自动重新计算更新。
一 RANK 函数基础语法
旧版 excel 通用 RANK 函数,语法结构 RANK (要排名的数值,全部数据区域,排序方式)
第三个参数填写 0 或者省略,代表降序,数值越大排名越靠前,也就是最常用的高分第一名
第三个参数填写 1,代表升序,数值越小排名越靠前,比如用时排名,耗时最短是第一名
基础示例,A 列是成绩,B 列要输出名次,B2 单元格公式
=RANK (A2,$A$2:$A$10,0)
重点说明,$A$2:$A$10 必须加美元符号绝对引用,也就是之前学到的 F4 快捷键锁定区域,如果不加绝对引用,下拉填充的时候,整个排名区域会跟着往下偏移,最终排名全部错乱。
二 新版两个排名函数
新版 excel 推荐使用 RANK.EQ 和 RANK.AVG,是原来 RANK 函数拆分出来的两个版本
RANK.EQ,和老版 RANK 函数逻辑完全一致,同分数据占用同一个名次,后面名次会跳号,比如两个人并列第 2,下一名直接是第 4 名
RANK.AVG,同分的时候取平均名次,比如两个人并列第 2,名次显示成 2.5,适合需要平均分排名的特殊统计场景
三 同分不跳号 连续排名方案
如果业务要求同分名次相同,但是后面名次不能跳号,也就是并列第 2 之后下一名仍然是第 3 名,普通 RANK 函数无法实现,需要使用 COUNTIF 组合公式
示例公式
=SUMPRODUCT (($A$2:$A$10>A2)/COUNTIF ($A$2:$A$10,$A$2:$A$10))+1
这个公式可以实现连续排名,不会出现名次跳号,适合考试排名、竞赛排名有连续名次要求的表格。
四 手动排序法 不用函数排名
如果不需要动态自动更新名次,只是一次性生成排名,可以直接排序
操作步骤
1 选中全部数据区域,注意一定要连同对应的姓名等配套信息一起选中,不能只单独选中分数列
2 点击数据选项卡,选择降序或者升序排序
3 排序完成之后,手动在旁边列填写 1、2、3、4 序号
缺点,原始数据修改之后,名次不会自动刷新,适合一次性报表,不推荐经常变动的数据。
五 四种排名方式对比表
| 函数 / 方法 | 同分处理 | 是否动态更新 | 适用场景 |
|---|---|---|---|
| RANK / RANK.EQ | 同分同名次,后面跳号 | 自动更新 | 业绩榜,最常用 |
| RANK.AVG | 同分取平均名次 | 自动更新 | 需要平均排名统计 |
| SUMPRODUCT 组合公式 | 同分同名次,名次连续不跳号 | 自动更新 | 考试竞赛连续排名 |
| 手动排序填序号 | 自定义 | 不会自动更新 | 一次性静态报表 |
六 高频错误排查表
| 错误现象 | 错误原因 | 解决办法 |
|---|---|---|
| 下拉之后排名全部错乱 | 排名区域没有加绝对引用 | 选中区域按 F4,改成 $A$2:$A$10 |
| 同分之后名次跳号 | 使用 RANK.EQ 默认逻辑 | 改用 SUMPRODUCT 连续排名公式 |
| 出现 #N/A 或者 #VALUE! | 数据区域里面有文本空格 | 清理区域内文本空格,只保留纯数值 |
| 排名顺序反过来,低分排第一 | 第三个参数填成 1 升序 | 改成 0 或者省略参数,切换降序 |
| WPS 找不到 RANK 函数 | 版本兼容问题 | 直接使用 RANK.EQ,功能一致 |
七 补充拓展
排名函数支持跨工作表排名,写法就是 Sheet1!A2,跨表的全部数据区域同样需要绝对引用锁定。如果需要多条件排名,也就是先按第一条件排序,第一条件相同再按第二条件排名,普通 RANK 函数不能直接实现,需要搭配 SUMPRODUCT 增加多条件判断。
【常见问题】
1 excel 中怎么对数据进行排名次,最常用的函数是什么
答 excel 中怎么对数据进行排名次,最常用就是 RANK 函数,excel 中怎么对数据进行排名次新版也可以使用 RANK.EQ,默认高分排在第一名。
2 excel 中怎么对数据进行排名次,下拉填充为什么排名出错
答 excel 中怎么对数据进行排名次,出错原因是排名区域没有绝对引用,excel 中怎么对数据进行排名次要用 F4 锁定整个数据区域,避免下拉偏移。
3 excel 中怎么对数据进行排名次,同分之后名次跳号怎么解决
答 excel 中怎么对数据进行排名次,普通 RANK 函数同分就会跳号,excel 中怎么对数据进行排名次想要连续名次就使用 SUMPRODUCT 组合公式。
4 excel 中怎么对数据进行排名次,可以不用函数手动排名吗
答 excel 中怎么对数据进行排名次,可以选中全部数据之后在数据选项卡排序,再手动填写序号,excel 中怎么对数据进行排名次缺点是数据改动之后名次不会自动更新。
【免责声明】
本教程基于桌面版 excel 通用版本讲解数据排名,不同版本 excel 函数名称存在细微差异,本内容仅作为学习参考,不承担因为公式编写错误造成排名结果异常以及相关损失责任。