1.描述

活字格中支持使用数组公式，数组公式只能在单元格中使用，表格中不支持数组公式。

数组公式是指可以在数组的一项或多项上执行多个计算的公式。您可以将数组视为一行值、一列值或行值和列值的组合。数组公式可以返回多个结果，也可返回一个结果。

包括多个单元格的数组公式称为多单元格公式，位于单个单元格中的数组公式称为单个单元格公式。

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

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

• 仅对满足特定条件的数字求和，例如某一区域中的最小值或介于上限和下限之间的数字。

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

数组公式通常又称作 CSE (Ctrl+Shift+Enter) 公式，因为不是只按 Enter 键，而是要按 Ctrl+Shift+Enter 完成公式。

2.数组公式语法

数组公式使用标准公式语法。数组公式以等号开始，可以在数组公式中使用大部分内置 Excel 函数。

使用数组公式必须按 Ctrl+Shift+Enter 提交公式。执行此操作后，数组公式将被大括号括起来。如果您手动输入大括号，公式将转换为文本字符串，不起作用。

数组公式是一种构建复杂公式的高效方式，如数组公式 “{=SUM(C2:C11*D2:D11)} ”与 一般公式“=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11)” 意义相同，但写起来更加简单。

3.多单元格公式

包括多个单元格的数组公式称为多单元格公式，在下面的示例中介绍如何创建使用多单元格公式。

图1  销售数据

在图1所示的销售数据中，如果要计算E列总销售额的值，可使用多单元格公式。

操作步骤

  选择E2:E9单元格区域。

图2  选择单元格区域

  在公式编辑栏中，输入公式“=C2:C9*D2:D9”。

图3  输入公式

  按 Ctrl+Shift+Enter，完成数组公式。在公式编辑栏中，您可以看到公式显示为“{=C2:C9*D2:D9}”，表示此公式为数组公式。

按 Ctrl+Shift+Enter 后，活字格使用大括号“{}”将公式括起，并在选定区域的各个单元格中插入数组公式。

图4  多单元格数组公式

  运行后，可在浏览器页面中看到多单元格数组公式的计算结果。

图5  计算结果

4.单个单元格公式

在单个单元格中的数组公式称为单个单元格公式。

如果您有10000行数据，您可以通过在单个单元格中创建数组公式来对部分或全部数据求和，而不是将公式向下拖动10000行来求所有行的和。

例如，在图1所示的销售数据中，如果要计算所有销售额的总和，可使用单个单元格公式。

操作步骤

  选择要显示销售额总和的单元格，例如选择F10。

  在F10中输入公式“=SUM(C2:C9*D2:D9)”。

图6  输入公式

  按 Ctrl+Shift+Enter，完成数组公式。在公式编辑栏中，您可以看到公式显示为“{=SUM(C2:C9*D2:D9)}”，表示此公式为数组公式，活字格使用大括号“{}”将公式括起。

图7  单个单元格数组公式

  运行后，可在浏览器页面中看到单个单元格公式的计算结果。

图8  计算结果

5.数组公式中引用表格模板行中的单元格

数组公式只能应用在单元格中，但数组公式可引用表格模板行中的单元格。引用表格模板行中的单元格，也就是引用了表格中对应的一列数据。

例如，在活字格中有一张员工信息表，如下图所示。

图9  员工表

如果要获取管理部中员工的最大年龄，可以使用统计字段。但如果只想要显示指定部门的最大年龄，则需要使用数组公式。

操作步骤

  在需要显示管理部最大年龄的单元格中输入公式“=MAX(IF(H3="管理部",L3,""))”。公式中的H3和L3为表格模板行中的单元格，等价于引用了表格中对应的H3和L3列。

图9  输入公式

  按Ctrl+Shift+Enter，完成数组公式。在公式编辑栏中，您可以看到公式显示为“{=MAX(IF(H3="管理部",L3,""))}”，表示此公式为数组公式，活字格使用大括号“{}”将公式括起。

图10  数组公式

   运行后，可在浏览器页面中看到计算出的管理部中的最大年龄。

图11  运行结果

6. 数组公式中使用ODATA函数展示表中的数据

通常情况下，在活字格中使用表格展示表中的数据。但如果您想使用的函数的参数不支持引用表格中的单元格，那么您就需要在普通单元格中展示表中的数据。

在这种情况下，您就可以通过在数组公式中使用ODATA函数来实现。

例如，活字格中的员工表内容如下：

图12  员工表

操作步骤

  员工表中数据有8列9行，要将其全部显示出来，则至少要选择8列9行的单元格区域。所以在活字格页面中选择A1:H9区域，在公式编辑栏中输入“=IFERROR(ODATA("员工表"),"")”。

图13  输入公式

  按Ctrl+Shift+Enter，完成数组公式。在公式编辑栏中，您可以看到公式显示为“{=IFERROR(ODATA("员工表"),"")}”，表示此公式为数组公式，活字格使用大括号“{}”将公式括起。

此时，A1:H9区域的所有单元格中都被填写上了该数组公式。

图14  数组公式

  运行后，可在浏览器页面中看到员工表中的数据全部显示了出来。

图15  运行结果

7. 编辑或删除数组公式

创建数组公式后，您可以根据实际情况对其进行编辑或删除。编辑数组公式之后，与创建数组公式一样，必须要按 Ctrl+Shift+Enter 完成数组公式，包括单个单元格公式和多单元格公式。

编辑数组公式

• 对于单个单元格公式来说，您可以直接双击数组公式进入编辑状态，编辑完成后，按 Ctrl+Shift+Enter 完成数组公式即可。

• 对于多单元格公式来说，需要注意以下几点：

①更改多单元格公式中的其中一个单元格的公式，按 Ctrl+Shift+Enter 后，整个区域的数组公式都将被更改。

②不能删除多单元格公式中的部分内容。如果您想缩减数组公式的单元格区域，您需要将整个数组公式应用的单元格区域选中，然后按 Delete 键将其删除，重新创建一个多单元格公式。

③不能向多单元格公式中插入空白单元格。

删除数组公式

• 对于单个单元格公式来说，您可以直接选中单元格，按 Delete 键将其删除。

• 对于多单元格公式来说，需要注意以下几点：

①删除多单元格公式时，需选中整个单元格区域，按 Delete 键将其删除。

②不能删除多单元格公式中的一部分。

8. 数组公式的优势

数组公式与一般公式相比，具有以下优势：

• 一致性
  在一个单元格区域应用数组公式后，单击该区域的任意一个单元格，您都将看到相同的数组公式。
• 安全性
  在一个单元格区域应用数组公式后，您将不能单独更改或删除其中一个单元格中的数组公式，必须选择应用了数组公式的整个单元格区域，然后更改或删除整个区域的数组公式，否则数组公式将保持原样。

回到顶部