陣列公式的規則和範例

本練習會示範如何使用多儲存格及單儲存格陣列公式來計算一組銷售數字。 第一組步驟使用多儲存格公式來計算一組小計。 第二組步驟則使用單儲存格公式來計算總計。

• 多儲存格陣列公式

  

• 在這裡，我們在 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*G1 2，F13*G13，F14*G14，F15*G15，F16*G16，F17*G17，F18*G18，F19*G19) 。

  • 溢出    動態數位公式會自動溢出到輸出範圍。 如果您的源數據位於 Excel 表格中，則動態數位公式會在您新增或移除資料時自動重設大小。

  • #SPILL！錯誤    動態陣列匯入 了 #SPILL！ 錯誤，這表示預期的溢出範圍因某種原因而遭到封鎖。 當您解決封鎖時，公式會自動溢出。

矩陣常數是陣列公式的一項元件。 您可以輸入項目清單來建立矩陣常數，然後手動輸入大括弧 ({ }) 括住清單，如下所示：

={1,2,3,4,5} 或 ={“January”，“二月”，“三月”}

如果是使用逗號來分隔項目，便會建立水平陣列 (列)。 如果是使用分號來分隔項目，便會建立垂直陣列 (欄)。 若要建立二維陣列，請以逗號分隔每一列中的專案，並以分號分隔每一列。

下列程序可讓您稍加練習如何建立水平、垂直及二維常數。 我們會顯示使用 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*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” (Jan、Feb、Mar 等 ) 。 如果您想要顯示完整月份名稱，例如「一月」，則會使用「mmmm」。

當您使用具名的常數做為數位公式時，請記得輸入等號，如 =Quarter1，而不只是第 1 季。 若未輸入等號，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 函數會在查閱陣列中搜尋指定的查閱值。 在本範例中，查閱值是最長的文字字串：

  MAX (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 計算時，您都會得到一組新的隨機數。

  

  輸入 =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 個最大的數值

  若要尋找範圍內最大的值，您可以使用 LARGE 函數取代 SMALL 函數。 除此之外，也可如下列範例般，使用 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。

• 加總含錯誤值的範圍

  當您嘗試加總包含錯誤值的範圍，例如 #VALUE！ 或 #N/A。 此範例示範如何加總名為「資料」範圍中包含錯誤的值：

  

• =SUM(IF(ISERROR(資料),"",資料))

  此公式會建立新陣列，其中包含減去任何錯誤值的原始值。 ISERROR 函數會從內部函數開始往外分析，搜尋儲存格範圍 (資料) 中的錯誤。 IF 函數會在您指定之條件的計算結果為 TRUE 時傳回特定的值，並在結果為 FALSE 時傳回另一個值。 在此例中，它會對所有錯誤值傳回空字串 ("")，這是因為計算結果為 TRUE；而且還會傳回範圍 (資料) 的其餘值，這是因為計算結果為 FALSE，表示當中不包含錯誤值。 SUM 函數接著會計算篩選陣列的總計。

• 計算範圍內錯誤值的數目

  此範例與上一個公式類似，但它會傳回名為「數據」範圍內的錯誤值數目，而不是篩選掉：

  =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(銷售>0,銷售))

IF 函數會建立一組正負值。 SUM 函數基本上會忽略偽值，原因在於 0+0=0。 您在此公式中使用的儲存格範圍可以包含任何數目的列和欄。

您也可以加總符合多個條件的數值。 例如，此陣列公式會計算大於 0 AND 小於 2500 的值：

=SUM ( (Sales>0) * (Sales<2500) * (Sales) )

請牢記在心，如果範圍內包含一個或多個非數值儲存格，那麼此公式就會傳回錯誤。

您也可以建立一些只使用一種 OR 條件的陣列公式。 例如，您可以加總大於 0 OR 小於 2500 的值：

=SUM (IF ( (Sales>0) + (Sales<2500) ，銷售) )

您不能直接在陣列公式中使用 AND 與 OR 函數，因為這些函數會傳回單一結果，不是 TRUE 就是 FALSE，而陣列函數需要的是結果陣列。 您可以使用先前公式中出現的邏輯，來解決這項問題。 換句話說，您會執行數學運算，例如對符合 OR 或 AND 條件的值執行加法或乘法。

