• 了解數(shù)組公式
• 了解數(shù)組常量
• 使用基本數(shù)組公式
• 使用高級(jí)數(shù)組公式

了解數(shù)組公式

本節(jié)介紹數(shù)組公式并解釋如何對(duì)數(shù)組公式進(jìn)行輸入、編輯并解答疑難問(wèn)題。

為什么要使用數(shù)組公式？

如果您在 Excel 中使用過(guò)公式，想必知道利用公式可以執(zhí)行某些相當(dāng)復(fù)雜的操作。例如，可以基于給定的年數(shù)計(jì)算貸款總成本。但是，如果您確實(shí)想精通 Excel，還需要掌握如何使用數(shù)組公式。因?yàn)槭褂脭?shù)組公式可以執(zhí)行更多復(fù)雜的任務(wù)，例如：

• 計(jì)算包含在某個(gè)單元格區(qū)域中的字符數(shù)。
• 僅對(duì)滿足特定條件的數(shù)字求和，例如某一區(qū)域中的最小值或介于上限和下限之間的數(shù)字。
• 對(duì)一系列值中的每第 n 個(gè)值求和。

 注釋   數(shù)組公式也被稱為“CSE 公式”，這是因?yàn)榭梢园?Ctrl+Shift+Enter 在工作簿中輸入它們。

數(shù)組和數(shù)組公式簡(jiǎn)介

如果您有過(guò)一點(diǎn)編程經(jīng)驗(yàn)，可能碰到過(guò)術(shù)語(yǔ)數(shù)組。在本文中，數(shù)組是項(xiàng)的集合。在 Excel 中，這些項(xiàng)可以位于一行（稱為一維水平數(shù)組）中，也可位于一列（稱為一維垂直數(shù)組）中或多行和多列（二維數(shù)組）中。無(wú)法在 Excel 中創(chuàng)建三維數(shù)組或三維數(shù)組公式。

數(shù)組公式是指可以在數(shù)組的一項(xiàng)或多項(xiàng)上執(zhí)行多個(gè)計(jì)算的公式。數(shù)組公式可以返回多個(gè)結(jié)果，也可返回一個(gè)結(jié)果。例如，可以將數(shù)組公式放入單元格區(qū)域中，并使用數(shù)組公式計(jì)算列或行的小計(jì)。也可以將數(shù)組公式放入單個(gè)單元格中，然后計(jì)算單個(gè)量。位于多個(gè)單元格中的數(shù)組公式稱為多單元格公式，位于單個(gè)單元格中的數(shù)組公式稱為單個(gè)單元格公式。

下節(jié)中的示例將演示如何創(chuàng)建多單元格和單個(gè)單元格數(shù)組公式。

試一試

本練習(xí)演示如何使用多單元格數(shù)組公式和單個(gè)單元格數(shù)組公式來(lái)計(jì)算一組銷售數(shù)據(jù)。第一組操作是使用多單元格公式計(jì)算一組小計(jì)。第二組操作是使用單個(gè)單元格公式計(jì)算總計(jì)。

創(chuàng)建多單元格數(shù)組公式

1. 打開(kāi)一個(gè)新的空白工作簿。
2. 復(fù)制示例工作表數(shù)據(jù)，并將它粘貼到的新工作簿中（從單元格 A1 開(kāi)始）。

• 創(chuàng)建一個(gè)空白工作簿或工作表。
• 選擇“幫助”主題中的示例。

 注釋   不要選擇行或列標(biāo)題。

從“幫助”中選擇示例

• 按 Ctrl+C。
• 在工作表中，選擇單元格 A1，然后按 Ctrl+V。

---

3. 使用附近顯示的“粘貼選項(xiàng)”按鈕
   
   以匹配目標(biāo)格式。
4. 要將數(shù)組（單元格區(qū)域 C2 到 D11）中的值相乘，請(qǐng)選擇單元格 E2 到 E11，然后在編輯欄中輸入以下公式：

=C2:C11*D2:D11

5. 按 Ctrl+Shift+Enter。

Excel 使用大括號(hào) ({ }) 將公式括起，并將一個(gè)公式實(shí)例放入所選區(qū)域的每個(gè)單元格中。因?yàn)閳?zhí)行速度很快，所以您在 E 列中看到的是每位銷售人員每種轎車類型的總銷售額。

創(chuàng)建單個(gè)單元格數(shù)組公式

1. 在工作簿的單元格 A13 中，鍵入總銷售額。
2. 在單元格 B13 中，鍵入下面的公式并按 Ctrl+Shift+Enter：

