副标题:普通排名 同分并列 中国式排名 多条件排名

核心结论前置: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

  1. 选中 C2 单元格
  2. 输入公式 =RANK.EQ(B2,$B$2:$B$10,0)
  3. 回车,得到当前成绩排名
  4. 鼠标放到单元格右下角填充柄,下拉填充整列

参数解释:

  • 第一个参数 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,只在本组内排序

三、深度注意事项

  1. 数据源区域一定要加绝对引用 $,如果忘记加,下拉的时候排名区域会自动偏移,排名全部错乱
  2. 如果数据源里面有空单元格、文本数字,RANK 会忽略或者报错,建议提前清理数据
  3. RANK.AVG:同分的时候返回平均名次,例如两个第 2 名,返回 2.5,普通业务一般不用
  4. 如果是新版 365 动态数组,可以直接使用 SORT、RANK 动态数组写法,但是传统表格 RANK.EQ 兼容性最好
  5. 如果排名之后需要按名次排序,可以使用排序功能,或者 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 常规版本功能讲解,公式仅作为技术参考,排名规则请根据业务要求选择跳号或者中国式排名,因引用区域、数据格式错误造成排名异常,本文不承担相关责任。