数组公式指南和示例

数组公式是可对数组中的一个或多个项目执行多个计算的公式。你可以将数组视为值的一行或一列，或者视为值的行和列的组合。数组公式可以返回多个结果，也可以返回单个结果。

从Office365的2018年9月更新开始，任何可返回多个结果的公式将自动溢出它们，或跨越相邻单元格。此行为更改还附带几个新的动态数组函数。动态数组公式，无论是使用现有函数还是动态数组函数，只需输入单个单元格，然后按enter确认。以前，旧数组公式要求首先选择整个输出区域，然后用Ctrl+Shift+Enter确认公式。它们通常称为CSE公式。

可以使用数组公式执行复杂任务，例如：

以下示例演示了如何创建多单元格数组公式和单单元格数组公式。在可能的情况下，我们提供了一些包含一些动态数组函数的示例，以及输入为动态和旧数组的现有数组公式。

下载我们的示例下载包含本文中所有数组公式示例的示例工作簿。

Office365Office2010-Office2019

多单元格和单单元格数组本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。第一组操作是使用多单元格公式计算一组小计。第二组操作是使用单个单元格公式计算总计。

• 多单元格数组公式

• 此处，我们通过在单元格G19中输入=F10：F19*G10：H10来计算每位销售人员的双门轿车和双门轿的总销售额。

  按enter时，你会看到结果溢出到单元格H10：H19。请注意，当你选择溢出区域中的任意单元格时，溢出范围将以边框突出显示。你可能还会注意到单元格H10中的公式是灰显的。它们只是为了引用，因此，如果你想要调整公式，则需要选择单元格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）。

  • 超过   动态数组公式将自动溢出到输出区域中。如果源数据位于Excel表中，则动态数组公式将在您添加或删除数据时自动调整大小。

  • #SPILL！时发生   动态数组引入了#SPILL！错误，表示由于某种原因，预期溢出范围被阻止。解决此问题时，该公式将自动溢出。

创建一个数组常量和两维数组常量数组常量是数组公式的组成部分。可以通过输入一系列项然后手动用大括号({})将该系列项括起来创建数组常量，类似于：

={1，2，3，4，5}或={“一月份”，”二月”，”三月”}

如果使用逗号分隔各个项，将创建水平数组（一行）。如果使用分号分隔项，将创建垂直数组（一列）。若要创建二维数组，请使用逗号分隔每行中的项目，并用分号分隔每一行。

下面将为你提供创建水平、垂直和二维常量的练习。我们将使用SEQUENCE函数显示示例，以自动生成数组常量以及手动输入的数组常量。

• 创建水平常量

  使用上述示例中所用工作簿，或创建新的工作簿。选择任何空单元格，然后按enter=SEQUENCE（1，5）。SEQUENCE函数通过与={1，2，3，4，5}生成1行x5列数组。将显示以下结果：

• 创建垂直常量

  选择下方有聊天室的任何空白单元格，然后按enter=SEQUENCE（5）或={1;2;3;4;5}。将显示以下结果：

• 创建二维常量

  选择右侧和下方带有空间的任何空白单元格，然后按enter=SEQUENCE（3，4）。将得到以下结果：

  您也可以输入：or={1，2，3，4;5，6，7，8;9，10，11，12}，但您希望在放置分号和逗号的位置上格外关注。

  正如你所看到的，序列选项比手动输入数组常量值具有显著的优势。它主要为您节省时间，但也有助于减少手动输入的错误。它也更易于阅读，尤其是在半冒号很难区分逗号分隔符。

数组常量语法下面是一个在较大公式中使用数组常量的示例。在示例工作簿中，转到公式工作表中的常量，或者创建新的工作表。

在单元格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）*序列（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)}。另外，数值不能包含百分号、货币符号、逗号或圆括号。

命名数组常量使用数组常量的最佳方式之一是将它们命名。命名的数组常量更易于使用，并且对于其他人来说，它们可以降低数组公式的复杂性。若要命名数组常量并在公式中使用它们，请执行以下操作：

转到”公式”>定义的名称>”定义名称”。在”名称”框中，键入”第1季度”。在“引用位置”框中，输入下面的常量（记住要手动键入大括号）：

={“一月”,”二月”,”三月”}

该对话框现在应如下所示：

单击”确定”，然后选择带有三个空白单元格的任何行，然后按Enter=第1季度。

将显示以下结果：

如果希望结果在垂直方向（而不是水平）溢出，可以使用=换位（第1季度）。

如果想要显示12个月的列表，例如在构建财务报表时可能使用的列表，则可以使用SEQUENCE函数将其与当前年份基本。此函数的整洁之处在于，即使只显示月份，也有一个有效的日期，您可以在其他计算中使用它。您将在示例工作簿中的命名数组常量和快速示例数据集工作表中找到这些示例。

=TEXT（日期（YEAR（TODAY（）），SEQUENCE（1，12），1），”mmm”）

