EXCEL：你知道什麼是CSE公式嗎？

05-27

在本教程中,您將學習Excel數組公式是什麼,如何正確地輸入數組公式,以及如何在Excel中使用數組常量和數組函數。

Excel中的數組公式是一個非常強大的工具，也是最難掌握的工具之一，一個Excel數組公式可以執行多個計算並替換數千個常用公式。而且，90%的Excel用戶從來沒有在工作表中使用過數組函數，因為他們害怕開始學習（我也一樣，感覺好複雜，難學！）

實際上，數組公式是最令人困惑的Excel特性之一。本教程的目的是讓學習的過程儘可能的簡單和流暢，循序漸進的學習。

在Excel里什麼是數組公式？

如何輸入數組公式(CTRL + SHIFT + enter)

如何評估計算數組公式的部分項(F9鍵)

Excel中單個單元格和多個單元格的數組公式

Excel數組常量

Excel數組公式中AND 和 OR 運算符

數組公式中的雙一元運算符在Excel里什麼是數組公式？

在我們開始使用Excel數組函數和公式之前，我們先來弄清楚「數組」這個詞的含義。從本質上講，Excel中的數組是項的集合。這些項可以是文本或數字，它們可以駐留在單個行或列中，或在多個行和列中。

例如，如果你把每周的購物清單放入Excel數組格式，它看起來是這樣的:

{「牛奶」，「雞蛋」，「黃油」，「玉米片」}

然後，如果您選擇A1到D1，輸入上面的數組，然後在公式欄中使用等號(=)並按CTRL + SHIFT + enter，您將得到以下結果:

你所做的只是創建一個一維水平數組。到目前為止還沒有什麼可怕的，對吧?

什麼是Excel數組公式?數組公式和普通Excel公式之間的區別是，數組公式處理多個值而不是一個值。換句話說，Excel中的數組公式對數組中的所有單個值進行計算，並根據公式中表達的條件對一個或多個項目執行多個計算。

數組公式不僅可以同時處理多個值，還可以同時返回多個值。因此，Excel數組公式返回的結果也是一個數組。

在Excel 2013,Excel 2010,Excel 2007，以及更低的版本中都可以使用數組公式。

現在，似乎是你創建第一個數組公式的合適時機。

Excel數組公式的簡單示例

假設在B列有一些商品的名稱，C列是價格，你想計算所有銷售的總金額。

當然，你可以先計算每一行的總數用= B2 * C2，然後再把每一行的總數求和:

但是，一個數組公式可以省去這些額外公式的輸入，因為它可以在內存中存儲中間結果，而不是在額外的列中。所以，它所需要的只是一個單一的數組公式和兩個簡單快速的操作步驟:

選擇一個空單元格並輸入以下公式:

=SUM(B2:B6*C2:C6)

按下鍵盤快捷鍵CTRL + SHIFT + ENTER完成數組公式。

一旦你這樣做了，Excel就會用{大括弧}來包圍這個公式，大括弧是數組公式的標識。

公式所做的是將每一行指定數組中的值相乘(單元格區域B2到C6)，將各行子總數加在一起，然後輸出一個大的總數:

這個簡單的例子展示了Excel數組公式的威力。當處理成百上千行數據時，只需考慮在單個單元中輸入一個數組公式可以節省多少時間。

為什麼在Excel中使用數組公式?

Excel數組公式是執行複雜計算和完成複雜任務的最方便工具。一個單一的數組公式可以替換數百個通常的公式。

數組公式非常善於處理諸如:

1. 滿足某些條件的數字，例如在一個範圍內的最大或最小值。

2.某幾行，或者第n行或列的求和，如這個例子所示。

3. 計算指定範圍內所有或某些字元的數量（ps:這一條有時間再舉例學習）如何在Excel中輸入數組公式(Ctrl + Shift + enter)

正如你已經知道的，CTRL + SHIFT + ENTER是一個神奇的組合鍵，它將一個常規公式變成一個數組公式。

當在Excel中輸入數組公式時，需要記住4件重要的事情:

一旦你完成了這個公式，並同時按下CTRL SHIFT ENTER鍵，Excel就會自動地將公式包圍在{大括弧}中。當您選擇這樣一個單元格時，您可以看到公式欄中的大括弧，說明這個單元格有數組公式。

