精通Excel数组公式008：数组常量

学习Excel技术，关注微信公众号：

excelperfect

下面是你可能在公式中使用或者遇到的3类数组常量。

1. 列数组常量（垂直数组常量）

如下图1和图2所示，如果使用公式引用一列中的项目，当按F9评估其值时，会看到：在花括号内放置了一组项目，文本被添加上了引号，分号意味着跨行，且项目列使用分号。

图1：单元格区域：使用行填充列。

图2：数组常量：使用行填充列，分号=行。

2. 行数组常量（水平数组常量）

如下图3和图4所示，如果使用公式引用一行中的项目，当按F9评估其值时，会看到：在花括号内放置了一组项目，文本被添加上了引号，数字仍保留原形式，逗号意味着跨列，且项目行使用逗号。

图3：单元格区域：使用列填充行。

图4：数组常量：使用列填充行，逗号=列。

3.表数组常量（双向数组常量）

如下图5和图6所示，如果使用公式引用行列组成的表，当按F9评估其值时，会看到：在花括号内放置了一组项目，文本被添加上了引号，数字仍保留原形式，分号意味着跨行，逗号意味着跨列。

图5：单元格区域：使用列和行填充表

图6：数组常量：使用列和行填充表。

数组语法规则

从上述讲解中，我们可以发现有下列数组语法规则：

1. 数组包含在花括号里。

2. 分号意味着跨行

3. 逗号意味着跨列

4. 数组中的文本放置在双引号中

5. 数字、逻辑值和错误值不需要双引号

6. 数组的3种类型是：列（垂直）、行（水平）和表（双向）

特别地：如果给公式提供的数据会变化，那么将其放到单元格中并通过使用单元格引用来获取数据。如果数据不会变化，那么将其硬编码到公式中。

由于对于一些函数来说，如果使用数组常量作为数组运算中的数组，那么数组公式不需要按Ctrl+Shift+Enter键。这意味着在数组运算中包含的数组项不会变化，那么应该尽量使用数组常量，避免必须按Ctrl+Shift+Enter键。

示例：使用SUM和SMALL函数对3个最小的值相加（不包括重复值）

如下图7所示，要求高尔夫球手击球杆数最小的3个值之和，并且如果第3个值有重复值的话，只计1个值。可以使用SMALL函数来解决这个问题，只需指定其参数k的值即可。

图7

图7中的公式显然很笨拙，如果要求10个最小值，是不是要用10个SAMLL函数？我们可以使用数组常量来简化，如下图8所示，指定参数k值为包含3个数的数组：{1,2,3}。

图8

注意到，图8所示的公式中，Excel并没有在公式两边添加花括号，这表明，在SMALL函数中使用数组常量作为参数k的值，不需要按Ctrl+Shift+Enter组合键。

如果你使用单元格引用作为SMALL函数的参数k的值，则需要按Ctrl+Shift+Enter组合键，如下图9所示。

图9

如果要避免使用Ctrl+Shift+Enter组合键，则可以使用SUMPRODUCT函数代替SUM函数：

=SUMPRODUCT(SMALL(B3:B8,D7:D9))

示例：使用SUMIF和SMALL函数对3个最小的值相加（包括重复值）

在有些情形下，在求和时可能要包含重复值，如下图10所示。此时，计算的结果为2+1+2+2=7。

图10

示例：一个动态求前n个值的和的公式

下图11展示了求前3个值的和的两个公式。公式1求得的和不包括重复值，公式2包括重复值。

图11

注意到，与上文所给出的公式不同之处在于，公式中没有硬编码。如果想改变求和的数量，只需修改单元格D3和D6中的数值。

下面重点看看公式1：

=SUMPRODUCT(LARGE(B2:B8,ROW(INDIRECT("1:"&D3))))

公式中，使用INDIRECT函数和ROW函数创建了一个按顺序排列的可变长度的数字数组，如果单元格D3中的值为3，则数组为{1;2;3}，如果是2，则为{1;2}。

示例：从单个单元格里的四个系列折扣中计算净成本

如下图12所示，四个系列折扣都在一个单元格中，需要使用公式计算净成本。

图12

可以使用MID函数来提取这四个系列折扣数字，如下图13所示。

图13

选择MID函数后按F9评估值，其结果如下图14所示。

图14

这些值虽然是文本，但应用到数学运算中后，会自动转换为数字。这样，得到的求净成本的公式如下图15所示。

图15

可以看出，在公式中使用了数组常量，不需要按Ctrl+Shift+Enter键。

示例：在VLOOKUP函数中的查找技巧

使用数组常量来节省工作表空间

在使用VLOOKUP函数时，如果你不想通过查找表查找且数据不会变化，可以将查找表硬编码到公式中，如下图16所示。

图16

使用名称

除了按上述方法在公式中列出查找表的所有数据外，还可以将数组常量定义为名称并在公式中使用。如下图17所示，定义名称包含查找表数据。

图17

这样，在公式中直接引用定义的名称，如下图18所示。

图18

对参数col_index_num指定数组常量

在VLOOKUP函数中的参数lookup_value不能处理数组，然而可以对参数col_index_num指定数组常量。图19是一个查找表，在第一列是经排序的产品名称，第2至8列是其组成成本，现在需要同时查找第2、4、5、7和8列，获取成本并将它们相加。你可以添加一个辅助列，放置上述各列相加后的值，然后使用VLOOKUP函数查找相应的值。

其实，你可以使用代表这些列的数字组成的数组作为VLOOKUP函数的参数col_index_num的值，如下图19所示，以获取相应的5个值{1.35,2.15,3,2,4}。

图19

根据上文的讲解，在公式中使用了数组常量，不需要按Ctrl+Shift+Enter键，直接按回车键后的结果如下图20所示。

图20

很显然，结果是错误的。这是因为对于VLOOKUP函数的参数col_index_num来说，无论指定的数组常量还是单元格区域，都需要按Ctrl+Shift+Enter键，如下图21所示。

图21

因为是求和，所以可以将VLOOKUP函数放置到SUMPRODUCT函数中，这样可以避免按Ctrl+Shift+Enter键，如下图22所示。

图22

从上述示例可以看出，有些函数参数包含数组常量而无须使用Ctrl+Shift+Enter键（例如SAMLL函数、LARGE函数、OR函数、LEN函数），而有些则需要使用Ctrl+Shift+Enter键（例如VLOOKUP函数）。

在数学和比较数组运算时使用数组常量的情形

下面的例子展示了是否需要Ctrl+Shift+Enter键的一般规则。

图23：判断职务是否是“V.P.”、“President”或者“Admin”。比较数组运算涉及到数组常量，不需要按Ctrl+Shift+Enter键。

图24：通过一组折扣率相乘计算零售商品的净等价成本。在PRODUCT函数的参数number1中的数学数组运算涉及到数组常量，不需要按Ctrl+Shift+Enter键。

图25：两个数组相乘，然后求和。在SUM函数的参数number1中的这个数学数组运算涉及到两个数组常量，不需要按Ctrl+Shift+Enter键。

图26：单元格区域和数组常量相乘，然后求和。这个数学数组运算涉及到单元格区域，需要按Ctrl+Shift+Enter键。

《Ctrl+Shift+Enter：MasteringExcel Array Formulas》学习笔记

完美Excel

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