这将使用DATE函数基于当前年份创建日期，序列将在1月到12月之间创建一个从1到12的数组常量，然后文本函数将显示格式转换为”mmm”（Jan、二月、三月等）。如果想要显示完整月份名称（如一月），请使用”mmmm”。

将命名常量用作数组公式时，请记住输入等号，如在=第1季度中，而不仅仅是第1季度。如果没有输入等号，Excel将数组解释为文本字符串，并且公式不会按预期工作。最后，请记住，你可以使用函数、文本和数字的组合。这完全取决于您希望获得的创意。

数组常量工作方式下面的示例演示可以将数组常量用于数组公式的几种方式。某些示例使用换位函数将行转换为列，反之亦然。

• 数组中的多个项目

  Enter=SEQUENCE（1，12）*2，或={1，2，3，4;5，6，7，8;9，10，11，12}*2

  您也可以用（/）进行除法运算，加上（+），然后用（-）进行减法运算。

• 对数组中的各项求平方

  Enter=SEQUENCE（1，12）^2，或={1，2，3，4;5，6，7，8;9，10，11，12}^2

• 查找数组中平方的平方根

  Enter=sqrt（SEQUENCE（1，12）^2），或者=SQRT（{1，2，3，4;5，6，7，8;9，10，11，12}^2）

• 转置一维行

  Enter=换位（SEQUENCE（1，5）），或=换位（{1，2，3，4，5}）

  即使输入的是水平数组常量，TRANSPOSE函数也会将该数组常量转换为列。

• 转置一维列

  Enter=换位（SEQUENCE（5，1）），或=换位（{1;2;3;4;5}）

  即使输入的是垂直数组常量，TRANSPOSE函数也会将该常量转换为行。

• 转置二维常量

  Enter=换位（SEQUENCE（3，4））或=换位（{1，2，3，4;5，6，7，8;9，10，11，12}）

  TRANSPOSE函数将各行转换为一系列的列。

使用基本数组公式本节提供基本数组公式的示例。

• 从现有值创建数组

  以下示例说明了如何使用数组公式从现有数组创建新数组。

  Enter=SEQUENCE（3，6，10，10），或={10，20，30，40，50，60;70，80，90100110120;130140150160170180}

  请确保在键入10之前键入{（左大括号），然后在键入180后键入”}”（右大括号），因为您要创建一个数字数组。

  接下来，在空白单元格中输入=d9#，或=d9：I11。3×6单元格的单元格显示，其值与D9：D11中显示的值相同。#符号称为溢出的范围运算符，它是引用整个数组范围的Excel’s方式，而无需键入它。

• 从现有值创建数组常量

  你可以获取溢出的数组公式的结果，并将其转换为其组件部分。选择单元格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（C9：C13）），LEN（C9：C13），0），1）

  本公式仅在数据区域包含单列单元格时适用。

  让我们从内部元素开始，由内而外深入了解此公式。LEN函数返回单元格区域D2：D6中每个项目的长度。MAX函数计算这些项目中的最大值，它们对应于单元格D3中最长的文本字符串。

  下面的计算稍微有点复杂。MATCH函数计算包含最长文本字符串的单元格的偏移量（相对位置）。为此，需要三个参数：分别是查阅值、查阅数组和匹配类型。MATCH函数在查阅数组中搜索指定的查阅值。在这种情况下，查阅值为最长的文本字符串：

  MAX（LEN：C13）

  并且该字符串位于此数组中：

  LEN（C9：C13）

  此例中的”匹配类型”参数为0。Match类型可以是1、0或-1值。

  • 1-返回小于或等于lookupval的最大值

  • 0-返回与查找值完全相等的第一个值

  • -1-返回大于或等于指定的查阅值的最小值

  • 如果未指定匹配类型，Excel会采用值1。

  最后，INDEX函数采用以下参数：数组，以及该数组中的行号和列号。C9的单元格区域：C13提供数组，MATCH函数提供单元格地址，最后一个参数（1）指定值来自数组中的第一列。

  如果想要获取最小文本字符串的内容，请将上述示例中的最大值替换为MIN。

• 查找出区域内的n个最小值

  此示例显示了如何查找单元格区域中的三个最小值，其中数组是单元格B9中的示例数据数组：=INT（RANDARRAY（10，1）*100）。B18has已创建。请注意，RANDARRAY是可变函数，因此每次Excel计算时你将收到一组新的随机数字。

  Enter=small（b9#，序列（D9），=SMALL（b9：B18，{1;2;3}）

  此公式使用数组常量计算小函数三次，并返回单元格B9中包含的数组中的最小3个成员：B18，其中3是单元格D9中的可变值。若要查找更多值，可以增大SEQUENCE函数中的值，或向该常量添加更多参数。还可以对此公式使用其他函数，例如SUM或AVERAGE。例如：

  =SUM（小号（B9#，序列（D9））

  =AVERAGE（SMALL（B9#，序列（D9））

