Excel數(shù)組公式指南和示例

1、Excel數(shù)組公式指南和示例要成為一名Excel高級用戶，您需要知道如何使用數(shù)組公式，它能執(zhí)行非數(shù)組公式所不能執(zhí)行的計算。本文內(nèi)容了解數(shù)組公式了解數(shù)組常量使用基本數(shù)組公式使用高級數(shù)組公式了解數(shù)組公式本節(jié)介紹數(shù)組公式并解釋如何對數(shù)組公式進(jìn)行輸入、編輯并解答疑難問題。為什么要使用數(shù)組公式？如果您在Excel中使用過公式，想必知道利用公式可以執(zhí)行某些相當(dāng)復(fù)雜的操作。例如，可以基于給定的年數(shù)計算貸款總成本。但是，如果您確實想精通Excel，還需要掌握如何使用數(shù)組公式。因為使用數(shù)組公式可以執(zhí)行更多復(fù)雜的任務(wù)，例如：計算包含在某個單元格區(qū)域中的字符數(shù)。僅對滿足特定條件的數(shù)字求和，例如某一區(qū)域中的最小值或介

2、于上限和下限之間的數(shù)字。對一系列值中的每第n個值求和。注釋數(shù)組公式也被稱為“CSE公式”，這是因為可以按Ctrl+Shift+Enter在工作簿中輸入它們。數(shù)組和數(shù)組公式簡介如果您有過一點編程經(jīng)驗，可能碰到過術(shù)語數(shù)組。在本文中，數(shù)組是項的集合。在Excel中，這些項可以位于一行（稱為一維水平數(shù)組）中，也可位于一列（稱為一維垂直數(shù)組）中或多行和多列（二維數(shù)組）中。無法在Excel中創(chuàng)建三維數(shù)組或三維數(shù)組公式。數(shù)組公式是指可以在數(shù)組的一項或多項上執(zhí)行多個計算的公式。數(shù)組公式可以返回多個結(jié)果，也可返回一個結(jié)果。例如，可以將數(shù)組公式放入單元格區(qū)域中，并使用數(shù)組公式計算列或行的小計。也可以將數(shù)組公式放入

3、單個單元格中，然后計算單個量。位于多個單元格中的數(shù)組公式稱為多單元格公式，位于單個單元格中的數(shù)組公式稱為單個單元格公式。下節(jié)中的示例將演示如何創(chuàng)建多單元格和單個單元格數(shù)組公式。本練習(xí)演示如何使用多單元格數(shù)組公式和單個單元格數(shù)組公式來計算一組銷售數(shù)據(jù)。第一組操作是使用多單元格公式計算一組小計。第二組操作是使用單個單元格公式計算總計。創(chuàng)建多單元格數(shù)組公式打開一個新的空白工作簿。復(fù)制示例工作表數(shù)據(jù)，并將它粘貼到的新工作簿中（從單元格A1開始）。+如何復(fù)制示例工作表數(shù)據(jù)創(chuàng)建一個空白工作簿或工作表。選擇“幫助”主題中的示例。按Ctrl+C。在工作表中，選擇單元格A1，然后按Ctrl+V。銷售人員汽車類型

4、銷售數(shù)量單價總銷售額劉鵬四門轎車52200雙門轎車41800尹歌四門轎車62300雙門轎車81700林彩瑜四門轎車32000雙門轎車11600潘杰四門轎車92150雙門轎車51950施德福四門轎車62250雙門轎車82000使用附近顯示的“粘貼選項”按鈕嚴(yán)以匹配目標(biāo)格式。要將數(shù)組（單元格區(qū)域C2到D11）中的值相乘，請選擇單元格E2到E11,然后在編輯欄中輸入以下公式：=C2:C11*D2:D115.按5.按Ctrl+Shift+Enter。Excel使用大括號（）將公式括起，并將一個公式實例放入所選區(qū)域的每個單元格中。因為執(zhí)行速度很快，所以您在E列中看到的是每位銷售人員每種轎車類型的總銷售額

