使用数组公式一步搞定公式批量计算，让效率提高数十倍

Excel 中数组公式非常有用， 可建立产生多值或对一组值而不是单个值进行操作的公式。 掌握数组公式的相关技能技巧， 当在不能使用工作表函数直接得到结果， 又需要对一组或多组数据进行多重计算时，方可大显身手。

下面将介绍在 Excel 2019 中数组公式的使用方法，包括输入和编辑数组、了解数组的计算方式等。

1．认识数组公式

数组公式是相对于普通公式而言的， 可以认为数组公式是 Excel 对公式和数组的一种扩充， 换句话说， 数组公式是 Excel 公式中一种专门用于数组的公式类型。

数组公式的特点就是所引用的参数是数组参数， 当把数组作为公式的参数进行输入时， 就形成了数组公式。

与普通公式的不同之处在于，数组公式能通过输入的单一公式， 执行多个输入的操作并产生多个结果， 而且每个结果都将显示在一个单元格中。

普通公式（如【=SUM(B2:D2)】【=B8+C7+D6】 等） 只占用一个单元格， 且只返回一个结果。 而数组公式可以占用一个单元格， 也可以占用多个单元格， 数组的元素可多达6500 个。 它对一组数或多组数进行多重计算， 并返回一个或多个结果。

因此， 可以将数组公式看成是有多重数值的公式， 它会让公式中有对应关系的数组元素同步执行相关的计算，或者在工作表的相应单元格区域中同时返回常量数组、 区域数组、 内存数组或命名数组中的多个元素。

2．输入数组公式

在 Excel 中， 数组公式的显示是用大括号【{}】 括住以区分普通Excel 公式。 要使用数组公式进行批量数据的处理， 首先要学会建立数组公式的方法， 具体操作步骤如下。

Step 01 如果希望数组公式只返回一个结果， 可先选择保存计算结果的单元格。 如果数组公式要返回多个结果，可选择需要保存数组公式计算结果的单元格区域。

Step 02 在编辑栏中输入数组的计算公式。

Step 03 公式输入完成后， 按【Ctrl+Shift+Enter】 组合键， 锁定输入的数组公式并确认输入。

其 中 第 3 步 使 用【Ctrl+Shift+Enter】 组合键结束公式的输入是最关键的， 这相当于用户在提示 Excel 输入的不是普通公式， 而是数组公式，需要特殊处理， 此时 Excel 就不会用常规的逻辑来处理公式了。

在 Excel 中， 只要在输入公式后按【Ctrl+Shift+Enter】 组合键结束公式， Excel 就会把输入的公式视为一个数组公式， 会自动为公式添加大括号【{}】， 以区别于普通公式。

输入公式后， 如果在第 3 步按【Enter】 键， 则输入的只是一个简单的公式， Excel 只在选择的单元格区域的第 1 个单元格位置（选择区域的左上角单元格） 显示一个计算结果。

3．使用数组公式的规则

在输入数组公式时， 必须遵循相应的规则， 否则公式将会出错， 无法计算出数据的结果。

（1） 输入数组公式时， 应先选择用来保存计算结果的单元格或单元格区域。 如果计算公式将产生多个计算结果， 必须选择一个与完成计算时所用区域大小和形状都相同的区域。

（2） 数组公式输入完成后， 按【Ctrl+Shift+Enter】 组合键， 这时在公式编辑栏中可以看见 Excel 在公式的两边加上了 {} 符号， 表示该公式是一个数组公式。 需要注意的是， {}符号是由 Excel 自动加上去的， 不用手动输入 {}； 否则， Excel 会认为输入的是一个正文标签。 但如果想在公式中直接表示一个数组， 就需要输入{} 符号将数组的元素括起来。 例如，【=IF({1,1},D2:D6,C2:C6)】 公式中数组 {1,1} 的 {} 符号就是手动输入的。

