请选择 进入手机版 | 继续访问电脑版
在线投稿 文字标题 文字标题 文字标题 文字标题 文字标题
切换皮肤
excel图文教程:极值的查找方法 ?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2F0c65e7f2j00q7oz31001mc000hs00a0m.jpg

编按:哈喽,大家好!说到excel中的“最值”,在日常的数据统计和分析中,经常会用到。比如计算本月的销量冠军、前三名销量的产品名称、本月最低销量等等,今天我们就一起来认识一下excel中的“最值”函数。学习更多技巧,请收藏部落窝教育excel图文教程。

对于数值大小敏感的表格,最值是数据分析中绕不开的话题,最值包括最大值、最小值、第几大的某个值、第几小的某个值,通过MAX()、MIN()、LARGE()、SMALL()函数,可以反映出数据最好和最坏水平,下面就跟着小编往下看看吧!
一、计算一组系列数字中的最大/最小值
如果要取得一组纯数字中的最大值,可以使用MAX()函数来实现,同样,要取得这组数字中的最小值,则可以使用MIN()函数来实现。函数很简单,大家应该也用过,小编简单介绍下:
MAX函数的语法格式为:MAX(number1,[number2],…)。
MIN函数的语法格式为:MIN(number1,[number2],…)。
从函数的语法格式上可以看出,MAX()函数、MIN()函数的参数和用法都相同,两个函数都至少需要一个number参数,最多可以指定255个参数,每个number都可以是数字或包含数字的名称、数组或引用。
如果number是数组或引用,其中的空白单元格、逻辑值或是文本将被忽略。
下面我们来看一个例子。
某销售公司将一年12个月的数据全部统计到了一张工作表中,现在需要用黄色填充每个月的最大销售数量,用红色填充最小销售数量。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2Fa43db150p00q7oz330052c000lk00f5m.jpg
我们可以先使用MAX和MIN找出每月数据的最大值、最小值,接着使用条件格式对满足条件的单元格应用对应格式。这里小编要提醒下各位,对单元格填充颜色,尽量使用条件格式,而不是单纯的填充颜色,这样一来当数据发生变化时,填充的颜色也会随之变换。
具体操作步骤如下:
① 选择C4:C11单元格区域,单击“条件格式”按钮,选择“新建规则”命令。
9bd2c13bg00q7oz33009vc000qj00d3m.gif
② 选择“使用公式确定要设置格式的单元格”选项,在下方输入公式“=C4=MAX(C$4:C$11)”后,单击“格式”按钮。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2Fd9cbd9c7p00q7oz33004ec000j300bvm.jpg
③ 在弹出的对话框中的“填充”选项卡下,选择黄色作为单元格的填充颜色,并单击“确定”按钮,关闭所有对话框。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2F86d575e9p00q7oz320029c000h900g1m.jpg
④ 按照上面的操作步骤,在条件格式设置框中输入公式“=C4=MIN(C$4:C$11)”后,单击“格式”按钮。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2F3ca953a6p00q7oz33003uc000g300bhm.jpg
⑤ 在弹出的对话框中的“填充”选项卡下,选择红色作为单元格的填充颜色,单击 “确定”按钮,关闭所有对话框。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2Fa1d49d4ep00q7oz33002ac000h900g1m.jpg
⑥ 再次单击“条件格式”按钮,在弹出的下拉菜单中选择“管理规则”命令,在打开的对话框中,将两个条件格式的“应用于”范围改为C4:N11单元格区域,单击“确定”按钮关闭对话框。
a58bf1f9g00q7oz3300akc000sy00h7m.gif
本例中使用了两个公式,但这两个公式结构完全相同,其中,第一个公式“=C4=MAX(C$4:C$11)”用于确定C4单元格是否为C4:C11单元格区域的最大值,而第二个公式“=C4=MIN(C$4:C$11)”则用于确定C4单元格是否为C4:C11单元格区域的最小值,如果是,则返回TRUE,由条件格式设置指定的单元格格式,否则返回FALSE,单元格格式保持不变。
二、计算一组数据中第K个最大/最小值
如果想知道一个数据系列中的第k个最大值,可以使用LARGE()函数得到,相反,若想知道其中的第k个最小值,则可以使用SMALL()函数来得到,语法格式如下:
LARGE函数的语法格式为:LARGE(array,k)。
SMALL函数的语法格式为:SMALL(array,k)。
LARGE函数和SMALL函数的语法格式和参数完全相同。其中array参数表示要处理的数值序列,k参数表示返回第几大/小的值。话不多说,上例子。学习更多技巧,请收藏部落窝教育excel图文教程。
现需要根据每种商品的销售数量,找出本月销量排名前3和排名后3的商品名称,以及对应的销售数量。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2F26fc8233p00q7oz32004ec000oe00j5m.jpg
想找出排名前3和排名后3的数值,可以分别使用LARGE函数和SMALL函数来完成,当找出这些数值后,可以使用MATCH函数来找出其在序列中的位置,然后通过INDEX函数返回其对应商品的名称,输出所需结果。具体操作步骤如下:
①选择F3:F5单元格区域,输入公式“=LARGE($B$3B$16,D3)”按“Ctrl+Enter”组合键完成输入,公式用于获取B3:B16单元格区域中前三个最大的值。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2F90d7dff7p00q7oz31000gc000j900b6m.jpg
②选择E3:E5单元格区域,输入公式“=INDEX($A$3A$16,MATCH(F3,$B$3B$16,0))”按“Ctrl+Enter”组合键完成输入。通过MATCH函数返回F列中的销售数量在$B$3B$16区域中所在的位置,最后利用INDEX函数返回销售数量对应的商品名称。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2F5b8384e8p00q7oz31000ec000ik00b3m.jpg
③选择E3:E5单元格区域,输入公式“=SMALL($B$3B$16,H3)”按“Ctrl+Enter”组合键完成输入,公式用于获取B3:B16单元格区域中前三个最小的值。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2F8dc46fbap00q7oz31000ec000ir00b4m.jpg
④选择I3:I5单元格区域,输入公式“=INDEX($A$3A$16,MATCH(J3,$B$3B$16,0))”按“Ctrl+Enter”组合键完成输入。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2Fe0cc10e6p00q7oz31000fc000in00b4m.jpg
在上述例子中,由于商品类别少,销售数量没有出现重复的情况,因此可以得到正确的结果,但如果商品类别较多,销售数量出现重复值,此时再用上面的公式返回销售数量对应的商品名称,就只会返回第一个与销售数量对应的商品名称,如下图所示。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2Fbdfa7f0bp00q7oz32001gc000cx009fm.jpg
要解决此问题,就必须让INDEX函数的第二参数返回每个重复值在数据区域中的实际位置,可以通过LARGE函数、IF函数、ROW函数、以及COUNTIF函数嵌套完成,返回商品名称的公式如下:“{=INDEX($A$3A$16,LARGE(IF($B$3B$16=F3,ROW($114)),COUNTIF($F$3:F3,F3)))}”,由于是数组公式,需要按“Ctrl+Shift+Enter”组合键结束公式。
?url=http%3A%2F%2Fdingyue.ws.126.net%2F2020%2F0324%2Fedf80e7ap00q7oz31000hc000nq00b5m.jpg
公式解析:用IF函数判断$B$3:$B$16区域中的值是否等于F3,等于则返回数量对应的序号,不等于则返回FALSE。于是可以得到一组由逻辑值FALSE和序号组成的数组,作为LARGE函数的第一参数。接着使用COUNTIF函数判断F3单元格在$F$3:F3区域中出现的次数,将得到的次数,作为LARGE函数的第二参数,用于确定返回这个数组中第几大的值。最后将LARGE函数的结果,作为INDEX的第二参数,返回重复数量对应的不同商品名称。
(请注意该公式中COUNTIF函数对于数据区域“$F$3:F3”的引用方法,这里我们限制了计数区域的范围,这个计数范围会随着公式的下拉不断扩大,需要统计的数量就会逐渐增多,这样一来,就可以统计出该重复值是第几次重复出现。)
好了,关于计算数值最值的方法就介绍到这,我们学习了MAX()、MIN()、LARGE()、SMALL()函数,同时也复习了条件格式、INDEX()、MATCH()等函数的用法。学习更多技巧,请收藏部落窝教育excel图文教程。
****部落窝教育-excel如何提取极值****


回复

使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则


    Archiver|手机版|小黑屋|齐聚无忧 |网站地图

    Powered by Discuz! X3.4  © 2001-2013 Comsenz Inc.