陣列公式的規則和範例

若要成為 Excel 進階使用者,必須知道如何使用陣列公式,它可以執行使用非陣列公式無法進行的計算。下文內容是根據 Colin Wilcox 所撰寫的一系列「Excel 進階使用者」專欄,並改寫一位 Excel MVP,John Walkenbach 所著 Excel 2002 公式 (英文) 一書中的第 14 和 15 章。若要進一步瞭解 John 的其他著作,請參閱他的書籍網頁 (英文)

本文內容


瞭解陣列公式

本節內容簡介陣列公式,並說明如何輸入、編輯並進行疑難排解。

為什麼要使用陣列公式?

如果您有曾經在 Excel 中使用過公式的經驗,就知道您可以執行一些相當複雜的運算 (例如,您可以計算任何指定年數的貸款總值),不過,如果真的要純熟運用 Excel 中的公式,必須知道如何使用陣列公式。您可以使用陣列公式來執行如下複雜的工作:

  • 計算儲存格範圍中所包含的字元數。
  • 只加總符合特定條件的數字,例如範圍中的最低值或是落在上限與下限之間的數字。
  • 加總值範圍中每隔 n 個數的值。

 附註   您可以看到陣列公式被稱為「CSE 公式」,因為您是按 CTRL+SHIFT+ENTER 鍵,將公式輸入您的活頁簿。

陣列及陣列公式快速簡介

只要您曾經做過一點程式設計,可能就會碰到「陣列」這個詞。我們這裡討論的陣列是項目集合。在 Excel 中,這些項目可能是存放於單一列 (稱為一維水平陣列)、欄 (一維垂直陣列),也可能存放於多列或欄 (二維陣列)。您不能在 Excel 中建立三維陣列或陣列公式。

陣列公式是可以對陣列中一個或多個項目執行多重計算的公式,陣列公式可以傳回多重結果或單一結果。例如,您可以將陣列公式放置在儲存格範圍中,然後使用陣列公式計算一個欄或列的小計;您也可以將陣列公式放在單一儲存格中,然後計算單一數量。存放在多個儲存格中的陣列公式稱為多儲存格公式;而存放在單一儲存格中的陣列公式則稱為單儲存格公式。

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

試試看吧!

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

建立多儲存格陣列公式

  1. 開啟新的空白活頁簿。
  2. 複製範例工作表資料,然後從 A1 儲存格開始貼入新的活頁簿。

顯示如何複製範例工作表資料

  • 建立空白的活頁簿或工作表。
  • 在 [說明] 主題中選取範例。

 附註   請勿選取列或欄標題。

從 [說明] 中選取範例

從 [說明] 中選取範例
  • 按下 CTRL+C 鍵。
  • 在工作表中,選取儲存格 A1,並按下 CTRL+V 鍵。

業務代表 車型 銷售數量 單位價格 總銷售額
劉沙東 四門轎車 5 2200  
  雙門轎跑車 4 1800  
李阿瑪 四門轎車 6 2300  
  雙門轎跑車 8 1700  
胡每得 四門轎車 3 2000  
  雙門轎跑車 1 1600  
陳依利 四門轎車 9 2150  
  雙門轎跑車 5 1950  
黃柯霓 四門轎車 6 2250  
  雙門轎跑車 8 2000  

  1. 使用出現在附近的 [貼上選項] 按鈕 按鈕圖像 ,以比對目的地格式。
  2. 若要將陣列中數值相乘 (儲存格範圍 C2 到 D11),請選取儲存格 E2 到 E11,然後在資料編輯列中輸入以下公式:

=C2:C11*D2:D11

  1. 按 CTRL+SHIFT+ENTER 鍵。

Excel 會在公式兩邊加上大括弧 ({ }),並在所選取範圍內的每個儲存格放置公式實例,但是這項作業極其快速,您在 E 欄中所看到的只是每個銷售人員的各車種銷售量。


範例資料


建立單儲存格陣列公式

  1. 在活頁簿的 A13 儲存格中,輸入「總銷售額」。
  2. 在 B13 儲存格中輸入以下公式,然後按 CTRL+SHIFT+ENTER 鍵:

=SUM(C2:C11*D2:D11)

在此範例中,Excel 會將陣列中的數值相乘 (儲存格範圍 C2 到 D11),然後使用 SUM 函數,將合計加在一起,結果是銷售額總計為 $111,800。這個範例顯示這種公式功能之強大。例如,假設,您有 15,000 列資料,您可以在單一儲存格中建立陣列公式,加總部分或全部資料。