以下範例為您示範如何在必須取得範圍內的平均值時，將範圍內的零移除。 公式會使用名為「銷售」的資料範圍：

=AVERAGE(IF(銷售<>0,銷售))

IF 函數會建立不等於 0 的值陣列，然後將這些值傳遞給 AVERAGE 函數。

此陣列公式會針對「我的資料」與「你的資料」這兩個儲存格範圍內的數值進行比較，然後傳回這兩個範圍之間的差異數目。 如果兩個範圍的內容完全相同，公式會傳回 0。 若要使用此公式，單元格範圍必須具有相同的大小和相同的維度。 例如，如果 MyData 的範圍是 3 列 5 欄，您的數據也必須是 3 列 5 欄：

=SUM(IF(我的資料=你的資料,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(資料))

您可以在範例活頁簿中的數據集工作表 差異 中找到類似的範例。

本練習會示範如何使用多儲存格及單儲存格陣列公式來計算一組銷售數字。 第一組步驟使用多儲存格公式來計算一組小計。 第二組步驟則使用單儲存格公式來計算總計。

• 多儲存格陣列公式

複製下方的整個表格，並貼到空白工作表中的儲存格 A1。

銷售 人員	汽車 類型	銷售編號	單 價	總 銷售額
孫哲翰	四門轎車	5	33000
	雙門轎跑車	4	37000
李莉華	四門轎車	6	24000
	雙門轎跑車	8	21000
羅書成	四門轎車	3	29000
	雙門轎跑車	1	31000
盧珮佳	四門轎車	9	24000
	雙門轎跑車	5	37000
吳又倫	四門轎車	6	33000
	雙門轎跑車	8	31000
公式 (總計)			總計
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. 若要查看每位銷售人員的雙車和雙車總銷售額，請選取 E2：E11 單元格，輸入 公式 =C2：C11*D2：D11，然後按 Ctrl+Shift+Enter。

2. 若要查看所有銷售額的總計，請選取 F11 單元格，輸入公式 =SUM (C2：C11*D2：D11) ，然後按 Ctrl+Shift+Enter。

當您按 Ctrl+Shift+Enter 時，Excel 會以括住大括號 ({ }) 並在所選範圍的每個單元格中插入公式實例。 這項作業的執行速度很快，因此您在 E 欄中看到的是每位銷售人員的各車種銷售量。 如果選取 E2，然後選取 E3、E4 等，都會看到相同的公式：{=C2:C11*D2:D11}。 

• 建立單儲存格陣列公式

在活頁簿的 D13 單元格中，輸入下列公式，然後按 Ctrl+Shift+Enter：

=SUM(C2:C11*D2:D11)

在此情況下，Excel 會將數位中的值 (儲存格範圍 C2 到 D11) 相乘，然後使用 SUM 函數將總計相加。 結果得出總銷售額 $1,590,000 的總計。 此範例顯示這類公式的強大功能。 例如，假設您有1,000列的數據。 您可以在單一單元格中建立數位公式，而不是將公式向下拖曳到 1,000 列，藉此加總部分或全部的數據。

此外，請注意，單元格 D13 中的單一單元格公式完全獨立於儲存格 E2 到 E11) 中的公式 (多單元格公式。 這也是使用陣列公式的另一項優點 ——彈性。 您可以變更 E 欄中的公式或刪除該欄，而不會影響 D13 中的公式。

陣列公式還具備下列優點：

• 一致性    按一下 E2 以下的任何儲存格，都會看到相同的公式。 這種一致性有助於確保提升正確性。

• 安全性    您無法覆寫多儲存格陣列公式的元件。 例如，按兩下 E3 單元格，然後按 Delete。 您必須選取整個範圍的儲存格 (E2 至 E11) 並變更整個陣列的公式，或將陣列保持現狀。 您必須按 Ctrl+Shift+Enter ，以確認公式有任何變更，這是額外的安全措施。

• 較小的檔案大小    您通常可以使用單一陣列公式，而非數個中間公式。 例如，活頁簿使用一個數位公式來計算 E 欄中的結果。如果您曾使用 =C2*D2、C3*D3、C4*D4...) 等標準公式 (，您會使用 11 種不同的公式來計算相同的結果。

一般而言，陣列公式使用的是標準公式語法， 開頭都是等號 (=)，而且您可以在陣列公式中使用大多數的內建 Excel 函數。 主要的差別在於，使用陣列公式時，要按 Ctrl+Shift+Enter 才能輸入公式。 主要差異在於，當您使用數位公式時，請按 Ctrl+Shift+Enter 來輸入公式。 如果您是手動輸入大括弧，則公式會轉換成文字字串，無法運作。

數位函數是建立複雜公式的有效方法。 =SUM( C2:C11*D2:D11) 這個陣列公式等同於以下公式：=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11)。

