ピボットテーブルの任意の複数アイテムをまとめて集計する方法(グループ化)[ピボットテーブル基礎]
AさんAさん

ピボットテーブルの元データにカテゴリー的なフィールドがないですが、特定のアイテムをいくつかまとめて集計したいです。

この場合、何か良い方法はありますか?

森田森田

そうした場合、ピボットテーブルの「グループ化」を使えば良いです!

では、詳細を解説していきますね。

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

  • Excelでデータの集計・分析作業を行うことが多い方
  • ピボットテーブルを実務で使う機会がある方
  • ピボットテーブルの複数アイテムを任意にグルーピングして集計したい方

前提条件

この記事はピボットテーブルの概要を理解していることが前提です。
→まず、以下の記事で概要を把握することをおすすめします。

元データ側に上位の階層のフィールドを加えられない場合に「グループ化」が有効

通常、特定のフィールドのデータが細かい場合、元データ側にそのフィールドの上位の階層のフィールドを差し込んでから、ピボットテーブルで集計することがベターです。

たとえば、商品単位で粒度が細かい場合、「商品カテゴリ」や「産地」などの別軸の上位フィールドを差し込むなどのイメージです。
(他には、担当者単位なら「部署」、都道府県単位なら「エリア」など)

しかし、何らかの事情で元データへのデータ追加が難しいといった場合は、ピボットテーブルの「グループ化」が有効です。

これで、ピボットテーブルレポート側の任意の複数アイテムをひとまとめに集計できます。

では、ピボットテーブルのグループ化の設定手順を確認していきましょう。

ピボットテーブルのグループ化の設定手順

今回の前提として、すでにピボットテーブルの集計条件がセットされた状態からスタートします。

ピボットテーブルのグループ化は、以下の2ステップとなります。

STEP1】任意のアイテムをグループ化

まず、ピボットテーブルレポート上で任意のアイテムを複数選択(①)の上、右クリック(②)し、「グループ化」をクリック(③)します。

当然ですが、手順①で単一アイテムを選択している状態で手順③まで進むと、以下のエラーメッセージが表示されます。よって、手順①は必ず複数アイテムを選択しましょう。

選択対象をグループ化することはできません。

【参考】手順②③はリボン経由でもOK

手順②③は以下のようにリボン経由(リボン「ピボットテーブル分析」タブ→「グループの選択」)でもOKです。

どちらでも効果は一緒なので、お好みの方をお使いください。

STEP2】任意のグループ名を入力

これで手順①で選択していたアイテムが「グループ1」というグループとなります。

あとは、数式バーで任意のグループ名を入力(④)すれば完了です。

手順①で選択していなかったアイテムはそれぞれと同じ名前のグループ扱いとなります。
別のグループを作りたい場合は、手順①~④をグループの数だけ繰り返しましょう。

任意のグループで集計されていればOK

今回は設定した通り、上から4つのアイテムをひとまとめに集計することができました!

グループ化を行うと、「ピボットテーブルのフィールド」ウィンドウ上には、グループ化を行う元のフィールド名に連番がついたフィールドが新設されます。
例)「商品名」フィールドを元にグループ化した場合は「商品名2」が新たに作成される。

【参考】グループを解除したい場合

グループを解除したい場合は、「グループ解除」というコマンドを使いましょう。

こちらも右クリックメニュー・リボンの2パターンありますが、どちらでも良いです。

いずれも、任意のグループを選択(①)したら、以下のいずれかの手順を踏んでください。

  • 右クリック(②)→「グループ解除」をクリック(③)
  • リボン「ピボットテーブル分析」タブをクリック(②)→「グループ解除」をクリック(③)

「行」・「列」ボックス内からグループのフィールドを外してもグループは解除された状態となります。
ただし、この方法だと「ピボットテーブルのフィールド」ウィンドウ上にはグループのフィールドが残ったままとなるため、再度グループ化する可能性がある場合はこちらの方法がおすすめです。
(「グループ解除」コマンドは、「ピボットテーブルのフィールド」ウィンドウ上からグループのフィールドを削除します)

【参考】グループは展開・折りたたみが可能

グループにした場合、グループ配下のアイテムの表示・非表示を行うことが可能です。

