日付や番号ごとに仕分けよう！ピボットテーブルで分析を効率化する方法（階層構造・グループ化編）

「なんだか難しそう……」と敬遠されがちなExcelの機能、「ピボットテーブル」。実は、慣れてしまえば関数よりも簡単で、そのうえ効率化も叶う便利な機能です。ピボットテーブルは大量のデータを一瞬で集計し、比較・分析ができる機能です。集計項目を一瞬で変更できるため、単なる集計だけでなく多角的な分析に役立ちます。
今回の記事では、データをグループに分けて比較する方法を取り上げます。グループ同士で比較・分析することはもちろん、グループ内の項目の内訳を分析することもできます。今回は売上の集計を例に解説しますが、「各部署の今月分の経費累計と内訳を同時に集計する」「客層ごとの来店数と、注文の内訳を分析する」といった応用も可能です。大変便利なツールですので、ぜひマスターして日々の業務に役立ててください。

「ピボットテーブルの基本的な使い方を知りたい」という方はこちら｜ピボットテーブルで何ができる？表の集計・分析を効率化しよう
前回の記事はこちら｜集計以外もできる！ピボットテーブルで分析を効率化する方法（平均・比率編）

【ピボットテーブル応用:その4】階層集計でカテゴリーごとに分析しよう

ピボットテーブルでは、特定の項目に合致する数値を集計すると同時にその内訳も集計することができます。例えば「月ごとの売上を知りたい、さらに売上が多い商品も知りたい」という場合は、行または列ラベルに複数の項目を指定するだけで一瞬のうちに分析することができます。実際に画面を見ながら使い方を見ていきましょう。

階層を使った分析とは？

まずは集計の元となるExcelのデータを見てみましょう。

今回はこのデータから各商品の「分類」「商品名」を行ラベルに、「金額」を値に指定してピボットテーブルを作成します。ピボットテーブルでは、行ラベル・列ラベルに複数の項目を指定することができ、分類ごとに分析することができるのです。

このように、2層以上の見出しの構造を階層構造と言います。フィールドを追加するときに枠の上側にあるフィールドが上位、下にあるフィールドが下位に設定されます。階層構造のメリットは、上位関係にあるカテゴリを細かい要素に分けて分析できることです。例えば、この表からは

• 主力商品のコーヒー類の中でも「オーガニック珈琲」の売上が高い
• フード類の売上が高い要因はランチAセットの注文が多いから

ということが読み取れます。ピボットテーブルを使うときに階層構造を使いこなせるようになれば、「上位にある項目の数値がなぜ高いのか（低いのか）」という要因の分析に役立ちます。

階層にはどんな項目でも指定できる

階層構造に配置できるフィールドには制限がありません。日付や地域など、様々な項目を自由に組み合わせて分析することができます。例えば、「各分類の売上を年度別に見たい」という場合を考えてみましょう。今回は「年ごと」「分類」で分析しますから、先ほどのピボットテーブルの行ラベルから一度「商品名」フィールドを削除し、作業ウィンドウから「日付」フィールドを「行」の枠に加えます。

商品分類ごとの売上を年別に集計することができました。

階層構造は行ラベル・列ラベルどちらでも行うことができます。例えば、先ほど作成したピボットテーブルの列ラベルに「販売エリア」「支店名」を追加してみましょう。このとき、階層の上位となるラベル（この場合は「販売エリア」）を必ず先に追加しましょう（理由は後ほど説明します。）。

エリアごとの売上推移、さらに各エリア内の売上状況を集計することができました。さらに見やすくするには「値フィールドの設定」から計算方法などを工夫すれば十分です。（計算方法の変更については前回の記事を参照してください。）

階層の上位・下位の入れ替え方法

階層を入れ替えるには、画面右側の作業ウィンドウ内でフィールドをドラッグします。例えば下の図のようなピボットテーブルで「分類」を下位の構造に変更したいときは「分類」を「販売エリア」の下にドラッグ・ドロップしましょう。

階層分析のコツと注意点

