ExcelでできるABC分析の計算手順【5ステップ】

今回は、Excelやスプレッドシートを使って、手元のデータから実際にABCランクを算出する手順を解説します。

 

難しそうに見えますが、使うのは「割り算」と「足し算」のみ。

一度型を作ってしまえば、ずっと使える分析スキルです。

5ステップで解説します。

 

◎データの準備

商品名や顧客名と「売上金額」が並んだリストを用意します。

（例：A列に商品名、B列に売上金額）

 

①売上が大きい順に並べ替える

最初の重要ポイントです。

データ範囲を選択し「降順（Z→A）」をクリックして、売上が高い順に並べ替えます。

累積構成比を正確に計算するために、貢献度の高い順に並んでいることが必須条件です。

データの順番がバラバラでは、正しい分析ができません。

 

②全体の合計を出す

リストの一番下に、SUM関数を使って売上の「合計」を出しておきます。

（例：合計がセルB100にあるとします）

 

③構成比を出す

「この商品は、全体の何％を占めているか？」を計算します。

C列に「構成比」という項目を作ります。

 

計算式：= 商品の売上 ÷ 売上の総合計

重要：合計のセルが動かないよう絶対参照にするため「$」をつけます。

例：=B2/$B$100

 

上記の数式を一番下の行までコピーし、表示形式を「％」にします。

 

④累積構成比を出す

ここからがABC分析の本番です。

「上から足していったら、どこで70％を超えるか？」を確認するために、構成比を足し合わせていきます。

D列に「累積構成比」という項目を作ります。

 

1行目：そのまま隣の構成比を使います。（例：=C2）

2行目以降：「上の段の累積」＋「自分の構成比」を足します。

例：=D2+C3

 

一番下までコピーします。

最後の行がちょうど「100%」になれば成功です。

 

⑤ランク付けを行う

累積構成比を見て、以下の基準でグループ分け（ランク付け）をします。

 

・Aランク：累積が0% 〜 70%まで

売上の柱となる最重要グループです。

在庫切れは絶対に防ぎましょう。

 

・Bランク：累積が70% 〜 90%まで

次の2割を支えるグループです。

Aランクへの昇格を目指します。

 

・Cランク：累積が90% 〜 100%まで

数は多いですが、売上への貢献度は低いグループです。

管理コストを見直す対象になります。

 

※「70：90：100」の境界線は、調整して構いません。

（例：80：95：100など）

 

数字の羅列だったデータが、A・B・Cのランクを持つことで「分析データ」に変わります。

ぜひ一度、手元のデータで計算してみてください。

意外な商品がAランクの「売上の柱」だとわかるかもしれません。