=SUM(C2:C11*D2:D11)

這時(shí)，Excel 會(huì)將數(shù)組（單元格區(qū)域 C2 到 D11）中的值相乘，然后使用 SUM 函數(shù)將這些乘積相加。結(jié)果等于 ￥111,800 的總銷售額。本示例演示了此類公式的強(qiáng)大功能。例如，假定您有 15,000 行數(shù)據(jù)。您可以通過(guò)在單個(gè)單元格中創(chuàng)建數(shù)組公式來(lái)對(duì)部分或全部數(shù)據(jù)求和。

另外，請(qǐng)注意單個(gè)單元格公式（單元格 B13 中）與多單元格公式（單元格 E2 到 E11 中的公式）完全無(wú)關(guān)。這使得使用數(shù)組公式具有另一個(gè)優(yōu)點(diǎn) — 靈活性。您可以執(zhí)行任意次數(shù)的操作，例如更改列 E 中的公式或者刪除該列，這都不會(huì)影響單個(gè)單元格公式。

數(shù)組公式還具有以下優(yōu)點(diǎn)：

• 一致性    如果單擊 E2 下的任意單元格，您將看到相同的公式。這種一致性有助于確保更高的準(zhǔn)確性。
• 安全性    您不能覆蓋多單元格數(shù)組公式的組成部分，例如單擊單元格 E3 并按 Delete。您必須選擇整個(gè)單元格區(qū)域（E2 到 E11），然后更改整個(gè)數(shù)組的公式，否則只能讓數(shù)組保留原樣。作為一種附加安全措施，必須按 Ctrl+Shift+Enter 確認(rèn)對(duì)公式的更改。
• 文件大小較小    通?？梢允褂脝蝹€(gè)數(shù)組公式，而不必用多個(gè)中間公式。例如，為本次練習(xí)創(chuàng)建的工作簿使用單個(gè)數(shù)組公式在列 E 中計(jì)算結(jié)果。如果使用標(biāo)準(zhǔn)公式（例如 =C2*D2），則要使用 11 個(gè)不同的公式，而計(jì)算得出的結(jié)果并無(wú)不同。

數(shù)組公式語(yǔ)法簡(jiǎn)介

數(shù)組公式主要使用標(biāo)準(zhǔn)公式語(yǔ)法。它們都以等號(hào)開(kāi)始，可以在數(shù)組公式中使用任何內(nèi)置 Excel 函數(shù)。使用數(shù)組公式的主要不同之處在于，必須按 Ctrl+Shift+Enter 輸入公式。執(zhí)行此操作時(shí)，Excel 將用大括號(hào)將數(shù)組公式括起來(lái) — 如果您手動(dòng)鍵入大括號(hào)，公式將轉(zhuǎn)換為文本字符串，并且不起作用。

您還需要注意的是數(shù)組函數(shù)是一種簡(jiǎn)化形式。例如，前面使用的多單元格函數(shù)等效于：

=C2*D2
=C3*D3

等。單元格 B13 中的單個(gè)單元格公式集中了所有這些乘法運(yùn)算，另外還有將這些小計(jì)相加所需的算法：=E2+E3+E4 等。

輸入和更改數(shù)組公式原則

再?gòu)?qiáng)調(diào)一下創(chuàng)建數(shù)組公式的基本原則：每當(dāng)需要輸入或編輯數(shù)組公式時(shí)都要按 Ctrl+Shift+Enter。該原則適用于單個(gè)單元格公式和多單元格公式。

使用多單元格公式時(shí)，還需遵循以下原則：

• 必須在輸入公式之前選擇用于保存結(jié)果的單元格區(qū)域。在多單元格數(shù)組公式練習(xí)的第 3 步，您通過(guò)選擇單元格 E2 到 E11 執(zhí)行了此操作。
• 不能更改數(shù)組公式中單個(gè)單元格的內(nèi)容。要試試是否真的如此，可以選擇示例工作簿中的單元格 E3 再按 Delete。
• 可以移動(dòng)或刪除整個(gè)數(shù)組公式，但無(wú)法移動(dòng)或刪除其部分內(nèi)容。換言之，要縮減數(shù)組公式，需先刪除現(xiàn)有公式再重新開(kāi)始。

 提示   要?jiǎng)h除數(shù)組公式，請(qǐng)選擇整個(gè)公式（例如，=C2:C11*D2:D11），按 Delete，再按 Ctrl+Shift+Enter。

• 不能向多單元格數(shù)組公式中插入空白單元格或刪除其中的單元格。

擴(kuò)展數(shù)組公式

