GROUPBY関数 ● グループごとに集計する (3)関数の指定方法

［Microsoft 365］

Excelの GROUPBY（グループ・バイ）関数は、表のデータをもとにグループ集計を行う関数です。集計の計算方法は、第3引数［関数］で指定します。ここでは［関数］のさまざまな指定方法を紹介します。

GROUPBY関数の基本的な使い方やその他の引数については、以下の記事を参考にしてください。

1 書式
2 第3引数［関数］の選択肢
3 使用例1…基本的なグループ集計
4 使用例2…「PERCENTOF」で構成比を求める
5 使用例3…「ARRAYTOTEXT」で文字列データを「,」で区切って連結する
6 使用例4…「最高点と最低点」のように2つの関数を指定する
7 LAMBDA関数とイータ縮小ラムダ

書式

グループごとに集計する
=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 ：階層を無視して並べ替える。

第3引数［関数］の選択肢

GROUPBY関数の第3引数［関数］は、集計の計算方法を指定するための引数です。「SUM」を指定すれば合計、「AVERAGE」を指定すれば平均、という具合にSUM関数やAVERAGE関数の機能を使用した集計が行われます。ポップヒントの選択肢から選ぶだけで簡単に入力できます。

図1　第3引数［関数］はポップヒントから選択できる。

選択肢には下表の種類があります。表中の「イータ縮小ラムダ」「明示的なLAMBDA関数」については、本記事の最後に解説します。

■［関数］の選択肢

選択肢（イータ縮小ラムダ）	説明	明示的なLAMBDA関数
SUM	合計	LAMBDA(x,SUM(x))
PERCENTOF	割合	LAMBDA(x,y,PERCENTOF(x,y))
AVERAGE	平均	LAMBDA(x,AVERAGE(x))
MEDIAN	中央値	LAMBDA(x,MEDIAN(x))
COUNT	数値の数	LAMBDA(x,COUNT(x))
COUNTA	データ数	LAMBDA(x,COUNTA(x))
MAX	最大値	LAMBDA(x,MAX(x))
MIN	最小値	LAMBDA(x,MIN(x))
PRODUCT	積	LAMBDA(x,PRODUCT(x))
ARRAYTOTEXT	「,」で区切った文字列結合	LAMBDA(x,ARRAYTOTEXT(x))
CONCAT	文字列結合	LAMBDA(x,CONCAT(x))
STDEV.S	不偏標準偏差	LAMBDA(x,STDEV.S(x))
STDEV.P	標本標準偏差	LAMBDA(x,STDEV.P(x))
VAR.S	不偏分散	LAMBDA(x,VAR.S(x))
VAR.P	標本分散	LAMBDA(x,VAR.P(x))
MODE.SNGL	最頻値	LAMBDA(x,MODE.SNGL(x))
LAMBDA	独自に定義した関数

使用例1…基本的なグループ集計

図2では、第1引数［行フィールド］に「商品ID」と「商品名」のセルB2～C13、第2引数［値］に「売上」のセルF2～F13、第3引数［関数］に「SUM」を指定して、商品ごとに売上の合計を求めています。

=GROUPBY(行フィールド, 値, 関数)

セルH2 | =GROUPBY(B2:C13,F2:F13,SUM)

図2　商品ごとに売上を合計する。
なお、集計結果の「売上」欄（セルJ2～J5）には、手動で通貨表示形式が設定してある。

図2の数式の「SUM」を「AVERAGE」に変えれば商品ごとの売上平均、「MAX」に変えれば商品ごとの最大売上、というように［関数］を変更することで集計の計算の種類を変更できます。ただし、どの計算をした場合も最下行の行見出しには「合計」「総計」などと表示されます。

使用例2…「PERCENTOF」で構成比を求める

［関数］に「PERCENTOF」を指定すると、［値］の列全体を100%として各項目の比率を求められます。図3では、各商品の売上構成比を求めています。

セルH2 | =GROUPBY(B2:C13,F2:F13,PERCENTOF)

図3　「PERCENTOF」を指定すると、項目ごとの構成比を求めることができる。

メモ

PERCENTOF関数

PERCENTOF関数をワークシートに入力して使う場合は、「PERCENTOF(数値,数値全体)」の書式で［数値全体］を100％としたときの［数値］の割合を求めます。下図の数式では、「10000÷(10000+6000+4000)」が求められます。

［数値］にセル範囲を指定することもできます。下図の数式では、「(10000+6000)÷(10000+6000+4000)」が求められます。

使用例3…「ARRAYTOTEXT」で文字列データを「,」で区切って連結する

［関数］の選択肢には、文字列連結を行う関数が「ARRAYTOTEXT」と「CONCAT」の2つあります。そのうちの「ARRAYTOTEXT」を使用すると、［値］のデータをカンマ「,」で区切って連結できます。

