【経営に役立つ】エクセルで売上分析（ABC分析）をする

2016年12月20日

大量の商品・売上先などがあると、全ての商品・売上先を重点的に管理することは不可能です。
そのため、商品・売上先の重要性により区分をする必要があります。

その重要性を判断する１つに「ABC分析」とよばれる手法があります。

ABC分析とは？

ABC分析というのは、商品・売上先別売上金額について、それぞれの「重要度」に応じて、

• Aランク（重要度：高）
• Bランク（重要度：中）
• Cランク（重要度：低）

の３つに区分して管理をする手法です。

ABC分析の具体例

例えば、次の例では、商品を「売上金額」に応じて重要度を付けています。

１．売上金額の順番に並び替え

最初に売上金額の順番に並び替えて、売上高の大きい商品が一番上に来るようにします。

２．累計金額の計算（Ｄ列）

次に、売上金額の累計を計算します。

Ｄ２セルは「Ｃ２セル」と全く一緒の金額ですが、その下は、

• Ｄ３セルは「Ｃ２セル～Ｃ３セル」の合計金額
• Ｄ４セルは「Ｃ２セル～Ｃ４セル」の合計金額
• ・・・

というように計算していきます。

３．累計割合の計算（Ｅ列）

次に、全体の売上金額（＝今回はＤ１０セルに入っている3,846,920円）に対する割合を計算していきます。

４．ＡＢＣ区分の算定（Ｆ列）

最後に累計割合を使ってＡＢＣの区分を振ります。

今回は、累計割合に応じて、次のようにランク付けをしています。

累計割合＜80%	Ａ
80%≦累計割合＜90%	Ｂ
90%≦累計割合	Ｃ

このような表の作り方を解説していきます。

ＡＢＣ分析表の作り方

それでは、実際に上のようなＡＢＣ分析表を作ってみようと思います。

下記データから、実際に加工をしていきます。

累計売上金額の計算

売上金額を上から順番に足していきます。

いろいろな計算方法がありますが、一番おすすめなのは次の計算式です。
※C2とD1の間は、コロン「：」ではなくカンマ「，」です。お気を付けください。

sum関数で複数のセルを「カンマ」で区切って入力すると、複数のセルを単純に足すという意味になります。

ですから、上記計算式は「=D1+C2」と（ほとんど）同じ意味の計算式ということになります。

ただ、通常の足し算で入れてしまうと、D1セルに文字が入っているためにエラーになってしまいます。

そこで、ここではあえてsum関数を使っています。

あとは、上記計算式を下にコピーすれば、累計金額が計算できます。

累計割合の計算

次に、売上金額の累計割合を計算をします。

先ほど、累計売上金額を計算したので、その一番下（今回であればD10セル）に売上合計が入っています。

そこで「各行の売上高÷D10セル」で累計売上割合を計算していきます。

D10セルへの参照を「絶対参照」にしておいたので、計算式を下にコピーしても正しい計算ができます。

ABC区分の計算

次にABCの区分を計算します。

ABCの区分けをどこにするか柔軟に対応できるようにするため、次のように上位何パーセントをA,B,Cに区分するかを表す表を作成します。

このような表を作っておくと、vlookup関数を使ってABC区分を計算することができます。

条件付き書式で装飾

次のような感じで条件付き書式を設定することで、下記のように表を装飾することができます。

※条件付き書式を設定する詳しい手順はエクセルのプルダウンリストで選択すると同時に色を変えるをご参照ください。

sumif関数を使ってABC区分別の集計

また、sumif関数を使えば、ABC区分別に数量・売上合計を計算することもできます。

商品ごとの売上金額・累計割合のグラフ化

あるいは、売上上位の商品からの売上金額・累計割合の比率をグラフ化することもできます。

１．グラフにしたい項目を選択し、グラフを選ぶ

２．グラフの詳細を選択

さらに、書式や軸を適当に整えることで、次のようなグラフができあがります。

まとめ

ABC分析をすることで、次のような分析データが得られます。

• 商品の重要度を、売上額（あるいは売上数量）で区分する
• 売上先の重要度を、売上額（あるいは売上数量）で区分する
• 在庫の重要度を、在庫金額（あるいは在庫数量）で区分する
• 経費項目の重要度を、勘定科目別の計上額で区分する

経営に役立つ情報が取れると思いますので、ぜひ、ご活用ください！