［Microsoft 365］
Excelの GROUPBY（グループ・バイ）関数の使い方を紹介します。
GROUPBY関数はMicrosoft 365の新関数です。この関数を使うと、表のデータをもとにグループ集計を行えます。「商品ごとに売上金額を合計」「顧客ごとに取引回数をカウント」のような集計を、関数1つで手早く行えるので大変便利です。ここでは基本的なグループ集計の方法と、GROUPBY関数の引数の使い方を紹介します。
GROUPBY関数によるさまざまな集計方法については、以下の記事も参考にしてください。
なお、グループ化した項目を縦軸と横軸に配置したクロス集計をしたい場合は、PIVOTBY関数を使用してください。
目次
書式
グループごとに集計する
=GROUPBY(行フィールド, 値, 関数, ［ヘッダー］, ［集計深さ］, ［並べ替え］, ［フィルター］, ［リレーション］)
［行フィールド］をグループ化して［関数］で指定した方法で［値］を集計します。関数式は、動的配列数式として入力されます。
■引数
| 引数 | 説明 |
|---|---|
| 行フィールド | グループ化する列を配列やセル参照で指定する。複数の列を指定可能。 |
| 値 | 集計対象の列を配列やセル参照で指定する。複数の列を指定可能。 |
| 関数 | 集計方法を指定する。選択肢は SUM、PERCENTOF、AVERAGE、MEDIAN、COUNT、COUNTA、MAX、MIN、PRODUCT、ARRAYTOTEXT、CONCAT、SRDEV.S、STDEV.P、VAR.S、VAR.P、MODE.SNGL、LAMBDA。 |
| ヘッダー | 引数［行フィールド］［値］を指定する際に列見出しを含めて指定したか、戻り値に列見出しを表示するかを次の数値で指定する。省略時はExcelが自動判断する。 0 ： ［行フィールド］［値］に列見出しが含まれない。戻り値に列見出しを表示しない。 1 ： ［行フィールド］［値］に列見出しが含まれる。戻り値に列見出しを表示しない。 2 ： ［行フィールド］［値］に列見出しが含まれない。戻り値に「行フィールド1」「値1」のような列見出しを自動表示する。 3 ： ［行フィールド］［値］に列見出しが含まれる。戻り値に列見出しを表示する。 |
| 集計深さ | 小計や総計を表示するかどうかを次の数値で指定する。省略時はExcelが自動判断する。 0 ： 小計や総計を表示しない。 1 ： 総計を表示する。 2 ： 小計と総計を表示する。 -1 ： 上部に総計を表示する。 -2 ： 上部に小計と総計を表示する。 |
| 並べ替え | 並べ替えの基準となる列の番号を、昇順の場合は正数で、降順の場合は負数で指定する。列の番号は、［行フィールド］と［値］を横に並べて左から数えた番号で指定する。 省略時は［行フィールド］の昇順で並べ替えられる。 |
| フィルター | 集計対象を絞り込むための抽出条件を指定する。 |
| リレーション | ［行フィールド］が階層になっている状態で［値］を基準に並べ替える場合の並べ替え方を次の数値で指定する。 0 ： 階層ごとに並べ替える（既定値）。 1 ： 階層を無視して並べ替える。 |
使用例…基本的なグループ集計
GROUPBY関数には多くの引数がありますが、指定が必須なのは［行フィールド］［値］［関数］の3つです。［行フィールド］に商品名のセル範囲、［値］に売上のセル範囲、［関数］に「SUM」を指定すれば、商品ごとに売上を合計できます。数式は、集計結果に応じた範囲に自動でスピル（隣接するセルに数式が自動拡張すること）します。
=GROUPBY(行フィールド, 値, 関数)
セルH2 | =GROUPBY(C2:C13,F2:F13,SUM)
図1 セルH2にGROUPBY関数を入力して［Enter］キーを押す。
図2 数式がセルI5までスピルして、商品ごとに売上を合計した集計結果が表示される。
なお、GROUPBY関数を修正したいときは、入力時と同様に単一のセルH2を選択して数式を修正し、［Enter］キーで確定してください。
メモ
第4引数［ヘッダー］の使い方
第4引数［ヘッダー］では、
- 引数［行フィールド］［値］を指定する際に列見出しを含めて指定したか
- 戻り値に列見出しを表示するか
の2点を下表の数値で指定します。この引数を省略した場合はExcelが自動判断します。
| 設定値 | 説明 |
|---|---|
| 0 | ［行フィールド］［値］に列見出しが含まれない。 戻り値に列見出しを表示しない。 |
| 1 | ［行フィールド］［値］に列見出しが含まれる。 戻り値に列見出しを表示しない。 |
| 2 | ［行フィールド］［値］に列見出しが含まれない。 戻り値に「行フィールド1」「値1」のような列見出しを自動表示する。 |
| 3 | ［行フィールド］［値］に列見出しが含まれる。 戻り値に列見出しを表示する。 |
例えば［ヘッダー］に「3」を指定すると、元の表に入力されている列見出しを集計結果の先頭行に表示できます。［行フィールド］［値］を指定する際に、列見出しのセルを忘れずに含めてください。
=GROUPBY(行フィールド, 値, 関数, ［ヘッダー］)
セルH1 | =GROUPBY(C1:C13,F1:F13,SUM,3)
図3 第4引数［ヘッダー］に「3」を指定して、集計結果の先頭に「商品」「売上」といった列見出しを表示する。
第5引数［集計深さ］の使い方
第5引数［集計深さ］では、集計結果に小計や総計を表示するかどうかを下表の数値で指定します。この引数を省略した場合はExcelが自動判断します。
| 設定値 | 説明 |
|---|---|
| 0 | 小計や総計を表示しない。 |
| 1 | 総計を表示する。 |
| 2 | 小計と総計を表示する。 |
| -1 | 上部に総計を表示する。 |
| -2 | 上部に小計と総計を表示する。 |
なお、集計結果に小計を表示するには、［行フィールド］に少なくとも2列指定する必要があります。［行フィールド］に3列以上を指定した場合は、「3」「-3」などを指定可能です。
下図では［行フィールド］に地区と商品の2列を指定し、さらに［集計深さ］に「2」を指定して、小計と総計を表示しています。上位の階層である地区ごとに小計が表示され、一番下に総計が表示されます。
=GROUPBY(行フィールド, 値, 関数,, ［集計深さ］)
セルH2 | =GROUPBY(B2:C13,F2:F13,SUM,,2)
図4 地区と商品の2階層で集計し、第5引数［集計深さ］に「2」を指定して小計と総計を表示する。
図4で入力したGROUPBY関数の引数［集計深さ］を変えると、集計結果は次のように変化します。
図5 「0」を指定した場合、合計行は表示されない。
図6 「1」を指定した場合は下端に、「-1」を指定した場合は上端に合計行が表示される。
図7 「2」を指定した場合は下側に、「-2」を指定した場合は上側に小計行と総計行が表示される。
第6引数［並べ替え］の使い方
第6引数［並べ替え］では、並べ替えの基準となる列の番号を、［行フィールド］と［値］を横に並べて左から数えた番号で指定します。昇順（小さい順）で並べ替える場合は列番号をそのまま指定し、降順（大きい順）で並べ替える場合は列番号の前に「-」を付けて指定します。省略した場合は［行フィールド］の昇順で並べ替えられます。
例えば［行フィールド］に「商品」列、［値］に「数量」列と「売上」列を指定し、［並べ替え］に「-3」を指定した場合、3列目の「売上」の降順に並べ替えられます。
=GROUPBY(行フィールド, 値, 関数,,, ［並べ替え］)
セルH2 | =GROUPBY(C2:C13,E2:F13,SUM,,,-3)
図8 第6引数［並べ替え］に「-3」を指定して、3列目の「売上」の降順に並べ替える。
なお、［行フィールド］に複数の列を指定した状態で［値］を基準とした並べ替えを行うときは、第8引数［リレーション］も使用してください。
メモ
第7引数［フィルター］の使い方
第7引数［フィルター］では、集計対象のデータの抽出条件を指定します。条件の指定方法はFILTER関数と同じです。「FILTER関数 ● 表や配列からデータを抽出する (2)抽出条件の指定例」を参考にしてください。
下図では、「B2:B13="東地区"」という条件を指定して、東地区のデータだけを集計しています。
=GROUPBY(行フィールド, 値, 関数,,,, ［フィルター］)
セルH2 | =GROUPBY(C2:C13,F2:F13,SUM,,,,B2:B13="東地区")
図9 第7引数［フィルター］を使用して、「東地区」の売上を集計する。
■抽出条件の指定例
| 指定例 | 説明 |
|---|---|
| D2:D13>=1000 | 単価が「1000」以上 |
| A2:A13>=DATE(2025,2,1) | 日付が「2025/2/1」以降 |
| RIGHT(C2:C13,3)="101" | 商品の末尾が「101」 |
| (B2:B13="西地区")*(LEFT(C2:C13,1)="A") | 地区が「西地区」かつ商品の先頭が「A」 |
| (C2:C13="A-101")+(C2:C13="A-201") | 商品が「A-101」または商品が「A-201」 |
第8引数［リレーション］の使い方
第8引数［リレーション］では、［行フィールド］が階層になっている状態で［値］を基準に並べ替える場合の並べ替え方を次の数値で指定します。
| 設定値 | 説明 |
|---|---|
| 0 | 階層ごとに並べ替える。既定値。 |
| 1 | ［値］全体を並べ替える。小計は表示できない。 |
図10と図11では、［並べ替え］に「-3」を指定して、売上の降順に並べ替えを行っています。［リレーション］に「0」を指定するか、指定を省略した場合、地区ごとに売上が並べ替えられます。
=GROUPBY(行フィールド, 値, 関数,,, ［並べ替え］,, ［リレーション］)
セルH2 | =GROUPBY(B2:C13,F2:F13,SUM,,,-3,,0)
図10 ［リレーション］に「0」を指定すると、地区ごとに売上が降順に並ぶ。
［リレーション］に「1」を指定した場合は、階層を無視して売上全体が並べ替えられます。
セルH2 | =GROUPBY(B2:C13,F2:F13,SUM,,,-3,,1)
図11 ［リレーション］に「1」を指定するすると、売上全体が降順に並ぶ。