有時(shí)，可能需要擴(kuò)展數(shù)組公式。（記住不能縮減數(shù)組公式。）這個(gè)過(guò)程不復(fù)雜，但必須記住上節(jié)中列出的原則。

1. 在示例工作簿中，清除位于主表下的所有文本和單個(gè)單元格公式。
2. 將增加的數(shù)據(jù)行粘貼到工作簿中（從單元格 A12 開(kāi)始）。使用附近顯示的“粘貼選項(xiàng)”按鈕
   
   以匹配目標(biāo)格式。

3. 選擇包含當(dāng)前數(shù)組公式 (E2:E11) 的單元格區(qū)域，以及新數(shù)據(jù)旁邊的空單元格 (E12:E17)。也就是選擇單元格 E2:E17。
4. 按 F2 切換到編輯模式。
5. 在編輯欄中，將 C11 更改為 C17，將 D11 更改為 D17，然后按 Ctrl+Shift+Enter。Excel 會(huì)更新單元格 E2 到 E11 中的公式，并在新單元格 E12 到 E17 中放入該公式的實(shí)例。

使用數(shù)組公式的缺點(diǎn)

數(shù)組公式看起來(lái)似乎功能很神奇，但它們也存在某些缺點(diǎn)：

• 您可能有時(shí)會(huì)忘記按 Ctrl+Shift+Enter。請(qǐng)記住每當(dāng)輸入或編輯數(shù)組公式時(shí)都要按此組合鍵。
• 其他用戶可能不理解您的公式。數(shù)組公式相對(duì)復(fù)雜，因此如果其他人需要修改您的工作簿，您應(yīng)避免使用數(shù)組公式或者確信這些用戶知道如何更改您的公式。
• 大型數(shù)組公式可能會(huì)降低計(jì)算速度，具體取決于計(jì)算機(jī)的處理速度和內(nèi)存。

了解數(shù)組常量

本節(jié)介紹數(shù)組常量并解釋如何對(duì)它們進(jìn)行輸入、編輯并解答疑難問(wèn)題。

數(shù)組常量簡(jiǎn)介

數(shù)組常量是數(shù)組公式的組成部分。可以通過(guò)輸入一系列項(xiàng)然后手動(dòng)用大括號(hào) ({ }) 將該系列項(xiàng)括起來(lái)創(chuàng)建數(shù)組常量，類似于：

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

我們?cè)诒疚那懊鎻?qiáng)調(diào)過(guò)在創(chuàng)建數(shù)組公式時(shí)需要按 Ctrl+Shift+Enter。因?yàn)閿?shù)組常量是數(shù)組公式的組成部分，可以通過(guò)鍵入一對(duì)大括號(hào)手動(dòng)將常量括起來(lái)。然后使用 Ctrl+Shift+Enter 輸入整個(gè)公式。

如果使用逗號(hào)分隔（隔開(kāi)）各個(gè)項(xiàng)，將創(chuàng)建水平數(shù)組（一行）。如果使用分號(hào)分隔項(xiàng)，將創(chuàng)建垂直數(shù)組（一列）。要?jiǎng)?chuàng)建二維數(shù)組，應(yīng)在每行中使用逗號(hào)分隔項(xiàng)，并使用分號(hào)分隔每行。

使用數(shù)組公式時(shí)，可以將數(shù)組常量用于 Excel 提供的所有內(nèi)置函數(shù)中。下面幾節(jié)將解釋如何創(chuàng)建各種類型的常量以及如何將這些常量用于 Excel 中的函數(shù)。

創(chuàng)建一維和二維常量

下面將為您提供創(chuàng)建水平、垂直和二維常量的練習(xí)。

創(chuàng)建水平常量

1. 使用前一列所在工作簿，或啟動(dòng)新的工作簿。
2. 選擇單元格 A1 到 E1。
3. 在編輯欄中輸入下面的公式，然后按 Ctrl+Shift+Enter：

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

 注釋   在這種情況下，應(yīng)鍵入左大括號(hào)和右大括號(hào) ({ })。

將得到以下結(jié)果。

您可能在想為什么不簡(jiǎn)單地手動(dòng)鍵入這些數(shù)字。繼續(xù)學(xué)習(xí)下去將得到答案，本文后面部分的在公式中使用常量一節(jié)將演示使用數(shù)組常量的優(yōu)點(diǎn)。

創(chuàng)建垂直常量

1. 在工作簿中，選擇一列中的五個(gè)單元格。
2. 在編輯欄中輸入下面的公式并按 Ctrl+Shift+Enter：

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