使用多儲存格公式時，也請記住以下要點：

• 您必須在輸入公式「之前」，選取要放置結果的儲存格範圍。 您是在選取 E2 至 E11 儲存格建立多儲存格陣列公式時進行此動作。

• 您不能變更陣列公式中個別儲存格的內容。 若要嘗試這項操作，請在活頁簿中選取 E3 儲存格，然後按 Delete 鍵。 Excel 會顯示一則訊息，告訴您無法變更陣列的任何部分。

• 您可以移動或刪除整個陣列公式，但不可移動或刪除陣列公式的某個部分。 換句話說，若要縮減陣列公式，必須先刪除現有公式，然後從頭開始。

• 若要刪除數位公式，請選取整個公式範圍 (例如 E2：E11) ，然後按 Delete。

• 您無法將空白儲存格插入，或刪除多儲存格陣列公式中的儲存格。

有時，您可能需要展開陣列公式。 選取現有數位範圍中的第一個儲存格，然後繼續，直到您選取要將公式延伸至的整個範圍為止。 按 F2 編輯公式，然後按 CTRL+SHIFT+ENTER 以在您調整公式範圍后確認公式。 關鍵在於從陣列中的左上角單元格開始選取整個範圍。 左上角的儲存格是可進行編輯的儲存格。

陣列公式很好用，但也有一些缺點：

• 您可能偶爾會忘記按 Ctrl+Shift+Enter。 即使是最有經驗的 Excel 使用者也可能發生這種情況。 凡是輸入或編輯陣列公式時，請記得按這個組合鍵。

• 活頁簿的其他使用者可能不了解您的公式。 實際上，工作表通常不會說明數位公式。 因此，如果其他人需要修改您的活頁簿，您應該避免使用數位公式，或確定這些人員知道任何數位公式，並瞭解如何在需要時變更數位公式。

• 視電腦的處理速度和記憶體而定，大型陣列公式可能會讓計算作業變得緩慢。

矩陣常數是陣列公式的一項元件。 您可以輸入項目清單來建立矩陣常數，然後手動輸入大括弧 ({ }) 括住清單，如下所示：

={1,2,3,4,5}

現在，您已經知道當您建立數位公式時，必須按 Ctrl+Shift+Enter 。 由於常數陣列是陣列公式的一項元件，因此您必須手動輸入大括弧來括住這些常數。 然後使用 Ctrl+Shift+Enter 輸入整個公式。

如果是使用逗號來分隔項目，便會建立水平陣列 (列)。 如果是使用分號來分隔項目，便會建立垂直陣列 (欄)。 若要建立二維陣列，必須使用逗號來分隔每列中的項目，並使用分號來分隔各列。

這是單一列中的陣列：{1,2,3,4}。 此為單欄中的陣列：{1;2;3;4}。 此為兩列和四欄的陣列：{1,2,3,4;5,6,7,8}。 在兩列的陣列中，第一列為 1、2、3 及 4，第二列為 5、6、7 及 8。 單一分號放在 4 與 5 之間，分隔了這兩列。

矩陣常數跟陣列公式一樣，可搭配使用 Excel 中所提供的大多數內建函數。 下列各節說明如何建立各種常數，以及這些常數要如何搭配 Excel 中的函數使用。

下列程序可讓您稍加練習如何建立水平、垂直及二維常數。

建立水平常數

1. 在空白工作表中，選取儲存格 A1 到 E1。

2. 在數據編輯列中，輸入下列公式，然後按 Ctrl+Shift+Enter：

   ={1,2,3,4,5}

   在此情況下，您 應該 輸入左大括弧 ({ }) ，Excel 會為您新增第二組。

   畫面會顯示下列結果。

   

建立垂直常數

1. 在活頁簿中，選取由五個儲存格組成的一欄。

