Excel揭秘11：强大而美妙的数组公式讲解Excel中数组公式的概念、原理及示

微信公众号：excelperfect

Excel中有一类称作数组公式的公式，有点难以理解，但很强大，能够完成很多不可思议的任务。

然而，要想精通并熟练运用数组公式，需要大量的练习。本文只是简单地介绍数组公式，让感兴趣的朋友对其有所了解，为进一步的研究和应用打下基础。

为什么要学习和使用数组公式

既然数组公式比较复杂且难于理解，那为什么还要学习和使用数组公式呢？

使用数组公式，可以使Excel完成我们认为不可能的任务，或者说要使用VBA才能完成的任务，并且在有些情形下，数组公式可能是一个非常有效的解决方案。当然，好奇心也会驱使我们想要学习进阶去创建更高级的公式，并且在学会数组公式的基本使用后想要创建更有效的数组公式。在掌握数组公式后，在面对创建Excel解决方案时又多了一份新的武器。我想，这可能是想要学习数组公式的一些原因。

在某些情形下，除非使用VBA，使用数组公式可能是实现目的的唯一办法。在使用普通的公式时，我们可能需要辅助列或一些中间步骤，然而，数组公式可以提供一次性的解决方案，省掉中间步骤。当然，高级筛选、数据透视表、以及新增的一些数组函数能够解决使用数组公式实现的任务，但是当输入发生变化时它们不能自动更新或者有局限，然而数组公式能够立即更新，这是其一大优势。

我们可以使用数组公式来判断数据是否与指定区域中的数据相匹配，可以统计单元格区域中不重复值的数量，可以提取单元格区域中的不重复数据，将列数据转换为行数据，…，等等。

什么是数组公式

在认识数组公式之前，先看看通常对于下图1所示的工作表求总销售额的过程。

图1

正如图1中所看到的，要求这四种水果的总销售额，先使用公式求出每种水果的销售额，然后相加即可。总共使用了4个公式。其实，我们可以只使用一个公式来求总销售额，如图2所示。

图2

在单元格C7中输入公式：

=SUM(B2:B5*C2:C5)

然后同时按下Ctrl+Shift+Enter键完成公式输入。

这个公式就是数组公式，即有操作运算符，运算一组数据而不是单个数据，传递的结果也是一组数组，而最终的结果可能是单个的数据，也可能是一组数据。

注意，当我们按下Ctrl+Shift+Enter键完成输入后，Excel会自动在公式两侧添加上花括号{}，无须手工输入它们。

数组公式原理

仍以上文所示的工作表为例，看看数组公式的计算过程，从而了解其运算原理。

首先，公式中的B2:B5与C2:C5分别被单元格数据替换成数组：

=SUM({5.8;1.2;1.1;3.5}*C2:C5)

=SUM({5.8;1.2;1.1;3.5}*{100;350;200;300})

然后，两个数组对应元素相乘得到：

=SUM({580;420;220;300})

最后，数组作为SUM函数的参数求和，得到最后的结果2270。

可以看到，数组公式是处理一组或一系列数据而不是单个数据的公式。它能够返回单个的值，如本例所示，也能够返回一组数据，如下面的例子。

选择一列中任意9个单元格，输入下面的数组公式：

=ROW(1:9)

结果如下图3所示。

图3

该公式产生一个数组{1;2;3;4;5;6;7;8;9},并将它们依次输入到单元格。

注意，输入完公式后，一定要记得按下Ctrl+Shift+Enter键。

本文中，凡是要求输入数组公式的，都是指在输入完公式内容后再按Ctrl+Shift+Enter键，这才完成了数组公式的输入。

再看一个例子，了解数组公式的逻辑运算。

如下图4所示的工作表，记录着各种产品由不同的销售人所售卖的数量。

图4

想要计算张三或者李四所销售的手机数量，可以使用数组公式：

=SUM((A2:A10="手机")*((B2:B10="张三")+(B2:B10="李四"))*(C2:C10))

结果如下图5所示。

图5

这个公式创建了3个数组：

第一个数组是一系列的TRUE或FALSE值，是由单元格区域A2:A10中的数据与“手机”比较后的结果。注意，Excel会将“手机”的数量扩展到与所比较的单元格数量相同（下面的相同）。结果数组为：{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}。

第二个数组由一系列的0或1组成，是由单元格B2:B10中的数据与“张三”和“李四”比较后的运算结果。其中，单元格区域B2:B10与“张三”比较生成一系列TRUE或FALSE值组成的数组：{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}，与“李四”比较也生成一系列TRUE或FALSE值组成的数组：{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}。将这两个数组相加，此时Excel将TRUE转换为1，FALSE转换为0，得到数组{1;0;1;1;1;1;1;1;0}。

第三个数组由单元格C2:C10中的数值组成，即{1200;200;300;120;220;50;600;100;800}。

