Excel 的GROUPBY 函數(shù)：強大的數(shù)據(jù)分組和聚合工具

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

GROUPBY函數(shù)語法

GROUPBY函數(shù)包含八個參數(shù)：

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

參數(shù)a到c是必需的：

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

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

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

GROUPBY函數(shù)實戰(zhàn)：僅使用必需參數(shù)

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

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

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

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

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

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

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

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

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

GROUPBY函數(shù)實戰(zhàn)：使用可選參數(shù)

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

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

字段標(biāo)題

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

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

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

請注意，標(biāo)題行包含在所選內(nèi)容中。實際上，在為前兩個參數(shù)選擇數(shù)據(jù)時，您應(yīng)該提前考慮是否希望輸出數(shù)據(jù)複製表中的標(biāo)題。

總深度

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

對於總深度參數(shù)，鍵入：

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

排序順序

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

您為此參數(shù)輸入的數(shù)字表示結(jié)果中的列。例如，如果您鍵入1，這將按升序或字母順序?qū)Φ谝涣械慕Y(jié)果進行排序。另一方面，鍵入-1 將按降序或反字母順序?qū)Φ谝涣械慕Y(jié)果進行排序。

在這個例子中，我已經(jīng)鍵入了：

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

這將按降序?qū)Φ诙校ㄤN售額）進行排序。

篩選數(shù)組

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

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

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

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

字段關(guān)係

最後，字段關(guān)係參數(shù)控制當(dāng)行字段參數(shù)引用多列時如何對數(shù)據(jù)進行分組。

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

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

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

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

除了在GROUPBY函數(shù)參數(shù)中使用SUM和AVERAGE之外，您還可以使用PERENTOF函數(shù)，該函數(shù)將數(shù)據(jù)轉(zhuǎn)換為百分比以顯示子集構(gòu)成整個數(shù)據(jù)集的比例。

以上是如何在Excel中使用Groupby函數(shù)的詳細內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！