2. 在數據編輯列中，輸入下列公式，然後按 Ctrl+Shift+Enter：

   ={1;2;3;4;5}

   畫面會顯示下列結果。

   

建立二維常數

1. 在活頁簿中，選取四欄寬三列高的儲存格區塊。

2. 在數據編輯列中，輸入下列公式，然後按 Ctrl+Shift+Enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}

   您會看到下列結果：

   

在公式中使用常數

以下是使用常數的簡單範例：

1. 在範例活頁簿中，建立一張新的工作表。

2. 在 A1 儲存格中輸入 3，然後在 B1 中輸入 4，C1 中輸入 5，D1 中輸入 6，E1 中輸入 7。

3. 在儲存格 A3 中，輸入下列公式，然後按 Ctrl+Shift+Enter：

   =SUM(A1:E1*{1,2,3,4,5})

   請注意，Excel 會用另一組大括弧括住常數，因為您將它輸入為陣列公式。

   

   值 85 出現在 A3 儲存格中。

下一節說明公式如何運作。

您剛使用的公式包含數個部分。

1. 函數

2. 儲存的陣列

3. 運算子

4. 矩陣常數

括弧之內的最後一個元素就是矩陣常數：{1,2,3,4,5}。 別忘了！ 另請記住，在數位公式中新增常數之後，請按 Ctrl+Shift+Enter 來輸入公式。

由於 Excel 會先對用括弧括住的運算式執行運算，因此接下來要計算的兩個元素是活頁簿中所儲存的值 (A1:E1) 和運算子。 此時，公式會將已儲存陣列中的值乘以常數中的對應值。 其結果等於：

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

最後，SUM 函數會將值相加，而總和 85 會顯示在 A3 儲存格中。

若要避免使用已儲存的陣列，而只是將運算整個保存在記憶體中，請用另一個矩陣常數來取代已儲存的陣列：

=SUM({3,4,5,6,7}*{1,2,3,4,5})

若要嘗試此方法，請複製函數，選取活頁簿中的空白單元格，將公式貼到數據編輯列，然後按 Ctrl+Shift+Enter。 您會看到與先前練習中使用陣列公式所得的相同計算結果。

=SUM(A1:E1*{1,2,3,4,5})

矩陣常數可以包含數字、文字、邏輯值 (例如 TRUE 和 FALSE) 及錯誤值 (如 #N/A)。 您可以使用整數、小數和科學記號。 如果要包含文字，則需要用引號 (") 括住文字。

矩陣常數不能包含其他的陣列、公式或函數。 換句話說，只能包含那些以逗點或分號分隔的文字或數字。 當您輸入 {1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)} 這類的公式時，Excel 會顯示警告訊息。 此外，數值不能包含百分比符號、貨幣符號、逗號或括弧。

使用矩陣常數的最佳方式之一就是為它們命名。 已命名的常數使用起來更加容易，而且可以隱藏一些陣列公式的複雜性，不讓其他人看見。 若要為矩陣常數命名並用在公式中，請執行下列步驟：

1. 在 [公式] 索引標籤上，按一下 [已定義之名稱] 群組中的 [定義名稱]。
   [ 定義名稱 ] 對話框隨即出現。

2. 在 [名稱] 方塊中輸入「第一季」。

3. 在 [參照到] 方塊中，輸入以下常數 (記得要手動輸入大括弧)：

   ={"一月","二月","三月"}

   對話方塊的內容現在看起來如下：

   

4. 按一下 [確定]，然後選取由三個空白儲存格組成的一列。

5. 輸入下列公式，然後按 Ctrl+Shift+Enter。

   =第一季

   畫面會顯示下列結果。

   

使用已命名的常數作為陣列公式時，記得輸入等號。 若未輸入等號，Excel 會將陣列解譯為文字字串，而公式會無法如預期般運作。 最後，請記住，文字與數字可以混用。

矩陣常數無法運作時，請查看是否有下列問題：

• 有些元素可能沒有用適當的字元分隔。 如果您省略逗號或分號，或將一個放在錯誤的位置，矩陣常數可能無法正確建立，或您可能會看到警告訊息。

