數組公式指南和示例

 Microsoft Office Excel 2007

要成為一名 Excel 高級用戶，您需要知道如何使用數組公式，它能執行非數組公式所不能執行的計算。下文是基於 Colin Wilcox 撰寫的 Excel 高級用戶專欄系列，並改編自 John Walkenbach 撰寫的《Excel 2002 公式》（英文）一書中的第 14 章和第 15 章，John Walkenbach 是一位 Excel MVP。要了解 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

---

3. 使用附近顯示的「粘貼選項」按鈕

   以匹配目標格式。

4. 要將數組（單元格區域 C2 到 D11）中的值相乘，請選擇單元格 E2 到 E11，然後在編輯欄中輸入以下公式：

=C2:C11*D2:D11

5. 按 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

---

3. 選擇包含當前數組公式 (E2:E11) 的單元格區域，以及新數據旁邊的空單元格 (E12:E17)。也就是選擇單元格 E2:E17。
4. 按 F2 切換到編輯模式。
5. 在編輯欄中，將 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

---

3. 在單元格 A3 中，輸入下面的公式，然後按 Ctrl+Shift+Enter：

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

請注意，Excel 用另一對大括弧將常量括起來，這是因為您是以數組公式的形式輸入該常量。

---

---

單元格 A3 中顯示 85。下節將討論此公式的計算方法。

數組常量語法簡介

剛才使用的公式包含若干部分。

---

 函數

 存儲數組

 運算符

 數組常量

---

括弧內的最後元素是數組常量：{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）。可以使用整數、小數和科學計數格式表示的數字。如果包括文本，則必須使用雙引號 (") 將文本括起來。

數組常量不能包含其他數組、公式或函數。換言之，它們只能包含以逗號或分號分隔的文本或數字。當您輸入如下所示的公式時，Excel 將顯示警告消息：{1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)}。另外，數值不能包含百分號、貨幣符號、逗號或圓括弧。

命名數組常量

使用數組常量的最佳方式是對它們進行命名。命名的數組常量更易於使用，並且對於初學者來說，它們可以降低數組公式的複雜性。要命名數組常量並在公式中使用它們，請執行以下操作：

1. 在「公式」選項卡上的「定義的名稱」組中，單擊「定義名稱」。

顯示「定義名稱」對話框。

2. 在「名稱」框中，鍵入第1季度。
3. 在「引用位置」框中，輸入下面的常量（記住要手動鍵入大括弧）：

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

對話框中的內容應類似如下：

4. 單擊「確定」。
5. 在工作表中，選擇一行中的三個空單元格。
6. 鍵入下面的公式，然後按 Ctrl+Shift+Enter。

=第1季度

將得到以下結果。

---

---

將命名常量用作數組公式時，切記要輸入等號。如果未輸入等號，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

3. 完成的工作表應如下所示。
   

   ---

4. 
   

   ---

5. 將第一個工作表命名為數據，第二個空白工作表命名為數組。

從現有值創建數組和數組常量

下面的示例介紹如何使用數組公式在不同工作表的單元格區域之間創建鏈接。還演示如何使用同一組值創建數組常量。

從現有值創建數組

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

單元格 C7 中顯示值 25。

這樣，LEN 函數返回該區域的每個單元格中的每個文本字元串的長度。然後 SUM 函數將這些值相加，並在包含該公式的單元格 C7 中顯示結果。

查找出區域內的 n 個最小值

本示例演示如何查找單元格區域內的三個最小值。

1. 在數據工作表上，選擇單元格 A12 到 A14。

這組單元格將保留數組公式返回的結果。

2. 在編輯欄中輸入下面的公式，然後按 Ctrl+Shift+Enter：

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

值 400、 475 和 500 將分別顯示在單元格 A12 到 A14 中。

此公式使用數組常量計算 SMALL 函數三次，並返回單元格 A1:A10 中包含的數組中的最小值 (1)、次小值 (2) 和第三小值 (3)。要查找出更多的值，可以向該常量添加更多參數並向 A12:A14 區域添加同等個數的結果單元格。還可以對此公式使用其他函數，例如 SUM 或 AVERAGE。例如：

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

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

查找出區域中的 n 個最大值

要找出區域中的多個最大值，可以使用 LARGE 函數替代 SMALL 函數。此外，下面的示例使用 ROW 和 INDIRECT 函數。

1. 在數據工作表上，選擇單元格 A12 到 A14。
2. 按 Delete 清除已有公式但保持單元格處於選中狀態。
3. 在編輯欄上輸入下面的公式，並按 Ctrl+Shift+Enter：

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

值 3200、2700 和 2000 分別顯示在單元格 A12 到 A14 中。

現在，了解一點 ROW 和 INDIRECT 函數可能會有所幫助。可以使用 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 函數添加更多的單元格區域。

最後，可以將此公式與其他函數一起使用，例如 SUM 和 AVERAGE。

查找單元格區域中的最長文本字元串

本示例查找單元格區域中的最長文本字元串。本公式僅在數據區域包含單列單元格時適用。

• 在數據工作表上，清除單元格 C7 中的已有公式，並在該單元格中輸入下面的公式，然後按 Ctrl+Shift+Enter：

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

值不知天上宮闕顯示在單元格 C7 中。

讓我們以此公式為例，從內層元素開始向外進行運算。LEN 函數返回單元格區域 C1:C5 中的每個項的長度。MAX 函數計算這些項中的最大值，它對應於最長文本字元串，位於單元格 C3 中。

下面的計算稍微有點複雜。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 函數在單元格區域 (數據) 中搜索錯誤。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 函數創建正值和 false 值數組。SUM 函數實際上將忽略 false 值，因為 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。要使用此公式，單元格區域必須大小相同並且包含相同的維數：

=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(數據))

推薦閱讀：