2014年09月14日

ピボットテーブルでパレート図を画いてみた(パレート分析)/ピボットテーブル/Excel2010

第1弾“ABC分析(パレート分析)”と第2弾“Excelのグラフでパレート図の作り方”を紹介しましたが、第3弾!はピボットテーブル(ピボットグラフ)だけでパレート図を作ってみます。
また、第2弾のExcelのグラフでパレート図を作る場合でも、グラフの元データになるアイテムと値の集計表を作るにはピボットテーブルを使うのが一番簡単な方法になります。(そこまでの集計ならExcel2007で可能です!)
その先の、累積構成比をピボットテーブルで集計するのは残念ながらExcel2010以上が必要です。

食料品を扱う問屋(卸売業者)の受注データから、2013年の1年間に受注した商品(アイテム)と受注金額(値)でパレート図を画いてみます。
元データ(データソース)はこんなデータです。
20140913_01.JPG

アイテムと値の集計
まずパーレート図の元データになる“商品名”(アイテム)と“金額”(値)の集計を行います。
これは、Excel2007でも可能です。

まずピボットテーブルを作ります。
“商品名”(アイテム)と“金額”(値)で集計するのですが、集計対象は“2013年”の1年間だけなので、2013年のデータだけを抽出しなければなりません。
このままでは、それができないのでまず“年”でグループ化してあげなければいけません。
そのために、まず行ラベルに“受注日”を設定します。
20140914_01.JPG

行ラベルの受注日の上にカーソルを置いて「ピボットテーブルツール」、「オプション」、「グループ」から「グループフィールド」を選択します。
20140914_02.JPG

「グループ化」画面が開きます。
「単位」に「年」を選択します。
20140914_03.JPG

すると、ピボットテーブルの行ラベルの“受注日”は年単位で表示されました。
20140914_04.JPG

そうしたら、「レポートフィルタ」に“受注日”、「行ラベル」に“商品名”「値」に“金額”を設定します。
20140914_05.JPG

ピボットテーブルのフィールドリストのレポートフィルタの“受注日”で“2013”を選択します。
20140914_06.JPG

値は降順で並べ替えします。
するとこのように、2013年の商品(アイテム)と金額(値)の集計が完成しました。
20140914_08.JPG

Excel2007ではここまでです。
正確には“累積”と“構成比”は集計できますが、“累積構成比”は2007では集計できません。
なので、2007の場合はこの後の作業はピボットテーブルを用いないで通常のワークシート上でグラフのデータを作っていきます。

累積構成比の集計
ではここからはExcel2010で累積構成比を集計していきましょう。
「値」に“金額”をもう1つ設定します。
20140914_09.JPG

追加した「金額2」をクリックして「値フィールドの設定」を選択します。
20140914_10.JPG

「値フィールドの設定」画面が開きます。
「計算の種類」タブで、「計算の種類」を“比率の累計”、「基準フィールド」を“商品名”に設定します。
20140914_11.JPG

すると、アッと言う間に累積構成比の集計ができました。
簡単でしょ?
20140914_12.JPG

ピボットグラフでパレート図の作成
では集計表が出来上がったところで、ピボットグラフを使ってパレート図を作って行きましょう。

ピボットテーブルの中にカーソルを置いて、
「ピボットテーブルツール」、「オプション」、「ツール」から「ピボットグラフ」を選択します。
20140914_13.JPG

「グラフの挿入」画面が開きます。
「縦棒」から「集合縦棒」を選択します。
20140914_14.JPG

すると、このような“ほぼパレート図”が出来上がりました。
20140914_15.JPG

ここからは、見易いグラフにする仕上げ作業になります。
第2弾“Excelのグラフでパレート図の作り方”の後半部分をごらん下さい。
全く同じ作業をするだけです。
と、このようなパレート図が出来上がりました!!
20140914_16.JPG


関連ページ
第1弾! ABC分析(パレート分析)について
第2弾! Excelのグラフでパレート図の作り方
posted by haku1569 at 18:51| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする