ABC分析の計算式は簡単！Excelを使ってスムーズにランク分けしてみよう

売れ筋商品を調べる際に具体的なデータを用意できず、現場の感覚でしか売れ筋商品がわからない……このような経験はありませんか？

どの商品が売れているのかはっきり知りたいときに有用なのが、重要な商品をランク付けできる「ABC分析」です。ABC分析という手法は聞いたことがあっても、具体的にどう活用すればよいかわからない人もいるのではないでしょうか。

この記事では、数学の知識や難しいExcel操作の知識がなくてもわかるように、ABC分析の具体的なやり方や計算方法を解説します。

ABC分析テンプレートのダウンロードはコチラ

ABC分析は「重要度を分類する」ための手法

ABC分析は「重点分析」とも呼ばれる分析手法の一つで、
分析したいデータ群に存在する複数のデータを「重要度」で分類する手法です。

例えば、商品群を売上ごとにABC分析でランク分けすると、売上に対する商品別「貢献度」が分かります。この貢献度をもとに在庫管理や発注管理を調節できるため、ABC分析は商品管理の重要な指標でもあります。

ABC分析は「パレートの法則」に基づいている

パレートの法則は「全体の数値に対して影響をおよぼすのは上位2割である」という法則。パレートの法則に基づき、ABC分析ではランクAを全体の80%まで、ランクBを80～90%、残りをランクCと設定する場合が多くなっています。

ABC分析のやり方と使う使用する計算式

ABC分析は、データの累積割合を算出してパーセンテージごとにランク分けするという手順でおこないます。下記の表を参考にイメージしてみてください。

商品	売上	累計売上高	累計売上割合
商品A	5,000円	5,000円	50%
商品B	3,000円	8,000円	80%
商品C	2,000円	10,000円	100%
合計	10,000円	―	―

累積売上割合の出し方は「累積売上高÷売上合計×100(%)」です。
累積売上割合に対して、
0～80%はランクA
80～90％はランクB
90～100%はランクC
というように分けると、商品の貢献度がわかります。

商品群が多ければ多いほど手作業での分析が困難になるため、ExcelやGoogleスプレッドシートといった表計算ソフトの関数を利用した自動計算をおすすめします。
次の章で、表計算ソフトの関数を用いた計算方法を解説していきます。

ExcelでABC分析を簡単におこなう手順

今回は商品の売上をもとにABC分析をおこないます。実際にスプレッドシートを用いた、具体的な手順を見ていきましょう。

手順1：分析したいデータを降順で並べ替える

まず、分析したいデータや数量を入力し、降順で並べ替えましょう。手順は以下の通りです。

表計算ソフトのA列に「商品名」、B列は「売上高」、C列に「累積売上高」、D列には「累積割合」、E列に「ランク」の項目を作り、A列とB列に値を入力します。この時点では、金額順を気にせず入力してかまいません。

次に「データ」タブの「並べ替えとフィルター」機能を使い、B列の数値を基準にしてデータを降順(Z→A）に並べ替えます。

　

手順2：SUM関数でデータの累積値を入力する

続いて、SUM関数を使用し、C列に商品ごとの累積売上高を計算していきます。

1つめの商品にあたるC2列のセルに「=SUM(B2,C1)」と入力してオートフィルを使うと、一気に計算できます。

手順3：累積割合を計算する

C列に表示された累積売上高をもとにして、D列で累積割合を計算します。
まず、D列セルの表示設定を「パーセンテージ」に変更しましょう。D2列に「=C2/(C列の最下段のセル)」と入力します。

ただ、最下段の数値をそのまま入力すると、オートフィル機能を使用するときに参照するセルがずれるため、例えば最下段がC120である場合は「=C2/($C$120)」と入力します。

手順4：VLOOKUP関数を使ってABCのランク分けをおこなう

累積割合を出したら、ここまでのデータをもとに商品をABCのランクに分けます。

ランク分けはVLOOKUP関数を使用すると手早く計算できますが、あらかじめ参照する値を空いているセルに入力しておく必要があります。

H～K列にランクの基準値を入力したのが下の表です。0～80%がランクA、80~90%はランクB、90~100%はランクCというのが一般的な基準値です。こちらの表も、H列・J列の表示設定はパーセンテージにします。さらに、J1には「=H2」、J2には「=H3」と入力し、同じ数値になるようにしておきましょう。

	H列	I列	J列	K列
1行目	0%	～	80%	A
2行目	80%	～	90%	B
3行目	90%	～	100%	C

準備ができたら、さっそくVLOOKUP関数を使ってみましょう。

E列に
=VLOOKUP(D2,$H$1:$K$3,4,TRUE)
と入力すると、D列の累積割合によって、H列からJ列の基準値に沿ったランク値が表示されます。オートフィルで表の最下段まで関数を入力すると、全体のランク分類は完了です。

まとめ：ABC分析で商品の重要度を見直そう

ABC分析は、精確な分析はできないものの、店舗経営に重要なデータを得るための手がかりとなる分析手法です。

しかし、ABC分析は商品が多くなればなるほど計算に手間がかかります。
この記事で解説した数式や手順をもとに、効率よく表計算ソフトを用いてデータ作成をおこないましょう。

ABC分析テンプレートのダウンロードはコチラ

タグ : データ分析