【Excel】ピボットテーブルを使用した集計の応用（階層化・グループ化・詳細化）

以下の記事では、ピボットテーブルを使用した様々な集計方法・並び替え・フィルターについて解説しました。

この記事では、集計をより柔軟に分かりやすく行う応用のテクニックを以下3つ紹介します。

• 階層化
• グループ化
• 詳細化

階層化

階層化とは、元データの既定の分類に基づき階層構造で表示することです。データを集計する際、項目が多いとデータの全体像がつかみずらくなってしまいます。たとえば、集計項目に「氏名」を選択すると顧客一人一人の集計結果が表示されますが、これだけではデータを見るのも大変で、良い示唆も得られません。

そこで、氏名をより大きな分類でグルーピングします。たとえば、氏名に紐づく分類として所在地や職業があります。

所在地別の売上や職業別の売上を併せて表示させることで、より全体像がつかみやすくなります。行ボックスの氏名の上に「所在地」の項目をドラッグします。

そうすると、氏名が都道府県別にグルーピングされ、階層構造で表示されるようになります。

都道府県別の小計を表示するには、デザインタブの小計を選択し、「小計を表示する」をクリックします。

そうすると、都道府県別の小計が表示されます。

顧客一人一人のデータを見るよりもデータの全体像がつかみやすくなります。職業の項目を氏名の上に追加すれば、職業別にグルーピングされます。

さらに所在地の項目を追加すれば、職業・所在地・氏名という3階層でデータを表示させることができます。

表示方法はデザインタブのレポートのレイアウトから変更することができます。

このように表示データを階層化することで、データの全体像がつかみやすくなります。

グループ化

グループ化とは、元データで定められていない任意のグループを設定することです。所在地や職業といった分類は元のデータに入力されていますが、データを収集した後に分類を設定したい場合もあります。そんな時はグループ化を使用します。

商品名のグループ化

たとえば、商品の分類をこのように設定したいと思います。

分類したい商品を選択します。この際、Ctrlキーを押しながらクリックすることで、複数選択することができます。

選択した状態で、ピボットテーブル分析タブの「グループの選択」をクリックします。

そうすると、「グループ1」という分類が表示されます。

F2キーを押して、「スキンケア」という分類名に変更します。

他の分類も同じように選択し、グループ化し名前を変更します。これで、任意のグループで階層的にデータを表示されることができます。

グループ解除をクリックすれば、元のデータに戻すことができます。

ピボットテーブルでは、数値データもグループ化することができます。

日付のグループ化

たとえば、日付をグループ化します。今度は購入日時を行ボックスに追加します。

日付のデータはデフォルトで、年・四半期・月というようにグループ化されており、最小単位は日付の表示になっています。

たとえば、1週間7日でグループ化したい場合は、日付の部分にカーソルを合わせた状態で、グループの選択をクリックします。

そうすると、グループ化のウィンドウが表示されるので、単位の月・四半期・年の選択を外し、日を選択します。日数に「7」を指定し、OKをクリックします。

そうすると7日単位でグループ化されます。

グループ解除をクリックすれば、元の日付の表示に戻すことができます。

年齢のグループ化

数値データは日付だけでなく、年齢などもグループ化することができます。年齢の項目をボックスに追加した状態で、グループの選択をクリックします。

今度はグループ化のウィンドウで、先頭の値・末尾の値・単位を指定することができます。

たとえば、20歳を先頭にして10歳ずつ区切るように指定します。

これで、20代、30代、40代という区切りでデータを集計することができます。

詳細化

詳細化とは、特定のデータについて任意の項目で細分化していくことです。

データを見ていく中で、特定のデータについて深く知りたい場合があります。たとえば、2022年と2023年の商品別売上を集計し、特に売上の低下が大きいマスカラについて詳しく見ていきたいと思います。

マスカラをダブルクリックすると「詳細データの表示」というウィンドウが表示されます。

ここでマスカラの売上について深堀したい項目を選択します。たとえば、どのような年齢での売上低下が著しいか調べるため、「購入時年齢」を選択します。

そうすると、年齢別のデータが表示されます。

ここでも先ほど使用したグループ化を行うことで、20代、30代、40代という区切りでデータを集計することができます。

これを見ると50代の売上低下が著しいことがわかります。さらに、50代の中でもどのような職業での売上が低下している見るために、今度は詳細データで「職業」を選択します。

そうすると、会社員・無職での売上低下が顕著であることがわかります。

このようにピボットテーブルでは、特定のデータを任意の項目で細かく分解することができます。元に戻したい場合は、「-」ボタンをクリックします。

これで行ボックスには、「購入時年齢」「職業」という項目が追加されているので、他の商品についても「+」ボタンをクリックすれば、詳細データを見ることができます。

以上、ピボットテーブルの集計に活用できる階層化、グループ化、詳細化について解説しました。これらのテクニックを活用すれば、柔軟にデータを集計・分析を行うことができます。