（3） 在数组公式所涉及的区域中， 既不能编辑、 清除或移动单个单元格， 也不能插入或删除其中的任何一个单元格。 这是因为数组公式所涉及的单元格区域是一个整体， 只能作为一个整体进行操作。 例如， 只能把整个区域同时删除、 清除， 而不能只删除或清除其中的一个单元格。

（4） 要编辑或清除数组公式，需要选择整个数组公式所涵盖的单元格区域， 并激活编辑栏（也可以单击数组公式所包括的任一单元格， 这时数组公式会出现在编辑栏中， 它的两边有 {} 符号， 单击编辑栏中的数组公式， 它两边的 {} 符号就会消失）， 然后在编辑栏中修改数组公式， 或者删除数组公式， 操作完成后按【Ctrl+Shift+Enter】 组合键计算出新的数据结果。

（5） 如果需要将数组公式移动至其他位置， 需要先选中整个数组公式所涵盖的单元格区域， 然后把整个区域拖放到目标位置， 也可通过【剪切】 和【粘贴】 命令进行数组公式的移动。

（6） 对于数组公式的范畴应引起注意， 在输入数值公式或函数的范围时， 其大小及外形应该与作为输入数据的范围的大小和外形相同。 如果存放结果的范围太小， 就看不到所有的运算结果； 如果存放结果的范围太大， 有些单元格就会出现错误信息【#N/A】。

4．数组公式的计算方式

为了以后能更好地运用数组公式， 还需要了解数组公式的计算方式，根据数组运算结果的多少， 将数组计算分为多单元格数组公式的计算和单个单元格数组公式的计算两种。

（1）多单元格数组公式

在 Excel 中使用数组公式可产生多值或对应一组值而不是单个值进行操作的公式， 其中能产生多个计算结果并在多个单元格中显示出来的单一数组公式， 称为【多单元格数组公式】。 在数据输入过程中出现统计模式相同， 而引用单元格不同的情况时，就可以使用多单元格数组公式来简化计算。 需要联合多单元格数组的情况主要有以下几种情况。

技术看板

多单元格数组公式主要进行批量计算， 可节省计算的时间。 输入多单元格数组公式时， 应先选择需要返回数据的单元格区域， 选择的单元格区域的行列数应与返回数组的行列数相同。 否则， 如果选中的区域小于数组返回的行列数， 将只显示该单元格区域的返回值， 其他的计算结果将不显示。 如果选择的区域大于数组返回的行列数，那超出的区域将会返回【#N/A】值。因此，在输入多单元格数组公式前，需要了解数组结果是几行几列。

①数组与单一数据的运算

一个数组与一个单一数据进行运算， 等同于将数组中的每一个元素均与这个单一数据进行计算， 并返回同样大小的数组。

例如， 在【年度优秀员工评选表】工作簿中， 要为所有员工的当前平均分上累加一个印象分， 通过输入数组公式快速计算出员工评选累计分的具体操作步骤如下。

Step 01 输入计算公式。打开素材文件年度优秀员工评选表 .xlsx，选择 I2:I12 单元格区域， 在编辑栏中输入【=H2:H12+B14】， 如图所示。

Step 02 查看计算结果。 按【Ctrl+Shift+Enter】 组合键后， 可看到编辑栏中的公式变为【{=H2:H12+B14}】， 同时会在 I2:I12 单元格区域中显示出计算的数组公式结果， 如图所示。

技术看板

该案例中的数组公式相当于在 I2单元格中输入公式【=H2+$B$14】，然后通过拖动填充控制柄复制公式到I3:I12 单元格区域中。

② 一维横向数组或一维纵向数组之间的计算

一维横向数组或一维纵向数组之间的运算， 也就是单列与单列数组或单行与单行数组之间的运算。

相比数组与单一数据的运算，只是参与运算的数据都会随时变动而已， 其实质是两个一维数组对应元素间进行运算， 即第一个数组的第一个元素与第二个数组的第一个元素进行运算， 结果作为数组公式结果的第一个元素， 然后第一个数组的第二个元素与第二个数组的第二个元素进行运算， 结果作为数组公式结果的第二个元素， 接着是第三个元素……直到第N 个元素。 一维数组之间进行运算后，返回的仍然是一个一维数组， 其行、列数与参与运算的行列数组的行列数相同。

