ABC分析は、売上の大きい順に並べた表で累積構成比を =SUM($B$2:B2)/SUM($B$2:$B$8) で出し、=IF(D2<=70%,"A",IF(D2<=90%,"B","C")) でランクを付けます。売上の上位を占める商品を A、次を B、残りを C に分けて、力を入れる商品を見つける方法です。
この記事では、7 つの商品の売上の表を例に、構成比・累積構成比・ランク分け・ランクごとの集計を、実際の Excel で確かめた結果で説明します。70%・90% は一般的によく使われる区切りで、会社や目的によって違います。
ABC分析の計算式
構成比 ＝ 売上 ÷ 売上の合計
累積構成比 ＝ 上からその行までの売上の合計 ÷ 売上の合計
ランク ＝ IF(累積構成比<=70%, "A", IF(累積構成比<=90%, "B", "C"))| 部品 | 意味 |
|---|---|
| 構成比 | その商品の売上が全体の何％か |
| 累積構成比 | 構成比を上から順に足した値。一番下の行は 100% になる |
| ランク | 累積構成比が 70% までを A、90% までを B、それより下を C とする（一般的な区切りの例） |
先に、表を売上の大きい順（降順）に並べ替えておきます。並べ替えは、売上の列のセルを選んで「データ」タブの「降順」を押します（並べ替えのやり方）。練習ファイルの表は、C〜E 列にもこの記事と同じ式が入っています。
1. 構成比を出す（売上÷合計）
それぞれの売上を、売上の合計で割ります。合計の範囲は、コピーしてもずれないように $ で固定します。
=B2/SUM($B$2:$B$8)
商品A は 40.0%、商品B は 25.0%、商品C は 15.0% でした。構成比の出し方は 割合（構成比）を求める方法でも説明しています。
2. 累積構成比を出す
SUM($B$2:B2) は、範囲の始まりだけを固定しています。下へコピーすると B2:B3、B2:B4…と広がり、上からその行までの合計になります。それを全体の合計で割ります。
=SUM($B$2:B2)/SUM($B$2:$B$8)
上から 40.0% / 65.0% / 80.0% / 90.0% / 95.0% / 98.0% / 100.0% になりました。上位 2 商品で 65.0%、一番下の行は 100.0% です。範囲を広げながら足す考え方は 累計の出し方で説明しています。
3. A・B・C のランクを付ける（IF）
累積構成比（D 列）が 70% 以下なら A、90% 以下なら B、それ以外は C にします。70%・90% は一般的な区切りの例なので、会社の決まりがあればその数字に変えます。
=IF(D2<=70%,"A",IF(D2<=90%,"B","C"))
上から A / A / B / B / C / C / C になりました。累積構成比がちょうど 90.0% の商品D は、「90% 以下」に入るので B です。
4. ランクごとの件数と売上合計を出す
A12〜A14 に A・B・C を入れておき、ランクの列（E 列）が同じ行の売上を SUMIF で合計します。件数は =COUNTIF($E$2:$E$8,A12) で数えます（表の B12〜B14）。
=SUMIF($E$2:$E$8,A12,$B$2:$B$8)
A ランクは 650,000、B ランクは 250,000、C ランクは 100,000 でした。件数は A が 2、B が 2、C が 3 です。A ランクは 7 商品のうち 2 商品だけですが、売上の 6 割以上を占めていることが分かります。
【実機で検証】ABC分析でつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| 降順の表での商品D の累積構成比 | =ABC分析!D5 | 90.0% |
| 並べ替えていない表（A12:B18）で 1 行目にある商品D の累積構成比 | =SUM(B12:B12)/SUM(B12:B18) | 10.0% |
| その商品D のランク（上の結果 B5 を判定） | =IF(B5<=70%,"A",IF(B5<=90%,"B","C")) | A |
| ちょうど 70% を「<=70%」で判定 | =IF(0.7<=70%,"A","B") | A |
| ちょうど 70% を「<70%」で判定 | =IF(0.7<70%,"A","B") | B |
| 表示は 70% でも実際は 70.4% のとき | =IF(0.704<=70%,"A","B") | B |
並べ替えていない表で同じ式を使うと、ランクが変わりました。降順の表では商品D の累積構成比は 90.0% で B ランクですが、商品D が 1 行目にある表では 10.0% になり、ランクは A と判定されました。累積構成比は「上から足した値」なので、かならず売上の大きい順に並べ替えてから式を入れます。
区切りちょうどの値は、式の書き方で結果が変わります。ちょうど 70% は、「<=70%」なら A、「<70%」なら B でした。「70% まで」なのか「70% 未満」なのかを先に決めておきます。
セルの表示が 70% でも、実際の値が 70.4% なら B になりました。表示形式で小数を隠していると、見た目と判定が合わないように見えます。区切りの近くは、小数第 1 位まで表示して確かめます。
よくある質問
ABC分析の結果をグラフにしたいときは？
売上の棒グラフと累積構成比の折れ線を重ねたパレート図にします。パレート図を作る方法で説明しています。
ランクの区切り（70%・90%）は決まっていますか？
決まりはありません。70%・90% のほか、80%・90% などを使う会社もあります。式の中の 70%・90% を変えるか、区切りを入れたセルを $ で固定して参照すれば、あとから変えられます。
商品が増えたときはどうしますか？
行を足したら、売上の大きい順に並べ替え直し、式の範囲（$B$8 の部分）を最後の行まで広げます。並べ替えのやり方は 並べ替えのやり方で説明しています。
