Excel数组公式指南和示例.docx
《Excel数组公式指南和示例.docx》由会员分享，可在线阅读，更多相关《Excel数组公式指南和示例.docx（22页珍藏版）》请在冰豆网上搜索。
Excel数组公式指南和示例
Excel数组公式指南和示例
全部隐藏
要成为一名Excel高级用户，您需要知道如何使用数组公式，它能执行非数组公式所不能执行的计算。
本文内容
∙了解数组公式
∙了解数组常量
∙使用基本数组公式
∙使用高级数组公式
了解数组公式
本节介绍数组公式并解释如何对数组公式进行输入、编辑并解答疑难问题。
为什么要使用数组公式？
如果您在Excel中使用过公式，想必知道利用公式可以执行某些相当复杂的操作。
例如，可以基于给定的年数计算贷款总成本。
但是，如果您确实想精通Excel，还需要掌握如何使用数组公式。
因为使用数组公式可以执行更多复杂的任务，例如：
∙计算包含在某个单元格区域中的字符数。
∙仅对满足特定条件的数字求和，例如某一区域中的最小值或介于上限和下限之间的数字。
∙对一系列值中的每第n个值求和。
注释 数组公式也被称为“CSE公式”，这是因为可以按Ctrl+Shift+Enter在工作簿中输入它们。
数组和数组公式简介
如果您有过一点编程经验，可能碰到过术语数组。
在本文中，数组是项的集合。
在Excel中，这些项可以位于一行（称为一维水平数组）中，也可位于一列（称为一维垂直数组）中或多行和多列（二维数组）中。
无法在Excel中创建三维数组或三维数组公式。
数组公式是指可以在数组的一项或多项上执行多个计算的公式。
数组公式可以返回多个结果，也可返回一个结果。
例如，可以将数组公式放入单元格区域中，并使用数组公式计算列或行的小计。
也可以将数组公式放入单个单元格中，然后计算单个量。
位于多个单元格中的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。
下节中的示例将演示如何创建多单元格和单个单元格数组公式。
试一试
本练习演示如何使用多单元格数组公式和单个单元格数组公式来计算一组销售数据。
第一组操作是使用多单元格公式计算一组小计。
第二组操作是使用单个单元格公式计算总计。
创建多单元格数组公式
1.打开一个新的空白工作簿。
2.复制示例工作表数据，并将它粘贴到的新工作簿中（从单元格A1开始）。
如何复制示例工作表数据
∙创建一个空白工作簿或工作表。
∙选择“帮助”主题中的示例。
注释 不要选择行或列标题。
从“帮助”中选择示例
∙按Ctrl+C。
∙在工作表中，选择单元格A1，然后按Ctrl+V。
销售人员
汽车类型
销售数量
单价
总销售额
刘鹏
四门轿车
5
2200
双门轿车
4
1800
尹歌
四门轿车
6
2300
双门轿车
8
1700
林彩瑜
四门轿车
3
2000
双门轿车
1
1600
潘杰
四门轿车
9
2150
双门轿车
5
1950
施德福
四门轿车
6
2250
双门轿车
8
2000
3.使用附近显示的“粘贴选项”按钮
以匹配目标格式。
4.要将数组（单元格区域C2到D11）中的值相乘，请选择单元格E2到E11，然后在编辑栏中输入以下公式：
=C2:
C11*D2:
D11
5.按Ctrl+Shift+Enter。
Excel使用大括号（{}）将公式括起，并将一个公式实例放入所选区域的每个单元格中。
因为执行速度很快，所以您在E列中看到的是每位销售人员每种轿车类型的总销售额。
创建单个单元格数组公式
1.在工作簿的单元格A13中，键入总销售额。
2.在单元格B13中，键入下面的公式并按Ctrl+Shift+Enter：
=SUM（C2:
C11*D2:
D11）
这时，Excel会将数组（单元格区域C2到D11）中的值相乘，然后使用SUM函数将这些乘积相加。
结果等于￥111,800的总销售额。
本示例演示了此类公式的强大功能。
例如，假定您有15,000行数据。
您可以通过在单个单元格中创建数组公式来对部分或全部数据求和。
另外，请注意单个单元格公式（单元格B13中）与多单元格公式（单元格E2到E11中的公式）完全无关。
这使得使用数组公式具有另一个优点—灵活性。
您可以执行任意次数的操作，例如更改列E中的公式或者删除该列，这都不会影响单个单元格公式。
数组公式还具有以下优点：
∙一致性 如果单击E2下的任意单元格，您将看到相同的公式。
这种一致性有助于确保更高的准确性。
∙安全性 您不能覆盖多单元格数组公式的组成部分，例如单击单元格E3并按Delete。
您必须选择整个单元格区域（E2到E11），然后更改整个数组的公式，否则只能让数组保留原样。
作为一种附加安全措施，必须按Ctrl+Shift+Enter确认对公式的更改。
∙文件大小较小 通常可以使用单个数组公式，而不必用多个中间公式。
例如，为本次练习创建的工作簿使用单个数组公式在列E中计算结果。
如果使用标准公式（例如=C2*D2），则要使用11个不同的公式，而计算得出的结果并无不同。
数组公式语法简介
数组公式主要使用标准公式语法。
它们都以等号开始，可以在数组公式中使用任何内置Excel函数。
使用数组公式的主要不同之处在于，必须按Ctrl+Shift+Enter输入公式。
执行此操作时，Excel将用大括号将数组公式括起来—如果您手动键入大括号，公式将转换为文本字符串，并且不起作用。
您还需要注意的是数组函数是一种简化形式。
例如，前面使用的多单元格函数等效于：
=C2*D2
=C3*D3
等。
单元格B13中的单个单元格公式集中了所有这些乘法运算，另外还有将这些小计相加所需的算法：
=E2+E3+E4等。
输入和更改数组公式原则
再强调一下创建数组公式的基本原则：
每当需要输入或编辑数组公式时都要按Ctrl+Shift+Enter。
该原则适用于单个单元格公式和多单元格公式。
使用多单元格公式时，还需遵循以下原则：
∙必须在输入公式之前选择用于保存结果的单元格区域。
在多单元格数组公式练习的第3步，您通过选择单元格E2到E11执行了此操作。
∙不能更改数组公式中单个单元格的内容。
要试试是否真的如此，可以选择示例工作簿中的单元格E3再按Delete。
∙可以移动或删除整个数组公式，但无法移动或删除其部分内容。
换言之，要缩减数组公式，需先删除现有公式再重新开始。
提示 要删除数组公式，请选择整个公式（例如，=C2:
C11*D2:
D11），按Delete，再按Ctrl+Shift+Enter。
∙不能向多单元格数组公式中插入空白单元格或删除其中的单元格。
扩展数组公式
有时，可能需要扩展数组公式。
（记住不能缩减数组公式。
）这个过程不复杂，但必须记住上节中列出的原则。
1.在示例工作簿中，清除位于主表下的所有文本和单个单元格公式。
2.将增加的数据行粘贴到工作簿中（从单元格A12开始）。
使用附近显示的“粘贴选项”按钮
以匹配目标格式。
潭思琪
四门轿车
6
2500
双门轿车
7
1900
王伟
四门轿车
4
2200
双门轿车
3
2000
杨威
四门轿车
8
2300
双门轿车
8
2100
3.选择包含当前数组公式（E2:
E11）的单元格区域，以及新数据旁边的空单元格（E12:
E17）。
也就是选择单元格E2:
E17。
4.按F2切换到编辑模式。
5.在编辑栏中，将C11更改为C17，将D11更改为D17，然后按Ctrl+Shift+Enter。
Excel会更新单元格E2到E11中的公式，并在新单元格E12到E17中放入该公式的实例。
使用数组公式的缺点
数组公式看起来似乎功能很神奇，但它们也存在某些缺点：
∙您可能有时会忘记按Ctrl+Shift+Enter。
请记住每当输入或编辑数组公式时都要按此组合键。
∙其他用户可能不理解您的公式。
数组公式相对复杂，因此如果其他人需要修改您的工作簿，您应避免使用数组公式或者确信这些用户知道如何更改您的公式。
∙大型数组公式可能会降低计算速度，具体取决于计算机的处理速度和内存。
返回页首
了解数组常量
本节介绍数组常量并解释如何对它们进行输入、编辑并解答疑难问题。
数组常量简介
数组常量是数组公式的组成部分。
可以通过输入一系列项然后手动用大括号（{}）将该系列项括起来创建数组常量，类似于：
={1,2,3,4,5}
我们在本文前面强调过在创建数组公式时需要按Ctrl+Shift+Enter。
因为数组常量是数组公式的组成部分，可以通过键入一对大括号手动将常量括起来。
然后使用Ctrl+Shift+Enter输入整个公式。
如果使用逗号分隔（隔开）各个项，将创建水平数组（一行）。
如果使用分号分隔项，将创建垂直数组（一列）。
要创建二维数组，应在每行中使用逗号分隔项，并使用分号分隔每行。
使用数组公式时，可以将数组常量用于Excel提供的所有内置函数中。
下面几节将解释如何创建各种类型的常量以及如何将这些常量用于Excel中的函数。
创建一维和二维常量
下面将为您提供创建水平、垂直和二维常量的练习。
创建水平常量
1.使用前一列所在工作簿，或启动新的工作簿。
2.选择单元格A1到E1。
3.在编辑栏中输入下面的公式，然后按Ctrl+Shift+Enter：
={1,2,3,4,5}
注释 在这种情况下，应键入左大括号和右大括号（{}）。
将得到以下结果。
您可能在想为什么不简单地手动键入这些数字。
继续学习下去将得到答案，本文后面部分的在公式中使用常量一节将演示使用数组常量的优点。
创建垂直常量
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
4
5
6
7
3.在单元格A3中，输入下面的公式，然后按Ctrl+Shift+Enter：
=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.在“公式”选项卡上的“定义的名称”组中，单击“定义名称”。
显示“定义名称”对话框。
2.在“名称”框中，键入第1季度。
3.在“引用位置”框中，输入下面的常量（记住要手动键入大括号）：
={"一月","二月","三月"}
对话框中的内容应类似如下：
4.单击“确定”。
5.在工作表中，选择一行中的三个空单元格。
6.键入下面的公式，然后按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
对数组中的各项求平方
∙选择一个宽四列高三行的空单元格块。
∙键入下面的数组公式，然后按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.打开一个现有工作簿或创建一个新工作簿，并确保其包含两个空工作表。
2.复制下表中的数据，并将它粘贴到工作表中（从单元格A1开始）。
400
水调歌头
1
2
3
4
1200
明月几时有
5
6
7
8
3200
把酒问青天
9
10
11
12
475
不知天上宫阙
13
14
15
16
500
今夕是何年
2000
600
1700
800
2700
3.完成的工作表应如下所示。
4.
5.将第一个工作表命名为数据，第二个空白工作表命名为数组。
从现有值创建数组和数组常量
下面的示例介绍如何使用数组公式在不同工作表的单元格区域之间创建链接。
还演示如何使用同一组值创建数组常量。
从现有值创建数组
1.在示例工作簿中，选择数组工作表。
2.选择单元格区域C1到E3。
3.在编辑栏中输入下面的公式，然后按Ctrl+Shift+Enter：
=数据!
E1:
G3
将得到以下结果。
该公式链接到数据工作表的单元格E1到G3中存储的值。
执行此多单元格数组公式的另一个方法是在数组工作表的每个单元格中放入唯一的公式，如下所示。
=数据!
E1
=数据!
F1
=数据!
G1
=数据!
E2
=数据!
F2
=数据!
G2
=数据!
E3
=数据!
F3
=数据!
G3
如果更改了数据工作表中的某些值，这些更改将显示在数组工作表中。
请注意，更改数据工作表中的任何值都必须遵循数组公式的编辑原则。
有关这些原则的详细信息，请参见了解数组公式一节。
从现有值创建数组常量
1.在数组工作表上，选择单元格C1到E3。
2.按F2切换到编辑模式。
3.按F9将单元格引用转换为值。
Excel将这些值转换为数组常量。
4.按Ctrl+Shift+Enter输入数组常量作为数组公式。
Excel使用下面的数组常量替换=数据!
E1:
G3数组公式：
={1,2,3;5,6,7;9,10,11}
数据与数组工作表之间的链接已破坏，数组公式已为数组常量替代。
在单元格区域中对字符计数
下面的示例演示如何计算单元格区域中的字符数（包括空格）。
∙在数据工作表的单元格C7中输入下面的公式，然后按Ctrl+Shift+Enter：
=SUM（LEN（C1:
C5））
单元格C7中显示值25。
这样，LEN函数返回该区域的每个单元格中的每个文本字符串的长度。
然后SUM函数将这些值相加，并在包含该公式的单元格C7中显示结果。
查找出区域内的n个最小值
本示例演示如何查找单元格区域内的三个最小值。
1.在数据工作表上，选择单元格A12到A14。
这组单元格将保留数组公式返回的结果。
2.在编辑栏中输入下面的公式，然后按Ctrl+Shift+Enter：
=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函数。
1.在数据工作表上，选择单元格A12到A14。
2.按Delete清除已有公式但保持单元格处于选中状态。
3.在编辑栏上输入下面的公式，并按Ctrl+Shift+Enter：
=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。
查找单元格区域中的最长文本字符串
本示例查找单元格区域中的最长文本字符串。
本公式仅在数据区域包含单列单元格时适用。
∙在数据工作表上，清除单元格C7中的已有公式，并在该单元格中输入下面的公式，然后按Ctrl+Shift+Enter：
=INDEX（C1:
C5,MATCH（MAX（LEN（C1:
C5））,LEN（C1:
C5）,0）,1）
值不知天上宫阙显示在单元格C7中。
让我们以此公式为例，从内层元素开始向外进行运算。
LEN函数返回单元格区域C1:
C5中的每个项的长度。
MAX函数计算这些项中的最大值，它对应于最长文本字符串，位于单元格C3中。
下面的计算稍微有点复杂。
MATCH函数计算包含最长文本字符串的单元格的偏移量（相对位置）。
为此，需要三
升级会员 