階層構造は便利な機能ですが、上位カテゴリと下位カテゴリの関係に注意しながら使用しなければなりません。「販売エリア」「支店名」の関係を例に考えてみましょう。下の図は「販売エリア」を上位、「支店名」を下位に設定しています。

上位と下位の関係を入れ替えると、このようなピボットテーブルが出来上がります。

それぞれの支店が属しているエリアが何度も表示されるため、上の表に比べると無駄な情報が多く、見にくい表になってしまいました。せっかく階層構造を作っても、上位と下位の関係を見誤ると逆に効率的な分析を妨げる資料になってしまいます。上位と下位の構造関係が固定されている場合は、本来の構造にしたがって上位・下位を指定しましょう。
（例）

• （国）−（地方）−（都道府県）−（市町村）
• （会社）−（部署）−（係）−（係員）
• （飲み物）−（お茶）−（紅茶）−（ダージリン）

上位・下位の関係がない項目同士で階層構造を作る場合は、分析の主軸になる項目を上位に置きましょう。例えば「各分類の売上を、販売エリアごとに」分析する場合は、「分類」が上位、「販売エリア」が下位になります。

また、表が横長になると見にくくなってしまいます。階層構造にするとどうしても表示される項目数が多くなるため、階層分析を行うときは行ラベル（縦方向）で行う方がおすすめです。

【ピボットテーブル応用:その5】データをグループに分けて分析しよう

ここでは、データを様々なグループに分けて分析する方法を紹介します。データを一定の法則に従ってグループ分けすることを「グループ化」と呼びます。「階層」が項目同士の関係でグループを作るのに対し、「グループ化」では元々のデータ上の日付や連番によってまとまりを作ります。元データにない項目で自由にグループ化することもできます。

その1:日付からグループ化して分析する（年・四半期・月・自分で決めた単位）

Excel 2016以降のバージョンであれば、複数年のデータを含む日付フィールドをピボットテーブルの列または行ラベルに追加すると、何もしなくても年・四半期・月の３つの単位でグループ化し、階層構造にしてくれます。これをそのまま使用しても良いですし、「7日ごと」「10日ごと」といったように任意の日数ごとにグループ化することもできます。

年・四半期・月のグループ化のまま分析したい

初期状態では「年」の集計だけが表示されています。年の横の「＋」ボタンをクリックするとその下の階層である「四半期」「月」が表示されるようになります。

展開した表示を折りたたむときは「分析」（または「ピボットテーブルの分析」）タブから「フィールドの折りたたみ」をクリックすると元に戻ります。（展開された項目の横には「−」が表示されており、ここをクリックすることでも元に戻せます。）

Excel 2013以前のバージョンでは、日付によって自動でグループ化する機能がありません。そのため、日付でグループ化をしたい場合は手動で行う必要があります。その場合、行または列ラベル上の日付データを選択した状態で「分析」(Excel 2013)タブ（Excel 2010では「オプション」タブ）の「グループの選択」をクリックしましょう。

「単位」の中から表示させたいグループ化の単位を選択し、「OK」をクリックします。このとき、複数年にまたがるデータを分析するのであれば「年」は必ず選択してください。「年」を選択しないで「四半期」・「月」を選択した場合、対象年に関係なくデータを月ごと、四半期ごとに集計してしまうからです。（例えば「2018年1月」も「2019年1月」も同じ「1月」としてカウントしてしまうため、正確な集計ができなくなります。）

自分で決めた単位ごとにグループ化したい

7日ごと・10日ごとなど、指定した日数ごとにグループを作って集計することもできます。「ピボットテーブルの分析」タブから「グループの選択」をクリックします。

初期状態では「年」「四半期」「月」が選択され、グループ化されています（背景が青色になっている項目が選択されています。）。どこか一つをクリックすると解除されるので、「日」をクリックして選択しましょう。

「先頭の値」（または「開始日」）には集計対象となる最初の日付を、「末尾の値」（または「最終日」）に集計対象となる最後の日付をそれぞれ入力します。「日数」にはグループ化したい日数を入力しましょう（7日ごとの集計を出したい場合は「7」と入力）。


