excel公式教程：TRANSPOSE，非數(shù)組版本

excel公式教程：TRANSPOSE，非數(shù)組版本

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

1. 認(rèn)為這樣可以提高工作表的性能（有時可以，有時不能）

2. 不喜歡必須使用輸入數(shù)組公式所必需的Ctrl+Shift+Enter組合鍵

3. 從理論上講，可能只是感興趣

本文研究數(shù)組公式的非數(shù)組版本，更多地屬于第三類。強(qiáng)迫TRANSPOSE正常運(yùn)行而不進(jìn)行數(shù)組輸入的必要強(qiáng)制措施令人費(fèi)解且不切實(shí)際。這并不是說對它們沒有興趣，但這些強(qiáng)制性的使用并非TRANSPOSE獨(dú)有。實(shí)際上，可以在許多函數(shù)中使用它們來生成返回值，否則將需要數(shù)組輸入。

下面是一個示例。假設(shè)從單元格A2起其下的單元格相加，想要確定這樣的和在哪個單元格等于或超過某個數(shù)，例如5。這些單元格依次相加的和顯示在列B中，假設(shè)我們不希望在解決方案中使用這樣的輔助列。并且，假設(shè)我們希望返回必須相加才能達(dá)到該數(shù)的單元格數(shù)，如下圖1所示。

圖1

從圖1中可以清楚地看出，答案是3，因為前3個單元格中1+2+3=6大于我們給定的目標(biāo)數(shù)5。

有兩種標(biāo)準(zhǔn)的技術(shù)可以得到我們想要的結(jié)果。一種是使用數(shù)組公式：

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

另一種是在公式中引入INDEX函數(shù)來代替數(shù)組公式（有關(guān)此技術(shù)的詳細(xì)討論，請參見《Excel公式技巧03：INDEX函數(shù)，給公式提供數(shù)組》）。公式為：

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

注意，這里不僅添加了INDEX函數(shù)，也添加了N函數(shù)。

如果傳遞給TRANSPOSE的數(shù)組不是其他函數(shù)生成的，而是實(shí)際的單元格區(qū)域引用，那么對于等效的非數(shù)組的必要強(qiáng)制將更加復(fù)雜。例如，下面的示例：

圖2

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

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

答案是70。

先試試SUMPRODUCT函數(shù)來簡化求乘積之和的公式：

=SUMPRODUCT(B1:E1*A2:A5)

結(jié)果是260，顯然是錯的。這是因為傳遞給SUMPRODUCT函數(shù)的兩個數(shù)組是正交的，所以求出來的和是這些數(shù)字交叉相乘后的和，如下圖3所示。

圖3

在B1:E1和A2:A5中的每個值依次相乘后的結(jié)果為260。

這樣，我們需要將B1:E1和A2:A5進(jìn)行轉(zhuǎn)換，以確保這兩個數(shù)組是相同類型的向量，即都是單行或都是單列。下面，嘗試公式：

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

得到的結(jié)果是78。并不是我們想要的結(jié)果，這個公式的中間結(jié)果為：

=SUMPRODUCT(B1:E1*3)

也就是說，TRANSPOSE函數(shù)僅返回數(shù)值3。

如果輸入下面的數(shù)組公式：

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

得到正確的結(jié)果70。上面的公式能夠像我們預(yù)想的那樣轉(zhuǎn)換：

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

如果我們不輸入本文開頭的連乘和的公式（對于大量的數(shù)據(jù)是不可取的）或者剛才的數(shù)組公式，那么能夠編寫其他公式獲得正確的結(jié)果嗎？

使用非數(shù)組公式：

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

得到正確的結(jié)果70。

我們可以仔細(xì)地研究一下本文展示的技術(shù)，它們既可以與TRANSPOSE函數(shù)結(jié)合使用，也可以應(yīng)用于其他函數(shù)。不推薦單純?yōu)楸苊廨斎霐?shù)組公式而想方設(shè)法采用其他公式，然而研究一下非數(shù)組公式解決問題也是一項有趣的練習(xí)！

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。