【Excel】GROUPBY関数でデータをグループごとに集計する方法

この記事は約8分で読めます。

今回は、ExcelのGROUPBY関数を使って、表のデータを項目ごとにまとめるグループ集計の方法と、引数を使いこなすコツを紹介します。

この記事は、Microsoft 365のExcelを対象にしています。GROUPBY関数は比較的新しく追加された関数のため、Excel 2021やExcel 2024などの買い切り版では使えない場合があります。数式を入力しても「#NAME?」と表示されるときは、お使いのExcelが対応していない可能性があります。

GROUPBY関数とは

GROUPBY関数は、指定した列の値でデータをグループに分け、それぞれのグループの値を集計する関数です。たとえば売上の一覧表から「支店ごとの売上合計」や「商品ごとの平均単価」を、数式ひとつで一覧にできます。

これまで同じような集計をするには、ピボットテーブルを作成したり、UNIQUE関数で項目を取り出してからSUMIFS関数で合計したりする方法が一般的でした。GROUPBY関数なら、グループ化、集計、並べ替え、絞り込みまでを1つの数式で行えます。結果は複数のセルに自動で広がる（スピルする）ため、数式をコピーする必要もありません。

ピボットテーブルとの違い

  • 元データを変更すると結果が自動で更新される：ピボットテーブルのように［更新］を押す必要がありません。
  • 数式なので、ほかの関数と組み合わせやすい：結果をさらにFILTER関数やTAKE関数で加工するといった使い方ができます。
  • 集計の条件が数式に残る：どの列を、どの方法で集計しているかが数式から読み取れるため、ほかの人に引き継ぐときにも説明しやすくなります。

一方で、ドラッグ操作でレイアウトを試行錯誤したい場合は、ピボットテーブルのほうが手軽なこともあります。目的に応じて使い分けるとよいでしょう。

GROUPBY関数の構文

GROUPBY関数の書式は次のとおりです。

=GROUPBY(行フィールド, 値, 関数, [フィールドヘッダー], [合計の深さ], [並べ替え順序], [フィルター配列], [フィールドの関係])

最初の3つが必須の引数で、残りは必要に応じて指定します。

  • 行フィールド（row_fields）：グループ分けに使う列です。支店名や商品名など、まとめたい項目の範囲を指定します。複数の列を指定すると、階層のあるグループになります。
  • 値（values）：集計したい数値の列です。複数の列を指定すると、それぞれの列が集計されます。
  • 関数（function）：集計の方法です。SUM、AVERAGE、COUNT、MAX、MIN、PERCENTOFなどの関数名を指定します。
  • フィールドヘッダー（field_headers）：範囲に見出し行が含まれているか、結果に見出しを表示するかを数値で指定します。
  • 合計の深さ（total_depth）：総計や小計を表示するかどうかを指定します。
  • 並べ替え順序（sort_order）：結果をどの列で並べ替えるかを指定します。
  • フィルター配列（filter_array）：集計の対象にする行を、TRUE／FALSEの条件で指定します。
  • フィールドの関係（field_relationship）：行フィールドに複数の列を指定したときの関係（階層か、表か）を指定します。

関数の引数は、「SUM」のように関数名だけを書き、かっこは付けないのがポイントです。「SUM()」と書くとエラーになるので注意しましょう。

基本の使い方：支店ごとの売上合計を出す

ここでは、次のような売上表があるとします。

  • A列：支店（A1セルが見出し、A2:A101にデータ）
  • B列：商品（B1セルが見出し、B2:B101にデータ）
  • C列：売上金額（C1セルが見出し、C2:C101にデータ）

支店ごとの売上合計を求めるには、結果を表示したいセルに次のように入力します。

=GROUPBY(A2:A101, C2:C101, SUM)

Enterキーを押すと、左の列に支店名が重複なく並び、右の列にそれぞれの合計金額が表示されます。既定では、一覧の最後に総計の行も自動で追加されます。

見出し付きで表示する

結果にも「支店」「売上金額」といった見出しを付けたい場合は、見出し行を含めた範囲を指定し、4番目の引数に「3」を指定します。

=GROUPBY(A1:A101, C1:C101, SUM, 3)

フィールドヘッダーの主な指定値は次のとおりです。

  • 0：範囲に見出しがなく、結果にも表示しない
  • 1：範囲に見出しがあるが、結果には表示しない
  • 2：範囲に見出しはないが、結果に見出しを生成する
  • 3：範囲に見出しがあり、結果にも表示する

省略した場合は自動判定になり、値の列の1行目が文字列で2行目が数値なら、見出しがあると判断されます。意図しない判定を避けたいときは、数値で明示しておくと確実です。

引数を活用した集計のコツ

集計結果を売上の多い順に並べ替える

5番目以降の引数を使うと、並べ替えや絞り込みも数式の中で指定できます。並べ替え順序には、行フィールドの列から数えた列番号を指定します。行フィールドが1列、値が1列の場合、行フィールドが「1」、値の列が「2」です。

