陣列公式是一種可以對陣列中的一個或多個項目執行多項計算的公式。 您可以將陣列視為值的列或欄，或值列和欄的組合。 陣列公式可以傳回多個結果，或是單一結果。
從 Microsoft 365 的 2018 年 9 月更新開始，任何可以傳回多個結果的公式都會自動將它們溢出，或橫跨儲存格。 這種行為的改變也伴隨著幾個新的 動態陣列函數。 動態陣列公式，無論是使用現有函數或動態陣列函數，都只需要輸入單一儲存格，然後按 Enter 進行確認。 舊版的陣列公式需要先選取整個輸出範圍，然後使用 Ctrl+Shift+Enter 確認公式。 它們通常稱為 CSE 公式。
您可以使用陣列公式來執行複雜的工作，例如：
- 快速建立範例資料集。
- 計算儲存格範圍中所包含的字元數。
- 只加總符合特定條件的數字，例如範圍中的最小值，或介於上限和下限之間的數字。
- 值範圍內每 N 個值加總一次。
下列範例示範如何建立多儲存格和單一儲存格陣列公式。 在可能的情況下，我們包含了一些動態陣列函數的範例，以及同時輸入為動態和舊版陣列的現有陣列公式。
下載我們的範例
多儲存格和單一儲存格陣列
本練習會示範如何使用多儲存格及單儲存格陣列公式來計算一組銷售數字。 第一組步驟使用多儲存格公式來計算一組小計。 第二組步驟則使用單儲存格公式來計算總計。
多儲存格陣列公式
在這裡，我們要在儲存格 H10 中輸入 =F10：F19*G10：G19 ，以計算每個銷售人員的轎跑車和轎車總銷售額。
當您按 Enter 時，您會看到結果溢出到儲存格 H10：H19。 請注意，當您選取溢出範圍內的任何儲存格時，溢出範圍會以框線醒目提示。 您可能也會注意到儲存格 H10：H19 中的公式呈現灰色。它們僅供參考，因此如果您想要調整公式，必須選取儲存格 H10，這是主公式所在的位置。單一儲存格陣列公式
在範例活頁簿的儲存格 H20 中，輸入或複製並貼上 =SUM (F10：F19*G10：G19) ，然後按 Enter。
在這個案例中，Excel 會將儲存格範圍 F10 到 G19) (陣列中的值相乘，然後使用 SUM 函數將加總總和相加。 結果是總銷售額為 1,590,000 美元。
此範例顯示這類公式有多強大。 例如，假設您有 1,000 列的資料。 您可以在單一儲存格中建立陣列公式，而不是在 1,000 列中拖曳公式，以加總部分或全部資料。 另請注意，儲存格 H20 中的單一儲存格公式與多儲存格公式完全獨立， (儲存格 H10 到 H19) 的公式。 這也是使用陣列公式的另一項優點 ——彈性。 您可以變更欄 H 中的其他公式，而不會影響 H20 中的公式。 像這樣獨立的總計也是一種很好的做法，因為它有助於驗證結果的準確性。動態陣列公式也具有下列優點：
- 一致性 如果您從 H10 向下按一下任何儲存格，您會看到相同的公式。 這種一致性有助於確保提升正確性。
- 安全性 您無法覆寫多儲存格陣列公式的元件。 例如，按一下儲存格 H11，然後按 Delete。 Excel 不會變更陣列的輸出。 若要變更，您必須選取陣列中的左上方儲存格或儲存格 H10。
- 較小的檔案大小 您通常可以使用單一陣列公式，而非數個中間公式。 例如，汽車銷售範例使用一個陣列公式來計算 E 欄中的結果。如果您使用標準公式，例如 =F10*G10、F11*G11、F12*G12 等，您會使用 11 個不同的公式來計算相同的結果。 這沒什麼大不了的，但如果你總共有數千列呢？ 然後它可以產生很大的不同。
- 效率 陣列函數是建立複雜公式的有效方法。 陣列公式 =SUM (F10：F19*G10：G19) 與以下相同：=SUM (F10*G10，F11*G11，F12*G12，F13*G13，F14*G14，F15*G15，F16*G16，F17*G17，F18*G18，F19*G19) 。
- 溢出動態陣列公式會自動溢出到輸出範圍。 如果您的來源資料位於 Excel 表格中，則動態陣列公式將會隨著您新增或移除資料而自動調整大小。
- Excel 中的 #溢出! 錯誤 動態陣列引入了 #SPILL！ 錯誤，這表示由於某種原因封鎖了預期的溢出範圍。 解決堵塞問題時，配方會自動溢出。
建立一維和二維陣列常數
矩陣常數是陣列公式的一項元件。 您可以輸入項目清單來建立矩陣常數，然後手動輸入大括弧 ({ }) 括住清單，如下所示：
={1,2,3,4,5} 或 ={“January”，“February”，“March”}
如果是使用逗號來分隔項目，便會建立水平陣列 (列)。 如果是使用分號來分隔項目，便會建立垂直陣列 (欄)。 若要建立二維陣列，您可以使用逗號分隔每一列中的項目，並以分號分隔每一列。
下列程序可讓您稍加練習如何建立水平、垂直及二維常數。 我們將展示使用 SEQUENCE 函數 自動產生陣列常數以及手動輸入陣列常數的範例。
-
建立水平常數
使用先前範例的活頁簿，或建立新的活頁簿。 選取任何空白儲存格，然後輸入 =SEQUENCE (1,5) 。 SEQUENCE 函數建構與 ={1,2,3,4,5} 相同的 1 列乘 5 欄陣列。 畫面會顯示下列結果：
-
建立垂直常數
選取任何下方有空格的空白儲存格，然後輸入 =SEQUENCE (5) ，或 ={1;2;3;4;5}。 畫面會顯示下列結果：
-
建立二維常數
選取任何右側和下方有空間的空白儲存格，然後輸入 =SEQUENCE (3,4) 。 您會看到以下結果：
您也可以輸入： 或 ={1,2,3,4;5,6,7,8;9、10、11、12}，但您需要注意分號與逗號的放置位置。
如您所見，SEQUENCE 選項比手動輸入陣列常數值具有顯著優勢。 首先，它可以節省您的時間，但也可以幫助減少手動輸入造成的錯誤。 它也更容易閱讀，特別是因為分號可能很難與逗號分隔符號區分開。
常數陣列語法
以下是使用陣列常數做為較大公式一部分的範例。 在範例活頁簿中，移至公式工作表 中的常數 ，或建立新工作表。
在儲存格 D9 中，我們輸入 =SEQUENCE (1,5,3,1) ，但您也可以在儲存格 A9：H9 中輸入 3、4、5、6 和 7。 這個特定的數字選擇並沒有什麼特別之處，我們只是選擇了 1-5 以外的其他數字來進行差異化。
在儲存格 E11 中，輸入 =SUM (D9：H9*SEQUENCE (1,5) ) ，或 =SUM (D9：H9*{1,2,3,4,5}) 。 公式將返回 85。
SEQUENCE 函數建置等同於常數 {1,2,3,4,5}陣列的函數。 由於 Excel 會先對括弧中的運算式執行作業，因此接下來的兩個元素是 D9：H9 中的儲存格值，以及乘法運算子 (*) 。 此時，公式會將已儲存陣列中的值乘以常數中的對應值。 其結果等於：
=SUM (D9*1，E9*2，F9*3，G9*4，H9*5) ，或 =SUM (3*1,4*2,5*3,6*4,7*5)
最後，SUM 函數會加總值，並傳回 85。
若要避免使用預存的陣列，並將作業完全保留在記憶體中，您可以將它取代為另一個常數陣列：
=SUM (SEQUENCE (1,5,3,1) *SEQUENCE (1,5) ) ，或 =SUM ({3,4,5,6,7}*{1,2,3,4,5})
可在陣列常數中使用的元素
- 陣列常數可以包含數字、文字、邏輯值 (例如 TRUE 和 FALSE) ，以及錯誤值 例如 #N/A。 您可以使用整數、十進位和科學格式的數字。 如果您包含文字，請用引號括住文字 (“text”) 。
- 矩陣常數不能包含其他的陣列、公式或函數。 換句話說，只能包含那些以逗點或分號分隔的文字或數字。 當您輸入 {1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)} 這類的公式時，Excel 會顯示警告訊息。 此外，數值不能包含百分比符號、貨幣符號、逗號或括弧。
為矩陣常數命名
使用陣列常數的最佳方式之一就是命名它們。 已命名的常數使用起來更加容易，而且可以隱藏一些陣列公式的複雜性，不讓其他人看見。 若要為矩陣常數命名並用在公式中，請執行下列步驟：
移至 [ 公式]、[>定義的名稱]、[>定義名稱]。 在 [名稱 ] 方塊中，輸入 Quarter1。 在 [參照到] 方塊中，輸入以下常數 (記得要手動輸入大括弧)：
={"一月","二月","三月"}
對話方塊現在看起來應該像這樣：
按一下 [ 確定]，然後選取任何含有三個空白儲存格的列，並輸入 =Quarter1。
畫面會顯示下列結果：
如果您希望結果垂直溢出，而不是水平溢出，您可以使用 =TRANSPOSE (Quarter1) 。
如果您想要顯示 12 個月的清單，就像建立財務報表時可能會使用之一樣，您可以使用 SEQUENCE 函數以當年為基準。 這個函數的巧妙之處在於，即使只顯示月份，它背後也有一個有效的日期，您可以在其他計算中使用。 您可以在範例活頁簿中的具 名陣列常數 和 快速範例資料集 工作表中找到這些範例。
=TEXT (DATE (YEAR (TODAY () ) ，SEQUENCE (1,12) ,1) ，“MMM”)
這會使用 DATE 函數 根據當年建立日期，SEQUENCE 會為 1 月至 12 月建立 1 到 12 的常數陣列，然後 TEXT 函數 會將顯示格式轉換為 “mmm” (1 月、2 月、3 月等日期 ) 。 如果您想要顯示完整的月份名稱，例如 1 月，您可以使用 “mmmm”。
當您使用具名常數作為陣列公式時，請記得輸入等號，如 =Quarter1，而不只是 Quarter1。 若未輸入等號，Excel 會將陣列解譯為文字字串，而公式會無法如預期般運作。 最後，請記住，您可以使用函數、文字和數字的組合。 這完全取決於您想要獲得多大的創意。
使用矩陣常數
以下範例提出多種方式，為您示範如何在陣列公式中使用矩陣常數。 某些範例使用 TRANSPOSE 函數 將列轉換為欄，反之亦然。
-
在陣列中多個項目
輸入 =SEQUENCE (1,12) *2，或 ={1,2,3,4;5,6,7,8;9,10,11,12}*2
您也可以用 (/) 除法，用 (+) 加法，用 (-) 減去。 -
求陣列中項目的平方值
輸入 =SEQUENCE (1,12) ^2，或 ={1,2,3,4;5,6,7,8;9,10,11,12}^2 -
尋找陣列中項目平方的平方根
輸入 =SQRT (SEQUENCE (1,12) ^2) ，或 =SQRT ({1,2,3,4;5,6,7,8;9,10,11,12}^2) -
轉置一維列
輸入 =TRANSPOSE (SEQUENCE (1,5) ) ，或 =TRANSPOSE ({1,2,3,4,5})
即使輸入水平矩陣常數，TRANSPOSE 函數也會將矩陣常數轉換至欄中。 -
轉置一維欄
輸入 =TRANSPOSE (SEQUENCE (5,1) ) ，或 =TRANSPOSE ({1;2;3;4;5})
即使輸入垂直矩陣常數，TRANSPOSE 函數也會將常數轉換至列中。 -
轉置二維常數
輸入 =TRANSPOSE (SEQUENCE (3,4) ) ，或 =TRANSPOSE ({1,2,3,4;5,6,7,8;9,10,11,12})
TRANSPOSE 函數會將各列轉換成一系列欄。
讓基本陣列公式開始運作
本節內容提供基本陣列函數的範例。
從現有值建立陣列
下列範例說明如何使用陣列公式從現有的陣列建立新陣列。
輸入 =SEQUENCE (3,6,10,10) ，或 ={10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170,180}
請務必在輸入 10 之前輸入 { (左大括弧) ，並在輸入 180 之後 (右大括弧) }，因為您正在建立數位陣列。
接下來，在空白儲存格中輸入 =D9# 或 =D9：I11 。 一個 3 x 6 的儲存格陣列隨即出現，其值與您在 D9：D11 中看到的值相同。 # 符號稱為 溢出範圍運算子，這是 Excel 參照整個陣列範圍的方式，而不必再輸入出來。
從現有的值建立矩陣常數
您可以取得溢出陣列公式的結果，並將其轉換成其組成部分。 選取儲存格 D9，然後按 F2 切換至編輯模式。 接下來，按 F9 將儲存格參照轉換成值，Excel 接著將其轉換成常數陣列。 當您按 Enter 時，公式 =D9# 現在應為 ={10,20,30;40,50,60;70,80,90}.計算儲存格範圍內的字元數
下列範例示範如何計算儲存格範圍中的字元數。 這包括空格。
=SUM (LEN (C9：C13) )
在此情況下， LEN 函數 會傳回範圍中每一個儲存格中每個文字字串的長度。 然後 SUM 函數將這些值相加，並顯示結果 (66) 。 如果您想要取得平均字元數，您可以使用：
=AVERAGE (LEN (C9：C13) )範圍 C9：C13 中最長儲存格的內容
=INDEX (C9：C13，MATCH (MAX (LEN (C9：C13) ) ，LEN (C9：C13) ,0) ,1)
此公式只有在資料範圍包含單欄儲存格時才能順利運作。
讓我們更仔細看一下公式，從內元素開始往外分析。 LEN 函數會傳回儲存格範圍 D2：D6 中每個項目的長度。 MAX 函數會計算這些項目中最大的值，也就是對應至儲存格 D3 中最長的文字字串。
下面的情形就比較複雜了。 MATCH 函數會計算位移 (包含最長文字字串之儲存格的相對位置) 。 若要執行這項作業，必須有以下三個引數：查閱值、查閱陣列、比對方式。 MATCH 函數會在查閱陣列中搜尋指定的查閱值。 在本範例中，查閱值是最長的文字字串：
最大 (LEN (C9：C13)
該字串存放於以下陣列中：
LEN (C9：C13)
這個例子中的比對類型引數是 0。 比對類型可以是 1、0 或 -1 值。- 1 - 傳回小於或等於查閱值的最大值
- 0 - 傳回完全等於查閱值的第一個值
- -1 - 傳回大於或等於指定查閱值的最小值
- 如果您省略比對方式，Excel 會假設為 1。
最後， INDEX 函數 會接受下列引數：陣列，以及該陣列內的列和欄號。 儲存格範圍 C9：C13 提供陣列，MATCH 函數提供儲存格位址，最後一個引數 (1) 指定值來自陣列中的第一欄。
如果您想要取得最小文字字串的內容，您可以將上述範例中的 MAX 取代為 MIN。找出範圍中 n 個最小的數值
此範例顯示如何尋找儲存格範圍中的三個最小值，其中儲存格 B9：B18 中的範例資料陣列是使用： =INT (RANDARRAY (10,1) *100) 建立的。 請注意，RANDARRAY 是一個可變更的函數，因此每次 Excel 計算時，您會得到一組新的亂數。
Enter =SMALL (B9#，SEQUENCE (D9) ， =SMALL (B9：B18，{1;2;3})
此公式會使用陣列常數來評估 SMALL 函數 三次，並傳回儲存格 B9：B18 中所含陣列中最小 3 個成員，其中 3 是儲存格 D9 中的變量值。 若要尋找更多值，您可以增加 SEQUENCE 函數中的值，或將更多引數新增至常數。 亦可使用其他函數搭配此公式，例如 SUM 或 AVERAGE。 例如：
=SUM (SMALL (B9#，SEQUENCE (D9) )
=AVERAGE (SMALL (B9#，SEQUENCE (D9) )找出範圍中 n 個最大的數值
若要尋找範圍中的最大值，您可以將 SMALL 函數取代為 LARGE 函數。 除此之外，也可如下列範例般，使用 ROW 和 INDIRECT 函數。
輸入 =LARGE (B9#，ROW (INDIRECT (“1：3”) ) ) ，或 =LARGE (B9：B18，ROW (INDIRECT (“1：3”) ) )
此時，如果對 ROW 和 INDIRECT 函數稍有了解，可能會有幫助。 您可以使用 ROW 函數來建立連續整數的陣列。 例如，選取空白並輸入：
=ROW(1:10)
公式隨即建立含 10 個連續整數的欄。 若要查看潛在的問題，請在含陣列公式的範圍上方 (亦即第 1 列上方) 插入列。 Excel 會調整列參照，而公式現在會產生 2 到 11 的整數。 若要修正該問題，可在公式中加入 INDIRECT 函數：
=ROW(INDIRECT("1:10"))
INDIRECT 函數使用文字字串作為其引數 (因此範圍 1：10 會以引號括住) 。 您插入列或移動陣列公式時，Excel 並不會調整文字值。 因此，ROW 函數永遠都會產生您所要的整數陣列。 您也可以輕鬆使用 SEQUENCE：
=SEQUENCE (10)
讓我們檢視一下您先前使用的公式 — =LARGE (B9#，ROW (INDIRECT (“1：3”) ) ) — 從內括弧開始，向外延伸： INDIRECT 函數會傳回一組文字值，在此案例中為值 1 到 3。 ROW 函數接著會產生一個包含三個儲存格的欄陣列。 LARGE 函數會使用儲存格範圍 B9：B18 中的值，並會評估三次，其中 ROW 函數傳回的每個參照一次。 如果要尋找更多值，請將較大的儲存格範圍新增至 INDIRECT 函數。 最後，如同 SMALL 範例一樣，您可以將此公式與其他函數搭配使用，例如 SUM 和 AVERAGE。
處理錯誤
-
加總含錯誤值的範圍
當您嘗試加總包含錯誤值的範圍時，Excel 中的 SUM 函數無法運作，例如 #VALUE！ 或 #N/A。 這個範例示範如何加總名為 Data 但包含錯誤的範圍中的值：
-
=SUM(IF(ISERROR(資料),"",資料))
此公式會建立新陣列，其中包含減去任何錯誤值的原始值。 ISERROR 函數會從內部函數開始往外分析，搜尋儲存格範圍 (資料) 中的錯誤。 IF 函數會在您指定之條件的計算結果為 TRUE 時傳回特定的值，並在結果為 FALSE 時傳回另一個值。 在此例中，它會對所有錯誤值傳回空字串 ("")，這是因為計算結果為 TRUE；而且還會傳回範圍 (資料) 的其餘值，這是因為計算結果為 FALSE，表示當中不包含錯誤值。 SUM 函數接著會計算篩選陣列的總計。 -
計算範圍內錯誤值的數目
此範例類似於上一個公式，但此公式會傳回名為 Data 的範圍中的錯誤值數目，而不是將其篩選掉：
=SUM(IF(ISERROR(資料),1,0))
此公式會建立陣列，其中包含值為 1 的含錯誤儲存格，以及值為 0 的不含錯誤儲存格。 您可以簡化公式，並且移除 IF 函數的第三個引數來得到相同的結果，如下所示：
=SUM (IF (ISERROR (資料) ,1) )
如果不指定引數，只要儲存格不包含錯誤值，IF 函數就會傳回 FALSE。 您可以更進一步將公式簡化如下：
=SUM(IF(ISERROR(資料)*1))
此公式運作無誤，因為 TRUE*1=1 而 FALSE*1=0。
根據條件加總數值
您可能必須根據條件加總數值。
例如，此陣列公式只會加總名為 Sales 的範圍中的正整數，其代表上例中的儲存格 E9：E24：
=SUM (IF (Sales>0，Sales) )
IF 函數會建立正值和假值的陣列。 SUM 函數基本上會忽略偽值，原因在於 0+0=0。 您在此公式中使用的儲存格範圍可以包含任何數目的列和欄。
您也可以加總符合多個條件的數值。 例如，此陣列公式會計算大於 0 且小於 2500 的 值：
=SUM ( (Sales>0) * (Sales<2500) * (Sales) )
請牢記在心，如果範圍內包含一個或多個非數值儲存格，那麼此公式就會傳回錯誤。
您也可以建立一些只使用一種 OR 條件的陣列公式。 例如，您可以加總大於 0 或 小於 2500 的值：
=SUM (IF ( (Sales>0) + (Sales<2500) ，Sales) )
您不能直接在陣列公式中使用 AND 與 OR 函數，因為這些函數會傳回單一結果，不是 TRUE 就是 FALSE，而陣列函數需要的是結果陣列。 您可以使用先前公式中出現的邏輯，來解決這項問題。 換句話說，您可以執行數學運算，例如對符合 OR 或 AND 條件的值進行加法或乘法。
以下範例為您示範如何在必須取得範圍內的平均值時，將範圍內的零移除。 公式會使用名為「銷售」的資料範圍：
=AVERAGE (IF (Sales<>0，Sales) )
IF 函數會建立不等於 0 的值陣列，然後將這些值傳遞給 AVERAGE 函數。
計算兩個儲存格範圍之間差異的數目
此陣列公式會針對「我的資料」與「你的資料」這兩個儲存格範圍內的數值進行比較，然後傳回這兩個範圍之間的差異數目。 如果兩個範圍的內容完全相同，公式會傳回 0。 若要使用此公式，儲存格範圍必須具有相同的大小和維度。 例如，如果 MyData 是 3 列乘以 5 欄的範圍，則 YourData 也必須是 3 列乘以 5 欄：
=SUM (IF (MyData=YourData，0,1) )
此公式會建立一個新陣列，而且該陣列的大小跟您要比較之範圍相同。 IF 函數會用 0 值和 1 值填滿陣列 (0 代表比對不相符，1 代表完全相同的儲存格)。 SUM 函數接著會傳回陣列中數值的總和。
公式可簡化如下：
=SUM (1* (MyData<>YourData) )
此公式就像是可計算範圍內有錯誤值的公式，之所以可以順利運作，就是因為 TRUE*1=1 而 FALSE*1=0。
以下陣列公式會傳回「資料」單欄範圍內最大值的列號：
=MIN(IF(資料=MAX(資料),ROW(資料),""))
IF 函數會建立新陣列，該陣列對應到名為「資料」的範圍。 若對應的儲存格包含範圍內的最大值，則該陣列會包含列號。 否則，該陣列會包含空字串 ("")。 MIN 函數會使用新陣列作為其第二個引數，並傳回最小值，該值對應的是「資料」中最大值的列號。 如果名為「資料」的範圍包含相同的最大值，則公式會傳回第一個值的列。
如果您要傳回最大數值的實際儲存格位址，請使用以下公式：
=ADDRESS(MIN(IF(資料=MAX(資料),ROW(資料),"")),COLUMN(資料))
您可以在 [資料集之間的差異] 工作表的範例活頁簿中找到類似的範例。
![含公式的 [編輯名稱] 對話方塊](media/xlarray8.gif)
鳴謝
本文有部分內容根據 Colin Wilcox 撰寫的一系列 Excel 進階使用者專欄，並改編自前 Excel MVP John Walkenbach 所著的《Excel 2002 公式》的第 14 章和第 15 章。
需要更多協助嗎？
您隨時都可以詢問 Excel 技術社群 中的專家，或在 社群中取得支援。