桓楠百科网

一、认识数组公式

数组公式不同于普通公式，是以按 组合键完成编辑的特殊公式。作为数组公式的标识，Excel会自动在数组公式的首尾添加大括号“{}”。数组公式的实质是单元格公式的一种书写形式，用来通知Excel计算引擎对其执行多项计算。

当编辑已有的数组公式时，大括号会自动消失，需要重新按组合完成编辑，否则公式将无法返回正确的结果。

在数据验证和条件格式的公式中，使用数组公式的规则和在单元格中使用有所不同,仅需输入公式即可，无须按组合键完成编辑。

多项计算是对公式中有对应关系的数组元素同时分别执行相关计算的过程。按< Ctrl+Shift+Enter >组合键，即表示通知Excel执行多项计算。

以下两种情况，必须使用数组公式才能得到正确结果。

(1)当公式的计算过程中存在多项计算，并且使用的函数不支持非常量数组的多项计算时。

(2)当公式计算结果为数组，需要在多个单元格中存放公式计算结果时。

但是，并非所有执行多项计算的公式都必须以数组公式的输入方式来完成编辑，如SUMPRODUCT、LOOKUP、MMULT及MODE函数等。

数组公式的优点是能够实现其他方法无法完成的复杂计算，但也有一定的局限性。

(1)数组公式相对较难理解，尤其是在修改由他人完成的复杂数组公式时，如不完全理解编辑者的思路，将会非常困难。

(2)由于数组公式执行的是多项计算，如果工作中使用较多的数组公式，或是数组公中引用的计算范围较大时，会显著降低工作簿的计算速度.

二、多单元格数组公式

在多个单元格使用同一公式时，按组合键结束编辑形成的公式，称为多单元格数组公式。

在单个单元格中使用数组公式进行多项计算后，有时会返回一组运算结果，但单元格中只能显示单个值(通常是结果数组中的首个元素)，而无法完整显示整组运算结果。使用多单元格数组公式，则可以在选定的范围内完全展现出数组公式运算所产生的数组结果，每个单元格分别显示数组中的一个元素。

例 使用多单元格数组公式计算销售额

如下图所示的是某超市销售记录表的部分内容。需要以E3:E10单元格的单价分别乘以F3:F10单元格的数量，计算不同业务员的销售额。

同时选中G3:G10单元格区域，在编辑栏中输入以下公式( 不包括两侧大括号)，按< Ctrl+Shift+Enter > 组合键。

{=E3:E10*F3:F10}

此公式将各种商品的单价分别乘以各自的销售数量，获得一个内存数组。

{212.5;87.5;120;157.5;120;160;125;105}

公式编辑完成后，在G3:G10单元格区域中将其依次显示出来，生成的内存数组与单元格区域尺寸一致。

例 计算前三名的商品销售额

下图所示的是某商品销售记录表的部分内容，需要根据B列的单价和C列的数量计算前三名的商品销售额。

同时选中E4:E6单元格区域，在编辑栏中输入以下数组公式，按组合键。

{=LARGE(B2:B10*C2:C10,{1;2;3})}

通过“B2:B10*C2:C10”部分，将每个商品的单价分别乘以各自的销售数量，获得个内存数组。

{150;252;85;88;90;104;74;138;264}

再使用LARGE函数，以{1;2;3}作为第二参数，在内存数组中分别提取出第1~3个最大值。因为LARGE函数的第二参数使用的是1列3行的常量数组，因此得到的结果也是1列3行的数组结果。

使用多单元格数组公式的输入方式，将数组结果中的每一个元素分别显示在E4:E6是单元格区域中。

三、单个单元格数组公式

单个单元格数组公式是指在单个单元格中进行多项计算并返回单一值的数组公式。

例 统计总销售利润

沿用之前的示例的销售数据，可以使用单个单元格数组公式统计所有饮品的总销售利润。

如下图所示，G12单元格使用以下数组公式，按组合键。

{=SUM(E3:E10*F3:F10)*G1}

该公式先将各商品的单价和销量分别相乘，然后用SUM函数汇总数组中的所有元素，得到总销售额。最后乘以G1单元格的利润率，即得出所有饮品的总销售利润。由于SUM函数的参数为number类型，不能直接支持多项运算，因此，该公式必须以数组公式的形式按< Ctrl+Shift+Enter>组合键输入，通知Excel执行多项运算。本例中的公式可用SUMPRODUCT函数代替SUM函数。

=SUMPRODUCT(E3:E10*F3:E10)*G1

SUMPRODUCT函数的参数是array数组类型，直接支持多项运算，因此该公式以普通公式的形式输入就能够得出正确结果。

四、数组公式的编辑

针对多单元格数组公式的编辑有如下限制。

(1)不能单独改变公式区域中某一部分单元格的内容

(2)不能单独移动公式区域中某一部分单元格

(3)不能单独删除公式区域中某一部分单元格

(4)不能在公式区域插入新的单元格。

当用户进行以上操作时，Excel会弹出“无法更改部分数组”的提示对话框。

如需修改多单元格数组公式，具体操作步骤如下。

1、选择公式所在单元格或单元格区域，按键进入编辑模式。

2、修改公式内容后，按组合键结束编辑。

如需删除多单元格数组公式，具体操作步骤如下，

1、选择数组公式所在的任意一个单元格，按 F2>进入编辑模式

2、删除该单元格公式内容后，按 < Ctrl+Shift+Enter >组合键结束编辑

另外，还可以先选择数组公式所在的任意一个单元格，按< Ctrl +/>组合键选择多单元格数组公式区域后，按键进行删除。