• 查找出区域中的n个最大值

  若要查找区域中的最大值，可以将SMALL函数替换为大型函数。此外，下面的示例使用ROW和INDIRECT函数。

  Enter=大型（b9#、ROW（间接（”1：3″）））或=大型（b9：B18、row（”1：3″））

  现在，了解一点ROW和INDIRECT函数可能会有所帮助。可以使用ROW函数创建连续的整数数组。例如，选择一个空输入，然后输入：

  =ROW(1:10)

  此公式创建由10个连续整数组成的一列。为了查看可能的问题，请在包含数组公式的区域上面插入一行（即第1行上）。Excel将调整行引用，公式现在将生成从2到11的整数。要修正该问题，可以向该公式添加INDIRECT函数：

  =ROW(INDIRECT(“1:10”))

  间接函数将文本字符串用作其参数（这就是区域1:10围绕引号括起的原因）。当插入行或移动数组公式时，Excel不会调整文本值。因此，此ROW函数总是生成所需的整数数组。您可以同样轻松地使用序列：

  =SEQUENCE（10）

  我们来看看以前使用的公式-=大型（B9#，ROW（”1：3″））-从内部括号开始，并向外工作：间接函数返回一组文本值，在此情况下，值为1到3。ROW函数反过来生成一个三个单元格的列数组。大型函数使用单元格区域B9：B18中的值，并对ROW函数返回的每个引用进行三次计算。如果要查找更多值，请向间接函数添加一个更大的单元格区域。最后，与小型示例一样，你可以将此公式与其他函数（如SUM和AVERAGE）一起使用。

处理错误

• 对包含错误值的区域求和

  当您尝试对包含错误值的区域求和时，Excel中的SUM函数不起作用，例如#VALUE！或#N/A。此示例演示如何对包含错误的名为数据的区域中的值求和：

• =SUM(IF(ISERROR(数据),””,数据))

  该公式创建一个新数组，包含除错误值以外的原始值。从内层函数开始向外运算，ISERROR函数在单元格区域(数据)中搜索错误。IF函数在指定的条件计算结果为TRUE时返回指定值，在计算结果为FALSE时返回另一个值。在此处，它为所有错误值返回空字符串(“”)，因为它们的计算结果为TRUE，并且返回该区域（数据）中的其他值（因为这些值计算结果为FALSE，表示它们不包含错误值）。接着SUM函数计算筛选出的数组的总和。

• 计算区域中错误值个数

  此示例与上一个公式类似，但它将返回一个名为数据的区域中的错误值的数目，而不是将它们筛选掉：

  =SUM(IF(ISERROR(数据),1,0))

  该公式创建一个数组，它为包含错误的单元格包含值1，为不包含错误的单元格包含值0。可以简化该公式，并达到相同的结果，方法是移除IF函数的第三个参数，如下所示：

  =SUM(IF(ISERROR(数据),1))

  如果未指定该参数，IF函数在单元格不包含错误值时返回FALSE。可以进一步简化该公式：

  =SUM(IF(ISERROR(数据)*1))

  此公式版本可以执行计算是因为TRUE*1=1并且FALSE*1=0。

条件求和可能需要根据条件对值求和。

例如，此数组公式仅对名为Sales的区域中的正整数进行求和，这表示上例中的单元格E9：E24：

=SUM(IF(Sales>0,Sales))

IF函数创建正值和false值的数组。SUM函数实际上将忽略false值，因为0+0=0。在此公式中使用的单元格区域可以由任意数量的行和列组成。

还可以对满足多个条件的值求和。例如，此数组公式计算大于0且小于2500的值：

=SUM（（销售额>0）*（销售额<2500）*（销售））

请注意，如果区域中包含一个或多个非数字单元格，此公式将返回错误。

还可以创建使用OR条件的数组公式。例如，你可以将大于0或小于2500的值相加：

=SUM（（销售额>0）+（销售额<2500），销售额）

不能在数组公式中直接使用AND和OR函数，因为这些函数返回单一结果，TRUE或FALSE，而数组函数需要结果数组。可以通过使用上一公式中显示的逻辑来解决这一问题。换句话说，对满足OR或AND条件的值执行数学运算，如加法或乘法运算。

本示例演示当您需要对区域中的值求平均值时，如何从该区域中移除零。下面的公式使用名为“销售量”的数据区域：

=AVERAGE(IF(Sales<>0,Sales))

IF函数创建不等于0的值数组，然后将这些值传递给AVERAGE函数。

计算两个单元格区域中的不同值个数此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。如果这两个区域中的内容完全相同，此公式将返回0。若要使用此公式，单元格区域必须具有相同的大小和相同的维度。例如，如果我是3行的范围5列，则数据还必须是3行，每列5列：

=SUM(IF(我的数据=你的数据,0,1))

此公式创建与正比较的区域大小相同的新数组。IF函数使用值0和值1填充数组（0表示单元格不匹配，1表示单元格匹配）。然后SUM函数返回该数组中的值的和。

