excel公式教程:TRANSPOSE,非数组版本

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

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

赞(67)
office办公分享 » excel公式教程:TRANSPOSE,非数组版本

本文链接:excel公式教程:TRANSPOSE,非数组版本https://www.officeapi.cn/85942.html

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

OFFICE,天天学一点,进步多一点

联系我们