rank
一、排名函数基本介绍
1. 函数发展历史
- RANK()：Excel 2007及以前版本的函数，为保持兼容性保留
- RANK.EQ()：Excel 2010引入，功能与RANK()相同，是新标准
- RANK.AVG()：Excel 2010引入，相同数值时返回平均排名
2. 函数语法对比
| 函数 | 语法 | 说明 |
|---|---|---|
| RANK() | =RANK(number, ref, [order]) | 传统排名函数 |
| RANK.EQ() | =RANK.EQ(number, ref, [order]) | 等价于RANK()，推荐使用 |
| RANK.AVG() | =RANK.AVG(number, ref, [order]) | 相同数值返回平均排名 |
3. 参数详解
| 参数 | 说明 | 必需 |
|---|---|---|
| number | 要排名的数值 | 是 |
| ref | 包含所有要排名的数值的区域 | 是 |
| order | 排序方式：0或省略=降序，非0=升序 | 否 |
注意：
- 降序排名：数值越大排名越靠前（第1名是最大值）
- 升序排名：数值越小排名越靠前（第1名是最小值）
二、详细示例数据表
表1：学生考试成绩表（20条记录）
| 学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 |
|---|---|---|---|---|---|---|
| S001 | 张三 | 85 | 92 | 78 | 255 | 85.0 |
| S002 | 李四 | 78 | 95 | 88 | 261 | 87.0 |
| S003 | 王五 | 92 | 88 | 76 | 256 | 85.3 |
| S004 | 赵六 | 65 | 72 | 68 | 205 | 68.3 |
| S005 | 孙七 | 88 | 96 | 92 | 276 | 92.0 |
| S006 | 周八 | 76 | 85 | 79 | 240 | 80.0 |
| S007 | 吴九 | 95 | 89 | 94 | 278 | 92.7 |
| S008 | 郑十 | 82 | 78 | 85 | 245 | 81.7 |
| S009 | 钱一 | 89 | 92 | 87 | 268 | 89.3 |
| S010 | 王一 | 73 | 68 | 72 | 213 | 71.0 |
| S011 | 刘二 | 91 | 94 | 89 | 274 | 91.3 |
| S012 | 陈三 | 68 | 75 | 70 | 213 | 71.0 |
| S013 | 杨四 | 87 | 90 | 86 | 263 | 87.7 |
| S014 | 黄五 | 79 | 82 | 80 | 241 | 80.3 |
| S015 | 赵七 | 94 | 97 | 95 | 286 | 95.3 |
| S016 | 周九 | 72 | 79 | 74 | 225 | 75.0 |
| S017 | 吴十 | 86 | 88 | 84 | 258 | 86.0 |
| S018 | 郑一 | 90 | 93 | 91 | 274 | 91.3 |
| S019 | 王二 | 81 | 84 | 82 | 247 | 82.3 |
| S020 | 孙三 | 70 | 76 | 73 | 219 | 73.0 |
表2：销售业绩表（15条记录）
| 销售员ID | 姓名 | 1月 | 2月 | 3月 | 季度总额 | 平均月销 |
|---|---|---|---|---|---|---|
| SA01 | 张明 | 158000 | 142000 | 165000 | 465000 | 155000 |
| SA02 | 李华 | 145000 | 138000 | 152000 | 435000 | 145000 |
| SA03 | 王芳 | 128000 | 135000 | 142000 | 405000 | 135000 |
| SA04 | 赵强 | 165000 | 158000 | 172000 | 495000 | 165000 |
| SA05 | 孙丽 | 142000 | 148000 | 155000 | 445000 | 148333 |
| SA06 | 周伟 | 135000 | 128000 | 140000 | 403000 | 134333 |
| SA07 | 吴刚 | 152000 | 162000 | 158000 | 472000 | 157333 |
| SA08 | 郑洁 | 138000 | 145000 | 148000 | 431000 | 143667 |
| SA09 | 钱勇 | 148000 | 152000 | 146000 | 446000 | 148667 |
| SA10 | 王静 | 125000 | 132000 | 128000 | 385000 | 128333 |
| SA11 | 刘洋 | 162000 | 155000 | 168000 | 485000 | 161667 |
| SA12 | 陈晨 | 155000 | 148000 | 162000 | 465000 | 155000 |
| SA13 | 杨光 | 132000 | 138000 | 135000 | 405000 | 135000 |
| SA14 | 黄蓉 | 142000 | 135000 | 148000 | 425000 | 141667 |
| SA15 | 赵云 | 158000 | 152000 | 165000 | 475000 | 158333 |
表3：产品销量表（相同数值测试）
| 产品ID | 产品名称 | 销量 | 销售额(元) |
|---|---|---|---|
| P001 | iPhone 15 | 150 | 1,125,000 |
| P002 | 华为P60 | 120 | 960,000 |
| P003 | 小米13 | 180 | 1,080,000 |
| P004 | MacBook Pro | 85 | 1,275,000 |
| P005 | 华为平板 | 120 | 720,000 |
| P006 | 三星电视 | 95 | 855,000 |
| P007 | 索尼耳机 | 150 | 450,000 |
| P008 | 戴尔笔记本 | 110 | 990,000 |
| P009 | 联想电脑 | 105 | 735,000 |
| P010 | 苹果手表 | 180 | 1,080,000 |
三、基础用法示例
示例1：基本降序排名（成绩排名）
在表1的H列（总分排名）输入公式：
=RANK.EQ(F2, $F$2:$F$21, 0)
或者使用RANK()：
=RANK(F2, $F$2:$F$21, 0)
解释：
- F2：要排名的总分
- $F$2:$F$21：总分区域（绝对引用）
- 0：降序排名（分数越高排名越靠前）
结果示例：
| 姓名 | 总分 | 排名 |
|---|---|---|
| 赵七 | 286 | 1 |
| 吴九 | 278 | 2 |
| 孙七 | 276 | 3 |
| 刘二 | 274 | 4 |
| 郑一 | 274 | 4 |
| 钱一 | 268 | 6 |
示例2：升序排名（用时最少排名）
假设有比赛用时数据，时间越少成绩越好：
在I列（用时排名）输入公式：
=RANK.EQ(G2, $G$2:$G$21, 1)
解释：
- G2：比赛用时
- 1：升序排名（用时越少排名越靠前）
示例3：多科目分别排名
语文排名：=RANK.EQ(C2, $C$2:$C$21, 0)
数学排名：=RANK.EQ(D2, $D$2:$D$21, 0)
英语排名：=RANK.EQ(E2, $E$2:$E$21, 0)
四、RANK() vs RANK.EQ() vs RANK.AVG() 对比
示例4：相同数值的排名对比
使用表3的销量数据进行测试：
1. 使用RANK.EQ()或RANK()：
=RANK.EQ(B2, $B$2:$B$11, 0)
结果：相同销量得到相同排名，后续排名跳过
2. 使用RANK.AVG()：
=RANK.AVG(B2, $B$2:$B$11, 0)
结果：相同销量得到平均排名
对比结果表：
| 产品名称 | 销量 | RANK.EQ排名 | RANK.AVG排名 | 说明 |
|---|---|---|---|---|
| 小米13 | 180 | 1 | 1.5 | 与苹果手表并列，平均排名(1+2)/2=1.5 |
| 苹果手表 | 180 | 1 | 1.5 | 同上 |
| iPhone 15 | 150 | 3 | 3.5 | 与索尼耳机并列，(3+4)/2=3.5 |
| 索尼耳机 | 150 | 3 | 3.5 | 同上 |
| 华为P60 | 120 | 5 | 5.5 | 与华为平板并列，(5+6)/2=5.5 |
| 华为平板 | 120 | 5 | 5.5 | 同上 |
| 戴尔笔记本 | 110 | 7 | 7 | 唯一值 |
| 联想电脑 | 105 | 8 | 8 | 唯一值 |
| 三星电视 | 95 | 9 | 9 | 唯一值 |
| MacBook Pro | 85 | 10 | 10 | 唯一值 |
示例5：并列排名的差异
情况：4个人得分相同，都是90分
最高分100分有1人，95分有2人
RANK.EQ结果：
100分：第1名
95分：第2名（2人并列）
90分：第4名（4人并列）← 注意：跳过了第3名
RANK.AVG结果：
100分：第1名
95分：第2.5名（(2+3)/2=2.5）
90分：第5名（(4+5+6+7)/4=5.5）
五、实际应用场景
场景1：学生成绩排名系统
在表1中添加排名列：
H列（总分排名）：=RANK.EQ(F2, $F$2:$F$21, 0)
I列（班级排名百分比）：=H2/COUNT($F$2:$F$21)
J列（前10%标记）：=IF(I2<=0.1, "优秀", "")
K列（排名变化）：与前次考试排名比较
场景2：销售业绩排行榜
在表2中添加排名列：
H列（季度排名）：=RANK.EQ(F2, $F$2:$F$16, 0)
I列（月度平均排名）：=RANK.EQ(G2, $G$2:$G$16, 0)
J列（奖金等级）：
=LOOKUP(H2, {1,4,8,12}, {"特等","一等","二等","三等"})
场景3：体育比赛排名系统
数据：运动员 | 100米成绩 | 跳远成绩 | 总分
1. 单项排名（时间越少越好）：
百米排名：=RANK.EQ(B2, $B$2:$B$20, 1)
2. 综合排名（分数越高越好）：
总分排名：=RANK.EQ(D2, $D$2:$D$20, 0)
3. 双排名差异：
=ABS(百米排名-总分排名) // 发现偏科选手
场景4：产品质量评分排名
数据：产品ID | 外观分 | 性能分 | 耐用分 | 总分
加权总分：=B2*0.3+C2*0.5+D2*0.2
综合排名：=RANK.AVG(E2, $E$2:$E$50, 0)
等级划分：
=IF(F2<=10, "A级",
IF(F2<=30, "B级",
IF(F2<=50, "C级", "D级")))
六、常见问题与解决方案
问题1：为什么我的排名结果不对？
可能原因及解决：
1. 区域引用错误：
错误：=RANK.EQ(A2, A2:A100, 0) // 相对引用
正确：=RANK.EQ(A2, $A$2:$A$100, 0) // 绝对引用
2. 数据包含非数值：
解决：=RANK.EQ(VALUE(A2), $A$2:$A$100, 0)
3. 排序方式理解错误：
order=0：降序（最大值排第1）
order=1：升序（最小值排第1）
问题2：如何处理并列排名？
需求：中国式排名（不跳过名次）
方法1：使用COUNTIFS
=SUMPRODUCT(($F$2:$F$21>F2)/COUNTIF($F$2:$F$21,$F$2:$F$21))+1
方法2：使用FREQUENCY
=SUM(--(FREQUENCY($F$2:$F$21, $F$2:$F$21)>0))-SUM(--(FREQUENCY($F$2:$F$21, IF($F$2:$F$21>F2, $F$2:$F$21))>0))
方法3：简单但有限制
=RANK.EQ(F2, $F$2:$F$21, 0)+COUNTIF($F$2:F2, F2)-1
问题3：如何排除某些值不参与排名？
假设成绩为0表示缺考，不参与排名：
=IF(F2=0, "缺考",
RANK.EQ(F2,
FILTER($F$2:$F$21, $F$2:$F$21>0),
0))
问题4：如何实现分组排名？
数据：班级 | 姓名 | 成绩
需求：每个班级内部排名
数组公式：
=SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100>C2))+1
七、高级技巧与应用
技巧1：动态排名区域
使用OFFSET定义动态区域：
=RANK.EQ(F2, OFFSET($F$2,0,0,COUNTA($F:$F)-1,1), 0)
或者使用表格：
=RANK.EQ([@总分], [总分], 0)
技巧2：条件排名
需求：只排名男生的成绩
数组公式（Ctrl+Shift+Enter）：
=SUM((B$2:B$100="男")*(C$2:C$100>C2))+1
技巧3：百分比排名
计算成绩在前百分之多少：
方法1：使用PERCENTRANK
=PERCENTRANK.INC($F$2:$F$21, F2)
方法2：手动计算
=RANK.EQ(F2, $F$2:$F$21, 0)/COUNT($F$2:$F$21)
技巧4：多条件加权排名
综合成绩 = 平时成绩×30% + 期中×30% + 期末×40%
综合排名 = RANK.EQ(综合成绩, 综合成绩区域, 0)
分项排名：
平时排名：RANK.EQ(平时成绩, 平时成绩区域, 0)
稳定性评分：=AVERAGE(各次排名标准差) // 越小越稳定
技巧5：排名变化追踪
本月排名：=RANK.EQ(F2, $F$2:$F$21, 0)
上月排名：从历史表获取
排名变化：=本月排名-上月排名
进步标记：=IF(排名变化<0, "↑进步", IF(排名变化>0, "↓退步", "-持平"))
八、RANK函数与其他函数组合
组合1：RANK + IF（条件显示）
=IF(F2>=60, RANK.EQ(F2, $F$2:$F$21, 0), "不及格")
=IF(RANK.EQ(F2, $F$2:$F$21, 0)<=10, "前十名", "")
组合2：RANK + VLOOKUP（查找对应信息）
先排名，再根据排名查找学生信息：
排名列：=RANK.EQ(F2, $F$2:$F$21, 0)
查找第1名姓名：
=VLOOKUP(1, CHOOSE({1,2}, H$2:H$21, B$2:B$21), 2, FALSE)
组合3：RANK + LARGE/SMALL（获取前N名）
获取前3名成绩：
第1名：=LARGE($F$2:$F$21, 1)
第2名：=LARGE($F$2:$F$21, 2)
第3名：=LARGE($F$2:$F$21, 3)
获取第1名姓名：
=INDEX($B$2:$B$21, MATCH(LARGE($F$2:$F$21, 1), $F$2:$F$21, 0))
组合4：RANK + COUNTIF（处理重复值）
在有重复值时添加辅助排名：
=RANK.EQ(F2, $F$2:$F$21, 0) + COUNTIF($F$2:F2, F2) - 1
九、性能优化建议
1. 避免整列引用
不好：=RANK.EQ(A2, A:A, 0) // 全列计算
好：=RANK.EQ(A2, $A$2:$A$1000, 0) // 限定范围
2. 使用排序减少计算
如果数据已排序，可以使用更简单的公式：
降序排序后：=MATCH(F2, $F$2:$F$21, -1)
升序排序后：=MATCH(F2, $F$2:$F$21, 1)
3. 批量计算
将排名公式放在一个单元格，然后向下填充
避免在条件格式中直接使用RANK函数
4. 考虑使用数据透视表
对于大数据量的排名，数据透视表的排序功能更高效。
十、综合实战案例
案例1：学生成绩综合排名系统
目标：创建包含以下功能的系统
1. 各科排名
2. 总分排名
3. 班级排名百分比
4. 进步退步分析
5. 奖励名单自动生成
实现步骤：
1. 计算各科排名：=RANK.EQ(C2, $C$2:$C$21, 0)
2. 计算总分排名：=RANK.EQ(F2, $F$2:$F$21, 0)
3. 百分比排名：=H2/COUNT($F$2:$F$21)
4. 进步分析：与前次排名比较
5. 奖励名单：=IF(H2<=10, "获奖", "")
案例2：销售团队绩效看板
目标：月度销售排名看板
功能：
1. 月度排名
2. 季度累计排名
3. 排名趋势（上升/下降）
4. 奖金自动计算
5. 可视化仪表板
公式示例：
月度排名：=RANK.EQ(C2, $C$2:$C$16, 0)
季度排名：=RANK.EQ(F2, $F$2:$F$16, 0)
排名变化：=本月排名-上月排名
奖金基数：=LOOKUP(月度排名, {1,2,3,4,5}, {10000,8000,6000,4000,2000})
案例3：产品质量评分排名系统
数据：产品ID | 批次 | 外观分 | 性能分 | 安全分 | 总分
需求：
1. 按批次内排名
2. 历史排名趋势
3. 等级划分（A/B/C/D）
4. 预警机制（连续下降）
实现：
批次内排名：=SUMPRODUCT(($B$2:$B$100=B2)*($F$2:$F$100>F2))+1
等级划分：=LOOKUP(排名, {1,5,10,15}, {"A","B","C","D"})
连续下降预警：=IF(AND(本次排名>上次排名, 上上次排名>上次排名), "预警", "")
十一、版本兼容性说明
| Excel版本 | RANK() | RANK.EQ() | RANK.AVG() | 建议 |
|---|---|---|---|---|
| 2003及以前 | ✓ | ✗ | ✗ | 使用RANK() |
| 2007 | ✓ | ✗ | ✗ | 使用RANK() |
| 2010-2013 | ✓ | ✓ | ✓ | 推荐RANK.EQ() |
| 2016-2019 | ✓ | ✓ | ✓ | 推荐RANK.EQ() |
| 365 | ✓ | ✓ | ✓ | 推荐RANK.EQ() |
跨版本建议：
=IFERROR(RANK.EQ(A2, $A$2:$A$100, 0),
IFERROR(RANK(A2, $A$2:$A$100, 0),
"函数不可用"))
十二、总结对比表
| 特性 | RANK() | RANK.EQ() | RANK.AVG() | 适用场景 |
|---|---|---|---|---|
| 相同值处理 | 并列相同排名，跳过后续名次 | 同RANK() | 返回平均排名 | 需要精确排名时使用EQ，需要平滑排名时使用AVG |
| 版本兼容 | 所有版本 | 2010+ | 2010+ | 旧文件用RANK，新文件用EQ |
| 语法 | =RANK(num,ref,order) | =RANK.EQ(num,ref,order) | =RANK.AVG(num,ref,order) | 参数相同 |
| 性能 | 相同 | 相同 | 稍慢（需计算平均） | 大数据量慎用AVG |
| 推荐度 | ★★★☆☆ | ★★★★★ | ★★★★☆ | 新项目一律用EQ |
十三、最佳实践建议
- 新项目一律使用RANK.EQ()，这是未来的标准
- 处理并列排名时，明确业务需求：
- 跳名次排名：RANK.EQ()
- 平均排名：RANK.AVG()
- 不跳名次（中国式排名）：使用COUNTIFS自定义公式
- 使用绝对引用：$F$2:$F$100
- 明确排序方向：0=降序，1=升序
- 处理错误值：=IFERROR(RANK.EQ(...), "数据异常")
- 考虑性能：避免在大型数据集频繁使用RANK函数
- 文档记录：在复杂排名逻辑旁添加注释说明
十四、练习题目
基础练习
- 使用RANK.EQ()计算学生总分排名
- 使用RANK.AVG()计算产品销量排名，观察并列差异
- 分别使用降序和升序对销售业绩排名
中级练习
- 实现中国式排名（不跳名次）
- 创建分组排名系统（按班级、部门等分组）
- 设计排名变化趋势分析
高级练习
- 构建多维度加权排名系统
- 实现动态排名仪表板
- 设计排名预警系统（连续下降预警）
实战挑战
- 模拟奥运会奖牌榜排名规则
- 实现电商商品多维度综合排名
- 设计员工绩效考核排名系统
最后建议：实际工作中，排名需求往往比想象的复杂。在开始前，一定要与业务方确认清楚排名规则，特别是如何处理并列情况。这是避免返工的关键！