GROUPBY函數是Excel和WPS表格新增的動態(tài)數組函數，用于對數據進行快速分組統(tǒng)計，類似數據透視表但更加靈活。它可根據指定字段對數據分類匯總，并自動生成動態(tài)數組結果。

微軟Office LTSC 2021專業(yè)增強版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大小：2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

參數挺多，但都挺好理解：

=GROUPBY(行字段, 匯總區(qū)域, [聚合函數], [標題], [總計]， [排列方式], [篩選條件], [字段關系])

1、單列行字段匯總

輸入公式：

=GROUPBY(A2:A5,C2:C5,SUM)

分組依據的行字段A2:A5（月份），需要匯總的數據區(qū)域C2:C5（銷售額），聚合函數SUM（求和函數）。即對每個月份的銷售額進行匯總求和。

2、多列行字段匯總

輸入公式：

=GROUPBY(A2:B5,C2:C5,SUM)

分組依據的行字段A2:B5（月份與銷售員），需要匯總的數據區(qū)域C2:C5（銷售額），聚合函數SUM（求和函數）。即對每個月份各個銷售員的銷售額進行匯總求和。

3、多函數組合（求和、平均、計數）

輸入公式：

=GROUPBY(B2:B5,C2:C5,HSTACK(SUM,AVERAGE,COUNT))

HSTACK為橫向合并函數，此時將多個函數平行合并(SUM,AVERAGE,COUNT)，對同一列數據分別執(zhí)行不同計算，分別為求和、求平均值，計數。

4、標題顯示（是否顯示原數據表頭）

輸入公式：

=GROUPBY(A1:B5,C1:C5,SUM,3)

我們只需要將第4參數修改為模式3，即可將首行標題行顯示出來。

注：第一參數與第二參數的數據區(qū)域需要包含首行標題行。因為只有當標題行被包含在了A1:B5與C1:C5區(qū)域之內，我們才會有選擇顯示或不顯示標題的權利。

修改第4參數：

=GROUPBY(A1:B5,C1:C5,SUM,1)

我們只需要將第4參數修改為模式1，即可將首行標題行隱藏。

注：第一參數與第二參數的數據區(qū)域需要包含首行標題行。因為只有當標題行被包含在了A1:B5與C1:C5區(qū)域之內，我們才會有選擇顯示或不顯示標題的權利。

5、總計與小計（多層分組顯示總計和小計）

通常默認省略跳過第5參數，此時默認顯示總計行：

=GROUPBY(A1:B5,C1:C5,SUM,3)

當我們將增加第5參數調整為0時，總計行自動隱藏：

=GROUPBY(A1:B5,C1:C5,SUM,3,0)

當我們將第5參數調整為2時，總計行與小計行同時出現：

=GROUPBY(A1:B5,C1:C5,SUM,3,2)

當我們將第5參數調整為-1時，總計行更換為頂端總計行：

=GROUPBY(A1:B5,C1:C5,SUM,3,-1)

6、排序（按某列降序或降序排列）

默認省略跳過第6參數顯示無規(guī)則亂序狀態(tài)：

=GROUPBY(A1:B5,C1:C5,SUM,3,0)

當我們增加并將第6參數調整為“3”時：

=GROUPBY(A1:B5,C1:C5,SUM,3,0,3)

表示對第3列的“銷售額”列，進行升序（從小到大）排序。

原則：第6參數是幾表示對返回數組區(qū)域的第幾列排序；如果是正數，表示升序排序；如果是負數，表示降序排序。

7、篩選

當我們省略或跳過第7參數時，表示無條件分組統(tǒng)計：

=GROUPBY(A1:B5,C1:C5,SUM,3,0,3)

當我們添加第7參數時：

=GROUPBY(A1:B5,C1:C5,SUM,3,0,3,B2:B5="李四")

增加條件B2:B5="李四"，即只有當B2:B5銷售員數據區(qū)域為“李四”時，我們才進行分組統(tǒng)計，即只篩選銷售員為李四的分類匯總結果。

以上全部為GROUPBY函數基礎參數的解釋。那么我們在實際的職場工作中用它高效解決最多的問題是什么呢？下面我們繼續(xù)舉兩個實用的案例，看看GROUPBY函數在其中發(fā)揮什么關鍵的作用。

高頻使用案例

案例1：文本合并

如下圖所示：

我們想要將A列相同的省份信息所對應的城市信息合并到一行顯示。

我們輸入函數公式：

=GROUPBY(A1:A5,B1:B5,ARRAYTOTEXT,3,0)

第3參數聚合函數設置為ARRAYTOTEXT函數，用于將數組或單元格區(qū)域中的數據轉換為文本格式，并合并到一個單元格中或以文本數組的形式返回。這樣本例可將城市合并為文本合并到一個單元格中。第4參數3顯示標題。第5參數0不顯示總計行。

案例2：二維表轉一維表

我們想要將A1:D4區(qū)域二維表轉換為F1:H10區(qū)域的一維表。

聚合函數核心邏輯：

=IF({1,0},N,TOCOL(B1:D1))

• {1,0}：生成數組 {TRUE, FALSE}，用于引導后續(xù)操作。
• TRUE（1）：保留原始產量數值（即N代表的當前值）。
• FALSE（0）：提取季度標簽（通過TOCOL轉換）。
• N：代表當前分組產量數值，例如“1車間”對應的原始產量數值6000、8000、3500。作用是將橫向分布的產量數值按行轉換為縱向單列。

TOCOL(B1:D1)：B1:D1：原始季度標題（如“1季度”“2季度”“3季度”）。TOCOL(B1:D1)：將橫向的季度標題轉換為垂直列（如“1季度、2季度、3季度”循環(huán)排列）。目的是為每個產量數值匹配對應的季度標簽。每個產量數值會循環(huán)對應到季度標簽（“1季度、2季度、3季度”），形成“產量數值+季度標簽”的配對數組溢出結果。

完善函數：

=GROUPBY(A1:A4,B1:D4,IF({1,0},N,TOCOL(B1:D1)),,0)

GROUPBY函數對A1:A4行字段作為分組依據，即按“車間”分組。值字段B1:D4（需處理的數據區(qū)域）。用上一步IF函數作為核心聚合函數邏輯，不顯示標題，不顯示總計。

推薦閱讀：秒殺Excel數據透視表! excel新函數GROUPBY真強大

最新評論