• 您已選取的儲存格範圍可能與常數中的元素數目不符。 例如，如果選取了由六個儲存格組成的一欄，並使用五儲存格常數，那麼空白儲存格中就會顯示 #N/A 錯誤值。 相反地，如果選取的儲存格太少，Excel 會省略那些沒有對應儲存格的值。

以下範例提出多種方式，為您示範如何在陣列公式中使用矩陣常數。 有些範例使用 TRANSPOSE 函 數將列轉換成欄，反之亦然。

以陣列中各項目相乘

1. 建立新的工作表，然後選取四欄寬三列高的空白儲存格區塊。

2. 輸入下列公式，然後按 Ctrl+Shift+Enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}*2

求陣列中項目的平方值

1. 選取四欄寬三列高的空白儲存格區塊。

2. 輸入下列陣列公式，然後按 Ctrl+Shift+Enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

   另外也可以輸入此陣列公式，其中使用脫字符號運算子 (^)：

   ={1,2,3,4;5,6,7,8;9,10,11,12}^2

轉置一維列

1. 選取由五個空白儲存格組成的一欄。

2. 輸入下列公式，然後按 Ctrl+Shift+Enter：

   =TRANSPOSE({1,2,3,4,5})

   即使輸入水平矩陣常數，TRANSPOSE 函數也會將矩陣常數轉換至欄中。

轉置一維欄

1. 選取由五個空白儲存格組成的一列。

2. 輸入下列公式，然後按 Ctrl+Shift+Enter：

   =TRANSPOSE({1;2;3;4;5})

即使輸入垂直矩陣常數，TRANSPOSE 函數也會將常數轉換至列中。

轉置二維常數

1. 選取三欄寬四列高的儲存格區塊。

2. 輸入下列常數，然後按 Ctrl+Shift+Enter：

   =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

   TRANSPOSE 函數會將各列轉換成一系列欄。

本節內容提供基本陣列函數的範例。

從現有值建立陣列及矩陣常數

下列範例說明如何使用陣列公式來建立不同工作表裡各儲存格範圍之間的連結。 同時也示範如何從同一組數值建立矩陣常數。

從現有值建立陣列

1. 在 Excel 工作表上，選取 C8:E10 儲存格，然後輸入此公式：

   ={10,20,30;40,50,60;70,80,90}

   因為您正在建立數字陣列，所以在輸入 10 之前，請務必輸入 { (左大括弧)，而在輸入 90 之後，請務必輸入 } (右大括弧)。

2. 按 Ctrl+Shift+Enter，此陣列會在儲存格範圍 C8：E10 中使用數位公式輸入此數位數位數組。 在您的工作表上，C8 至 E10 看起來應該像這樣：

   10	20	30
   40	50	60
   70	80	90

3. 選取 C1 至 E3 的儲存格範圍。

4. 在數據編輯列中輸入下列公式，然後按 Ctrl+Shift+Enter：

   =C8:E10

   單元格 C1 到 E3 中會出現 3x3 單元格陣列，其值與您在 C8 到 E10 中看到的值相同。

從現有的值建立矩陣常數

1. 選取 C1：C3 單元格后，按 F2 切換至編輯模式。 

2. 按 F9 將儲存格參照轉換成值。 Excel 就會將數值轉換成矩陣常數。 公式現在應該是 ={10,20,30;40,50,60;70,80,90}。

3. 按 Ctrl+Shift+Enter ，以數位公式的形式輸入常數陣列。

計算儲存格範圍內的字元數

下列範例為您示範如何計算儲存格範圍內的字元數，空格包括在內。

1. 複製這整個資料表，並貼到工作表的 A1 儲存格。

   資料
   This is a
   bunch of cells that
   come together
   to form a
   single sentence.
   A2:A6 中的總字元數
   =SUM(LEN(A2:A6))
   最長的儲存格 (A3) 的內容
   =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

2. 選取 A8 儲存格，然後按 Ctrl+Shift+Enter 以查看儲存格 A2：A6 中的字元總數 (66) 。

3. 選取 A10 單元格，然後按 Ctrl+Shift+Enter 來查看 A2：A6 中最長的單元格內容， (單元格 A3) 。

下列公式用於單元格 A8 會計算儲存格 A2 到 A6 中字元總數 (66) 。

=SUM(LEN(A2:A6))

在此例中，LEN 函數會傳回範圍內每個儲存格中每個文字字串的長度。 SUM 函數接著將這些值相加，並顯示結果 (66) 。

