BDAstyle

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

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

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

アパレルショップX17品目の商品を販売しています。XABC分析を念頭に,今年上半期の売上金額を商品別に集計しすでに下表を得ています。XはそのABC分析の端緒として,これからABC分析表パレート図を作ろうと考えています。このときクラス分けにあたっては,暫定的に次の基準を充てることとしています。

Aクラス
累積構成比≦70%
Bクラス
70%<累積構成比≦90%
Cクラス
90%<累積構成比≦100%

DL

ABC分析・元データ

以下,この頁ではABC分析表をつくるために必要な一連の手続きを示します。

ABC分析表の作成

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

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

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

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

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

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

scrollable

Step 4構成比の計算

「構成比」見出し直下のセルで構成比を求めます。これは各個の商品の売上を合計で割って求めます。

C2 =B2/$B$19

この式を,合計を除くすべての商品の分コピーします。

Step 5累積構成比の計算

「累積構成比」見出し直下のセルで構成比を積み上げます。積み上げなので単純に最初の行は左隣の構成比をそのまま転記し,2行目以降は同じく左隣の構成比を直前の累積構成比に積み上げていけばよいのですが,管理の上で,それはいささかスマートさに欠けるので,ここではSum関数でさっくり済ませておきたいと思います。

D2 =SUM( $C$2:C2 )

この式を,合計を除くすべての商品の分コピーします。

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

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

Step 7「クラス」列の作成

「クラス」列をつくります。

Step 8クラス判定

「クラス」見出し直下のセルでA・B・Cいずれかのクラス名を与えます。最初に掲げた区分けのルールを組み込むには,通常,

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

とする式で実現できます。また,version 2016からif関数をネストしなくても分岐がシンプルに書けるようになったので,これを利用して

E2 (v2016) =IFS( D2<=0.7, "A", D2<=0.9, "B", TRUE, "C" )

としてもいいかもしれません。

いずれにしろ,この式も合計を除くすべての商品の分コピーします。

Step 9完成

ABC分析表の完成です。

Next

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

その他の参照