將得到以下結(jié)果。

創(chuàng)建二維常量

1. 在工作簿中，選擇一個(gè)寬四列高三行的單元格塊。
2. 在編輯欄中輸入下面的公式，然后按 Ctrl+Shift+Enter：

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

將得到以下結(jié)果：

在公式中使用常量

現(xiàn)在您已經(jīng)熟悉如何輸入數(shù)組常量，下面是一個(gè)使用我們討論過(guò)的內(nèi)容的簡(jiǎn)單示例：

1. 打開(kāi)一張空白工作表。
2. 從單元格 A1 開(kāi)始復(fù)制下表。使用附近顯示的“粘貼選項(xiàng)”按鈕
   
   以匹配目標(biāo)格式。

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

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

請(qǐng)注意，Excel 用另一對(duì)大括號(hào)將常量括起來(lái)，這是因?yàn)槟且詳?shù)組公式的形式輸入該常量。

單元格 A3 中顯示 85。下節(jié)將討論此公式的計(jì)算方法。

數(shù)組常量語(yǔ)法簡(jiǎn)介

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

 函數(shù)

 存儲(chǔ)數(shù)組

 運(yùn)算符

 數(shù)組常量

括號(hào)內(nèi)的最后元素是數(shù)組常量：{1,2,3,4,5}。請(qǐng)注意，Excel 不會(huì)用大括號(hào)將數(shù)組常量括起來(lái)，您必須自己添加大括號(hào)。另外請(qǐng)不要忘記，在向數(shù)組公式添加常量后，需按 Ctrl+Shift+Enter 輸入公式。

因?yàn)?Excel 首先對(duì)括號(hào)括起來(lái)的表達(dá)式執(zhí)行運(yùn)算，接下來(lái)參與運(yùn)算的兩個(gè)元素是存儲(chǔ)在工作簿 (A1:E1) 中的值以及運(yùn)算符。此時(shí)，公式將存儲(chǔ)數(shù)組中的值與常量中對(duì)應(yīng)的值相乘。它等價(jià)于：

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

最后，SUM 函數(shù)將這些值相加，和 85 顯示在單元格 A3 中：

要避免使用存儲(chǔ)數(shù)組并讓運(yùn)算完全位于內(nèi)存中，可用另一個(gè)數(shù)組常量來(lái)替換存儲(chǔ)數(shù)組：

=SUM({3,4,5,6,7}*{1,2,3,4,5})

要嘗試此操作，請(qǐng)復(fù)制函數(shù)，并在工作簿中選擇一個(gè)空白單元格，將該公式粘貼到編輯欄中，然后按 Ctrl+Shift+Enter。將得到與上述練習(xí)中使用數(shù)組公式 =SUM(A1:E1*{1,2,3,4,5}) 相同的結(jié)果。

常量中可以使用的元素

數(shù)組常量可以包含數(shù)字、文本、邏輯值（例如 TRUE 和 FALSE）和錯(cuò)誤值（例如 #N/A）?？梢允褂谜麛?shù)、小數(shù)和科學(xué)計(jì)數(shù)格式表示的數(shù)字。如果包括文本，則必須使用雙引號(hào) (") 將文本括起來(lái)。

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

命名數(shù)組常量

使用數(shù)組常量的最佳方式是對(duì)它們進(jìn)行命名。命名的數(shù)組常量更易于使用，并且對(duì)于初學(xué)者來(lái)說(shuō)，它們可以降低數(shù)組公式的復(fù)雜性。要命名數(shù)組常量并在公式中使用它們，請(qǐng)執(zhí)行以下操作：

1. 在“公式”選項(xiàng)卡上的“定義的名稱”組中，單擊“定義名稱”。

顯示“定義名稱”對(duì)話框。

2. 在“名稱”框中，鍵入第1季度。
3. 在“引用位置”框中，輸入下面的常量（記住要手動(dòng)鍵入大括號(hào)）：

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

對(duì)話框中的內(nèi)容應(yīng)類似如下：

4. 單擊“確定”。
5. 在工作表中，選擇一行中的三個(gè)空單元格。
6. 鍵入下面的公式，然后按 Ctrl+Shift+Enter。

=第1季度

將得到以下結(jié)果。

將命名常量用作數(shù)組公式時(shí)，切記要輸入等號(hào)。如果未輸入等號(hào)，Excel 會(huì)將該數(shù)組解釋為文本字符串。最后，請(qǐng)記住可以使用文本和數(shù)字的組合。

數(shù)組常量疑難解答