然后，将这3个数组对应的元素相乘。与前面数组相加相同，在数组相乘时，Excel将TRUE和FALSE分别转换为1和0。因此，3个数组相乘的公式为：

{1;0;1;0;0;0;1;0;1}*{1;0;1;1;1;1;1;1;0}*{1200;200;300;120;220;50;600;100;800}

这3个数组相乘的结果也是一个数组，其每个元素为这3个数组对应元素相乘的结果，即：

{1200;0;300;0;0;0;600;0;0}

该数组作为SUM函数的参数得到最终的结果为2100（=1200+300+600），即张三和李四销售的手机数量。

详细的运算过程如下图6所示。

图6

可以看出：

数组公式中将乘法（*）用于逻辑与，即遵守与AND操作相同的规则；将加法（+）用于逻辑或，遵守与OR操作相同的规则。MOD运算模拟异或XOR操作。

在进行算术运算时，Excel会将TRUE和FALSE转换成数值1和0。

数组公式示例

下面列举两个简单的示例，更多的示例参见后面链接的内容。

示例1：计算及格的学生成绩的平均值

在图7所示的工作表中，求及格的学生成绩的平均值，使用数组公式：

=AVERAGE(IF(B2:B9>60,B2:B9,FALSE))

图7

这个数组公式使用IF函数来测试多个单元格。比较单元格区域B2:B9中的数值是否大于60，返回一个包含布尔值的数组{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}，然后IF函数根据该数组中的值，如果为TRUE则返回B2:B9中的值，为FALSE则返回FALSE。扩展后的公式如下：

=AVERAGE(IF({TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},{89;92;78;56;88;58;95;55},{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}))

注意，在原公式最后的FALSE被扩展成一个与前面数组相匹配的适合大小的数组。

在IF函数测试完成后，得到下面的中间结果：

=AVERAGE({89;92;78;FALSE;88;FALSE;95;FALSE})

AVERAGE函数忽略布尔值（TRUE或FALSE），仅对数值求平均值。

示例2：计算排名前3的学生成绩之和

仍以图7所示的工作表为例，要求前3名的学生成绩之和。

可以使用数组公式：

=SUM(LARGE(B2:B9,ROW(1:3)))

其中，ROW(1:3)返回数组{1;2;3}。LARGE函数依次取单元格区域B2:B9中的最大值、第2大及第3大值，返回数组{95;92;89}，然后将其作为SUM函数的参数求和。

示例3：计算数值单元格中各数字之和

在单元格A1中的数值为12345，要通过公式得出1+2+3+4+5的值。

可以使用数组公式：

=SUM(1*MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1))

LEN函数计算单元格A1中字符的长度，因此上面的公式变为：

=SUM(1*MID(A1,ROW(INDIRECT("1:6")),1))

INDIRECT函数返回对第1至6行的引用：

=SUM(1*MID(A1,ROW(1:6),1))

然后扩展成为：

=SUM(1*MID(A1,{1;2;3;4;5;6},1))

MID函数依次取单元格A1中的字符，每次1个：

=SUM(1*{“1”;”2”;”3”;”4”;”5”;”6”})

将1与数字文本相乘将其转换为数字：

=SUM({1;2;3;4;5;6})

最后，SUM函数对数字数组求和。

更多的数组公式示例

上面只是列举了一些简单的数组公式示例，以加深读者对数组公式的理解。更多的数组公式示例可以参见Excel公式练习系列：

Excel公式练习1：将列数据转换为行数据

Excel公式练习2：获取非空单元格

Excel公式练习3：求连续数据之和的最大值

Excel公式练习5：找出符合多列条件的值

Excel公式练习6：判断单元格区域中是否有重复值

Excel公式练习7：统计单元格区域中不重复值的数量

Excel公式练习8：获取单元格区域中的不重复值

Excel公式练习10：判断两个单元格区域是否有重复值

Excel公式练习11：颠倒单元格区域中的数据

Excel公式练习12：获取单元格中字符串的最后一个单词

Excel公式练习13：统计单元格中无效数据的数量

Excel公式练习14：使用数组公式创建日历

Excel公式练习16：求包含文本内容的单元格中的数字之和

Excel公式练习17：提取字符串中的数字

Excel公式练习18：获取每行中第一个非空单元格

在问与答系列中也有两个代表性的数组公式示例：

问与答7：如何使用公式实现自动排序？

问与答13：如何将单元格中的数字和文本分开？

↑提示：单击标题链接即可进入相应的文章页面。

本文为原创文章，转载请注明出处。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎关注[完美Excel]微信公众号：

方法1—在微信“添加朋友”或者通讯录中搜索“完美Excel”或者“excelperfect”后点击关注。

方法2—扫一扫下面的二维码

别忘了，留言、点赞、点击每篇文章底部的广告，也是给我的支持和鼓励哟！