手動地在一個公式兩端輸入大括弧不會把它轉換成一個數組公式。你必須按下Ctrl + Shift + Enter鍵來完成一個數組公式。

每次編輯數組公式時，括弧都會消失，必須重新按下Ctrl + Shift + Enter，以保存公式中的更改。

如果忘記按Ctrl + Shift + Enter，那麼您的公式就會像通常的Excel公式一樣，只處理指定數組中的第一個值。

因為所有的Excel數組公式都需要按Ctrl + Shift + Enter，所以它們有時被稱為CSE公式（和標題對上了）。如何評估計算數組公式的部分項(F9鍵)

在Excel中使用數組公式時，您可以觀察它們如何計算和存儲它們的項(內部數組)以顯示在單元格中看到的最終結果。要做到這一點，在函數的括弧內選擇一個或多個參數，然後按F9鍵。按Esc鍵退出評估模式。

在上面的例子中，為了查看所有產品的子總數，你選擇B2:B6 * C2:C6，按F9，得到以下結果。

請注意，您必須在按F9之前選擇公式的一部分，否則F9鍵將簡單地將Excel數組公式替換為計算值。Excel中的單個單元格和多單元格數組公式

Excel數組公式可以在單個單元格或多個單元格中返回結果。在單元格範圍內輸入的數組公式稱為多單元公式。駐留在單個單元格中的數組公式稱為單個單元格公式。

這裡存在幾個Excel數組函數，用於返回多單元數組，例如TRANSPOSE，TREND，FREQUENCY，LINEST等。

其他功能函數，如SUM，AVERAGE，AGGREGATE，MAX，MIN，可以使用Ctrl + Shift + Enter輸入單個單元格時計算數組表達式。

以下示例演示如何在Excel中使用單個單元格和多單元格數組公式。示例1.單個單元格數組公式

假設您有兩列列出了兩個不同月份銷售的商品數量，比如列B和C，並希望找到最大的銷售增額是多少。

通常，您可以添加一個額外的列，例如列D，使用公式如= C2-B2計算每個產品的銷售額變化，然後在該附加列= MAX（D：D）中找到最大值。

Excel數組公式不需要額外的列，因為它完美地將中間結果存儲在內存中。所以，您只需輸入以下公式，然後按Ctrl + Shift + Enter：

示例2. Excel中的多單元格數組公式

在以前的SUM示例中，假設您必須從每次銷售中支付10％的稅款，並且您想要使用一個公式計算每個產品的稅額。

選擇一些空白列中的單元格範圍，例如D2：D6，並在公式欄中輸入以下公式：

= B2：B6 * C2：C6 * 0.1

一旦按下Ctrl + Shift + Enter，Excel會將您的數組公式的實例放在所選範圍的每個單元格中，您將得到以下結果：

示例3.使用Excel數組函數返回多單元格數組

如前所述，Microsoft Excel提供了一些所謂的「數組函數」，它們是專為多單元格數組而設計的。 Excel TRANSPOSE是其中一個功能，我們將利用它來轉置上表，即將行轉換為列。

選擇要輸出轉置表的單元格的空白範圍。由於我們將行轉換為列，因此請務必分別選擇與源表具有列和行相同數量的行和列。在這個例子中，我們選擇6列和4行。

按F2進入編輯模式。

輸入數組函數= TRANSPOSE（數組），然後按Ctrl + Shift + Enter。在我們的例子中，公式為= TRANSPOSE（$ A $ 1：$ D $ 6）。

結果看起來與此類似：

這是在Excel中如何使用數組函數。如何使用多單元格數組公式

在Excel中使用多單元格數組公式時，請務必遵循以下規則獲取正確的結果：

在輸入公式之前，選擇要輸出結果的單元格範圍。

要刪除多單元格數組公式，請選擇所有包含它的單元格，然後按DELETE，或者選擇公式欄中的整個公式，按DELETE，然後按Ctrl + Shift + Enter。

您不能編輯或移動Excel數組公式中的單個單元格的內容，也不能在多單元格數組公式中插入新單元格或從其刪除現有單元格。無論何時嘗試執行此操作，Microsoft Excel將拋出警告「您不能更改數組的一部分」。

要縮小陣列公式，即將其應用於更少的單元格，您需要首先刪除現有公式，然後輸入新的公式。

