TRANSPOSE，非数组版本-英雄云拓展知识分享

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

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

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

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

本文研究数组公式的非数组版本，更多地属于第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函数结合使用，也能够利用于其他函数。不推荐单纯为避免输入数组公式而千方百计采取其他公式，但是研究一下非数组公式解决问题也是一项有趣的练习！