excel查找技巧:数组函数在区间查找中的应用解析

时间:2020-08-05 来源:王佳奇说职场 栏目:游戏

编按:哈喽,大家好!相信在看过前两期区间查找的教程后,小伙伴们已经大致掌握了6种关于区间查找的方法了,可以说在区间查找的问题上,已经能沉着应对了。但excel最大的魅力就是它的多元性,任何一道题都是一题多解的。本篇是区间查找系列的最后一篇教程——数组函数篇,同时它也是本次系列教程中最难的一篇。快跟着小编一起来学习吧!

*********

【引言】通过前两篇教程的内容,我们了解了区间取值问题的常规解法,也了解了嵌套函数的解法,应该说我们日常工作中再遇到此类问题,已经有6种方法可以快速统计数据了。那么,此篇的内容,就让我们来升华一下自己的Excel函数技能,看看数组函数是如何解决“区间取值”的!

*********

【数据源】

要求:根据B列的数值,在E列的范围条件中找到对应范围在H列的区间系数,并提取到C列计提系数中。

展开剩余87%

【解题方案】

方法七:SUM函数的“数组函数用法”

C2单元格函数:

{=SUM((B2>=$G$2:$G$6)*(B2<$G$3:$G$7)*($H$2:$H$6))}

输入完成后,按数组函数的结束键CTRL+SHIFT+ENTER三键结束。

函数解析:

这个案例需要一个辅助单元格,就是G7单元格。在G7单元格输入了一个903E7值,这是一个科学计数法,意思就是903*10的七次方,等于903*POWER(10,7)=9030000000,目的是为了找一个临界值。

那么有的表友可能会问了,为什么要加这个值?

答:为了区域相等,错位找到区间极值!

由图中不难看出G2:G6就是每个“条件”的最小极值,那么最大极值呢,是不是错位之后G3:G7区域呢?可是G7是空值,默认为0,所以我们加了一个绝对大的值代替了∞。

这里也教大家一个学习数组函数的小窍门,就是如何看到那些看不到的内存数据。以C2单元格为例,我们可以通过工具栏中公式——公式审核——公式求值来看到这些内容。

当我们选中C2单元格,然后鼠标单击“公式求值”按钮,就会弹出公式求值窗口,此时就可以看到我们设置的函数内容。接着我们一下一下的点击“求值”按钮,就会发现,函数按步骤显示出了每个环节的运算结果。

将两个比较运算的部分分别进行数组运算,比较值为真返回TRUE,比较值为假返回FALSE,这样的运算结果得到了两个由TRUE和FALSE组成的数列,{TREU;TRUE;TRUE;FALSE;FALSE}和{FALSE;FALSE;TRUE;TRUE;TRUE}。

这两个值在EXCLE中被叫做“逻辑值”,既然是“值”,就是可以参与计算的,TRUE是1,FALSE是0。那么{TREU;TRUE;TRUE;FALSE;FALSE}乘以{FALSE;FALSE;TRUE;TRUE;TRUE},就可以理解为{1;1;1;0;0}*{0;0;1;1;1}={0;0;1;0;0},藉此得到了我们计算的唯一值,再乘以区间系数,就得到如下图显示的内容。

最后的结果也就很清楚了。

方法八:MAX函数的“数组函数用法”

C2单元格函数:

{=MAX((B2>=$G$2:$G$6)*$H$2:$H$6)}

输入完成后,按数组函数的结束键CTRL+SHIFT+ENTER三键结束。

函数解析:

看了方法七的用法,是不是感觉“太硬,不好下嘴”?那么本例就给大家介绍一个简单的数组函数吧,虽然简单,但是如果你不会原理,还是不能正常的应用。看一下“公式求值”给出的运算结果吧。

目标值大于条件值,则为TRUE,否则为FALSE,得到了一个数列,再乘以区间系数H2:H6区域,就得到了{0;0.01;0.03;0;0}的数列。

最后用MAX函数取值,就完成了我们区间取值的要求。

方法九:INDEX+MAX函数的“数组函数用法”

C2单元格函数:

{=INDEX($H$2:$H$6,MAX(IF(B2>=$G$2:$G$6,ROW($1:$5),0)))}

输入完成后,按数组函数的结束键CTRL+SHIFT+ENTER三键结束。

函数解析:

这个函数的思路,就是“传说中的万金油”函数了。这种函数基本来说分为三步走:

1.条件赋值

通过IF函数的判断,给每一个值都对应上一个序号。正常的序号部分我们经常使用ROW函数或者COLUMN函数,因为行号和列号一般都是等差排列的1、2、3…这个形式,如果不满足条件的话,我们往往给这个位置设置0或者99^99,意思就是“相对最小”或者“相对最大”。

那么我们本例中的IF函数部分,返回了什么呢?我们通过“公式求值”的方式,就可以很轻松的得到答案,如下图所示:

通过这个过程我们看到IF函数的运算结果是{1,2,3,0,0}。

2.按需要取序号

因为我们上面的IF部分是做出想要的序号,那么第二步就是按要求取出我们需要的序号了。取出最后一次满足条件的值,也就是最大值,所以我们使用了MAX函数。

在万金油函数中,我们经常会看到SMALL或者LARGR函数,这也是一种提取序号的过程,只不过是逐个从小到大或者从大到小的取值(不是取一次值),有兴趣的同学可以看下我们往期的教程《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读》,和今天我们的主题偏离较大就不多介绍了。

3.回归到INDEX函数区间取值

取到了我们需要的序号,第三步就顺理成章的又回归到了INDEX函数上了,只不过之前我们使用的是MATCH函数提取的序号,这次我们用的是MAX+IF函数的方式。有没有学会呢?

【编后语】

数组函数并不难,只是大家可能还没有找到窍门。其实数组函数也挺“有趣”的,它能在你不会使用VBA的情况下,解决一些比较复杂的运行效果。所以学无止境,有的技能可以不用,但还是要会的。

EXCEL最大的魅力是它的多元化,任何一道题,都是一题多解的,关键还是思路。这篇文章写得很长,分了上、中、下三篇,但是依然不敢说已经收录齐了,只是可能逻辑上有重复的,就没有收录。

会一两种方法可以解决问题就可以了,列出如此多的方案,只是希望大家能从中学到每个方法的知识点:比如VLOOKUP函数对于条件区域需要“升序排列”;比如“逻辑值”是如何参与计算的;比如“万金油”公式的三步走等等。哪怕你只学到了规范的区间书写方式,也算是不虚看此篇。

****部落窝教育-excel数组函数查找应用****

原创:E图表述/部落窝教育(未经同意,请勿转载)

关键词 : 查找 数组 区间 函数

本站部分内容来源于互联网权威网站,文章内容并不代表本站的观点或立场,如有关于文章内容,版权或其它问题请联系我们删除;

相关资讯

Copyright@2019-2020 www.jznyw.cn 九州资讯网 All Rights Reserved 鲁ICP备17005488号-5

九州资讯网