要擴展數組公式，即將其應用於更多的單元格，請選擇所有包含當前公式的單元格加上要存儲的單元格，然後按F2切換到編輯模式，調整公式中的引用，然後按Ctrl + Shift + Enter進行更新。

您應該在與公式返回的結果數組相同大小的單元格範圍內輸入多單元格數組公式。如果您的Excel數組公式產生的數組大於所選範圍，則超出值將不會顯示在工作表上。如果公式返回的數組小於所選範圍，＃N / A錯誤將顯示在額外的單元格中。

如果您的公式可能返回具有可變數目元素的數組，請在等於或大於公式返回的最大數組的範圍內輸入，並將公式包裝在IFERROR函數中。Excel數組常量

在Microsoft Excel中，數組常量只是一組靜態值。將公式複製到其他單元格或值時，這些值不會更改。

您已經看到了本教程一開始就從雜貨列表創建的數組常量的例子。現在，我們來看看其他數組類型的存在以及如何創建它們。

Excel中存在3種數組常量：

1.水平數組常量

一個水平數組常量駐留在一行中，要創建一行數組常量，請鍵入逗號分隔的值，然後用大括弧括起來，例如{1,2,3,4}。

注意。創建數組常量時，您應該手動輸入開關和大括弧。

要在電子表格中輸入水平數組，請選擇一行中相應數目的空白單元格，在公式欄中鍵入公式= {1,2,3,4}，然後按Ctrl + Shift + Enter。結果將類似於：

如截圖所示，Microsoft Excel將數組常量包裹在另一組大括弧中，與在Excel中輸入數組公式時一樣。

2.垂直數組常量

垂直數組常量駐留在列中，與水平數組相同的方式創建它，唯一的區別是常量之間是以分號分割的，例如：

3.二維數組常量

要在Excel中創建一個二維數組，您可以用逗號分隔每行中的項目，並用分號分隔每列。

使用Excel數組常量

數組常量是Excel數組公式的基石之一。以下信息和提示可能有助於您以最有效的方式使用它們。

1.數組常量的元素

Excel數組常量可以包含數字，文本值，布爾值（TRUE和FALSE）以及以逗號或分號分隔的錯誤值。

您可以輸入數值為整數，十進位或科學記數法。如果使用文本值，它們應該以雙引號（「）包圍，就像在任何Excel公式中一樣。

數組常量不能包括其他數組，單元格引用，範圍，日期，定義的名稱，公式或Excel函數。

2.命名數組常量

為了使數組常量更容易使用，請給它一個名稱：

切換到公式選項卡>定義名稱組，然後單擊定義名稱。或者，按Ctrl + F3，然後單擊新建。

在名稱中鍵入名稱

在「引用」框中，輸入包含大括弧的數組常量項與前面的等號（=）。例如：

= {「Su」，「Mo」，「Tu」，「We」，「Th」，「Fr」，「Sa」}

單擊「確定」保存命名的數組並關閉窗口。

要在工作表中輸入命名的數組常量，請選擇與數組中的項目一致的行或列中的單元格，在公式欄中鍵入數組的名稱，然後按Ctrl + Shift + Enter。

結果應該類似於：

3.防止錯誤

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

使用正確的字元定義數組常量的元素 - 水平數組常量中的逗號和垂直數組常量中的分號。

選擇與數組常量中的項目數量完全匹配的單元格範圍。如果您選擇更多的單元格，每個額外的單元格將具有＃N / A錯誤。如果選擇較少的單元格，則只會插入陣列的一部分。在Excel公式中使用數組常量

既然您已經熟悉了數組常量的概念，我們來看看如何在Excel公式中使用數組來解決實際的任務。

示例1.在範圍內求和N個最大/最小的數字

您首先創建一個垂直數組常量，其中包含您想要求和的數量。例如，如果要在範圍內加起3個最小或最大的數字，則數組常量為{1,2,3}。

然後，您將使用LARGE或SMALL函數，在第一個參數中指定單元格的整個範圍，並在第二個參數中包含數組常量。最後，將其嵌入到SUM函數中，如下所示：

總和最大的3個數字：= SUM（LARGE（range，{1,2,3}））

求最小3個數字：= SUM（SMALL（range，{1,2,3}））

輸入Excel數組公式後，不要忘記按Ctrl + Shift + Enter，您將得到以下結果：

