2013年

3月

05日

データ分析の勧め ~Excelを使って自社の業務データを整理・分析してみよう!~ その5

第5回 データ分析の実際・バラツキを考える(散布図を作る)

今回は散布図を作り、売上金額と利益率でバラつかせてみて全体の傾向を掴み、グループ分けをして商品グループ毎の対応を考えてみます。前回まで使ったフライパンの売上明細データを使って、今後の販売戦略や商品の取り揃えなどの検討を行うのに役立てようというものです。

 

※説明に使用するサンプルデータをダウンロードできますので、実際に操作しながら確認してみてください。


①前回までと同様にピボットテーブルで図5-1の売上金額、利益額の表を作ります。これを別のシートにコピーし、利益率の計算を追加してグラフを作るのが一般的です。ちなみにピボットテーブル上では散布図は作成できません。

図5-1 ピボットテーブルで売上金額と粗利益を集計 (クリックで拡大)
図5-1 ピボットテーブルで売上金額と粗利益を集計 (クリックで拡大)

②なお、最近のピボットテーブルは値の列項目を使って、計算結果を表示することが出来るようになりました(手間を考えると別シートで当たり前に「=粗利率/売上金額」と計算列を追加するのとあまり変わりませんが)。 

 

まず、ピボットテーブルツールのオプションを選び「フィールド/アイテム/セット」をクリックします。集計フィールドの挿入というウィザードの名前を「粗利率」、数式欄にフィールドの挿入欄から粗利益と売上金額を選び「/」を入れて数式を完成させてOKをクリックすると、「合計/粗利率」の列が追加されます。

図5-2 粗利率(粗利益÷売上金額)の列を追加 (クリックで拡大)
図5-2 粗利率(粗利益÷売上金額)の列を追加 (クリックで拡大)

 

③ピボットテーブルで散布図は作成できないので、どのみち別のシートにコピーして、この表から散布図を作っていきます。貼り付けた表の売上金額のB列と粗利率のD列をマウスで選択し、挿入-グラフ-散布図と選んでいくと散布図が出来上がります。

図5-3 散布図の作成 (クリックで拡大)
図5-3 散布図の作成 (クリックで拡大)

 

④あとはグラフの書式を整えます。ここで平均売上金額と平均粗利率を計算してグラフに線を挿入し、4つの象限に分けてそれぞれに属する商品の対応を考えていきます。

 

図5-3の表の下に総計も貼り付けてありますが、ここの下に平均の売上金額を計算して入れておきましょう。ここでは数式のAVERAGE関数を使ってB列の明細データを選択しています。粗利率は総計の粗利率がそのまま平均となりますのでこれを使います(図では見やすいように明細行と総計行の間を一行わざと空けてあります)。

図5-4 平均値の計算 (クリックで拡大)
図5-4 平均値の計算 (クリックで拡大)

 

⑤体裁を整えた散布図の上に売上金額と粗利率の平均値の線を引いて4つの象限に区分けします。整理したものを図5-5に示しましたが、それぞれの象限に名前を付けてみました。

 

右上の象限の商品群は「今日の商品」です。これは売上金額も粗利率も高い、こちらの企業の看板商品です。これを継続することが重要です。

 

右下は「昨日の商品」。売り上げは平均より高いのですが、粗利率が低い商品群です。単なる薄利多売なのかボチボチ賞味期限キレで値引き品になっているのか。このへんはさらに突っ込んで、発売時期や得意先の特徴、その数などさらに分析が必要となります。

 

左上の象限は「明日の商品」と名付けました。売り上げは低いのですが高利益商品群です。ここの中から右上の事象「今日の商品」に移るものが出るのか。移るためには何をしなければならないのか。商品1点ごとに分析して、販売戦略を練り直すことが重要となります。 

 

左下は「品揃え品」です。ここに位置する商品はそれぞれに意味を持っているので、一括りに扱うことはできませんが、廃止するのか今後の取り扱いを含めて検討しなければなりません。さらには在庫日数が長くなっていないか、これ以外に不良在庫化しているものはないのか(ここでは売上データ分析なので荷動きの無いものは表示されない)、商品在庫データにも注目して、商品戦略のカギとなってくる商品群です。

 

バラツキを考え、そのバラツキから特徴のある群に分けて見ると、次に行うことが見えてきます。これがデータ分析の本質です。決して難しく考えず、分析することに慣れていただくことが重要です。

図5-5 散布図でバラツキを考える (クリックで拡大)
図5-5 散布図でバラツキを考える (クリックで拡大)

 

単に集計表を作る、それをグラフ化するというだけでなく、何を知りたいのか、検証したいのかを考えて、データ分析に取り組んで下さい。次回は簡単だけれど意外と解説書に掲載されていない、集計や比率だけでなくデータの個数(カウント)を使ったクロス集計表について説明します。

NICO 情報戦略チーム 星野

 

「いいね!」ボタンを押していただくと、最新情報をお届けできます。

 フィードの購読もできます。

 (NEWS,BLOGのみ)

連載ブログ 

 大人気3rdシリーズ

 

公益財団法人にいがた産業創造機構

産業創造グループ

情報戦略チーム

TEL 025-246-0069

Mail  kns@nico.or.jp