今回は、並べ替え機能を使ってABC分析を行う方法を紹介していこう。ABC分析とは、過去の売上実績などをもとに、商品をA、B、Cの3つのグループに分類して管理するマーケティング手法となる。Excelを使って簡単に導き出すことができるので、気になる人は試してみるとよいだろう。

ABC分析とは？

ABC分析は、数ある商品を「ランクA」「ランクB」「ランクC」の3つのグループに分類して在庫管理や販売戦略の立案などに役立てるマーケティング手法となる。

たとえば、過去の売上実績をもとに商品を分類する場合、

　・「ランクA」は売上に大きく貢献している主力商品
　・「ランクB」は売上に少し貢献している中堅の商品
　・「ランクC」はほとんど売上に貢献していない商品

と考えて戦略を練っていくことになる。

今回は、あるTシャツ屋さんの販売実績をまとめた表を例に、ExcelでABC分析を行う手順を紹介していこう。

  • 販売実績をまとめた表

ABC分析の手順

今回は、売上金額のデータをもとにABC分析を行ってみよう。まずは「売上」の大きい順にデータを並べ替える。

  • 「売上」の大きい順に並べ替え

続いて、各商品が売上に貢献している割合（構成比）と、その累積値を算出する。これらの計算を行うために、先ほどの表に以下のようなセルを追加する。

  • ABC分析を行うためのセルを準備

それでは、さっそくABC分析を行っていこう。最初に「売上」の合計を関数SUM()で求める。

  • 「売上」の合計の算出

次は、「売上の合計」に対する各商品の「構成比」（割合）を算出する。この値は（各商品の売上）／（売上の合計）で算出できる。このとき、数式の分母（売上の合計）を絶対参照で指定しておくと、以降のセルに数式をオートフィルでコピーできるようになる。

  • 「構成比」を計算する数式の入力

  • オートフィルで数式をコピー

今回の例では、各商品の「構成比」が以下の図のように算出された。通常の数値のままでは状況を把握しにくいので、表示形式を「パーセンテージ」に変更しておこう。

  • 算出された「構成比」

  • 表示形式を「パーセンテージ」に変更

続いて、それぞれの構成比を順番に加算していき、構成比の「累計」を求める。1番上にあるセルは、左隣のセルをそのまま参照すればよいので、「＝（セル参照）」と数式を入力する。

  • 累積構成比の算出（1）

2番目のセルは、「先ほどの値」に「2番目の商品の構成比」を足し算した値を算出する。これを数式で記すと、以下の図のようになる。

  • 累積構成比の算出（2）

3番目以降のセルも同様の手順で「累積」の値を算出していく。この計算は「2番目の数式」をオートフィルでコピーすると実行できる。正しく計算が行われていれば、最後のセルの「累積」は必ず100％になるはずだ。

  • オートフィルで数式をコピー

  • 算出された「累積構成比」

これでABC分析の準備は完了。あとは「累積」の値に応じで、商品をA、B、Cの3つのグループに分類していくだけだ。この分類方法について絶対的な決まりはないが、一般的には、

　・ランクA ・・・・・ 累積構成比が70％未満の範囲
　・ランクB ・・・・・ 累積構成比が70～90％の範囲
　・ランクC ・・・・・ 累積構成比が90～100％の範囲

のように分類するケースが多いようである。今回の例を上記のように分類すると、「ランクA」「ランクB」「ランクC」の商品は、それぞれ以下の図のようになる。

  • ABC分析の例

以上が、ExcelでABC分析を行うときの基本的な操作手順となる。

ABC分析は何を意味している？

ABC分析は「パレートの法則」を応用して在庫管理などを行う手法となる。パレートの法則は、別名「80:20の法則」とも呼ばれるもので、「全体の8割を、わずか2割の構成要素で生み出している」という状況を示したものとなる。具体的な例を挙げると、

　・全売上の8割を、わずか2割の商品で生み出している
　・全売上の8割を、わずか2割の顧客が生み出している
　・全アクセス数の8割を、わずか2割のWebページで稼ぎ出している
　・全社会の所得の8割を、わずか2割の人々が稼ぎ出している

などが「パレートの法則」に当てはまる事例となる。

もちろん、これは大雑把な話であり、必ずしも80:20になる訳ではない。実際には、75:25の場合もあれば、70:30の場合もある。要は、「少ない構成要素により、全体の大部分が占められる」ということを意味している。

先ほど例として示した「Tシャツの販売実績」の場合、全部で18種類のTシャツがあり、その1/3となる6商品（ランクA）で全体の約7割の売上を生み出していることになる。同様に、CランクのTシャツも商品数は6個であるが、その売上構成は10％程度しかないことになる。

このように、ABC分析を行うと、「どれが主力商品（ランクA）なのか？」、また「どれが売上に貢献していない商品（ランクC）なのか？」を明確に把握することが可能となる。

当然ながら「ランクA」の商品が欠品すると、売上は大きく落ち込むことになる。よって「ランクA」の商品はこまめに在庫管理を行い、欠品しないように細心の注意を払う必要がある。その一方で、「ランクC」の商品は少しくらい欠品しても、それほど大きな問題には発展しないと考えられる。

要は、商品に応じて在庫管理の体制や頻度を変化させることで、より効率的に運営していこう、というのがABC分析の目的となる。もちろん、商品の在庫管理だけでなく、さまざまな分野にABC分析を応用することが可能だ。気になる方は、各自が携わっている業務でも試してみるとよいだろう。

ただし、ABC分析に過度の期待を抱いてはいけない。ABC分析は、①売上の大きい順に並べ替え、②各商品の「構成比」と「累計」を算出し、③3つのグループに分ける、といった作業を行っているに過ぎない。このため、実際に店舗で働いている方からすると、おそらく当たり前の結果しか得られないであろう。

現場に携わっていれば、いちいちABC分析を行わなくても「売れている商品」と「売れていない商品」を感覚的に把握できているはずだ。「ABC分析」は、それを再確認するための手段にしかならない場合が多い。

よって、絶対に行うべき分析手法とはいえない。「より体系的な管理体制を構築したい」、もしくは「現場を知らない上司に向けて、報告書や要望書を作成したい」といった場合などに、「もしかしたらABC分析が使えるかも？」と思い出して頂ければ十分である。