同時也請注意,單儲存格公式 (在 B13 儲存格中) 與多儲存格公式 (儲存格 E2 到 E11 中的公式) 完全無關。這也是使用陣列公式的另一項優點:彈性。您可以採取任何動作,不限數目,例如,變更 E 欄中的公式,或整個刪除該欄,而不會影響單儲存格公式。

陣列公式也提供下列優點:

  • 一致性    如果按一下 E2 以下的任何儲存格,就會看到相同的公式,這種一致性有助確保提升準確性。
  • 安全性    您不能覆寫多儲存格陣列公式中的組成元素 (例如,按一下 E3 儲存格,然後按 DELETE 鍵),您必須選取整個儲存格範圍 (E2 到 E11),然後變更整個陣列的公式,或者保留陣列原貌。另外還有一項安全措施,您必須按 CTRL+SHIFT+ENTER 以確認公式的變更。
  • 檔案較小    您經常可以使用單一陣列公式,而不需要使用多個中繼公式。例如,您為本練習所建立的活頁簿使用一個陣列公式來計算 E 欄的結果;如果使用標準公式 (例如 =C2*D2),就會用到 11 個不同的公式來計算相同的結果。

陣列公式語法一窺

陣列公式中大部分是使用標準公式語法,都是以等號開頭,您可以在陣列公式中使用 Excel 的任何內建函數。使用陣列公式最主要的差別在於必須按 CTRL+SHIFT+ENTER 鍵才能輸入公式,輸入時 Excel 會在陣列公式兩邊用大括弧圍住;如果手動輸入大括弧,公式將轉換成文字字串而無法運作。

您必須瞭解的第二件事是,陣列公式是簡化的公式。例如,上文使用過的多儲存格函數是相當於:

=C2*D2
=C3*D3

依此類推。 B13 儲存格中的單儲存格公式濃縮了所有乘法運算,再加上所需的算術,將所有小計相加:=E2+E3+E4,依此類推。

輸入及變更陣列公式的規則

在這裡,再次提醒您建立陣列公式的主要規則:當您必須輸入或編輯陣列公式時,請按 CTRL+SHIFT+ENTER 鍵。這條規則同時適用於單儲存格和多儲存格公式。

只要使用多儲存格公式,就必須同時遵循下列規則:

  • 您必須在輸入公式「之前」,選取儲存格範圍以保存結果。您是在多儲存格陣列公式練習的步驟 3,選取儲存格 E2 到 E11 時,執行這項作業。
  • 您不能變更陣列公式中個別儲存格的內容。若要嘗試這項操作,請在範例活頁簿中選取 E3 儲存格,然後按 DELETE 鍵。
  • 您可以移動或刪除整個陣列公式,但不能移動或刪除部分陣列公式,換句話說,若要縮減陣列公式,必須先刪除現有公式,再從頭開始。

 秘訣   若要刪除陣列公式,請選取整個公式 (例如 =C2:C11*D2:D11),再按 DELETE 鍵,然後按 CTRL+SHIFT+ENTER 鍵。

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

展開陣列公式

有時,您可能需要展開陣列公式。(別忘了!您不能縮減陣列公式。) 處理程序並不複雜,但您必須記住上一節中列出的規則。

  1. 在範例活頁簿中,清除位於主資料表之下的任何文字和單儲存格公式。.
  2. 將資料的其他行從 A12 儲存格開始貼入活頁簿中。使用出現在附近的 [貼上選項] 按鈕 按鈕圖像 ,以比對目的地格式。

劉布萊 四門轎車 6 2500
  雙門轎跑車 7 1900
王路意 四門轎車 4 2200
  雙門轎跑車 3 2000
謝米可 四門轎車 8 2300
  雙門轎跑車 8 2100

  1. 選取包含目前陣列公式 (E2:E11) 的儲存格範圍,再選取新資料旁邊的空白儲存格 (E12:E17),換句話說,選取儲存格 E2:E17。
  2. 按 F2 鍵切換至編輯模式。
  3. 在資料編輯列中,將 C11 變更為 C17,將 D11 變更為 D17,然後按 CTRL+SHIFT+ENTER 鍵。Excel 會更新儲存格 E2 到 E11 之中的公式,並取代新儲存格從 E12 到 E17 之中的公式實例。

