excel公式教程:转置 非数组版本

有时，我们希望找到一个通常需要数组公式的非数组版本。原因可能是：

1.认为这样可以提高工作表的性能(有时是，有时不是)。

2.我不喜欢必须使用Ctrl Shift Enter，这是输入数组公式所必需的。

3.理论上，可能只是兴趣。

本文研究的数组公式的非数组版本属于第三类。强制转置在没有数组输入的情况下正常运行的必要强制措施令人费解且不切实际。这并不是说对它们没有兴趣，但这些强制使用并不是转置所独有的。事实上，您可以在许多函数中使用它们来生成返回值，否则您将需要数组输入。

以下是一个例子。让我们假设单元格A2下面的单元格相加，我们想确定在哪个单元格中这样的总和等于或超过某个数字，例如5。假设我们不想在解决方案中使用这样的辅助列，那么这些依次相加的单元格的总和显示在列b中。此外，假设我们想要返回达到这个数字必须添加的单元格数，如下图1所示。

图1

从图1中可以清楚地看到，答案是3，因为前3个单元格中的1 2 3=6比我们给出的目标数字5大。

有两种标准技术可以得到我们想要的结果。一种是使用数组公式：

=MATCH(真，MMULT(0(ROW(A2:A6)=转置(ROW(a 2: a6)))，A2:A6)5，0)

另一种是在公式中引入INDEX函数，而不是数组公式(有关这项技术的详细讨论，请参见《Excel公式技巧03：INDEX函数，给公式提供数组》)。公式是：

=MATCH(TRUE，MMULT(0(ROW(A2:A6)=转置(N(INDEX(ROW(A2:A6)，(a 2: a6))，A2:A6)5，0)

请注意，这里不仅添加了INDEX函数，还添加了n函数。

如果传递给转置的数组不是由其他函数生成的，而是实际的单元格区域引用，那么等效非数组的必要强制将更加复杂。例如，以下示例：

图2。

要求B1和A2、C1和A3、D1和A4、E1和A5的乘积之和，可以直接用公式：

=(B1*A2) (C1*A3) (D1*A4) (E1*A5)

答案是70。

首先尝试SUMPRODUCT函数来简化求乘积和的公式：

=SUMPRODUCT(b 1: E1 * a 2: a5)

结果是260，这显然是错误的。这是因为传递给SUMPRODUCT函数的两个数组是正交的，所以得到的和就是这些数交叉相乘后的和，如下图3所示。

图3。

将B1:E1和A2:A5中的每个值依次相乘的结果是260。

因此，我们需要对B1:E1和A2:A5进行变换，以保证这两个数组是同类型的向量，即都是单行或单列。现在，试试这个公式：

=SUMPRODUCT(b 1: E1 *转置(A2:A5))

结果是78。不是我们想要的结果，这个公式的中间结果是：

=SUMPRODUCT(B1:E1*3)

也就是说，转置函数只返回值3。

如果输入以下数组公式：

=SUM(b 1: E1 *转置(A2:A5))

得到正确的结果。上述公式可以按照我们的预期进行转换：

=SUM({5，6，7，8}*{1，2，3，4})

如果不输入本文开头的乘和公式(对于大量数据来说是不可取的)或者刚才的数组公式，还能写出其他公式来得到正确的结果吗？

使用非数组公式：

=SUMPRODUCT(B1:E1，转置(INDEX(A2:A5，N(IF(1，INDEX(ROW(a 2: a5)-MIN(ROW(a 2: a5))1，))))

得到正确的结果。

我们可以仔细研究本文介绍的技术，这些技术可以与转置函数结合使用，也可以应用于其他函数。不建议单纯为了避免输入数组公式而使用其他公式，但是研究非数组公式解题也是一个有趣的练习！