【シリーズ：人より一歩先行くエクセル技を身につける】ピボットテーブル編①

仕事で使う(もしくは使わざるを得なくなった...？)エクセルを、周りの人たちより一歩先に進んで自分の技としたいあなたに贈るシリーズの第一弾。

今回から何回かに分けて「クロス集計」などでデータ分析を行うツール、そう
「ピボットテーブル」
の使い方を見ていきたいと思います。

「ピボットテーブル」ってなんだ・・・？

昔から業務でエクセルを使ってきている方は、
「ピボットテーブル」と言えば、
「聞いたことがある」「見たことがある」「使ったことがある」
という人がほとんどだと思います。

しかし、マイクロソフトのオフィス製品に触れる機会が少なかった方や
エクセルを使っては来たけれども、業務で
「ピボットテーブル」
を使う機会がなかった、と言う方には、”どういった場面で使うのか”、”どんな機能なのか”、と分からない事ばかりでしょう。

「ピボットテーブル」とは、元の表から様々な分析を行うために新たに作られる表のことであり、簡単に分析する項目を入れ替えることのできる機能を持っています。

普通に表を作成して、数値を合計して、合間に関数を挟んで、印刷をして・・
というだけでは、中々見えてこない「ピボットテーブル」の難しさは、何と言っても
「今ある表が、クロス集計・分析を行うのにふさわしいのか」
「目的となるクロス集計をするための表はどのように作成されるものなのか」
という、クロス集計の元となる「表(データベース)」の在り方が見えてこない、という理由にも依るところがあるのです。

では、早速クロス集計するための「元の表」を作成してみたいと思います。

今回使用するエクセルデータは、こちらからダウンロードできます。

集計・分析の元となる表を作成する

エクセルを起ち上げて、ある会社の営業部門のメンバー表を作成してみます。

一見よくある名簿データですよね。
10名の営業メンバーがリスト化され、それぞれ性別や年齢、役職がセットになっています。

しかし、このような表は「クロス集計」には向いていません。
”向いていない”、というよりは「集計するためのデータがない」と言った方がいいかもしれません。

こういった表の場合、せいぜい
「性別で抽出」したり
「役職」を拾い出してみたり
時には、「(何の意味があるかはさておき)氏名が3文字の人」を拾い出したり
「一覧を印刷」したり
ということが表の主な利用方法となるでしょう。

アクセスなどのデータベースやプログラミングの世界では、このような表はいわゆる
「マスタデータと呼ばれる基礎データ」
であり、マスタデータは他の表やデータベースなどから
「参照されるためのデータ」
になります。

そう、つまりクロス集計や分析に使う表(データ)というのは、
逆に「マスタデータを参照する一覧表」であり、
その一覧表が持っている数値の値を合計したり、最大値を拾い出したり、平均値を求めたり
することで多角的な分析を行うことができる作りになっているのです。

・・・とさっぱり？？？
と言う方がほとんどかもしれませんね。

でも、次の表を見たら、なんとなく分かると思うので心配はいりません。

それでは、実際に営業メンバーの名簿を参照するような表を作成し、集計・分析を行ってみることにしましょう。

上の表は、10月の15日までに各営業メンバーがどの商品をいくら売ったのか、その売上高を一覧表にしています。

日々のデータが蓄積されていくこの表では、例えば
「どの商品が一番売れているのか」であったり
「営業担当者で一番売れていない人は誰だ」など
ぱっと見ただけではすぐに各数値データを細かく分析できません。

さらにデータが11月、12月、来年、再来年と蓄積されていくと、
「どの時期にはどの商品が売れるのか」
なども調べたくなります。

そこで使う機能が「ピボットテーブル」なのです。

上の表を見ても分かるように、先述の「マスタデータ」と違って、分析したい数値データ(金額、個数)が表に含まれていますね。

このように、「ピボットテーブル」を使う場面と言うのは、
「会社の売り上げである金額データ」であったり、
「学校の生徒たちの点数データ」であったり、
計算されそうな数値データを含む表である、という事が言えるのです。

そして、「マスタデータ」である「営業メンバー」を参照するこの「売上一覧表」は、日々データが追加されていき、最終的に「多角的な分析」をするために必要なビッグデータへと生まれ変わるわけです。

ピボットテーブルを表示する

表の中の任意のセルをクリックし、「挿入」タブから「ピボットテーブル」をクリックします。

「ピボットテーブルの作成」ウィンドウが表示されます。
「分析するデータ」は、表のセル範囲を選択します。
「ピボットテーブルレポート」を同じシート内に配置します。

「ピボットテーブル」がシート内に配置されました。
ただし、中身はまだ何もありません。

リボン内には、「ピボットテーブル」を選択している時に「分析」と「デザイン」と言う項目が新たに追加されます。

「ピボットテーブル」のタイトルには、デフォルトで「ピボットテーブル1」と名前が付けられますが、「分析」タブの左端にある「ピボットテーブル名」でタイトルをいつでも変更することができます。

さて、「ピボットテーブル」内に商品名と営業担当名の項目を設置して上の表ができあがりました。
10月1日から10月15日からずらーっと売上データが羅列されていた表と見比べてみてください。

このような表になっただけでデータの分析が簡単になり、次のようなことが分かるようになったと思います。

• 各営業担当が半月でどれだけの売上を生んだか
• 各商品は半月でどれだけ売れているのか

先程、”できない”と言っていた事があっという間に分析できるようなりましたね。

「商品名」が表示されている「列」と「営業担当」が表示されている「行」がクロスする金額と「商品ごと・営業担当ごとの小計」、「それらの総計」を表示しているこの「クロス集計表」が、まさにエクセル上でデータ分析を行う上で大きな役割を果たすわけです。

それでは次回、この「ピボットテーブル」の編集や細かい操作などを行っていきたいと思います。