要成为一名Excel高级用户，您需要知道如何使用数组公式，它能执行非数组公式所不能执行的计算。下文是基于ColinWilcox撰写的Excel高级用户专栏系列，并改编自JohnWalkenbach撰写的一书中的第14章和第15章，JohnWalkenbach是一位ExcelMVP。要了解John出版的其他书籍，请参见他的。
本节介绍数组公式并解释如何对数组公式进行输入、编辑并解答疑难问题。
如果您在Excel中使用过公式，想必知道利用公式可以执行某些相当复杂的操作。例如，可以基于给定的年数计算贷款总成本。但是，如果您确实想精通Excel，还需要掌握如何使用数组公式。因为使用数组公式可以执行更多复杂的任务，例如：
注释数组公式也被称为“CSE公式”，这是因为可以按Ctrl+Shift+Enter在工作簿中输入它们。
如果您有过一点编程经验，可能碰到过术语数组。在本文中，数组是项的集合。在Excel中，这些项可以位于一行（称为一维水平数组）中，也可位于一列（称为一维垂直数组）中或多行和多列（二维数组）中。无法在Excel中创建三维数组或三维数组公式。
数组公式是指可以在数组的一项或多项上执行多个计算的公式。数组公式可以返回多个结果，也可返回一个结果。例如，可以将数组公式放入单元格区域中，并使用数组公式计算列或行的小计。也可以将数组公式放入单个单元格中，然后计算单个量。位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。
下节中的示例将演示如何创建多单元格和单个单元格数组公式。
本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。第一组操作是使用多单元格公式计算一组小计。第二组操作是使用单个单元格公式计算总计。
Excel使用大括号({})将公式括起，并将一个公式实例放入所选区域的每个单元格中。因为执行速度很快，所以您在E列中看到的是每位销售人员每种轿车类型的总销售额。
这时，Excel会将数组（单元格区域C2到D11）中的值相乘，然后使用SUM函数将这些乘积相加。结果等于￥111,800的总销售额。本示例演示了此类公式的强大功能。例如，假定您有15,000行数据。您可以通过在单个单元格中创建数组公式来对部分或全部数据求和。
另外，请注意单个单元格公式（单元格B13中）与多单元格公式（单元格E2到E11中的公式）完全无关。这使得使用数组公式具有另一个优点—灵活性。您可以执行任意次数的操作，例如更改列E中的公式或者删除该列，这都不会影响单个单元格公式。
数组公式还具有以下优点：
数组公式主要使用标准公式语法。它们都以等号开始，可以在数组公式中使用任何内置Excel函数。使用数组公式的主要不同之处在于，必须按Ctrl+Shift+Enter输入公式。执行此操作时，Excel将用大括号将数组公式括起来—如果您手动键入大括号，公式将转换为文本字符串，并且不起作用。
您还需要注意的是数组函数是一种简化形式。例如，前面使用的多单元格函数等效于：=C2*D2=C3*D3
等。单元格B13中的单个单元格公式集中了所有这些乘法运算，另外还有将这些小计相加所需的算法：=E2+E3+E4等。
再强调一下创建数组公式的基本原则：每当需要输入或编辑数组公式时都要按Ctrl+Shift+Enter。该原则适用于单个单元格公式和多单元格公式。
使用多单元格公式时，还需遵循以下原则：
有时，可能需要扩展数组公式。（记住不能缩减数组公式。）这个过程不复杂，但必须记住上节中列出的原则。
数组公式看起来似乎功能很神奇，但它们也存在某些缺点：
本节介绍数组常量并解释如何对它们进行输入、编辑并解答疑难问题。
数组常量是数组公式的组成部分。可以通过输入一系列项然后手动用大括号({})将该系列项括起来创建数组常量，类似于：
={1,2,3,4,5}
我们在本文前面强调过在创建数组公式时需要按Ctrl+Shift+Enter。因为数组常量是数组公式的组成部分，可以通过键入一对大括号手动将常量括起来。然后使用Ctrl+Shift+Enter输入整个公式。
如果使用逗号分隔（隔开）各个项，将创建水平数组（一行）。如果使用分号分隔项，将创建垂直数组（一列）。要创建二维数组，应在每行中使用逗号分隔项，并使用分号分隔每行。
使用数组公式时，可以将数组常量用于Excel提供的所有内置函数中。下面几节将解释如何创建各种类型的常量以及如何将这些常量用于Excel中的函数。
下面将为您提供创建水平、垂直和二维常量的练习。
将得到以下结果。
您可能在想为什么不简单地手动键入这些数字。继续学习下去将得到答案，本文后面部分的一节将演示使用数组常量的优点。
将得到以下结果：
现在您已经熟悉如何输入数组常量，下面是一个使用我们讨论过的内容的简单示例：
请注意，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)}。另外，数值不能包含百分号、货币符号、逗号或圆括号。
使用数组常量的最佳方式是对它们进行命名。命名的数组常量更易于使用，并且对于初学者来说，它们可以降低数组公式的复杂性。要命名数组常量并在公式中使用它们，请执行以下操作：
将命名常量用作数组公式时，切记要输入等号。如果未输入等号，Excel会将该数组解释为文本字符串。最后，请记住可以使用文本和数字的组合。
当数组常量不起作用时请检查下面的问题：
下面的示例演示可以将数组常量用于数组公式的几种方式。某些示例使用TRANSPOSE函数将行转换为列，或将列转换为行。
即使输入的是水平数组常量，TRANSPOSE函数也会将该数组常量转换为列。
即使输入的是垂直数组常量，TRANSPOSE函数也会将该常量转换为行。
TRANSPOSE函数将各行转换为一系列的列。
本节提供基本数组公式的示例。
使用本节中的数据创建两个示例工作表。
下面的示例介绍如何使用数组公式在不同工作表的单元格区域之间创建链接。还演示如何使用同一组值创建数组常量。
该公式链接到数据工作表的单元格E1到G3中存储的值。执行此多单元格数组公式的另一个方法是在数组工作表的每个单元格中放入唯一的公式，如下所示。
如果更改了数据工作表中的某些值，这些更改将显示在数组工作表中。请注意，更改数据工作表中的任何值都必须遵循数组公式的编辑原则。有关这些原则的详细信息，请参见一节。
Excel使用下面的数组常量替换=数据!E1:G3数组公式：
={1,2,3;5,6,7;9,10,11}
数据与数组工作表之间的链接已破坏，数组公式已为数组常量替代。
下面的示例演示如何计算单元格区域中的字符数（包括空格）。
单元格C7中显示值25。
这样，LEN函数返回该区域的每个单元格中的每个文本字符串的长度。然后SUM函数将这些值相加，并在包含该公式的单元格C7中显示结果。
本示例演示如何查找单元格区域内的三个最小值。
值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}))
要找出区域中的多个最大值，可以使用LARGE函数替代SMALL函数。此外，下面的示例使用ROW和INDIRECT函数。
值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。
本示例查找单元格区域中的最长文本字符串。本公式仅在数据区域包含单列单元格时适用。
值不知天上宫阙显示在单元格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(数据))