TRANSPOSE，非數組版本

TRANSPOSE，非數組版本

有時候，我們想要尋找通常需要數組公式的非數組版本，其理由可能是：

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函數結合使用，也可以應用于其他函數。不推薦單純為避免輸入數組公式而想方設法采用其他公式，然而研究一下非數組公式解決問題也是一項有趣的練習！

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。