Excel 時間帯による来客数の傾向を分析する方法

質問

1日の時間帯と来客数を毎日集計して(表参照)
・時間帯と来客数に何らかの相関があるかどうか
・日と来客数に何らかの相関があるかどうか
を調べるにはどのようにすればよいでしょうか。
例えば、
Excelに入力された表のデータから、
時間帯と来客数、または、日と来客数の間には、
何らかの相関がある、または、何の関係もないなど。

よろしくお願いします。(Windows10,Excel2016)

回答No.3

masnoske

No.2です。
やり方はそれで合ってます。
ただし、R2乗値のRが相関係数になるのは１次近似式の場合だけです。
ですが、多項近似式がどれだけフィットしているかを R2乗値で判断できます。
時間と来客数の関係はうまく説明できそうなので、予測可能かと思います。
差し支えなければ 2次式を提示いただけませんか？
多項式の場合は、ピーク値に注目すると面白いかも知れません。
ピーク値は、予測式を微分して 0になる点がピーク値になります。

日付と来客数は予測が難しそうですが、最大値と最小値に注目してください。
他の点に比べて特別離れていませんか？
特定の日が高い場合、その日に集客イベントをやっているかも知れません。
相関係数は、異常値の影響を受けやすいので、説明可能な異常値であれば、そのデータを外してから再度分析してみてください。

あと、曜日との相関も面白そうですね。
その場合は、
まず日～土に 0～6を割り当ててみます。
次に月～日に 0～6を割り当ててみます。
次に火～月に 0～6を割り当ててみます。
このようにして土～金までの 7通りのパターンを試してみてください。
何か傾向があるかも知れません。

お礼

いろいろアドバイスをしていただきまして
ありがとうございました。

回答No.2

masnoske

考え方はどちらも同じです．

・時間帯と来客数に何らかの相関があるかどうか
(1) 時間帯別の平均来客数を求める．
(2) x軸を時間帯，y軸を平均来客数として分布図を作成する．
(3) 分布図のオプションで回帰直線（1次近似）と R2乗値を表示．
(4) R2乗値で相関の強さを判定する．

・日と来客数に何らかの相関があるかどうか
(1) 日別の平均来客数を求める．
(2) x軸を日，y軸を平均来客数として分布図を作成する．
(3) 分布図のオプションで回帰直線（1次近似）と R2乗値を表示．
(4) R2乗値で相関の強さを判定する．

R2乗値のRとは相関係数です．
R2乗値の目安は以下の通りです．
1.00～0.49：高い相関がある
0.49～0.25：かなり高い相関がある
0.25～0.16：中程度の相関がある
0.16～0.09：ある程度の相関がある
0.09～0.04：弱い相関がある
0.04～0.00：ほとんど相関がない

一般的に0.5以上であれば「相関がある」，それ以下であれば「相関があるとは言えない」で判断すれば良いと思います．
ただし，そもそも xが yの説明変数になっているかどうかを考察することのほうが重要です．

お礼

回答ありがとうございます。
教えていただきました方法で確認しましたが、
相関についてよく分からない点があるので教えてください。

>・時間帯と来客数に何らかの相関があるかどうか

　時間帯別の平均来客数を求めて散布図を作成して、
　グラフ右上の＋(グラフ要素)ボタンをクリックして、
　近似曲線/その他のオプションで、
　右端に「近似曲線の書式設定」のダイアログが表示されますが、
近似曲線のオプションの中に「回帰直線（1次近似）」が見当たりませんでしたので、
以下のようにして確認をしました。

　1) 線形近似を選択して、
「グラフに数式を表示する」と「グラフにR-2乗値を表示する」をチェックすると
　 グラフに直線が表示されましたが、
データのドットとはあまり一致していなくて、R-2乗値は0.2054でした。

2) 多項式近似を選択して、次数を2にして
「グラフに数式を表示する」と「グラフにR-2乗値を表示する」をチェックすると
　 グラフに曲線が表示されて、データのドットに近い軌跡を描いていて、
　 R-2乗値は0.9205でした。

>・日と来客数に何らかの相関があるかどうか

3) 日別の平均来客数を求めて散布図を作成して、
　 1)と同様の処理をしてグラフに直線が表示されましたが、
データのドットとはあまり一致していなくて、R-2乗値は0.0841でした。

これより、時間帯と来客数には、
1次元式では相関があるとは言えないが2次元式では高い相関がある。
日と来客数には1次元式では相関があるとは言えない。
という理解で正しいでしょうか。

よろしくお願いします。

回答No.1

aokii

エクセルによって相関係数を計算するには分析ツールを使う方法がお勧めです。
ファイル→オプション→アドイン→分析ツール→設定→分析ツール→OK

お礼

ありがとうございました。