在本教程中，您將了解什麼是Excel數(shù)組公式，如何在工作表中正確輸入它，以及如何使用數(shù)組常數(shù)和數(shù)組函數(shù)。

Excel中的陣列公式是一種非常強大的工具，也是最難掌握的工具之一。一個陣列公式可以執(zhí)行多個計算，並替換數(shù)千個常規(guī)公式。儘管如此，90％的用戶從未在工作表中使用數(shù)組功能，只是因為他們害怕開始學(xué)習(xí)它們。

確實，陣列公式是最令人困惑的Excel功能之一。本教程的目的是使學(xué)習(xí)曲線盡可能輕鬆且流暢。

什麼是Excel中的陣列？

在開始以數(shù)組函數(shù)和公式開始之前，讓我們弄清楚術(shù)語“數(shù)組”的含義。從本質(zhì)上講，陣列是項目的集合。這些項目可以是文本或數(shù)字，它們可以駐留在單行或列中，也可以駐留在多個行和列中。

例如，如果您將每週的雜貨清單納入Excel陣列格式，則看起來像：

{“牛奶”，“雞蛋”，“黃油”，“玉米片”}

然後，如果您選擇單元格A1至D1，請在公式欄中輸入上述數(shù)組，並在公式欄中進(jìn)行相等的符號（=），然後按Ctrl Shift Enter ，您將獲得以下結(jié)果：

您剛剛完成的是創(chuàng)建一個一維水平陣列。到目前為止，沒什麼可怕的，對嗎？

什麼是Excel中的陣列公式？

數(shù)組公式和常規(guī)公式之間的區(qū)別在於，數(shù)組公式會處理幾個值，而不僅僅是一個值。換句話說，Excel中的數(shù)組公式評估數(shù)組中的所有單個值，並根據(jù)公式中表達(dá)的條件對一個或幾個項目進(jìn)行多個計算。

數(shù)組公式不僅可以同時處理幾個值，還可以一次返回幾個值。因此，陣列公式返回的結(jié)果也是一個數(shù)組。

Array公式在Excel 2019，Excel 2016，Excel 2013，Excel 2010，Excel 2007及更低版本的所有版本中都可以使用。

而現(xiàn)在，這似乎是您創(chuàng)建第一個數(shù)組公式的合適時機。

Excel陣列公式的簡單示例

假設(shè)您在B列中有一些項目，其價格在C列中，並且您想計算所有銷售總額。

當(dāng)然，沒有什麼可以阻止您首先使用像=B2*C2一樣簡單的東西來計算每行的小寫，然後總和這些值：

但是，陣列公式可以為您提供這些額外的鑰匙筆觸，因為它可以在內(nèi)存中而不是在附加列中存儲中間結(jié)果。因此，它只需要一個單個陣列公式和2個快速步驟：

1. 選擇一個空單元格並輸入以下公式：

   =SUM(B2:B6*C2:C6)

2. 按鍵盤快捷鍵CTRL Shift Enter ，以完成數(shù)組公式。

   一旦執(zhí)行此操作，Microsoft Excel將用{Curly Braces}圍繞該公式，這是數(shù)組公式的視覺指示。

   該公式的作用是將指定陣列的每個單個行中的值乘以（單元格至C6），將子量表添加在一起，然後輸出大總數(shù)：

   

這個簡單的示例顯示了數(shù)組公式的功能。在使用成千上萬行數(shù)據(jù)時，請考慮一下在一個單元格中輸入一個數(shù)組公式可以節(jié)省多少時間。

為什麼在Excel中使用數(shù)組公式？

Excel Array公式是執(zhí)行複雜計算並執(zhí)行複雜任務(wù)的最方便工具。單個陣列公式可以替換數(shù)百個常規(guī)公式。陣列公式非常適合以下任務(wù)：

• 符合某些條件的總和數(shù)，例如範(fàn)圍內(nèi)的最大或最小值。
• 如本示例所示，總和其他每一行或每一^行或列。
• 計算指定範(fàn)圍內(nèi)的所有或某些字符的數(shù)量。這是一個數(shù)組公式，它計算所有字符，而另一個則計算任何給定字符。

如何在Excel中輸入數(shù)組公式（Ctrl Shift Enter）

如您所知，3鍵Ctrl Shift Enter的組合是一種魔術(shù)觸摸，它將常規(guī)公式變成陣列公式。

在Excel中輸入陣列公式時，有4個重要的事情要記?。?/p>