例如， 在【销售统计表】 工作簿中， 需要计算出各产品的销售额， 即让各产品的销售量乘以其销售单价。通过输入数组公式可以快速计算出各产品的销售额， 具体操作步骤如下。

Step 01 输入计算公式。 打开素材文件销售统计表 .xlsx，选择 H3: H11 单元格区域，在编辑栏中输入【=F3:F11*G3:G11】， 如图所示。

Step 02 查看计算结果。 按【Ctrl+Shift+Enter】 组合键后， 可看到编辑栏中的公式变为【{=F3:F11*G3:G11}】， 在H3:H11 单元格区域中同时显示出计算的数组公式结果， 如图所示。

技术看板

该案例中 F3:F11*G3:G11 是两个一维数组相乘， 返回一个新的一维数组。 该案例如果使用普通公式进行计算， 通过复制公式也可以得到需要的结果， 但若需要对 100 行甚至更多行数据进行计算， 仅复制公式就会比较麻烦。

（3） 一维横向数组与一维纵向数组的计算

一维横向数组与一维纵向数组进行运算后， 将返回一个二维数组， 且返回数组的行数同一维纵向数组的行数相同、 列数同一维横向数组的列数相同。 返回数组中第 M 行第 N 列的元素是一维纵向数组的第 M 个元素和一维横向数组的第 N 个元素运算的结果。 具体的计算过程可以通过查看一维横向数组与一维纵向数组进行运算后的结果来进行分析。

例如， 在【产品合格量统计】工作表中已经将生产的产品数量输入为一组横向数组， 并将预计的可能合格率输入为一组纵向数组， 需要通过输入数组公式计算每种合格率可能性下不同产品的合格量， 具体操作步骤如下。

Step 01 输入计算公式。 打开素材文件产品合格量统计 .xlsx，选择 B2:G11 单元格区域，在编辑栏中输入【=B1:G1*A2:A11】， 如图所示。

Step 02 查看计算结果。按【Ctrl+Shift+Enter】组合键后，可看到编辑栏中的公式变为【{=B1:G1*A2:A11}】，在B2:G11 单元格区域中同时显示出计算的数组公式结果，如图所示。

（4） 行数（或列数） 相同的单列（或单行） 数组与多行多列数组的计算

单列数组的行数与多行多列数组的行数相同时， 或者单行数组的列数与多行多列数组的列数相同时， 计算规律与一维横向数组或一维纵向数组之间的运算规律大同小异， 计算结果将返回一个多行列的数组， 其行列数与参与运算的多行多列数组的行列数相同。 单列数组与多行多列数组计算时， 返回数组的第 M 行第 N 列的数据等于单列数组的第 M 行的数据与多行多列数组的第 M 行第 N 列的数据的计算结果； 单行数组与多行多列数组计算时， 返回数组的第 M 行第N 列的数据等于单行数组第 N 列的数据与多行多列数组第 M 行第 N 列数据的计算结果。

例如， 在【生产完成率统计】 工作表中已经将某一周预计要达到的生产量输入为一组纵向数组， 并将各产品的实际生产数量输入为一个二维数组， 需要通过输入数组公式计算每种产品每天的实际完成率， 具体操作步骤如下。

Step 01 输入公式。 打开素材文件生产完成率统计 .xlsx，合并 B11:G11 单元格区域， 并输入相应的文本，选择 B12:G19 单元格区域，在编辑栏中输入【=B3:G9/A3:A9】， 如图所示。

Step 02 查看数据公式计算结果。按【Ctrl+Shift+Enter】 组合键后， 可看到编辑栏中的公式变为【{=B3:G9/A3:A9}】，在 B12:G19 单元格区域中同时显示出计算的数组公式结果，如图所示。

Step 03 设置百分比格式。为整个结果区域设置边框线，在第 11 行单元格的下方插入一行单元格， 并输入相应的文本，选择 B12:G19 单元格区域，单击【开始】 选项卡【数字】 组中的【百分比样式】 按钮 ，让计算结果显示为百分比样式， 如图所示。

