データ分析

売れ筋商品を見つけるためのデータ分析(パレート図をつくる)

どの商品が売れているか、把握していますか?

POSレジからデータが出てくることも多くなっていますが、まだ対応していない組織ではエクセルで商品別売上を出す必要があります。

<スポンサードリンク>



日々の売上データから商品別売上を求める

毎日の売上はレジを操作するたびに、どんどん溜まっていきます。

どの商品が売れ筋なのか見極める前に、商品別の売上を求めましょう。ここでは2つの方法をご紹介します。

SUMIF関数を使う方法

SUMIF関数というものを使う方法です。ザックリ言うと、条件に合った数値だけを合計してくれる関数です。

日々の売上データの隣に商品別売上の欄をつくります。

例えば、掃除機の売上合計を出すには、品名(B列)から掃除機(G3)だけを選び出し、その売上金額(E列)を合計します。それを式にすると以下のようになります。

H2 = SUMIF(B:B,G3,E:E)

あとはこの式をゲームソフトのH12までコピーすれば商品別の売上高が出てきます。

ピボットテーブルを使う方法

関数が面倒くさいという場合は、ピボットテーブルを使う方法があります。データのどこかを選択した状態で、挿入→ピボットテーブルを選択します。

行に品名を、値に売上金額をドラック&ドロップすると、すぐに商品別の売上が出てきます。

ピボットテーブルの方が、商品の追加があったときでも即対応できるのでラクなのですが、後述するパレート図を出すなら、SUMIF関数を使う方がやりやすいと感じています。(エクセルは選択肢が多すぎて悩みますね~)

パレート図をつくって、売れ筋商品を把握する

次にパレート図をつくりましょう。数字だけでもある程度、売れ筋商品を把握することはできますが、図にすることによって、より直感的に分かります。こんな図です。

棒グラフは商品別の売上高で、折れ線グラフは売上構成比の累計です。

一般的には上位80%に入っている商品が売れ筋です。この場合ですと、エアコンまでが売れ筋(Aランク商品)と考えられます。80~90%に入るのがスマホ(Bランク商品)、それ以降がCランク商品です。ABC分析と呼ばれます。

では、このパレート図のつくり方です。まずはSUMIF関数を使って出した商品別売上を、売上高が多い順に並び変えましょう。

先ほどのSUMIF関数の説明では同じシートに表をつくっていましたが、並び替えをすることを考えると、最初から別シートに作成しておいた方がラクです。B列を選択して降順に並び変えましょう。

C列に累積売上高を作成します。テレビの場合は、パソコンの売上にテレビの売上を追加します。

C3 = C2 + B3

あとは最後の電池まで同じです。さらにD列に売上構成比累計を出します。全体の売上は電池までの累積売上高と等しいですから

D2 = C2 / C$11

です。最後は絶対参照($)にしておきます。後は電池まで計算式をコピペします。最後で累積が100%になっていることを確認しましょう。(そうなったなければ、どこかを間違えています)

これで数字データは出来たので、グラフを挿入します。商品名・売上計・売上構成比累計の範囲を選択します。

この場合は、A1からB11を選択。さらにCtrlキーを押したまま、D1からD11を選択します。その状態でグラフを挿入します。挿入→「マーカー付き折れ線」グラフを挿入。そのままだと、「なんじゃこれ?」というグラフですが、慌てずに。

グラフツールの書式から、「系列”売上構成比の累計”」を選択して、「選択対象の書式設定」をクリック。右側にデータ系列の書式設定が出てくるので、「第2軸」を選びます。

さらに売上計を棒グラフに変更します。
グラフツールのデザインから、「グラフの種類の変更」

売上計のグラフの種類を「集合縦棒」に変更します。グラフのタイトルを修正して、パレート図の完成です。

ちなみにこのパレート図は、商品別でなくとも、顧客別・事業別など、様々な切り口で活用することができます。今回は商品別を例にしましたが、皆様の商売内容に応じて、使い分けて下さい。

まとめ
  • 数字だけよりも、図式かした方が売れ筋商品を把握しやすい
  • 売れ筋商品を把握するには、パレート図が便利
  • 上位80%がAランク、90%がBランク、その他がCランク商品

本連載の記事を以下にまとめておきます。

  1. 売上の傾向を把握するためのデータ分析(移動平均)
  2. 売上目標(予算)の達成状況を把握するためのグラフのつくり方
  3. 顧客別・商品別・部門別の売上を割り出す方法
  4. 売れ筋商品を見つけるためのデータ分析(パレート図をつくる)(←今回はここ)
  5. 事業ごとの成長率を把握するためのデータ分析(ファンチャート)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
メルマガ『経営は100種競技!』を毎日配信しています。
マーケティングやITを身につけたい。
ビジネスを楽しみたい。
変化・成長したいというビジネスパーソンにお読みいただいています。