数组公式指南和示例
要成为一名 Excel 高级用户，您需要知道如何使用数组公式，它能执行非数组公式所不能执行的计算。下文是基于 Colin Wilcox 撰写的 Excel 高级用户专栏系列，并改编自 John Walkenbach 撰写的《Excel 2002 公式》（英文）一书中的第 14 章和第 15 章，John Walkenbach 是一位 Excel MVP。要了解 John 出版的其他书籍，请参见他的书页（英文）。
本文内容
了解数组公式本节介绍数组公式并解释如何对数组公式进行输入、编辑并解答疑难问题。 为什么要使用数组公式？如果您在 Excel 中使用过公式，想必知道利用公式可以执行某些相当复杂的操作。例如，可以基于给定的年数计算贷款总成本。但是，如果您确实想精通 Excel，还需要掌握如何使用数组公式。因为使用数组公式可以执行更多复杂的任务，例如：
注释 数组公式也被称为“CSE 公式”，这是因为可以按 Ctrl+Shift+Enter 在工作簿中输入它们。 数组和数组公式简介如果您有过一点编程经验，可能碰到过术语数组。在本文中，数组是项的集合。在 Excel 中，这些项可以位于一行（称为一维水平数组）中，也可位于一列（称为一维垂直数组）中或多行和多列（二维数组）中。无法在 Excel 中创建三维数组或三维数组公式。 数组公式是指可以在数组的一项或多项上执行多个计算的公式。数组公式可以返回多个结果，也可返回一个结果。例如，可以将数组公式放入单元格区域中，并使用数组公式计算列或行的小计。也可以将数组公式放入单个单元格中，然后计算单个量。位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。 下节中的示例将演示如何创建多单元格和单个单元格数组公式。 试一试本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。第一组操作是使用多单元格公式计算一组小计。第二组操作是使用单个单元格公式计算总计。 创建多单元格数组公式
注释 不要选择行或列标题。
从“帮助”中选择示例
=C2:C11*D2:D11
Excel 使用大括号 ({ }) 将公式括起，并将一个公式实例放入所选区域的每个单元格中。因为执行速度很快，所以您在 E 列中看到的是每位销售人员每种轿车类型的总销售额。
创建单个单元格数组公式
=SUM(C2:C11*D2:D11) 这时，Excel 会将数组（单元格区域 C2 到 D11）中的值相乘，然后使用 SUM 函数将这些乘积相加。结果等于 ￥111,800 的总销售额。本示例演示了此类公式的强大功能。例如，假定您有 15,000 行数据。您可以通过在单个单元格中创建数组公式来对部分或全部数据求和。 另外，请注意单个单元格公式（单元格 B13 中）与多单元格公式（单元格 E2 到 E11 中的公式）完全无关。这使得使用数组公式具有另一个优点 — 灵活性。您可以执行任意次数的操作，例如更改列 E 中的公式或者删除该列，这都不会影响单个单元格公式。 数组公式还具有以下优点：
数组公式语法简介数组公式主要使用标准公式语法。它们都以等号开始，可以在数组公式中使用任何内置 Excel 函数。使用数组公式的主要不同之处在于，必须按 Ctrl+Shift+Enter 输入公式。执行此操作时，Excel 将用大括号将数组公式括起来 — 如果您手动键入大括号，公式将转换为文本字符串，并且不起作用。 您还需要注意的是数组函数是一种简化形式。例如，前面使用的多单元格函数等效于： 等。单元格 B13 中的单个单元格公式集中了所有这些乘法运算，另外还有将这些小计相加所需的算法：=E2+E3+E4 等。 输入和更改数组公式原则再强调一下创建数组公式的基本原则：每当需要输入或编辑数组公式时都要按 Ctrl+Shift+Enter。该原则适用于单个单元格公式和多单元格公式。 使用多单元格公式时，还需遵循以下原则：
提示 要删除数组公式，请选择整个公式（例如，=C2:C11*D2:D11），按 Delete，再按 Ctrl+Shift+Enter。
扩展数组公式有时，可能需要扩展数组公式。（记住不能缩减数组公式。）这个过程不复杂，但必须记住上节中列出的原则。
使用数组公式的缺点数组公式看起来似乎功能很神奇，但它们也存在某些缺点：
了解数组常量本节介绍数组常量并解释如何对它们进行输入、编辑并解答疑难问题。 数组常量简介数组常量是数组公式的组成部分。可以通过输入一系列项然后手动用大括号 ({ }) 将该系列项括起来创建数组常量，类似于： ={1,2,3,4,5} 我们在本文前面强调过在创建数组公式时需要按 Ctrl+Shift+Enter。因为数组常量是数组公式的组成部分，可以通过键入一对大括号手动将常量括起来。然后使用 Ctrl+Shift+Enter 输入整个公式。 如果使用逗号分隔（隔开）各个项，将创建水平数组（一行）。如果使用分号分隔项，将创建垂直数组（一列）。要创建二维数组，应在每行中使用逗号分隔项，并使用分号分隔每行。 使用数组公式时，可以将数组常量用于 Excel 提供的所有内置函数中。下面几节将解释如何创建各种类型的常量以及如何将这些常量用于 Excel 中的函数。 创建一维和二维常量下面将为您提供创建水平、垂直和二维常量的练习。 创建水平常量
={1,2,3,4,5} 注释 在这种情况下，应键入左大括号和右大括号 ({ })。 将得到以下结果。
您可能在想为什么不简单地手动键入这些数字。继续学习下去将得到答案，本文后面部分的在公式中使用常量一节将演示使用数组常量的优点。 创建垂直常量
={1;2;3;4;5} 将得到以下结果。
创建二维常量
={1,2,3,4;5,6,7,8;9,10,11,12} 将得到以下结果：
在公式中使用常量现在您已经熟悉如何输入数组常量，下面是一个使用我们讨论过的内容的简单示例：
=SUM(A1:E1*{1,2,3,4,5}) 请注意，Excel 用另一对大括号将常量括起来，这是因为您是以数组公式的形式输入该常量。
单元格 A3 中显示 85。下节将讨论此公式的计算方法。 数组常量语法简介刚才使用的公式包含若干部分。
 函数 存储数组 运算符 数组常量括号内的最后元素是数组常量：{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季度 将得到以下结果。
将命名常量用作数组公式时，切记要输入等号。如果未输入等号，Excel 会将该数组解释为文本字符串。最后，请记住可以使用文本和数字的组合。 数组常量疑难解答当数组常量不起作用时请检查下面的问题：
数组常量工作方式下面的示例演示可以将数组常量用于数组公式的几种方式。某些示例使用 TRANSPOSE 函数将行转换为列，或将列转换为行。 乘以数组中的各项
={1,2,3,4;5,6,7,8;9,10,11,12}*2 对数组中的各项求平方
={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 转置一维行
=TRANSPOSE({1,2,3,4,5}) 即使输入的是水平数组常量，TRANSPOSE 函数也会将该数组常量转换为列。 转置一维列
=TRANSPOSE({1;2;3;4;5}) 即使输入的是垂直数组常量，TRANSPOSE 函数也会将该常量转换为行。 转置二维常量
=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12}) TRANSPOSE 函数将各行转换为一系列的列。 使用基本数组公式本节提供基本数组公式的示例。 入门使用本节中的数据创建两个示例工作表。
从现有值创建数组和数组常量下面的示例介绍如何使用数组公式在不同工作表的单元格区域之间创建链接。还演示如何使用同一组值创建数组常量。 从现有值创建数组
=数据!E1:G3 将得到以下结果。
该公式链接到数据工作表的单元格 E1 到 G3 中存储的值。执行此多单元格数组公式的另一个方法是在数组工作表的每个单元格中放入唯一的公式，如下所示。
如果更改了数据工作表中的某些值，这些更改将显示在数组工作表中。请注意，更改数据工作表中的任何值都必须遵循数组公式的编辑原则。有关这些原则的详细信息，请参见了解数组公式一节。 从现有值创建数组常量
Excel 使用下面的数组常量替换 =数据!E1:G3 数组公式： ={1,2,3;5,6,7;9,10,11} 数据与数组工作表之间的链接已破坏，数组公式已为数组常量替代。 在单元格区域中对字符计数下面的示例演示如何计算单元格区域中的字符数（包括空格）。
=SUM(LEN(C1:C5)) 单元格 C7 中显示值 25。 这样，LEN 函数返回该区域的每个单元格中的每个文本字符串的长度。然后 SUM 函数将这些值相加，并在包含该公式的单元格 C7 中显示结果。 查找出区域内的 n 个最小值本示例演示如何查找单元格区域内的三个最小值。
这组单元格将保留数组公式返回的结果。
=SMALL(A1:A10,{1;2;3}) 值 400、 475 和 500 将分别显示在单元格 A12 到 A14 中。 此公式使用数组常量计算 SMALL 函数三次，并返回单元格 A1:A10 中包含的数组中的最小值 (1)、次小值 (2) 和第三小值 (3)。要查找出更多的值，可以向该常量添加更多参数并向 A12:A14 区域添加同等个数的结果单元格。还可以对此公式使用其他函数，例如 SUM 或 AVERAGE。例如： =SUM(SMALL(A1:A10,{1;2;3})) =AVERAGE(SMALL(A1:A10,{1;2;3})) 查找出区域中的 n 个最大值要找出区域中的多个最大值，可以使用 LARGE 函数替代 SMALL 函数。此外，下面的示例使用 ROW 和 INDIRECT 函数。
=LARGE(A1:A10,ROW(INDIRECT("1:3"))) 值 3200、2700 和 2000 分别显示在单元格 A12 到 A14 中。 现在，了解一点 ROW 和 INDIRECT 函数可能会有所帮助。可以使用 ROW 函数创建连续的整数数组。例如，在练习工作簿中选择一个包含 10 个单元格的空列，在单元格 A1:A10 中输入下面的数组公式，然后按 Ctrl+Shift+Enter： =ROW(1:10) 此公式创建由 10 个连续整数组成的一列。为了查看可能的问题，请在包含数组公式的区域上面插入一行（即第 1 行上）。Excel 调整行引用，并且此公式生成从 2 到 11 的整数。要修正该问题，可以向该公式添加 INDIRECT 函数： =ROW(INDIRECT("1:10")) INDIRECT 函数使用文本字符串作为参数（这是区域 1:10 由双引号括起的原因）。当插入行或移动数组公式时，Excel 不会调整文本值。因此，此 ROW 函数总是生成所需的整数数组。 让我们以前面使用过的公式为例 — =LARGE(A1:A10,ROW(INDIRECT("1:3"))) — 从内层的括号开始向外计算：INDIRECT 函数返回一组文本值，在这种情况下，为值 1 到 3。ROW 函数依次生成包含三个单元格的纵栏式数组。 LARGE 函数使用单元格 A1:A10 中的值，并且它计算三次，每次都对应于 ROW 函数返回的每个引用。值 3200、2700 和 2000 返回到这三个单元格纵栏式数组中。如果要查找更多值，可以向 INDIRECT 函数添加更多的单元格区域。 最后，可以将此公式与其他函数一起使用，例如 SUM 和 AVERAGE。 查找单元格区域中的最长文本字符串本示例查找单元格区域中的最长文本字符串。本公式仅在数据区域包含单列单元格时适用。
=INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1) 值不知天上宫阙显示在单元格 C7 中。 让我们以此公式为例，从内层元素开始向外进行运算。LEN 函数返回单元格区域 C1:C5 中的每个项的长度。MAX 函数计算这些项中的最大值，它对应于最长文本字符串，位于单元格 C3 中。 下面的计算稍微有点复杂。MATCH 函数计算包含最长文本字符串的单元格的偏移量（相对位置）。为此，需要三个参数：分别是查阅值、查阅数组和匹配类型。MATCH 函数在查阅数组中搜索指定的查阅值。在这种情况下，查阅值为最长的文本字符串： (MAX(LEN(C1:C5)) 并且该字符串位于此数组中： LEN(C1:C5) 匹配类型参数为 0。匹配类型可以包含值 1、0 或 -1。如果指定 1，MATCH 返回小于或等于查阅值的最大值。如果指定 0，MATCH 返回正好等于查阅值的第一个值。如果指定 -1，MATCH 查找出大于或等于指定查阅值的最小值。如果未指定匹配类型，Excel 会采用值 1。 最后，INDEX 函数采用这些参数：数组以及该数组内的行号和列号。单元格区域 C1:C5 提供该数组，MATCH 函数提供单元格地址，最后的参数 (1) 指定该值来自数组的第一列。 有关此处讨论的函数的详细信息，请参见 Excel 帮助。 使用高级数组公式本节提供高级数组公式的示例。 对包含错误值的区域求和当试图对包含错误值（例如 #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(销售量>0,销售量)) IF 函数创建正值和 false 值数组。SUM 函数实际上将忽略 false 值，因为 0+0=0。在此公式中使用的单元格区域可以由任意数量的行和列组成。 还可以对满足多个条件的值求和。例如，下面的数组公式计算大于 0 并且小于等于 5 的值： =SUM((销售量>0)*(销售量<=5)*(销售量)) 请注意，如果区域中包含一个或多个非数字单元格，此公式将返回错误。 还可以创建使用 OR 条件的数组公式。例如，可以对小于 5 和大于 15 的值求和： =SUM(IF((销售量<5)+(销售量>15),销售量)) IF 函数查找所有小于 5 和大于 15 的值，然后将这些值传递给 SUM 函数。 要点 不能在数组公式中直接使用 AND 和 OR 函数，因为这些函数返回单一结果，TRUE 或 FALSE，而数组函数需要结果数组。可以通过使用上一公式中显示的逻辑来解决这一问题。也就是，对满足 OR 或 AND 条件的值执行加法或乘法等算术运算。 计算零以外的平均值本示例演示当您需要对区域中的值求平均值时，如何从该区域中移除零。下面的公式使用名为“销售量”的数据区域： =AVERAGE(IF(销售量<>0,销售量)) IF 函数创建不等于 0 的值数组，然后将这些值传递给 AVERAGE 函数。 计算两个单元格区域中的不同值个数此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。如果这两个区域中的内容完全相同，此公式将返回 0。要使用此公式，单元格区域必须大小相同并且包含相同的维数： =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(数据)) |
|
|
如何复制示例工作表数据
以匹配目标格式。