可以如下所示简化该公式：

=SUM（1*（我<>数据））

与计算区域中的错误值的公式相似，此公式版本可以执行计算是因为TRUE*1=1并且FALSE*1=0。

此数组公式返回名为“数据”的单列区域中的最大值所在的行号：

=MIN(IF(数据=MAX(数据),ROW(数据),””))

IF函数创建与名为“数据”的区域对应的新数组。如果对应的单元格包含区域中的最大值，则此数组包含该行号。否则，此数组包含空字符串(“”)。MIN函数使用此新数组作为它的第二个参数并且返回与“数据”区域中最大值的行号相对应的最小值。如果名为“数据”的区域包含完全相同的最大值，则该公式返回第一个值的行号。

如果要返回最大值的实际单元格地址，请使用下面的公式：

=ADDRESS(MIN(IF(数据=MAX(数据),ROW(数据),””)),COLUMN(数据))

您将在示例工作簿中的”数据集之间的差异”工作表中找到类似的示例。

多单元格和单单元格数组公式本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。第一组操作是使用多单元格公式计算一组小计。第二组操作是使用单个单元格公式计算总计。

• 多单元格数组公式

复制下面的整个表格，并将其粘贴到空白工作表中的单元格A1中。

销售人

汽车键入

号码售出

单元价格

总销售

刘鹏

四门轿车

5

33000

双门轿车

4

37000

尹歌

四门轿车

6

24000

双门轿车

8

21000

林彩瑜

四门轿车

3

29000

双门轿车

1

31000

潘杰

四门轿车

9

24000

双门轿车

5

37000

施德福

四门轿车

6

33000

双门轿车

8

31000

公式（总计）

总计

‘=SUM(C2:C11*D2:D11)

=SUM(C2:C11*D2:D11)

1. 若要查看每个销售人员的双门轿车和双门轿的总销售额，请选择单元格E2：E11，输入公式=C2：C11*D2：D11，然后按Ctrl+Shift+enter。

2. 若要查看所有销售的总计，请选择单元格F11，输入公式=SUM（C2：C11*D2：D11），然后按Ctrl+Shift+enter。

按Ctrl+Shift+enter时，Excel会用大括号（{}）将公式括起来，并在所选区域的每个单元格中插入公式的一个实例。因为执行速度很快，所以你在E列中看到的是每位销售人员每种轿车类型的总销售额。如果你选择E2，然后选择E3、E4等，你将看到相同的公式{=C2:C11*D2:D11}。 

• 创建单个单元格数组公式

在工作簿的单元格D13中，键入以下公式，然后按Ctrl+Shift+Enter：

=SUM(C2:C11*D2:D11)

在这种情况下，Excel会将数组中的值（单元格区域C2到D11）相乘，然后使用SUM函数将总计相加。结果等于$1,590,000的总销售额。本示例演示了此类公式的强大功能。例如，假定您有1,000行数据。您可以通过在单个单元格中创建数组公式来对部分或全部数据求和，而不是将公式向下拖动以包括所有1,000行来求和。

此外，请注意单元格D13中的单单元格公式完全独立于多单元格公式（单元格E2到E11中的公式）。这是使用数组公式的另一个优点 -灵活性。你可以更改列E中的公式或删除该列，而不会影响D13中的公式。

数组公式还具有以下优点：

• 一致性    如果单击E2下的任意单元格，您将看到相同的公式。这种一致性有助于确保更高的准确性。

• 安全    您不能覆盖多单元格数组公式的组件。例如，单击单元格E3并按Delete。您必须选择整个单元格区域（E2到E11），然后更改整个数组的公式，否则只能让数组保留原样。作为增加的安全措施，您必须按Ctrl+Shift+Enter以确认对公式所做的任何更改。

• 较小的文件大小    您通常可以使用单个数组公式，而不是多个中间公式。例如，工作簿使用一个数组公式计算列E中的结果。如果你使用的是标准公式（如=C2*D2、C3*D3、C4*D4），则你将使用11个不同的公式计算相同的结果。

数组公式语法通常，数组公式使用标准公式语法。它们都以等号开始，可以在数组公式中使用大部分内置Excel函数。主要区别是使用数组公式时，按Ctrl+Shift+enter可输入公式。执行此操作时，Excel将用大括号将数组公式括起来 -如果您手动键入大括号，公式将转换为文本字符串，并且不起作用。

数组函数可以是构建复杂公式的有效方式。数组公式=SUM(C2:C11*D2:D11)与=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11)相同。

输入和更改数组公式重要: 只要需要输入数组公式，请按Ctrl+Shift+enter。这适用于单个单元格和多单元格公式。

使用多单元格公式时，还需记住以下原则：

• 必须在输入公式之前选择用于保存结果的单元格区域。如果选定了E2到E11单元格，在创建了多单元格数组公式后，执行此操作。

• 不能更改数组公式中单个单元格的内容。要试试是否真的如此，可以选择工作簿中的单元格E3再按Delete。Excel将显示一个消息，告知您无法更改数组中的一部分。