5、。創(chuàng)建單個單元格數(shù)組公式在工作簿的單元格A13中，鍵入總銷售額。在單元格B13中，鍵入下面的公式并按Ctrl+Shift+Enter：=SUM（C2:C11*D2:D11）這時，Excel會將數(shù)組（單元格區(qū)域C2到D11）中的值相乘，然后使用SUM函數(shù)將這些乘積相加。結(jié)果等于111,800的總銷售額。本示例演示了此類公式的強(qiáng)大功能。例如，假定您有15,000行數(shù)據(jù)。您可以通過在單個單元格中創(chuàng)建數(shù)組公式來對部分或全部數(shù)據(jù)求和。另外，請注意單個單元格公式（單元格B13中）與多單元格公式（單元格E2到E11中的公式）完全無關(guān)。這使得使用數(shù)組公式具有另一個優(yōu)點-靈活性。您可以執(zhí)行任意次數(shù)的操作，例如更

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

7、，而計算得出的結(jié)果并無不同。數(shù)組公式語法簡介數(shù)組公式主要使用標(biāo)準(zhǔn)公式語法。它們都以等號開始，可以在數(shù)組公式中使用任何內(nèi)置Excel函數(shù)。使用數(shù)組公式的主要不同之處在于，必須按Ctrl+Shift+Enter輸入公式。執(zhí)行此操作時，Excel將用大括號將數(shù)組公式括起來如果您手動鍵入大括號，公式將轉(zhuǎn)換為文本字符串，并且不起作用。您還需要注意的是數(shù)組函數(shù)是一種簡化形式。例如，前面使用的多單元格函數(shù)等效于：=C2*D2=C3*D3等。單元格B13中的單個單元格公式集中了所有這些乘法運算，另外還有將這些小計相加所需的算法：=E2+E3+E4等。輸入和更改數(shù)組公式原則再強(qiáng)調(diào)一下創(chuàng)建數(shù)組公式的基本原則：每當(dāng)

8、需要輸入或編輯數(shù)組公式時都要按Ctrl+Shift+Enter。該原則適用于單個單元格公式和多單元格公式。使用多單元格公式時，還需遵循以下原則：必須在輸入公式之前選擇用于保存結(jié)果的單元格區(qū)域。在多單元格數(shù)組公式練習(xí)的第3步，您通過選擇單元格E2到E11執(zhí)行了此操作。不能更改數(shù)組公式中單個單元格的內(nèi)容。要試試是否真的如此，可以選擇示例工作簿中的單元格E3再按Delete?？梢砸苿踊騽h除整個數(shù)組公式，但無法移動或刪除其部分內(nèi)容。換言之，要縮減數(shù)組公式，需先刪除現(xiàn)有公式再重新開始。提示要刪除數(shù)組公式，請選擇整個公式（例如，=C2:C11*D2:D11），按Delete，再按Ctrl+Shift+En

9、ter。不能向多單元格數(shù)組公式中插入空白單元格或刪除其中的單元格。擴(kuò)展數(shù)組公式有時，可能需要擴(kuò)展數(shù)組公式。（記住不能縮減數(shù)組公式。）這個過程不復(fù)雜，但必須記住上節(jié)中列出的原則。在示例工作簿中，清除位于主表下的所有文本和單個單元格公式。將增加的數(shù)據(jù)行粘貼到工作簿中（從單元格A12開始）。使用附近顯示的“粘貼選項”按鈕廚以匹配目標(biāo)格式。潭思琪四門轎車62500雙門轎車71900王偉四門轎車42200雙門轎車32000楊威四門轎車82300雙門轎車82100選擇包含當(dāng)前數(shù)組公式（E2:E11）的單元格區(qū)域，以及新數(shù)據(jù)旁邊的空單元格（E12:E17）。也就是選擇單元格E2:E17。按F2切換到編輯模式

