打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
这对兄弟函数不常用,但是很厉害——Large与Small函数

今天给大家讲解两个重要的并且有用的兄弟函数,Large函数与Small函数。在讲解具体的案例之前,还是先来了解一下这两个函数的基本作用与语法。

【作用】Large函数与Small函数是两个获取前几个或者后几个最大值或者最小值。

【语法=Large/Small(数据区域,k)

【注意】其中k值表示要取这个数据区域里的第几个最大值或者最小值。值得注意的是当k值大于数据区域里的数据个数或者k值小于0时那么公式就会报错。

数据来源:以下数据截取新浪财务2019年Q1企业财报。

基本用法

要求,计算下表是Q1利润排名第2与倒数第2的企业的同比是多少?

在H6单元格中输入公式:=LARGE(D2:D20,2)

在H8单元格中输入公式:=SMALL(D2:D20,2)

查询

要求,计算下表是Q1利润排名第2与倒数第2的企业分别是哪一个?

在H6单元格中输入公式:

=OFFSET(B2,MATCH(LARGE(D2:D20,2),D2:$D$20,0)-1,,,)

在H8单元格中输入公式:

=OFFSET(B2,MATCH(SMALL(D2:D20,2),D2:$D$20,0)-1,,,)

上面的公式还可以有其他的方法,还可以写成LOOKUP函数与HLOOKUP函数。

在H6单元格中输入公式:

=LOOKUP(1,0/(LARGE(D2:D20,2)=D2:D20),B2:B20)

在H8单元格中输入公式:

=LOOKUP(1,0/(SMALL(D2:D20,2)=D2:D20),B2:B20)

在H6单元格中输入公式:

=HLOOKUP(B2,$B$2:$B$20,MATCH(LARGE(D2:D20,2),D2:$D$20,0),0)

在H8单元格中输入公式:

=HLOOKUP(B2,$B$2:$B$20,MATCH(SMALL(D2:D20,2),D2:$D$20,0),0)

注意:当期还可以使用INDEXb函数与INDIRECT函数,当然也可以使用VLOOKUP函数。这里就不一一再做举例了,特别强调的是如果有重复的数值的话那就结果就不一定准确了。

前三与后三的利润同比平均值

要求,计算下表是Q1利润排名前三与后三的平均值是多少?

在H6单元格中输入公式:

=AVERAGE(LARGE(D2:D20,{1,2,3}))

在H8单元格中输入公式:

=AVERAGE(SMALL(D2:D20,{1,2,3}))

4算指定条件后的最值

要求,计算下表是Q1利润小于平均值的最大值与大于平均值的最小值。

在H6单元格中输入公式:

{=LARGE(IF(D2:D20<AVERAGE(D2:D20),D2:D20),1)}

在H8单元格中输入公式:

{=SMALL(IF(D2:D20>AVERAGE(D2:D20),D2:D20),1)}

注意:上面的两个公式的两边的花括号是按组合键<Ctrl+Shift+Enter>键完成后自动添加的,不是手动添加的。

一对多查询

要求,查找公告日期为2019-6-3的所有企业名称。

在G4单元格中输入公式:

{=IFERROR(INDEX($B$2:$B$20,SMALL(IF($E$2:$E$20=$G$3,ROW(E$2:E$20)-1,4^8),ROW(A1))),'')}

按组合键<Ctrl+Shift+Enter>完成,向下填充至空白出现为止。

另外呢,SMALL与LARGE函数还有很多的其他的用途,这里就不一一讲解了,后面的文章遇到了再给大家做详细的介绍~

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
Vlookup的亲姊妹,可总是被人们给忽略了,原来Hlookup也这么好用!
office excel最常用函数公式技巧搜集大全(13.12.09更新)19
72个反向查找的公式套路,都看明白的就成精了!
Excel函数应用之查询与引用函数(下)
excel查询与引用函数:HLOOKUP、LOOKUP、MATCH、VLOOKUP(2)
INDEX、VLOOKUP、HLOOKUP、LOOKUP函数
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服