陣列公式是一種公式,可以在陣列中的一或多個專案上執行多個計算。 您可以將陣列想像成值的列或欄,或是值列與欄的組合。 陣列公式可以返回多個結果或單一結果。
從 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,則當您新增或移除資料時,動態陣列公式會自動調整大小。
-
#SPILL!錯誤 動態陣列引入 #SPILL! 錯誤,表示預期溢出範圍因某種原因被封鎖。 當您解決封鎖時,公式會自動溢出。
-
矩陣常數是陣列公式的一項元件。 您可以輸入項目清單來建立矩陣常數,然後手動輸入大括弧 ({ }) 括住清單,如下所示:
={1,2,3,4,5} 或 ={"一月","2 月","3 月"}
如果是使用逗號來分隔項目,便會建立水平陣列 (列)。 如果是使用分號來分隔項目,便會建立垂直陣列 (欄)。 若要建立二維陣列,請以逗號分隔每一列的專案,然後以分號分隔每一列。
下列程序可讓您稍加練習如何建立水平、垂直及二維常數。 我們將顯示使用 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 (TODAY (TODAY () ) ,SEQUENCE (1,12) ,1) ,"mmm")
這會使用 DATE 函數根據目前年份來建立日期,SEQUENCE 會為 1 月到 12 日建立常數從 1 到 12 的陣列,然後 TEXT 函數會將顯示格式轉換為 "mmm" (Jan、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
-
尋找陣列中平方專案的平方根
Enter =SQRT (SEQUENCE (1,12) ^2) ,或=SQRT ({1,2,3,4;5,6,7,8;9,10,11,12}^2)
-
轉置一維列
輸入 =TRANSPOSE (順序 (1,5) ) 或 =TRANSPOSE ({1,2,3,4,5})
即使輸入水平矩陣常數,TRANSPOSE 函數也會將矩陣常數轉換至欄中。
-
轉置一維欄
輸入 =TRANSPOSE (順序 (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 - 會返回小於或等於查詢 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。 例如:
=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(銷售>0,銷售))
IF 函數會建立一個正值和誤值陣列。 SUM 函數基本上會忽略偽值,原因在於 0+0=0。 您在此公式中使用的儲存格範圍可以包含任何數目的列和欄。
您也可以加總符合多個條件的數值。 例如,此陣列公式會計算大於 0 AND 小於 2500 的值:
=SUM ( (銷售額>0) * (銷售額<2500) * (銷售) )
請牢記在心,如果範圍內包含一個或多個非數值儲存格,那麼此公式就會傳回錯誤。
您也可以建立一些只使用一種 OR 條件的陣列公式。 例如,您可以加總大於 0 OR 小於 2500 的值:
=SUM (IF ( (銷售額>0) + (銷售額<2500) ,Sales) )
您不能直接在陣列公式中使用 AND 與 OR 函數,因為這些函數會傳回單一結果,不是 TRUE 就是 FALSE,而陣列函數需要的是結果陣列。 您可以使用先前公式中出現的邏輯,來解決這項問題。 換句話說,您可以執行數學運算,例如,對符合 OR 或 AND 條件的值執行加法或乘法。
以下範例為您示範如何在必須取得範圍內的平均值時,將範圍內的零移除。 公式會使用名為「銷售」的資料範圍:
=AVERAGE(IF(銷售<>0,銷售))
IF 函數會建立不等於 0 的值陣列,然後將這些值傳遞給 AVERAGE 函數。
此陣列公式會針對「我的資料」與「你的資料」這兩個儲存格範圍內的數值進行比較,然後傳回這兩個範圍之間的差異數目。 如果兩個範圍的內容完全相同,公式會傳回 0。 若要使用此公式,儲存格範圍必須大小相同且維度相同。 例如,如果 MyData 的範圍是 3 列 5 欄,則 YourData 也必須是 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) |
-
若要查看每個銷售人員的雙門車和小車總銷售額,請選取儲存格 E2:E11,輸入公式 =C2:C11*D2:D11,然後按 Ctrl+Shift+Enter。
-
若要查看所有銷售額的總計,請選取儲存格 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)。
重要: 每當您需要輸入陣列公式時,請按 Ctrl+Shift+Enter。 這條規則同時適用於單儲存格和多儲存格的公式。
使用多儲存格公式時,也請記住以下要點:
-
您必須在輸入公式「之前」,選取要放置結果的儲存格範圍。 您是在選取 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 中的函數使用。
下列程序可讓您稍加練習如何建立水平、垂直及二維常數。
建立水平常數
-
在空白工作表中,選取儲存格 A1 到 E1。
-
在資料編輯欄中,輸入下列公式,然後按Ctrl+Shift+Enter:
={1,2,3,4,5}
在這種情況下,您應該在{ } (中輸入左大括弧) 右大括弧,Excel會新增第二組。
畫面會顯示下列結果。
建立垂直常數
-
在活頁簿中,選取由五個儲存格組成的一欄。
-
在資料編輯欄中,輸入下列公式,然後按Ctrl+Shift+Enter:
={1;2;3;4;5}
畫面會顯示下列結果。
建立二維常數
-
在活頁簿中,選取四欄寬三列高的儲存格區塊。
-
在資料編輯欄中,輸入下列公式,然後按Ctrl+Shift+Enter:
={1,2,3,4;5,6,7,8;9,10,11,12}
您會看到下列結果:
在公式中使用常數
以下是使用常數的簡單範例:
-
在範例活頁簿中,建立一張新的工作表。
-
在 A1 儲存格中輸入 3,然後在 B1 中輸入 4,C1 中輸入 5,D1 中輸入 6,E1 中輸入 7。
-
在儲存格 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 會顯示警告訊息。 此外,數值不能包含百分比符號、貨幣符號、逗號或括弧。
使用陣列常數最好的方法之一是命名常數。 已命名的常數使用起來更加容易,而且可以隱藏一些陣列公式的複雜性,不讓其他人看見。 若要為矩陣常數命名並用在公式中,請執行下列步驟:
-
在 [公式] 索引標籤上,按一下 [已定義之名稱] 群組中的 [定義名稱]。
[ 定義名稱> 對話方塊會出現。 -
在 [名稱] 方塊中輸入「第一季」。
-
在 [參照到] 方塊中,輸入以下常數 (記得要手動輸入大括弧):
={"一月","二月","三月"}
對話方塊的內容現在看起來如下:
-
按一下 [確定],然後選取由三個空白儲存格組成的一列。
-
輸入下列公式,然後按 Ctrl+Shift+Enter。
=第一季
畫面會顯示下列結果。
使用已命名的常數作為陣列公式時,記得輸入等號。 若未輸入等號,Excel 會將陣列解譯為文字字串,而公式會無法如預期般運作。 最後,請記住,文字與數字可以混用。
矩陣常數無法運作時,請查看是否有下列問題:
-
有些元素可能沒有用適當的字元分隔。 如果您省略逗號或分號,或將逗號或分號放在錯誤的位置,陣列常數可能無法正確建立,或者您可能會看到警告訊息。
-
您已選取的儲存格範圍可能與常數中的元素數目不符。 例如,如果選取了由六個儲存格組成的一欄,並使用五儲存格常數,那麼空白儲存格中就會顯示 #N/A 錯誤值。 相反地,如果選取的儲存格太少,Excel 會省略那些沒有對應儲存格的值。
以下範例提出多種方式,為您示範如何在陣列公式中使用矩陣常數。 部分範例使用 TRANSPOSE 函數 將列轉換成欄,反之亦然。
以陣列中各項目相乘
-
建立新的工作表,然後選取四欄寬三列高的空白儲存格區塊。
-
輸入下列公式,然後按Ctrl+Shift+Enter:
={1,2,3,4;5,6,7,8;9,10,11,12}*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
轉置一維列
-
選取由五個空白儲存格組成的一欄。
-
輸入下列公式,然後按Ctrl+Shift+Enter:
=TRANSPOSE({1,2,3,4,5})
即使輸入水平矩陣常數,TRANSPOSE 函數也會將矩陣常數轉換至欄中。
轉置一維欄
-
選取由五個空白儲存格組成的一列。
-
輸入下列公式,然後按Ctrl+Shift+Enter:
=TRANSPOSE({1;2;3;4;5})
即使輸入垂直矩陣常數,TRANSPOSE 函數也會將常數轉換至列中。
轉置二維常數
-
選取三欄寬四列高的儲存格區塊。
-
輸入下列常數,然後按Ctrl+Shift+Enter:
=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})
TRANSPOSE 函數會將各列轉換成一系列欄。
本節內容提供基本陣列函數的範例。
從現有值建立陣列及矩陣常數
下列範例說明如何使用陣列公式來建立不同工作表裡各儲存格範圍之間的連結。 同時也示範如何從同一組數值建立矩陣常數。
從現有值建立陣列
-
在 Excel 工作表上,選取 C8:E10 儲存格,然後輸入此公式:
={10,20,30;40,50,60;70,80,90}
因為您正在建立數字陣列,所以在輸入 10 之前,請務必輸入 { (左大括弧),而在輸入 90 之後,請務必輸入 } (右大括弧)。
-
按 Ctrl+Shift+Enter,使用陣列公式在儲存格範圍 C8:E10 中輸入此數位陣列。 在您的工作表上,C8 至 E10 看起來應該像這樣:
10
20
30
40
50
60
70
80
90
-
選取 C1 至 E3 的儲存格範圍。
-
在資料編輯欄中輸入下列公式,然後按Ctrl+Shift+Enter:
=C8:E10
儲存格 C1 到 E3 中會出現一個 3x3 儲存格陣列,其值與在 C8 到 E10 中所看到的值相同。
從現有的值建立矩陣常數
-
選取儲存格 C1:C3 後,按 F2 以切換到編輯模式。
-
按 F9 以將儲存格參照轉換為值。 Excel 就會將數值轉換成矩陣常數。 公式現在應為 ={10,20,30;40,50,60;70,80,90}。
-
按 Ctrl+Shift+Enter 以陣列公式輸入常數陣列。
計算儲存格範圍內的字元數
下列範例為您示範如何計算儲存格範圍內的字元數,空格包括在內。
-
複製這整個資料表,並貼到工作表的 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)
-
選取儲存格 A8,然後按 Ctrl+Shift+Enter 以查看儲存格 A2:A6 中的字元總數 (66) 。
-
選取儲存格 A10,然後按 Ctrl+Shift+Enter 以查看儲存格 A2:A6 中最長儲存格 A2:A6 (儲存格 A3) 。
下列公式用於儲存格 A8 中,計算儲存格 A2 (A6) 66 個字元的總字元數。
=SUM(LEN(A2:A6))
在此例中,LEN 函數會傳回範圍內每個儲存格中每個文字字串的長度。 SUM 函數接著會將這些值相加,並顯示 (66) 。
找出範圍內 n 個最小的數值
本範例示範如何找出儲存格範圍內三個最小的數值。
-
在儲存格 A1:A11 中輸入一些亂數字。
-
選取儲存格 C1 到 C3。 這一組儲存格會保存由陣列公式傳回的結果。
-
輸入下列公式,然後按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 函數。
-
選取儲存格 D1 到 D3。
-
在資料編輯欄中輸入此公式,然後按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(資料))
確認
本文的一部分是以 Colin Wilcox 撰寫的一系列 Excel Power User 資料行為基礎,並依據 2002 年 Excel 年公式的第 14 和 15 章進行調整,此書由前 Excel MVP John Walken用書撰寫。
需要更多協助嗎?
您可以隨時詢問 Excel 技術社群中的專家,或是在 Answers 社群取得支援。