Excel GROUPBY、PIVOTBY 函數教學|公式版樞紐分析

文章屬性:教學實戰(Excel 函數)
適用版本:Excel for Microsoft 365(Windows / Mac)與網頁版 Excel;2024 年 9 月起在「目前通道」正式推出
難易度 / 耗時:⭐ / 約 10 分鐘
核心結論:GROUPBY 用一條公式做「單軸分組統計」,PIVOTBY 做「列欄雙軸交叉統計」;資料一改,結果自動重算,不必像樞紐分析表按重新整理。
適用對象:常用 Excel 做報表彙總、又懶得每次重建樞紐分析表的人

📌 快速答案

一句話答案:Excel GROUPBY 用一條公式做分組統計——給它「分組欄、要彙總的值、彙總方式(如 SUM)」,就自動長出摘要表;需要列、欄雙向交叉時改用 PIVOTBY。兩者都是動態陣列函數,來源資料一變結果就自動更新,省掉樞紐分析表「重新整理」那一步。

🧰 開始前的準備

系統需求:Excel for Microsoft 365(Windows 或 Mac)或免費的網頁版 Excel。Excel 2019 / 2021 永久授權版沒有 GROUPBY(微軟官方 GROUPBY 頁面只列 Microsoft 365 與網頁版)。
權限需求:一般使用者即可,不需系統管理員。
需要準備:一份「明細型」資料表(每列一筆紀錄,欄位如年份、區域、產品、銷售額)。
本文示範資料:一張銷售明細,A 欄=年份、B 欄=區域、C 欄=產品、D 欄=銷售額,資料落在第 2 到 76 列(對齊微軟官方範例的 A2:D76)。

🔍 為什麼你需要這個?

先講結論:如果你只是想把明細「加總成摘要」,又希望來源一改、摘要就自動跟著變,GROUPBY / PIVOTBY 會比樞紐分析表更快、更好維護。

你有沒有這種經驗:每個月拿到一份幾百列的銷售明細,要做「各產品總額」「各年度各區域交叉表」,於是又插入一次樞紐分析表、拖欄位、改版面;下個月資料更新,還得記得按「重新整理」,忘了按就報錯數字。樞紐分析表很強,但它是一個「獨立物件」,和你的公式體系是分開的,也不會自己跟著來源更新。GROUPBY 和 PIVOTBY 把「分組彙總」這件事變成一條公式:寫在儲存格裡、結果自動溢出成一張表,來源資料一改就立即重算。對於要接續其他公式、要放進儀表板、或單純想少點幾下滑鼠的人,這是把重複勞動一次收掉的做法。

🛠️ 實戰步驟

步驟一:GROUPBY——一條公式做單軸分組統計

GROUPBY 至少要給三個東西:分組欄、要彙總的值、彙總方式。 語法是:

GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

前三個是必填。以示範資料為例,要算「各年度銷售總額」:

=GROUPBY(A2:A76, D2:D76, SUM)

A2:A76 是分組依據(年份),D2:D76 是要加總的銷售額,SUM 是彙總方式。按下 Enter,Excel 會自動「溢出」成一張兩欄的摘要表(年份、加總),還會附上總計列。function 除了 SUM,也可換成 AVERAGE(平均)、COUNT(筆數)、MAX、PERCENTOF(佔比)等。

想「多層分組」很簡單,把分組欄擴成多欄即可。例如同時依年份與區域分組:

=GROUPBY(A2:B76, D2:D76, SUM)

步驟二:PIVOTBY——列、欄雙軸交叉統計

GROUPBY 只沿一個方向分組;要同時有「列標題」和「欄標題」的交叉表,就用 PIVOTBY。 它比 GROUPBY 多一個「欄分組」參數:

PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])

要做「各產品(列)× 各年度(欄)的銷售總額」交叉表:

=PIVOTBY(C2:C76, A2:A76, D2:D76, SUM)

C2:C76(產品)當列、A2:A76(年份)當欄、D2:D76(銷售額)為值、SUM 為彙總。結果就是一張你熟悉的交叉樞紐樣式表——但它是公式長出來的。

