精通Excel数组公式004：数学数组运算

excelperfect

先来看一个示例。下图1所示的工作表中，在单元格区域B2:B5中是一组成本数据，在单元格B8中是净成本率，想要计算每项净成本，然后将它们相加。一种方法是，在辅助列中逐项计算净成本，然后使用SUM函数将这些净成本数值相加，正如下图1所示。

图1

可以创建一个单个单元格数组公式计算出结果吗？实际上，上图1中的计算过程已经暗示了如何构建数组公式。注意到，单元格B2至B5都与单元格B8相乘，似乎可以将整个列与B8相乘，于是可以创建数组运算：

B2:B5*B8

下图2展示了一个使用数组运算B2:B5*B8的数组公式（数组乘以单项）。由于该数组运算位于SUMPRODUCT函数作为第1个参数，因此这个数组公式不需要按Ctrl+Shift+回车键。

图2

在上图2的下方，展示了数组运算的过程。第1个数组中的每个数字乘以0.85。

下图3展示了如何使用一个数组公式来计算出结果，而不需要中间的计算净成本的公式。

图3

从上例中，我们可以看到，可以从一个数组中减去单个数字。其实，也可以使用单个数字减去一个数组。再看一个示例。

下图4所示的工作表，给出一系列折扣，计算某产品的净成本。在单元格区域A2:A5中是系列折扣。先计算每次折扣后的值，即使用1减去折扣，再将这些值相乘得到净等效成本，，最后将其与零售价相乘得到结果。

图4

有了上文中的基础后，现在创建一个数组公式来获得相同的结果应该很简单。上述一步一步得出最后结果的计算步骤给创建数组公式打下了基础，一个有用的创建单个单元格数组公式的技巧是，从上面的结果开始向前追溯，从而得到最终的公式：

1. 注意到单元格B10中的公式：=ROUND(B9*B7,2)，引用了包含公式=PRODUCT(B2:B5)的单元格B7，将其代入=ROUND(B9*B7,2)中，得到：

=ROUND(B9*(PRODUCT(B2:B5),2)

2. 而在单元格区域B2:B5中，包含了所有的折扣后的值的运算，都是使用1减去一组折扣值，可以创建数组操作：1-A2:A5。将其代入上述公式，得到：

=ROUND(B9*(PRODUCT(1-A2:A5),2)

3. 将上述公式输入到单元格后，按Ctrl+Shift+回车键。结果如下图5所示。

图5

综上，我们可以看到，构造数组操作有三种可能：

1. 数组与数组的操作

2. 数组与单项的操作

3. 单项与数组的操作