エクセル分析スペシャリスト対策！ Excelで相関係数を求める操作方法
はじめに
ご訪問ありがとうございます！まちゅけんです。
ヨメちゃんです。
１：Excelを使った相関ツールの操作
２：相関ツールの使い道
この記事ではエクセル分析スペシャリストの学習に役立つ、エクセルの使い方について解説をします。今回のテーマは相関編です。スプレッドシートを使用する方法は別でご紹介をしています。
https://machuken.com/post-892/「ビジネス統計スペシャリストってなに？」という方は以下の記事を参考にして下さい。
操作方法
Excelを使った相関ツールの操作方法について解説をします。前提として、Excelのデータ分析ツールは初期設定の状態では使うことができません。「初期設定のままだ！」という方は、データ分析ツールの有効化についてまとめた記事があるので参考にして下さい。
相関ツールの説明にあたり、以下の例題を使用します。
あなたはテレビ局のプロデューサーです。この度、新しいレギュラー番組を制作することになりました。５人の男性アイドルグループから毎週２人ずつを組ませて出演してもらうバラエティ番組です。出演交渉にあたり、アイドル事務所のマネージャーと以下のような会話がされました。
あなた「毎週５人の中から２人ずつ出演してもらいたいと思いますがどうでしょうか」
マネージャー「企画としては賛成ですが…実はうちのグループはファンの間でもメンバー毎の好き嫌いがハッキリしているんですよね」
あなた「なるほど。ということは組ませるメンバーによってはファンから不評を買うかもしれませんね」
マネージャー「ご参考までにファン２０人にアンケートを取りました。メンバーそれぞれの好感度を３段階評価で調査しています」
あなた「なるほど！これを使えばある程度ファンにとって好きなメンバーを組ませることができますね」
マネージャー「どうやって調べるんですか？」
| 田中 | 鈴木 | 髙橋 | 齋藤 | 中村 | |
|---|---|---|---|---|---|
| A | 1 | 3 | 3 | 3 | 1 |
| B | 3 | 1 | 1 | 1 | 3 |
| C | 1 | 3 | 3 | 3 | 1 |
| D | 2 | 2 | 1 | 1 | 3 |
| E | 1 | 3 | 3 | 2 | 1 |
| F | 3 | 2 | 3 | 1 | 1 |
| G | 2 | 2 | 1 | 1 | 2 |
| H | 1 | 3 | 3 | 2 | 1 |
| I | 3 | 1 | 1 | 1 | 3 |
| J | 3 | 1 | 1 | 2 | 3 |
| K | 1 | 3 | 1 | 3 | 1 |
| L | 3 | 1 | 3 | 2 | 3 |
| M | 1 | 3 | 2 | 2 | 1 |
| N | 1 | 3 | 1 | 3 | 1 |
| O | 3 | 1 | 2 | 1 | 3 |
| P | 3 | 1 | 1 | 1 | 3 |
| Q | 1 | 3 | 1 | 3 | 1 |
| R | 3 | 1 | 1 | 1 | 3 |
| S | 3 | 1 | 2 | 1 | 3 |
| T | 1 | 3 | 1 | 3 | 3 |
相関ツールを選択
Excelのデータ分析ツールを開いて相関を選択して下さい。
範囲指定をする
分析するデータ範囲を指定します。選択列は田中～中村、選択行は一番下までです。選択列でメンバーの名前が抜けてしまうと分析結果にメンバーの名前が表示されなくなるので分析ができなくなります。また範囲指定にアルファベットａ～ｔの列は含まないので注意です。またこの時に、必ず先頭行をラベルとして使用にチェックを入れて下さい。
指定した範囲の先頭列に数字以外のデータを入れた場合は必ず先頭行をラベルとして使用にチェックを入れましょう。これはExcelに対して「先頭行のデータは分析対象のグループ名だよ」と伝えることになります。チェックを入れ忘れると上の画像のようなアラートが出ます。
結果が出ました！
おめでとうございます！分析結果が出力されました。これで相関ツールの操作は以上です。
データから分かること
操作はこれだけ！
この数字は何かな？
それでは今度は出力された結果から読み解けることを解説します。今回算出された結果は以下の通りでした。
表を見てみよう
| 田中 | 鈴木 | 高橋 | 斎藤 | 中村 | |
|---|---|---|---|---|---|
| 田中 | 1 | ||||
| 鈴木 | -0.97 | 1 | |||
| 高橋 | -0.18 | 0.26 | 1 | ||
| 斎藤 | -0.80 | 0.77 | 0.15 | 1 | |
| 中村 | 0.76 | -0.84 | -0.45 | -0.59 | 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.97 | 1 | |||
| 高橋 | -0.18 | 0.26 | 1 | ||
| 斎藤 | -0.80 | 0.77 | 0.15 | 1 | |
| 中村 | 0.76 | -0.84 | -0.45 | -0.59 | 1 |
暖色が濃くなるほど正の相関。寒色が濃くなるほど負の相関に近づきます。ちなみに１も存在しますが、これは同一人物での相関になりますので分析の対象外です。この結果をもとに番組出演でファンの不評を買わない組み合わせは下記の通りです。
●相乗効果に期待！
田中＆中村
鈴木＆斎藤
●無難な組み合わせ
田中＆高橋
鈴木＆高橋
高橋＆斎藤
●できれば組まない方が…
高橋＆中村
斎藤＆中村
●混ぜるな危険！
田中＆鈴木
田中＆斎藤
鈴木＆中村
という結果になりました。好感度のアンケートだけでは見えづらかったメンバー同士の相関関係の強さも、こうして分析ツールを通して見てみると一目瞭然ですね。
まとめ
相関ツールに関する説明はこれで以上です。今回はアイドルを例に解説をしましたが、相関ツールは普段のお仕事の現場でも活躍できるとても便利なツールです。資料作成はもちろん、マーケティングの手段として活用するなど用途は様々です。
ちなみに今回は統計学を学んだことのない方に向けてとてもシンプルな説明をしました。なので共分散や標準偏差といった統計学の基本的な概念は全てすっぽ抜けています(なくても問題はないのですが…)。つまるところ今回の説明だけでは相関の説明は統計学の観点から見ると不完全であることは忘れないで下さい。
「相関係数ってどうやって出したの？」
「もっと色んな分析方法が知りたい！」
と思った方。本格的に統計学を勉強したい方に向けて、初学者が最初に目を通すべき書籍を紹介していますので併せてお読み下さい。
お読みいただきありがとうございました！
ありがとうございました！