【Excel】カテゴリーごとの集計ならSUMIFS/COUNTIFS関数よりGROUPBY関数が簡単

GROUPBY関数の構文

　以下はGROUPBY関数の構文です。実装されたばかりなのか引数名は英語表記ですが、意味は単純です。引数は8つありますが、最初の3つを指定すれば動作します。

GROUPBY関数の構文。横長くなってしまうので折り返しています。最初の［row_fields］［values］［function］の3つを指定すれば動作します

・row_fields … 集計の基準となるセル範囲
・values … 集計するセル範囲
・function … 集計方法(SUM、AVERAGE、COUNTなど)
・field_headers（省略可） … 見出しを表示するかどうかの指定（省略時は表示しない）
・total_depth（省略可） … 合計を表示するかどうかの指定（省略時は表示）
・sort_order（省略可） … 並べ替え順を指定する（省略時は1列目を昇順。「-」で降順となり、例えば「-2」は2列目で降順となる）
・filter_array（省略可） … フィルターの指定（例えば「C2:C125<>"渋谷"」のように指定する）
・field_relationship（省略可） … 階層的に並べ替えるかを指定（省略時は下の階層も並べ替え）

　例えば、以下のように指定するだけで、各販売店の合計値と総計を求められます。結果はスピルで表示されるので、数式をコピーする必要もありません。

=GROUPBY(C:C,F:F,SUM)

セルH2に「=GROUPBY(C:C,F:F,SUM)」（③）と入力した結果です

　ただ、「0」の表示が余計ですね。これは「C:C」のように列全体を指定したことにより、空白のセルも集計された結果です。また、合計値は不要、見出しが欲しいという場合はオプションの引数を指定しましょう。

オプションの引数を指定する

　見栄えを整えるためにオプションの引数を指定してみます。先ほどの式を修正して、見出し（ヘッダー）あり、合計金額を降順、総計行と空白行の集計（0）を非表示にしてみます。なお、見出しは自動的に判定されるので誤作動することもあります。入力する数式は以下の通りです。

=GROUPBY(C:C,F:F,SUM,3,0,-2,C:C<>"")

見出しを表示するので、数式はセルH1に入力します。セルH1に「=GROUPBY(C:C,F:F,SUM」まで入力した後、続けて「,」を入力するとチップが表示されます。ここでは［3-はい、表示します］（④）を選択します。「3」と直接入力しても構いません

続けて「,0,-2,C:C<>"")」と入力します（⑤）。「0」は総計行なし、「-2」は2列目を降順、「C:C<>""」はC列の空白以外という意味です

見出しが表示され、総計行と「0」は非表示になりました。2列目（販売金額）で降順に並べ替えられています

複数の列を集計する

　最初の引数［row_fields］には複数の列を指定することも可能です。例えば「C:D」のように指定すると、「席」ごとの「販売店」の集計結果を求められます。しかし、「販売店」ごとの「席」を集計したいこともありますよね。「日付」と「販売店」といった組み合わせも考えられます。

　そのような場合は、HSTACK関数を利用してください。指定したセル範囲を横方向に重ねて表示できる関数です。「HSTACK(C:C,B:B)」のように指定すれば、［販売店］列［席］列の順に並んだ配列を生成できます。ここでは、「販売店」ごとの「席」の販売数（COUNTA）を降順で取り出してみます。

=GROUPBY(HSTACK(C:C,B:B),E:E,COUNTA,3,0,-3,C:C<>"")

セルH1に「=GROUPBY(HSTACK(C:C,B:B),E:E,COUNTA,3,0,-3,C:C<>"")」と入力します（⑥）。集計方法は「COUNTA」、総計行と空白行の集計を非表示にして、3列目（販売数）で降順（-3）と指定しています

　オプションの引数にこだわると複雑になってきますが、あるカテゴリーごとの集計値をすばやく確認するなら、指定する引数は3つだけです。フィルターの切り替えや他の関数を使った集計よりもはるかに手軽ですよね。ぜひ使ってみてください。