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

十三、最佳实践建议

1. 新项目一律使用RANK.EQ()，这是未来的标准
2. 处理并列排名时，明确业务需求：
3. 跳名次排名：RANK.EQ()
4. 平均排名：RANK.AVG()
5. 不跳名次（中国式排名）：使用COUNTIFS自定义公式
6. 使用绝对引用：$F$2:$F$100
7. 明确排序方向：0=降序，1=升序
8. 处理错误值：=IFERROR(RANK.EQ(...), "数据异常")
9. 考虑性能：避免在大型数据集频繁使用RANK函数
10. 文档记录：在复杂排名逻辑旁添加注释说明

十四、练习题目

基础练习

1. 使用RANK.EQ()计算学生总分排名
2. 使用RANK.AVG()计算产品销量排名，观察并列差异
3. 分别使用降序和升序对销售业绩排名

中级练习

1. 实现中国式排名（不跳名次）
2. 创建分组排名系统（按班级、部门等分组）
3. 设计排名变化趋势分析

高级练习

1. 构建多维度加权排名系统
2. 实现动态排名仪表板
3. 设计排名预警系统（连续下降预警）

实战挑战

1. 模拟奥运会奖牌榜排名规则
2. 实现电商商品多维度综合排名
3. 设计员工绩效考核排名系统

---

最后建议：实际工作中，排名需求往往比想象的复杂。在开始前，一定要与业务方确认清楚排名规则，特别是如何处理并列情况。这是避免返工的关键！