Microsoft Officeで仕事力アップ【Excelで仕事力アップ】来客数が増えると、本当に売上も上がる傾向？2つの数字の相関関係を見える化！

使用ソフト：Excel2016

店舗で「来客数が増えれば売上も増える」というように、2つの項目に何らかの関係があることは、何となく予測できます。
しかし、来客数や売上のデータも売上日によって数値にばらつきもあるので、それを視覚的に把握できなければ説得力のある予測とはいいにくいですね。
このように、感覚的にではなく、視覚的に項目間の関係を把握したい場合は、散布図を作成します。
今回はExcelで散布図を作成して、2つの項目間の関係を視覚的に分析する方法をご紹介します。

散布図の作成

散布図を作成するときは、2つの項目のうち、結果を表示したい項目を縦軸に、もう一方を横軸にします。
今回の例では、来場者数が多いと売上高はどうなるかを表示したいため、結果を表示したい売上高を縦軸にします。
今回は、以下のような散布図を作成します。

コンビニエンスストアA店の、月別来客数と売上高の関係を示す散布図を作成してみましょう。

来客数と売上高のセル範囲【B5：C52】を選択します。
《挿入》タブ→《グラフ》グループの《散布図（X,Y）またはバブルチャートの挿入》ボタンの下向き三角のボタンをクリックし、一覧から《散布図》をクリックします。
散布図が作成されます。
グラフの表示位置とサイズを調整します。

散布図を見やすいように編集しましょう。
グラフタイトルに「来客数と売上高」と入力します。

横軸と縦軸に何を表しているかわかりやすいように軸ラベルを表示しましょう。
散布図を選択し、右上に表示されている《グラフ要素》ボタンをクリックして、《軸ラベル》をオンにします。

横軸のラベルに「来客数（千人）」、縦軸のラベルに「売上高（千円）」と入力します。

バランスよく情報が表示されるように横軸と縦軸の書式を変更します。
今回は、横軸、縦軸の最小値を再設定します。
横軸を右クリックし、《軸の書式設定》をクリックします。
《軸の書式設定》作業ウィンドウが表示されます。
《境界値》の《最小値》に《20》と入力します。

縦軸を右クリックし、《軸の書式設定》をクリックします。
《境界値》の《最小値》に《1800》と入力します。
横軸、縦軸の最小値を再設定できました。

正の相関と負の相関

「相関関係」とは、ある属性の値が増加すると、もう一方の属性の値も増加するというような、2つの項目の関係性のことをいいます。散布図にプロットされた点が直線に近い関係がある場合、2つの属性は相関関係があるといいます。
たとえば、以下のようになります。

気温が上がれば清涼飲料水の売上も上がるというように、一方が増加したら他方も増加する場合は「正の相関」であると言えます。
気温が上がればホット飲料の売上が下がるというように、一方が増加したら他方は減少する場合は「負の相関」であると言えます。
雑誌の売上と気温のように、プロットされた点が点在している場合は、「無相関」と言い、気温と雑誌の売上には相関関係がないことがわかります。

今回作成した散布図を見ると、点が右上がりになっています。これは、来客数が多いほど、売上高は高いことを示しています。つまり「正の相関」があるといえます。

相関係数の表示

散布図を作成することにより、正の相関があるのか、負の相関があるのか、あるいは無相関なのかを把握することができるようになりました。
さらに一歩進んで今度は、同じ正の相関であっても、その関連性の「強さ」を分析してみましょう。
同じ相関関係でも、データの分布によっては強い相関であったり弱い相関であったりします。このように、相関の強さを数値的に示したものが「相関係数」です。
相関係数を表示する場合は、CORREL関数を使います。来客数と売上高の関連性の強さを分析してみましょう。

セル【E5】に「=CORREL(B5:B52,C5:C52)」と入力します。

相関係数が表示されます。

相関係数がプラスであれば正の相関、マイナスであれば負の相関となります。正の相関では数字が大きいほど強い関係性があるといえます。
来客数と売上高の例では、相関係数はプラスなので正の相関であり、0.86であることから、強い関連性があるとわかりますね。

関連性の目安としては以下のようになります。

このように散布図を作成したり、相関係数を表示したりすると、2つの項目の関連性を分析できます。
ぜひ試してみてください。

• 【Excelで仕事力アップ】アンケート結果を細かく分析。従来の平均値による分析より、レベルをぐっと上げる方法！