TRANSPOSE，非数组版本

Excel教程 2023-05-29 22:26:17

有时候，我们想要寻找通常需要数组公式的非数组版本，其理由可能是：

1. 认为这样可以提高工作表的性能（有时可以，有时不能）

2. 不喜欢必须使用输入数组公式所必需的Ctrl+Shift+Enter组合键

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

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

下面是一个示例。假设从单元格A2起其下的单元格相加，想要确定这样的和在哪个单元格等于或超过某个数，例如5。这些单元格依次相加的和显示在列B中，假设我们不希望在解决方案中使用这样的辅助列。并且，假设我们希望返回必须相加才能达到该数的单元格数，如下所示。

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

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

=MATCH(TRUE,MMULT(0+(ROW(A2:A6)>=TRANSPOSE(ROW(A2:A6))),A2:A6)>5,0)

另一种是在公式中引入INDEX函数来代替数组公式（有关此技术的详细讨论，请参见《excel公式技巧03：INDEX函数，给公式提供数组》）。公式为：

=MATCH(TRUE,MMULT(0+(ROW(A2:A6)>=TRANSPOSE(N(INDEX(ROW(A2:A6),,)))),A2:A6)>5,0)

注意，这里不仅添加了INDEX函数，也添加了N函数。

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

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

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

答案是70。

先试试sumPRODUCT函数来简化求乘积之和的公式：

=sumPRODUCT(B1:E1*A2:A5)

结果是260，显然是错的。这是因为传递给sumPRODUCT函数的两个数组是正交的，所以求出来的和是这些数字交叉相乘后的和，如下所示。

在B1:E1和A2:A5中的每个值依次相乘后的结果为260。

这样，我们需要将B1:E1和A2:A5进行转换，以确保这两个数组是相同类型的向量，即都是单行或都是单列。下面，尝试公式：

=sumPRODUCT(B1:E1*TRANSPOSE(A2:A5))

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

=sumPRODUCT(B1:E1*3)

也就是说，TRANSPOSE函数仅返回数值3。

如果输入下面的数组公式：

=sum(B1:E1*TRANSPOSE(A2:A5))

得到正确的结果70。上面的公式能够像我们预想的那样转换：

=sum({5,6,7,8}*{1,2,3,4})

如果我们不输入本文开头的连乘和的公式（对于大量的数据是不可取的）或者刚才的数组公式，那么能够编写其他公式获得正确的结果吗？

使用非数组公式：

=sumPRODUCT(B1:E1,TRANSPOSE(INDEX(A2:A5,N(if(1,INDEX(ROW(A2:A5)-MIN(ROW(A2:A5))+1,,))))))

得到正确的结果70。

我们可以仔细地研究一下本文展示的技术，它们既可以与TRANSPOSE函数结合使用，也可以应用于其他函数。不推荐单纯为避免输入数组公式而想方设法采用其他公式，然而研究一下非数组公式解决问题也是一项有趣的练习！