• 可以移动或删除整个数组公式，但无法移动或删除其部分内容。换言之，要缩减数组公式，需先删除现有公式再重新开始。

• 若要删除数组公式，请选择整个公式范围（例如，E2：E11），然后按delete。

• 不能在多单元格数组公式中插入空白单元格，也不能删除单元格。

扩展数组公式有时，可能需要扩展数组公式。选择现有数组区域中的第一个单元格，然后继续操作，直到选择了要将公式扩展到的整个区域。按F2编辑公式，然后按CTRL+SHIFT+ENTER，以在调整公式区域后确认公式。关键是选择整个区域，从数组中左上角的单元格开始。左上角的单元格是一个可编辑的单元格。

使用数组公式的缺点数组公式很出色，但它们也有一些缺点：

• 有时，您可能会忘记按Ctrl+Shift+Enter。甚至最有经验的Excel用户也会发生这种情况。请记住每当输入或编辑数组公式时都要按此组合键。

• 你的工作簿的其他用户可能不理解你的公式。在实践中，工作表中通常不解释数组公式。因此，如果其他人需要修改你的工作簿，你应该避免数组公式，或者确保这些用户知道任何数组公式，并了解如何更改它们（如果需要）。

• 大型数组公式可能会降低计算速度，具体取决于计算机的处理速度和内存。

了解数组常量数组常量是数组公式的组成部分。可以通过输入一系列项然后手动用大括号({})将该系列项括起来创建数组常量，类似于：

={1,2,3,4,5}

现在，你知道创建数组公式时需要按Ctrl+Shift+Enter。因为数组常量是数组公式的组成部分，可以通过手动输入一对大括号将常量括起来。然后使用Ctrl+Shift+enter输入整个公式。

如果使用逗号分隔各个项，将创建水平数组（一行）。如果使用分号分隔项，将创建垂直数组（一列）。若要创建二维数组，应在每行中使用逗号分隔项，并使用分号分隔每行。

下面是一行中的数组：{1，2，3，4}。下面是单列中的数组：{1;2;3;4}。还有一个两行四列的数组：{1,2,3,4;5,6,7,8}。在两个行数组中，第一行是1、2、3和4，第二行是5、6、7和8。单个分号在4和5之间分隔两行。

使用数组公式时，可以将数组常量用于Excel提供的大部分内置函数中。下面几节将解释如何创建各种类型的常量以及如何将这些常量用于Excel中的函数。

创建一维和二维常量下面将为你提供创建水平、垂直和二维常量的练习。

创建水平常量

1. 在空白工作表中，选择单元格A1到E1。

2. 在编辑栏中，输入以下公式，然后按Ctrl+Shift+enter：

   ={1,2,3,4,5}

   在这种情况下，应键入左括号和右大括号（{}），Excel将为你添加第二个集。

   将显示以下结果。

创建垂直常量

1. 在工作簿中，选择一列中的五个单元格。

2. 在编辑栏中，输入以下公式，然后按Ctrl+Shift+enter：

   ={1;2;3;4;5}

   将显示以下结果。

创建二维常量

1. 在工作簿中，选择一个宽四列高三行的单元格块。

2. 在编辑栏中，输入以下公式，然后按Ctrl+Shift+enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}

   将得到以下结果：

在公式中使用常量

下面是一个使用常量的简单示例：

1. 在示例工作簿中创建一个新工作表。

2. 在单元格A1中键入3，然后在B1中键入4，在C1中键入5，在D1中键入6，并在E1中键入7。

3. 在单元格A3中，键入下面的公式，然后按Ctrl+Shift+Enter：

   =SUM(A1:E1*{1,2,3,4,5})

   请注意，Excel用另一对大括号将常量括起来，这是因为您是以数组公式的形式输入该常量。

   单元格A3中显示值85。

下节将讨论此公式的计算方法。

数组常量语法刚才使用的公式包含若干部分。

1.函数

2.存储数组

3.运算符

4.数组常量

括号内的最后一个元素是数组常量：{1,2,3,4,5}。请注意，Excel不会用大括号将数组常量括起来，您必须自己添加大括号。另请注意，在将常量添加到数组公式后，按Ctrl+Shift+enter可输入公式。

因为Excel首先对括号括起来的表达式执行运算，接下来参与运算的两个元素是存储在工作簿(A1:E1)中的值以及运算符。此时，公式将存储数组中的值与常量中对应的值相乘。它等价于：

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

最后，SUM函数将这些值相加，总和85显示在单元格A3中：

要避免使用存储数组并让运算完全位于内存中，可用另一个数组常量来替换存储数组：

=SUM({3,4,5,6,7}*{1,2,3,4,5})

若要尝试此操作，请复制函数，选择工作簿中的空白单元格，将公式粘贴到编辑栏中，然后按Ctrl+Shift+Enter。将得到与上述练习中使用以下数组公式相同的结果：

