本節介紹數組公式并解釋如何對數組公式進(jìn)行輸入、編輯并解答疑難問(wèn)題。
如果您在 Excel 中使用過(guò)公式，想必知道利用公式可以執行某些相當復雜的操作。例如，可以基于給定的年數計算貸款總成本。但是，如果您確實(shí)想精通 Excel，還需要掌握如何使用數組公式。因為使用數組公式可以執行更多復雜的任務(wù)，例如：
注釋 數組公式也被稱(chēng)為“CSE 公式”，這是因為可以按 Ctrl+Shift+Enter 在工作簿中輸入它們。
如果您有過(guò)一點(diǎn)編程經(jīng)驗，可能碰到過(guò)術(shù)語(yǔ)數組。在本文中，數組是項的集合。在 Excel 中，這些項可以位于一行（稱(chēng)為一維水平數組）中，也可位于一列（稱(chēng)為一維垂直數組）中或多行和多列（二維數組）中。無(wú)法在 Excel 中創(chuàng )建三維數組或三維數組公式。
數組公式是指可以在數組的一項或多項上執行多個(gè)計算的公式。數組公式可以返回多個(gè)結果，也可返回一個(gè)結果。例如，可以將數組公式放入單元格區域中，并使用數組公式計算列或行的小計。也可以將數組公式放入單個(gè)單元格中，然后計算單個(gè)量。位于多個(gè)單元格中的數組公式稱(chēng)為多單元格公式，位于單個(gè)單元格中的數組公式稱(chēng)為單個(gè)單元格公式。
下節中的示例將演示如何創(chuàng )建多單元格和單個(gè)單元格數組公式。
本練習演示如何使用多單元格數組公式和單個(gè)單元格數組公式來(lái)計算一組銷(xiāo)售數據。第一組操作是使用多單元格公式計算一組小計。第二組操作是使用單個(gè)單元格公式計算總計。
注釋 不要選擇行或列標題。
| 銷(xiāo)售人員 | 汽車(chē)類(lèi)型 | 銷(xiāo)售數量 | 單價(jià) | 總銷(xiāo)售額 |
|---|---|---|---|---|
| 劉鵬 | 四門(mén)轎車(chē) | 5 | 2200 | |
| 雙門(mén)轎車(chē) | 4 | 1800 | ||
| 尹歌 | 四門(mén)轎車(chē) | 6 | 2300 | |
| 雙門(mén)轎車(chē) | 8 | 1700 | ||
| 林彩瑜 | 四門(mén)轎車(chē) | 3 | 2000 | |
| 雙門(mén)轎車(chē) | 1 | 1600 | ||
| 潘杰 | 四門(mén)轎車(chē) | 9 | 2150 | |
| 雙門(mén)轎車(chē) | 5 | 1950 | ||
| 施德福 | 四門(mén)轎車(chē) | 6 | 2250 | |
| 雙門(mén)轎車(chē) | 8 | 2000 |
=C2:C11*D2:D11
Excel 使用大括號 ({ }) 將公式括起，并將一個(gè)公式實(shí)例放入所選區域的每個(gè)單元格中。因為執行速度很快，所以您在 E 列中看到的是每位銷(xiāo)售人員每種轎車(chē)類(lèi)型的總銷(xiāo)售額。
=SUM(C2:C11*D2:D11)
這時(shí)，Excel 會(huì )將數組（單元格區域 C2 到 D11）中的值相乘，然后使用 SUM 函數將這些乘積相加。結果等于 ￥111,800 的總銷(xiāo)售額。本示例演示了此類(lèi)公式的強大功能。例如，假定您有 15,000 行數據。您可以通過(guò)在單個(gè)單元格中創(chuàng )建數組公式來(lái)對部分或全部數據求和。
另外，請注意單個(gè)單元格公式（單元格 B13 中）與多單元格公式（單元格 E2 到 E11 中的公式）完全無(wú)關(guān)。這使得使用數組公式具有另一個(gè)優(yōu)點(diǎn) — 靈活性。您可以執行任意次數的操作，例如更改列 E 中的公式或者刪除該列，這都不會(huì )影響單個(gè)單元格公式。
數組公式還具有以下優(yōu)點(diǎn)：
數組公式主要使用標準公式語(yǔ)法。它們都以等號開(kāi)始，可以在數組公式中使用任何內置 Excel 函數。使用數組公式的主要不同之處在于，必須按 Ctrl+Shift+Enter 輸入公式。執行此操作時(shí)，Excel 將用大括號將數組公式括起來(lái) — 如果您手動(dòng)鍵入大括號，公式將轉換為文本字符串，并且不起作用。
您還需要注意的是數組函數是一種簡(jiǎn)化形式。例如，前面使用的多單元格函數等效于：
=C2*D2
=C3*D3
等。單元格 B13 中的單個(gè)單元格公式集中了所有這些乘法運算，另外還有將這些小計相加所需的算法：=E2+E3+E4 等。
再強調一下創(chuàng )建數組公式的基本原則：每當需要輸入或編輯數組公式時(shí)都要按 Ctrl+Shift+Enter。該原則適用于單個(gè)單元格公式和多單元格公式。
使用多單元格公式時(shí)，還需遵循以下原則：
提示 要刪除數組公式，請選擇整個(gè)公式（例如，=C2:C11*D2:D11），按 Delete，再按 Ctrl+Shift+Enter。
有時(shí)，可能需要擴展數組公式。（記住不能縮減數組公式。）這個(gè)過(guò)程不復雜，但必須記住上節中列出的原則。
| 潭思琪 | 四門(mén)轎車(chē) | 6 | 2500 |
|---|---|---|---|
| 雙門(mén)轎車(chē) | 7 | 1900 | |
| 王偉 | 四門(mén)轎車(chē) | 4 | 2200 |
| 雙門(mén)轎車(chē) | 3 | 2000 | |
| 楊威 | 四門(mén)轎車(chē) | 8 | 2300 |
| 雙門(mén)轎車(chē) | 8 | 2100 |
數組公式看起來(lái)似乎功能很神奇，但它們也存在某些缺點(diǎn)：
本節介紹數組常量并解釋如何對它們進(jìn)行輸入、編輯并解答疑難問(wèn)題。
數組常量是數組公式的組成部分?？梢酝ㄟ^(guò)輸入一系列項然后手動(dòng)用大括號 ({ }) 將該系列項括起來(lái)創(chuàng )建數組常量，類(lèi)似于：
={1,2,3,4,5}
我們在本文前面強調過(guò)在創(chuàng )建數組公式時(shí)需要按 Ctrl+Shift+Enter。因為數組常量是數組公式的組成部分，可以通過(guò)鍵入一對大括號手動(dòng)將常量括起來(lái)。然后使用 Ctrl+Shift+Enter 輸入整個(gè)公式。
如果使用逗號分隔（隔開(kāi)）各個(gè)項，將創(chuàng )建水平數組（一行）。如果使用分號分隔項，將創(chuàng )建垂直數組（一列）。要創(chuàng )建二維數組，應在每行中使用逗號分隔項，并使用分號分隔每行。
使用數組公式時(shí)，可以將數組常量用于 Excel 提供的所有內置函數中。下面幾節將解釋如何創(chuàng )建各種類(lèi)型的常量以及如何將這些常量用于 Excel 中的函數。
下面將為您提供創(chuàng )建水平、垂直和二維常量的練習。
={1,2,3,4,5}
注釋 在這種情況下，應鍵入左大括號和右大括號 ({ })。
將得到以下結果。
您可能在想為什么不簡(jiǎn)單地手動(dòng)鍵入這些數字。繼續學(xué)習下去將得到答案，本文后面部分的在公式中使用常量一節將演示使用數組常量的優(yōu)點(diǎn)。
={1;2;3;4;5}
將得到以下結果。
={1,2,3,4;5,6,7,8;9,10,11,12}
將得到以下結果：
現在您已經(jīng)熟悉如何輸入數組常量，下面是一個(gè)使用我們討論過(guò)的內容的簡(jiǎn)單示例：
| 3 | 4 | 5 | 6 | 7 |
=SUM(A1:E1*{1,2,3,4,5})
請注意，Excel 用另一對大括號將常量括起來(lái)，這是因為您是以數組公式的形式輸入該常量。
單元格 A3 中顯示 85。下節將討論此公式的計算方法。
剛才使用的公式包含若干部分。
括號內的最后元素是數組常量：{1,2,3,4,5}。請注意，Excel 不會(huì )用大括號將數組常量括起來(lái)，您必須自己添加大括號。另外請不要忘記，在向數組公式添加常量后，需按 Ctrl+Shift+Enter 輸入公式。
因為 Excel 首先對括號括起來(lái)的表達式執行運算，接下來(lái)參與運算的兩個(gè)元素是存儲在工作簿 (A1:E1) 中的值以及運算符。此時(shí)，公式將存儲數組中的值與常量中對應的值相乘。它等價(jià)于：
=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)
最后，SUM 函數將這些值相加，和 85 顯示在單元格 A3 中：
要避免使用存儲數組并讓運算完全位于內存中，可用另一個(gè)數組常量來(lái)替換存儲數組：
=SUM({3,4,5,6,7}*{1,2,3,4,5})
要嘗試此操作，請復制函數，并在工作簿中選擇一個(gè)空白單元格，將該公式粘貼到編輯欄中，然后按 Ctrl+Shift+Enter。將得到與上述練習中使用數組公式 =SUM(A1:E1*{1,2,3,4,5}) 相同的結果。
數組常量可以包含數字、文本、邏輯值（例如 TRUE 和 FALSE）和錯誤值（例如 #N/A）?？梢允褂谜麛?、小數和科學(xué)計數格式表示的數字。如果包括文本，則必須使用雙引號 (") 將文本括起來(lái)。
數組常量不能包含其他數組、公式或函數。換言之，它們只能包含以逗號或分號分隔的文本或數字。當您輸入如下所示的公式時(shí)，Excel 將顯示警告消息：{1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)}。另外，數值不能包含百分號、貨幣符號、逗號或圓括號。
使用數組常量的最佳方式是對它們進(jìn)行命名。命名的數組常量更易于使用，并且對于初學(xué)者來(lái)說(shuō)，它們可以降低數組公式的復雜性。要命名數組常量并在公式中使用它們，請執行以下操作：
顯示“定義名稱(chēng)”對話(huà)框。
={"一月","二月","三月"}
對話(huà)框中的內容應類(lèi)似如下：
=第1季度
將得到以下結果。
將命名常量用作數組公式時(shí)，切記要輸入等號。如果未輸入等號，Excel 會(huì )將該數組解釋為文本字符串。最后，請記住可以使用文本和數字的組合。
當數組常量不起作用時(shí)請檢查下面的問(wèn)題：
下面的示例演示可以將數組常量用于數組公式的幾種方式。某些示例使用 TRANSPOSE 函數將行轉換為列，或將列轉換為行。
={1,2,3,4;5,6,7,8;9,10,11,12}*2
={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
=TRANSPOSE({1,2,3,4,5})
即使輸入的是水平數組常量，TRANSPOSE 函數也會(huì )將該數組常量轉換為列。
=TRANSPOSE({1;2;3;4;5})
即使輸入的是垂直數組常量，TRANSPOSE 函數也會(huì )將該常量轉換為行。
=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})
TRANSPOSE 函數將各行轉換為一系列的列。
本節提供基本數組公式的示例。
使用本節中的數據創(chuàng )建兩個(gè)示例工作表。
| 400 | 水調歌頭 | 1 | 2 | 3 | 4 | ||
| 1200 | 明月幾時(shí)有 | 5 | 6 | 7 | 8 | ||
| 3200 | 把酒問(wèn)青天 | 9 | 10 | 11 | 12 | ||
| 475 | 不知天上宮闕 | 13 | 14 | 15 | 16 | ||
| 500 | 今夕是何年 | ||||||
| 2000 | |||||||
| 600 | |||||||
| 1700 | |||||||
| 800 | |||||||
| 2700 | |||||||
下面的示例介紹如何使用數組公式在不同工作表的單元格區域之間創(chuàng )建鏈接。還演示如何使用同一組值創(chuàng )建數組常量。
=數據!E1:G3
將得到以下結果。
該公式鏈接到數據工作表的單元格 E1 到 G3 中存儲的值。執行此多單元格數組公式的另一個(gè)方法是在數組工作表的每個(gè)單元格中放入唯一的公式，如下所示。
| =數據!E1 | =數據!F1 | =數據!G1 |
| =數據!E2 | =數據!F2 | =數據!G2 |
| =數據!E3 | =數據!F3 | =數據!G3 |
如果更改了數據工作表中的某些值，這些更改將顯示在數組工作表中。請注意，更改數據工作表中的任何值都必須遵循數組公式的編輯原則。有關(guān)這些原則的詳細信息，請參見(jiàn)了解數組公式一節。
Excel 使用下面的數組常量替換 =數據!E1:G3 數組公式：
={1,2,3;5,6,7;9,10,11}
數據與數組工作表之間的鏈接已破壞，數組公式已為數組常量替代。
下面的示例演示如何計算單元格區域中的字符數（包括空格）。
=SUM(LEN(C1:C5))
單元格 C7 中顯示值 25。
這樣，LEN 函數返回該區域的每個(gè)單元格中的每個(gè)文本字符串的長(cháng)度。然后 SUM 函數將這些值相加，并在包含該公式的單元格 C7 中顯示結果。
本示例演示如何查找單元格區域內的三個(gè)最小值。
這組單元格將保留數組公式返回的結果。
=SMALL(A1:A10,{1;2;3})
值 400、 475 和 500 將分別顯示在單元格 A12 到 A14 中。
此公式使用數組常量計算 SMALL 函數三次，并返回單元格 A1:A10 中包含的數組中的最小值 (1)、次小值 (2) 和第三小值 (3)。要查找出更多的值，可以向該常量添加更多參數并向 A12:A14 區域添加同等個(gè)數的結果單元格。還可以對此公式使用其他函數，例如 SUM 或 AVERAGE。例如：
=SUM(SMALL(A1:A10,{1;2;3}))
=AVERAGE(SMALL(A1:A10,{1;2;3}))
要找出區域中的多個(gè)最大值，可以使用 LARGE 函數替代 SMALL 函數。此外，下面的示例使用 ROW 和 INDIRECT 函數。
=LARGE(A1:A10,ROW(INDIRECT("1:3")))
值 3200、2700 和 2000 分別顯示在單元格 A12 到 A14 中。
現在，了解一點(diǎn) ROW 和 INDIRECT 函數可能會(huì )有所幫助?？梢允褂?ROW 函數創(chuàng )建連續的整數數組。例如，在練習工作簿中選擇一個(gè)包含 10 個(gè)單元格的空列，在單元格 A1:A10 中輸入下面的數組公式，然后按 Ctrl+Shift+Enter：
=ROW(1:10)
此公式創(chuàng )建由 10 個(gè)連續整數組成的一列。為了查看可能的問(wèn)題，請在包含數組公式的區域上面插入一行（即第 1 行上）。Excel 調整行引用，并且此公式生成從 2 到 11 的整數。要修正該問(wèn)題，可以向該公式添加 INDIRECT 函數：
=ROW(INDIRECT("1:10"))
INDIRECT 函數使用文本字符串作為參數（這是區域 1:10 由雙引號括起的原因）。當插入行或移動(dòng)數組公式時(shí)，Excel 不會(huì )調整文本值。因此，此 ROW 函數總是生成所需的整數數組。
讓我們以前面使用過(guò)的公式為例 — =LARGE(A1:A10,ROW(INDIRECT("1:3"))) — 從內層的括號開(kāi)始向外計算：INDIRECT 函數返回一組文本值，在這種情況下，為值 1 到 3。ROW 函數依次生成包含三個(gè)單元格的縱欄式數組。 LARGE 函數使用單元格 A1:A10 中的值，并且它計算三次，每次都對應于 ROW 函數返回的每個(gè)引用。值 3200、2700 和 2000 返回到這三個(gè)單元格縱欄式數組中。如果要查找更多值，可以向 INDIRECT 函數添加更多的單元格區域。
最后，可以將此公式與其他函數一起使用，例如 SUM 和 AVERAGE。
本示例查找單元格區域中的最長(cháng)文本字符串。本公式僅在數據區域包含單列單元格時(shí)適用。
=INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)
值不知天上宮闕顯示在單元格 C7 中。
讓我們以此公式為例，從內層元素開(kāi)始向外進(jìn)行運算。LEN 函數返回單元格區域 C1:C5 中的每個(gè)項的長(cháng)度。MAX 函數計算這些項中的最大值，它對應于最長(cháng)文本字符串，位于單元格 C3 中。
下面的計算稍微有點(diǎn)復雜。MATCH 函數計算包含最長(cháng)文本字符串的單元格的偏移量（相對位置）。為此，需要三個(gè)參數：分別是查閱值、查閱數組和匹配類(lèi)型。MATCH 函數在查閱數組中搜索指定的查閱值。在這種情況下，查閱值為最長(cháng)的文本字符串：
(MAX(LEN(C1:C5))
并且該字符串位于此數組中：
LEN(C1:C5)
匹配類(lèi)型參數為 0。匹配類(lèi)型可以包含值 1、0 或 -1。如果指定 1，MATCH 返回小于或等于查閱值的最大值。如果指定 0，MATCH 返回正好等于查閱值的第一個(gè)值。如果指定 -1，MATCH 查找出大于或等于指定查閱值的最小值。如果未指定匹配類(lèi)型，Excel 會(huì )采用值 1。
最后，INDEX 函數采用這些參數：數組以及該數組內的行號和列號。單元格區域 C1:C5 提供該數組，MATCH 函數提供單元格地址，最后的參數 (1) 指定該值來(lái)自數組的第一列。
有關(guān)此處討論的函數的詳細信息，請參見(jiàn) Excel 幫助。
本節提供高級數組公式的示例。
當試圖對包含錯誤值（例如 #N/A）的區域求和時(shí)，Excel 中的 SUM 函數不再適用。本示例演示如何對包含錯誤的命名為“數據”的區域中的值求和。
=SUM(IF(ISERROR(數據),"",數據))
該公式創(chuàng )建一個(gè)新數組，包含除錯誤值以外的原始值。從內層函數開(kāi)始向外運算，ISERROR 函數在單元格區域 (數據) 中搜索錯誤。IF 函數在指定的條件計算結果為 TRUE 時(shí)返回指定值，在計算結果為 FALSE 時(shí)返回另一個(gè)值。在此處，它為所有錯誤值返回空字符串 ("")，因為它們的計算結果為 TRUE，并且返回該區域 (數據) 中的其他值（因為這些值計算結果為 FALSE，表示它們不包含錯誤值）。接著(zhù) SUM 函數計算篩選出的數組的總和。
本示例與上面的公式相似，但它返回名為“數據”的區域中的錯誤值個(gè)數，而不是將錯誤值篩選掉：
=SUM(IF(ISERROR(數據),1,0))
該公式創(chuàng )建一個(gè)數組，它為包含錯誤的單元格包含值 1，為不包含錯誤的單元格包含值 0?？梢院?jiǎn)化該公式，并達到相同的結果，方法是移除 IF 函數的第三個(gè)參數，如下所示：
=SUM(IF(ISERROR(數據),1))
如果未指定該參數，IF 函數在單元格不包含錯誤值時(shí)返回 FALSE?？梢赃M(jìn)一步簡(jiǎn)化該公式：
=SUM(IF(ISERROR(數據)*1))
此公式版本可以執行計算是因為 TRUE*1=1 并且 FALSE*1=0。
可能需要根據條件對值求和。例如，此數組公式僅對名為“銷(xiāo)售量”的區域中的正值求和：
=SUM(IF(銷(xiāo)售量>0,銷(xiāo)售量))
IF 函數創(chuàng )建正值和 false 值數組。SUM 函數實(shí)際上將忽略 false 值，因為 0+0=0。在此公式中使用的單元格區域可以由任意數量的行和列組成。
還可以對滿(mǎn)足多個(gè)條件的值求和。例如，下面的數組公式計算大于 0 并且小于等于 5 的值：
=SUM((銷(xiāo)售量>0)*(銷(xiāo)售量<=5)*(銷(xiāo)售量))
請注意，如果區域中包含一個(gè)或多個(gè)非數字單元格，此公式將返回錯誤。
還可以創(chuàng )建使用 OR 條件的數組公式。例如，可以對小于 5 和大于 15 的值求和：
=SUM(IF((銷(xiāo)售量<5)+(銷(xiāo)售量>15),銷(xiāo)售量))
IF 函數查找所有小于 5 和大于 15 的值，然后將這些值傳遞給 SUM 函數。
要點(diǎn) 不能在數組公式中直接使用 AND 和 OR 函數，因為這些函數返回單一結果，TRUE 或 FALSE，而數組函數需要結果數組?？梢酝ㄟ^(guò)使用上一公式中顯示的邏輯來(lái)解決這一問(wèn)題。也就是，對滿(mǎn)足 OR 或 AND 條件的值執行加法或乘法等算術(shù)運算。
本示例演示當您需要對區域中的值求平均值時(shí)，如何從該區域中移除零。下面的公式使用名為“銷(xiāo)售量”的數據區域：
=AVERAGE(IF(銷(xiāo)售量<>0,銷(xiāo)售量))
IF 函數創(chuàng )建不等于 0 的值數組，然后將這些值傳遞給 AVERAGE 函數。
此數組公式對名為“我的數據”和“您的數據”的兩個(gè)單元格區域中的值進(jìn)行比較并返回它們之間不同值的個(gè)數。如果這兩個(gè)區域中的內容完全相同，此公式將返回 0。要使用此公式，單元格區域必須大小相同并且包含相同的維數：
=SUM(IF(我的數據=您的數據,0,1))
此公式創(chuàng )建與正比較的區域大小相同的新數組。IF 函數使用值 0 和值 1 填充數組（0 表示單元格不匹配，1 表示單元格匹配）。然后 SUM 函數返回該數組中的值的和。
可以如下所示簡(jiǎn)化該公式：
=SUM(1*(我的數據<>您的數據))
與計算區域中的錯誤值的公式相似，此公式版本可以執行計算是因為 TRUE*1=1 并且 FALSE*1=0。
此數組公式返回名為“數據”的單列區域中的最大值所在的行號：
=MIN(IF(數據=MAX(數據),ROW(數據),""))
IF 函數創(chuàng )建與“數據”區域對應的新數組。如果對應的單元格包含區域中的最大值，則此數組包含該行號。否則，此數組包含空字符串 ("")。MIN 函數使用此新數組作為它的第二個(gè)參數并且返回與“數據”區域中最大值的行號相對應的最小值。如果“數據”區域包含完全相同的最大值，該公式返回第一個(gè)值的行號。
如果要返回最大值的實(shí)際單元格地址，請使用下面的公式：
=ADDRESS(MIN(IF(數據=MAX(數據),ROW(數據),"")),COLUMN(數據))
聯(lián)系客服
