excel公式技巧之強(qiáng)制返回?cái)?shù)組的公式技術(shù)

excel公式技巧之強(qiáng)制返回?cái)?shù)組的公式技術(shù)

有時(shí)候，我們希望將公式應(yīng)用于一組值而不是一個(gè)值，這可以簡(jiǎn)單地將公式作為數(shù)組公式（按Ctrl+Shift+Enter鍵）來(lái)實(shí)現(xiàn)。然而，并不是所有公式都能如此輕松地產(chǎn)生這樣的效果，有些公式很“頑強(qiáng)”地抵制任何試圖強(qiáng)制讓它們返回?cái)?shù)組的嘗試。本文將探討一些技術(shù)，除了數(shù)組形式的輸入外，可以幫助強(qiáng)制達(dá)到想要的結(jié)果。

例如，下中單元格區(qū)域A1:A5是要使用的數(shù)據(jù)，右側(cè)的數(shù)組公式并沒有給出想要的結(jié)果。（特別說(shuō)明：示例純粹是為了演示我們要解的技術(shù)。）

第一個(gè)公式使用了INDIRECT函數(shù)和ADDRESS函數(shù)組合來(lái)求單元格區(qū)域A1:A5中的數(shù)值之和。顯然，諸如下面的非數(shù)組公式：

=INDIRECT(ADDRESS(1,1))

解析成：

=INDIRECT(“$A$1”)

結(jié)果為：

9.2

因此，我們可能希望使用數(shù)組公式將這種公式構(gòu)造應(yīng)用于多個(gè)單元格。然而，使用數(shù)組輸入后的公式：

=SUM(INDIRECT(ADDRESS({1,2,3,4,5},1)))

并不會(huì)像你所希望的那樣解析，而是轉(zhuǎn)換為：

=SUM(INDIRECT(“$A$1”))

ADDRESS函數(shù)僅僅處理了數(shù)組中的第一個(gè)元素，就像我們將其作為非數(shù)組公式輸入一樣。

在這種情況下，所需的強(qiáng)制并不是特別復(fù)雜，也不需要涉及OFFSET函數(shù)或INDEX函數(shù)的構(gòu)造。實(shí)際上，所需要做的就是包含一個(gè)小的N函數(shù)：

=SUM(N(INDIRECT(ADDRESS({1,2,3,4,5},1))))

解析為：

=SUM(N(INDIRECT({“$A$1″,”$A$2″,”$A$3″,”$A$4″,”$A$5”})))

解析為：

=SUM(N({9.2,1.1,5.5,7.4,3.3})))

即：

=SUM({9.2,1.1,5.5,7.4,3.3})

結(jié)果是：

26.5

在這種公式結(jié)構(gòu)中使用N函數(shù)并不是為了將其用作函數(shù)（廣義上，是在可能的情況下將非數(shù)字轉(zhuǎn)換為數(shù)字），而是因?yàn)樗哂懈綆У模ㄒ彩欠浅Ｓ杏玫模┨匦裕耗軌虼偈蛊渌瘮?shù)操作一組數(shù)值數(shù)組，例如此處的ADDRESS函數(shù)不能操作數(shù)組的情況。

當(dāng)然，我們應(yīng)該意識(shí)到我們使用N函數(shù)是因?yàn)榇颂幍闹凳怯蓴?shù)字組成。但是，如果我們想使用帶有INDIRECT和ADDRESS函數(shù)的公式構(gòu)造生成一個(gè)數(shù)組，但是其值不是數(shù)字，而是文本，并且所得到的數(shù)組不被求和而是傳遞給其他函數(shù)，該怎么辦？

幸運(yùn)的是，Excel為提供了一個(gè)與N函數(shù)具有相似功能的T函數(shù)。與N函數(shù)一樣，T函數(shù)同樣具有強(qiáng)制返回?cái)?shù)組的特性。N函數(shù)用于數(shù)字，T函數(shù)用于文本值。

例如，如果單元格區(qū)域A1:A5中的值是“A”、”B“、”C“、”D”、“E”，那么公式：

=LOOKUP(REPT(“z”,255),T(INDIRECT(ADDRESS({1,2,3,4,5},1))))

LOOKUP函數(shù)也具有強(qiáng)制返回?cái)?shù)組的特性。公式解析為：

=LOOKUP(REPT(“z”,255),T(INDIRECT({“$A$1″,”$A$2″,”$A$3″,”$A$4″,”$A$5”})))

解析為：

=LOOKUP(REPT(“z”,255),T({“A”,”B”,”C”,”D”,”E”}))

解析為：

=LOOKUP(REPT(“z”,255),{“A”,”B”,”C”,”D”,”E”})

結(jié)果為：

E

但是，使用N或T函數(shù)強(qiáng)制轉(zhuǎn)換為數(shù)組不適用于所有函數(shù)，以包含DEC2BIN函數(shù)的公式為例：

=SUM(DEC2BIN(A1:A5))

解析為：

=SUM(#VALUE!)

結(jié)果為：

#VALUE!

這不是我們想要的結(jié)果。

試著在其中添加N函數(shù)：

=SUM(N(DEC2BIN(A1:A5)))

情況并不好，因?yàn)镈EC2BIN函數(shù)拒絕N函數(shù)試圖強(qiáng)制返回?cái)?shù)組的嘗試，解析為：

