【Excel】GROUPBY関数でカテゴリごとの集計を柔軟に行う

Excelの比較的新しい関数のGROUPBY関数を使えば、ピボットテーブルを作成せずカテゴリごとのデータの集計を簡単に行うことができます。

GROUPBY関数でカテゴリごとの集計を柔軟に行う

たとえば、このような顧客の売上を記録した営業記録表からGROUPBY関数を使って顧客ごとの売上データを作成します。

GROUPBY(グループ化列, 集計列, 集計方法, [見出し行], [合計], [順序], [フィルター])
任意のカテゴリで集計を行うことができる。 「グループ化列」で集計したいカテゴリの列を選択を選択し、集計列で計算に使用したい数値データの列を選択します。集計方法には、合計、構成比、平均など計算の種類を指定する。 その他、見出し行、合計、順序、フィルターなどを指定することで、カスタマイズすることができる。

顧客ごとの売上データを集計するには、グループ化列に顧客の列を選択し、集計列に売上の列を選択します。合計を計算するには、集計方法に「SUM」を指定します。見出し行で「3」を選択すれば、元の表と同じ見出しを作成することができます。

=GROUPBY(B1:B25,D1:D25,SUM,3,1)

これで顧客ごとの売上が集計されます。

書式はコピーされないので、任意のものを設定します。

計算の方法は合計だけでなく、様々なものを選択できます。「PERCENTOF」を選択すれば、売上の構成比が表示されます。

=GROUPBY(B1:B25,D1:D25,PERCENTOF,3,1)

「AVERAGE」を選択すれば、売上の平均が表示されます。

=GROUPBY(B1:B25,D1:D25,AVERAGE,3,1)

さらに引数で表示させるデータをカスタマイズすることができます。5つ目の引数で0を指定すれば、一番下の合計欄を削除することができます。

=GROUPBY(B1:B25,D1:D25,SUM,3,0)

また、6つ目の引数では並び替えの順序を指定できます。今は顧客が五十音順に並んでいますが、「-1」を指定すれば逆順に並び替えることができます。

=GROUPBY(B1:B25,D1:D25,SUM,3,0,-1)

また、売上の大きい順に並び替えたい場合は、「-2」を指定し、2列目を降順に並び替えるようにします。

=GROUPBY(B1:B25,D1:D25,SUM,3,0,-2)

また、7つ目の引数では、集計を行う条件を指定できます。たとえば、商品Aの売上だけで集計したい場合は、商品の列を選択し、=”商品A”と入力します。

=GROUPBY(B1:B25,D1:D25,SUM,3,0,-2,C1:C25=”商品A”)

これで商品Aだけの売上が集計されます。

また、2つのカテゴリで集計したい場合は、1つ目の引数で複数の列を選択します。たとえば、顧客と商品2つの列を選択します。

=GROUPBY(B1:C25,D1:D25,SUM,3,0,1)

そうすると、顧客と商品2つのカテゴリで分けられた売上が集計されます。

以上がGROUPBY関数の使い方です。このようなデータは下にデータを追加していくことが多いと思いますが、普通の表ではGROUPBY関数の結果には反映されません。

表をテーブルに変換すれば、データの追加を集計結果に反映することができます。表の上でCtrl+Tを押してテーブルに変換します。

あとは先ほどと同じようにGROUPBY関数を入力します。

=GROUPBY(テーブル5[[#すべて],[顧客]],テーブル5[[#すべて],[売上]],SUM,3,1)

これで下にデータを追加してもGROUPBY関数の集計結果に反映されます。