範例資料


使用陣列公式的缺點

陣列公式似乎很神奇,但其實本身也有一些缺點:

  • 偶而您可能會忘記按 CTRL+SHIFT+ENTER 鍵。只要輸入或編輯陣列公式,就要記得按這個組合鍵。
  • 其他使用者可能不瞭解您的公式。比較起來,陣列公式的文件記錄相當少,因此,如果其他人必須修改您的活頁簿,應該避免使用陣列公式,或是確定使用者瞭解如何變更這些公式。
  • 視電腦的處理速度和記憶體而定,大型陣列公式可能會讓計算作業變得緩慢。

頁首 頁首

瞭解矩陣常數

本節內容介紹矩陣常數,並說明如何輸入、編輯,及進行疑難排解。

扼要簡介矩陣常數

矩陣常數是陣列公式的一項元素。您可以輸入項目清單,然後以手動方式將清單圍在大括弧 ({ }) 之內,如下所示:

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

在本篇文章先前的討論內容中,一再強調建立任何陣列公式時,必須先按 CTRL+SHIFT+ENTER 鍵。由於矩陣常數是陣列公式中的一項元素,您要手動鍵入大括弧圍住這些矩陣常數,然後再使用 CTRL+SHIFT+ENTER 鍵來輸入整個公式。

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

與陣列公式相同,您可以使用矩陣常數與 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 4 5 6 7

  1. 在 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. [公式] 索引標籤的 [已定義之名稱] 群組中,按一下 [定義名稱]

[定義名稱] 對話方塊隨即出現。

  1. [名稱] 方塊中輸入「第一季」。
  2. [參照到] 方塊中,輸入以下常數 (記得要手動輸入大括弧):

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

對話方塊的內容應該看起來如下:

含公式的 [編輯名稱] 對話方塊

  1. 按一下 [確定]
  2. 在工作表上,選取三個空白儲存格的列。
  3. 輸入以下公式,然後按 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

求陣列中項目的平方值

  • 選取四欄寬三列高的空白儲存格區塊。
  • 輸入以下陣列公式,然後按 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. 開啟現有活頁簿或建立新活頁簿,並確認其中包含兩個空白的工作表。
  2. 複製下表中的資料,然後從 A1 儲存格開始貼入工作表中。
400   敏捷的   1 2 3 4
1200   棕色狐狸   5 6 7 8
3200   跳躍   9 10 11 12
475   懶散的   13 14 15 16
500   進階使用者          
2000              
600              
1700              
800              
2700              
  1. 完成的工作表看起來如下所示。

  2. 完整的範例資料

  3. 將第一張工作表命名為「資料」,而將第二張空白工作表命名為「陣列」。

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

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

從現有值建立陣列

  1. 在範例活頁簿中,選取 [陣列] 工作表。
  2. 選取儲存格範圍 C1 到 E3。
  3. 在資料編輯列中輸入以下公式,然後按 CTRL+SHIFT+ENTER 鍵:

=資料!E1:G3

您會看到以下結果。

陣列工作表上的結果

公式會連結資料工作表上存放於儲存格 E1 到 G3 的數值;此多儲存格陣列公式的另外一種方式是,在陣列工作表的每一個儲存格中放置唯一的公式,如下所示。


=資料!E1 =資料!F1 =資料!G1
=資料!E2 =資料!F2 =資料!G2
=資料!E3 =資料!F3 =資料!G3

如果變更了 [資料] 工作表上一些數值,這些變更就會出現在 [陣列] 工作表上。別忘了,若要變更 [資料] 工作表上任何數值,必須遵循編輯陣列公式的規則。如需有關這些規則的詳細資訊,請參閱瞭解陣列公式一節。

從現有的值建立矩陣常數

  1. [陣列] 工作表上,選取儲存格 C1 到 E3。
  2. 按 F2 鍵切換到編輯模式。
  3. 按 F9 將儲存格參照轉換為數值,Excel 就會將數值轉換成矩陣常數。
  4. 按 CTRL+SHIFT+ENTER 鍵來輸入矩陣常數做為陣列公式。

Excel 會用以下矩陣常數取代 =資料!E1:G3 陣列公式:

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

資料工作表與陣列工作表之間的連結已中斷,陣列公式已由矩陣常數取代。

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

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

  • [資料] 工作表上的 C7 儲存格中輸入以下公式,然後按 CTRL+SHIFT+ENTER 鍵:

