陣列公式的規則和範例

陣列公式的規則和範例

陣列公式是可對陣列中的一或多個專案執行多個計算的公式。 您可以將陣列看作是一列或一欄的值,或是資料列和資料行的組合。 陣列公式可以傳回多個結果,或返回單一結果。

Microsoft 365的2018年9月更新開始,任何可傳回多個結果的公式,都會自動將它們溢出,或跨越連續的儲存格。 這種行為變更也會伴隨數個新的動態陣列函數。 動態陣列公式(無論是使用現有函數或動態陣列函數),只需要輸入單一儲存格,然後按enter進行確認。 舊版陣列公式首先需要選取整個輸出範圍,然後使用Ctrl + Shift + Enter來確認公式。 它們通常稱為CSE公式。

您可以使用陣列公式來執行複雜的工作,例如:

  • 快速建立範例資料集。

  • 計算儲存格範圍內所含的字元數。

  • 只加總符合特定條件的數位,例如範圍中的最低值,或是落在上限與下限之間的數位。

  • 加總值範圍中的每 n 個值。

下列範例示範如何建立多儲存格和單儲存格陣列公式。 如有可能,我們已將範例包含在一些動態陣列函數中,以及現有的陣列公式同時輸入為動態與舊版陣列。

下載我們的範例

下載範例活頁簿,其中包含本文所述的所有陣列公式範例

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

  • 多儲存格陣列公式

    儲存格 H10 數值中的多儲存格陣列函數 = F10: F19 * G10: G19 計算依單位價格售出的汽車數

  • 在這裡,我們會輸入= F10: F19 * G10: G19在儲存格 h10 數值中,為每位銷售人員計算車和房的總銷售額。

    當您按下enter時,會看到結果溢出至儲存格 H10 數值: H19。 請注意,當您選取溢出範圍中的任何儲存格時,會以框線醒目提示溢出範圍。 您可能也會注意到儲存格 H10 數值: H19 中的公式呈現灰色。 它們只是為了參照,所以如果您想要調整公式,您必須選取 [儲存格 H10 數值],主要公式就會存在。

  • 單儲存格陣列公式

    單儲存格陣列公式,用於計算總和 = SUM 的總計(F10: F19 * G10: G19)

    在範例活頁簿的儲存格 H20 中,輸入或複製並貼上= SUM (F10: F19 * G10: G19),然後按enter

    在這種情況下,Excel 會將陣列中的值相乘(儲存格範圍是從 F10 到 G19),然後使用 SUM 函數將總計相加。 結果是 sales 中的 $1590000 總計。

    此範例顯示此公式類型的功能。 例如,假設您有1000列的資料。 您可以在單一儲存格中建立陣列公式,而不是將公式向下拖曳至1000列,即可加總部分或全部資料。 另外,請注意,儲存格 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}= {"一月份","二月","三月"}

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

下列程序可讓您稍加練習如何建立水平、垂直及二維常數。 我們將顯示使用SEQUENCE 函數來自動產生陣列常數的範例,以及手動輸入的陣列常數。

  • 建立水平常數

    使用先前範例的活頁簿,或建立新的活頁簿。 選取任何空白儲存格,然後按 enter = SEQUENCE (1,5)。 SEQUENCE 函數會建立1列乘5欄陣列,其結果與= {1,2,3,4,5}。 隨即顯示下列結果:

    使用 = SEQUENCE (1,5)或 = {1,2,3,4,5} 建立水準陣列常數。

  • 建立垂直常數

    選取位於其底下的任何空白儲存格,然後輸入= SEQUENCE (5),或= {1; 2; 3; 4; 5}。 隨即顯示下列結果:

    使用 = SEQUENCE (5)或 = {1; 2; 3; 4; 5} 建立垂直陣列常數

  • 建立二維常數

    選取任何空白儲存格,右側及下方都有一個空格,然後按 enter = SEQUENCE (3,4)。 您會看到以下結果:

    建立3列乘4欄陣列常數 = SEQUENCE (3,4)

    您也可以輸入: or = {1,2,3,4; 5,6,7,8; 9,10,11,12},但是您可能會想要在您將半冒號與逗號放在哪個位置上加以留意。

    如您所見,順序選項可提供與手動輸入您的陣列常數值相比的明顯優點。 主要是節省您的時間,但也有助於減少手動輸入的錯誤。 它也更容易閱讀,特別是在分號分隔的情況下,很難區別半冒號。

以下範例會使用常數常數作為較大公式的一部分。 在範例活頁簿中,移至公式工作表中的常數,或建立新的工作表。

在儲存格 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。

