陣列公式是一種可以對陣列中一個或多個項目進行多次計算的公式。 你可以把陣列想像成一列或一列的值，或是數列和數值的組合。 陣列公式可以回傳多個結果，或是單一結果。
自 2018 年 9 月的 Microsoft 365 更新起，任何能回傳多個結果的公式都會自動將結果洩漏到相鄰的儲存格。 這種行為改變也伴隨著幾個新的 動態陣列函數。 動態陣列公式，不論是使用現有函式還是動態陣列函式，只要輸入到單一儲存格，然後按 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 儲存格並按下刪除。 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} 或 ={「一月」、「二月」、「三月」}
如果是使用逗號來分隔項目，便會建立水平陣列 (列)。 如果是使用分號來分隔項目，便會建立垂直陣列 (欄)。 要建立二維陣列，你用逗號分隔每列的項目，並用分號分隔每列。
下列程序可讓您稍加練習如何建立水平、垂直及二維常數。 我們將展示使用 SEQUENCE 函式 自動產生陣列常數，以及手動輸入陣列常數的範例。
-
建立水平常數
使用先前範例的活頁簿，或建立新的活頁簿。 選擇任一空格子並輸入 =SEQUENCE (1.5) 。 SEQUENCE 函式建立一個 1 列乘 5 欄的陣列，與 ={1,2,3,4,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*序列 (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。
為了避免使用儲存的陣列並將操作完全保留在記憶體中，你可以用另一個陣列常數替換：
= (數列總和 (1,5,3,1) *序列 (1,5) ) ，或 =和 ({3,4,5,6,7}*) {1,2,3,4,5}
你可以用在陣列常數中的元素
- 陣列常數可以包含數字、文字、邏輯值 (如真與假) ，以及錯誤值如 #N/A。 你可以使用整數、十進位和科學格式的數字。 如果你包含文字，請用引號 (「text」) 包圍。
- 矩陣常數不能包含其他的陣列、公式或函數。 換句話說，只能包含那些以逗點或分號分隔的文字或數字。 當您輸入 {1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)} 這類的公式時，Excel 會顯示警告訊息。 此外，數值不能包含百分比符號、貨幣符號、逗號或括弧。
為矩陣常數命名
使用陣列常數的最佳方法之一是命名它們。 已命名的常數使用起來更加容易，而且可以隱藏一些陣列公式的複雜性，不讓其他人看見。 若要為矩陣常數命名並用在公式中，請執行下列步驟：
前往 公式>定義名稱>定義名稱。 在 姓名 框中輸入 Quarter1。 在 [參照到] 方塊中，輸入以下常數 (記得要手動輸入大括弧)：
={"一月","二月","三月"}
對話框現在應該是這樣：
點選 確定，然後選擇任意一列有三個空白格子，輸入 =Quarter1。
結果如下顯示：
如果你想讓結果垂直而非水平溢出，可以用 =TRANSPOSE (Quarter1) 。
如果你想顯示12個月的清單，就像建立財務報表時用的那樣，你可以用SEQUENCE函數根據當年來列出。 這個函式的好處是，雖然只顯示月份，但背後有有效日期，可以用來做其他計算。 你可以在範例工作簿中的 命名陣列常數 和 快速範例資料集 工作表中找到這些範例。
=文字 (日期 (年 (今天 () ) ，序列 (1,12) ,1) ，「嗯」)
此系統使用 DATE 函數 建立基於當前年份的日期，SEQUENCE 建立從 1 到 12 的陣列常數，涵蓋 1 至 12 的 1 月至十二月，然後 TEXT 函數 將顯示格式轉換為 1 月、二月、三月等 (「mmm」) 。 如果你想顯示完整的月份名稱，例如一月，就用「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 (序列 (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 函數會將各列轉換成一系列欄。
讓基本陣列公式開始運作
本節內容提供基本陣列函數的範例。
從現有值建立陣列
以下範例說明如何利用陣列公式從現有陣列建立新陣列。
輸入 =序列 (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}.計算儲存格範圍內的字元數
以下範例示範如何計算一組格子中的字元數。 這也包括空間。
= ( (C9：C13) )
在這種情況下， LEN 函式 會回傳該區間每個儲存格中每個文字字串的長度。 SUM 函數接著將這些數值相加，並顯示 66) (結果。 如果你想取得平均字數，可以使用：
=平均 ( (C9：C13) )範圍 C9：C13 中最長細胞的內容物
=指數 (C9：C13，比賽 (最大 (LEN (C9：C13) ) ，LEN (C9：C13) ,0) ,1)
此公式只有在資料範圍包含單欄儲存格時才能順利運作。
讓我們更仔細看一下公式，從內元素開始往外分析。 LEN 函數會回傳格子範圍 D2：D6 中每個項目的長度。 MAX 函數計算出這些項目中最大的值，對應於最長的文字串，位於 D3 儲存格中。
下面的情形就比較複雜了。 MATCH 函數計算包含最長文字串的儲存格相對位置) (偏移量。 若要執行這項作業，必須有以下三個引數：查閱值、查閱陣列、比對方式。 MATCH 函數會在查閱陣列中搜尋指定的查閱值。 在本範例中，查閱值是最長的文字字串：
麥克斯· (倫 (C9：C13)
該字串存放於以下陣列中：
LEN (C9：C13)
此時匹配型態參數為 0。 匹配類型可以是 1、0 或 -1。- 1 - 回傳小於或等於查詢 val 的最大值
- 0 - 回傳第一個值，恰好等於查詢值
- -1 - 回傳大於或等於指定查詢值的最小值
- 如果您省略比對方式，Excel 會假設為 1。
最後， INDEX 函式 會取這些參數：一個陣列，以及該陣列中的列號和欄號。 儲存格範圍 C9：C13 提供陣列，MATCH 函式提供儲存格位址，最後一個參數 (1) 指定值來自陣列的第一欄。
如果你想取得最小文字串的內容，你可以把上述範例中的 MAX 替換成 MIN。找出範圍中 n 個最小的數值
此範例展示了如何在一個儲存格範圍內找到三個最小的值，其中在 B9：B18 儲存格中建立的樣本資料陣列為： =INT (RANDARRAY (10,1) *100) 。 請注意，RANDARRAY 是一個揮失性函數，所以每次 Excel 計算時你會得到一組新的隨機數。
輸入 =SMALL (B9#，SEQUENCE (D9) ， =SMALL (B9：B18，{1;2;3})
這個公式使用陣列常數來計算 SMALL 函數 三次，並回傳陣列中包含在 B9：B18 儲存格中最小的 3 個成員，其中 3 是 D9 儲存格中的變數值。 要找到更多值，你可以增加 SEQUENCE 函式的值，或在常數中加入更多參數。 亦可使用其他函數搭配此公式，例如 SUM 或 AVERAGE。 例如：
= (小 (B9#，序列 (D9) )
=平均 (小 (B9#，序列 (D9) )找出範圍中 n 個最大的數值
要找出範圍內最大的值，你可以把 SMALL 函數替換成 LARGE 函數。 除此之外，也可如下列範例般，使用 ROW 和 INDIRECT 函數。
輸入 =LARGE (B9#，ROW (間接 (“1：3”) ) ) ，或 =LARGE (B9：B18，ROW (間接 (“1：3”) ) )
此時，如果對 ROW 和 INDIRECT 函數稍有了解，可能會有幫助。 您可以使用 ROW 函數來建立連續整數的陣列。 例如，選擇一個空位並輸入：
=ROW(1:10)
公式隨即建立含 10 個連續整數的欄。 若要查看潛在的問題，請在含陣列公式的範圍上方 (亦即第 1 列上方) 插入列。 Excel 會調整列參考，公式現在會產生從 2 到 11 的整數。 若要修正該問題，可在公式中加入 INDIRECT 函數：
=ROW(INDIRECT("1:10"))
INDIRECT 函式使用文字字串作為參數 (，因此 1：10 範圍被引號包圍) 。 您插入列或移動陣列公式時，Excel 並不會調整文字值。 因此，ROW 函數永遠都會產生您所要的整數陣列。 你也可以用 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 函數的第三個引數來得到相同的結果，如下所示：
= (若 (ISERROR (資料) ,1) )
如果不指定引數，只要儲存格不包含錯誤值，IF 函數就會傳回 FALSE。 您可以更進一步將公式簡化如下：
=SUM(IF(ISERROR(資料)*1))
此公式運作無誤，因為 TRUE*1=1 而 FALSE*1=0。
根據條件加總數值
您可能必須根據條件加總數值。
例如，這個陣列公式僅將名為 Salesforce 的正整數相加，該範圍在上述範例中代表 E9：E24 的儲存格：
=總和 (若 (銷售>額 0，銷售額) )
IF 函數會產生一個正值與假值陣列。 SUM 函數基本上會忽略偽值，原因在於 0+0=0。 您在此公式中使用的儲存格範圍可以包含任何數目的列和欄。
您也可以加總符合多個條件的數值。 例如，這個陣列公式計算出大於 0 且 小於 2500 的值：
=總和 ( (銷售額>0) * (銷售<額 2500) * (銷售額) )
請牢記在心，如果範圍內包含一個或多個非數值儲存格，那麼此公式就會傳回錯誤。
您也可以建立一些只使用一種 OR 條件的陣列公式。 例如，你可以加總大於 0 或 小於 2500 的值：
=總和 (若 ( (銷售額>0) + (銷售額<2500) ，銷售額) )
您不能直接在陣列公式中使用 AND 與 OR 函數，因為這些函數會傳回單一結果，不是 TRUE 就是 FALSE，而陣列函數需要的是結果陣列。 您可以使用先前公式中出現的邏輯，來解決這項問題。 換句話說，你要對符合 OR 或 AND 條件的值進行數學運算，例如加法或乘法。
以下範例為您示範如何在必須取得範圍內的平均值時，將範圍內的零移除。 公式會使用名為「銷售」的資料範圍：
=平均 (若 (銷售<>0，銷售) )
IF 函數會建立不等於 0 的值陣列，然後將這些值傳遞給 AVERAGE 函數。
計算兩個儲存格範圍之間差異的數目
此陣列公式會針對「我的資料」與「你的資料」這兩個儲存格範圍內的數值進行比較，然後傳回這兩個範圍之間的差異數目。 如果兩個範圍的內容完全相同，公式會傳回 0。 使用此公式時，格子範圍必須大小相同且尺寸相同。 例如，如果 MyData 是 3 列 5 欄的範圍，YourData 也必須是 3 列 5 欄：
=SUM (若 (MyData = YourData，0,1) )
此公式會建立一個新陣列，而且該陣列的大小跟您要比較之範圍相同。 IF 函數會用 0 值和 1 值填滿陣列 (0 代表比對不相符，1 代表完全相同的儲存格)。 SUM 函數接著會傳回陣列中數值的總和。
公式可簡化如下：
=總和 (1* (我的資料<>你的資料) )
此公式就像是可計算範圍內有錯誤值的公式，之所以可以順利運作，就是因為 TRUE*1=1 而 FALSE*1=0。
以下陣列公式會傳回「資料」單欄範圍內最大值的列號：
=MIN(IF(資料=MAX(資料),ROW(資料),""))
IF 函數會建立新陣列，該陣列對應到名為「資料」的範圍。 若對應的儲存格包含範圍內的最大值，則該陣列會包含列號。 否則，該陣列會包含空字串 ("")。 MIN 函數會使用新陣列作為其第二個引數，並傳回最小值，該值對應的是「資料」中最大值的列號。 如果名為「資料」的範圍包含相同的最大值，則公式會傳回第一個值的列。
如果您要傳回最大數值的實際儲存格位址，請使用以下公式：
=ADDRESS(MIN(IF(資料=MAX(資料),ROW(資料),"")),COLUMN(資料))
你可以在「 資料集差異 」工作紙的範例工作手冊中找到類似的範例。
![含公式的 [編輯名稱] 對話方塊](media/xlarray8.gif)
致謝
本文部分內容基於 Colin Wilcox 撰寫的一系列 Excel Power User 專欄，並改編自 John Walkenbach 所著的《Excel 2002 公式》第14及15章，他曾是 Excel MVP。
需要更多協助嗎？
你隨時可以向 Excel 技術社群 的專家詢問，或在 社群中獲得支援。