excel 排名怎么弄 函数与排序完整实操指南

在 excel 里面做排名，主流方式分为函数自动排名和手动排序生成静态排名两大类，函数排名核心就是 rank 相关函数，包含旧版 rank 函数，新版 rank.eq 和 rank.avg，另外还有国内业务常用的中国式排名组合公式，排序方式适合一次性报表，不需要数据变动自动更新的场景，很多新手做 excel 排名出错，主要问题集中在没有锁定引用区域，下拉填充之后对比范围偏移，还有不清楚同分排名的两种规则，excel 排名底层原理，就是拿当前单元格数值，和全部参与排名的数据做比对，统计大于或者小于当前数值的数量，计算出对应的位次，支持降序也就是数值越大名次越靠前，也支持升序也就是数值越小名次越靠前，理解底层逻辑之后，就可以根据业务需求选择对应的排名方案。

第一种 rank 函数 基础自动排名

rank 是兼容性最好的经典排名函数，旧版 excel 全部支持，适合考试成绩业务积分这类常规排名，高分排第一名。

基础公式

=RANK (B2,$B$2:$B$20,0)

公式解释，B2 代表当前需要计算名次的单元格，$B$2:$B$20 是全部参与排名的数据区间，美元符号是绝对引用锁定区域，末尾数字 0 代表降序排名，改成数字 1 就是升序排名。

操作步骤

第一步 点击存放排名结果的空白单元格，也就是 C2 单元格

第二步 输入 rank 公式，把区间替换成自己表格真实数据范围

第三步 按下回车键，得到第一条数据对应的名次

第四步 鼠标移动到单元格右下角，光标变成黑色十字填充柄，下拉填充整列，全部数据自动生成名次

重点提醒

如果没有添加美元符号锁定区域，下拉的时候区间会自动偏移，每一行对比的数据范围都变化，最终 excel 排名全部错乱，这是新手最高频的错误点。

第二种 rank.eq 函数 新版标准排名

rank.eq 是新版 excel 官方推荐替代旧版 rank 的函数，计算规则和旧版 rank 完全一致，遇到同分的时候，同分数据共用同一个名次，后面的数据名次会跳号，比如两个第一名，下一条数据直接显示第三名。

公式

=RANK.EQ (B2,$B$2:$B$20,0)

适用场景

竞赛打分，月考成绩，员工绩效排名，业务认可并列名次，允许名次跳号的表格。

第三种 rank.avg 函数 同分平均名次

rank.avg 和 rank.eq 核心差别，同分并列的时候，会计算并列位置的平均名次，不会跳号，例如两个同分第一名，两个单元格都会显示 1.5，适合统计分析实验数据，需要连续位次的场景。

公式

=RANK.AVG (B2,$B$2:$B$20,0)

适用场景

科研统计，样本数据位次计算，不希望名次出现跳空的报表。

第四种 排序生成静态 excel 排名

如果不需要公式自动刷新，只是临时做排名报表，可以直接使用排序功能，排序之后填充连续序号，这种属于静态排名，原始数值修改之后名次不会自动更新。

操作步骤

第一步 选中完整数据区域，姓名和数值列要一起选中，不能只单独选中数值列

第二步 点击顶部数据选项卡，选择排序

第三步 主要关键字选择数值所在列，次序选择降序

第四步 排序完成之后，在辅助列填充连续数字 1，2，3 作为名次

缺点说明

静态排名灵活性差，后续修改原始分数，必须重新排序，适合一次性打印或者归档报表。

第五种 中国式排名 同分不跳号

原生 rank 系列函数同分之后名次跳号，很多国内业务要求同分同名次，但是下一名次不跳号，也就是两个第一名，下一条直接是第二名，这种需要使用组合公式实现中国式排名。

中国式排名公式

=SUMPRODUCT ((B$2:B$20>B2)/COUNTIF (B$2:B$20,B$2:B$20))+1

公式原理，统计比当前数值大的不重复数值个数，再加 1，实现同分并列，名次连续不跳号。

操作步骤

第一步 在排名单元格输入这个组合公式

第二步 锁定数据区间，下拉填充整列

第三步 核对同分数据，确认名次没有跳号

各类排名方式对比表格

特殊情况处理要点

第一 空单元格和文本单元格，rank 函数会自动跳过空单元格，如果区间里面是文本内容，公式会直接报错，提前清理文本类内容。

第二 筛选状态排名，普通 rank 函数会统计全部行，包含筛选隐藏的行，如果想要只针对可见行做 excel 排名，需要搭配动态数组或者 subtotal 相关方案处理。

第三 升序排名，修改公式末尾参数，把 0 改成 1，就变成数值越小名次越靠前，适合跑步计时，耗时越短排名越高这类数据。

第四 重复数值，中国式排名公式里面 countif 的作用，就是处理重复数值，避免重复计数，保证排名结果准确。

第五 跨表排名，如果需要引用另外一张工作表的数据做排名，区间前面加上工作表名称，比如 Sheet2!$B$2:$B$20。

总结

普通高分在前的自动 excel 排名，优先选择 rank 或者 rank.eq 函数，一定要记得锁定数据区域，下拉填充，如果同分需要平均位次，就选用 rank.avg，如果业务要求同分并列但是名次不跳号，就使用中国式排名组合公式，如果只是临时一次性排名，可以直接排序之后填充序号，做 excel 排名的时候，重点把控区域锁定，同分规则，升序降序参数这三点，就可以避免大部分排名错误。

【常见问题】

问题 1 excel 排名怎么弄，下拉之后名次全部错乱

回答 1 excel 排名怎么弄出现下拉错乱，核心原因是数据区域没有绝对引用锁定，下拉填充的时候对比范围自动偏移，修改公式，把排名区域加上美元符号锁定，重新下拉，就可以修正 excel 排名错乱的问题。

问题 2 excel 排名怎么弄，同分之后下一名直接变成第三名

回答 2 excel 排名怎么弄出现名次跳号，是因为使用 rank 或者 rank.eq 函数，该函数本身规则就是同分并列之后跳号，如果不想跳号，需要更换中国式排名公式，实现 excel 排名同分之后名次连续。

问题 3 excel 排名怎么弄，想要数值越小名次越高

回答 3 excel 排名怎么弄想要小数值排前面，修改 rank 函数最后一个参数，把 0 改成 1，也就是升序排名，数值越小对应的名次越靠前，适合计时类数据完成 excel 排名。

问题 4 excel 排名怎么弄，有没有不用函数的办法

回答 4 excel 排名怎么弄可以不用函数，选中全部数据，进入数据选项卡执行降序排序，排序完成之后填充连续数字作为名次，这种是静态 excel 排名，原始数值变动之后名次不会自动更新。

【免责申明】

本文教程基于主流 excel 版本编写，rank.avg rank.eq 新版函数在旧版 excel 不支持，中国式排名组合公式运算量大，行数很多的时候计算速度变慢，操作前建议备份原始表格，本文仅作为技术参考，不承担因操作失误带来的数据错误相关责任。