第9回 「初めてのピボットテーブル」本格分析の基本を理解しよう

今回からデータ分析の本質に迫って行くシリーズに入ります。PowerView や PowerMap が直感的にデータを読み取るツールだとしたら、今後、ご紹介させていただくツールは、意図を持って深堀をしていくツールと理解していただければと思います。

分析の基本はデータの傾向や内容を見つけ出すことです。そのためには「分析データそのもの」と、それを「分析するための軸」が最低でも必要になります。今や「分析データ」は様々な企業や公的機関から提供され大量のデータとして存在しています。それらをどのように集めるのかが1つのポイントになります。また、「分析するための軸」はたった1種類のデータであっても、データの集計方法や観点で複数の軸を有することとなります。これらをどのように組み合わせるかが次のポイントになるわけです。
分析の軸を考える上で「5W1H」を持つことが必要だということは、第4回のコラムでまとめています。もし、内容が思い出せないようでしたら、一度、読み返していただければと思います。
今回はピボットテーブルを使いながら、実際に分析を進めてみたいと思います。

ピボットテーブルの基本

ピボットテーブルという言葉は、Excel を使いこなしている皆様でしたら 1度は耳にされたことがあるのではないでしょうか。ピボット（Pivot）には「中心、中軸」といった意味があり、行×列のクロス集計をイメージしていただければと思います。下図がピボットテーブルの一例です。

ピボットテーブルの例

縦軸に日付、横軸には保存された各データが表示されている

例えば、この例だと分析の軸は「時間」ということになります。時系列にデータがどのように存在しているのかを分析すれば良いということになります。
ただ、この分析ではたった1つの「時間」という軸による分析内容からしか結論づけることができません。これではピボットテーブルである必要はありません。通常の Excel のシートを利用して分析できます。

では、複数の軸で分析するためにはどのような工夫が必要なのでしょうか。そのカギを探すためにまずは Excel のピボットテーブルの画面構成を確認していただきたいと思います。
下図がピボットテーブルの主要な画面構成をまとめたものです。

ピボットテーブルの画面構成

まずは分析する「値」を決定してください。これは主に「数値データ」が入ることになります。サンプルプログラムでは「受注金額」を配置しています。受注金額はフィールド一覧から選択します。

(1)「値」にフィールド一覧から「受注金額」を配置

受注金額の総合計が表示されている

ポイントは分析の軸の配置になります。軸は2つのエリアに対して配置することができます。1つは「行」、もう1つは「列」です。それぞれの軸にデータを配置してみましょう。まず、行に「受注」テーブルから「年月」フィールドを配置、列に「商品区分」テーブルから「区分名」フィールドを配置してみて下さい。下図のように、商品区分名ごとの時系列の受注金額の推移を確認することができます。この状態に条件付き書式から「カラー スケール」や「データ バー」、「アイコン セット」をセットしたのが、上記「○ピボット テーブルの例」でご紹介した表になります。

(2)行に「年月」、列に「商品区分」を配置

深堀するデータを見つけ出す

ここからが、分析の第一歩になります。深掘するデータを見つけましょう。
今回は「最も受注金額の大きいデータがどのように構成されているのか」を分析してみたいと思います。
このような分析のテーマや観点は皆さんが会社や日常生活の中で気になっていることを選択すれば良いと思います。加えて、どのデータをあぶりだすのかは、最小値、最大値、平均とのかい離といった観点から見出すと良い結果が得られることが多いです。

本コラムの最初の図である「○ピボットテーブルの例」をご覧ください(できればサンプルファイルをダウンロードして一番左の「商品区分別 受注金額」タブをご覧ください)。受注金額が最も多いのは「魚介類」、「200706」のデータであることがわかります。受注金額は \792,100 ですね。このデータがどのような構成になっているのかを見ていきましょう。

(3)まずは絞り込む

データを深掘りする前にまずは絞り込みを行っていきたいと思います。フィルターに「受注」テーブルから「年」と「月」フィールドを配置してみて下さい。表の左上に「年」と「月」のフィルターが配置されます。

(4)「年」「月」フィルターを配置

ここからまず6月のデータだけ抽出してみましょう。下図のように All の左側の「+」をクリックして選択肢を表示し、「06」を選択して 6月のデータだけを表示してみて下さい。

(5)6月のデータのみで絞り込み

すると、6月の過去3年のデータ比較ができます。3年間のデータを比較すると必ずしも「魚介類」だけが売上を伸ばしているのではなく、他に「菓子類」「肉類」「乳製品」も売上を伸ばしていることがわかります。

本来であればここまでの分析で発見された4区分すべを深掘りしたいのですが、原稿の量の関係で「魚介類」のみとします。
まずは、1商品だけ表示されるように フィルター をかけます。

(6)「魚介類」のみ表示するようにフィルターをかける

さらに、「社員」テーブルから「在籍支社」を追加してみましょう。すると、驚くべきことがわかります。東京本社以外の大阪支社と北九州支社は売上を伸ばしているどころか、減少しているのです。

(7)「魚介類」に絞り込み、「在籍支社」を追加

少々、コラムとしては長くなってしまいました。ここから先の分析は次回以降とさせてください。どうぞお楽しみに!!

●書籍のご案内