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

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

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

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

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

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

图1

从图1中可以清楚地看出答案是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的数组不是其他函数生成的而是实际的单元格区域引用那么对于等效的非数组的必要强制将更加复杂。例如下面的示例：

图2

想要求出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函数的两个数组是正交的所以求出来的和是这些数字交叉相乘后的和如下图3所示。

图3

在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函数结合使用也可以应用于其他函数。不推荐单纯为避免输入数组公式而想方设法采用其他公式然而研究一下非数组公式解决问题也是一项有趣的练习！