表示する場合は「展開」、非表示にする場合は「折りたたみ」と言います。

展開・折りたたみする対象のグループを個別・全体にするかで方法は異なります。

それぞれの方法は以下の通りです。

個別のグループを展開・折りたたみしたい場合

ピボットテーブル上の+」「-」をクリックすればOKです。

ちなみに、「+」をクリックすると展開、「-」をクリックすると折りたたみとなります。

一応、右クリックメニューでも個別のグループの展開・折りたたみは可能ですが、手順が増えるため、「+」「-」をクリックする方法がおすすめです。

全体を展開・折りたたみしたい場合

全グループを一括で展開・折りたたみする方法は、右クリックメニューとリボン経由の2パターンあります。

いずれも、ピボットテーブル上のグループのあるフィールドのいずれかのアイテムを選択(①)した状態で以下のいずれかの手順を踏んでください。

  • 右クリック(②)→「展開/折りたたみ」をクリック(③)→「フィールド全体を展開」or「フィールド全体を折りたたみ」をクリック(④)
  • リボン「ピボットテーブル分析」タブをクリック(②)→「フィールドの展開」or「フィールドの折りたたみ」をクリック(③)

複数フィールドにそれぞれグループがある場合、手順①で選択したフィールドとその下位のフィールドが展開・折りたたみの対象となります。
下位のフィールドだけを一括で展開・折りたたみしたい場合は注意しましょう。

【参考】日付・時刻のフィールドは自動的にグループ化される

ピボットテーブルの仕様として、日付・時刻のフィールドは自動的にグループ化されます。

たとえば、「受注日」などの日単位のフィールドだと、「月」でグループ化されるなどです。

もし、「年」などの別な単位でグループ化したい場合、上記のグループ化の手順③まで進むと、次のように「グループ化」ダイアログが起動し、グループ化する時間軸の単位を任意のものへ変更できます。

ちなみに、単位は複数選択が可能です。

たとえば、何年か分のデータがあるフィールドの場合、「月」だけでグループ化すると各月の集計が複数年分まとめて集計されてしまうため、「年」と「月」を選択することで、別グループとして集計できます。

日付・時刻の自動グループ化が煩わしい場合、「Excelのオプション」ダイアログ経由で設定をOFFにすることも可能です。
(「Excelのオプション」ダイアログは、リボン「ファイル」タブ→「その他」→「オプション」の順にクリックで起動)

ダイアログの「データ」をクリックし、「ピボットテーブルで日付/時刻列の自動グループ機能を無効にする」のチェックをONにすればOKです。(デフォルトはチェックOFF

【参考】数値のフィールドは数量の単位ごとにグループ化が可能

数値のフィールドをグループ化する場合、上記のグループ化の手順①で選択するアイテムが単一の場合、「グループ化」ダイアログが起動します。
(同名ですが日付・時刻とは別ダイアログ)

このダイアログの「単位」ボックスへいくつ刻みでグループ化したいか数値入力することで、フィールド内で複数グループを一括で作成できます。

ただし、グループのフィールドが新たに作成されず、そのフィールド自体でグループ化されてしまうため、展開や折りたたみができませんので、ご注意ください。

グループのフィールドを作りたい場合は、上記のグループ化の手順①で複数アイテムを選択し、一つずつグループ化しておけばOKです。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ピボットテーブル_グループ化.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ファイルを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. ピボットテーブルレポート上で任意のアイテムを複数選択
    ※今回はA4~A7セルを選択
  2. 右クリック
  3. 「グループ化」をクリック
  4. 数式バーで任意のグループ名を入力
    ※今回は「国産」と入力

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

グループ化は元データを修正できない時でも、集計の粒度を変えることができる緊急用の機能です。

知っておくと、どんなケースでも集計に困るリスクを減らせるため、この機会に覚えておくことをおすすめします。

なお、ピボットテーブルの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

グループ化は感覚的に操作でき難しい機能ではないですが、改めて解説しようとすると結構なボリュームになっちゃいました。

実際、元データのレコード数が多過ぎる場合など、グループ化で暫定的に対応するといったケースが実務では発生します。

今まで使ったことがなかった方は、ぜひ本記事を参考にお試しください!