當(dāng)數(shù)組常量不起作用時(shí)請(qǐng)檢查下面的問(wèn)題：

• 某些元素可能未使用正確的字符分隔。如果遺漏了逗號(hào)或分號(hào)，或者如果將它們放錯(cuò)了位置，將無(wú)法正確創(chuàng)建數(shù)組常量或者可能顯示一條警告消息。
• 選擇的單元格區(qū)域可能與常量中的元素個(gè)數(shù)不匹配。例如，如果在一列中選擇六個(gè)單元格用于要占用五個(gè)單元格的常量，則會(huì)在空單元格中顯示 #N/A 錯(cuò)誤值。反過(guò)來(lái)，如果選擇的單元格太少，Excel 將忽略沒(méi)有對(duì)應(yīng)單元格的值。

數(shù)組常量工作方式

下面的示例演示可以將數(shù)組常量用于數(shù)組公式的幾種方式。某些示例使用 TRANSPOSE 函數(shù)將行轉(zhuǎn)換為列，或?qū)⒘修D(zhuǎn)換為行。

乘以數(shù)組中的各項(xiàng)

1. 選擇一個(gè)寬四列高三行的空單元格塊。
2. 鍵入下面的公式，然后按 Ctrl+Shift+Enter。

={1,2,3,4;5,6,7,8;9,10,11,12}*2

對(duì)數(shù)組中的各項(xiàng)求平方

• 選擇一個(gè)寬四列高三行的空單元格塊。
• 鍵入下面的數(shù)組公式，然后按 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}

或者，輸入下面的數(shù)組公式，它使用脫字符號(hào) (^)：

={1,2,3,4;5,6,7,8;9,10,11,12}^2

轉(zhuǎn)置一維行

1. 選擇一列中的五個(gè)空白單元格。
2. 鍵入下面的公式，然后按 Ctrl+Shift+Enter：

=TRANSPOSE({1,2,3,4,5})

即使輸入的是水平數(shù)組常量，TRANSPOSE 函數(shù)也會(huì)將該數(shù)組常量轉(zhuǎn)換為列。

轉(zhuǎn)置一維列

1. 選擇一列中的五個(gè)空白單元格。
2. 輸入下面的公式，然后按 Ctrl+Shift+Enter：

=TRANSPOSE({1;2;3;4;5})

即使輸入的是垂直數(shù)組常量，TRANSPOSE 函數(shù)也會(huì)將該常量轉(zhuǎn)換為行。

轉(zhuǎn)置二維常量

1. 選擇一個(gè)寬三列高四行的單元格塊。
2. 輸入下面的常量，然后按 Ctrl+Shift+Enter。

=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

TRANSPOSE 函數(shù)將各行轉(zhuǎn)換為一系列的列。

使用基本數(shù)組公式

本節(jié)提供基本數(shù)組公式的示例。

入門

使用本節(jié)中的數(shù)據(jù)創(chuàng)建兩個(gè)示例工作表。

1. 打開(kāi)一個(gè)現(xiàn)有工作簿或創(chuàng)建一個(gè)新工作簿，并確保其包含兩個(gè)空工作表。
2. 復(fù)制下表中的數(shù)據(jù)，并將它粘貼到工作表中（從單元格 A1 開(kāi)始）。

3. 完成的工作表應(yīng)如下所示。

   ---

4. 

   ---

5. 將第一個(gè)工作表命名為數(shù)據(jù)，第二個(gè)空白工作表命名為數(shù)組。

從現(xiàn)有值創(chuàng)建數(shù)組和數(shù)組常量

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

從現(xiàn)有值創(chuàng)建數(shù)組

1. 在示例工作簿中，選擇數(shù)組工作表。
2. 選擇單元格區(qū)域 C1 到 E3。
3. 在編輯欄中輸入下面的公式，然后按 Ctrl+Shift+Enter：

=數(shù)據(jù)!E1:G3

將得到以下結(jié)果。

該公式鏈接到數(shù)據(jù)工作表的單元格 E1 到 G3 中存儲(chǔ)的值。執(zhí)行此多單元格數(shù)組公式的另一個(gè)方法是在數(shù)組工作表的每個(gè)單元格中放入唯一的公式，如下所示。

如果更改了數(shù)據(jù)工作表中的某些值，這些更改將顯示在數(shù)組工作表中。請(qǐng)注意，更改數(shù)據(jù)工作表中的任何值都必須遵循數(shù)組公式的編輯原則。有關(guān)這些原則的詳細(xì)信息，請(qǐng)參見(jiàn)了解數(shù)組公式一節(jié)。