1. 完成鍵入公式並同時按鍵Ctrl Shift Enter的鍵後，Excel自動將公式封閉在{curly Braces}之間。當(dāng)您選擇這樣的單元格時，您可以在公式欄中看到牙套，這為您提供了一個線索，即陣列公式存在。
2. 手動鍵入公式周圍的牙套不起作用。您必須按CTRL Shift Enter快捷方式以完成數(shù)組公式。
3. 每次編輯數(shù)組公式時，括號都會消失，並且必須再次按CTRL Shift Enter Enter以節(jié)省更改。
4. 如果您忘記按CTRL Shift Enter，您的公式將像通常的公式一樣行為，而僅處理指定數(shù)組中的第一個值。

因為所有Excel陣列公式都需要按下Ctrl Shift Enter，因此它們有時稱為CSE公式。

使用F9鍵評估陣列公式的部分

在Excel中使用數(shù)組公式時，您可以觀察它們?nèi)绾斡嬎愫痛鎯ζ漤椖浚▋?nèi)部數(shù)組）以顯示您在單元格中看到的最終結(jié)果。為此，請在函數(shù)括號內(nèi)選擇一個或幾個參數(shù)，然後按F9鍵。要退出公式評估模式，請按ESC鍵。

在上面的示例中，要查看所有產(chǎn)品的子計數(shù)，您可以選擇B2：B6*C2：C6，按F9 ，並獲得以下結(jié)果。

筆記。請注意，您必須在按F9之前選擇該公式的某個部分，否則F9鍵將簡單地用計算值替換您的公式。

Excel中的單細(xì)胞和多細(xì)胞陣列公式

Excel陣列公式可以返回單個單元格或多個單元中的結(jié)果。輸入的陣列公式稱為多細(xì)胞公式。位於單個單元格中的陣列公式稱為單細(xì)胞公式。

存在一些旨在返回多細(xì)胞陣列的Excel數(shù)組函數(shù)，例如轉(zhuǎn)置，趨勢，頻率，Linest，Ett。

當(dāng)使用CTRL Shift Enter輸入到單個單元時，其他功能（例如總和，平均，骨料，最大，最?。┛梢杂嬎銛?shù)組表達(dá)式。

以下示例演示瞭如何使用單細(xì)胞和多細(xì)胞陣列公式。

示例1。單細(xì)胞陣列公式

假設(shè)您有兩列列出了兩個不同月份中售出的商品數(shù)量（例如B和C列），並且您希望找到最大的銷售量增加。

通常，您會添加一個額外的列，例如D列，該列使用=C2-B2的公式來計算每種產(chǎn)品的銷售更改，然後在該額外列中找到最大值=MAX(D:D) 。

陣列公式不需要額外的列，因為它完美地存儲了中間的記憶中的中間結(jié)果。因此，您只需輸入以下公式，然後按Ctrl Shift Enter ：

=MAX(C2:C6-B2:B6)

示例2。 excel中的多細(xì)胞陣列公式

在上一個總和示例中，假設(shè)您必須從每次銷售中繳納10％的稅，並且要用一個公式計算每種產(chǎn)品的稅額。

選擇空單元的範(fàn)圍，例如D2：D6，然後在公式條中輸入以下公式：

=B2:B6 * C2:C6 * 0.1

按下Ctrl Shift Enter後，Excel將在選定範(fàn)圍的每個單元格中放置一個數(shù)組公式的實例，您將獲得以下結(jié)果：

示例3。使用Excel數(shù)組函數(shù)返回多細(xì)胞數(shù)組

如前所述，Microsoft Excel提供了一些所謂的“數(shù)組功能”，這些功能專門設(shè)計用於使用多單元陣列。轉(zhuǎn)置是這樣的功能之一，我們將利用它來轉(zhuǎn)換上表，即將行轉(zhuǎn)換為列。

1. 選擇要輸出轉(zhuǎn)移表的空單元範(fàn)圍。由於我們將行轉(zhuǎn)換為列，因此請確保與源表分別具有列和行分別選擇相同數(shù)量的行和列。在此示例中，我們選擇了6列和4行。
2. 按F2進(jìn)入編輯模式。
3. 輸入公式，然後按Ctrl Shift Enter 。

在我們的示例中，公式是：

=TRANSPOSE($A$1:$D$6)

結(jié)果看起來與此相似：

這就是您在Excel 2019及更早期間使用Transpose作為CSE陣列公式的方式。在Dynamic Array Excel中，這也可以作為常規(guī)公式。要學(xué)習(xí)其他在Excel中轉(zhuǎn)??置的方法，請查看本教程：如何在Excel中切換列和行。