=SUM(A1:E1*{1,2,3,4,5})

常量中可以使用的元素数组常量可以包含数字、文本、逻辑值（例如TRUE和FALSE）和错误值（例如#N/A）。可以使用整数、小数和科学计数格式表示的数字。若使用了文本，您需要用引号(“)将文本括起来。

数组常量不能包含其他数组、公式或函数。换言之，它们只能包含以逗号或分号分隔的文本或数字。当您输入如下所示的公式时，Excel将显示警告消息：{1,2,A1:D4}或{1,2,SUM(Q2:Z8)}。另外，数值不能包含百分号、货币符号、逗号或圆括号。

命名数组常量使用数组常量的最佳方式之一是将它们命名。命名的数组常量更易于使用，并且对于其他人来说，它们可以降低数组公式的复杂性。若要命名数组常量并在公式中使用它们，请执行以下操作：

1. 在“公式”选项卡上的“定义的名称”组中，单击“定义名称”。将显示”定义名称”对话框。

2. 在“名称”框中，键入第1季度。

3. 在“引用位置”框中，输入下面的常量（记住要手动键入大括号）：

   ={“一月”,”二月”,”三月”}

   对话框中的内容现在类似于以下内容：

4. 单击“确定”，然后选择一行中的三个空单元格。

5. 键入下面的公式，然后按Ctrl+Shift+Enter。

   =第1季度

   将显示以下结果。

将命名常量用作数组公式时，切记要输入等号。如果没有输入等号，Excel将数组解释为文本字符串，并且公式不会按预期工作。最后，请记住可以使用文本和数字的组合。

数组常量疑难解答当数组常量不起作用时请检查下面的问题：

• 某些元素可能未使用正确的字符分隔。如果省略逗号或分号，或者如果您将其中一个括在错误的位置，则可能无法正确创建数组常量，或者您可能会看到一条警告消息。

• 选择的单元格区域可能与常量中的元素个数不匹配。例如，如果在一列中选择六个单元格用于要占用五个单元格的常量，则会在空单元格中显示#N/A错误值。反过来，如果选择的单元格太少，Excel将忽略没有对应单元格的值。

数组常量工作方式下面的示例演示可以将数组常量用于数组公式的几种方式。某些示例使用换位函数将行转换为列，反之亦然。

乘以数组中的各项

1. 新建一个工作表，然后选择一个宽四列高三行的空单元格块。

2. 键入下面的公式，然后按Ctrl+Shift+Enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}*2

对数组中的各项求平方

1. 选择一个宽四列高三行的空单元格块。

2. 键入以下数组公式，然后按Ctrl+Shift+Enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

   或者，输入下面的数组公式，它使用脱字符号(^)：

   ={1,2,3,4;5,6,7,8;9,10,11,12}^2

转置一维行

1. 选择一列中的五个空白单元格。

2. 键入下面的公式，然后按Ctrl+Shift+Enter：

   =TRANSPOSE({1,2,3,4,5})

   即使输入的是水平数组常量，TRANSPOSE函数也会将该数组常量转换为列。

转置一维列

1. 选择一列中的五个空白单元格。

2. 输入以下公式，然后按Ctrl+Shift+Enter：

   =TRANSPOSE({1;2;3;4;5})

即使输入的是垂直数组常量，TRANSPOSE函数也会将该常量转换为行。

转置二维常量

1. 选择一个宽三列高四行的单元格块。

2. 输入以下常量，然后按Ctrl+Shift+Enter：

   =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

   TRANSPOSE函数将各行转换为一系列的列。

使用基本数组公式本节提供基本数组公式的示例。

从现有值创建数组和数组常量

下面的示例介绍如何使用数组公式在不同工作表的单元格区域之间创建链接。还演示如何使用同一组值创建数组常量。

从现有值创建数组

1. 在Excel工作表上，选择单元格C8:E10，并输入此公式：

   ={10,20,30;40,50,60;70,80,90}

   确保在输入10之前输入{（左大括号），在输入90之后输入}（右大括号），因为你要创建数字数组。

2. 按Ctrl+Shift+Enter，该单元格区域C8：E10通过使用数组公式在单元格区域中输入此数字数组。 在工作表上，C8到E10应如下所示：
   10
   20
   大约

   40

   50

   60

   70

   80

   90

3. 选择单元格区域C1到E3。

4. 在编辑栏中输入以下公式，然后按Ctrl+Shift+Enter：

   =C8:E10

   通过3×3单元格数组，单元格C1到E3中显示的值与在C8到E10中看到的值相同。

从现有值创建数组常量

1. 在选定单元格C1：C3时，按F2切换到编辑模式。 

2. 按F9将单元格引用转换为值。 Excel将这些值转换为数组常量。公式现在应为={10，20，30;40，50，60;70，80，90}。

3. 按Ctrl+Shift+enter，以数组公式的形式输入数组常量。

在单元格区域中对字符计数