10、。在編輯欄中，將C11更改為C17，將D11更改為D17，然后按Ctrl+Shift+Enter。Excel會更新單元格E2到E11中的公式，并在新單元格E12到E17中放入該公式的實例。AECDE銷售1人員單價總銷售12四門轎車e250015000取門轎車1PDC1330014王偉四門餅車2200S30015取門僑車33000600016楊威四門轎車53001340017戲門新車2100IISSOO使用數(shù)組公式的缺點數(shù)組公式看起來似乎功能很神奇，但它們也存在某些缺點：您可能有時會忘記按Ctrl+Shift+Enter。請記住每當(dāng)輸入或編輯數(shù)組公式時都要按此組合鍵。其他用戶可能不理解您的公式。

11、數(shù)組公式相對復(fù)雜，因此如果其他人需要修改您的工作簿，您應(yīng)避免使用數(shù)組公式或者確信這些用戶知道如何更改您的公式。大型數(shù)組公式可能會降低計算速度，具體取決于計算機(jī)的處理速度和內(nèi)存。返回頁首了解數(shù)組常量本節(jié)介紹數(shù)組常量并解釋如何對它們進(jìn)行輸入、編輯并解答疑難問題。數(shù)組常量簡介數(shù)組常量是數(shù)組公式的組成部分?？梢酝ㄟ^輸入一系列項然后手動用大括號()將該系列項括起來創(chuàng)建數(shù)組常量，類似于：=123,4,5我們在本文前面強(qiáng)調(diào)過在創(chuàng)建數(shù)組公式時需要按Ctrl+Shift+Enter。因為數(shù)組常量是數(shù)組公式的組成部分，可以通過鍵入一對大括號手動將常量括起來。然后使用Ctrl+Shift+Enter輸入整個公式。如

12、果使用逗號分隔（隔開）各個項，將創(chuàng)建水平數(shù)組（一行）。如果使用分號分隔項，將創(chuàng)建垂直數(shù)組（一列）。要創(chuàng)建二維數(shù)組，應(yīng)在每行中使用逗號分隔項，并使用分號分隔每行。使用數(shù)組公式時，可以將數(shù)組常量用于Excel提供的所有內(nèi)置函數(shù)中。下面幾節(jié)將解釋如何創(chuàng)建各種類型的常量以及如何將這些常量用于Excel中的函數(shù)。創(chuàng)建一維和二維常量下面將為您提供創(chuàng)建水平、垂直和二維常量的練習(xí)。創(chuàng)建水平常量使用前一列所在工作簿，或啟動新的工作簿。選擇單元格A1到E1。在編輯欄中輸入下面的公式，然后按Ctrl+Shift+Enter：=1,2,3,4,5注釋在這種情況下，應(yīng)鍵入左大括號和右大括號（）。將得到以下結(jié)果。A1十A

13、23,4,5AEcDE1I12345|您可能在想為什么不簡單地手動鍵入這些數(shù)字。繼續(xù)學(xué)習(xí)下去將得到答案，本文后面部分的在公式中使用常量一節(jié)將演示使用數(shù)組常量的優(yōu)點。創(chuàng)建垂直常量在工作簿中，選擇一列中的五個單元格。在編輯欄中輸入下面的公式并按Ctrl+Shift+Enter：=1；2;3;4;5將得到以下結(jié)果。創(chuàng)建二維常量1.在工作簿中，選擇一個寬四列高三行的單元格塊。2.在編輯欄中輸入下面的公式，然后按Ctrl+Shift+Enter：=1,2,3,4;5,6,7,8;9,10,11,12將得到以下結(jié)果：在公式中使用常量現(xiàn)在您已經(jīng)熟悉如何輸入數(shù)組常量，下面是一個使用我們討論過的內(nèi)容的簡單示例:

14、1.打開一張空白工作表。2.從單元格A1開始復(fù)制下表。使用附近顯示的“粘貼選項”按鈕以匹配目標(biāo)格式。345673.在單元格A3中，輸入下面的公式，然后按Ctrl+Shift+Enter：=SUM(A1:E1*1,2,3,4,5)請注意，Excel用另一對大括號將常量括起來，這是因為您是以數(shù)組公式的形式輸入該常量。單元格A3中顯示85。下節(jié)將討論此公式的計算方法。數(shù)組常量語法簡介剛才使用的公式包含若干部分。函數(shù)存儲數(shù)組運算符數(shù)組常量括號內(nèi)的最后元素是數(shù)組常量：1,2,3,4,5。請注意，Excel不會用大括號將數(shù)組常量括起來，您必須自己添加大括號。另外請不要忘記，在向數(shù)組公式添加常量后，需按Ct

