商圏には、同じエリアといえども、お店と相性の良い地域と悪い地域が存在します。
一律にどの地域からもお客様がやって来るとは限りません。
- 大きい道路や大きな川などがあって物理的に分断されている地域
- 年齢などお店の客層と違う人が住んでいる地域
などは、チラシを撒いても反応が悪いものです。
そこで、エクセルでデータ分析を行い、どの地域に販促を重点的に行うべきかを割り出す方法をご紹介します。
※エクセルは２００７を使用
異なる単位を比較するにはデータの「標準化」が必要
どの地域がお店と相性が良いか？あなたはそう聞けば、わざわざデータ分析をしなくとも、単純に売上額や客数が多い地域を調べればわかるだろうと思うかもしれません。
そう反論する前に、売上の方程式を思い出して下さい。
売上の方程式は
です。
たとえば同じ１０万円の売上額でも、
１人×１０万円×１回＝１０万円
１０人×１万円×１回＝１０万円
５人×５千円×４回＝１０万円
と、その内訳よって、売上額の意味が変わります。
客数ベースで考えても同じです。
地域によっては、客数は多いが売上が低い地域もあるでしょう。その反対に、客数は少ないが売上は多い地域もあります。
だとしたら、単純にデータを数字の多い順に並べても意味ないでしょう。
間違った解釈のデータで予想しても、当たる確率は上がりません。
比較に必要なのは、異なる単位のデータを標準化することです。
データを基準を揃えてこそ、その数値の持つ意味がわかります。
ググって調べてください
異なる数値を標準化するには、データごとの「平均値」と「標準偏差」を求めます。
なぜデータの標準化に平均値と標準偏差が必要なのかは、ご自分で書籍を買うか、「エクセル 標準化データ」など入れてググって調べるてください。
ここでは「そいうもの」という前提で話を進めていきます。
動画解説
動画での解説はこちらから視聴できます。テキストでの解説はこのまま記事をお読みください。
動画はエクセル2016を使用しています。
この売上と客数でチラシ投入地域をどう判断しますか？
では次のように、地区ごとの客数と売上を集計したデータがあります。
分析の目的は、お店と相性の良い優良地域を見つけ出し、そこに重点的にチラシを投入しようという考えです。
客数の単位は「人」、売上は「円」です。
この表だけ見れば、客数は少なくても売上が高い地域や、客数は多いが売上はそこそこの地域もあり、客数を基準とすべきか、売上を基準とすべきか迷うところです。
この異なる単位を比較し、限られた予算の中で、チラシを重点的に撒くべき地域を見つけるにはどうすればいいでしょう？
そこで必要なのが、データの「標準化」です。
標準化することで、違った単位のものでも、同じものさしで測ることができます。
データを標準化するためには、平均値を「０」に、標準偏差を「１」にします。
平均値の出し方
まず平均値を計算します。平均値は「AVERAGE」で求めます。ここでの数式は「=AVERAGE(B3:B9)」になります。(B3:B9)が平均を求めるセルの範囲。
数式をセル（B10）に入れたら、売上の列のセル（C10）にもドラッグして数式を自動で入れます。
これで、平均値は出ました。
標準偏差の出し方
次に標準偏差を算出します。
標準偏差を出す関数は、「STDEV」で、数式は「=STDEV(B3:B9)」と入力します。「(B3:B9)」は計算の対象となるセルの範囲。※「STDEV」は、エクセル2013以降は「STDEV.S」
数式をセル（B11）に入れたら、売上の列のセル（C11）にもドラッグして数式を自動で入れます。
これで、標準偏差も出ました。
データを「標準化」する
平均値と標準偏差が出ましたので、この２つの数値を使って、それぞれの客数と売上の数値を標準化していきます。
標準化するための関数は「STANDARDIZE」です。入力する数式は「=STANDARDIZE(x,平均,標準偏差)」です。
セルF3に入力する数式は「=STANDARDIZE(B3,$B$10,$B$11)」です。
- X:B3（標準化したい数値のセル）
- 平均：B10
- 標準偏差：B11
※数式の中に「$」と付くのは、「絶対参照」の意味です。必ずここを参照しろと命令しています。
=STANDARDIZE(B3,$B$10,$B$11)
この数式では、「B10」と「B11」を絶対参照しろという意味です。「$」は「$B10」や「B$10」という使い方もできます。「$B」はB列を絶対参照しろという意味で、「$10」はセルの10を絶対参照しろという意味で使います。
F3のセルに数式を入力したら、オートフィルでセルF9まで、ドラッグして数式を入力します。
これで、客数の標準化したデータが算出されました。
売上の列も同じ手順で数式を入力します。数式は「=STANDARDIZE(C3,$C$10,$C$11)」です。
G3のセルに数式を入力したら、オートフィルでセルG9まで、ドラッグして数式を入力します。
これで、すべて数値が標準化したデータになりました。
平均値と標準偏差も「０」と「１」になっています。
数値の結果の読み取り方
客数の「人」と売上の「円」を標準化することで、客数と売上を同じ数値として比較することができます。
標準化されたデータをＺ値といいます。
Ｚ値の見方は
- Ｚ＝０ データ値は平均値と同じ
- ０＜Ｚ＜１ データは平均値より大きいが標準偏差の範囲内にある
- Ｚ＞１ データは標準偏差を超えるほど、平均値を大きく上回る数値
- －１＞Ｚ＞０ データは平均値より小さいが、標準偏差の範囲内にある
- Ｚ＜－１ データは標準偏差の範囲内を超えるほど、平均値を下回る
といっても、数値だけではよくわからないので、グラフ化して目で理解できるようにします。
グラフ化して分析しやすくする
標準化したデータのグラフ化したい範囲、「F2:G9」までをドラッグして選び、「挿入」から「散布図」をクリックします。
散布図が挿入されました。
データラベルに地区名を入れる
ただこれだけでは、何区がどこなのか読みづらいので、データにラベル（地区名）を付けていきます。
「デザイン」→「グラフのレイアウト」から「レイアウト６」を選びます。
「軸ラベル」をクリックして、縦軸に「売上」、横軸に「客数」と入力します。
次に散布図にプロットされた任意のデータの上を右クリックし、「データラベルの書式設定」をクリックします。
「データラベルの書式設定」メニューが開いたら、「Ｘ値」と「Ｙ値」にチェックを入れ、「閉じる」ボタンを押します。
データの横に数値が入ったことが確認できます。
最後にそれぞれのデータにラベル名（地域名）を付けていきます。
まず「Ｃ地区」から付けていきます。
グラフ上の「Ｃ地区」のデータを確認し、データラベルを２回ゆっくりクリックします。そして「数式バー」に「=」を入して、「Ｃ地区」と書いてある「E5セル」をクリックし、「ENTER」ボタンを押します。
「Ｃ地区」と入力されました。
あとはその他のデータラベルも、同じように入力していきます。
グラフの見方
この散布図の右上のエリアにある地域が、客数、売上とも平均より上で、お店と相性の良い優良地域です。
Ｅ地区とＤ地区にチラシを投入すべきとはっきりと判断できます。
Ａ地区、Ｃ地区、Ｇ地区も投入候補としてはありでしょうが、予算が限られているなら、この地区は後回しを検討してみても良いでしょう。
このようにグラフ化しなければ、あいまいな判断しかできないでしょう。
ただ、標準化したデータからもわかるように、Ａ地区は売上を伸ばせば、優良地区に格上げされるスペックがありそうだと予想できますし、Ｃ地区は客数は少ないですが、売上は１より大きい値を示していますので、ここも客層を調べれば十分伸びる地域だといえそうです。
散布図にして可視化すれば、分析がしやすく、今後の販売戦略にも役立てることができます。
お客様のランク付けにも利用できる
今回はチラシの投入地域を見つけるためにこの分析法を使いましたが、この方法はお客様のランク付けにも応用できます。
たとえば、売上額と購入回数などを比較して、優良顧客を見つけ出し、そのお客様にダイレクトメールを送れば、高反応が返ってくるでしょう。
単純に、購入回数、売上額、お店までの距離などでリストから抽出しても、送り先が増えるばかりです。
最小の資金で最大の効果を求めるなら、データ分析して、真の優良顧客を見つけましょう。
まとめ
数値を表にまとめただけでは、見えてこないものがあります。
単純に数字が多いか少ないかだけで並べてみても、大きく外れた予想値が出てしまいます。
それではデータ分析の意味がないでしょう。
今後は、少子高齢化や社会保険料の増大などで、世の中に出回るお金が少なくなります。
貴重な経営資源を無駄にしないためには、データ分析して、勝てる勝負に投入していかなくてはいけません。
決して、えいやあで勝負しないようにしてください。
負ける確率だけが大幅に増えます。
コメント