図4では、所属ごとに社員名を表示しています。合計行は必要ないので、第5引数［集計深さ］に「0」を指定して非表示にしました。

セルF2 | =GROUPBY(D2:D9,B2:B9,ARRAYTOTEXT,,0)

図4　「ARRAYTOTEXT」を指定すると、項目ごとにデータを「,」で区切って連結できる。

図4の数式の「ARRAYTOTEXT」を「CONCAT」に変えると、「市川村岡江原」のようにデータが区切り文字なしで連結されます。

使用例4…「最高点と最低点」のように2つの関数を指定する

1列分の［値］に対して複数の計算方法で集計するには、HSTACK関数を使用します。この関数は、引数に指定した複数の配列を左右に結合する関数です。

図5では、選択科目ごとに得点のデータ数、平均値、最大値、最小値を求めています。一番下の「合計」行には、得点全体のデータ数、平均値、最大値、最小値が求められます。また、一番上の行には使用した関数名が表示されます。

セルF2 | =GROUPBY(C2:C11,D2:D11,HSTACK(COUNT,AVERAGE,MAX,MIN))

図5　HSTACK関数を使用して「COUNT」「AVERAGE」「MAX」「MIN」を計算する。

戻り値の一番上に表示される関数名を非表示にしたい場合は、DROP関数を使用します。DROP関数は「DROP(配列,［行数］,［列数］)」の書式で、［配列］から指定した行数や列数を除外します。「DROP(配列,1)」とすると、配列の先頭から1行目を除外できます。

セルF2 | =DROP(GROUPBY(C2:C11,D2:D11,HSTACK(COUNT,AVERAGE,MAX,MIN)),1)

図6　DROP関数を使用して1行目を除外する。

ちなみに、図5の数式のHSTACK関数をVSTACK関数に変えると、集計値が縦に並びます。VSTACK関数は、引数に指定した複数の配列を上下に結合する関数です。

セルF2 | =GROUPBY(C2:C11,D2:D11,VSTACK(MAX,MIN))

図7　VSTACK関数を使用して「MAX」「MIN」を縦方向に表示する。

LAMBDA関数とイータ縮小ラムダ

ヘルプによると、GROUPBY関数の第3引数［関数］には「明示的またはetaの縮小ラムダ」を指定するとあります。［関数］のポップヒントには「SUM」「AVERAGE」「COUNT」などの関数名が並びますが、これらはLAMBDA関数の数式を簡略化したもので「イータ縮小ラムダ関数」と呼びます。

例えばイータ縮小ラムダ関数「SUM」は、「LAMBDA(x,SUM(x))」というLAMBDA関数式の簡略版です。GROUPBY関数の第3引数［関数］には本来「LAMBDA(x,SUM(x))」と記述するところを「SUM」と簡略記述してもよいことになっています。次の2つの数式は同じ結果を戻します。

セルE2 | =GROUPBY(B2:B9,C2:C9,SUM)

セルE2 | =GROUPBY(B2:B9,C2:C9,LAMBDA(x,SUM(x)))

図8　［関数］にはイータ縮小ラムダ関数とLAMBDA関数のどちらを指定してもよい。

■イータ縮小ラムダ関数の例

イータ縮小ラムダ	説明	明示的なLAMBDA関数
SUM	合計	LAMBDA(x,SUM(x))
PERCENTOF	割合	LAMBDA(x,y,PERCENTOF(x,y))
AVERAGE	平均	LAMBDA(x,AVERAGE(x))
MEDIAN	中央値	LAMBDA(x,MEDIAN(x))
COUNT	数値の数	LAMBDA(x,COUNT(x))
COUNTA	データ数	LAMBDA(x,COUNTA(x))
MAX	最大値	LAMBDA(x,MAX(x))
MIN	最小値	LAMBDA(x,MIN(x))

そもそもLAMBDA関数は、独自の計算式を関数化するための特殊な関数です。LAMBDA関数には、より便利に使うための「ヘルパー関数」が用意されています。ヘルパー関数とは、配列や値をLAMBDA関数の変数に渡す役目をする関数です。イータ縮小ラムダは、ヘルパー関数の記述を簡略化するための仕組みといえます。

GROUPBY関数は、ヘルパー関数の1つです。「LAMBDA(x,SUM(x))」のようにLAMBDA関数で変数を1つ指定した場合、GROUPBY関数から変数「x」にグループごとの配列が渡されます。また、「LAMBDA(x,y,PERCENTOF(x,y))」のように変数を2つ指定した場合、GROUPBY関数から変数「x」にグループごとの配列が渡され、変数「y」に列全体の配列が渡されます。