從現(xiàn)有值創(chuàng)建數(shù)組常量

1. 在數(shù)組工作表上，選擇單元格 C1 到 E3。
2. 按 F2 切換到編輯模式。
3. 按 F9 將單元格引用轉(zhuǎn)換為值。Excel 將這些值轉(zhuǎn)換為數(shù)組常量。
4. 按 Ctrl+Shift+Enter 輸入數(shù)組常量作為數(shù)組公式。

Excel 使用下面的數(shù)組常量替換 =數(shù)據(jù)!E1:G3 數(shù)組公式：

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

數(shù)據(jù)與數(shù)組工作表之間的鏈接已破壞，數(shù)組公式已為數(shù)組常量替代。

在單元格區(qū)域中對(duì)字符計(jì)數(shù)

下面的示例演示如何計(jì)算單元格區(qū)域中的字符數(shù)（包括空格）。

• 在數(shù)據(jù)工作表的單元格 C7 中輸入下面的公式，然后按 Ctrl+Shift+Enter：

=SUM(LEN(C1:C5))

單元格 C7 中顯示值 25。

這樣，LEN 函數(shù)返回該區(qū)域的每個(gè)單元格中的每個(gè)文本字符串的長(zhǎng)度。然后 SUM 函數(shù)將這些值相加，并在包含該公式的單元格 C7 中顯示結(jié)果。

查找出區(qū)域內(nèi)的 n 個(gè)最小值

本示例演示如何查找單元格區(qū)域內(nèi)的三個(gè)最小值。

1. 在數(shù)據(jù)工作表上，選擇單元格 A12 到 A14。

這組單元格將保留數(shù)組公式返回的結(jié)果。

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

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

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

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

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

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

查找出區(qū)域中的 n 個(gè)最大值

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

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

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

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

現(xiàn)在，了解一點(diǎn) ROW 和 INDIRECT 函數(shù)可能會(huì)有所幫助?？梢允褂?ROW 函數(shù)創(chuàng)建連續(xù)的整數(shù)數(shù)組。例如，在練習(xí)工作簿中選擇一個(gè)包含 10 個(gè)單元格的空列，在單元格 A1:A10 中輸入下面的數(shù)組公式，然后按 Ctrl+Shift+Enter：

=ROW(1:10)

此公式創(chuàng)建由 10 個(gè)連續(xù)整數(shù)組成的一列。為了查看可能的問(wèn)題，請(qǐng)?jiān)诎瑪?shù)組公式的區(qū)域上面插入一行（即第 1 行上）。Excel 調(diào)整行引用，并且此公式生成從 2 到 11 的整數(shù)。要修正該問(wèn)題，可以向該公式添加 INDIRECT 函數(shù)：

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

INDIRECT 函數(shù)使用文本字符串作為參數(shù)（這是區(qū)域 1:10 由雙引號(hào)括起的原因）。當(dāng)插入行或移動(dòng)數(shù)組公式時(shí)，Excel 不會(huì)調(diào)整文本值。因此，此 ROW 函數(shù)總是生成所需的整數(shù)數(shù)組。

讓我們以前面使用過(guò)的公式為例 — =LARGE(A1:A10,ROW(INDIRECT("1:3"))) — 從內(nèi)層的括號(hào)開(kāi)始向外計(jì)算：INDIRECT 函數(shù)返回一組文本值，在這種情況下，為值 1 到 3。ROW 函數(shù)依次生成包含三個(gè)單元格的縱欄式數(shù)組。 LARGE 函數(shù)使用單元格 A1:A10 中的值，并且它計(jì)算三次，每次都對(duì)應(yīng)于 ROW 函數(shù)返回的每個(gè)引用。值 3200、2700 和 2000 返回到這三個(gè)單元格縱欄式數(shù)組中。如果要查找更多值，可以向 INDIRECT 函數(shù)添加更多的單元格區(qū)域。

最后，可以將此公式與其他函數(shù)一起使用，例如 SUM 和 AVERAGE。

查找單元格區(qū)域中的最長(zhǎng)文本字符串

本示例查找單元格區(qū)域中的最長(zhǎng)文本字符串。本公式僅在數(shù)據(jù)區(qū)域包含單列單元格時(shí)適用。

• 在數(shù)據(jù)工作表上，清除單元格 C7 中的已有公式，并在該單元格中輸入下面的公式，然后按 Ctrl+Shift+Enter：

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

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

