今回のエントリーでは、このブログの「PV数」と「記事数」のデータを使って、エクセルを使った相関係数の求め方を解説します。
この記事で扱う内容は、↓こちらの散布図を作るために用いたローデータです。
相関係数の公式
まずは、相関係数の公式をさらっと確認しておきます。
- 相関係数=偏差積の平均÷記事数のSD÷PV数のSD
※SDとは標準偏差のことです
つまり、相関係数を求めたければ
- 偏差積の平均
- 記事数のSD(標準偏差)
- PV数のSD(標準偏差)
の３つを求めるってことですね。
相関係数を求めるまでの流れ
ということで
実際に計算を始める前に、全体の流れだけ↓に示しておきますので、頭に入れておいてください。
- PV数と記事数の「平均」と「標準偏差」を求める
- 「偏差」を求める
- 「偏差積」と「その平均」を求める
- 1〜3で求めた値を使って、相関係数を求める
以上の流れに沿って計算すれば相関係数など余裕で求められます。
計算に使用するデータの確認
実際に使うローデータはこちらになります。
No1〜No30までありますね。
それでは、このデータを使って、ブログの記事数とPV数の相関係数を求めることにしましょう。
※↑のフォーマットが欲しい方はこちらから
平均と標準偏差を求める
最初にこんな感じで、記事数とPV数の「平均」・「標準偏差」を求める枠を作っておきます。
平均は、「AVERAGE」を使って、標準偏差は「STDEV」を使って求めます。
ちなみに、「標準偏差＝√分散」であり、分散＝偏差の２乗の平均ですが、この意味がわからない方は過去記事を参照ください。
これで、それぞれの「平均」と「PV数」を求めることができましたね。
結果はご覧の通りです。
偏差を求める
続いて、偏差を求めます。
- 偏差＝データの値-平均値
で求めることができます。
ただし、計算をする前に、偏差を計算するために、「記事数」と「PV数」の項目の横に、それぞれ新たな行を挿入し、その列を「偏差」とします。
こんな感じ
これができたら、計算開始。
- 偏差=データの値-平均
でしたから、
- 記事数のNo1のデータ・・・「25-157.467=-132.47」
- PV数のNo1のデータ・・・「370-22462.4=-22092」
となります。
これを全てのデータにおいて計算すると・・・
こうなります。
データの後半です。
偏差積を求める
続いて、偏差積を求めます。偏差積は、先ほど「記事数」と「PV数」のデータごとの偏差を出しました。これをデータごとにかけ合わせた値が「データ積」です。つまり、以下の赤枠の部分を掛け合わせるということです。
するとNo1の偏差積はこうなります。
同様に、No2-No30まで算出します。
偏差積の平均を求める
はい。では、肝心の偏差積の平均を求めますが、例のごとく計算用の枠を設けましょう。
偏差積の列の値の平均を「AVERAGE」で求めます。
こうなります。
相関係数を求める
さて、これでいよいよ相関係数の公式に戻ってくることができました。
- 相関係数=偏差積の平均÷記事数のSD÷PV数のSD
※SDとは標準偏差のことです
この計算をすると、相関係数は、「0.87439」という値になりました。
※↑のフォーマットが欲しい方はこちらから
ということは、「PV数」と「記事数」には「強い相関がある」と言えるわけですね。
どうやって「強い相関がある」と判断したの？という方は↓↓をご覧ください。
参考書籍
①統計学がわかる（回帰分析・因子分析編）
②よくわかる心理統計
③やさしく学ぶデータ分析に必要な統計の教科書
コメント