Microsoft Excelに新関数・GROUPBY関数が登場しました！
GROUPBY関数は、膨大なデータを様々な基準で集計することができる超絶に便利な関数です。
この関数を使えば、SUMIF関数やAVERAGEIF関数、COUNTIF関数、ピボットテーブルなど、さまざまな集計関数を使用する必要がなくなり、GROUPBY関数で代用することができます。
将来、GROUPBY関数は、XLOOKUP関数やCOUNTIF関数やSUMIF関数などに並ぶほどの定番関数になることが予想されています。
この記事では、エクセルのGROUPBY関数の使い方と、実務に役立つ事例集を紹介するので、実践に役立てください。
エクセルのGROUPBY関数とは？
GROUPBY関数の読み方は、「グループ・バイ関数」です。
エクセルのGROUPBY関数とは、指定したセル範囲のデータを①集計、②並べ替え、③フィルター処理することが出来る関数です。
「①集計」は、合計(SUM)を算出するだけでなく、平均値(AVERAGE)やセルの個数(COUNT)など様々な計算方法を指定することができます。
GROUPBY関数で指定できる機能(１～４がおすすめ)
- SUM・・・合計
- AVERAGE・・・平均
- COUNT・・・数値セルの個数
- COUNTA・・・空白セルを除くすべてのセルの個数
- MAX・・・最大値
- MIN・・・最小値
- MEDIAN・・・中央値
- CONCAT・・・テキストの結合
- PERCENTOF・・・パーセントの算出
- PRODUCT・・・掛け算して積を算出
- STDEV.S(P)・・・標準偏差
- VAR.S(P)・・・不偏分散
- MODE.SNGL・・・最頻値
- LAMBDA・・・カスタム関数を作成
- ARRAYTOTEXT・・・配列を文字列に変換
さらに、複数条件で集計したり、元データの合計行を集計の対象から外すなど、いろいろなアウトプットするデータの調整も可能です。
ただ、GROUPBY関数を使用できるExcelバージョンは、25/4月時点でMicrosoft365(サブスクリプション)のみとなっています。
GROUPBY関数を使用できないユーザーは、今後、新バージョンで利用できるようになるまで、どんなことができる関数なのか事前に学習しておくことをおすすめします。
GROUPBY関数の書式と引数
GROUPBY関数の書式と引数は、以下のとおりです。
GROUPBY関数を利用しようとすると、引数は英語で表記されます。
英語版
=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])
分かりやすいように日本語版で書くと、以下の通りです。
日本語版
=GROUPBY(行フィールド,値,関数,[ヘッダー],[集計の深さ],[並べ替え],[フィルター],[リレーション])
GROUPBY関数の引数は８つあり、利用するのが難しそうに感じるかもしれませんが、[ ]で囲まれた後半５つの引数は省略可能です。
そのため、簡単な集計であれば、前半の３つの引数「行フィールド」「値」「関数」で利用できます。
GROUPBY関数の引数のそれぞれの内容は、以下の表のとおりです。
引数の具体的な使い方は、以降の見出しを読み進めていただければイメージがつくと思うので、ここで理解できなくても大丈夫です。安心してください。
| 引数の名前 | 説明 | |
| 1 | 行フィールド row_fields | グループ化する列を指定。複数の列を指定することも可能。 |
| 2 | 値 values | 集計する対象の列を指定。複数の列を指定することも可能。 |
| 3 | 関数 function | どの関数を使って集計するかを指定する。選択出来る関数は以下のとおり。 SUM、PERCENTOF、AVERAGE、MEDIAN、COUNT、COUNTA、MAX、MIN、PRODUCT、ARRAYTOTEXT、CONCAT、SRDEV.S、STDEV.P、VAR.S、VAR.P、MODE.SNGL、LAMBDA |
| 4 | ヘッダー field_headers | 指定する行フィールドや値に列見出しが含まれるかによって、「0」～「3」を指定。省略した場合、エクセルが自動で集計方法を判断する。 0 ： 指定した列に列見出しが含まれない⇒戻り値に列見出しを表示しない 1 ： 指定した列に列見出しが含まれる⇒戻り値に列見出しを表示しない 2 ： 指定した列に列見出しが含まれない⇒戻り値に列見出しを自動表示 3 ： 指定した列に列見出しが含まれる⇒戻り値に列見出しを表示 |
| 5 | 集計の深さ total_depth | 小計や総計を表示するかどうか以下の数値の中から指定する。省略した場合、エクセルが自動で集計方法を判断する。 0 ： 小計や総計を表示しない 1 ： 総計を表示する 2 ： 小計と総計を表示する -1 ： 上部に総計を表示する -2 ： 上部に小計と総計を表示する |
| 6 | 並べ替え sort_order | 並べ替えしたい列が左から何列目か指定。昇順の場合は正数、降順の場合は負数で入力する。省略した時は「行フィールド」が昇順で並べ替えられる |
| 7 | フィルター filter_array | 省略可能。集計対象を絞り込むための抽出条件を指定 |
| 8 | リレーション field_relationship | 「行フィールド」に複数の列を指定する場合、「0」か「1」を指定 0 ： 階層ごとに並べ替え(省略可) 1 ： 階層を無視して並べ替え |
GROUPBY関数の基本的な使い方
以下の表を使って、GROUPBY関数の使い方を紹介します。
GROUPBY関数は、元データにテーブル機能を設定しておくと、引数の設定がしやすくなり、さらに、空白行を自動で集計から除くことができます。
今回は、テーブル機能を①使わない場合と②使った場合、ぞれぞれのGROUPBY関数の使い方を紹介します。
GROUPBY関数の元データ
パターン１．テーブル機能の設定なし
パターン２・テーブル機能の設定あり
テーブル機能は、元データを選択して、「Ctrl＋T」キーのショートカットキーを押せば簡単に設定できます。
テーブル機能について詳しく知りたい方は、以下のリンクを参考にしてください。
GROUPBY関数/テーブルを設定しない場合
GROUPBY関数を使って、「地域」別の「販売数量」を集計する方法を紹介します。
手順１．GROUPBY関数を挿入
まず、集計結果を表示させたいセルに、GROUPBY関数を挿入します。
=GROUPBY(
手順２．GROUPBY関数の第１引数を指定
GROUPBY関数の第１引数「行フィールド(row_fields)」を指定します。「地域」を基準にデータを集計したいので、C列の列番号をクリックします。クリックしたら、「,」(カンマ)を入力します。
=GROUPBY(C:C,
手順３．GROUPBY関数の第２引数を指定
GROUPBY関数の第２引数「値(values)」を指定します。「販売数量」を集計したいので、F列の列番号をクリックします。クリックしたら、「,」(カンマ)を入力します。
=GROUPBY(C:C,F:F,
手順４．GROUPBY関数の第３引数を指定
GROUPBY関数の第３引数「関数(function)」を指定します。合計で集計したいので、「SUM」と入力します。
=GROUPBY(C:C,F:F,SUM)
手順５．GROUPBY関数を確定
数式が完成したので、「Enter」キーでGROUPBY関数を確定します。
そうすると、スピル機能が働いて、隣接しているセル範囲に「地域別」の「販売数量」が自動で集計されて表示されます。
ただ、集計結果の下から２行目に「0」の行が作成されました。
これは、第１引数「列フィールド」と第２引数「値」を列番号で指定したため、値が入力されていない空白セルの集計が行われたことが原因です。
空白セルによる「0」を表示したくない場合は、列番号を指定せず、データが入力されているセル範囲だけを指定してください。(例：C:C⇒C2:C15)
もしくは、次の見出しで紹介するテーブル機能や第7引数「フィルター」を使用することで、「0」を非表示にできます。
GROUPBY関数/テーブルを設定した場合
続いて、元データにテーブルを設定して、GROUPBY関数を使う方法を紹介します。
テーブルを使用した表に対してGROUPBY関数を使用すると、後から最下行にデータを追加しても、自動で数式が調整されます。
手順１．表にテーブル機能を設定
表内の任意のセルで「Ctrl＋T」キーを押して、テーブルを設定します。
テーブル機能の設定方法が分からない方は、以下の記事を参考にしてください。
手順２．GROUPBY関数の第１引数を指定
GROUPBY関数の第１引数「行フィールド(row_fields)」を指定します。「地域」を基準にデータを集計したいので、C2セルからC11セルをドラッグして選択します。そうすると、テーブルの名前と列の名称が自動で表示されます。
=GROUPBY(テーブル1[地域],
手順３．GROUPBY関数の第２引数を指定
同様に、GROUPBY関数の第２引数「値(values)」を指定します。「販売数量」を集計したいので、F2セルからF11セルをドラッグして選択します。
=GROUPBY(テーブル1[地域],テーブル1[販売数量],
手順４．GROUPBY関数の第３引数を指定
GROUPBY関数の第３引数「関数(function)」を指定します。合計で集計したいので、「SUM」と入力します。
=GROUPBY(テーブル1[地域],テーブル1[販売数量],SUM)
手順５．GROUPBY関数を確定
数式が完成したので、「Enter」キーでGROUPBY関数を確定します。そうすると、隣接しているセル範囲に「地域別」の「販売数量」が自動で集計されて表示されます。
手順６．最下行にデータを追加
試しに元表の最下行にデータを追加します。そうすると、テーブル範囲は自動で拡張されるので、GROUPBY関数の結果も自動で反映されます。
このように、元表にテーブルを設定しておくと、テーブルの設定範囲が自動で調整されるため、GROUPBY関数の結果が自動で調整される、というメリットがあります。
引数の設定も簡単になるので、テーブルの操作に慣れている方は、ぜひ一緒に利用してみてください。
GROUPBY関数を使った事例を紹介
GROUPBY関数の４つ目以降の引数は省略可能ですが、これらの引数を使用すると、さまざまなデータ加工が可能となります。
ここからは、GROUPBY関数を使った事例集を紹介します。
GROUPBY/事例１．複数条件(複数列)で集計
GROUPBY関数の第１引数「行フィールド」に複数の列を指定することで、より高度なデータ集計が可能です。
例えば、下のように、第１引数に「C1: D20」と隣接している２列を指定します。
=GROUPBY(C1:D20,F1:F20,SUM)
そうすると、下のように、「地域」「販売店舗」別の販売数量を集計することができます。
複数の列が離れている場合は、HSTACK関数を使用します。
HSTACK関数とは、「異なる列のデータを横方向に統合して１つにまとめる関数」です。
=HSTACK(配列1,配列2‥‥)
⇩HSTACK関数の詳しい使い方
下のように、GROUPBY関数の第１引数にHSTACK関数を挿入して、C列とE列の２列を指定します。
=GROUPBY(HSTACK(C1:C13,E1:E13),F1:F13,SUM)
そうすると、下のように、離れている２列の「地域」「商品名」別の販売数量を集計することができます。
また、GROUPBY関数は、第２引数「値」も複数列で指定することができます。
下の数式のように指定することで、「地域」「販売店舗」別の「販売数量」と「販売金額」を集計することができます。
=GROUPBY(C1:D13,F1:G13,SUM)
GROUPBY/事例２．小計と総計を表示
GROUPBY関数の第５引数「集計の深さ(total_depth)」に以下の数値を指定すると、集計結果に小計や総計を表示することができます。
0 ： 小計や総計を表示しない
1 ： 総計を表示する
2 ： 小計と総計を表示する
-1 ： 上部に総計を表示する
-2 ： 上部に小計と総計を表示する
小計と総計を表示したいときは、第５引数に「2」を指定します。第4引数「ヘッダー」は入力しないので、第5引数の手前には「,」が2つ必要です。
=GROUPBY(B1:C20,E1:E20,SUM,,2)
そうすると、上の図のように、集計結果に小計と総計を挿入することができます。
GROUPBY/事例３．月単位の日付で集計
GROUPBY関数の第１引数「行フィールド」に、日付列を入力すると、下の図のように、日別にデータが集計されてしまいます。
こんな時、GROUPBY関数の第１引数の中にTEXT関数を挿入することで、年月別にデータを集計することができます。
=GROUPBY(TEXT(B1:B19,"yyyy/mm"),E1:E19,SUM)
TEXT関数はセルの表示形式を変更するエクセルの定番関数です。以下の記事で詳しい使い方を紹介しているので、合わせて参考にしてください。
GROUPBY/事例４．空白行による「0」を表示しない方法
GROUPBY関数の第１引数や第２引数に空白行のセル範囲が含まれていると、下の図のように、集計結果に「0」が表示されます。
=GROUPBY(B:B,E:E,SUM)
上記で紹介したように元データにテーブル機能を使うことで、空白行を集計の対象から除外することができますが、もう一つの方法を紹介します。
GROUPBY関数の第7引数「フィルター(filter_array)」に、「B列が空白以外の場合(B:B<>””)」というフィルター条件を数式で設定すれば、0を非表示にすることができます。
=GROUPBY(B:B,E:E,SUM,,,,B:B<>"")
第４引数から第６引数は指定しない場合、第７引数の手前には「,」が４つ必要です。
そうすると、上記のように、B列に空白セルが含まれていても、GROUPBY関数の集計結果に0が表示されることは無くなります。
GROUPBY/事例５．棒グラフを自動更新
GROUPBY関数の集計結果に対して、棒グラフや折れ線グラフを挿入することもできます。
GROUPBY関数の結果が表示されたセル範囲を選択して、「挿入」タブから作成したいグラフを選択します。
そうすると、下の図のように、棒グラフが挿入されます。
元データの最下行にデータを追加すると、GROUPBY関数の結果だけでなく、グラフにも自動で更新したデータが反映されます。
ただ、元表の最下行に追加したデータが、GROUPBY関数の結果の最下行に挿入された場合はグラフは自動で更新されません。
その場合は、グラフが参照しているセル範囲を変更してください。
GROUPBY/事例６．昇順/降順で並べ替え
GROUPBY関数の第6引数「並べ替え(sort_order)」を使うことで、集計結果と昇順か降順で並べ替えすることができます。
並べ替えしたい列が左から数えて何列目か数値で指定します。昇順の場合は正数、降順の場合は負数で入力します。省略した時は「行フィールド」が昇順で並べ替えられます。
例えば、下の図に対して、GROUPBY関数で第６引数に「-2」と入力すると、「販売数量」列(２列目)を降順(負の数値)で並べ替えすることができます。
=GROUPBY(B1:B17,E1:E17,SUM,,,-2)
第４引数から第５引数は指定しない場合、第６引数の手前には「,」が３つ必要です。
GROUPBY/事例７．フィルターで条件に合うデータを表示
GROUPBY関数の第7引数「フィルター(filter_array)」を使用すると、特定の条件に合うデータのみを表示することができます。
例えば、B列の「地域」列が「大阪」の列だけの販売数量を表示したい場合は、以下のようにGROUPBY関数を入力します。
=GROUPBY(B1:B17,E1:E17,SUM,,0,,B1:B17="大阪")
第7引数に「B1:B17="大阪"」と入力することで、「B列の値が”大阪”」というフィルター条件を設定しています。
もし、「B列の値が”大阪”もしくは”東京”」という条件を設定したい場合は、以下のように条件を設定します。
=GROUPBY(B1:B17,E1:E17,SUM,,0,,(B1:B17="大阪")+(B1:B17="東京"))
