エクセル分析スペシャリスト対策！ スプレッドシートで相関係数を求める操作方法
はじめに
ご訪問ありがとうございます！まちゅけんです。
ヨメちゃんです。
１：スプレッドシートを使った相関ツールの操作
２：相関ツールの使い道
この記事ではエクセル分析スペシャリストの学習に役立つ、スプレッドシートの使い方について解説をします。今回のテーマは相関編です。Excelを使用する方法は別でご紹介をしています。
https://machuken.com/post-891/「ビジネス統計スペシャリストってなに？」という方は以下の記事を参考にして下さい。
操作方法
スプレッドシートを使った相関ツールの操作方法について解説をします。前提として、スプレッドシートのデータ分析ツールは初期設定の状態では使うことができません。「初期設定のままだ！」という方は、データ分析ツールの有効化についてまとめた記事があるので参考にして下さい。
相関ツールの説明にあたり、以下の例題を使用します。
あなたはアイドルグループの事務所のマネージャーです。この度、動画配信サイトで新しい動画チャンネルを立ち上げることになりました。企画としては５人の男性アイドルグループから毎週２人ずつを組ませて出演してもらうバラエティ番組です。企画制作にあたり、事務所の先輩と以下のような会話がされました。
あなた「毎週５人の中から２人ずつ出演してもらいたいと思いますがどうでしょうか」
先輩「企画としては面白そう！だけど、このグループってファンの間でもメンバー毎の好き嫌いがハッキリしてるのは知ってるよな？」
あなた「そうですよね…。組ませるメンバーによってはファンから不評を買って再生数が伸びないかもしれませんね…」
先輩「参考になるか分からないけど、ファン２０人にアンケートを取ってるよ。メンバーそれぞれの好感度を３段階評価で調査してるよ」
あなた「なるほど！これを使えばある程度ファンにとって好きなメンバーを組ませることができますね」
先輩「どうやって調べるの？」
| 伊藤 | 渡辺 | 山本 | 小林 | 加藤 | |
|---|---|---|---|---|---|
| A | 3 | 1 | 3 | 1 | 2 |
| B | 1 | 3 | 1 | 3 | 2 |
| C | 2 | 3 | 2 | 3 | 1 |
| D | 3 | 1 | 3 | 1 | 3 |
| E | 1 | 3 | 3 | 3 | 1 |
| F | 1 | 3 | 1 | 3 | 1 |
| G | 3 | 1 | 3 | 1 | 3 |
| H | 3 | 1 | 3 | 1 | 2 |
| I | 1 | 3 | 3 | 1 | 3 |
| J | 3 | 1 | 3 | 1 | 3 |
| K | 1 | 3 | 3 | 3 | 2 |
| L | 3 | 1 | 3 | 1 | 3 |
| M | 1 | 3 | 2 | 3 | 2 |
| N | 3 | 1 | 3 | 1 | 2 |
| O | 3 | 1 | 3 | 1 | 1 |
| P | 3 | 1 | 2 | 1 | 3 |
| Q | 1 | 3 | 2 | 3 | 3 |
| R | 3 | 1 | 3 | 1 | 3 |
| S | 3 | 1 | 3 | 1 | 3 |
| T | 1 | 3 | 3 | 1 | 3 |
Correlation を選択
スプレッドシートのXLMiner Analysis ToolPakを開いてCorrelationを選択して下さい。日本語で相関という意味です。
範囲指定をする
分析するデータ範囲を指定します。選択列は伊藤～加藤。選択行は一番下までです。 選択列でメンバーの名前が抜けてしまうと分析結果にメンバーの名前が表示されなくなるので分析ができなくなります。また範囲指定にアルファベットａ～ｔの列は含まないので注意です。 またこの時に、必ずLabels in First Rowにチェックを入れて下さい。
指定した範囲の先頭列に数字以外のデータを入れた場合は必ず先頭行をラベルとして使用にチェックを入れましょう。これはスプレッドシートに対して「先頭行のデータは分析対象のグループ名だよ」と伝えることになります。チェックを入れ忘れると上の画像のようなアラートが出ます。
結果が出ました！
おめでとうございます！分析結果が出力されました。これで相関ツールの操作は以上です。
データから分かること
操作はこれだけ！
この数字は何かな？
それでは今度は出力された結果から読み解けることを解説します。今回算出された結果は以下の通りでした。
表を見てみよう
| 伊藤 | 渡辺 | 山本 | 小林 | 加藤 | |
|---|---|---|---|---|---|
| 伊藤 | 1 | ||||
| 渡辺 | -0.98 | 1 | |||
| 山本 | 0.49 | -0.52 | 1 | ||
| 小林 | -0.77 | 0.811 | -0.66 | 1 | |
| 加藤 | 0.27 | -0.35 | 0.33 | -0.55 | 1 |
相関ツールは、相関係数を求めるためのツールです。相関係数とは２つのデータが存在するとき、その２つの関係性の強さを表す指標のことを指します。
もう少し分かりやすい例えとして、気温とアイスコーヒーの売上の例を挙げましょう。
夏は気温が高いのでアイスコーヒーの売上が上がります。逆に冬場は売上が落ちます。片方がプラスになると、もう片方もプラスになる。これを正の相関と言います。
ではホットコーヒーの売上はどうでしょう。むせ返るような暑い日に熱々のホットコーヒーを飲みたい方は多くありません。ということは気温が上がるほどホットコーヒーの売上は下がります。片方がプラスになると、もう片方はマイナスになる。これを負の相関といいます。
●正の相関
片方がプラスになると、もう片方もプラスになる。
●負の相関
片方がプラスになると、もう片方はマイナスになる。
相関係数が示す関係性の強さとは、この正の相関と負の相関がどのくらい強いかを数字の-1〜1で表現します。
相関係数が0よりも上、つまりプラスの値の時は正の相関。0よりも下、つまりマイナスの値を取る時は負の相関を指しています。
正の相関では1に近づくほど、負の相関では-1に近づくほど関係性が強いと言えます。0の時は無相関と言って、何の関係性も存在しません。
表の中でメンバーが交差しているセルの数字が、２人の好感度の相関係数なんだね
そういうこと！
表を読み解いてみよう
関係性の強さを-1と1の間の数値で説明することは分かりました。では一体、どのくらい数値が大きければ関係性は強いのでしょうか。実は数値の大きさによってある程度のランクが存在します。
無関係 ・・・0
弱い ・・・|～0.3|
やや強い ・・・|0.3～0.5|
強い ・・・|0.5～0.7|
とても強い・・・|0.7～1|
ランク数やランクを分ける数値は参考書・解説サイトでまちまちですが、おおむね上記の通りだとお考え下さい。|数値|と書いたのは絶対値を表します。簡単にいうと相関係数がマイナスでもプラスでも数値が大きければ関係性は強いということです。
それでは関係性の強さを色を塗って視覚化してみましょう！
| 伊藤 | 渡辺 | 山本 | 小林 | 加藤 | |
|---|---|---|---|---|---|
| 伊藤 | 1 | ||||
| 渡辺 | -0.98 | 1 | |||
| 山本 | 0.49 | -0.52 | 1 | ||
| 小林 | -0.77 | 0.811 | -0.66 | 1 | |
| 加藤 | 0.27 | -0.35 | 0.33 | -0.55 | 1 |
暖色が濃くなるほど正の相関。寒色が濃くなるほど負の相関に近づきます。ちなみに１も存在しますが、これは同一人物での相関になりますので分析の対象外です。この結果をもとに番組出演でファンの不評を買わない組み合わせは下記の通りです。
●相乗効果に期待！
渡辺＆小林
伊藤＆山本
●無難な組み合わせ
山本＆加藤
伊藤＆加藤
●できれば組まない方が…
渡辺＆加藤
渡辺＆山本
小林＆加藤
山本＆小林
●混ぜるな危険！
伊藤＆渡辺
伊藤＆小林
という結果になりました。好感度のアンケートだけでは見えづらかったメンバー同士の相関関係の強さも、こうして分析ツールを通して見てみると一目瞭然ですね。
まとめ
相関ツールに関する説明はこれで以上です。今回はアイドルを例に解説をしましたが、相関ツールは普段のお仕事の現場でも活躍できるとても便利なツールです。資料作成はもちろん、マーケティングの手段として活用するなど用途は様々です。
ちなみに今回は統計学を学んだことのない方に向けてとてもシンプルな説明をしました。なので共分散や標準偏差といった統計学の基本的な概念は全てすっぽ抜けています(なくても問題はないのですが…)。つまるところ今回の説明だけでは相関の説明は統計学の観点から見ると不完全であることは忘れないで下さい。
「相関係数ってどうやって出したの？」
「もっと色んな分析方法が知りたい！」
と思った方。本格的に統計学を勉強したい方に向けて、初学者が最初に目を通すべき書籍を紹介していますので併せてお読み下さい。
お読みいただきありがとうございました！
ありがとうございました！