Excel LARGE函数详解：提取前几名数据与排名实战案例
掌握LARGE函数的精髓，轻松处理数据排名与分析！
一、LARGE函数基础语法
函数定义
LARGE(array, k)函数用于返回数据集中第 k 个最大值。
参数说明
array：必需。需要从中选择第 k 个最大值的数组或数据区域
k：必需。返回值在数组中的位置（从大到小排列）
使用注意事项
如果数组为空，函数返回错误值
#NUM!如果 k ≤ 0 或 k 大于数据点个数，函数返回错误值
#NUM!如果区域中有 n 个数据点：
LARGE(array, 1)返回最大值LARGE(array, n)返回最小值
二、基础应用案例
案例1：求班级考试成绩的前后三名平均分
数据准备
解决方案
' 两个班前三名平均分
=AVERAGE(LARGE((B3:B17, E3:E17), {1,2,3}))' 两个班后三名平均分
=AVERAGE(SMALL((B3:B17, E3:E17), {1,2,3}))
公式解析
(B3:B17, E3:E17)：将两个班级的分数区域合并为一个数组{1,2,3}：使用常量数组同时获取第1、2、3大的值AVERAGE()：对获取的三个值计算平均值
效果展示
前三名平均：返回两个班级分数最高的3个分数的平均值
后三名平均：返回两个班级分数最低的3个分数的平均值
视频演示：
求两个班中的前三名的平均分和后三名的平均分（large函数）
三、进阶应用：提取前N名的完整信息
案例2：提取分数前三名的姓名和分数
数据准备
方法1：MOD+ROW组合法
提取姓名公式：
=INDEX(A:A, SMALL(MOD(LARGE($B$2:$B$16*10000+ROW($2:$16), {1,2,3}), 10000), {1;2;3}))
提取分数公式：
=INDEX(B:B, SMALL(MOD(LARGE($B$2:$B$16*10000+ROW($2:$16), {1,2,3}), 10000), {1;2;3}))
方法2：反向ROW计算法
提取姓名公式：
=INDEX(A:A, 18-MOD(LARGE($B$2:$B$16/1%%+18-ROW($2:$16), ROW(1:1)), 10^4))
提取分数公式：
=INDEX(B:B, 18-MOD(LARGE($B$2:$B$16/1%%+18-ROW($2:$16), ROW(1:1)), 10^4))
公式深度解析
核心技巧：构建辅助数值
两种方法都使用了相同的核心思路：
1. 构建"分数+行号"的复合值：
' 方法1：分数*10000 + 行号
B$2:$B$16*10000 + ROW($2:$16)' 方法2：分数/1%% + (最大行号-当前行号)
$B$2:$B$16/1%% + 18-ROW($2:$16)
为什么要乘以10000或除以1%%？
确保分数部分在数值中占高位
行号信息保存在低位
排序时先按分数排序，分数相同再按行号排序
步骤拆解（以方法1为例）
第一步：创建复合数值
假设B2=92, 行号=2
复合值 = 92*10000 + 2 = 920002
第二步：获取前三名的复合值
LARGE(复合值数组, {1,2,3})
' 返回前三大的复合值
第三步：提取行号
MOD(复合值, 10000)
' 取余数部分，得到原始行号
第四步：获取姓名/分数
INDEX(A:A, 行号)
' 通过行号引用对应数据
视频演示：
求前三个分数最高的姓名以及分数（large、small函数）
四、实际应用场景
场景1：销售业绩排名
' 提取销售冠军信息
=INDEX(A:A, MATCH(LARGE(B:B, 1), B:B, 0))' 提取前三名销售员
=INDEX(A:A, MOD(LARGE(B$2:$B$100*10000+ROW($2:$100), ROW(1:1)), 10000))
场景2：学生成绩分析
' 各科目前三名平均分
=AVERAGE(LARGE((数学成绩范围, 英语成绩范围, 语文成绩范围), {1,2,3}))' 进步最快前三名
=INDEX(姓名范围, MOD(LARGE((期末成绩-期中成绩)*10000+ROW(数据范围), {1,2,3}), 10000))
场景3：库存管理
' 销量最高的三种产品
=INDEX(产品名称范围, MOD(LARGE(销量范围*10000+ROW(销量范围), {1,2,3}), 10000))
五、实用技巧与注意事项
1. 处理重复值
当有相同分数时，上述方法会按行号顺序返回结果。如需其他处理方式，可增加辅助列：
' 在C列创建唯一值
=B2 + ROW()/100000
2. 动态获取前N名
' 使用单元格指定N值
=INDEX(A:A, MOD(LARGE(B$2:$B$100*10000+ROW($2:$B$100), ROW(1:1)), 10000))
' 下拉N行即可
3. 结合其他函数使用
' 前10%的数据
=LARGE(数据范围, ROUNDUP(COUNT(数据范围)*0.1, 0))' 排除异常值后的前几名
=LARGE(FILTER(数据范围, 数据范围<异常值阈值), k)
六、常见问题解答
Q1：为什么我的公式返回#NUM!错误？
检查k值是否大于数据点总数
确认数据范围是否存在空值或错误值
验证数组参数是否正确
Q2：如何提取后几名数据？
使用SMALL函数，语法与LARGE相似：
=SMALL(array, k) ' 第k小的值
Q3：Office 365新版本有更简单的方法吗？
是的，Office 365可以使用：
=SORT(数据范围, 排序列, -1) ' 降序排序
=TAKE(SORT(数据范围, 排序列, -1), 3) ' 取前三行
七、总结
LARGE函数是Excel中强大的排名分析工具，掌握它可以：
快速进行数据排名分析
提取前N名完整信息
与其他函数组合实现复杂分析
通过本文的案例和解析，相信你已经掌握了LARGE函数的核心用法。在实际工作中，根据具体需求灵活运用这些技巧，将大大提高数据分析效率！