GROUPBY 関数は、表を分類ごとにまとめて、合計や件数を出す関数です。=GROUPBY(分類の列, 金額の列, SUM) と書くだけで、分類の一覧と合計が並んだ集計表ができます。ピボットテーブルを作らなくても、式 1 つで集計でき、元の表を書き換えると結果も変わります。
この記事では、GROUPBY の基本から、2 つの列でまとめる・件数にする・大きい順に並べる・合計行を消す方法まで、実際の Excel で確かめた結果で説明します。GROUPBY は Microsoft 365 の新しい関数です。
GROUPBY 関数の書き方
=GROUPBY(行フィールド, 値, 関数, [フィールドヘッダー], [合計の深さ], [並べ替え順序], [フィルター配列])| 引数 | 意味 |
|---|---|
| 行フィールド | まとめる列（分類など） |
| 値 | 集計する列（金額など） |
| 関数 | SUM・COUNT・AVERAGE・MAX など |
| フィールドヘッダー（省略可） | 見出しを含めるか |
| 合計の深さ（省略可） | 合計行を出すか（0 で出さない） |
| 並べ替え順序（省略可） | 何列目で並べるか（マイナスで大きい順） |
| フィルター配列（省略可） | 集計する行の条件 |
結果は右下に広がって（スピル）表示されます。結果を出す場所の右と下は空けておきます。
1. 分類ごとの合計を出す
分類（A 列）ごとに、金額（D 列）を合計します。
=GROUPBY(A2:A11,D2:D11,SUM)
結果は「飲料 850」「果物 4,300」「野菜 2,100」で、いちばん下に「合計 7,250」の行が付きました。分類は自動で読みの順（飲料・果物・野菜）に並びました。
2. 2 つの列でまとめる（分類×担当）
行フィールドを「分類・担当」のようにつなぐと、組み合わせごとに集計できます。ここでは合計行を出さない設定（5 つ目を 0）にしました。
=GROUPBY(A2:A11&"・"&C2:C11,D2:D11,SUM,,0)
先頭は「飲料・佐藤」が 350 でした。「・」でつなぐと、離れた列どうしでも組み合わせを作れます。
3. 件数・平均にする
3 つ目の関数を COUNT にすると件数、AVERAGE にすると平均になります。
=GROUPBY(A2:A11,D2:D11,COUNT,,0)
飲料は 3 件、果物は 4 件、野菜は 3 件でした。AVERAGE・MAX・MIN も同じように使えます。
4. 合計の大きい順に並べる
6 つ目の並べ替え順序に -2 を入れると、2 列目（合計）の大きい順になります。
=GROUPBY(A2:A11,D2:D11,SUM,,0,-2)
1 位は 果物（4,300）でした。2 なら小さい順、1 なら分類の名前の順です。
【実機で検証】GROUPBY でつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| SUMIF で果物の合計 | =SUMIF(GROUPBY!A2:A11,"果物",GROUPBY!D2:D11) | 4300 |
| GROUPBY の果物の合計 | =INDEX(GROUPBY(GROUPBY!A2:A11,GROUPBY!D2:D11,SUM),2,2) | 4300 |
| 表の下の空白の行まで範囲に入れたときの行数 | =ROWS(GROUPBY(GROUPBY!A2:A13,GROUPBY!D2:D13,SUM,,0)) | 4 |
| 文字の「300」がまざった金額を SUM | =INDEX(GROUPBY(A14:A16,D14:D16,SUM,,0),1,2) | 700 |
| 見出しを含めて 4 つ目に 3 | =INDEX(GROUPBY(GROUPBY!A1:A11,GROUPBY!D1:D11,SUM,3,0),1,1) | 分類 |
| フィルター配列で山田だけ（果物） | =INDEX(GROUPBY(GROUPBY!A2:A11,GROUPBY!D2:D11,SUM,,0,,GROUPBY!C2:C11="山田"),2,2) | 1200 |
GROUPBY の果物の合計（4300）は、SUMIF（4300）と同じでした。
表の下の空白の行まで範囲に入れると、行数が 4 になり、空白が 1 つの分類として出ました。範囲は表のぴったりにするか、テーブルにして指定します。
金額に文字の「300」がまざっていると、SUM では合計に入りませんでした（500＋200 で 700）。7 つ目のフィルター配列に C2:C11="山田" を入れると、山田さんの行だけを集計でき、果物は 1200 になりました。
よくある質問
GROUPBY が使えない（#NAME?）ときは？
GROUPBY は Microsoft 365 の新しい関数です。Excel 2021 以前では使えないので、SUMIF やピボットテーブルで集計します。
ピボットテーブルとの違いは？
GROUPBY は式なので、元の表を書き換えるとすぐに結果が変わり、更新の操作がいりません。行と列の両方で集計したいときは PIVOTBY 関数を使います。
見出しを付けるには？
範囲に見出しの行を含めて、4 つ目のフィールドヘッダーに 3 を入れます。試したところ、結果の左上に「分類」の見出しが出ました。
あわせて読みたい：関連記事
全体に対する割合（構成比）を1つの式で出したいときは、PERCENTOF関数の使い方もあわせてご覧ください。
コメント