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 函数名称存在细微差异,本内容仅作为学习参考,不承担因为公式编写错误造成排名结果异常以及相关损失责任。