BDAstyle

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

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

Step 0シチュエーションの設定

アパレルショップX17品目の商品を販売しています。先に今年上半期の売上高を商品別に集計したところ,下表のようになりました。Xはこの結果をもとにABC分析をおこなうため,ABC分析表パレート図を作成します。このとき,クラス分けにあたっては次の基準を用いることとします。

初期データ

1. ABC分析表の作成

Step 1「構成比」および「累積構成比」列の作成

「構成比」および「累積構成比」列を作成します。

Step 2降順に並べ替え(1)

表のセル範囲A1~B18を選択し,データタブ「並べ替えとフィルタ」グループの並べ替えボタンをクリックします。

Step 3降順に並べ替え(2)

「列」の最優先されるキーを「売上高」に,順序を「降順」にしてOKボタンをクリックします。

Step 4構成比の計算

「構成比」列のセルC2で構成比を計算します(=売上高÷総計[ただし絶対参照])。

セルC2 =B2/$B$19

この式を18行目までコピーします。

Step 5累積構成比の計算(1)

「累積構成比」列のセルD2にこの行の構成比を転記します(=構成比)。

セルD2 =C2

Step 6累積構成比の計算(2)

「累積構成比」列のセルD3に次の式を入力して構成比を積み上げます(=直前の累積構成比の値+当該行の構成比の値)

セルD2 =D2+C3

この式を18行目までコピーします。

Step 7パーセントスタイルの適用

「構成比」および「累積構成比」両列にパーセントスタイルを適用します(下図は適用前のもの)。

Step 8「クラス」列の作成

「クラス」列を作成します。

Step 9クラス判定

セルE2に計算式を入力します。最初に掲げたクラス分けの基準にしたがうと,計算式は

セルE2 =IF(D2<=0.7,"A",IF(D2<=0.9,"B","C"))

となります。

この式を18行目までコピーします。

Step 10完成

ABC分析表の完成です。

Next

次頁はパレート図作成の工程です。

その他の参照