Excelを使用しているなら誰もが経験する「大量データの集計や分類」。 従来のピボットテーブルや関数の組み合わせでの集計もですが、実はもっと簡単かつ柔軟にデータを保持できる便利な方法があります。GROUPBY関数
を使えば、手間がかかる手間や複雑な関数が必要がなくなり、データの一括処理を行えます。データ分析をもっとやりたい方は必見です!
紹介記事の人物
橋本 由夏(はしもと ゆか)
Excelの作家(主に、フリーランスとして活動しています。)
著書:Excel厳選テクニック本を出版
職 業:Excel作家、ExcelのYouTuber、Excel・Wordの講演
出身地:熊本県
経 歴:民間、パソコンインストラクター、公的機関で勤務経験あり
GROUPBY関数
GROUPBY関数の、GROUPBYは、グループ化する関数です。対象となる列から、集計したい又。集計に利用する関数(集計関数)の最低3つを引数として指定します。例えば「商品別の平均販売価格を知りたい」といった場合にとても役にたちます。
GROUPBY関数の使い方
上記の様に、複数の関数で行っていたものがGROUPBY関数一発で集計可能です。具体的に見ていきましょう。
GROUPBY関数の引数
引数番号 | 引数名 | 必修OR省略 | 説明 | 上記の表 |
---|---|---|---|---|
1 | 行フィールド | 必修 | データをグループ化する範囲を指定します。基本的には、列を選択 | B2:B13 |
2 | 値 | 必修 | 集計するデータの列指向の配列または範囲 | E2:E13 |
3 | 集計方法 | 必修 | 集計方法を指定します。SUM(合計)などの関数を指定 | SUM、AVERAGE |
4 | フィールドのヘッダー | 省略可 | 0 : いいえ(ヘッダーなし、ヘッダー生成しない) 1 : はい、表示しません(ヘッダーあり、ヘッダー表示しない) 2 : いいえ、生成します(ヘッダーなし、ヘッダー生成する) 3 : はい、表示します(ヘッダーあり、ヘッダー出力する) |
ナシ |
5 | 合計深さ | 省略可 | 行ヘッダーに合計を含めるかどうかを決定します。 省略 : 自動: 総計と、可能な場合は小計。 0 : 合計なし 1 : 総計 2 : 総計と小計 -1 : 上部に総計 -2 : 上部に総計と小計 小計の場合、フィールドには少なくとも 2つの列が必要です。 |
ナシ |
6 | ソート | 省略可 | 省略時は行フィールドの昇順で並べ替えられます。行をソートする方法を示す数値。 | ナシ |
7 | フィルター | 省略可 | グループ化して集計する範囲を指定 | -2 |
GROUPBY関数の使用例
この様な、商品名販売数一覧表があります。
この中の、商品別の合計、そして、その総計を求める際、今までは、SUMIF関数や、ピポットテーブルで求めていました。コチラの集計を、GROUPBY関数だけで処理できます。
G2に数式を挿入します。具体的には、
=GROUPBY(B2:B13,E2:E13,SUM)を挿入します。
すると、右の表の様に、この関数1つで、商品別合計、そして、総合計を求める事が可能になります。
引数の集計方法を、SUMにした事により、商品ごとの合計を導き出す事が出来ます。AVERAGEにしたら、平均を求める事が出来ます。
商品別集計表を出す
この様に、商品とメーカーの2つの列を商品名として、絞り子む事が出来ます。
後は、同じように、数式を入れる事によって、商品名と、メーカの集計をする事が出来ます。
では、キーボード関係、パソコン関係ではいくらか?という小計と、全体の合計を表します。
この様に、【総計と小計】の項目が表示されますので、2を選択して、タブキーで決定します。
そうすると、各項目の、小計と、総計が表示されます。-2に設定すると、総計が上の方にでてきます。又、小計も上の方に表示されます。0にすると、小計も、合計も表示されません。
6番の引数、ソート
言葉の意味道りソートする、順番を並び替えるという意味になります。
=GROUPBY(B2:B13,E2:E13,SUM,,,-2)
この様な数式を入れると、下記の様に、なります。
この様に、降順に並び替える事も出来ます。2にすると、昇順になります。このように、プラスか?マイナス?かで、昇順、降順に並び替えられます。
まとめ
今回はExcelの新機能、GROUPBY関数について詳しく解説しました。これまで複雑なピボットテーブルやSUMIF関数などで対応していました作業を、1つの関数で処理できます。 ぜひ、日々の業務やデータ分析してみてください。
コメント