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

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

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

※下記を再生しても音は出ませんので、音が出せない環境でもご安心ください。

ABC分析とは?

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

  • Aランク(重要度:高)
  • Bランク(重要度:中)
  • Cランク(重要度:低)

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

ABC分析の具体例

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

abc_1_1
1.売上金額の順番に並び替え

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

2.累計金額の計算(D列)

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

abc_1_1_2

D2セルは「C2セル」と全く一緒の金額ですが、その下は、

  • D3セルは「C2セル~Cセル」の合計金額
  • D4セルは「C2セル~Cセル」の合計金額
  • ・・・

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

3.累計割合の計算(E列)

次に、全体の売上金額(=今回はD10セルに入っている3,846,920円)に対する割合を計算していきます。

abc_1_1_3
4.ABC区分の算定(F列)

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

abc_1_1_4

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

累計割合<80%
80%≦累計割合<90%
90%≦累計割合

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

ABC分析表の作り方

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

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

abc_1_2

累計売上金額の計算

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

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

D2セル:=sum(C2,D1)
abc_1_3

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

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

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

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

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

abc_1_4

累計割合の計算

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

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

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

E2セル:=D2/$D$10
abc_1_5

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

abc_1_6

ABC区分の計算

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

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

abc_1_7

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

F2セル:=vlookup(E2, $H$2:$K$4, 4, TRUE)
abc_1_8

条件付き書式で装飾

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

abc_1_9

abc_1_10

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

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

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

abc_1_11
※L2セルの計算式をL2~M4にコピーできます

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

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

1.グラフにしたい項目を選択し、グラフを選ぶ
abc_1_12
2.グラフの詳細を選択
abc_1_13

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

abc_1_14

まとめ

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

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

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

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。