找出範圍內 n 個最小的數值

本範例示範如何找出儲存格範圍內三個最小的數值。

1. 在儲存格 A1：A11 中輸入一些隨機數位。

2. 選取儲存格 C1 到 C3。 這一組儲存格會保存由陣列公式傳回的結果。

3. 輸入下列公式，然後按 Ctrl+Shift+Enter：

   =SMALL (A1：A11，{1;2;3})

此公式使用矩陣常數來評估 SMALL 函數三次，並傳回單元格 A1：A10 中包含之陣列中最小的 (1) 、第二個最小 (2) ，以及第三小 (3) 成員：若要尋找更多值，請將更多自變數新增至常數。 亦可使用其他函數搭配此公式，例如 SUM 或 AVERAGE。 例如：

=SUM (SMALL (A1：A10，{1,2,3})

=AVERAGE (SMALL (A1：A10，{1,2,3})

找出範圍內 n 個最大的數值

若要找出範圍內最大的數值，可以用 LARGE 函數取代 SMALL 函數。 除此之外，也可如下列範例般，使用 ROW 和 INDIRECT 函數。

1. 選取格格 D1 到 D3。

2. 在數據編輯列中輸入此公式，然後按 Ctrl+Shift+Enter：

   =LARGE (A1：A10，ROW (INDIRECT (“1：3”) ) )

此時，如果對 ROW 和 INDIRECT 函數稍有了解，可能會有幫助。 您可以使用 ROW 函數來建立連續整數的陣列。 例如，在練習活頁簿中選取 10 個儲存格的空白欄，輸入此數位公式，然後按 Ctrl+Shift+Enter：

=ROW(1:10)

公式隨即建立含 10 個連續整數的欄。 若要查看潛在的問題，請在含陣列公式的範圍上方 (亦即第 1 列上方) 插入列。 Excel 會調整列參照，而公式會產生 2 到 11 的整數。 若要修正該問題，可在公式中加入 INDIRECT 函數：

=ROW(INDIRECT("1:10"))

INDIRECT 函數使用文字字串作為引數 (因此 1:10 範圍才會用雙引號括住)。 您插入列或移動陣列公式時，Excel 並不會調整文字值。 因此，ROW 函數永遠都會產生您所要的整數陣列。

讓我們來看看您先前使用的公式： =LARGE (A5：A14，ROW (INDIRECT (“1：3”) ) ) ， 從內括弧開始往外算： INDIRECT 函數會傳回一組文字值，在此情況下會傳回值 1 到 3。 ROW 函數會產生三儲存格欄數位。 LARGE 函數使用 A5：A14 單元格範圍中的值，並且會針對 ROW 函數傳回的每一個參照進行一次評估三次。 3200、2700 和 2000 的值會傳回三單元格欄陣列陣列。 如果您想要尋找更多值，請將較大的儲存格範圍新增至 INDIRECT 函數。

如同先前的範例，您可以將此公式與其他函數搭配使用，例如 SUM 和 AVERAGE。

找出儲存格範圍內最長的文字字串

返回 至先前的文字字串範例，請在空白單元格中輸入下列公式，然後按 Ctrl+Shift+Enter：

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

隨即會出現「有一堆單元格」的文字。

讓我們更仔細看一下公式，從內元素開始往外分析。 LEN 函數會傳回單元格範圍 A2：A6 中每個項目的長度。 MAX 函數會計算這些專案中的最大值，對應到最長的文字字串，也就是單元格 A3。

下面的情形就比較複雜了。 MATCH 函數會計算含最長文字字串之儲存格的位移 (相對位置)。 若要執行這項作業，必須有以下三個引數：查閱值、查閱陣列、比對方式。 MATCH 函數會在查閱陣列中搜尋指定的查閱值。 在本範例中，查閱值是最長的文字字串：

(MAX (LEN (A2：A6) )

該字串存放於以下陣列中：

LEN (A2：A6)

比對方式引數是 0。 比對方式可以包含 1、0 或 -1 的值。 如果您指定 1，MATCH 會傳回小於或等於查閱值的最大值。 如果您指定 0，MATCH 會傳回第一個完全等於查閱值的值。 如果您指定 -1，MATCH 函數會尋找大於或等於指定查閱值的最小值。 如果您省略比對方式，Excel 會假設為 1。

最後，INDEX 函數會採用以下引數：陣列，以及該陣列中的列號和欄號。 A2：A6 單元格範圍提供數位， MATCH 函數提供儲存格地址，最後自變數 (1) 指定值來自數位中的第一欄。

本節內容提供進階陣列函數的範例。

加總含錯誤值的範圍

若您嘗試加總的範圍內包含錯誤值 (如 #N/A)，Excel 中的 SUM 函數就無法運作。 這個範例會示範如何加總名為「資料」且包含錯誤的範圍內的值。

=SUM(IF(ISERROR(資料),"",資料))

此公式會建立新陣列，其中包含減去任何錯誤值的原始值。 ISERROR 函數會從內部函數開始往外分析，搜尋儲存格範圍 (資料) 中的錯誤。 IF 函數會在您指定之條件的計算結果為 TRUE 時傳回特定的值，並在結果為 FALSE 時傳回另一個值。 在此例中，它會對所有錯誤值傳回空字串 ("")，這是因為計算結果為 TRUE；而且還會傳回範圍 (資料) 的其餘值，這是因為計算結果為 FALSE，表示當中不包含錯誤值。 SUM 函數接著會計算篩選陣列的總計。

計算範圍內錯誤值的數目

本範例類似於先前的公式，但是會傳回名為「資料」的範圍內的錯誤值而不是篩選除去：

=SUM(IF(ISERROR(資料),1,0))

此公式會建立陣列，其中包含值為 1 的含錯誤儲存格，以及值為 0 的不含錯誤儲存格。 您可以簡化公式，並且移除 IF 函數的第三個引數來得到相同的結果，如下所示：

=SUM(IF(ISERROR(資料),1))

如果不指定引數，只要儲存格不包含錯誤值，IF 函數就會傳回 FALSE。 您可以更進一步將公式簡化如下：

=SUM(IF(ISERROR(資料)*1))

此公式運作無誤，因為 TRUE*1=1 而 FALSE*1=0。

根據條件加總數值

您可能必須根據條件加總數值。 例如，以下陣列公式只加總名為「銷售」的範圍內的正整數：

=SUM(IF(銷售>0,銷售))

IF 函數會建立由正值與偽值組成的陣列。 SUM 函數基本上會忽略偽值，原因在於 0+0=0。 您在此公式中使用的儲存格範圍可以包含任何數目的列和欄。

您也可以加總符合多個條件的數值。 例如，以下陣列公式會計算大於 0 且小於或等於 5 的數值：

=SUM((銷售>0)*(銷售<=5)*(銷售))

請牢記在心，如果範圍內包含一個或多個非數值儲存格，那麼此公式就會傳回錯誤。

您也可以建立一些只使用一種 OR 條件的陣列公式。 例如，您可以加總小於 5 以及大於 15 的數值：

=SUM(IF((銷售<5)+(銷售>15),銷售))

IF 函數會找出小於 5 以及大於 15 的所有數值，然後將這些數值傳遞給 SUM 函數。

您不能直接在陣列公式中使用 AND 與 OR 函數，因為這些函數會傳回單一結果，不是 TRUE 就是 FALSE，而陣列函數需要的是結果陣列。 您可以使用先前公式中出現的邏輯，來解決這項問題。 換句話說，您在符合 OR 或 AND 條件的數值上，執行加法或乘法之類的數學運算。

計算排除零以外的平均值

以下範例為您示範如何在必須取得範圍內的平均值時，將範圍內的零移除。 公式會使用名為「銷售」的資料範圍：

=AVERAGE(IF(銷售<>0,銷售))

IF 函數會建立不等於 0 的值陣列，然後將這些值傳遞給 AVERAGE 函數。

計算兩個儲存格範圍之間差異的數目

此陣列公式會針對「我的資料」與「你的資料」這兩個儲存格範圍內的數值進行比較，然後傳回這兩個範圍之間的差異數目。 如果兩個範圍的內容完全相同，公式會傳回 0。 若要使用此公式，儲存格範圍必須是相同大小及相同維度 (例如，如果「我的資料」的範圍是 3 列 5 欄，「你的資料」也必須是 3 列 5 欄)：

=SUM(IF(我的資料=你的資料,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(資料))