7日ごとにグループ化することができました。「日数」には好きな数字を指定することができます。「長期的に分析したいときは月ごと、短期的に分析したい場合は日数を指定して週ごと」など、分析の用途に合わせて使い分けましょう。

その2:連続番号からグループ化して分析する（品番などの番号で○○件ごとにまとめる）

データを会員コードや発注番号などの連番で管理しているなら、番号ごとにまとめて集計することもできます。例えば下のピボットテーブルは、各商品の売上を注文番号ごとに集計しています。

このままだとデータ量が多すぎて分析には不向きです。そこで、「100件単位ごとに売上金額をまとめ、傾向を分析」してみましょう。

ピボットテーブル上で番号ごとに集計するときは、グループ化したい項目が登録されているラベルのセルをクリックして選択し、「分析」タブの「グループの選択」を使います。

「先頭の値」「末尾の値」に、データに登録されている連番の最初と最後が表示されています。値が正しいか確認しましょう。その下の欄には連番をまとめたい単位を入力します。今回は100件ごとにまとめるので「100」と入力します。

連番ごとに整理されたデータが集計できました。100件ごと、1,000件ごとなどの連番に分けた分析も集計方法を変えるだけですぐに完了するので便利です。

その3:独自の分類でグループ化して分析する（フィールドにない分類に手動で仕分け）

選択した項目を、手動で独自の分類に振り分けることもできます。「元データには登録されていない情報があるけれど、もしかして数値の増減と関係があるのかも？」という場合に使うことができます。一時的にグループを作って比較することができます。

例えば、上のピボットテーブルは商品ごとの売上をまとめたものです。これらの商品を「キャンペーン商品」「定番商品」に分類してみましょう。まずは商品の中から「定番商品」として分類したいものを選びます。

その状態で「分析」タブから「グループの選択」をクリックします。

選択した項目がグループ化されました。「グループ1」という仮の名前がついています。セルを選択して文字入力すると好きな名前をつけられます。ここでは「定番商品」と入力しました。

残りの商品も同様にグループ化します。セルを選択→「分析」タブ→「グループの選択」でグループ化し、グループ名を選択して好きなグループ名を入力します。

作成したグループを並べ替えたいとき

グループ化した項目を並べ替える方法もあります。そんなときは、グループ名のセルをクリックし、セルの枠線の上にポインターを合わせて、移動させたい場所にドラッグさせましょう。

作成したグループを修正または解除する方法

最後に、グループ化した項目の内容を修正する方法、グループ化を解除する方法についても説明します。操作を間違えたときも、「元に戻す」を連打したり、一からピボットテーブルを作成したりするより素早く効率的に作業しましょう。

グループ化する項目を選び直すとき

グループ化したい項目の選択を間違えた場合は、グループ化をやり直すこともできます。例えば、「定番商品のココアを、間違えてキャンペーン商品に分類した」という場合は、再度正しい項目を選択した状態で「分析」タブから「グループの選択」をクリックします。

すると、新しく指定した枠組みで再度グループ化されます。

グループ化を解除するとき

グループ化を解除したいときは、グループ名のセルを選択した状態で「分析」タブから「グループの解除」をクリックします。

まとめ

今回はピボットテーブルで階層構造分析をする方法、項目をグループ化して分析する方法についてお伝えしました。日付や分類ごとにまとめて分析する手法は汎用性が高く、様々な業務に応用ができます。上位カテゴリ全体の数値を比較するだけでなく、下位カテゴリから数値の高低の要因を探ることにも役立ちます。
ピボットテーブルのメリットは関数を使うよりも短時間で集計ができること。操作方法と集計方法を指定する方法さえ覚えてしまえば、何通りものデータ集計を瞬時に実行できます。この点をいかして、様々なデータを多角的な視点から分析することをおすすめします。サイトやExcelの指南書を読むことも大切ですが、習得スピードを上げるにはやはり実践あるのみです。実際に手を動かして操作しながら、ピボットテーブルの使い方をマスターし、日々の仕事を効率化していきましょう。

こちらもオススメ