Excelには「ピボットテーブル」や「ピボットグラフ」といった機能も用意されている。とはいえ、こういった機能があることを知っていても実際に使った経験がある人は意外と少ないかもしれない。難しそうに見えて実は簡単に利用できる機能なので、この機会にぜひ試してみるとよいだろう。Excelグラフの活用の幅が広がるはずだ。
目次
データ表の形式を確認する
ピボットグラフの使い方を説明する前に、まずは「表形式の違い」について解説しておこう。表にまとめられたデータは、その形式に応じて大きく2種類に分類できる。
一つ目は「クロス集計表」と呼ばれる形式で、表の「上端」と「左端」にラベル(見出し)が配置された形式となる。一般的に「Excelで作成した表」というと、こちらの形式を連想する方が多いのではないだろうか。
二つ目は「リスト形式」と呼ばれるもので、表の「上端」にのみラベル(見出し)があり、各行に「個別のデータ」が並ぶ形式となる。こちらは集計作業を行う前の「素データ」を扱う場合などによく見られる形式だ。データベースからダウンロードしたデータ(CSVファイルなど)も、この形式になっている場合が多い。
「クロス集計表」と「リスト形式の表」
これら2種類の表形式のうち、ピボットテーブルやピボットグラフに使えるのは「リスト形式」の表となる。
具体的な例を示しておこう。以下の図は、マスクを製造する会社(架空)の受注データを記録したものだ。表の上端には「ID」、「受注日」、「納品先」、「タイプ」、「サイズ」、「色」……といったラベルが並んでおり、2行目以降に「個々の受注データ」が記録されている。
集計前のデータ表
マスクの種類は「通常／立体／冷感」の3種類があり、それぞれ「L／M／S」の3サイズを提供している。さらに、白や黒、グレーなどのカラーバリエーションもある。
こういったデータを「タイプ別」や「サイズ別」、「色別」などで自動集計してグラフ化できる機能が「ピボットグラフ」となる。少し難しそうに見えるかもしれないが、本連載を読み進めていけば「意外と簡単に使えそう!」と実感できるはずだ。
なお、データ表が「クロス集計表」になっているときも、ピボットテーブルやピボットグラフを活用できる場合がある。この場合は、左端のラベルを「見出し」ではなく「データ」として扱うことになる。
ピボットグラフの作成手順
それでは、ピボットグラフの使い方を詳しく紹介していこう。ピボットグラフを利用するには、事前にピボットテーブルを作成しておく必要がある。表内のセルを1つだけ選択し、「挿入」タブにある「ピボットテーブル」のアイコンをクリックする。
ピボットテーブルの作成
すると、以下の図のような設定画面が表示される。ここでは「データ表の範囲」を確認し、ピボットテーブルの配置先を選択すればよい。
データ範囲と配置先の確認
配置先に「新規ワークシート」を選択した場合は、新しいワークシートが作成され、右端に「ピボットテーブルのフィールド」という設定画面が表示される。ここでは「どのフィールド(列)を基準にデータを集計するか」を指定する。
たとえば、タイプ別(通常／立体／冷感)にデータを集計したい場合は、「タイプ」のフィールドを「行」のエリアにドラッグ&ドロップする。
「行」に配置する項目の指定
続いて、「列」のエリアにもドラッグ&ドロップでフィールドを配置する。今回は「サイズ」のフィールドを配置してみた。これで「タイプ」と「サイズ」について個々のデータ集計したピボットテーブル(クロス集計表)を作成できる。
「値」のエリアには「集計するデータ」を配置する。ここには「数量」や「小計」のように数値データが入力されているフィールドを配置しなければならない。今回は各商品の受注数を調べたいので「数量」のフィールドを配置した。
「列」と「値」に配置する項目の指定
このように「行」、「列」、「値」に配置するフィールドを指定していくのが、ピボットテーブル作成の基本操作となる。今回の例では「タイプ別」と「サイズ別」を基準にして、「数量」(受注数)を集計した表が作成される。
作成されたピボットテーブル
この集計表を数値のまま比較・分析しても構わないが、グラフ化した方が一目で状況を把握できるようになる。続いては、ピボットグラフを作成していこう。「ピボットテーブル分析」タブにある「ピボットグラフ」のアイコンをクリックする。
ピボットグラフの追加
続いて、作成するグラフの種類を指定する。この操作は普通にグラフを作成する場合と同じだ。
グラフの種類を選択
すると、ピボットテーブルをもとにしたグラフが自動作成される。このグラフを見ることで、「タイプ別」と「サイズ別」の受注数の傾向を把握できるようになる。
作成されたピボットグラフ
上図を見ると、通常マスクと立体マスクはMサイズの受注数が多く、冷感マスクのみLサイズの需要が高いことを把握できる。
ピボットグラフを使ったデータ分析の基本
「行」や「列」に配置したフィールドを自由に追加・変更できることもピボットグラフの魅力の一つといえる。この操作も各フィールドをドラッグ&ドロップするだけで実行できる。
たとえば、「色」のフィールドを「軸」(ピボットテーブルの「行」に相当)のエリアにドラッグ&ドロップすると、「タイプ別」だけでなく「色別」でも集計したグラフに置き換えることができる。
分類項目の追加
分類項目を追加したグラフ
もちろん、すでに配置してあるフィールドを撤去することも可能だ。この場合は、そのフィールドを上部のエリアへドラッグ&ドロップすればよい。すると、「色別」だけで集計したグラフに変更できる。
分類項目の削除
「タイプ」の分類項目を削除したグラフ
このように各エリアに配置するフィールドを変更して、色々な角度からデータを分析できるのがピボットグラフの最大の特長といえる。
もう一つ例を紹介しておこう。たとえば、以下の図のようにフィールドを再配置すると、横軸で「サイズ」、系列(凡例)で「タイプ」を示したグラフに変更できる。「値」には「小計」のフィールドを配置しているので、今度は「受注数」ではなく「売上金額」を比較するグラフを作成できたことになる。
各フィールドをドラッグして再配置
フィールドの配置を変更したいグラフ
ピボットグラフの種類の変更
ピボットグラフを作成した後に「グラフの種類」を変更することも可能だ。この場合は、「デザイン」タブにある「グラフの種類の変更」をクリックし、グラフの種類を指定しなおせばよい。
グラフの種類の変更
先ほどの例の場合、グラフの種類を「積み上げ縦棒」にしたほうが見やすいグラフになる。こうすることで「サイズ別」の売上金額をより明確に比較できるようになる。
「積み上げ縦棒」に変更したピボットグラフ
月別の集計グラフを作成するには？
「受注日」のように日付としてデータが記録されているフィールドを「軸」や「凡例」に配置した場合は、「月」というフィールドが自動追加され、月ごとに集計したデータをグラフ化することも可能となる。こちらもピボットグラフならではの便利な機能といえるだろう。
データが日付のフィールドを配置した場合
月別に集計されたピボットグラフ
このグラフを見ることで、
・5月は少しだけ売上が落ちたが、6月になって再び売上が増加している
・6月は冷感タイプの売上が大きく伸びている
※そのぶん立体タイプの売上は減少している
などの傾向を把握することが可能となる。
ちなみに、今回の連載で示したデータは架空のデータであり、マスク販売の実情を示したものではない。あくまで「ピボットグラフの使い方」の一例として捉えて頂ければ幸いだ。
このほかにもピボットグラフには便利な機能がたくさん用意されている。ということで、次回は「フィルター」や「集計方法の変更」などについて紹介していこう。
