数组公式指南和示例

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

• 多单元格数组公式

复制下面的整个表格，并将其粘贴到空白工作表的 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) 相同。

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

• 必须在输入公式之前选择用于保存结果的单元格区域。 如果选定了 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 将忽略没有对应单元格的值。

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

乘以数组中的各项

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	30
   40	50	60
   70	80	90

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

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

   =C8:E10

   单元格 C1 到 E3 中的单元格中显示一个 3x3 数组，其值与 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})

此公式使用数组常量对 SMALL 函数求值三次，并返回最小 (1) 、第二小 (2) 和第三个最小 (3 个) 成员，这些成员包含在单元格 A1：A10 中的数组中要查找更多值，请向常量添加更多参数。 还可以对此公式使用其他函数，例如 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：

   =LARGE (A1：A10，ROW (INDIRECT (“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 函数总是生成所需的整数数组。

让我们看看前面使用的公式 （ =LARGE (A5：A14，ROW (INDIRECT (“1：3”) ) ) ），从内括号开始向外工作： INDIRECT 函数返回一组文本值，在本例中为值 1 到 3。 ROW 函数反过来会生成一个由三个单元格的列数组。 LARGE 函数使用单元格区域 A5：A14 中的值，并且对 ROW 函数返回的每个引用计算三次。 值 3200、2700 和 2000 返回到三单元格列数组。 如果要查找更多值，请向 INDIRECT 函数添加更大的单元格区域。

与前面的示例一样，可以将此公式用于其他函数，例如 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 (LEN (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* (MyData<>YourData) )

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

查找区域中最大值的位置

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

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

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

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

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