如何使用多細(xì)胞陣列公式

在Excel中使用多細(xì)胞陣列公式時，請務(wù)必遵循以下規(guī)則以獲取正確的結(jié)果：

1. 在輸入公式之前，選擇要輸出結(jié)果的單元格範(fàn)圍。
2. 要刪除多細(xì)胞陣列公式，請選擇所有包含它的單元格，然後按DELETE ，或在公式欄中選擇整個公式，然後按Delete ，然後按Ctrl Shift Enter 。
3. 您不能以陣列公式編輯或移動單個單元格的內(nèi)容，也不能將新單元格插入或從多細(xì)胞陣列公式中刪除現(xiàn)有單元格。每當(dāng)您嘗試執(zhí)行此操作時，Microsoft Excel都會發(fā)出警告“您不能更改數(shù)組的一部分”。
4. 要縮小數(shù)組公式，即將其應(yīng)用於更少的單元格，您需要先刪除現(xiàn)有的公式，然後輸入新的公式。
5. 要擴展數(shù)組公式，即將其應(yīng)用於更多單元格，請選擇包含當(dāng)前公式以及要擁有它的空單元格的所有單元格，請按F2切換到編輯模式，調(diào)整公式中的引用，然後按Ctrl Shift exter Enter Enter進(jìn)行更新。
6. 您不能在Excel表中使用多細(xì)胞陣列公式。
7. 您應(yīng)該在與公式返回的結(jié)果陣列相同的一系列單元格中輸入多細(xì)胞陣列公式。如果您的Excel數(shù)組公式產(chǎn)生的數(shù)組大於所選範(fàn)圍，則多餘值不會出現(xiàn)在工作表上。如果公式返回的陣列小於所選範(fàn)圍，則＃n/a錯誤將出現(xiàn)在額外的單元格中。

如果您的公式可以返回具有可變數(shù)量元素的數(shù)組，請以等於或大於公式返回的最大數(shù)組的範(fàn)圍內(nèi)輸入，並將您的公式包裝在IFERROR函數(shù)中，如本示例所示。

Excel Array常數(shù)

在Microsoft Excel中，數(shù)組常數(shù)只是一組靜態(tài)值。當(dāng)您將公式複製到其他單元格或值時，這些值永遠(yuǎn)不會改變。

您已經(jīng)看到了本教程開始時從雜貨列表中創(chuàng)建的數(shù)組常數(shù)的示例。現(xiàn)在，讓我們看看存在哪些其他數(shù)組類型以及如何創(chuàng)建它們。

存在3種類型的數(shù)組常數(shù)：

1。水平陣列常數(shù)

水平陣列常數(shù)位於一行。要創(chuàng)建一個行陣列常數(shù)，請鍵入由逗號隔開的值，然後封閉在牙套中，例如{1,2,3,4}。

筆記。創(chuàng)建數(shù)組常數(shù)時，您應(yīng)該手動鍵入開口和關(guān)閉括號。

要在電子表格中輸入水平數(shù)組，請在一行中選擇相應(yīng)數(shù)的空白單元格，請在公式欄中鍵入公式={1,2,3,4} ，然後按Ctrl Shift Enter 。結(jié)果將與此相似：

正如您在屏幕截圖中看到的那樣，Excel將數(shù)組常數(shù)包裝在另一組括號中，就像您輸入數(shù)組公式時一樣。

2。垂直陣列常數(shù)

垂直陣列常數(shù)位於列中。您以與水平陣列相同的方式創(chuàng)建它，例如，您用半olon劃分了項目的唯一區(qū)別：

={11; 22; 33; 44}

3。二維陣列常數(shù)

要創(chuàng)建一個二維數(shù)組，您可以通過半隆和每列數(shù)據(jù)將每一行分開。

={"a", "b", "c"; 1, 2, 3}

使用Excel Array常數(shù)

陣列常數(shù)是Excel陣列公式的基石之一。以下信息和提示可能會幫助您以最有效的方式使用它們。

1. 陣列常數(shù)的元素

   陣列常數(shù)可以包含數(shù)字，文本值，布爾值（真和錯誤）和錯誤值，並由逗號或半olon隔開。

   您可以作為整數(shù)，十進(jìn)製或科學(xué)符號輸入數(shù)值。如果您使用文本值，則應(yīng)像任何Excel公式中一樣被雙引號（“）包圍。

   數(shù)組常數(shù)不能包括其他數(shù)組，單元格引用，範(fàn)圍，日期，定義的名稱，公式或函數(shù)。

