副标题:普通排名 同分并列 中国式排名 多条件排名
核心结论前置:Excel 排名首选 RANK 函数,最基础用法可以实现分数排名,同分自动并列名次;如果需要中国式排名(同分不跳号),要使用 COUNTIF 组合公式;多部门、多组别分组排名,使用 RANK.EQ 搭配条件或者 SUMPRODUCT 实现,是成绩表、销售业绩表高频函数。
一、函数对照表
| 类型 | 公式写法 | 特点 |
|---|---|---|
| 基础降序排名 | RANK.EQ(A2,$A$2:$A$10,0) | 数值越大排名越靠前,同分并列,下一名跳号 |
| 基础升序排名 | RANK.EQ(A2,$A$2:$A$10,1) | 数值越小排名越靠前 |
| 中国式排名 | SUMPRODUCT(($A$2:$A$10>A2)/COUNTIF($A$2:$A$10,$A$2:$A$10))+1 | 同分同名次,后面不跳号 |
| 分组排名 | SUMPRODUCT(($B$2:$B$10=B2)*($A$2:$A$10>A2))+1 | 同组别内部单独排名 |
补充:旧版 RANK 函数和新版 RANK.EQ 效果一致,新版推荐使用 RANK.EQ,还有 RANK.AVG,同分返回平均名次,日常很少使用
二、详细操作步骤
方法 1 RANK.EQ 基础排名(最常用)
案例:B 列为成绩,C 列生成排名,数据范围 B2:B10
- 选中 C2 单元格
- 输入公式 =RANK.EQ(B2,$B$2:$B$10,0)
- 回车,得到当前成绩排名
- 鼠标放到单元格右下角填充柄,下拉填充整列
参数解释:
- 第一个参数 B2:当前要排名的数值
- 第二个参数 $B$2:$B$10:全部排名数据源,必须加绝对引用 $,下拉的时候区域不能偏移
- 第三个参数 0:降序,高分排第 1;改成 1 就是升序,低分排第 1
特点:比如两个人都是 90 分,都是第 2 名,下一名直接是第 4 名,会跳号
方法 2 中国式排名(同分不跳号)
需求:两个 90 分并列第 2,下一名是第 3,不跳号
C2 公式:
=SUMPRODUCT(($B$2:$B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1
写好之后下拉填充,适合考试、竞赛经常要求的中国式排名
方法 3 分组排名(同部门内部排名)
A 列成绩,B 列部门,在 C 列实现同部门内部排名
C2 公式:
=SUMPRODUCT(($B$2:$B$10=B2)($A$2:$A$10>A2))+1
原理:同时满足同部门,并且分数比当前单元格更高的数量 + 1,只在本组内排序
三、深度注意事项
- 数据源区域一定要加绝对引用 $,如果忘记加,下拉的时候排名区域会自动偏移,排名全部错乱
- 如果数据源里面有空单元格、文本数字,RANK 会忽略或者报错,建议提前清理数据
- RANK.AVG:同分的时候返回平均名次,例如两个第 2 名,返回 2.5,普通业务一般不用
- 如果是新版 365 动态数组,可以直接使用 SORT、RANK 动态数组写法,但是传统表格 RANK.EQ 兼容性最好
- 如果排名之后需要按名次排序,可以使用排序功能,或者 SORT 函数重新生成排序后的清单
四、FAQ
Q1:excel 中排名怎么弄,同分之后名次跳号是什么原因?
A1:excel 中排名怎么弄,RANK.EQ 默认规则就是同分并列,下一名跳号,如果不想跳号,就改用 SUMPRODUCT 中国式排名公式。
Q2:excel 中排名怎么弄,下拉之后排名全部错误?
A2:excel 中排名怎么弄,绝大多数是排名数据源没有加B2:B$10 固定区域即可。
Q3:excel 中排名怎么弄,可以按部门单独排名吗?
A3:excel 中排名怎么弄,可以实现分组排名,使用 SUMPRODUCT 增加部门相等的判断条件,只在同一个组别内部计算名次。
Q4:excel 中排名怎么弄,RANK 和 RANK.EQ 有区别吗?
A4:excel 中排名怎么弄,旧版 RANK 函数和 RANK.EQ 运算结果一致,RANK.EQ 是新版标准化函数,推荐优先使用 RANK.EQ。
免责声明
本文内容基于微软 Excel 常规版本功能讲解,公式仅作为技术参考,排名规则请根据业务要求选择跳号或者中国式排名,因引用区域、数据格式错误造成排名异常,本文不承担相关责任。
