编按:哈喽,大家好!相信在看过前两期区间查找的教程后,小伙伴们已经大致掌握了6种关于区间查找的方法了,可以说在区间查找的问题上,已经能沉着应对了。但excel最大的魅力就是它的多元性,任何一道题都是一题多解的。本篇是区间查找系列的最后一篇教程——数组函数篇,同时它也是本次系列教程中最难的一篇。快跟着小编一起来学习吧!
【引言】
通过前两篇教程的内容,我们了解了区间取值问题的常规解法,也了解了嵌套函数的解法,应该说我们日常工作中再遇到此类问题,已经有6种方法可以快速统计数据了。那么,此篇的内容,就让我们来升华一下自己的Excel函数技能,看看数组函数是如何解决“区间取值”的!
【数据源】
要求:根据B列的数值,在E列的范围条件中找到对应范围在H列的区间系数,并提取到C列计提系数中。
图1
【解题方案】
方法七:SUM函数的“数组函数用法”
图例:
图2
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单元格为例,我们可以通过工具栏中公式——公式审核——公式求值来看到这些内容。
图3
当我们选中C2单元格,然后鼠标单击“公式求值”按钮,就会弹出公式求值窗口,此时就可以看到我们设置的函数内容。接着我们一下一下的点击“求值”按钮,就会发现,函数按步骤显示出了每个环节的运算结果。
图4
将两个比较运算的部分分别进行数组运算,比较值为真返回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} ,藉此得到了我们计算的唯一值,再乘以区间系数,就得到如下图显示的内容。
图5
最后的结果也就很清楚了。
方法八:MAX函数的“数组函数用法”
图例:
图6
C2单元格函数:
{=MAX((B2>=$G$2:$G$6)*$H$2:$H$6)}
输入完成后,按数组函数的结束键CTRL+SHIFT+ENTER三键结束。
函数解析:
看了方法七的用法,是不是感觉“太硬,不好下嘴”?那么本例就给大家介绍一个简单的数组函数吧,虽然简单,但是如果你不会原理,还是不能正常的应用。看一下“公式求值”给出的运算结果吧。
图7
目标值大于条件值,则为TRUE,否则为FALSE,得到了一个数列,再乘以区间系数H2:H6区域,就得到了{0;0.01;0.03;0;0}的数列。
图8
最后用MAX函数取值,就完成了我们区间取值的要求。
方法九:INDEX+MAX函数的“数组函数用法”
图例:
图9
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函数部分,返回了什么呢?我们通过“公式求值”的方式,就可以很轻松的得到答案,如下图所示:
图10
通过这个过程我们看到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函数对于条件区域需要“升序排列”;比如“逻辑值”是如何参与计算的;比如“万金油”公式的三步走等等。哪怕你只学到了规范的区间书写方式,也算是不虚看此篇。