15、rl+Shift+Enter輸入公式。因為Excel首先對括號括起來的表達(dá)式執(zhí)行運算，接下來參與運算的兩個元素是存儲在工作簿(A1:E1)中的值以及運算符。此時，公式將存儲數(shù)組中的值與常量中對應(yīng)的值相乘。它等價于：=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)最后，SUM函數(shù)將這些值相加，和85顯示在單元格A3中：要避免使用存儲數(shù)組并讓運算完全位于內(nèi)存中，可用另一個數(shù)組常量來替換存儲數(shù)組：=SUM(3,4,5,6,7*1,2,3,4,5)要嘗試此操作，請復(fù)制函數(shù)，并在工作簿中選擇一個空白單元格，將該公式粘貼到編輯欄中，然后按Ctrl+Shift+Enter。將得到與上述練習(xí)中使用

16、數(shù)組公式=SUM(A1:E1*1,2,3,4,5)相同的結(jié)果。常量中可以使用的元素數(shù)組常量可以包含數(shù)字、文本、邏輯值(例如TRUE和FALSE)和錯誤值(例如#N/A)。可以使用整數(shù)、小數(shù)和科學(xué)計數(shù)格式表示的數(shù)字。如果包括文本，則必須使用雙引號()將文本括起來。數(shù)組常量不能包含其他數(shù)組、公式或函數(shù)。換言之，它們只能包含以逗號或分號分隔的文本或數(shù)字。當(dāng)您輸入如下所示的公式時，Excel將顯示警告消息：1,2,A1:D4或1,2,SUM(Q2:Z8)。另外，數(shù)值不能包含百分號、貨幣符號、逗號或圓括號。命名數(shù)組常量使用數(shù)組常量的最佳方式是對它們進(jìn)行命名。命名的數(shù)組常量更易于使用，并且對于初學(xué)者來說，

17、它們可以降低數(shù)組公式的復(fù)雜性。要命名數(shù)組常量并在公式中使用它們，請執(zhí)行以下操作：在“公式”選項卡上的“定義的名稱”組中，單擊“定義名稱”。顯示“定義名稱”對話框。在“名稱”框中，鍵入第1季度。在“引用位置”框中，輸入下面的常量(記住要手動鍵入大括號)：=一月,二月,三月對話框中的內(nèi)容應(yīng)類似如下：單擊“確定”。在工作表中，選擇一行中的三個空單元格。鍵入下面的公式，然后按Ctrl+Shift+Enter。=第1季度將得到以下結(jié)果。將命名常量用作數(shù)組公式時，切記要輸入等號。如果未輸入等號，Excel會將該數(shù)組解釋為文本字符串。最后，請記住可以使用文本和數(shù)字的組合。數(shù)組常量疑難解答當(dāng)數(shù)組常量不起作用時

18、請檢查下面的問題：某些元素可能未使用正確的字符分隔。如果遺漏了逗號或分號，或者如果將它們放錯了位置，將無法正確創(chuàng)建數(shù)組常量或者可能顯示一條警告消息。選擇的單元格區(qū)域可能與常量中的元素個數(shù)不匹配。例如，如果在一列中選擇六個單元格用于要占用五個單元格的常量，則會在空單元格中顯示#N/A錯誤值。反過來，如果選擇的單元格太少，Excel將忽略沒有對應(yīng)單元格的值。數(shù)組常量工作方式下面的示例演示可以將數(shù)組常量用于數(shù)組公式的幾種方式。某些示例使用TRANSPOSE函數(shù)將行轉(zhuǎn)換為列，或?qū)⒘修D(zhuǎn)換為行。乘以數(shù)組中的各項選擇一個寬四列高三行的空單元格塊。鍵入下面的公式，然后按Ctrl+Shift+Enter。=1,

19、2,3,4;5,6,7,8;9,10,11,12*2對數(shù)組中的各項求平方選擇一個寬四列高三行的空單元格塊。鍵入下面的數(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ù)組公式，它使用脫字符號(人)：=1,2,3,4;5,6,7,8;9,10,11,12八2轉(zhuǎn)置一維行選擇一列中的五個空白單元格。鍵入下面的公式，然后按Ctrl+Shift+Enter：=TRANSPOSE(1,2,3,4,5)即使輸入的是水平數(shù)組常量，TRANSPOSE函數(shù)也會將該數(shù)組常量轉(zhuǎn)換為列。轉(zhuǎn)置一維列

20、選擇一列中的五個空白單元格。輸入下面的公式，然后按Ctrl+Shift+Enter：=TRANSPOSE(1;2;3;4;5)即使輸入的是垂直數(shù)組常量，TRANSPOSE函數(shù)也會將該常量轉(zhuǎn)換為行。轉(zhuǎn)置二維常量1.選擇一個寬三列高四行的單元格塊。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)建兩個示例工作表。1.打開一個現(xiàn)有工作簿或創(chuàng)建一個新工作簿，并確保其包含兩個空工作表。2.復(fù)制下表