步驟三:排序、總計(選用參數)

想控制排序或總計,靠後面幾個選用參數,不用手動再排一次。 兩個函數共通的重點:

排序 sort_order:填欄位序號,負號代表由大到小。例如「各產品總額,由高到低」:

=GROUPBY(C2:C76, D2:D76, SUM, , , -2)

（第 4、5 個參數留空,第 6 個 -2 表示依第 2 欄=銷售額遞減排序。）

總計 total_depth / row_total_depth:0=不顯示總計、1=總計、2=總計加小計、-1=總計放最上面。留空則自動顯示總計與(可行時)小計。

步驟四:驗證結果會自動更新

確認它真的是「動態」的:改一筆來源、不要按任何鍵,摘要就會自己變。 隨手把明細裡某筆銷售額改掉,GROUPBY / PIVOTBY 的結果會立刻重算——這正是它和樞紐分析表最有感的差別(樞紐分析表要手動「重新整理」)。若結果只顯示一格卻出現 #SPILL!,代表溢出範圍被下方或右方既有內容擋住,把擋路的儲存格清空即可。

💡 總結:和樞紐分析表到底該用哪個?

站長我的建議很簡單:要「自動更新、接後續公式、放儀表板」選 GROUPBY / PIVOTBY;要「拖拉互動、鑽取明細、群組日期」選樞紐分析表。 微軟官方文件裡也特別註明一句關鍵:PIVOTBY「並非直接等同 Excel 的樞紐分析表功能」——兩者長得像、引擎不同。函數版的好處是它活在你的公式體系裡:來源一改就重算、可以再被其他函數引用、複製到別的檔案也不會斷開資料連線;缺點是不能像樞紐分析表那樣用滑鼠拖欄位互動、也沒有雙擊鑽取明細。實務上我會這樣分:每月固定格式的報表、要餵給圖表或其他公式的中繼統計,用 GROUPBY / PIVOTBY 一次寫好;臨時想「東翻西看」探索一份陌生資料,還是樞紐分析表順手。最後提醒版本這件事——這兩個函數吃的是 Microsoft 365 訂閱版(或網頁版),用永久授權的 Excel 2021 打開含這些函數的檔案,很可能直接顯示 #NAME?。

❓ 常見問題

Q:我打 GROUPBY 出現 #NAME?,是不是打錯了?

多半不是拼錯,是版本不支援。GROUPBY 需要 Excel for Microsoft 365 或網頁版;Excel 2019 / 2021 永久版並沒有這個函數(微軟官方 GROUPBY 頁面只列 Microsoft 365 與網頁版)。最省事的驗證方式:用瀏覽器開免費的網頁版 Excel 試一次,能用就代表是本機版本太舊。

Q:GROUPBY / PIVOTBY 會取代樞紐分析表嗎?

不會,兩者定位不同。函數適合「要自動更新、要被其他公式接續」的固定報表;樞紐分析表適合「拖拉互動、鑽取、依日期自動分組」的探索式分析。微軟也明講 PIVOTBY 與樞紐分析表功能並非直接對應,可以並存使用。

Q:結果出現 #SPILL! 怎麼辦?

代表公式要「溢出」的範圍被擋住了。GROUPBY / PIVOTBY 會自動往下、往右長出一整張表,只要那片區域有任何既有內容就會卡住。把摘要表預定範圍內的儲存格清空,結果就會正常展開。

🔗 延伸閱讀

📎 參考資料來源

📖 第一級|廠商官方:

GROUPBY function | Microsoft Support — 2026-07-02 查證
PIVOTBY function | Microsoft Support — 2026-07-02 查證
New aggregation functions: GROUPBY and PIVOTBY | Microsoft Tech Community — 2026-07-02 查證(推出時程)

⚠️ 本文核心事實以第一級為準,第二級為補充。

📅 本文查證戳記:2026-07-02 依據微軟官方 GROUPBY / PIVOTBY 文件撰寫。若你在後續版本遇到函數行為變動,歡迎在留言區回報,站長會更新文章。