下面的示例演示如何计算单元格区域中的字符数（包括空格）。

1. 复制此整个表并粘贴到工作表的单元格A1中。
   数据

   这是

   很多单元格

   组合

   构成了

   一个句子。

   A2:A6中的总字符

   =SUM(LEN(A2:A6))

   最长的单元格内容(A3)

   =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

2. 选择单元格A8，然后按Ctrl+Shift+Enter，以查看单元格A2：A6（66）中的总字符数。

3. 选择单元格A10，然后按Ctrl+Shift+Enter，以查看单元格A2：A6（单元格A3）中最长的内容。

在单元格A8中使用以下公式计算单元格区域A2到A6中的字符总数（66）。

=SUM(LEN(A2:A6))

这样，LEN函数返回该区域的每个单元格中的每个文本字符串的长度。然后，SUM函数将这些值相加并显示结果（66）。

查找出区域内的n个最小值

本示例演示如何查找单元格区域内的三个最小值。

1. 在单元格A1：A11中输入一些随机数字。

2. 选择单元格C1到C3。 这组单元格将保留数组公式返回的结果。

3. 输入以下公式，然后按Ctrl+Shift+Enter：

   =SMALL（A1：A11，{1;2;3}）

此公式使用数组常量来计算小函数三次，并返回单元格A1：A10中包含的数组中的最小值（1）、第2位和第三个最小值（3）的成员。要查找更多值，请将更多参数添加到持续.还可以对此公式使用其他函数，例如SUM或AVERAGE。例如：

=SUM（SMALL（A1：A10，{1，2，3}）

=AVERAGE（SMALL（A1：A10，{1，2，3}）

查找出区域中的n个最大值

要找出区域中的多个最大值，可以使用LARGE函数替代SMALL函数。此外，下面的示例使用ROW和INDIRECT函数。

1. 选择单元格D1到D3。

2. 在编辑栏中，输入此公式，然后按Ctrl+Shift+enter：

   =大（A1：A10，ROW（”1：3″））

现在，了解一点ROW和INDIRECT函数可能会有所帮助。可以使用ROW函数创建连续的整数数组。例如，在练习工作簿中选择一个空列10个单元格，输入此数组公式，然后按Ctrl+Shift+enter：

=ROW(1:10)

此公式创建由10个连续整数组成的一列。为了查看可能的问题，请在包含数组公式的区域上面插入一行（即第1行上）。Excel调整行引用，并且此公式生成从2到11的整数。要修正该问题，可以向该公式添加INDIRECT函数：

=ROW(INDIRECT(“1:10”))

INDIRECT函数使用文本字符串作为参数（这是区域1:10由双引号括起的原因）。当插入行或移动数组公式时，Excel不会调整文本值。因此，此ROW函数总是生成所需的整数数组。

让我们看看以前使用过的公式-=大（A5：A14，ROW（”1：3″））-从内部括号开始，并向外工作：间接函数返回一组文本值，在这种情况下，值为1到3。ROW函数反过来生成一个包含三个单元格的纵栏数组。大型函数使用单元格区域A5：A14中的值，并对ROW函数返回的每个引用进行三次计算。值3200、2700和2000将返回到3个单元格的纵栏数组。如果要查找更多值，请向间接函数添加一个更大的单元格区域。

与前面的示例一样，你可以将此公式与其他函数（如SUM和AVERAGE）一起使用。

查找单元格区域中的最长文本字符串

返回到前面的文本字符串示例，在空单元格中输入以下公式，然后按Ctrl+Shift+enter：

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

显示的文本”单元格组”。

让我们从内部元素开始，由内而外深入了解此公式。LEN函数返回单元格区域A2：A6中每个项目的长度。MAX函数计算这些项目中的最大值，这两个值对应于单元格A3中最长的文本字符串。

下面的计算稍微有点复杂。MATCH函数计算包含最长文本字符串的单元格的偏移量（相对位置）。为此，需要三个参数：分别是查阅值、查阅数组和匹配类型。MATCH函数在查阅数组中搜索指定的查阅值。在这种情况下，查阅值为最长的文本字符串：

（MAX（A2：A6））

并且该字符串位于此数组中：

LEN（A2：A6）

匹配类型参数为0。匹配类型可以包含值1、0或-1。如果指定1，MATCH返回小于或等于查阅值的最大值。如果指定0，MATCH返回正好等于查阅值的第一个值。如果指定-1，MATCH查找出大于或等于指定查阅值的最小值。如果未指定匹配类型，Excel会采用值1。

最后，INDEX函数采用这些参数：数组以及该数组内的行号和列号。单元格区域A2：A6提供数组，MATCH函数提供单元格地址，最后一个参数（1）指定值来自数组中的第一列。

使用高级数组公式本节提供高级数组公式的示例。

对包含错误值的区域求和

当您尝试对包含错误值（例如#N/A）的区域求和时，Excel中的SUM函数不起作用。本示例显示如何对包含错误的命名为“数据”的区域中的值求和。

