excel数组函数有哪些？有关函数公式大全

编按：哈喽，大家好！相信在看过前两期区间查找的教程后，小伙伴们已经大致掌握了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函数对于条件区域需要“升序排列”；比如“逻辑值”是如何参与计算的；比如“万金油”公式的三步走等等。哪怕你只学到了规范的区间书写方式，也算是不虚看此篇。