BDAstyle

ビジネスデータ分析ツールの作成 with Excel

ABC分析[商品別売上高] with Excel 2/2

Step 0イントロダクション

この頁では,前の頁で作成したABC分析表(下図再掲)からパレート図をつくります。

元データ・ABC分析表

ただしイメージとしては下図のように,クラスの別を視覚上明瞭にする意味で,柱に異なる彩色を加えていきたいと考えています。通常のつくり方で描いたパレート図から事後的にそれらの処置を加える作業はけっこうな面倒を強いるので,ここでは最初からクラスの別に異なる彩色をおこなう想定を組み入れていきます。

そのあたりを考慮する必要がなければ,リンク先で説明するように一般的な手順で進めたほうがシンプルです。

これから作るパレート図のイメージ

以下,パレート図を描くために必要な一連の手続きを示します。

パレート図の作成

Step 1シートに行を追加する

グラフをより簡単に描くための工夫として,ABC分析表にあたらしく3つの列を追加します。

具体的には,「売上」列のすぐ右方にそれらが欲しいので,列番号C-Eをドラッグして選択し,ショートカットメニューやリボンなりで列を挿入します。

列を3つ挿入

挿入したあたらしい列名(見出し)として,クラスの名称―――通常はABCのいずれかとしますが―――を配置します。

scrollable

[C1]class:A, [D1]class:B, [E1]class:C

Step 2クラスごとに系列を分ける

クラス名の見出しの直下に下表の式を入力し,これを表の最下行までコピーします。なお例と異なるクラス名を使用した場合,下表の式の該当する部分を修正する必要があります。

C2 =IF( H2="A", B2, NA() )
D2 =IF( H2="B", B2, NA() )
E2 =IF( H2="C", B2, NA() )

scrollable

売上をクラスごとに分ける

Step 3複合グラフを挿入する(1)

「商品名」列,クラスA-C列および「累積構成比」列を選択します(合計部分を除く。計5列)。

scrollable

A,C-E,G列を選択

挿入タブ「グラフ」グループの複合グラフの挿入ボタンをクリックします。

プルダウンメニューのデフォルトの選択肢を回避してユーザー設定の複合グラフを作成するを選択します。

scrollable

「ユーザー設定の複合グラフを作成する」

Step 4複合グラフを挿入する(2)

「グラフの挿入」ダイアログで次の操作を済ませます。

  1. A―Cクラスのすべての系列 「積み上げ縦棒」へ変更
  2. 「累積構成比」系列 「折れ線」へ変更の上「第2軸」に

classA-C:積み上げ縦棒, 累積構成比:折れ線(第2軸)

これまでの作業により,グラフは下図の構成でシートの上に描画されます。

プレーンなグラフ

なぜ “積み上げ” の方の棒グラフを使う?

細かな部分に書式設定を加えようとするとき………とくにデザイン的な意味合いから棒と棒の間隔を詰めたり広げたり調整したいとき,「要素の間隔」項目のみを変更すれば足るところに利があるからです。

棒グラフの場合,これは「系列の重なり」項目との兼ね合いも見ていかないといけなくなるので,調整のしやすさの点では劣ります。

Step 5パレート図の体裁をととのえる

これより書式設定を任意に加えていきます。たとえば―――

4つの系列(AーCクラス,累積構成比)を配色パターンなどを参考に彩色し,

e.g. Adobe Color CC, カラーユニバーサルデザイン推奨配色セット

配色パターンを参考に各系列を彩色

軸のMin, Maxをととのえ,

軸の最低・最大値を設定

ラベルを調整(個人的にはノーマル&項目数の多いパレート図の場合,“重点志向”の趣旨と照らしても上段に置いた方が読みやすく思うので)し,

ラベルを上に

凡例やグラフタイトルといった視線の散る要素を削って,

凡例・グラフタイトルの削除

AーCクラスの適当な要素にデータラベルを表示させ,これをクラス名の名札として使うなどしてもいいかもしれません。

クラス名を描きこむ

Step 6その他設定・パレート図の完成

さらに細かな設定を加え,パレート図の完成です。

完成したパレート図

その他の参照