エクセル関数応用
GROUPBY関数が最強すぎる！Excelの集計作業が爆速に！

まず、最も伝統的な集計方法です。
Excel 2019までのバージョンを使っている方や、旧来の集計方法に慣れている方向けです。
画像の「スピルなし」の部分をご覧ください。

この方法では、項目ごとに手動で数式を入力する必要があります。
件数/合計: COUNTIFS や SUMIFS のように、IFにSが付いた関数を使えば、複数の条件でもデータを集計が可能になります。
最大値/最小値/中央値: MAXIFS や MINIFS が使えます。
中央値はMEDIANIFS関数が無いので面倒です。
MEDIANとIFを組み合わせて、「=MEDIAN(IF(A:A=D3,B:B))」このように少し難しい数式になります。
この方法は、新しい項目が増えた場合には数式を一つひとつコピー＆ペーストしなければなりません。

次に、Excel 2021以降で使える「スピル機能」を活用した集計方法です。
画像の「スピルあり」の部分をご覧ください。

スピル機能とは、1つの数式を入力するだけで、結果が自動的に複数のセルにこぼれ出す（スピルする）便利な機能です。
ユニークな項目: SORT と UNIQUE を組み合わせることで、元データから重複のない項目リストを自動で生成できます。
集計: 1つのセルにSUMIFS(B:B, A:A, D7#)と入力するだけで、D7#が自動的にユニークな項目リスト全体を指し、すべての項目の合計が一度に計算されます。
ただし、この方法ではMEDIAN関数は使えません。
MEDIAN関数は単一の結果しか返すことができません。
これらのスピル数式は、手動でのコピペ作業が不要になり、入力する数式もシンプルになります。

そして、Excel 365ユーザーにぜひ知ってほしいのが、この最新のGROUPBY関数です。
GROUPBY関数は、「スピルなし」「スピルあり」の集計作業をすべて自動化する画期的な機能です。

これまで個別の数式が必用だったものが、1つの数式で完結出来ます。
=GROUPBY(A:.A,B:.B,HSTACK(COUNTA,SUM,MAX,MIN,MEDIAN,PERCENTOF),1,0)
このように、たった1つの数式ですべての集計が完了します。
自動でユニーク化してくれます。
項目リストを事前に作成する必要はありません。
GROUPBY関数が自動でユニークな項目を抽出し、その項目ごとに集計を行います。
値の出力では、 HSTACK関数を使うことで複数の関数結果を横にまとめて出力することが出来ています。
COUNT、SUM、MAX、MIN、MEDIAN
GROUPBY関数を使いこなせば、これまで数十分かかっていた集計作業が、数秒で終わるようになります。
Excel 365を使っているなら、ぜひGROUPBY関数をお試しください。

同じテーマの記事
新着記事
アクセスランク