=SUM(LEN(C1:C5))

數值 17 隨即出現在 C7 儲存格中。

在此範例中,LEN 函數傳回範圍中每一個儲存格的文字字串長度,SUM 函數再將這些數值加在一起,並將結果顯示於含該公式 C7 的儲存格中。

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

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

  1. [資料] 工作表上,選取儲存格 A12 到 A14。

這一組儲存格會保存由陣列公式傳回的結果。

  1. 在資料編輯列中輸入以下公式,然後按 CTRL+SHIFT+ENTER 鍵:

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

數值 400475500 隨即分別出現在儲存格 A12 到 A14 之中。

這個公式使用矩陣常數來評估 SMALL 函數三次,並傳回儲存格 A1:A10 範圍之內陣列中最小數值 (1)、第二個最小數值 (2),以及第三個最小數值 (3) 成員。若要找出更多數值,就在常數中加入更多引數,並將相等數目的結果儲存格加入 A12:A14 範圍中;您也可以使用其他函數來配合此公式運作,例如 SUMAVERAGE 函數。例如:

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

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

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

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

  1. [資料] 工作表上,選取儲存格 A12 到 A14。
  2. 按 DELETE 鍵,清除現有的公式,但是讓儲存格保持為選取狀態。
  3. 在資料編輯列中,輸入以下公式,然後按 CTRL+SHIFT+ENTER 鍵:

=LARGE(A1:A10,ROW(INDIRECT("1:3")))

數值 320027002000 隨即分別出現在儲存格 A12 到 A14 之中。

此時,如果對 ROWINDIRECT 函數稍微有點瞭解,可能會有幫助。您可以使用 ROW 函數來建立連續整數的陣列。例如,在練習用活頁簿中選取 10 個儲存格的空白欄,在 A1:A10 儲存格中輸入陣列公式,然後按 CTRL+SHIFT+ENTER 鍵:

=ROW(1:10)

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

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

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

現在檢閱一下先前使用過的公式-=LARGE(A1:A10,ROW(INDIRECT("1:3")))-從內括弧開始往外檢視:INDIRECT 函數會傳回一組文字值,在本範例中,是數值 1 到 3。ROW 函數再轉而產生三儲存格欄式陣列;LARGE 函數就會在儲存格範圍 A1:A10 中使用該數值,進行三次評估,ROW 函數每傳回一個儲存格參照進行一次評估,結果數值 3200、2700 和 2000 隨即傳回至三儲存格欄式陣列。如果要找出更多數值,就在 INDIRECT 函數中加入更大的儲存格範圍。

最後,您可以配合其他函數來使用此公式,如 SUMAVERAGE

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

本範例會尋找儲存格範圍中最長的文字字串。此公式只有在資料範圍包含單欄儲存格時才能順利運作。

  • [資料] 工作表上,清除 C7 儲存格中現有的公式,然後在該儲存格中輸入以下公式,再按 CTRL+SHIFT+ENTER 鍵:

=INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

值「進階使用者」隨即出現在 C7 儲存格中。

現在來檢閱一下公式,從內元素開始往外檢視。LEN 函數會傳回儲存格範圍 C1:C5 中的每個項目長度,MAX 函數則計算這些項目之中的最大值,此最大值對應於最長的文字字串,也就是 C5 儲存格。

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

(MAX(LEN(C1:C5))

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

LEN(C1:C5)

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

最後,INDEX 函數會採用如下引數:陣列,以及該陣列之內的列數和欄數。儲存格範圍 C1:C5 會提供您陣列,MATCH 函數提供儲存格位址,而最後的引數 (1) 則指定該值是來自陣列中的第一欄。

如需有關此處所討論函數的詳細資訊,請參閱 Excel 中的「說明」。

頁首 頁首

讓進階陣列公式開始運作

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

加總含錯誤值的範圍

如果嘗試要加總的範圍內包含錯誤值 (如, #N/A),Excel 中的 SUM 函數就無法運作。本範例為您示範如何在命名為「資料」而包含錯誤的範圍中加總數值。

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

公式會建立包含原始值但刪去任何錯誤值的新陣列,從內函數開始往外處理,ISERROR 函數會搜尋儲存格範圍 () 以尋找錯誤。如果指定的情況評估為 TRUE,IF 函數會傳回特定值,如果評估為 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。若要使用此公式,儲存格範圍必須有相同維度的相同大小:

=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 2007