有時候，我們希望將公式應用于一組值而不是一個值，這可以簡單地將公式作為數組公式（按Ctrl+Shift+Enter鍵）來實現。然而，并不是所有公式都能如此輕松地產生這樣的效果，有些公式很“頑強”地抵制任何試圖強制讓它們返回數組的嘗試。本文將探討一些技術，除了數組形式的輸入外，可以幫助強制達到想要的結果。
例如，下圖1中單元格區域A1:A5是要使用的數據，右側的數組公式并沒有給出想要的結果。（特別說明：示例純粹是為了演示我們要講解的技術。）
圖1
第一個公式使用了INDIRECT函數和ADDRESS函數組合來求單元格區域A1:A5中的數值之和。顯然，諸如下面的非數組公式：
=INDIRECT(ADDRESS(1,1))
解析成：
=INDIRECT(“$A$1”)
結果為：
9.2
因此，我們可能希望使用數組公式將這種公式構造應用于多個單元格。然而，使用數組輸入后的公式：
=SUM(INDIRECT(ADDRESS({1,2,3,4,5},1)))
并不會像你所希望的那樣解析，而是轉換為：
=SUM(INDIRECT(“$A$1”))
ADDRESS函數僅僅處理了數組中的第一個元素，就像我們將其作為非數組公式輸入一樣。
在這種情況下，所需的強制并不是特別復雜，也不需要涉及OFFSET函數或INDEX函數的構造。實際上，所需要做的就是包含一個小的N函數：
=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})
結果是：
26.5
在這種公式結構中使用N函數并不是為了將其用作函數（廣義上講，是在可能的情況下將非數字轉換為數字），而是因為它具有附帶的（也是非常有用的）特性：能夠促使其他函數操作一組數值數組，例如此處的ADDRESS函數不能操作數組的情況。
當然，我們應該意識到我們使用N函數是因為此處的值是由數字組成。但是，如果我們想使用帶有INDIRECT和ADDRESS函數的公式構造生成一個數組，但是其值不是數字，而是文本，并且所得到的數組不被求和而是傳遞給其他函數，該怎么辦？
幸運的是，Excel為提供了一個與N函數具有相似功能的T函數。與N函數一樣，T函數同樣具有強制返回數組的特性。N函數用于數字，T函數用于文本值。
例如，如果單元格區域A1:A5中的值是“A”、”B“、”C“、”D”、“E”，那么公式：
=LOOKUP(REPT(“z”,255),T(INDIRECT(ADDRESS({1,2,3,4,5},1))))
LOOKUP函數也具有強制返回數組的特性。公式解析為：
=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”})
結果為：
E
但是，使用N或T函數強制轉換為數組不適用于所有函數，以包含DEC2BIN函數的公式為例：
=SUM(DEC2BIN(A1:A5))
解析為：
=SUM(#VALUE!)
結果為：
#VALUE!
這不是我們想要的結果。
試著在其中添加N函數：
=SUM(N(DEC2BIN(A1:A5)))
情況并不好，因為DEC2BIN函數拒絕N函數試圖強制返回數組的嘗試，解析為：
=SUM(N(#VALUE!))
那么，在這些情況下，我們需要尋求替代方法來強制得到我們所需的數組處理。一種方法是使用OFFSET函數，如下所示：
=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})
結果為：
1225
再提醒一下，雖然以這種方式求二進制之和并不實用，但我們這里重在用其來演示公式技術。
這種包含OFFSET函數的公式技術可用于多個函數，這些函數最初拒絕數組輸入。在第三個示例中，使用了DOLLARDE函數，公式如下：
=SUM(DOLLARDE(A1:A5,2))
結果為#VALUE!，而公式：
=SUM(DOLLARDE(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),2))
可以正確地處理數組，并得到想要的結果32.5。
第四個示例：
=IMSUM(COMPLEX(A1:A5,1))
結果返回錯誤值，而公式：
=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”})
得到想要的結果：
26.5+5i
但是，這種包含OFFSET函數的公式技術并不是使我們能夠強制這些“頑固”的函數產生數組返回值的唯一方法。在《Excel公式技巧03：INDEX函數，給公式提供數組》中，講解了從INDEX函數強制返回數組的技術，在這里也可以使用。上述示例公式的等效的INDEX函數構造如下：
=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))
實際上，因為INDEX函數是非易失性的，所以可以說這個版本的公式更優于OFFSET版本的公式，盡管多使用了兩個函數N和IF。
現在，看看最后一個示例，該示例涉及到很少使用的CELL函數。在這里，我們嘗試與第一個示例中的INDIRECT/ADDRESS函數組合類似的方式使用此函數，即指定參數info_type為“address”來獲得要傳遞給SUM的一組單元格引用（當然是在通過INDIRECT函數處理后）。公式：
=SUM(INDIRECT(CELL(“address”,A1:A5)))
會解析為：
=SUM(INDIRECT(“$A$1”))
這不是我們想要的結果。
像上文介紹的那樣，先嘗試在公式中添加OFFSET函數來解決這個問題：
=SUM(INDIRECT(CELL(“address”,OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,))))
這次仍沒有達到我們預想的結果，盡管是數組公式輸入，但這里的OFFSET函數并未操作ROW函數構造中的所有元素。實際上，公式中的：
ROW(A1:A5)-MIN(ROW(A1:A5))
我們希望返回的結果是：
{0;1;2;3;4}
但實際上不是這樣，結果是：
0
等價于：
ROW(A1)-MIN(ROW(A1:A5))
只是處理了ROW(A1:A5)數組中的第一個元素。
奇怪！完全不是我們期望的數組公式，也不確定CELL函數拒絕接受生成數組輸入的原因。不知道有沒有人知道？
這樣，現在需要采取更多的強制辦法：
=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”}))
這樣，就與第一個示例一樣了。
值得記住的是，無論強制性需求的程度如何，無論它是否必然涉及一個、兩個甚至三個額外的函數，這些強制性函數很大程度上來自于INDEX、N（或T）和OFFSET函數的組合，并使用一些特定的語法。
