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
欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。
2020.8.23 社群动态
#VBA 如何到点后让Excel自动提醒我要做的工作？
#Excel公式练习 使用公式统计不同学年满足分数线的学分数量
#Excel公式 关于Excel中的易失性函数
#话题 Excel中表功能的一些局限性
联系客服