=SUM(IF(ISERROR(数据),””,数据))

该公式创建一个新数组，包含除错误值以外的原始值。从内层函数开始向外运算，ISERROR函数在单元格区域(数据)中搜索错误。IF函数在指定的条件计算结果为TRUE时返回指定值，在计算结果为FALSE时返回另一个值。在此处，它为所有错误值返回空字符串(“”)，因为它们的计算结果为TRUE，并且返回该区域（数据）中的其他值（因为这些值计算结果为FALSE，表示它们不包含错误值）。接着SUM函数计算筛选出的数组的总和。

计算区域中错误值个数

本示例与上面的公式相似，但它返回名为“数据”的区域中的错误值个数，而不是将错误值筛选掉：

=SUM(IF(ISERROR(数据),1,0))

该公式创建一个数组，它为包含错误的单元格包含值1，为不包含错误的单元格包含值0。可以简化该公式，并达到相同的结果，方法是移除IF函数的第三个参数，如下所示：

=SUM(IF(ISERROR(数据),1))

如果未指定该参数，IF函数在单元格不包含错误值时返回FALSE。可以进一步简化该公式：

=SUM(IF(ISERROR(数据)*1))

此公式版本可以执行计算是因为TRUE*1=1并且FALSE*1=0。

条件求和

可能需要根据条件对值求和。例如，此数组公式仅对名为“销售量”的区域中的正值求和：

=SUM(IF(Sales>0,Sales))

IF函数创建正值和false值数组。SUM函数实际上将忽略false值，因为0+0=0。在此公式中使用的单元格区域可以由任意数量的行和列组成。

还可以对满足多个条件的值求和。例如，下面的数组公式计算大于0并且小于等于5的值：

=SUM((Sales>0)*(Sales<=5)*(Sales))

请注意，如果区域中包含一个或多个非数字单元格，此公式将返回错误。

还可以创建使用OR条件的数组公式。例如，可以对小于5和大于15的值求和：

=SUM(IF((Sales<5)+(Sales>15),Sales))

IF函数查找所有小于5和大于15的值，然后将这些值传递给SUM函数。

不能在数组公式中直接使用AND和OR函数，因为这些函数返回单一结果，TRUE或FALSE，而数组函数需要结果数组。可以通过使用上一公式中显示的逻辑来解决这一问题。也就是，对满足OR或AND条件的值执行加法或乘法等算术运算。

计算零以外的平均值

本示例演示当您需要对区域中的值求平均值时，如何从该区域中移除零。下面的公式使用名为“销售量”的数据区域：

=AVERAGE(IF(Sales<>0,Sales))

IF函数创建不等于0的值数组，然后将这些值传递给AVERAGE函数。

计算两个单元格区域中的不同值个数

此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。如果这两个区域中的内容完全相同，此公式将返回0。要使用此公式，单元格区域的大小必须相同，其维度也必须相同（例如，如果MyData是一个3行5列区域，YourData也必须是3行5列）：

=SUM(IF(我的数据=你的数据,0,1))

此公式创建与正比较的区域大小相同的新数组。IF函数使用值0和值1填充数组（0表示单元格不匹配，1表示单元格匹配）。然后SUM函数返回该数组中的值的和。

可以如下所示简化该公式：

=SUM（1*（我<>数据））

与计算区域中的错误值的公式相似，此公式版本可以执行计算是因为TRUE*1=1并且FALSE*1=0。

查找区域中最大值的位置

此数组公式返回名为“数据”的单列区域中的最大值所在的行号：

=MIN(IF(数据=MAX(数据),ROW(数据),””))

IF函数创建与名为“数据”的区域对应的新数组。如果对应的单元格包含区域中的最大值，则此数组包含该行号。否则，此数组包含空字符串(“”)。MIN函数使用此新数组作为它的第二个参数并且返回与“数据”区域中最大值的行号相对应的最小值。如果名为“数据”的区域包含完全相同的最大值，则该公式返回第一个值的行号。

如果要返回最大值的实际单元格地址，请使用下面的公式：

=ADDRESS(MIN(IF(数据=MAX(数据),ROW(数据),””)),COLUMN(数据))

声明本文的部分内容基于由ColinWilcox编写的一系列ExcelPowerUser列，并与Excel2002公式的第14和第15章（由JohnWalkenbach编写）是一个以前的ExcelMVP。

需要更多帮助吗？可随时在Excel技术社区中咨询专家，在解答社区获得支持，或在ExcelUserVoice上建议新功能或功能改进。

另请参阅动态数组和溢出数组行为

动态数组公式与旧CSE数组公式对比

FILTER函数

RANDARRAY函数

SEQUENCE函数

SORT函数

SORTBY函数

UNIQUE函数

Excel中的#SPILL!错误

绝对交集运算符:@

公式概述

注意: 本页面是自动翻译的，可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。能否告知我们此信息是否有所帮助？下面是该参考内容的英文版。