SUMIF や COUNTIF を使わずに項目ごとの集計をする（GROUPBY 関数）その1

GROUPBY 関数は、テーブルなどの表のデータをグループごとにまとめ、合計や個数などを集計する関数です。
ピボットテーブルを作成せずに数式でグループごとの集計を行えるため、元データが変更されたとき、「更新」作業が不要です。

たとえば、下図のテーブルでエリアごとの出荷数量の合計を知りたいときは、SUMIF 関数を使った数式を、エリアの数分作成してきました。(もちろん条件の部分はセル参照でも OK)

GROUPBY ならこんな感じで、全エリアの数量の合計を 1 つの数式で算出できます。
GROUPBY 関数は、Microsoft 365 の Excel で使える関数で、SORT や FILTER などと同様にスピルに対応しており、引数による指定次第で、並べ替えや見出し、小計や合計の表示なども設定できます。

集計方法を選択できるため、合計だけでなく、個数も指定でき、COUNTIF で対応していた件数や個数も求められます。

Excelに限らずですが、「BY」というキーワードのつく名前の関数があります。
この「BY」は「〜ごとに」という意味で、データを指定した単位でまとめて処理することを表していると覚えるとよいでしょう。
GROUPBY や PIVOTBYはなにごとに集計するのかの単位を指定する関数で、BYROWやBYCOLは行ごとや列ごとに処理を行う関数です。

GROUPBY 関数の構文

必須なのは先頭の 3 つです。なにごとに、どの列を、どのように集計するか指定します。

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

	引数	説明
1	行フィールド（必須）	何ごとにまとめるのか、行のグループ化に使う列を指定する。複数列の指定も可能。
2	値（必須）	集計する列を指定する。合計や件数を求めるデータが格納されている列。複数列の指定も可能。
3	関数（必須）	SUM、COUNT、AVERAGE、PERCENTOF などを使って、値の集計方法を指定する。
4	[フィールドヘッダー]	見出し行の表示 / 非表示を指定する。 3 を指定すると、見出しが含まれている場合、表示されます。
5	[集計レベル]	総計や小計の表示 / 非表示を指定する。
6	[並べ替え順]	結果の並べ替え順 (昇順 / 降順) を列の位置で指定する。「-1」だと 1列目を降順で並べ替えられる。複数列をキーにする場合は、{-1, -2} のように括弧で指定する。
7	[フィルター]	特定のエリアなど、「エリア列が "エリアA"」のように行を絞り込む条件を指定する。
8	[列の関係]	内部的に階層的な並べ替えして表示するかどうかを指定する。0 を指定、または省略すると、行の項目を階層的に並べ替えられる。下位の項目も並べ替えられ、[並べ替え順] が指定されていない列は昇順となる。1 を指定すると、指定された並べ替え順のみが適用され、小計を表示できない。

GROUPBY による集計

ここではシンプルに、必須の引数だけを指定して、テーブルの [エリア] ごとの [出荷数量] 列の値の合計を算出します。あとから見出しの表示を追加するため、見出し行となる 1 行目も範囲に含めてやってみます。

第 1 引数の 行フィールド に、何ごとにグループ化するのか、見出しも含めて列を指定します。

複数の列を指定することもできます。
第 2 引数の値に、見出しも含めて、集計する列を指定します。
第 3 引数の関数に、集計方法に合う関数を指定し、数式を確定します。
エリアごとの出荷数量の合計値が表示されます。
見出しはなし、総計は表示されています。(ここではわかりやすいように合計のところに太字を設定しています)

複数列を指定した場合はこんな感じ↓
並べ替えや列の関係を指定していないので、エリアの昇順で並び、エリア内で担当IDの昇順に並んでいます。
数式を編集状態にして、第 4 引数に「3」を指定します。
見出しが表示されます。

複数列を指定した場合はこんな感じ↓
テーブルのデータが変更されたり、追加や削除が行われたりしたら、反映されます。