有時(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ù)組公式并沒(méi)有給出想要的結(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)然是在通過(guò)INDIRECT函數(shù)處理后）。公式：
=SUM(INDIRECT(CELL(“address”,A1:A5)))
會(huì)解析為：
=SUM(INDIRECT(“$A$1”))
這不是我們想要的結(jié)果。
像上文介紹的那樣，先嘗試在公式中添加OFFSET函數(shù)來(lái)解決這個(gè)問(wèn)題：
=SUM(INDIRECT(CELL(“address”,OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,))))
這次仍沒(méi)有達(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ù)組輸入的原因。不知道有沒(méi)有人知道？
這樣，現(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ǔ)法。
