経済学の有名な法則に「パレートの法則」というものがあります。これは数値の8割は、2割の要素によってもたらされるという法則です。
これは、一般的に、売上にも適用されると言われています。つまり、売上（数値）の8割は、2割の商品（要素）が生み出しているのです。
あなたの売上の8割をもたらしている商品は何でしょうか？ 商品の優良度を測定するABC分析を用いて見つけてみましょう。
ABC分析とは？
ABC分析を簡単に言えば、対象をA・B・Cの3つに「ランク付け」する手法です。
企業が抱える在庫を効率よく管理するためなどに用いられます。
たとえば対象が商品の場合、次のようになります。
- A … 重要管理品目（売れ筋商品）
- B … 中程度管理品目（戦略商品）
- C … 一般管理品目（一般商品）
パレートの法則をあてはめる場合、Aには売上の80%を占める20％の商品が入ることになるでしょう。
BとCの割合は、一般的に目的によって変化します。たとえば、Bを一般的な商品として割合を大きく取り、Cを人気のない商品としてコストとの比較を検討する等が考えられます。
このようにA・B・Cそれぞれの割合は一定ではありませんが、一般的にはAを10%、Bを20%、Cを70%程度とすることが多いようです。
今回はこうしたABC分析手法をエクセルで行ない、記録した商品リストなどを分析する方法をご紹介します。
エクセルを使ったABC分析
エクセルを使ってABC分析を行なうにはいくつかの情報が用意されている必要があります。
次の手順で作業を行います。
- 商品ごとに売上を集計
- 売上高で並べ替え
- 商品ごとに、売上が全体の何割を占めるかを集計
- この結果を上位の商品から順に累積する
- この割合の累積が何%に達するかをもとに、商品をABCの三つのクラスに分類
ひとつずつ作業を進めていきましょう。
最初に、記録された商品一覧の売上高を大きい順に並べます。売上が記入されたセルの先頭を選択し、リボンの「データ」タブから「並べ替え」というアイコンを見つけてください。その左隣にある小さな「ZA」というアイコンをクリックします。
続いて隣のセルD列に売上の累積額を記入します。
累積売上高を記入するには、最初に先頭のセルをコピーします。画像ではC2のセルに先頭があるので、D2セルにも入力しています。
2番目のセルには「1つ上の金額＋2つ目の売上」を計上するように計算式を記入します。
累積で計算されたセル（2番目）をオートフィルを使って一番下まで複製します。
累積売上高が記入されました。
続いて「商品ごとに、売上が全体の何％を占めるか」を求めます。ここではこれを構成比と呼びます。
商品ごとの売上比率を求める計算式は簡単で、
=[売上高の先頭のセル番号] / [累積売上高の末尾のセル番号（画像では絶対参照を使用）]
です。カッコや半角スペースは入れないでください。
画像だと売上高の先頭がC2、累積売上高の末尾はD22になります。
絶対参照を使う場合、対象のセルの「行番号」「列番号」それぞれの先頭に半角の $ をつけます。
今回、累計売上高の位置を固定したまま、全商品の構成比を計算したいため、「$D$22」と絶対参照にしました。
構成比が求まりましたが、セルの書式がデフォルトのため小数点で表示されています。
わかりやすく割合表示としたい場合は、リボンの「ホーム」タブより「％」アイコンをクリックするとセル内の値を％で表記してくれるようになります。
この構成比も1つ作ったらオートフィルを使って全体に適用します。
最後に、構成比をもとにして商品にランク付けを行います。
=IF(E2<=5%,”C”,IF(E2<=20%,”B”,”A”))
画像で使用した計算式はこのようになります。
=IF()はIF関数による「条件分岐」（計算に条件を設け、あてはまる時はtrue（先に設定した条件）、あてはまらない時はfalse（後に設定した条件）と異なる結果を返す式）です。IF関数の中にもう1つIF関数が入っているので、この計算式は条件次第で3種類の結果を返してくれます。
E2<=5%,”C”,が1つ目の条件と答えで、「E2セルの中身が5%以下ならCを返す」もの。
IF(E2<=20%,”B”,”A”)までが、「E2セルの中身が20%以下でない場合この計算を行なう」ことを示します。
E2<=20%,”B”,”A”は2つ目の条件分岐となります。これは「E2セルの中身が20%以下ならB、そうでなければAを返す」式です。
たとえばE2セル内に 26% と入っていた場合、1つ目のIF関数ではfalesとなり、2つ目のIF関数でもfalesとなるため「A」と返されることになります。
ランクもオートフィルを使って全体に適用します。
以上で、画像で使用した22種類の商品の売上に対するABC分析結果が求められました。
今回は「ランクA＝全体の20%」としていますが、変動させたい場合は計算式の中をいじれば対応できます。
構成比を円グラフで見る
構成比は円グラフ表示するとわかりやすくなります。
「商品名」の列を範囲選択してから、Ctrlキーを押したまま「売上」の列を範囲選択します。
「挿入」タブから「円グラフ」のマークを選択します。
そうすると、売上の構成比が円グラフで表示されます。
円グラフを作成すると、総数に対する割合が自動で表示されるので、計算式を入れなくても視覚的に構成比を確認することができます。
そのため、先ほど計算式で出した構成比から円グラフを作成しても、同じ表示になります。
いかがでしたか？
商材が複数ある場合や、メンバー毎の売上を比較したいときなど、構成比を知ることはその後の戦略へとつなげることができます。ぜひ活用してみてください。