讓我們以此公式為例，從內(nèi)層元素開(kāi)始向外進(jìn)行運(yùn)算。LEN 函數(shù)返回單元格區(qū)域 C1:C5 中的每個(gè)項(xiàng)的長(zhǎng)度。MAX 函數(shù)計(jì)算這些項(xiàng)中的最大值，它對(duì)應(yīng)于最長(zhǎng)文本字符串，位于單元格 C3 中。

下面的計(jì)算稍微有點(diǎn)復(fù)雜。MATCH 函數(shù)計(jì)算包含最長(zhǎng)文本字符串的單元格的偏移量（相對(duì)位置）。為此，需要三個(gè)參數(shù)：分別是查閱值、查閱數(shù)組和匹配類型。MATCH 函數(shù)在查閱數(shù)組中搜索指定的查閱值。在這種情況下，查閱值為最長(zhǎng)的文本字符串：

(MAX(LEN(C1:C5))

并且該字符串位于此數(shù)組中：

LEN(C1:C5)

匹配類型參數(shù)為 0。匹配類型可以包含值 1、0 或 -1。如果指定 1，MATCH 返回小于或等于查閱值的最大值。如果指定 0，MATCH 返回正好等于查閱值的第一個(gè)值。如果指定 -1，MATCH 查找出大于或等于指定查閱值的最小值。如果未指定匹配類型，Excel 會(huì)采用值 1。

最后，INDEX 函數(shù)采用這些參數(shù)：數(shù)組以及該數(shù)組內(nèi)的行號(hào)和列號(hào)。單元格區(qū)域 C1:C5 提供該數(shù)組，MATCH 函數(shù)提供單元格地址，最后的參數(shù) (1) 指定該值來(lái)自數(shù)組的第一列。

有關(guān)此處討論的函數(shù)的詳細(xì)信息，請(qǐng)參見(jiàn) Excel 幫助。

使用高級(jí)數(shù)組公式

本節(jié)提供高級(jí)數(shù)組公式的示例。

對(duì)包含錯(cuò)誤值的區(qū)域求和

當(dāng)試圖對(duì)包含錯(cuò)誤值（例如 #N/A）的區(qū)域求和時(shí)，Excel 中的 SUM 函數(shù)不再適用。本示例演示如何對(duì)包含錯(cuò)誤的命名為“數(shù)據(jù)”的區(qū)域中的值求和。

=SUM(IF(ISERROR(數(shù)據(jù)),"",數(shù)據(jù)))

該公式創(chuàng)建一個(gè)新數(shù)組，包含除錯(cuò)誤值以外的原始值。從內(nèi)層函數(shù)開(kāi)始向外運(yùn)算，ISERROR 函數(shù)在單元格區(qū)域 (數(shù)據(jù)) 中搜索錯(cuò)誤。IF 函數(shù)在指定的條件計(jì)算結(jié)果為 TRUE 時(shí)返回指定值，在計(jì)算結(jié)果為 FALSE 時(shí)返回另一個(gè)值。在此處，它為所有錯(cuò)誤值返回空字符串 ("")，因?yàn)樗鼈兊挠?jì)算結(jié)果為 TRUE，并且返回該區(qū)域 (數(shù)據(jù)) 中的其他值（因?yàn)檫@些值計(jì)算結(jié)果為 FALSE，表示它們不包含錯(cuò)誤值）。接著 SUM 函數(shù)計(jì)算篩選出的數(shù)組的總和。

計(jì)算區(qū)域中錯(cuò)誤值個(gè)數(shù)

本示例與上面的公式相似，但它返回名為“數(shù)據(jù)”的區(qū)域中的錯(cuò)誤值個(gè)數(shù)，而不是將錯(cuò)誤值篩選掉：

=SUM(IF(ISERROR(數(shù)據(jù)),1,0))

該公式創(chuàng)建一個(gè)數(shù)組，它為包含錯(cuò)誤的單元格包含值 1，為不包含錯(cuò)誤的單元格包含值 0。可以簡(jiǎn)化該公式，并達(dá)到相同的結(jié)果，方法是移除 IF 函數(shù)的第三個(gè)參數(shù)，如下所示：

=SUM(IF(ISERROR(數(shù)據(jù)),1))

如果未指定該參數(shù)，IF 函數(shù)在單元格不包含錯(cuò)誤值時(shí)返回 FALSE?？梢赃M(jìn)一步簡(jiǎn)化該公式：

=SUM(IF(ISERROR(數(shù)據(jù))*1))

此公式版本可以執(zhí)行計(jì)算是因?yàn)?TRUE*1=1 并且 FALSE*1=0。

條件求和

