2013年

2月

27日

データ分析の勧め ~Excelを使って自社の業務データを整理・分析してみよう!~ その4

第4回 データ分析の実際・ABC分析その2(ABC分析の複合グラフを作る)

前回はABC分析のためのピボットテーブル操作と集計表の整理を説明しましたが、今回は図3-4に示した、商品の売上高順に並べた棒グラフと累計比率の示した折れ線グラフの2つが一緒になった複合グラフの作り方を説明します(図4-1は前回のグラフにABCランク付けをしたもの)。

 

※説明に使用するサンプルデータをダウンロードできますので、実際に操作しながら確認してみてください。

図4-1 ABC複合グラフ
図4-1 ABC複合グラフ

 

前回お見せしたABC集計表を図4-2に示します。前回のものに粗利益÷売上金額=粗利率、売上累計の50%未満をA、50%以上80%未満をB、それ以下をCとして表を整理し、総計と平均粗利率、平均売上金額も計算し表示しています。Aランクは売上金額の半分を占めていますが、その数は全商品69アイテムの内10アイテムで全体の14.5%、Bランクまででも29アイテムで42%です。このABCランクの設定は自由ですので、各自どのように比較してどんな傾向を見たいのか検討してみて下さい。


ではこの図4-2から、まず図4-1の棒と折れ線の複合グラフを作る手順を示します。

 

①Excel表のA列の商品、B列の売上金額、C列の累計比を選び、一番下の行までをマウスで選択します。

図4-2 ピボットテープルにABCランクを加えた表
図4-2 ピボットテープルにABCランクを加えた表

 

②挿入タブの「グラフ」グループから棒グラフの2D縦棒(一番左上)を選び、グラフを表示します。

図4-3 挿入タブの「グラフ」グループ
図4-3 挿入タブの「グラフ」グループ

 

③棒グラフをクリックすると図4-4のように売上金額だけが棒グラフになっていますが、実は累計比もグラフの一番底に小さく棒グラフとして含まれていますのでここを選択し、折れ線グラフにしていきます。グラフツールのレイアウトタブの左隅のグラフエリアボックスから「系列“累計比”」を選ぶと横軸の一番底が選択されます(いきなり横軸底をクリックすることもできます)。

図4-4 第2軸(累計比)の選択 (クリックで拡大)
図4-4 第2軸(累計比)の選択 (クリックで拡大)

 

④選択されたら、レイアウトタブのデザインを選び、グラフの種類の変更から折れ線グラフを選んでください。

図4-5 折れ線グラフの選択 (クリックで拡大)
図4-5 折れ線グラフの選択 (クリックで拡大)

 

折れ線なのですが、左側縦軸目盛を基準としているため値が小さすぎて横軸の底に張り付いているので、右側に累計比用の別の目盛(第2軸)を作ります。  まず、対象の折れ線をクリックして選択。レイアウトタブの「選択対象の書式設定」ボタンを押して書式設定ウィザードを表示し、第2軸(上/右側)を選択すると折れ線グラフが大きく立ち上がって表示されます。あとはグラフの体裁を整えれば、図4-1の複合グラフが出来上がりますが、分析の本質は「検証」なので体裁よりは現状をしっかり見ることです。そのためにはABCの基準を変えてみるとか、何社に売れているのかとか、月に何日出荷されているのかとか、次に何を見ていくのかを考えながら整理することが重要です。見てくれを良くするのは意外と時間がかかるので、会議資料などの説明に使う以外は自分なり、関係する方々の間で理解できる程度で十分です。

 

図4-6 第2軸を整理して折れ線グラフも併せて表示 (クリックで拡大)
図4-6 第2軸を整理して折れ線グラフも併せて表示 (クリックで拡大)

 

とは言いながら見てくれも大切という方に、最近のExcelはビジュアル化が進んでいるので、表の中にグラフを挿入して体裁を整える方法をお教えします。これはピボットテーブル内でも普通の表でもどちらでも使えますので、今回はピボットテーブルで説明します。

 

 次ページ ピボットテーブルの場合
  1       2     次ページ>>  

「いいね!」ボタンを押していただくと、最新情報をお届けできます。

 フィードの購読もできます。

 (NEWS,BLOGのみ)

連載ブログ 

 大人気3rdシリーズ

 

公益財団法人にいがた産業創造機構

産業創造グループ

情報戦略チーム

TEL 025-246-0069

Mail  kns@nico.or.jp