在公式中使用常數陣列。 在這個範例中,我們使用 = SUM (D9: H (* 順序(1,5))

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 (順序(1,5,3,1) * 順序(1,5)),或= SUM ({3,4,5,6,7,4,5,2,3,4,5})

您可以在陣列常數中使用的元素

  • 陣列常數可以包含數位、文字、邏輯值(例如 TRUE 和 FALSE)及錯誤值(例如 #N/A。 您可以在整數、小數及科學格式中使用數位。 如果您包含文字,您必須在它的兩邊加上引號("text")。

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

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

移至 [公式] >定義的名稱> [定義名稱]。 在 [名稱] 方塊中,輸入 Quarter1。 在 [參照到] 方塊中,輸入以下常數 (記得要手動輸入大括弧):

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

對話方塊現在看起來就像這樣:

從公式中新增命名的陣列常數 > 定義的名稱 > Name Manager > 新增

按一下[確定],然後選取含有三個空白儲存格的任何資料列,然後按 Enter = Quarter1

隨即顯示下列結果:

在公式中使用命名的陣列常數,例如 = Quarter1,其中 Quarter1 已定義為 = {"一月份","二月","三月"}

如果您想要讓結果垂直溢出,而不是水準溢出,您可以使用=換位(Quarter1)

如果您想要顯示12個月份的清單,就像您在建立財務報表時所使用的那樣,您可以使用 SEQUENCE 函數將本年度作為一年。 這個函數的重點,就是即使只顯示月份,仍會有有效的日期供您在其他計算中使用。 您可以在範例活頁簿中的命名陣列常數快速範例資料集工作表上找到這些範例。

= TEXT (DATE (YEAR (TODAY ()),順序(1,12),1),"mmm")

使用文字、日期、年份、TODAY 及順序函數的組合來建立12個月的動態清單

這會使用DATE 函數根據目前年份建立日期,序列會在1月到12月的1到12之間建立一個常數,然後, TEXT 函數會將顯示格式轉換為 "mmm" (Jan、二月、Mar 等)。 如果您想要顯示完整的月份名稱(例如1月),您可以使用「mmmm」。

當您使用命名常數做為陣列公式時,請記得輸入等號,就像在 = Quarter1 中,而不只是 Quarter1。 若未輸入等號,Excel 會將陣列解譯為文字字串,而公式會無法如預期般運作。 最後,請記住,您可以使用函數、文字與數位的組合。 這完全取決於您要取得的創意。

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

  • 陣列中的多個專案

    Enter = SEQUENCE (1,12) * 2,或= {1,2,3,4; 5,6,7,8; 9,10,11,12} * 2

    您也可以使用(/)、加上 and (+),並以(-)分隔。

  • 求陣列中項目的平方值

    Enter = SEQUENCE (1,12) ^ 2,或= {1,2,3,4; 5,6,7,8; 9,10,11,12} ^ 2

  • 在陣列中找出平方的平方根

    Enter =SQRT(順序(1,12) ^ 2),或= SQRT ({1,2,3,4; 5,6,7,8; 9,10,11,12} ^ 2)

  • 轉置一維列

    Enter = 換位(順序(1,5)),或= 換位({1,2,3,4,5})

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

  • 轉置一維欄

    Enter = 換位(順序(5,1)),或= 換位({1; 2; 3; 4; 5})

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

  • 轉置二維常數

    Enter = 換位(順序(3,4)),或= 換位({1,2,3,4; 5,6,7,8; 9,10,11,12})

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

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

  • 從現有值建立陣列

    下列範例說明如何使用陣列公式從現有陣列建立新陣列。

    Enter = SEQUENCE (3,6,10,10),或= {10,20,30,40,50,60; 70,80,90100110120; 130140150160170180}

    請務必先輸入 {(左大括弧),再輸入10,然後輸入180(右大括弧),因為您要建立數位的陣列。

    接下來,在空白儲存格中輸入= D9 #,或= D9: I11 。 出現 3 x 6 個儲存格陣列,並顯示與 D9: D11 中相同的值。 # 符號稱為 [溢出範圍] 運算子,而且它是參照整個陣列範圍的 Excel's 方式,而不需要輸入它。

    使用溢出的範圍運算子(#)來參照現有陣列

  • 從現有的值建立矩陣常數

    您可以取得溢出陣列公式的結果,並將其轉換成其元件部分。 選取儲存格 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)

    在此情況下,match 類型引數為0。 Match 類型可以是1、0或-1 值。

    • 1-傳回小於或等於查閱值的最大值

    • 0-傳回完全等於查閱值的第一個值

    • -1-傳回大於或等於指定查閱值的最小值

    • 如果您省略比對方式,Excel 會假設為 1。

    最後, INDEX 函數會採用下列引數:陣列,以及該陣列中的列和欄號。 在 C9: C13 提供陣列時,MATCH 函數會提供儲存格位址,而最後一個引數(1)則指定值來自陣列中的第一欄。

    如果您想要取得最小文字字串的內容,請將上述範例中的 [最大值] 取代為 [最小值]。

  • 找出範圍中 n 個最小的數值

    這個範例說明如何在儲存格範圍中找出三個最小的值,其中,儲存格 B9 中的範例資料陣列是以下列: = INT (RANDARRAY(10,1) * 100)為 B18has 建立。 請注意,RANDARRAY 是一個可變函數,所以每次 Excel 計算時,您都會收到一組新的亂數字。

    要尋找第 n 個最小值的 Excel 陣列公式: = SMALL (B9 #,SEQUENCE (D9))

    Enter = 小號(b9 #,SEQUENCE (D9)= SMALL (B9: B18,{1; 2; 3})

    此公式使用常數陣列來評估SMALL 函數三次,並傳回儲存格 B9: B18 中所含陣列中的最小3個成員,其中3是儲存格 D9 中的可變值。 若要尋找更多值,您可以增加 SEQUENCE 函數中的值,或在常數中新增更多引數。 亦可使用其他函數搭配此公式,例如 SUMAVERAGE。 例如:

    = SUM (小號(B9 #,SEQUENCE (D9))

    = AVERAGE (小號(B9 #,SEQUENCE (D9))

  • 找出範圍中 n 個最大的數值

    若要找出範圍中的最大值,您可以使用大型函數來取代 SMALL 函數。 除此之外,也可如下列範例般,使用 ROWINDIRECT 函數。

    Enter = 大型(b9 #,ROW (間接("1: 3")))),或= 大型(B9: B18,列(間接("1: 3") ))

    此時,如果對 ROW 和 INDIRECT 函數稍有了解,可能會有幫助。 您可以使用 ROW 函數來建立連續整數的陣列。 例如,選取空白,然後按 enter:

    =ROW(1:10)

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

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

    間接函數會使用文字字串做為其引數,這就是範圍1:10 在引號周圍的原因。 您插入列或移動陣列公式時,Excel 並不會調整文字值。 因此,ROW 函數永遠都會產生您所要的整數陣列。 您可以簡單地使用順序:

    = SEQUENCE (10)

    讓我們來檢查您先前使用的公式: = 大型(B9 #,ROW (間接("1: 3"))))))))))))中的公式:間接函數會傳回一組文字值,在這種情況下,值1到3。 ROW 函數接著會產生一個三個儲存格的欄陣列。 大型函數會使用儲存格範圍 B9: B18 中的值,而且會針對 ROW 函數所傳回的每個參照,評估三次。 如果您想要尋找更多值,您可以在間接函數中新增更大的儲存格範圍。 最後,與小型範例一樣,您也可以將這個公式與其他函數搭配使用,例如 SUM 與 AVERAGE。

  • 加總含錯誤值的範圍

    當您嘗試加總包含錯誤值的範圍時,Excel 中的 SUM 函數將無法運作,例如 #VALUE! 或 #N/A。 這個範例示範如何加總包含錯誤之名稱資料範圍中的值:

    使用陣列來處理錯誤。 例如,= SUM (IF (ISERROR (資料),"",資料)會加總名為數據的範圍,即使它包含錯誤,例如 #VALUE! 或 #NA!。

  • =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,Sales))會加總在名為 Sales 的範圍中大於0的所有值。

例如,此陣列公式只會加總一個名為 Sales 的正整數,代表上面範例中的儲存格 E9: E24:

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

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

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

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

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

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

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

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

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

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

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

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

=SUM(IF(我的資料=你的資料,0,1))

此公式會建立一個新陣列,而且該陣列的大小跟您要比較之範圍相同。 IF 函數會用 0 值和 1 值填滿陣列 (0 代表比對不相符,1 代表完全相同的儲存格)。 SUM 函數接著會傳回陣列中數值的總和。

公式可簡化如下:

= SUM (1 * (我<>資料你資料))

此公式就像是可計算範圍內有錯誤值的公式,之所以可以順利運作,就是因為 TRUE*1=1 而 FALSE*1=0。

以下陣列公式會傳回「資料」單欄範圍內最大值的列號:

=MIN(IF(資料=MAX(資料),ROW(資料),""))

IF 函數會建立新陣列,該陣列對應到名為「資料」的範圍。 若對應的儲存格包含範圍內的最大值,則該陣列會包含列號。 否則,該陣列會包含空字串 ("")。 MIN 函數會使用新陣列作為其第二個引數,並傳回最小值,該值對應的是「資料」中最大值的列號。 如果名為「資料」的範圍包含相同的最大值,則公式會傳回第一個值的列。

如果您要傳回最大數值的實際儲存格位址,請使用以下公式:

=ADDRESS(MIN(IF(資料=MAX(資料),ROW(資料),"")),COLUMN(資料))

您可以在範例活頁簿中找到與資料集工作表之間的差異相似的範例。

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

  • 多儲存格陣列公式

複製下列整個表格,並將其貼到空白工作表中的 A1 儲存格。

銷售 人員

汽車 類型

銷售數量

單價 Price

銷售額

孫哲翰

四門轎車

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}。 

E 欄的總計是由陣列公式計算而得

  • 建立單儲存格陣列公式

在活頁簿的儲存格 D13 中,輸入下列公式,然後按Ctrl + Shift + Enter

=SUM(C2:C11*D2:D11)

在這種情況下,Excel 會將陣列中的值相乘(C2 到 D11 的儲存格範圍),然後使用SUM 函數將總計相加。 結果是 sales 中的 $1590000 總計。 此範例顯示此公式類型的功能。 例如,假設您有1000列的資料。 您可以在單一儲存格中建立陣列公式,而不是將公式向下拖曳至1000列,即可加總部分或全部資料。

另外,請注意,儲存格 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 中的函數使用。

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

建立水平常數

  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 會省略那些沒有對應儲存格的值。

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

以陣列中各項目相乘

  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

    為期

    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})

此公式使用常數陣列來評估小型函數三次,並傳回儲存格 A1: A10 中包含的陣列中的最小值(1)、第二小值(2)和第三個最小(3)成員)若要找出更多的值,您可以新增更多引數到常數。 亦可使用其他函數搭配此公式,例如 SUMAVERAGE。 例如:

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

= AVERAGE (小號(A1: A10,{1,2,3})

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

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

  1. 選取儲存格 D1 到 D3。

  2. 在資料編輯列中,輸入此公式,然後按Ctrl + Shift + enter

    = 大型(A1: A10,列(間接("1: 3")))

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

=ROW(1:10)

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

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

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

讓我們來看看您先前使用的公式: = 大型(A5: A14,列(間接("1: 3") )))(從內部括弧開始,然後向外工作):間接函數會傳回一組文字值,在此情況下,值1到3。 ROW函數接著會產生一個三個儲存格的縱欄陣列。 大型函數會使用儲存格範圍 A5: A14 中的值,而且會針對ROW函數所傳回的每個參照,評估三次。 值3200、2700和2000會傳回到三個儲存格的縱欄陣列中。 如果您想要尋找更多值,您可以在間接函數中新增更大的儲存格範圍。

就像先前的範例,您可以將此公式與其他函數搭配使用,例如SUMAVERAGE

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

回到先前的文字字串範例,在空白儲存格中輸入下列公式,然後按Ctrl + Shift + enter

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

隨即出現的文字「儲存格組成堆」。

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

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

(MAX (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 函數。

您不能直接在陣列公式中使用 ANDOR 函數,因為這些函數會傳回單一結果,不是 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 * (我<>資料你資料))

此公式就像是可計算範圍內有錯誤值的公式,之所以可以順利運作,就是因為 TRUE*1=1 而 FALSE*1=0。

找出範圍中最大值的位置

以下陣列公式會傳回「資料」單欄範圍內最大值的列號:

=MIN(IF(資料=MAX(資料),ROW(資料),""))

IF 函數會建立新陣列,該陣列對應到名為「資料」的範圍。 若對應的儲存格包含範圍內的最大值,則該陣列會包含列號。 否則,該陣列會包含空字串 ("")。 MIN 函數會使用新陣列作為其第二個引數,並傳回最小值,該值對應的是「資料」中最大值的列號。 如果名為「資料」的範圍包含相同的最大值,則公式會傳回第一個值的列。

如果您要傳回最大數值的實際儲存格位址,請使用以下公式:

=ADDRESS(MIN(IF(資料=MAX(資料),ROW(資料),"")),COLUMN(資料))

消息

本文中的部分是以 Colin Wilcox 所撰寫的一系列 Excel Power User 欄為基礎,並與 Excel 2002 公式的章節14和15(由 John Walkenbach 撰寫的書)是舊版的 Excel MVP 所撰寫。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

另請參閱

動態陣列與溢出陣列行為

動態陣列公式與舊版 CSE 陣列公式

FILTER 函數

RANDARRAY 函數

SEQUENCE 函數

SORT 函數

SORTBY 函數

UNIQUE 函數

Excel 中的 #SPILL! 錯誤

隱含交集運算子:@

公式概觀

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×