各种方法求函数最小值(最大最小你会找)

EXCEL进阶课堂 · 函数说 持续更新!我们将为各位小伙伴提供更加专业、更加精炼、更加实用的EXCEL操作技能,帮助大家轻松解决工作任务,提高工作效率,不再做不停加班的表哥,表姐。欢迎各位小伙伴转发、点赞、讨论,更欢迎私信获取练习素材,刻意练习才能学有收获。

这是函数说的第30篇教程。

EXCEL中进行数据分析时,经常会涉及到最大值和最小值,而两个问题可以轻松地被MAX和MIN两个函数轻松解决。但是有一个问题,如果找某个数据当中的前几大和是前几小呢?有一案例如下图所示:

各种方法求函数最小值(最大最小你会找)(1)

案例中的问题,是让我们求每科排名前3的成绩和排名后3的成绩。这个怎么解决了呢?有小伙伴说:这个还不简单,先按科目成绩排序,然后复制进行了。

如果是这样去做,当然是可以完成任务,达到目标的。但是,这种方法至少有两个明显的不足:

其一,操作繁琐,容易出现误操作。每科都要先排序,然后去复制和粘贴,在手抖的情况下,很容易出现误操作。

其二,结果无法自动更新。在实际工作当中,数据源里面的数据出现误输入的情况是非常常见的。用这种方法,如果数据源里面的数据发生了变化,结果是不会自动变化的,除非你全部重新再做一次。

看到这里,相信有的小伙伴已经开始频频点头了,因为他们在工作当中确实采用的这种方法,也遇到了方法不足造成的困扰。在这里,进阶君给大家介绍两个很简单但是使用频率不高的函数,它们可以轻松应对这种问题。

1 从大到小的顺序,按指定排名取值函数——LARGE函数

(1)函数格式:=LARGE(数据区域,排名序号)

(2)函数功能:在数据区域中,以从大到小为顺序,按指定的排名序号取出相应的值。

(3)应用举例:

各种方法求函数最小值(最大最小你会找)(2)

上图例1:=LARGE(A1:A10,1),表示在A1:A10这个数据区域中,按从大到小,找排名为第1名的值,就相当于MAX函数的作用。

各种方法求函数最小值(最大最小你会找)(3)

上图例2:=LARGE(A1:A10,2),表示在A1:A10这个数据区域中,按从大到小,找排名为第2名的值,得到的结果为34。

于是案例中,求语文前3名的成绩,就可以分别在相应的单元格内输入公式:=LARGE(C2:C11,1) =LARGE(C2:C11,2) =LARGE(C2:C11,3),这样就取得排名为前3的值。具体操作过程及效果,如下动图所示。

各种方法求函数最小值(最大最小你会找)(4)

2 从小到大的顺序,按指定排名取值函数——SMALL函数

(1)函数格式:=SMALL(数据区域,排名序号)

(2)函数功能:在数据区域中,以从小到大为顺序,按指定的排名序号取出相应的值。

(3)应用举例:

各种方法求函数最小值(最大最小你会找)(5)

上图例1:=SMALL(A1:A10,1),表示在A1:A10这个数据区域中,按从小到大,找排名为第1名的值,就相当于MAIN函数的作用。

各种方法求函数最小值(最大最小你会找)(6)

上图例2:=SMALL(A1:A10,2),表示在A1:A10这个数据区域中,按从小到大,找排名为第2名的值,得到的结果为2。

于是案例中,求语文后3名的成绩,就可以分别在相应的单元格内输入公式:=SMALL(C2:C11,1) =SMALL(C2:C11,2) =SMALL(C2:C11,3),这样就取得排名为后3的值。具体操作过程及效果,如下动图所示。

各种方法求函数最小值(最大最小你会找)(7)

3 总结与思考

通过LARGE函数和SMALL函数,我们求出了语文的前3成绩和后3成绩,其它两科同样的方法进行。

但是有两个问题值得思考:

第一,如何让LARGE函数和SMALL函数要查找的数据区域自动拓展?案例当中,只有10个人人的成绩,但是我们是可以往里面添加更多人的信息。如何在增加人的情况,让它们查找的数据区域自动拓展,从而不进行公式修改呢?

第二:要查找的名次如何自动调整?我们在查找前3名成绩时,分别使用到的公式为:=LARGE(C2:C11,1) =LARGE(C2:C11,2) =LARGE(C2:C11,3),这里面的查找排名序号是手动输入进去的。如何让查找的名次值可以自动调整,从而实现公式复制,减少公式的输入量呢?

各位小伙伴们,你们有什么好的办法呢?喜欢大家在留言区里讨论交流。


为方便小伙伴们学习,进阶君将原始素材共享出来,获取素材的方法:

第一步:关注 Excel进阶课堂。

第二步:私信 Excel进阶课堂,因为设定的是自动回复,所以内容一定要准确

私信内容:练一练

第三步:根据得到信息打开网盘,找到 第30讲 快速取指定排名数值 工作簿 自行下载

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页