売上データを部門ごとに合計したい、担当者ごとの平均単価を出したい。こういったグループ集計、ピボットテーブルを作るほどでもないけど手作業は面倒ですよね。GROUPBY関数を使えば、数式ひとつでグループごとの集計表を自動生成できます。この記事では基本の書き方から複数列でのグループ化、ソート、実務パターンまで丸ごと紹介します。
この記事は次のような人におすすめ
- 売上や経費データをカテゴリごとにサッと合計・平均したい
- ピボットテーブルを使わずに数式だけでグループ集計を作りたい
- GROUPBY関数とPIVOTBY関数の違いを知りたい
GROUPBY関数とは？
GROUPBY（グループバイ）関数は、データの行を指定した列でグループ化し、集計関数を適用して結果を返す関数です。英語の「group by」は「〜ごとにまとめる」という意味で、SQLのGROUP BY句と同じ発想の関数です。
たとえば売上データの「部門」列でグループ化して「金額」列をSUMで合計すれば、部門ごとの売上合計表が数式ひとつで出来上がります。結果はスピル（動的配列）で自動展開されるので、データが増えても数式を修正する必要がありません。
GROUPBY関数はMicrosoft 365（Office 365）専用の関数です。Excel 2021以前のバージョンでは使用できません。
GROUPBY関数の書き方（構文と引数）
基本構文
=GROUPBY(行フィールド, 値, 集計関数, [フィールドヘッダー], [合計の深さ], [並べ替え順], [フィルター配列], [フィールドの関係])引数の説明
| 引数 | 必須/省略可 | 説明 |
|---|---|---|
| 行フィールド | 必須 | グループ化の基準となる列（カテゴリ列など） |
| 値 | 必須 | 集計対象の列（金額列など） |
| 集計関数 | 必須 | 集計に使う関数（SUM, AVERAGE, COUNTなど） |
| フィールドヘッダー | 省略可 | ヘッダー行の扱い。0=なし、1=あり（非表示）、2=あり（表示）、3=自動生成。既定は2 |
| 合計の深さ | 省略可 | 総計・小計の表示。0=なし、1=総計あり、2=総計＋小計、-1=総計のみ（データ行なし）。既定は0 |
| 並べ替え順 | 省略可 | 結果の並べ替え。0=昇順、1=降順。既定は0（昇順） |
| フィルター配列 | 省略可 | 行を絞り込むTRUE/FALSEの配列 |
| フィールドの関係 | 省略可 | 0=階層（既定）、1=テーブル形式 |
NOTE
必須の引数は3つだけです。まずは「行フィールド」「値」「集計関数」の3つを指定する基本形をおさえましょう。
GROUPBY関数の基本的な使い方
1列でグループ集計する
次のような売上データがA1:C11にあるとします（1行目がヘッダー）。
| 部門 | 商品 | 金額 |
|---|---|---|
| 営業部 | 商品A | 50000 |
| 開発部 | 商品B | 30000 |
| 営業部 | 商品C | 45000 |
| 開発部 | 商品A | 25000 |
| 総務部 | 商品B | 15000 |
| 営業部 | 商品B | 60000 |
| 総務部 | 商品C | 20000 |
| 開発部 | 商品C | 35000 |
| 営業部 | 商品A | 40000 |
| 総務部 | 商品A | 18000 |
部門ごとの売上合計を求めるには、次のように書きます。
=GROUPBY(A2:A11,C2:C11,SUM)結果はスピルで展開され、以下のような表が自動生成されます。
| 営業部 | 195000 |
|---|---|
| 開発部 | 90000 |
| 総務部 | 53000 |
行フィールドにA2:A11（部門列）、値にC2:C11（金額列）、集計関数にSUMを指定しただけです。SUMIFS関数で部門ごとに数式を並べる必要がなくなります。
TIP
集計関数の引数はSUM、AVERAGE、COUNT、MAX、MINなど、通常のExcel関数名をそのまま指定します。カッコは付けません。
ヘッダー付きで集計する
データにヘッダー行が含まれている場合は、ヘッダー行ごと範囲に含めて第4引数を指定すると、結果にもヘッダーが表示されます。
=GROUPBY(A1:A11,C1:C11,SUM,2)第4引数の「2」は「ヘッダーあり・表示する」を意味します。結果の1行目に「部門」「金額の合計」のようなヘッダーが自動表示されます。
集計関数を変えてみる
集計関数の部分を変えるだけで、さまざまな集計ができます。
=GROUPBY(A2:A11,C2:C11,AVERAGE)部門ごとの平均金額を求めます。
=GROUPBY(A2:A11,C2:C11,COUNT)部門ごとの件数を求めます。
=GROUPBY(A2:A11,C2:C11,MAX)部門ごとの最大金額を求めます。
GROUPBY関数の実務活用パターン
総計行を追加する
第5引数（合計の深さ）に1を指定すると、結果の最後に総計行が追加されます。
=GROUPBY(A2:A11,C2:C11,SUM,,1)第4引数は省略（既定値を使用）するので、カンマだけ入れています。結果は次のようになります。
| 営業部 | 195000 |
|---|---|
| 開発部 | 90000 |
| 総務部 | 53000 |
| 総計 | 338000 |
報告資料に使うときは総計行があると便利ですよね。
結果を降順でソートする
第6引数に1を指定すると、結果が降順（大きい順）に並びます。
=GROUPBY(A2:A11,C2:C11,SUM,,,1)売上が大きい部門から順に表示されるので、ランキング形式のレポートを作りたいときに使えます。
特定の条件でフィルターする
第7引数にTRUE/FALSEの配列を渡すと、条件に合う行だけを集計対象にできます。たとえば金額が30000以上のデータだけで集計する場合です。
=GROUPBY(A2:A11,C2:C11,SUM,,,,(C2:C11>=30000))金額が30000未満の行は除外された状態で、部門ごとの合計が計算されます。
複数列でグループ化する
行フィールドに複数列を指定すると、組み合わせでグループ化できます。部門と商品の両方でグループ化して集計する場合は、HSTACK関数で列を結合して渡します。
=GROUPBY(HSTACK(A2:A11,B2:B11),C2:C11,SUM)結果は「営業部 × 商品A」「営業部 × 商品B」のように、すべての組み合わせごとに集計値が表示されます。
| 営業部 | 商品A | 90000 |
|---|---|---|
| 営業部 | 商品B | 60000 |
| 営業部 | 商品C | 45000 |
| 開発部 | 商品A | 25000 |
| 開発部 | 商品B | 30000 |
| 開発部 | 商品C | 35000 |
| 総務部 | 商品A | 18000 |
| 総務部 | 商品B | 15000 |
| 総務部 | 商品C | 20000 |
ちょっとピボットテーブルに近い表が、数式だけで作れてしまいます。
LAMBDA関数でカスタム集計する
第3引数にLAMBDA関数を指定すれば、独自の集計ロジックも使えます。たとえば最大値と最小値の差（レンジ）を求める場合です。
=GROUPBY(A2:A11,C2:C11,LAMBDA(x,MAX(x)-MIN(x)))部門ごとに金額の振れ幅（最大値 – 最小値）が算出されます。標準の集計関数にない計算もLAMBDA関数で自由に定義できるのがGROUPBY関数の強みです。
よくあるエラーと対処法
| 症状 | 原因 | 対処法 |
|---|---|---|
| #NAME?エラー | Excel 2021以前のバージョンで使用した | GROUPBY関数はMicrosoft 365専用です。バージョンを確認してください |
| #VALUE!エラー | 行フィールドと値の行数が一致しない | グループ化列と集計列の行数を揃えてください。ヘッダーの有無も統一します |
| #SPILL!エラー | スピル先のセルにデータが入っている | 結果が展開されるセル範囲を空にしてください |
| #CALC!エラー | 集計関数の指定が正しくない | SUM、AVERAGE、COUNTなどの関数名をカッコなしで指定してください。「SUM()」ではなく「SUM」です |
| 想定外のグループが表示される | 同じに見える値に余分なスペースがある | TRIM関数でスペースを除去してからグループ化してください |
| 結果が1行しか返らない | 全データが同じグループに分類された | 行フィールドの列指定が正しいか確認してください |
GROUPBY関数とPIVOTBY関数の違い
GROUPBY関数と同時期に追加されたPIVOTBY関数も、データを集計する関数です。一見似ていますが、出力形式がまったく異なります。
| 項目 | GROUPBY関数 | PIVOTBY関数 |
|---|---|---|
| 出力形式 | 縦方向のリスト（行方向に展開） | クロス集計表（行と列の2軸で展開） |
| グループ化の軸 | 行方向のみ（1軸） | 行と列の2軸 |
| 構文の違い | 行フィールド + 値 + 集計関数 | 行フィールド + 列フィールド + 値 + 集計関数 |
| 得意な場面 | 部門ごとの合計・平均などシンプルな集計 | 部門 × 月のクロス集計表 |
| 使い分け | 「〜ごとの合計を縦に並べたい」 | 「〜ごと × 〜ごとの表を作りたい」 |
「部門ごとの売上合計」のように1軸でまとめたいならGROUPBY関数、「部門 × 四半期」のようにクロス表を作りたいならPIVOTBY関数です。
たとえば同じデータから「部門 × 商品」のクロス集計表を作るなら、PIVOTBY関数のほうが適しています。
=PIVOTBY(A2:A11,B2:B11,C2:C11,SUM)行に部門、列に商品が並ぶクロス表が出力されます。GROUPBYでは複数列をHSTACKで結合してリスト形式で出すことになるので、見た目の違いを意識して使い分けてください。
SUMIFS関数との比較
グループ集計といえばSUMIFS関数を使っている方も多いですよね。GROUPBY関数との違いを整理します。
| 項目 | GROUPBY関数 | SUMIFS関数 |
|---|---|---|
| グループの自動認識 | データから自動でグループを抽出 | 事前にグループ一覧を用意する必要がある |
| 数式の数 | 1つの数式で全グループ分を出力 | グループ数だけ数式をコピーする |
| データ増減への対応 | スピルで自動拡張 | グループが増えたら数式を追加する必要がある |
| 対応バージョン | Microsoft 365のみ | Excel 2007以降 |
| 処理速度 | 大量データでも高速 | データ量が多いと重くなることがある |
Microsoft 365を使っているなら、グループ集計はGROUPBY関数に置き換えたほうがメンテナンスが楽です。グループが増えても数式を追加する必要がないので、運用コストが大幅に下がります。
まとめ
GROUPBY関数は「データを指定した列でグループ化し、集計結果を一覧で返す」関数です。
- 行フィールド・値・集計関数の3つを指定するだけで、グループごとの集計表が自動生成される
- SUM、AVERAGE、COUNT、MAX、MINなど集計関数を切り替えるだけで、さまざまな集計ができる
- 総計行の追加、降順ソート、フィルターなどオプション引数も充実している
- 複数列でのグループ化はHSTACK関数と組み合わせる
- PIVOTBY関数はクロス集計表、GROUPBY関数は縦方向のリスト集計と使い分ける
ピボットテーブルを作るほどでもない日常的なグループ集計は、GROUPBY関数で数式化しておくとデータ更新時も自動で再計算されて便利です。ぜひ試してみてください。
関連記事
関数一覧
Excel関数の一覧は下記の記事で確認できます。
エラー値が表示される場合は、下記の記事も参考にしてみてください。