以類似的方式，您可以計算一個範圍內N個最小或最大值的平均值：

三個數值靠前的平均值：= AVERAGE（LARGE（range，{1,2,3}））

三個數值靠後的平均值：= AVERAGE（SMALL（range，{1,2,3}））示例2.用多個條件對單元格進行計數的數組公式

假設你有一個訂單列表，你想知道一個送貨商賣給指定產品的次數。

最簡單的方法是使用具有多個條件的COUNTIFS公式。但是，如果要包含許多產品，您的COUNTIFS公式可能會增長太大。為了使其更緊湊，您可以與SUM一起使用COUNTIFS，並在一個或多個參數中包含數組常量，例如：

= SUM（COUNTIFS（range1，「criteria1」，range2，{「criteria1」，「criteria2」}））

真正的公式可能如下：

= SUM（COUNTIFS（B2：B9，「sally」，C2：C9，{「apples」，「lemons」}））

我們的示例數組只包含兩個元素，因為目標是演示該方法。在您的真正的數組公式中，您可能會包含與業務邏輯需求相同的元素，前提是在Excel 2013，2010和2007中，公式的總長度不超過8,192個字元（Excel 2003及更低版本中為1,024個字元）強大到足以處理大型陣列。有關詳細信息，請參閱Excel中數組公式的限制。AND和OR運算符在Excel數組公式的用法

數組運算符告訴公式如何處理數組 - 使用AND或OR邏輯。

AND運算符是星號（*）。如果所有條件都為TRUE，它將指示Excel返回TRUE。

OR運算符是加號（+）。如果給定表達式中的任何條件求值為TRUE，則返回TRUE。

1.使用AND運算符的數組公式

在這個例子中，我們找到銷售人員是Mike的銷售總額，產品是蘋果：

= SUM（（A2：A9 =「Mike」）*（B2：B9 =「蘋果」）*（C2：C9））

要麼

= SUM（IF（（（A2：A9 =「Mike」）*（B2：B9 =「Apples」）），（C2：C9）））

2.Excel數組公式與OR運算符

OR運算符（+）的以下數組公式將銷售人員的所有銷售額加起來，或者產品是蘋果：

= SUM（IF（（（A2：A9 =「Mike」）+（B2：B9 =「Apples」）），（C2：C9）））

在現代版本的Excel 2013,2010或2007中，在這種情況下使用數組公式實際上是多餘的，而簡單的SUMIFS公式可以產生完全相同的結果。然而，數組公式中的AND和OR運算符可能在其他更複雜的場景中證明是有用的。Excel數組公式中的雙一元運算符

如果您曾經在Excel中使用過數組公式，那麼您可能會碰到一些包含兩個「- -「符號的數組，您可能會想知道它是用於什麼。

技術上稱為雙一元運算符的雙破折號用於將某些表達式返回的非數字布爾值（TRUE / FALSE）轉換為Excel數組函數可以理解的1和0。

以下示例將有助於使事情更容易理解。假設您在列A中列出了日期列表，並且希望知道1月份的日期多少個，而不考慮年份。

以下公式將會奏效：

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

因為這是一個Excel數組公式，記得按Ctrl + Shift + Enter來完成它。

如果您對其他月份感興趣，請使用相應的數字替換1。例如，2代表2月，3代表March，等等。要使公式更靈活，您可以在某些單元格中指定月份號，如屏幕截圖所示：

現在，我們分析一下這個Excel數組公式的工作原理。 MONTH函數返回由序列號表示的單元格A2到A10中每個日期的月份，產生陣列{2; 1; 4; 2; 12; 1; 2; 12; 1}。

之後，將數組的每個元素與單元格D1中的值進行比較，該值在本示例中為1。此比較的結果是布爾值為TRUE和FALSE的數組。記住，您可以選擇數組公式的某一部分，然後按F9查看該部分等同於：

最後，您必須將這些布爾值轉換為1和0，SUM函數可以理解。這就是雙重一元運算符所需要的。第一個一元運算符分彆強製為TRUE / FALSE到-1/0。第二個一元運算符再次把值否定，即反轉，將其轉換為+1和0，這大部分Excel函數可以理解和使用。如果從上述公式中刪除雙一元，則不起作用。

我希望這個簡短的教程在您掌握Excel數組公式的道路上已被證明是有幫助的。