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

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

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

什么是Excel中的陣列？

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

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

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

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

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

什么是Excel中的陣列公式？

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

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

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

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

Excel陣列公式的簡單示例

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

當然，沒有什么可以阻止您首先使用像=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í)行復雜任務的最方便工具。單個陣列公式可以替換數(shù)百個常規(guī)公式。陣列公式非常適合以下任務：

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

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

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

在Excel中輸入陣列公式時，有4個重要的事情要記住：

1. 完成鍵入公式并同時按鍵Ctrl Shift Enter的鍵后，Excel自動將公式封閉在{curly Braces}之間。當您選擇這樣的單元格時，您可以在公式欄中看到牙套，這為您提供了一個線索，即陣列公式存在。
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中的單細胞和多細胞陣列公式

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

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

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

以下示例演示了如何使用單細胞和多細胞陣列公式。

示例1。單細胞陣列公式

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

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

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

=MAX(C2:C6-B2:B6)

示例2。excel中的多細胞陣列公式

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

選擇空單元的范圍，例如D2：D6，然后在公式條中輸入以下公式：

=B2:B6 * C2:C6 * 0.1

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

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

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

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

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

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

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

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

如何使用多細胞陣列公式

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

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

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

Excel Array常數(shù)

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

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

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

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

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

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

要在電子表格中輸入水平數(shù)組，請在一行中選擇相應數(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ù)，十進制或科學符號輸入數(shù)值。如果您使用文本值，則應像任何Excel公式中一樣被雙引號（“）包圍。

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

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

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

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

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

     

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

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

   結(jié)果應該類似于：

   

3. 防止錯誤

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

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

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

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

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

首先，創(chuàng)建一個垂直數(shù)組常數(shù)，其中包含要概括的數(shù)字。例如，如果要在范圍內(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é)果：

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

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

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

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

假設(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"}))

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

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

以及Excel Array公式中的運營商

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

• 操作員是星號（*），它是乘法符號。如果所有條件都評估為true，它指示Excel返回True。
• 或操作員是加號（）。如果給定表達式中的任何條件評估為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與“蘋果”進行比較的結(jié)果。第三個數(shù)組包含來自C2：C9范圍的銷售數(shù)字。與任何數(shù)學操作一樣，乘法將真實和錯誤轉(zhuǎn)換為1和0的元素，并且在0中均不在0時，如果要乘坐0個元素，則在0中，無論是在0時都乘坐的元素。從第三個數(shù)組進入最終數(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）當所有條件評估為false時。然后，如果檢查添加結(jié)果是否大于0，并且如果是，則總和添加了第三個數(shù)組的相應元素（C2：C9）。

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

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

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

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

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

以下公式將有效：

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

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

如果您對其他一個月感興趣，請用相應的數(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中的值進行比較，該值在此示例中為1。此比較的結(jié)果是一系列布爾值真實和錯誤。如您所記住的那樣，您可以選擇數(shù)組公式的某些部分，然后按F9來查看該部分等同于：

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

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

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