陣列公式的規則和範例

若要成為 Excel 進階使用者,您必須了解如何使用陣列公式,來執行非陣列公式所無法進行的計算。以下文章是以 Colin Wilcox 所撰寫的一系列 Excel 進階使用者專欄為基礎,並以 Excel 最有價值專家 John Walkenbach 所著書籍《Excel 2002 公式》之第 14 章和第 15 章的內容改寫而成。

了解陣列公式

陣列公式通常稱為 CSE (Ctrl+Shift+Enter) 公式,因為您不是只按 Enter 鍵,而是按 Ctrl+Shift+Enter 來完成公式。

為什麼要使用陣列公式?

如果您曾經在 Excel 中使用過公式,就知道可以執行一些相當複雜的運算。例如,您可以計算出某指定年數的貸款總成本。您可以使用陣列公式來執行下列複雜的工作:

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

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

  • 加總值範圍內每隔 n 個數的值。

陣列及陣列公式的快速簡介

陣列公式是一種可對陣列中一或多個項目執行多項計算的公式。您可以把陣列想成一列的值、一欄的值,或是混合了多列和多欄的值。陣列公式可以傳回多個結果或單一結果。例如,您可以在儲存格範圍內建立陣列公式,並使用陣列公式來計算一欄或一列的小計。也可以將陣列公式放在單一儲存格內,然後計算單一數量。包含多個儲存格的陣列公式稱為多儲存格公式,而單一儲存格中的陣列公式稱為單儲存格公式。

下一節中的範例會為您示範如何建立多儲存格及單儲存格的陣列公式。

試試看吧!

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

多儲存格陣列公式

以下是內嵌在瀏覽器中的活頁簿。雖然當中包含範例資料,但請注意,您無法在內嵌活頁簿中建立或變更陣列公式,必須使用 Excel 程式才行。您可以參考內嵌活頁簿中的答案,部分內文會說明陣列公式的運作方式,但是若要真正了解陣列公式,必須在 Excel 中查看該活頁簿。

建立多儲存格陣列公式

  1. 複製下方的整份資料表,將其貼到 Excel 空白工作表的 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)

  2. 若要查看每位銷售員的雙門車和房車總銷售額,請選取 E2:E11,輸入公式 =C2:C11*D2:D11,然後按 Ctrl+Shift+Enter。

  3. 若要查看所有銷售額的總計,請選取 F11 儲存格,輸入 =SUM(C2:C11*D2:D11),然後按 Ctrl+Shift+Enter。

按一下活頁簿底端黑色列的綠色 Excel 按鈕,即可下載此活頁簿。然後,就可以在 Excel 中開啟該檔案,選取包含陣列公式的儲存格,然後按 Ctrl+Shift+Enter 執行該公式。

如果您是在 Excel 中操作,請確定 [工作表1] 已在使用中,然後選取 E2:E11 儲存格。按 F2,然後在目前的 E2 儲存格中輸入公式 =C2:C11*D2:D11。如果您按 Enter 鍵,就會看到只有 E2 儲存格輸入公式,並顯示 165000。輸入公式後,請改按 Ctrl+Shift+Enter,而不是只按 Enter。您現在可以在 E2:E11 儲存格看到結果了。請注意,在資料編輯列中,公式顯示為 {=C2:C11*D2:D11}。因此,您可以得知它是陣列公式,如下表所示。

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

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

建立單儲存格陣列公式

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

=SUM(C2:C11*D2:D11)

在此情況下,Excel 會將陣列 (即 C2 至 D11 的儲存格範圍) 中的值相乘,然後使用 SUM 函數,將合計加總。結果得出總計 $1,590,000 美元的銷售額。以下範例展示這類公式的功能有多強大。例如,假設您有 1,000 個資料列, 那麼就可以在單一儲存格中建立陣列公式來加總部分或全部的資料,而不用將公式向下拖曳過 1,000 列。

亦請注意,G11 儲存格中的單儲存格公式其實與 E2 至 E11 儲存格的多儲存格公式完全無關。這也是使用陣列公式的另一項優點 ——彈性。您可以在不影響 G11 中的公式下,變更 E 欄中的公式,或一併將該欄刪除。

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

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

  • 安全性    您無法覆寫多儲存格陣列公式的元件。例如,按一下 E3 儲存格,再按 Delete 鍵。您必須選取整個範圍的儲存格 (E2 至 E11) 並變更整個陣列的公式,或將陣列保持現狀。另外還有一項安全措施,必須按 Ctrl+Shift+Enter 才能確認公式的變更。

  • 檔案較小    您通常可以使用單一陣列公式,而不需要使用多個中繼公式。例如,活頁簿使用一個陣列公式來計算 E 欄的結果。如果您使用標準公式 (例如 =C2*D2、C3*D3、C4*D4...),則會用到 11 個不同的公式來求算出相同的結果。

陣列公式語法

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

陣列公式是建立複雜公式的有效方法。=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 會顯示一則訊息,告訴您無法變更陣列的任何部分。

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

  • 若要刪除陣列公式,請選取整個公式 (例如 =C2:C11*D2:D11),再按 Delete 鍵,然後按 Ctrl+Shift+Enter。

  • 您不能在多儲存格陣列公式中插入空白儲存格,或從中刪除儲存格。

展開陣列公式

有時,您可能需要展開陣列公式。過程並不複雜,但請務必遵循上述指引。

在此工作表上,我們在第 12 列到第 17 列新增了一些銷售列。現在,我們要更新陣列公式,使其包含這些額外的列。

下載活頁簿到電腦之後,請務必在 Excel 傳統型程式中執行這項作業。

展開陣列公式

  1. 請將整個表格複製到 Excel 工作表中的 A1 儲存格。

    銷售
    人員


    銷售



    銷售額

    孫哲翰

    四門轎車

    5

    33000

    165000

    雙門轎跑車

    4

    37000

    148000

    李莉華

    四門轎車

    6

    24000

    144000

    雙門轎跑車

    8

    21000

    168000

    羅書成

    四門轎車

    3

    29000

    87000

    雙門轎跑車

    1

    31000

    31000

    盧珮佳

    四門轎車

    9

    24000

    216000

    雙門轎跑車

    5

    37000

    185000

    吳又倫

    四門轎車

    6

    33000

    198000

    雙門轎跑車

    8

    31000

    248000

    蕭庭宇

    四門轎車

    2

    27000

    雙門轎跑車

    3

    30000

    王立民

    四門轎車

    4

    22000

    雙門轎跑車

    1

    41000

    楊棟材

    四門轎車

    5

    32000

    雙門轎跑車

    3

    36000

    總計

  2. 請選取 E18 儲存格,在 A20 儲存格中輸入「總計」公式:=SUM(C2:C17*D2:D17)。然後,按 Ctrl+Shift+Enter。
    答案應為 2,131,000。

  3. 選取包含目前陣列公式 (E2:E11) 的儲存格範圍,再選取新資料旁邊的空白儲存格 (E12:E17), 換句話說,就是選取 E2:E17 儲存格。

  4. 按 F2 切換到編輯模式。

  5. 在資料編輯列中,將 C11 變更為 C17,將 D11 變更為 D17,然後按 Ctrl+Shift+Enter。
    Excel 會更新 E2 至 E11 儲存格中的公式,並在 E12 至 E17 的新儲存格中放入公式實例。

  6. 在 F17 儲存格中輸入此陣列公式:= SUM(C2:C17*D2*D17),使其參照第 2 列至第 17 列的儲存格。然後,按 Ctrl+Shift+Enter 來輸入此陣列公式。
    新的總計應為 2,131,000。

使用陣列公式的缺點

陣列公式很好用,但也有一些缺點:

  • 您可能偶爾會忘記按 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. 使用先前範例的活頁簿,或建立新的活頁簿。

  2. 選取 A1 至 E1 的儲存格。

  3. 在資料編輯列中輸入下列公式,然後按 Ctrl+Shift+Enter:

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

    在本範例中,您「應該」輸入左、右大括弧 ({ })。

    畫面會顯示下列結果。

    公式中的水平常數陣列

建立垂直常數

  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}。別忘了!Excel 不會用大括弧括住常數陣列;您必須自行輸入大括弧, 另外請記住,在陣列公式中加入常數之後,要按 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. 選取 C 1:C3 儲存格後,請按 F2 切換到編輯模式。
    陣列公式仍應為 = C8:E10。

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

  3. 按 Ctrl+Shift+Enter 將常數陣列輸入為陣列公式。

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

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

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

  2. 選取 A9 儲存格,然後按 Ctrl+Shift+Enter 來查看 A2:A6 儲存格中的字元總數 (66)。

  3. 選取 A12 儲存格,然後按 Ctrl+Shift+Enter 來查看 A2:A6 中最長儲存格 (A3 儲存格) 的內容。

資料

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)

下列公式用於 A9 儲存格,用來計算 A2 至 A6 儲存格中的字元總數 (66)。

=SUM(LEN(A2:A6))

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

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

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

  1. 選取 A16 至 A18 的儲存格。
    這一組儲存格會保存由陣列公式傳回的結果。

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

    =SMALL(A5:A14,{1;2;3})

400475500 這三個數值隨即分別出現在 A16 至 A18 的儲存格之中。

此公式使用常數陣列來評估 SMALL 函數三次,並傳回 A1:A10 儲存格包含的陣列中最小 (1)、次小 (2) 和第三小 (3) 的成員。若要找出更多數值,請在常數中加入更多引數,並在 A12:A14 範圍內新增相等數目的結果儲存格。亦可使用其他函數搭配此公式,例如 SUMAVERAGE。例如:

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{1;2;3}))

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

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

  1. 選取 A1 至 A3 的儲存格。

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

    =LARGE(A5:A14,ROW(INDIRECT("1:3")))

320027002000 這三個數值隨即分別出現在 A1 至 A3 的儲存格之中。

此時,如果對 ROWINDIRECT 函數稍有了解,可能會有幫助。您可以使用 ROW 函數來建立連續整數的陣列。例如,在練習用活頁簿中,選取由 10 個儲存格組成的空白欄,在 A5:A14 儲存格中輸入以下陣列公式,然後按 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 函數中加入更大的儲存格範圍。

最後,可將此公式搭配其他函數使用,如 SUMAVERAGE

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

此公式只有在資料範圍包含單欄儲存格時才能順利運作。請在 [工作表3] 的 A16 儲存格中輸入下列公式,然後按 Ctrl+Shift+Enter:

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

A16 儲存格中會出現「bunch of cells that」文字。

讓我們更仔細看一下公式,從內元素開始往外分析。LEN 函數會傳回 A6:A9 儲存格範圍內每個項目的長度。MAX 函數則會計算這些項目之中的最大值,此最大值對應至最長的文字字串,亦即 A7 儲存格。

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

(MAX(LEN( A6 : A9 ))

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

LEN( A6:A9 )

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

最後,INDEX 函數會採用以下引數:陣列,以及該陣列中的列號和欄號。A6:A9 儲存格範圍會提供陣列,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(資料))

頁面頂端

需要更多協助嗎?

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

請參閱

公式概觀

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×