数组公式是可以对数组中的一个或多个项目执行多个计算的公式。 可以将数组视为值的行或列，或值的行和列的组合。 数组公式可以返回多个结果或单个结果。
从 Microsoft 365 的 2018 年 9 月更新开始，任何可返回多个结果的公式都将自动将它们向下溢出或跨到相邻单元格中。 此行为更改还伴随着几个新的 动态数组函数。 动态数组公式（无论是使用现有函数还是动态数组函数）只需输入到单个单元格中，然后按 Enter 进行确认。 在早期，旧版数组公式需要首先选择整个输出区域，然后按 Ctrl+Shift+Enter 确认公式。 它们通常称为 CSE 公式。
可以使用数组公式执行复杂任务，例如：
- 快速创建示例数据集。
- 计算单元格区域中包含的字符数。
- 仅对满足特定条件的数字（如区域中的最小值或介于上限和下限之间的数字）求和。
- 对某个值区域中的每 N 个数值求和。
以下示例演示如何创建多单元格和单单元格数组公式。 在可能的情况下，我们包含了一些动态数组函数的示例，以及作为动态和旧数组输入的现有数组公式。
下载我们的示例
多单元和单单元阵列
本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。 第一组操作是使用多单元格公式计算一组小计。 第二组操作是使用单个单元格公式计算总计。
多单元格数组公式
在这里，我们通过在单元格 H10 中输入 =F10：F19*G10：G19 来计算每个销售人员的轿跑车和轿车总销售额。
按 Enter 时，你将看到结果向下溢出到单元格 H10：H19。 请注意，当您选择溢出范围内的任何单元格时，溢出范围将用边框突出显示。 你可能还会注意到，单元格 H10：H19 中的公式呈灰显。它们仅供参考，因此如果要调整公式，则需要选择主公式所在的单元格 H10。单个单元格数组公式
在示例工作簿的单元格 H20 中，键入或复制并粘贴 =SUM (F10：F19*G10：G19) ，然后按 Enter。
在这种情况下，Excel 将单元格区域 F10 到 G19) (数组中的值相乘，然后使用 SUM 函数将总计相加。 结果等于 $1,590,000 的总销售额。
本示例演示了此类公式的强大功能。 例如，假定您有 1,000 行数据。 您可以通过在单个单元格中创建数组公式来对部分或全部数据求和，而不是将公式向下拖动以包括所有 1,000 行来求和。 另请注意，单元格 H20 中的单个单元格公式完全独立于单元格 H10 到 H19) 中的公式 (多单元格公式。 这是使用数组公式的另一个优点 - 灵活性。 可以更改 H 列中的其他公式，而不会影响 H20 中的公式。 像这样独立的总计也是一种很好的做法，因为它有助于验证结果的准确性。动态数组公式还具有以下优点：
- 一致性 如果从 H10 向下单击任何单元格，则会看到相同的公式。 这种一致性有助于确保更高的准确性。
- 安全 不能覆盖多单元格数组公式的组件。 例如，单击单元格 H11，然后按 Delete。 Excel 不会更改数组的输出。 若要更改它，需要选择数组中的左上角单元格或单元格 H10。
- 更小的文件大小 通常可以使用单个数组公式而不是多个中间公式。 例如，汽车销售示例使用一个数组公式来计算 E 列中的结果。如果您使用了标准公式，如 =F10*G10、F11*G11、F12*G12 等，您将使用 11 个不同的公式来计算相同的结果。 这没什么大不了的，但如果总共有数千行怎么办？ 然后它可以产生很大的不同。
- 效率 数组函数是构建复杂公式的有效方法。 数组公式 =SUM (F10：F19*G10：G19) 与此相同：=SUM (F10*G10，F11*G11，F12*G12，F13*G13，F14*G14，F15*G15，F16*G16，F17*G17，F18*G18，F19*G19) 。
- 溢出动态数组公式将自动溢出到输出区域。 如果源数据位于 Excel 表中，则添加或删除数据时，动态数组公式将自动调整大小。
- #溢出! 错误 动态数组引入了 #SPILL！ 错误，该错误表示预期溢出范围由于某种原因被阻止。 解决堵塞问题后，公式将自动溢出。
创建一维和二维数组常量
数组常量是数组公式的组成部分。 可以通过输入一系列项然后手动用大括号 ({ }) 将该系列项括起来创建数组常量，类似于：
={1,2,3,4,5} or ={“January”，“February”，“March”}
如果使用逗号分隔各个项，将创建水平数组（一行）。 如果使用分号分隔项，将创建垂直数组（一列）。 若要创建二维数组，请用逗号分隔每一行中的项目，并用分号分隔每一行。
下面将为你提供创建水平、垂直和二维常量的练习。 我们将展示使用 SEQUENCE 函数 自动生成数组常量以及手动输入的数组常量的示例。
-
创建水平常量
使用上述示例中所用工作簿，或创建新的工作簿。 选择任何空单元格并输入 =SEQUENCE (1,5) 。 SEQUENCE 函数生成一个 1 行乘 5 列的数组，与 ={1,2,3,4,5} 相同。 将显示以下结果：
-
创建垂直常量
选择任何下方有空格的空白单元格，然后输入 =SEQUENCE (5) ，或 ={1;2;3;4;5}。 将显示以下结果：
-
创建二维常量
选择右侧和下方有空间的任何空白单元格，然后输入 =SEQUENCE (3,4) 。 将得到以下结果：
还可以输入：or ={1,2,3,4;5,6,7,8;9,10,11,12}，但需要注意分号与逗号的放置位置。
如您所见，与手动输入数组常量值相比，SEQUENCE 选项具有显着优势。 首先，它可以节省您的时间，但也可以帮助减少手动输入的错误。 它也更易于阅读，尤其是因为分号可能难以与逗号分隔符区分。
数组常量语法
下面是一个使用数组常量作为更大公式的一部分的示例。 在示例工作簿中，转到公式工作表 中的“常量 ”，或创建新工作表。
在单元格 D9 中，我们输入了 =SEQUENCE (1,5,3,1) ，但也可以在单元格 A9：H9 中输入 3、4、5、6 和 7。 这个特定的数字选择并没有什么特别之处，我们只是选择了 1-5 以外的东西来区分。
在单元格 E11 中，输入 =SUM (D9：H9*SEQUENCE (1,5) ) ，或 =SUM (D9：H9*{1,2,3,4,5}) 。 公式返回 85。
SEQUENCE 函数生成数组常量 {1,2,3,4,5}的等效项。 由于 Excel 首先对括在括号中的表达式执行操作，因此接下来要发挥作用的两个元素是 D9：H9 中的单元格值和乘法运算符 (*) 。 此时，公式将存储数组中的值与常量中对应的值相乘。 它等价于：
=SUM (D9*1，E9*2，F9*3，G9*4，H9*5) 或 =SUM (3*1,4*2,5*3,6*4,7*5)
最后，SUM 函数将值相加，返回 85。
要避免使用存储的数组并将操作完全保留在内存中，可以将其替换为另一个数组常量：
=SUM (SEQUENCE (1,5,3,1) *SEQUENCE (1,5) ) ，或 =SUM ({3,4,5,6,7}*{1,2,3,4,5})
可在数组常量中使用的元素
- 数组常量可以包含数字、文本、逻辑值 (（如 TRUE 和 FALSE) ）和错误值（如 #N/A）。 可以使用整数、十进制和科学格式的数字。 如果包含文本，则需要使用引号 (“text”) 将其括起来。
- 数组常量不能包含其他数组、公式或函数。 换言之，它们只能包含以逗号或分号分隔的文本或数字。 当您输入如下所示的公式时，Excel 将显示警告消息：{1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)}。 另外，数值不能包含百分号、货币符号、逗号或圆括号。
命名数组常量
使用数组常量的最佳方法之一是为它们命名。 命名的数组常量更易于使用，并且对于其他人来说，它们可以降低数组公式的复杂性。 若要命名数组常量并在公式中使用它们，请执行以下操作：
转到公式>、定义的名称、>定义名称。 在 “名称 ”框中，键入 Quarter1。 在“引用位置”框中，输入下面的常量（记住要手动键入大括号）：
={"一月","二月","三月"}
对话框现在应如下所示：
单击“ 确定”，然后选择包含三个空白单元格的任何行，然后输入 =Quarter1。
将显示以下结果：
如果希望结果垂直溢出而不是水平溢出，可以使用 =TRANSPOSE (Quarter1) 。
如果要显示 12 个月的列表（如编制财务报表时可能使用），可以使用 SEQUENCE 函数以当年为基础计算一个月。 这个函数的巧妙之处在于，即使只显示月份，它背后有一个有效的日期，您可以在其他计算中使用。 你可以在示例工作簿的 命名数组常量 和 快速示例数据集 工作表中找到这些示例。
=TEXT (DATE (YEAR (TODAY () ) ，SEQUENCE (1,12) ,1) ，“mmm”)
这将使用 DATE 函数 创建基于当前年份的日期，SEQUENCE 创建 1 月到 12 月的数组常量，然后 TEXT 函数 将显示格式转换为 1 月、2 月、3 月等 (的“mmm”) 。 如果要显示完整的月份名称（如一月），请使用“mmmm”。
使用命名常量作为数组公式时，请记得输入等号，如 =Quarter1，而不仅仅是 Quarter1。 如果没有输入等号，Excel 将数组解释为文本字符串，并且公式不会按预期工作。 最后，请记住，您可以使用函数、文本和数字的组合。 这完全取决于您想要获得多大的创意。
数组常量工作方式
下面的示例演示可以将数组常量用于数组公式的几种方式。 其中一些示例使用 TRANSPOSE 函数 将行转换为列，反之亦然。
-
为数组中的每个项目添加多个
输入 =SEQUENCE (1,12) *2 或 ={1,2,3,4;5,6,7,8;9,10,11,12}*2
也可以用 (/) 除，用 (+) 加，用 (-) 减。 -
对数组中的各项求平方
输入 =SEQUENCE (1,12) ^2，或 ={1,2,3,4;5,6,7,8;9,10,11,12}^2 -
查找数组中项的平方根
输入 =SQRT (SEQUENCE (1,12) ^2) ，或 =SQRT ({1,2,3,4;5,6,7,8;9,10,11,12}^2) -
转置一维行
输入 =TRANSPOSE (SEQUENCE (1,5) ) ，或 =TRANSPOSE ({1,2,3,4,5})
即使输入的是水平数组常量，TRANSPOSE 函数也会将该数组常量转换为列。 -
转置一维列
输入 =TRANSPOSE (SEQUENCE (5,1) ) ，或 =TRANSPOSE ({1;2;3;4;5})
即使输入的是垂直数组常量，TRANSPOSE 函数也会将该常量转换为行。 -
转置二维常量
输入 =TRANSPOSE (SEQUENCE (3,4) ) ，或 =TRANSPOSE ({1,2,3,4;5,6,7,8;9,10,11,12})
TRANSPOSE 函数将各行转换为一系列的列。
使用基本数组公式
本节提供基本数组公式的示例。
从现有值创建数组
下面的示例说明如何使用数组公式从现有数组创建新数组。
输入 =SEQUENCE (3,6,10,10) ，或 ={10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170,180}
请务必在键入 10 之前键入 { (左大括号) ，在键入 180 之后键入 } (右大括号) ，因为你正在创建一个数字数组。
接下来，在空白单元格中输入 =D9# 或 =D9：I11 。 此时会显示一个 3 x 6 单元格数组，其值与 D9：D11 中所见的值相同。 # 符号称为 溢出范围运算符，它是 Excel 引用整个数组范围的方式，而不必键出它。
从现有值创建数组常量
您可以获取溢出的数组公式的结果，并将其转换为其组成部分。 选择单元格 D9，然后按 F2 切换到编辑模式。 接下来，按 F9 将单元格引用转换为值，然后 Excel 会将这些值转换为数组常量。 按 Enter 时，公式 =D9# 现在应为 ={10,20,30;40,50,60;70,80,90}.在单元格区域中对字符计数
下面的示例演示如何计算单元格区域中的字符个数。 这包括空格。
=SUM (LEN (C9：C13) )
在这种情况下， LEN 函数 返回区域中每个单元格中每个文本字符串的长度。 然后，SUM 函数将这些值相加，并显示结果 (66) 。 如果要获取平均字符数，可以使用：
=AVERAGE (LEN (C9：C13) )区域 C9：C13 中最长单元格的内容
=INDEX (C9：C13，MATCH (MAX (LEN (C9：C13) ) ，LEN (C9：C13) ,0) ,1)
本公式仅在数据区域包含单列单元格时适用。
让我们从内部元素开始，由内而外深入了解此公式。 LEN 函数返回单元格区域 D2：D6 中每个项目的长度。 MAX 函数计算这些项目中的最大值，该值对应于单元格 D3 中的最长文本字符串。
下面的计算稍微有点复杂。 MATCH 函数计算 (包含最长文本字符串的单元格的相对位置) 偏移量。 为此，需要三个参数：分别是查阅值、查阅数组和匹配类型。 MATCH 函数在查阅数组中搜索指定的查阅值。 在这种情况下，查阅值为最长的文本字符串：
最大 (LEN (C9：C13)
并且该字符串位于此数组中：
LEN (C9：C13)
在这种情况下，匹配类型参数为 0。 匹配类型可以是 1、0 或 -1 值。- 1 - 返回小于或等于查找值的最大值
- 0 - 返回与查阅值完全相等的第一个值
- -1 - 返回大于或等于指定查找值的最小值
- 如果未指定匹配类型，Excel 会采用值 1。
最后， INDEX 函数 采用以下参数：数组以及该数组内的行号和列号。 单元格区域 C9：C13 提供数组，MATCH 函数提供单元格地址，最后一个参数 1 () 指定值来自数组中的第一列。
如果要获取最小文本字符串的内容，请将上面示例中的 MAX 替换为 MIN。查找区域中的 n 个最小值
此示例演示如何在单元格区域中查找三个最小值，其中单元格 B9：B18 中的示例数据数组已使用以下代码创建： =INT (RANDARRAY (10,1) *100) 。 请注意，RANDARRAY 是一个可变函数，因此每次 Excel 计算时，你都会得到一组新的随机数。
Enter =SMALL (B9#，SEQUENCE (D9) ， =SMALL (B9：B18，{1;2;3})
此公式使用数组常量计算 SMALL 函数 三次，并返回单元格 B9：B18（其中 3 是单元格 D9 中的变量值）中包含的数组中最小的 3 个成员。 若要查找更多值，可以增加 SEQUENCE 函数中的值，或向常量添加更多参数。 还可以对此公式使用其他函数，例如 SUM 或 AVERAGE。 例如：
=SUM (SMALL (B9#，SEQUENCE (D9) )
=AVERAGE (SMALL (B9#，SEQUENCE (D9) )查找区域中的 n 个最大值
若要查找区域中的最大值，可以将 SMALL 函数替换为 LARGE 函数。 此外，下面的示例使用 ROW 和 INDIRECT 函数。
输入 =LARGE (B9#，ROW (INDIRECT (“1：3”) ) ) 或 =LARGE (B9：B18，ROW (INDIRECT (“1：3”) ) )
现在，了解一点 ROW 和 INDIRECT 函数可能会有所帮助。 可以使用 ROW 函数创建连续的整数数组。 例如，选择一个空的并输入：
=ROW(1:10)
此公式创建由 10 个连续整数组成的一列。 为了查看可能的问题，请在包含数组公式的区域上面插入一行（即第 1 行上）。 Excel 调整行引用，公式现在生成 2 到 11 之间的整数。 要修正该问题，可以向该公式添加 INDIRECT 函数：
=ROW(INDIRECT("1:10"))
INDIRECT 函数使用文本字符串作为其参数 (这就是范围 1：10 由引号) 括起来的原因。 当插入行或移动数组公式时，Excel 不会调整文本值。 因此，此 ROW 函数总是生成所需的整数数组。 您也可以同样轻松地使用 SEQUENCE：
=SEQUENCE (10)
让我们检查之前使用的公式 — =LARGE (B9#，ROW (INDIRECT (“1：3”) ) ) — 从内括号向外开始： INDIRECT 函数返回一组文本值，在本例中为值 1 到 3。 ROW 函数反过来生成一个三单元格列数组。 LARGE 函数使用单元格区域 B9：B18 中的值，并会计算三次，对于 ROW 函数返回的每个引用，计算一次。 如果要查找更多值，请向 INDIRECT 函数添加更大的单元格区域。 最后，与 SMALL 示例一样，可以将此公式与其他函数（如 SUM 和 AVERAGE）一起使用。
处理错误
-
对包含错误值的区域求和
尝试对包含错误值（如 #VALUE！）的区域求和时，Excel 中的 SUM 函数不起作用！ 或 #N/A。 此示例演示如何对名为 Data 的包含错误的区域中的值求和：
-
=SUM(IF(ISERROR(数据),"",数据))
该公式创建一个新数组，包含除错误值以外的原始值。 从内层函数开始向外运算，ISERROR 函数在单元格区域 (数据) 中搜索错误。 IF 函数在指定的条件计算结果为 TRUE 时返回指定值，在计算结果为 FALSE 时返回另一个值。 在此处，它为所有错误值返回空字符串 ("")，因为它们的计算结果为 TRUE，并且返回该区域（数据）中的其他值（因为这些值计算结果为 FALSE，表示它们不包含错误值）。 接着 SUM 函数计算筛选出的数组的总和。 -
计算区域中错误值个数
此示例与前面的公式类似，但它返回名为 Data 的区域中的错误值数，而不是筛选掉它们：
=SUM(IF(ISERROR(数据),1,0))
该公式创建一个数组，它为包含错误的单元格包含值 1，为不包含错误的单元格包含值 0。 可以简化该公式，并达到相同的结果，方法是移除 IF 函数的第三个参数，如下所示：
=SUM (IF (ISERROR (Data) ,1) )
如果未指定该参数，IF 函数在单元格不包含错误值时返回 FALSE。 可以进一步简化该公式：
=SUM(IF(ISERROR(数据)*1))
此公式版本可以执行计算是因为 TRUE*1=1 并且 FALSE*1=0。
条件求和
可能需要根据条件对值求和。
例如，以下数组公式只对名为 Sales 的区域（代表以上示例中的单元格 E9：E24）中的正整数求和：
=SUM (IF (Sales>0，Sales) )
IF 函数创建由正值和假值组成的数组。 SUM 函数实际上将忽略 false 值，因为 0+0=0。 在此公式中使用的单元格区域可以由任意数量的行和列组成。
还可以对满足多个条件的值求和。 例如，以下数组公式计算大于 0 且 小于 2500 的值：
=SUM ( (Sales>0) * (Sales<2500) * (Sales) )
请注意，如果区域中包含一个或多个非数字单元格，此公式将返回错误。
还可以创建使用 OR 条件的数组公式。 例如，可以对大于 0 或 小于 2500 的值求和：
=SUM (IF ( (Sales>0) + (Sales<2500) ，Sales) )
不能在数组公式中直接使用 AND 和 OR 函数，因为这些函数返回单一结果，TRUE 或 FALSE，而数组函数需要结果数组。 可以通过使用上一公式中显示的逻辑来解决这一问题。 换言之，对满足 OR 或 AND 条件的值执行数学运算（如加法或乘法）。
本示例演示当您需要对区域中的值求平均值时，如何从该区域中移除零。 下面的公式使用名为“销售量”的数据区域：
=AVERAGE (IF (Sales<>0，Sales) )
IF 函数创建不等于 0 的值数组，然后将这些值传递给 AVERAGE 函数。
计算两个单元格区域中的不同值个数
此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。 如果这两个区域中的内容完全相同，此公式将返回 0。 若要使用此公式，单元格区域必须大小相同且尺寸相同。 例如，如果 MyData 是 3 行乘 5 列的区域，YourData 也必须是 3 行乘 5 列：
=SUM (IF (MyData=YourData，0,1) )
此公式创建与正比较的区域大小相同的新数组。 IF 函数使用值 0 和值 1 填充数组（0 表示单元格不匹配，1 表示单元格匹配）。 然后 SUM 函数返回该数组中的值的和。
可以如下所示简化该公式：
=SUM (1* (MyData<>YourData) )
与计算区域中的错误值的公式相似，此公式版本可以执行计算是因为 TRUE*1=1 并且 FALSE*1=0。
此数组公式返回名为“数据”的单列区域中的最大值所在的行号：
=MIN(IF(数据=MAX(数据),ROW(数据),""))
IF 函数创建与名为“数据”的区域对应的新数组。 如果对应的单元格包含区域中的最大值，则此数组包含该行号。 否则，此数组包含空字符串 ("")。 MIN 函数使用此新数组作为它的第二个参数并且返回与“数据”区域中最大值的行号相对应的最小值。 如果名为“数据”的区域包含完全相同的最大值，则该公式返回第一个值的行号。
如果要返回最大值的实际单元格地址，请使用下面的公式：
=ADDRESS(MIN(IF(数据=MAX(数据),ROW(数据),"")),COLUMN(数据))
你可以在 “数据集之间的差异” 工作表上的示例工作簿中找到类似的示例。
致谢
本文部分内容基于 Colin Wilcox 撰写的一系列 Excel 高级用户列，改编自前 Excel MVP John Walkenbach 所著《Excel 2002 公式》的第 14 章和第 15 章。
需要更多帮助吗？
你随时可以在 Excel 技术社区 中咨询专家，或在 社区中获取支持。