精通Excel数组公式003：数组公式是个啥

本文详细讲解数组公式的相关概念，为数组公式进一步学习打下基础。

基本概念

数组是两个或多个项目的集合。在Excel中，有三种类型的数组：

1. 引用数组，包含一个以上的单元格引用，例如单元格区域、工作表引用和定义的名称。

2. 由公式元素创建的数组，也称作结果数组，是通过数组操作创建的一组项目。

3. 数组常量，一组硬编码到公式中的值。

数组公式是一种包含对一组项目而非单个项目进行运算（数学、比较、连接或函数参数）的公式，并且运算提供的结果是一组项目而不是单个项目。这种运算被称为数组运算，以区别于聚合运算。结果数组可用于大公式中的公式元素，也可以是公式传递到单元格区域中的最终结果。数组公式的结果可以是单个项目，也可以是一组项目。

理解数组公式的概念

在上述数组公式的概念中，核心是“执行对一组项目而非单个项目的运算”。我们以示例来讲解这个概念。

如下图1所示，我们想要计算这4天收盘价与开盘价之间的变化的最大值。

图1

图1所示的工作表中的计算过程如下：

1. 在单元格E3中使用公式=D3-C3计算该天的变化值，这只是一个简单的单个值之间的减法运算。

2. 同样，使用减法运算计算出其它3天的变化值。

3. 在单元格E8中，使用公式=MAX(E3:E6)求出这4天变化值中的最大值。

很显然，这些公式都不是数组公式，因为它们只是在单个项目上执行运算，得到的结果也是单个项目。

辅助列

在上图1所示的工作表中，要获取这4天股价的最大变化值，我们先在列E中计算每天的变化值，然后再将这些值传递给MAX函数求得最大值。我们称列E为辅助列，因为它帮助我们获取需要计算股价变化最大值的值。由MAX函数执行的运算称为聚合运算，因为它遍历所有值并计算出结果，这不是数组运算，虽然它处理多个项目。

如果你的需求要求显示每天的股价变化量，那么构造辅助列，然后使用MAX函数聚合运算是一种很好的解决方案。然而，如果你不想看到详细的计算过程，只是想计算出最大变化量，或者有成千上万的数据，创建辅助列占用工作表空间且耗费较大，那么可以使用单个单元格数组公式来解决。

单个单元格的数组公式

下图2中，使用公式来计算4天股价变化的最大值。

图2

图2中使用了一个公式进行计算，其过程如下：

1. 单元格D8中包含从单元格区域D3:D6中的值减去单元格区域C3:C6中的值的公式，执行着两组数值的减法运算：D3:D6-C3:C6。

2. 减法运算的结果是一组数值。然后，MAX函数从这组数值中取最大值作为结果。

这是一个数组公式，因为直接对两组项目执行运算，并且生成的结果是一组数字。

技巧：使用评估公式元素来显示结果数组

在数组公式处于编辑状态时，我们可以评估公式元素的计算结果。例如，在图2所示的示例中，我们可以看看该公式中的D3:D6-C3:C6是否生成与图1辅助列一致的结果。

1.选择公式中的元素，如下图3所示。

图3

2.按F9键评估公式中所选的部分公式元素，如下图4所示，数组元素与辅助列的值相同。

图4

注意，为了避免无意中将硬编码放置到公式，在按F9评估后，按Ctrl+Z来撤销。

这是在创建数组公式时经常使用的一个技巧，可以查看数组公式元素的结果，从而确保公式正确，特别是创建包含多个公式元素的大型数组公式时。

数组公式的正确输入方式

在上面的示例中，如果你像输入普通公式那样，在输入完后，按回车键，则会得到一个错误值#VALUE!，如下图5所示。

图5

这表明，没有正确地输入数组公式。在Excel中，除少数几个函数外（例如SUMPRODUCT、LOOKUP、AGGREGATE、INDEX），大部分函数都不能自动处理数组操作。因此，必须告诉Excel该函数的参数包含数组运算，希望Excel执行数组运算。通过使用特别的组合键Ctrl+Shift+回车键来告诉Excel执行数组运算。

也就是说，在输入完公式中的字符后，按Ctrl+Shift+回车键，这才真正完成了数组公式的输入。此时，Excel会自动在公式两端加上花括号，如上图2所示。

隐式交叉陷阱

如下图6所示，只有单元格B8中输入的数组公式才是正确的结果，其它的公式有的显示错误值#VALUE!，有的显示数字。错误值还好说，如果我们输入公式后获得的是数字，可能我们以为得到了结果，但这个结果却是错误的。

图6

观察一下，除数组公式外，得到数值的公式都是在数字数据的旁边列。在数字的旁边输入的公式，Excel会使用相同行的对应列进行计算处理，这被称作隐式交叉。如果我们不仔细，很可能认为这样的公式计算结果是对的，因此在使用数组公式时，在输入完公式后，我们要留意公式栏中，是否在公式两边已添加了花括号。这也提醒我们，数组公式的最终输入完成一定是以按下Ctrl+Shift+回车键为标准的。

数组公式的优缺点

优点

1. 数组公式有时是除了VBA外唯一的解决问题的方式。

2. 数组公式能够节省工作表空间（不需要一个或多个辅助列）。

3. 在多单元格中的数组公式难以删除。

4. 给定了所需要的结果和环境条件，数组公式是最好的选择。

缺点

1. 数组公式可能减慢公式计算时间，特别是对于包含具有大量单元格引用和计算的许多公式的工作表。

2. 大多数的数组公式都需要使用Ctrl+Shift+回车键，而这恰恰是最容易忘记的。

3. 使用的数组公式对于用户来说可能比较难以理解。

4. 数组公式的学习资料较少。

5. 创建数组公式有时相当复杂。

确定是否有比数组公式更有效的方法

1. 是否有实现同样目的的内置函数？

2. 是否可以使用辅助列？

3. 是否可以使用辅助单元格？

4. 是否可以使用筛选、高级筛选或数据透视表？

5. 是否有不同的公式选项？对于包含具有大量单元格引用和计算的许多公式的工作表来说，可能需要花时间设定不同的公式选项，以查看是否明显更快。

6. 是否有替代的数组公式或者非数组公式是更有效的？

7. 是否可以使用VBA代替数组公式？