ABC分析［商品別売上高］ with Excel 1/2
Step 0シチュエーションの設定
アパレルショップXは17品目の商品を販売しています。XはABC分析を念頭に，今年上半期の売上金額を商品別に集計しすでに下表を得ています。XはABC分析の端緒として，これからABC分析表とパレート図を作ろうと考えています。このときクラス分けについては，暫定的に次の基準をあてておこなうことを決めています。
- Aクラス
- 累積構成比≦70％
- Bクラス
- 70％＜累積構成比≦90％
- Cクラス
- 90％＜累積構成比≦100％
DL
以下，この頁では目的の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
その他の参照
このサイトの関連How-toです。
メインサイト「ひとりマーケティングのためのデータ分析」のクラス管理ツールに関するHow-toです。