売上金額の多い順（降順）に並べるには、番号にマイナスを付けて「-2」と指定します。

=GROUPBY(A2:A101, C2:C101, SUM, , , -2)

途中の引数を省略するときは、カンマだけを続けて書きます。カンマの数がずれると別の引数として扱われるので、入力後に結果を確認しておきましょう。

総計の行を表示しない

総計が不要な場合は、合計の深さに「0」を指定します。

=GROUPBY(A2:A101, C2:C101, SUM, , 0)

合計の深さの主な指定値は次のとおりです。

  • 0：合計を表示しない
  • 1：総計を表示する
  • 2：総計と小計を表示する
  • -1：総計を一覧の上部に表示する
  • -2：総計と小計を一覧の上部に表示する

支店と商品の2段階でグループ化する

行フィールドに2列以上を指定すると、階層のある集計表になります。支店ごとに、さらに商品別の売上をまとめたい場合は、次のように入力します。

=GROUPBY(A2:B101, C2:C101, SUM, , 2)

合計の深さに「2」を指定しているため、支店ごとの小計と、全体の総計が表示されます。小計を表示するには、行フィールドが2列以上である必要があります。

なお、最後の引数「フィールドの関係」に「1」（表）を指定すると、各列が独立して並べ替えられる代わりに、小計は表示されなくなります。小計が必要な場合は、既定の「0」（階層）のままにしておきます。

条件に合う行だけを集計する

フィルター配列を使うと、集計する前に対象の行を絞り込めます。たとえば、商品が「試供品」の行を除いて集計したい場合は、次のように指定します。

=GROUPBY(A2:A101, C2:C101, SUM, , , , B2:B101″試供品”)

フィルター配列には、行フィールドと同じ行数の条件を指定する必要があります。範囲の行数がずれているとエラーになるため、範囲の開始行と終了行をそろえておきましょう。

複数の集計方法を並べて表示する

関数の引数には、複数の集計方法をまとめて指定することもできます。HSTACK関数で関数名を横に並べると、集計結果も横に並びます。

=GROUPBY(A2:A101, C2:C101, HSTACK(SUM, AVERAGE))

この数式では、支店ごとの合計と平均が隣り合った列に表示されます。合計に加えて全体に占める割合も見たい場合は、AVERAGEの代わりにPERCENTOFを指定すると、各グループの構成比を求められます。

件数を数えたい場合は、COUNTやCOUNTAを使います。値の列が数値ならCOUNT、文字列を含む列ならCOUNTAを選ぶと、意図した件数になりやすくなります。

GROUPBY関数を使うときの注意点とTips

スピルする範囲を空けておく

GROUPBY関数の結果は、入力したセルから右下方向に自動で広がります。広がる先のセルに文字や数値が入っていると「#SPILL!」エラーになります。グループの数が増えることも考えて、結果を表示する場所の下や右には余白を確保しておきましょう。

テーブルと組み合わせて範囲の更新を楽にする

元データを「A2:A101」のようなセル範囲で指定すると、データが102行目以降に増えたときに数式を修正する必要があります。元の表をテーブルに変換しておき、構造化参照で指定すると、行を追加しても集計範囲が自動で広がります。

=GROUPBY(売上表[支店], 売上表[売上金額], SUM)

テーブル名や列名が数式に入るため、どの列を集計しているのかが読み取りやすくなる点も利点です。

結果の表示形式を整える

GROUPBY関数で求めた合計や平均は、元データの表示形式が引き継がれないことがあります。金額なら桁区切り、構成比ならパーセント表示など、結果のセル範囲に表示形式を設定しておくと読みやすくなります。グループ数の増減に備えて、少し広めの範囲に設定しておくと手間が減ります。

うまく動かないときの確認ポイント

  • 「#NAME?」が表示される：関数名のつづりを確認します。つづりが正しい場合は、お使いのExcelがGROUPBY関数に対応していない可能性があります。
  • 「#VALUE!」が表示される：行フィールド、値、フィルター配列の行数がそろっているかを確認します。
  • 見出しが集計に混ざる：フィールドヘッダーの引数を指定し、範囲に見出しが含まれているかどうかを明示します。

まとめ

GROUPBY関数を使うと、表のデータを項目ごとにまとめるグループ集計を、数式ひとつで作成できます。

  • 基本形は「=GROUPBY(行フィールド, 値, 関数)」で、関数名はかっこを付けずに指定する
  • フィールドヘッダーの「3」で、結果に見出しを表示できる
  • 並べ替え順序、合計の深さ、フィルター配列で、並び順や小計、集計対象を調整できる
  • HSTACK関数を使えば、合計と平均など複数の集計を並べて表示できる
  • 元データをテーブルにしておくと、行が増えても範囲を直さずに済む

ピボットテーブルを作るほどではない集計や、元データの変更にすぐ追従させたい集計表に、GROUPBY関数を取り入れてみてください。