2. 命名陣列常數(shù)

   為了使數(shù)組不變易於使用，請給它一個名稱：

   • 切換到“公式”選項卡>定義的名稱組，然後單擊“定義名稱” 。或者，按CTRL F3 ，然後單擊“新” 。
   • 在名稱中輸入名稱
   • 在指框中，輸入帶有前面相等符號（=）的括號包圍的數(shù)組常數(shù)的項目。例如：

     ={"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"}

     

   • 單擊確定以保存您的命名數(shù)組並關(guān)閉窗口。

   要在表格中輸入命名數(shù)組常數(shù)，請選擇一行或列中的數(shù)量多個單元格中的數(shù)組中的項目，請在= sign之前的公式欄中輸入數(shù)組的名稱，然後按Ctrl shift eNTER 。

   結(jié)果應(yīng)該類似於：

   

3. 防止錯誤

   如果您的數(shù)組常數(shù)無法正常工作，請檢查以下問題：

   • 用適當(dāng)?shù)淖址?- 水平陣列常數(shù)和垂直陣列的半隆在陣列中的元素元素 - 逗號 - 逗號。
   • 選擇了一系列與陣列常數(shù)中項目數(shù)的範(fàn)圍。如果選擇更多單元格，則每個額外的單元格將具有＃N/A錯誤。如果選擇較少的單元格，則只會插入陣列的一部分。

在Excel公式中使用陣列常數(shù)

現(xiàn)在，您已經(jīng)熟悉了數(shù)組常數(shù)的概念，讓我們看看如何使用數(shù)組信息來解決您的實際任務(wù)。

示例1。範(fàn)圍內(nèi)的最大 /最小數(shù)字

首先，創(chuàng)建一個垂直數(shù)組常數(shù)，其中包含要概括的數(shù)字。例如，如果要在範(fàn)圍內(nèi)添加3個最小或最大數(shù)字，則數(shù)組常數(shù)為{1,2,3}。

然後，您採用大型或小功能，在第一個參數(shù)中指定整個單元格，並在第二個參數(shù)中包含陣列常數(shù)。最後，將其嵌入總和函數(shù)中：這樣：

總結(jié)最大的3個數(shù)字： =SUM(LARGE(range, {1,2,3}))

總和最小的3個數(shù)字： =SUM(SMALL(range, {1,2,3}))

由於您要輸入一個數(shù)組公式，因此不要忘記按Ctrl Shift Enter ，您將獲得以下結(jié)果：

以類似的方式，您可以計算一定範(fàn)圍內(nèi)N最小或最大值的平均值：

前3個數(shù)字的平均值： =AVERAGE(LARGE(range, {1,2,3}))

底部3個數(shù)字的平均值： =AVERAGE(SMALL(range, {1,2,3}))

示例2。陣列公式以多種條件計數(shù)細(xì)胞

假設(shè)您有訂單列表，並且想知道給定賣方已經(jīng)出售了給定產(chǎn)品的次數(shù)。

最簡單的方法是使用具有多種條件的Countifs公式。但是，如果您想包括許多產(chǎn)品，您的Countifs公式的尺寸可能太大。為了使其更加緊湊，您可以將Countifs一起使用，並在一個或幾個參數(shù)中包含一個數(shù)組常數(shù)：例如：

=SUM(COUNTIFS(range1, "criteria1", range2, {"criteria1", "criteria2"}))

真實公式看起來如下：

=SUM(COUNTIFS(B2:B9, "sally", C2:C9, {"apples", "lemons"}))

我們的樣本陣列僅由兩個要素組成，因為目標(biāo)是證明方法。在您的真實數(shù)組公式中，您可以包含與業(yè)務(wù)邏輯所需的元素一樣多的元素，前提是在Excel 2019-2007中的公式的總長度不超過8,192個字符（Excel 2003及Lower中的1,024個字符），並且您的計算機足以處理大型數(shù)組。有關(guān)更多詳細(xì)信息，請查看陣列公式的局限性。

這是一個高級數(shù)組公式示例，該示例在表中找到所有匹配值的總和：以數(shù)組常數(shù)為單位和vlookup。

以及Excel Array公式中的運營商

數(shù)組操作員告訴公式您要如何使用和 /或邏輯處理數(shù)組。