21、中的數(shù)據(jù)，并將它粘貼到工作表中（從單元格A1開始）。400水調(diào)歌頭12341200明月幾時有56783200把酒問青天9101112475不知天上宮夕是何年2000600170080027003.完成的工作表應(yīng)如下所示。4.A4.ACDEH1400水調(diào)款頭123421200明月幾時有567033200把酒問吞天91011124475不知天上宮闌131415165500今夕務(wù)何年620007600B17009SOO1037005.將第一個工作表命名為數(shù)據(jù)，第二個空白工作表命名為數(shù)組。從現(xiàn)有值創(chuàng)建數(shù)組和數(shù)組常量下面的示例介紹如何使用數(shù)組公式在不同工作表的單元格區(qū)域之間創(chuàng)建

22、鏈接。還演示如何使用同一組值創(chuàng)建數(shù)組常量。從現(xiàn)有值創(chuàng)建數(shù)組在示例工作簿中，選擇數(shù)組工作表。選擇單元格區(qū)域C1到E3。在編輯欄中輸入下面的公式，然后按Ctrl+Shift+Enter：=數(shù)據(jù)!E1:G3將得到以下結(jié)果。該公式鏈接到數(shù)據(jù)工作表的單元格E1到G3中存儲的值。執(zhí)行此多單元格數(shù)組公式的另一個方法是在數(shù)組工作表的每個單元格中放入唯一的公式，如下所示。=數(shù)據(jù)!E1=數(shù)據(jù)!F1=數(shù)據(jù)!G1=數(shù)據(jù)!E2=數(shù)據(jù)!F2=數(shù)據(jù)!G2=數(shù)據(jù)!E3=數(shù)據(jù)!F3=數(shù)據(jù)!G3如果更改了數(shù)據(jù)工作表中的某些值，這些更改將顯示在數(shù)組工作表中。請注意，更改數(shù)據(jù)工作表中的任何值都必須遵循數(shù)組公式的編輯原則。有關(guān)這些原則

