【Excel应用】数组公式的应用

要成为一名 Excel 高级用户，应该知道如何使用数组公式，它能执行非数组公式所不能执行的计算。

数组公式是指可以在数组的一项或多项上执行多个计算的公式。数组公式可以返回多个结果，也可返回一个结果。

使用数组公式可以执行更多复杂的任务，例如：

• 计算包含在某个单元格区域中的字符数。

• 仅对满足特定条件的数字求和。

• 对一系列值中的第 n 个值求和。

%小提示：

数组公式也被称为“CSE 公式”，因为可以按Ctrl+Shift+Enter 在工作簿中输入它们。

1. 创建数组公式

示例1：现有一张如图 1所示的表格，需要计算D2:D9的值。

图1

每个月总销售额的计算有两种方法：一种是先定义一个单元格（如D2）的公式，然后通过拖动的方式填充到D3:D9的单元格。还有一种就是使用数组公式。具体的操作步骤是：

1) 选定D2:D9区域，输入“=”；

2) 然后鼠标点选B2:B9区域；

3) 然后输入乘号（“*”）；

4) 再选中C2:C9区域；

5) 按Ctrl+Shift+Enter组合键，就将得到D2:D9区域的结果。可以看到最后的公式形式为：{=B2:B9*C2:C9}。可以看到数组公式以一对{}括起来。

示例2：图 1所示的表格，现需要在D10中求得所有月份总销售额。

这个示例也有两种方法来计算所有月份的总销售额：一种是先定义D2:D9各个单元格的公式（即先求得每个月的销售额），然后将D2:D9加总得到D10的值。第二种方法也是借助数组公式。具体操作步骤是：

1) 选定D10单元格；

2） 输入公式：=SUM(B2:B9*C2:C9)；

3） 按Ctrl+Shift+Enter组合键，将得到D10的结果，其公式为：{=SUM(B2:B9*C2:C9)}。

值得注意的是，借助数组公式定义的D10，与D2:D9区域完全无关。您可以执行任意次数的操作，例如更改区域 D2:D9中的公式或者删除这些单元格，都不会影响D10单个单元格公式。这体现了使用数组公式的一大优点——灵活性。

数组公式的主要优点还有：

• 一致性：如果单击 D2 下的任意单元格，将看到相同的公式。这种一致性有助于确保更高的准确性。

• 安全性：不能覆盖多单元格数组公式的组成部分，例如单击单元格 D3 并按 Delete。数组公式只能整体删除或更改。

• 文件大小较小：通常可以使用单个数组公式，而不必用多个中间公式。如示例2所示，就省掉了中间计算单个销售额的公式定义。

%小提示：

位于多个单元格中的数组公式称为多单元格公式，如示例1。位于单个单元格中的数组公式称为单个单元格公式，如示例2。

2. 更改和删除数组公式

数组公式只能整体删除或更改。更改或删除数组公式的方法很简单：选定数组公式的区域，然后更改整个数组的公式，否则只能让数组保留原样。

作为一种附加安全措施，更改完成后，还必须按 Ctrl+Shift+Enter 组合键来确认对公式的更改。

示例3：还是图 1所示的表格，现假定B列为销售数量列，C列为销售额列，D列为单价列（将C1改为“总销售额”，D1改为“单价”），需要重新定义D2:D9的公式。

具体步骤如下：

1） 选定D2:D9区域；

2） 输入=C2:C9/B2:B9；

3） 按Ctrl+Shift+Enter组合键确认所作的修改。

可以在数组公式中使用任何内置 Excel 函数。使用数组公式的主要不同之处在于：必须按Ctrl+Shift+Enter 输入公式。执行此操作时，Excel 将用大括号将数组公式括起来。

值得注意的是：如果手动键入大括号，公式将转换为文本字符串，并且不起作用。

3. 扩展数组公式

有时，可能需要扩展数组公式。

示例4：在图 1所示的表格中，假如增加了两个月：9月、10月，其销售情况如图 2所示。现需要扩展数组公式，将新增的两个月的销售额计算出来。

图2

这个过程不复杂，具体步骤如下：

1） 选中D2:D11区域，这包含了原数组公式区域（D2:D9）和待计算区域（D10:D11）；

2） 按F2切换到编辑状态；

3） 在编辑栏中，将B9改为B11，C9改为C11，然后按Ctrl+Shift+Enter确认即可。