• 操作員是星號（*），它是乘法符號。如果所有條件都評估為true，它指示Excel返回True。
• 或操作員是加號（）。如果給定表達(dá)式中的任何條件評估為true，它將返回真實。

與操作員的陣列公式

在此示例中，我們找到了銷售人員是Mike ，產(chǎn)品是蘋果的銷售總和：

=SUM((A2:A9="Mike") * (B2:B9="Apples") * (C2:C9))

或者

=SUM(IF(((A2:A9="Mike") * (B2:B9="Apples")), (C2:C9)))

從技術(shù)上講，該公式將三個陣列的元素乘以相同位置。前兩個陣列由真實和錯誤值表示，這是將A2：A9與Mike“和B2：B9與“蘋果”進(jìn)行比較的結(jié)果。第三個數(shù)組包含來自C2：C9範(fàn)圍的銷售數(shù)字。與任何數(shù)學(xué)操作一樣，乘法將真實和錯誤轉(zhuǎn)換為1和0的元素，並且在0中均不在0時，如果要乘坐0個元素，則在0中，無論是在0時都乘坐的元素。從第三個數(shù)組進(jìn)入最終數(shù)組（例如1*1*c2 = 10）。

帶有或操作員的Excel Array公式

使用OR Operator（）的以下陣列公式將銷售人員是Mike或產(chǎn)品為蘋果的所有銷售額：

=SUM(IF(((A2:A9="Mike") (B2:B9="Apples")), (C2:C9)))

在此公式中，您將前兩個數(shù)組的元素添加（這是您要測試的條件），如果至少一個條件評估為true，則獲得true（> 0）； false（0）當(dāng)所有條件評估為false時。然後，如果檢查添加結(jié)果是否大於0，並且如果是，則總和添加了第三個數(shù)組的相應(yīng)元素（C2：C9）。

提示。在現(xiàn)代版本的Excel中，無需為此任務(wù)使用陣列公式 - 簡單的Sumifs公式可以完美處理它們。然而，陣列公式中的和 /或操作員在更複雜的場景中可能會有所幫助，更不用說非常好的心理體操:)

Excel陣列公式中的雙元運算符

如果您曾經(jīng)在Excel中使用Array公式，那麼您可能會遇到一些包含雙重破折號（ - ）的陣列公式，並且您可能想知道它的用途是什麼。

在技??術(shù)上稱為雙元運算符或雙重負(fù)數(shù)的雙重破折號用於將某些表達(dá)式返回的非數(shù)字布爾值（true / fals）轉(zhuǎn)換為1和0，以使數(shù)組函數(shù)可以理解。

以下示例希望使事情更容易理解。假設(shè)您在A列中有一個日期列表，並且您想知道一月份發(fā)生了多少日期，無論年度如何。

以下公式將有效：

=SUM(--(MONTH(A2:A10)=1))

由於這是一個Excel數(shù)組公式，因此請記住按CTRL Shift Enter完成。

如果您對其他一個月感興趣，請用相應(yīng)的數(shù)字替換1。例如，2月2日代表3月3日，等等。為了使公式更加靈活，您可以在某些單元格中指定月份編號，例如屏幕截圖中所示：

現(xiàn)在，讓我們分析此數(shù)組公式的工作方式。月份函數(shù)返回以序列號表示的單元格A2到A10中的每個日期的月份，該序列產(chǎn)生數(shù)組{2; 1; 1; 4; 2; 12; 1; 1; 2; 12; 1}。

之後，將數(shù)組的每個元素與單元格D1中的值進(jìn)行比較，該值在此示例中為1。此比較的結(jié)果是一系列布爾值真實和錯誤。如您所記住的那樣，您可以選擇數(shù)組公式的某些部分，然後按F9來查看該部分等同於：

最後，您必須將這些布爾值轉(zhuǎn)換為1和0的函數(shù)可以理解的0。這就是需要雙重符號的目的。第一個單元脅迫分別對-1/0脅迫。第二個否定值，即逆轉(zhuǎn)標(biāo)誌，將它們變成1和0，大多數(shù)Excel函數(shù)都可以理解和使用。如果您從上面的公式中刪除雙元素，則它將無法使用。

我希望這個簡短的教程已證明對您掌握Excel Array公式的道路有所幫助。下週，我們將通過關(guān)注高級公式示例來繼續(xù)使用Excel陣列。請繼續(xù)關(guān)注，感謝您的閱讀！

以上是陣列公式和功能在Excel中 - 示例和指南的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！