=SUM(N(#VALUE!))

那么，在這些情況下，我們需要尋求替代方法來(lái)強(qiáng)制得到我們所需的數(shù)組處理。一種方法是使用OFFSET函數(shù)，如下所示：

=SUM(0+DEC2BIN(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,)))

解析為：

=SUM(0+DEC2BIN(OFFSET(A1,{1;2;3;4;5}-1,,,)))

解析為：

=SUM(0+DEC2BIN(OFFSET(A1,{0;1;2;3;4},,,)))

解析為：

=SUM(0+DEC2BIN({9.2;1.1;5.5;7.4;3.3}))

解析為：

=SUM(0+{“1001″;”1″;”101″;”111″;”11”})

解析為：

=SUM({1001;1;101;111;11})

結(jié)果為：

1225

再提醒一下，雖然以這種方式求二進(jìn)制之和并不實(shí)用，但我們這里重在用其來(lái)演示公式技術(shù)。

這種包含OFFSET函數(shù)的公式技術(shù)可用于多個(gè)函數(shù)，這些函數(shù)最初拒絕數(shù)組輸入。在第三個(gè)示例中，使用了DOLLARDE函數(shù)，公式如下：

=SUM(DOLLARDE(A1:A5,2))

結(jié)果為#VALUE!，而公式：

=SUM(DOLLARDE(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),2))

可以正確地處理數(shù)組，并得到想要的結(jié)果32.5。

第四個(gè)示例：

=IMSUM(COMPLEX(A1:A5,1))

結(jié)果返回錯(cuò)誤值，而公式：

=IMSUM(COMPLEX(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),1))

解析為：

=IMSUM(COMPLEX(OFFSET(A1,{0;1;2;3;4},,,),1))

解析為：

=IMSUM(COMPLEX({9.2;1.1;5.5;7.4;3.3},1))

解析為：

=IMSUM({“9.2+i”;”1.1+i”;”5.5+i”;”7.4+i”;”3.3+i”})

得到想要的結(jié)果：

26.5+5i

但是，這種包含OFFSET函數(shù)的公式技術(shù)并不是使我們能夠強(qiáng)制這些“頑固”的函數(shù)產(chǎn)生數(shù)組返回值的唯一方法。在《Excel公式技巧03：INDEX函數(shù)，給公式提供數(shù)組》中，解了從INDEX函數(shù)強(qiáng)制返回?cái)?shù)組的技術(shù)，在這里也可以使用。上述示例公式的等效的INDEX函數(shù)構(gòu)造如下：

=SUM(0+DEC2BIN(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1)))))

=SUM(DOLLARDE(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1))),2))

=IMSUM(COMPLEX(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1))),1))

實(shí)際上，因?yàn)镮NDEX函數(shù)是非易失性的，所以可以說(shuō)這個(gè)版本的公式更優(yōu)于OFFSET版本的公式，盡管多使用了兩個(gè)函數(shù)N和IF。

現(xiàn)在，看看最后一個(gè)示例，該示例涉及到很少使用的CELL函數(shù)。在這里，我們嘗試與第一個(gè)示例中的INDIRECT/ADDRESS函數(shù)組合類似的方式使用此函數(shù)，即指定參數(shù)info_type為“address”來(lái)獲得要傳遞給SUM的一組單元格引用（當(dāng)然是在通過INDIRECT函數(shù)處理后）。公式：

=SUM(INDIRECT(CELL(“address”,A1:A5)))

會(huì)解析為：

=SUM(INDIRECT(“$A$1”))

這不是我們想要的結(jié)果。

像上文介紹的那樣，先嘗試在公式中添加OFFSET函數(shù)來(lái)解決這個(gè)問題：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,))))

這次仍沒有達(dá)到我們預(yù)想的結(jié)果，盡管是數(shù)組公式輸入，但這里的OFFSET函數(shù)并未操作ROW函數(shù)構(gòu)造中的所有元素。實(shí)際上，公式中的：

ROW(A1:A5)-MIN(ROW(A1:A5))

我們希望返回的結(jié)果是：

{0;1;2;3;4}

但實(shí)際上不是這樣，結(jié)果是：

0

等價(jià)于：

ROW(A1)-MIN(ROW(A1:A5))

只是處理了ROW(A1:A5)數(shù)組中的第一個(gè)元素。

奇怪！完全不是我們期望的數(shù)組公式，也不確定CELL函數(shù)拒絕接受生成數(shù)組輸入的原因。不知道有沒有人知道？

這樣，現(xiàn)在需要采取更多的強(qiáng)制辦法：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N(INDEX(ROW(A1:A5)-MIN(ROW(A1:A5)),,)),,,))))

解析為：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N(INDEX({0;1;2;3;4},,)),,,))))

解析為：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N({0;1;2;3;4}),,,))))

解析為：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,{0;1;2;3;4},,,))))

解析為：

=SUM(INDIRECT({“$A$1″;”$A$2″;”$A$3″;”$A$4″;”$A$5”}))

這樣，就與第一個(gè)示例一樣了。

值得記住的是，無(wú)論強(qiáng)制性需求的程度如何，無(wú)論它是否必然涉及一個(gè)、兩個(gè)甚至三個(gè)額外的函數(shù)，這些強(qiáng)制性函數(shù)很大程度上來(lái)自于INDEX、N（或T）和OFFSET函數(shù)的組合，并使用一些特定的語(yǔ)法。

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