Excel的LARGE&SMALL函数,评分/排名/筛选超好用!
- 2026-04-10 15:59:52
点击蓝字 关注我吧！
8
人力 李四
09:51
老鱼，最近述职，要取去掉
最高分和最低分求平均分。
用哪个函数好？
AVERAGE和
LARGE就行。
看我的~
今天一起来学习LARGE、SMALL函数的功能语法、使用场景和注意事项吧！
后台回复“大小”，可以获得练习案例哦！
一、功能语法
1.LARGE函数
返回数据集中第k个最大值。
语法
=LARGE(数组,K)
数组(必填)：需要确定第k个最大值的数组或数据区域；
K(必填)：返回值在数组或数据单元格区域中的位置（从大到小排）。
【举例】
=LARGE(A1:A10,3)→返回A1:A10区域中第3大的数值
2.SMALL函数
返回数据集中第k个最小值。
语法
=SMALL(数组,K)
数组(必填)：需要确定第k个最小值的数组或数据区域；
K(必填)：要返回的数据在数组或数据区域里的位置（从小到大）。
【举例】
=SMALL(A1:A10,1)→返回A1:A10区域中最小的数值
二、使用场景
1.计算去掉最高分和最低分的平均分
在述职打分、比赛评分中非常常见。
【方法】
在I2输入=AVERAGE(LARGE(C2:H2,{2,3,4,5}))，双击向下填充就完成了！
【解读】
LARGE(C2:H2,{2,3,4,5})会返回6个分数中，第2~5大的数字(去掉最高和最低)；
再用AVERAGE对4个中间分数求平均。
【备选】
也可以把公式变成=AVERAGE(SMALL(C2:H2,{2,3,4,5})),取第2~5小的分数来求平均。
2.返回销售额前三名
①返回前三的销售额
公式=LARGE(C2:C11,{1,2,3})，结果会横向显示前三名的销售额。
如果要在结果纵向显示，把公式修改为：=LARGE(C2:C11,{1;2;3})，大括号里的逗号改成分号。
② 返回前三名的销售人员
F2到H2单元格得到前3的销售额后，在F3单元格输入：
=XLOOKUP(F2,$C$2:$C$11,$B$2:$B$11)，向右填充到H3单元格，就完成啦！
【解读】
3.销售1部排名前三的销售额
输入数组公式=LARGE(IF(C2:C11="销售1部",D2:D11),{1,2,3})。
【解读】
(IF(C2:C11="销售1部",D2:D11)：如果该行的C列是“销售1部”，则返回D列对应的销售额，否则返回FALSE。最终返回一个由FALSE和销售额组成的数组。
LARGE函数会忽略逻辑值FALSE，从数组中提出最大的3个数值。
三、注意事项
1.参数"K"
K必须是>0且≤数组内的数字个数的整数。比如数组有5个数字，K的取值范围是1到5。
2.处理重复值
如果数组中存在重复值，比如两个相同的最低分，SMALL(数组,1)和SMALL(数组,2)都会返回相同的值。
3.非数值数据
如果数组中包含文本、逻辑值或者空单元格，它们都会被忽略。
错误值(如#N/A,#DIV/0!等)不会被忽略，会导致LARGE和SMALL返回错误值。
4.数组公式
如果你使用的是Office365、Excel2021+、WPS2023+版本，写完数组公式后，按Enter，结果会自动“溢出”到相邻单元格。
如果是其他版本，都需要输入公式后，同时按下Ctrl+Shift+Enter，公式两端出现{}，才能正确计算。
点赞、关注
再划走呀！
