Excel で 時短。②

〇 Excel新登場のGROUPBY関数を試す、担当者別の営業額をこれだけで一発自動集計。

　前回、担当者の営業額合計をExcelの別表で計算する場合、UNIQUE関数とSUMIF関数のコンビネーションを利用して自動集計した。一方で新たに関数の仲間に加わったGROUPBY関数を利用すると、これと同じことをたった1つの関数で行える。下記の表で実行してみよう。

関連記事：Excelで担当者別の営業額を自動集計、UNIQUE関数とSUMIF関数の楽々コンボ

画1、GROUPBY関数を用いてE2以下に担当者の一覧、F2以下に各担当者の営業額合計を一挙に算出する。新しい関数なので古いバージョンのExcelとは互換性がない。

　GROUPBY関数の引数は7つとやたらに多い。ただし今回は最初の3つしか使用しないので、下記の関数構造表ではその他の引数を省略した。なお、新しい関数のため引数名はいまだ英語名になっている。

GROUPBY関数　「検索／行列」ボタン

＝GROUPBY（Row_fields,Values,Function)

指定したフィールドに基づいてデータをグループ化して集計する。
①Row_fields　グループ化の対象となる配列または列範囲を指定する。
②Values　集計する配列または列範囲を指定する。
③Function　値の集計に使用する関数を指定する。

あっという間に集計が返る。

　ここではB列から一意の担当者をE2以下にリスト化し、それぞれの営業額合計をF2以下に集計する。初出の関数でもあるから、今回は「関数の引数」ダイアログを用いて丁寧に引数を設定したい。

　E2を選んだら、「検索／行列」ボタンから「GROUPBY」を選んで「関数の引数」ダイアログを開く。最初の3つの引数を次のように設定する。

Row_fields　テーブル1[担当者]
Values　テーブル1[営業額]
Function　SUM

画2、「関数の引数」ダイアログに引数を設定する。「テーブル1[担当者]」や「テーブル1[営業額]」はテーブルの構造化参照で、現状ではそれぞれB2:B10、C2:C10を示している。環境によって「テーブル1」は「1」以外の値になることもあるが気にしない。

　最初の引数「Row_fields」はグループ化の対象となる範囲を選ぶ。テキストボックスにカーソルを置いたあと、B2:B10を選ぶと、自動的に「テーブル1[担当者]」と入る。

　前にも説明したが、このような参照形式になるのはA1:C10がテーブルになっているからだ。これを構造化参照という。この場合、各列の範囲に列見出しの名称が名前として自動的につく。「担当者」の場合だと範囲はB2:B10になる。

　次の「Values」には集計する範囲を選ぶ。集計したいのは「営業額」だから、C2:C10を選ぶ。すると自動的に「テーブル1[営業額]」と入る。最後に「Function」には集計方法の関数名を指定する。合計だから「SUM」と入力する。以上で数式は、

=GROUPBY(テーブル1[担当者],テーブル1[営業額],SUM)。

になった。設定ができたら「OK」ボタンを押して数式を確定しよう。一挙に担当者の一覧と各担当者の営業額合計を計算できた。F7には総計である「合計」が出ているのが分かる。これはかなり快適だ。

画3、担当者の一覧と各担当者の営業額合計を計算できた。E2には「=GROUPBY(テーブル1[担当者],テーブル1[営業額],SUM)」という数式が入っている。

範囲の自動拡大や自動集計にも対応。

　続いて表に新しいデータを追加してみたい。A11に「1月31日」、B11に既存の担当者である「和田敦」、C11に「￥31,000」と入力する。すると集計側の表は、「担当者」の一覧はそのままで、「和田敦」さんの「営業額合計」が「￥128,500」になった。

画4、A11:C11に新しいデータを追加した。E2以下の「担当者」はそのままで、「和田敦」さんの「営業額合計」が「￥31,000」増えて「￥128,500」になった。

　さらに新しい担当者を追加してみる。A12に「1月31日」、B12に「石田透」、C12に「￥22,000」と入力する。集計表の「担当者」に「石田透」さんが追加となり、対応する「営業額合計」も表示できた。

　UNIQUE関数とSUMIF関数のコンボでいくか、それとも手っ取り早くGROUPBY関数でいくか、好みに応じて使い分けたい。

画5、A12:C12に新しいデータを追加した。新しい担当者のため集計表の「担当者」に「石田透」さんが追加となり、対応する「営業額合計」を表示できた。ただし漢字の並べ替えには難があるようで、この点は注意したい。