可能需要根據(jù)條件對(duì)值求和。例如，此數(shù)組公式僅對(duì)名為“銷售量”的區(qū)域中的正值求和：

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

IF 函數(shù)創(chuàng)建正值和 false 值數(shù)組。SUM 函數(shù)實(shí)際上將忽略 false 值，因?yàn)?0+0=0。在此公式中使用的單元格區(qū)域可以由任意數(shù)量的行和列組成。

還可以對(duì)滿足多個(gè)條件的值求和。例如，下面的數(shù)組公式計(jì)算大于 0 并且小于等于 5 的值：

=SUM((銷售量>0)*(銷售量<=5)*(銷售量))

請(qǐng)注意，如果區(qū)域中包含一個(gè)或多個(gè)非數(shù)字單元格，此公式將返回錯(cuò)誤。

還可以創(chuàng)建使用 OR 條件的數(shù)組公式。例如，可以對(duì)小于 5 和大于 15 的值求和：

=SUM(IF((銷售量<5)+(銷售量>15),銷售量))

IF 函數(shù)查找所有小于 5 和大于 15 的值，然后將這些值傳遞給 SUM 函數(shù)。

 要點(diǎn)   不能在數(shù)組公式中直接使用 AND 和 OR 函數(shù)，因?yàn)檫@些函數(shù)返回單一結(jié)果，TRUE 或 FALSE，而數(shù)組函數(shù)需要結(jié)果數(shù)組?？梢酝ㄟ^(guò)使用上一公式中顯示的邏輯來(lái)解決這一問(wèn)題。也就是，對(duì)滿足 OR 或 AND 條件的值執(zhí)行加法或乘法等算術(shù)運(yùn)算。

計(jì)算零以外的平均值

本示例演示當(dāng)您需要對(duì)區(qū)域中的值求平均值時(shí)，如何從該區(qū)域中移除零。下面的公式使用名為“銷售量”的數(shù)據(jù)區(qū)域：

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

IF 函數(shù)創(chuàng)建不等于 0 的值數(shù)組，然后將這些值傳遞給 AVERAGE 函數(shù)。

計(jì)算兩個(gè)單元格區(qū)域中的不同值個(gè)數(shù)

此數(shù)組公式對(duì)名為“我的數(shù)據(jù)”和“您的數(shù)據(jù)”的兩個(gè)單元格區(qū)域中的值進(jìn)行比較并返回它們之間不同值的個(gè)數(shù)。如果這兩個(gè)區(qū)域中的內(nèi)容完全相同，此公式將返回 0。要使用此公式，單元格區(qū)域必須大小相同并且包含相同的維數(shù)：

=SUM(IF(我的數(shù)據(jù)=您的數(shù)據(jù),0,1))

此公式創(chuàng)建與正比較的區(qū)域大小相同的新數(shù)組。IF 函數(shù)使用值 0 和值 1 填充數(shù)組（0 表示單元格不匹配，1 表示單元格匹配）。然后 SUM 函數(shù)返回該數(shù)組中的值的和。

可以如下所示簡(jiǎn)化該公式：

=SUM(1*(我的數(shù)據(jù)<>您的數(shù)據(jù)))

與計(jì)算區(qū)域中的錯(cuò)誤值的公式相似，此公式版本可以執(zhí)行計(jì)算是因?yàn)?TRUE*1=1 并且 FALSE*1=0。

查找區(qū)域中最大值的位置

此數(shù)組公式返回名為“數(shù)據(jù)”的單列區(qū)域中的最大值所在的行號(hào)：

=MIN(IF(數(shù)據(jù)=MAX(數(shù)據(jù)),ROW(數(shù)據(jù)),""))

IF 函數(shù)創(chuàng)建與“數(shù)據(jù)”區(qū)域?qū)?yīng)的新數(shù)組。如果對(duì)應(yīng)的單元格包含區(qū)域中的最大值，則此數(shù)組包含該行號(hào)。否則，此數(shù)組包含空字符串 ("")。MIN 函數(shù)使用此新數(shù)組作為它的第二個(gè)參數(shù)并且返回與“數(shù)據(jù)”區(qū)域中最大值的行號(hào)相對(duì)應(yīng)的最小值。如果“數(shù)據(jù)”區(qū)域包含完全相同的最大值，該公式返回第一個(gè)值的行號(hào)。

如果要返回最大值的實(shí)際單元格地址，請(qǐng)使用下面的公式：

=ADDRESS(MIN(IF(數(shù)據(jù)=MAX(數(shù)據(jù)),ROW(數(shù)據(jù)),"")),COLUMN(數(shù)據(jù)))