（5） 行列数相同的二维数组间的运算

行列相同的二维数组之间的运算， 将生成一个新的同样大小的二维数组。 其计算过程等同于第一个数组第一行的第一个元素与第二个数组第一行的第一个元素进行运算， 结果为数组公式的结果数组第一行的第一个元素， 接着是第二个， 第三个……直到第 N 个元素。

例如， 在【月考平均分统计】 工作表中已经将某些同学前 3 次月考的成绩分别统计为一个二维数组， 需要通过输入数组公式计算这些同学 3 次考试的每科成绩平均分， 具体操作步骤如下。

Step 01 输入公式。 打开素材文件月考平均分统计 .xlsx，选择B13:D18 单元格区域，在编辑栏中输入【=(B3:D8+G3:I8+L3:N8)/3】，如图所示。

Step 02 查看计算结果。 按【Ctrl+Shift+Enter】组合键后，可看到编辑栏中的公式变为【{=(B3:D8+G3:I8+L3:N8)/3}】，在 B13:D18 单元格区域中同时显示出计算的数组公式结果，如图所示。

技术看板

使用多单元格数组公式的优势在于： ①能够保证在同一个范围内的公式具有同一性， 防止用户在操作时无意间修改到表格的公式。 创建此类公式后， 公式所在的任何单元格都不能被单独编辑， 否则将会打开提示对话框， 提示用户不能更改数组的某一部分； ②能够在一个较大范围内快速生成大量具有某种规律的数据； ③数组通过数组公式运算后生成的新数组（通常称为【内存数组】） 存储在内存中，因此使用数组公式可以减少内存占用，加快公式的执行时间。

（2）单个单元格数组公式

通过前面对数组公式计算规律的讲解和案例分析， 不难发现， 一维数组公式经过运算后， 得到的结果可能是一维的， 也可能是多维的， 存放在不同的单元格区域中。 有二维数组参与的公式计算， 其结果也是一个二维数组。 总之， 数组与数组的计算， 返回的将是一个新的数组， 其行数与参与计算的数组中行数较大的数组的行数相同， 列数与参与计算的数组中列数较大的数组的列数相同。

以上两个数组公式有一个共同点， 其讲解的数组运算都是普通的公式计算， 如果将数组公式运用到函数中， 结果又会如何？ 实际上， 上面得出的两个结论都会被颠覆。 将数组用于函数计算中， 计算的结果可能是一个值， 也可能是一个一维数组或二维数组。

函数的内容将在后面的章节中进行讲解， 这里先用一个简单的例子来进行说明。 例如， 沿用【销售统计表】工作表中的数据， 下面使用一个函数来完成对所有产品的总销售利润进行统计， 具体操作步骤如下。

Step 01 计算销售利润。 打开素材文件销售统计表 .xlsx，合并F13:G13 单元格区域， 并输入相应文本，选择 H13 单元格，在编辑栏中输入【=SUM(F3:F11*G3:G11)*H1】，如图所示。

Step 02 查看计算结果。 按【Ctrl+Shift+Enter】 组合键后， 可看到编辑栏中的公式变为【={SUM(F3:F11*G3:G11)* H1}】， 在 H13 单元格中同时显示出计算的数组公式结果，如图所示。

技术看板

当运算中存在着一些只有通过复杂的中间运算过程才会得到的结果时，就必须结合使用函数和数组了。

本例的数组公式先在内存中执行计算， 将各商品的销量和单价分别相乘， 然后将数组中的所有元素用 SUM函数汇总， 得到总销售额， 最后乘以H1 单元格的利润率得出最终结果。

本例 中 的 公 式 还 可 以 用SUMPRODUCT函数来代替，输 入【=SUMPRODUCT(F3:F11* G3:G11)* H1】即可。SUMPRODUCT 函 数 的所有参数都是数组类型的参数， 直接支持多项计算，具体应用参考后面的章节。