如何在Excel中使用Groupby函數

Excel 的GROUPBY 函數：強大的數據分組和聚合工具

Excel 的GROUPBY函數允許您根據數據表中的特定字段對數據進行分組和聚合。它還提供參數，允許您對數據進行排序和篩選，以便您可以根據您的特定需求定制輸出。

GROUPBY函數語法

GROUPBY函數包含八個參數：

 <code>=GROUPBY(a,b,c,d,e,f,g,h)</code>

參數a到c是必需的：

• a (行字段)：包含要據其分組數據的數值或類別的範圍（一列或多列）。
• b (值)：包含聚合數據的數值的範圍（一列或多列）。
• c (函數)：用於聚合參數b中值的函數。

參數d到h是可選的，您可以在本文的最後一部分了解有關這些參數的更多信息：

• d (字段標題)：一個數字，指定您是否在參數a和b中選擇了標題，以及是否應在輸出中顯示它們。
• e (總深度)：一個數字，確定輸出是否應顯示總計。
• f (排序順序)：一個數字，指示結果的排序方式。
• g (篩選數組)：一個面向數組的公式，用於篩選出不需要的信息。
• h (字段關係)：一個數字，指定在參數a中提供多列時的字段關係。

GROUPBY函數實戰：僅使用必需參數

如果您被GROUPBY函數的大量參數所淹沒，重要的是要注意，即使您只填充參數a 、 b和c ， GROUPBY函數也能完美運行。因此，首先，我將向您展示GROUPBY函數如何僅使用這三個參數。

假設您擁有一家連鎖餐廳，供應不同菜系的不同菜餚，並且您已經計算了每個菜系-菜餚組合的總銷售額和平均顧客評分。

雖然這些數據很有用，但您可能更感興趣的是不同類別的數據比較。具體來說，您可能想知道每種菜系的總收入以及每種菜餚的平均顧客評分。

然後，由於您希望查看每種菜系的總銷售額，請選擇包含這些數據的單元格，並添加另一個逗號：

 <code>=GROUPBY(TabFood[Cuisine],TabFood[Sales],</code>

最後一個必需參數是對聚合數據使用的函數。在本例中，由於您想找出每種菜系的總銷售額，因此需要插入SUM函數並關閉括號：

 <code>=GROUPBY(TabFood[Cuisine],TabFood[Sales],SUM)</code>

按下Enter 鍵後，Excel 將計算每種菜餚類型的平均顧客評分。同樣，在沒有任何可選參數的情況下，數據默認按左側列中的值按字母順序排序，底部還有一個方便的總計行。

由於J 列中的值是十進制平均值，因此可以通過單擊“開始”選項卡的“數字”組中的“增加小數位數”和“減少小數位數”按鈕來整理顯示的小數位數。

GROUPBY函數實戰：使用可選參數

儘管GROUPBY函數除了三個必需參數之外還有五個可選參數，這使得它看起來更複雜，但這些附加選項實際上只是為了幫助您創建更符合您需求的輸出。更重要的是，您可以選擇要使用哪些可選參數，並跳過不需要的參數。

下面，我將介紹每個可選參數，以便您可以了解在選擇包含它們時它們將如何影響您的數據。

字段標題

在我的上述示例中，我手動鍵入了輸出列標題，因為默認情況下它們不包含在結果中。但是，如果您希望輸出數據包含列標題及其包含的數據，請使用字段標題參數。

首先鍵入您的GROUPBY公式，包括前三個（必需）參數。在本例中，讓我們假設您希望按平均顧客評分對菜系進行分組：

 <code>=GROUPBY(A1:A21,D1:D21,AVERAGE</code>

請注意，標題行包含在所選內容中。實際上，在為前兩個參數選擇數據時，您應該提前考慮是否希望輸出數據複製表中的標題。

包含字段標題的好處	包含字段標題的缺點
如果您更改原始表中的標題，輸出標題將採用這些更改。	如果您想使輸出標題比原始表標題更具體，則無法更改輸出標題。

總深度

總深度參數允許您決定是否希望結果顯示總計，以及如果顯示，它們是否應位於數據的頂部或底部。此參數還允許您選擇是否顯示小計。

對於總深度參數，鍵入：

• 0，如果您不希望顯示任何總計或小計，
• 1，如果您只想在結果底部顯示總計，
• 2，如果您希望小計出現在每個結果類別的底部，並在整個結果的底部顯示總計，
• -1，如果您只想在結果頂部顯示總計，
• -2，如果您希望小計出現在每個結果類別的頂部，並在整個結果的頂部顯示總計。

排序順序

排序順序字段允許您告訴Excel 是否以及如何對結果進行排序。使用此參數確實突出了GROUPBY函數為什麼比使用數據透視表更有用的原因：只要您更改原始表中的任何數據，整個輸出數據就會根據排序順序參數重新排序，而數據透視表需要手動刷新。

您為此參數輸入的數字表示結果中的列。例如，如果您鍵入1，這將按升序或字母順序對第一列的結果進行排序。另一方面，鍵入-1 將按降序或反字母順序對第一列的結果進行排序。

在這個例子中，我已經鍵入了：

 <code>=GROUPBY(A1:A21,C1:C21,SUM,,,,-2)</code>

這將按降序對第二列（銷售額）進行排序。

篩選數組

篩選數組參數不太可能像之前的可選參數那樣被使用，儘管如果您的原始數據表包含可能中斷數據的行，它可以提供幫助。

在這個例子中，單元格A2、A8 和A17 中的年份中斷了GROUPBY函數的結果。

我可以使用篩選數組參數告訴Excel 通過ISNUMBER函數忽略列A 中包含數字的任何單元格：

 <code>=GROUPBY(A1:A24,C1:C24,SUM,,,,ISNUMBER(A1:A24)=FALSE)</code>

字段關係

最後，字段關係參數控制當行字段參數引用多列時如何對數據進行分組。

在這個例子中，當字段關係參數包含0（如果省略參數則為默認值）時， GROUPBY將返回一個分層結果表，其中每列都單獨用單獨的數據行表示。

 <code>=GROUPBY(A1:B21,C1:C21,SUM,,,3,,0)</code>

另一方面，當字段關係參數包含1 時， GROUPBY將返回一個忽略層次結構並獨立排序每一列的結果表。換句話說，類別不會嵌套，這就是為什麼當您選擇此字段關係選項時，您也不能在結果中包含小計的原因。

 <code>=GROUPBY(A1:B21,C1:C21,SUM,,,3,,1)</code>

除了在GROUPBY函數參數中使用SUM和AVERAGE之外，您還可以使用PERENTOF函數，該函數將數據轉換為百分比以顯示子集構成整個數據集的比例。

以上是如何在Excel中使用Groupby函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！