23、的詳細(xì)信息，請參見了解數(shù)組公式一節(jié)。從現(xiàn)有值創(chuàng)建數(shù)組常量在數(shù)組工作表上，選擇單元格C1到E3。按F2切換到編輯模式。按F9將單元格引用轉(zhuǎn)換為值。Excel將這些值轉(zhuǎn)換為數(shù)組常量。按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ū)域中對字符計數(shù)下面的示例演示如何計算單元格區(qū)域中的字符數(shù)(包括空格)。在數(shù)據(jù)工作表的單元格C7中輸入下面的公式，然后按Ctrl+Shift+Enter：=SUM(LEN(C1:C5)單元格C7

24、中顯示值25。這樣，LEN函數(shù)返回該區(qū)域的每個單元格中的每個文本字符串的長度。然后SUM函數(shù)將這些值相加，并在包含該公式的單元格C7中顯示結(jié)果。查找出區(qū)域內(nèi)的N個最小值本示例演示如何查找單元格區(qū)域內(nèi)的三個最小值。1.在數(shù)據(jù)工作表上，選擇單元格A12到A14。這組單元格將保留數(shù)組公式返回的結(jié)果。2.在編輯欄中輸入下面的公式，然后按Ctrl+Shift+Enter：2.=SMALL(A1:A10,1;2;3)值400、475和500將分別顯示在單元格A12到A14中。此公式使用數(shù)組常量計算SMALL函數(shù)三次，并返回單元格A1:A10中包含的數(shù)組中的最小值(1)、次小值(2)和第三小值(3)。要查找

25、出更多的值，可以向該常量添加更多參數(shù)并向A12:A14區(qū)域添加同等個數(shù)的結(jié)果單元格。還可以對此公式使用其他函數(shù)，例如SUM或AVERAGE。例如：=SUM(SMALL(A1:A10,1;2;3)=AVERAGE(SMALL(A1:A10,1;2;3)查找出區(qū)域中的N個最大值要找出區(qū)域中的多個最大值，可以使用LARGE函數(shù)替代SMALL函數(shù)。此外，下面的示例使用ROW和INDIRECT函數(shù)。在數(shù)據(jù)工作表上，選擇單元格A12到A14。按Delete清除已有公式但保持單元格處于選中狀態(tài)。3.在編輯欄上輸入下面的公式，并按Ctrl+Shift+Enter：=LARGE(A1:A10,ROW(INDIR

26、ECT(1:3)值3200、2700和2000分別顯示在單元格A12到A14中。現(xiàn)在，了解一點ROW和INDIRECT函數(shù)可能會有所幫助。可以使用ROW函數(shù)創(chuàng)建連續(xù)的整數(shù)數(shù)組。例如，在練習(xí)工作簿中選擇一個包含10個單元格的空列，在單元格A1:A10中輸入下面的數(shù)組公式，然后按Ctrl+Shift+Enter：=ROW(1:10)此公式創(chuàng)建由10個連續(xù)整數(shù)組成的一列。為了查看可能的問題，請在包含數(shù)組公式的區(qū)域上面插入一行(即第1行上)。Excel調(diào)整行引用，并且此公式生成從2到11的整數(shù)。要修正該問題，可以向該公式添加INDIRECT函數(shù)：=ROW(INDIRECT(1:10)INDIRECT函

27、數(shù)使用文本字符串作為參數(shù)(這是區(qū)域1:10由雙引號括起的原因)。當(dāng)插入行或移動數(shù)組公式時，Excel不會調(diào)整文本值。因此，此ROW函數(shù)總是生成所需的整數(shù)數(shù)組。讓我們以前面使用過的公式為例=LARGE(A1:A10,ROW(INDIRECT(1:3)從內(nèi)層的括號開始向外計算：INDIRECT函數(shù)返回一組文本值，在這種情況下，為值1到3。ROW函數(shù)依次生成包含三個單元格的縱欄式數(shù)組。LARGE函數(shù)使用單元格A1:A10中的值，并且它計算三次，每次都對應(yīng)于ROW函數(shù)返回的每個引用。值3200、2700和2000返回到這三個單元格縱欄式數(shù)組中。如果要查找更多值，可以向INDIRECT函數(shù)添加更多的單元

28、格區(qū)域。最后，可以將此公式與其他函數(shù)一起使用，例如SUM和AVERAGE。查找單元格區(qū)域中的最長文本字符串本示例查找單元格區(qū)域中的最長文本字符串。本公式僅在數(shù)據(jù)區(qū)域包含單列單元格時適用。在數(shù)據(jù)工作表上，清除單元格C7中的已有公式，并在該單元格中輸入下面的公式，然后按Ctrl+Shift+Enter：=INDEX(C1:C5,MATCH(MAX(LEN(C1:C5),LEN(C1:C5),0),1)值不知天上宮闕顯示在單元格C7中。讓我們以此公式為例，從內(nèi)層元素開始向外進(jìn)行運算。LEN函數(shù)返回單元格區(qū)域C1:C5中的每個項的長度。MAX函數(shù)計算這些項中的最大值，它對應(yīng)于最長文本字符串，位于單元格

29、C3中。下面的計算稍微有點復(fù)雜。MATCH函數(shù)計算包含最長文本字符串的單元格的偏移量(相對位置)。為此，需要三個參數(shù)：分別是查閱值、查閱數(shù)組和匹配類型。MATCH函數(shù)在查閱數(shù)組中搜索指定的查閱值。在這種情況下，查閱值為最長的文本字符串：(MAX(LEN(C1:C5)并且該字符串位于此數(shù)組中：LEN(C1:C5)匹配類型參數(shù)為0。匹配類型可以包含值1、0或-1。如果指定1，MATCH返回小于或等于查閱值的最大值。如果指定0，MATCH返回正好等于查閱值的第一個值。如果指定-1，MATCH查找出大于或等于指定查閱值的最小值。如果未指定匹配類型，Excel會采用值1。最后，INDEX函數(shù)采用這些參數(shù)

30、：數(shù)組以及該數(shù)組內(nèi)的行號和列號。單元格區(qū)域C1:C5提供該數(shù)組，MATCH函數(shù)提供單元格地址，最后的參數(shù)(1)指定該值來自數(shù)組的第一列。有關(guān)此處討論的函數(shù)的詳細(xì)信息，請參見Excel幫助?；仨撌资褂酶呒墧?shù)組公式本節(jié)提供高級數(shù)組公式的示例。對包含錯誤值的區(qū)域求和當(dāng)試圖對包含錯誤值(例如#N/A)的區(qū)域求和時，Excel中的SUM函數(shù)不再適用。本示例演示如何對包含錯誤的命名為“數(shù)據(jù)”的區(qū)域中的值求和。=SUM(IF(ISERROR(數(shù)據(jù)數(shù)據(jù))該公式創(chuàng)建一個新數(shù)組，包含除錯誤值以外的原始值。從內(nèi)層函數(shù)開始向外運算，ISERROR函數(shù)在單元格區(qū)域(數(shù)據(jù))中搜索錯誤。IF函數(shù)在指定的條件計算結(jié)果為TRU

31、E時返回指定值，在計算結(jié)果為FALSE時返回另一個值。在此處，它為所有錯誤值返回空字符串()，因為它們的計算結(jié)果為TRUE，并且返回該區(qū)域(數(shù)據(jù))中的其他值(因為這些值計算結(jié)果為FALSE，表示它們不包含錯誤值)。接著SUM函數(shù)計算篩選出的數(shù)組的總和。計算區(qū)域中錯誤值個數(shù)本示例與上面的公式相似，但它返回名為“數(shù)據(jù)”的區(qū)域中的錯誤值個數(shù)，而不是將錯誤值篩選掉：=SUM(IF(ISERROR(數(shù)據(jù)),1,0)該公式創(chuàng)建一個數(shù)組，它為包含錯誤的單元格包含值1，為不包含錯誤的單元格包含值0?？梢院喕摴剑⑦_(dá)到相同的結(jié)果，方法是移除IF函數(shù)的第三個參數(shù)，如下所示：=SUM(IF(ISERROR(數(shù)據(jù)),1)如果未指定該參數(shù)，IF函數(shù)在單元格不包含錯誤值時返回FALSE?？梢赃M(jìn)一步簡化該公式：=SUM(IF(ISERROR(數(shù)據(jù))*1)此公式版本可以執(zhí)行計算是因為TRUE*1=1并且FALSE*1=0。條件求和可能需要根據(jù)條件對值求和。例如，此數(shù)組公式僅對名為“銷售量”的區(qū)域中的正值求和：=SUM(IF(銷售量0,銷售量)IF函數(shù)創(chuàng)建正值和false值數(shù)組。SUM函數(shù)實際上將忽略false值，因為0+0=0。在此公式中使用的單元格區(qū)域可以由任意數(shù)量的行和列組成。還可以對滿足多個條件的值求和。例如，下面的數(shù)組公式計算大于0并且小于等于5的值=S