ビジネス統計スペシャリスト対策！Excelで共分散を求める操作方法
●Excelで共分散を求める方法
●データの読み取り方
はじめに
ご訪問ありがとうございます！まちゅけんです
ヨメちゃんです
今回はビジネス統計スペシャリストにも役立つExcelのデータ分析ツールを使って共分散を求める方法についてご紹介します。
実のところ共分散を求める問題はこの資格に登場しません。しかし敢えて紹介するからには理由があります。その理由は二つ。
１：相関関係の理解に繋がる
２：統計検定では理解必須
１：相関関係の理解に繋がる
二つのデータ群の関係性を相関といいます。そして相関の強さを数値で表したものが共分散であり、相関係数です。用語の意味は後ほど説明しますが、相関係数を求めるには共分散の算出が必要です。
確かにExcelのデータ分析ツールでは共分散をすっ飛ばして相関係数を算出できるのですが、それだと共分散と相関係数の違いも含めて「相関を理解した」とは言いづらいですよね。
２：統計検定では理解必須
もし統計検定の受験を視野に入れている場合は確実に共分散を理解しなければいけません。
Excelで簡単に計算できる相関係数ですが、共分散と二つのデータ群それぞれの分散だけを提示されて相関係数を求める問題が統計検定3級から出題されたりします。ということは、共分散とは何なのかを理解しておかなければいけません。
共分散の大切さが分かった所で、データ分析ツールを使った共分散の算出方法を解説します。
「統計検定３級ってなに？」という方は下記の記事を
https://machuken.com/post-241/「ビジネス統計スペシャリストってなに？」という方は下記の記事を参考にして下さい。
操作方法
共分散の算出方法を勉強するにあたり、今回は下記の例題を使用します。
あなたはとある中学１年生のクラスの担任です。そして学年テストを終えたばかりです。ある日、同僚とこのような会話をしました。
担任「この前のテストどうだった？」
同僚「みんな頑張ってたよ。あまり勉強してない子もいるけどね…」
担任「勉強してる子は総じて点数がいいし、その逆もあるよね」
同僚「たしかに！勉強できない子は、まず１科目でいいから頑張ってほしいな。そしたら他のテストも自然と点数って上がると思うけど。親御さんにも説明したいんだけど、それっていい感じに数値で出せたりするのかな？」
| 国語 | 数学 | |
|---|---|---|
| A | 70 | 160 |
| B | 80 | 170 |
| C | 50 | 110 |
| D | 40 | 90 |
| E | 75 | 160 |
| F | 90 | 190 |
| G | 40 | 70 |
| H | 80 | 180 |
| I | 40 | 70 |
| J | 50 | 100 |
共分散とは、二つのデータ群の関係性を表す数値です。「ある一方の数値が上がる時、もう片方の数値は上がる（下がる）傾向にある」という説明を数値で行うことができます。今回のケースでは「国語の点数が高い生徒は数学の点数も高い傾向にある」ということを共分散を使って数値化したいと思います。
共分散の算出はExcel関数のCOVARIANCE.Pを使用して算出することも可能ですが、今回はデータ分析ツールを使用します。Excelのデータ分析ツールは初期設定では使うことができません。設定方法については下記の記事を参照して下さい。
共分散ツールを選択
まずはExcelのデータ分析ツールを開いて共分散ツールを選択します。
範囲指定をする
次に範囲指定をします。選択列は国語～数学、選択行は数字の最下部までを選択します。上画像で表示されている表はすべて選択範囲です。選択する際の注意点として、必ず先頭行をラベルとして使用にチェックを入れて下さい。
指定した範囲の先頭列に数字以外のデータを入れた場合は必ず先頭行をラベルとして使用にチェックを入れましょう。これはExcelに対して「先頭行のデータは分析対象のグループ名だよ」と伝えることになります。チェックを入れ忘れると上の画像のようなアラートが出ます。
結果が出ました！
共分散を算出することができました！
簡単だよね
それぞれの数値の意味について知りたいな
データから分かること
| 国語 | 数学 | |
|---|---|---|
| 国語 | 340.25 | |
| 数学 | 805 | 1960 |
まずはそれぞれの数値が意味するのは下記の通りです。
●340.25(国語×国語)
クラスの国語の点数の分散
●1960(数学×数学)
クラスの数学の点数の分散
●805(国語×数学)
クラスの国語と数学の点数の共分散
分散とはクラス内でテストの点数にどれくらいバラつきがあるのかを知ることができる数値です。
例えば国語の分散を調べるには、クラスの国語の平均点と、各生徒の点数の差をそれぞれ二乗して合算。最後にクラスの生徒数で割った数のことをいいます。分散だけを使うことはあまりなく、分散の平方根を取った標準偏差という値を指標として用いる場合がほとんどです。
しかもクラス平均と各生徒との点数の差を二乗しているため単位が～点でありません。したがって平方根を取ることで単位を点にもどして指標を分かりやすくします。なので国語の標準偏差は340.25の平方根、18点(四捨五入)となります。こうすることで「このクラスの国語の点数は、どの生徒も平均点から前後18点以内の点数である」ということが分かります。
●分散
１：（クラスの国語の平均点 ー 生徒の平均点）を二乗
２：１の計算をクラス全員分する
３：２で求めた値を合計してクラスの人数で割る
●標準偏差
１：分散の平方根を取る
※ExcelではSQRT関数を使用すると簡単に求めることができます。
例 =SQRT(340.25)
共分散が示す意味もとても簡単です。共分散の値が0より大きいほど、国語の点数が上がれば数学の点数も上がる傾向(正の相関)にあります。逆に0より小さいほど、国語の点数が上がれば数学の点数も下がる傾向(負の相関)にあります。
先ほど求めた共分散の値は340.25でした。ということは正の値を取っているので、国語と数学の点数は正の相関にあることが分かります。つまり今回の分析結果から、「片方の点数が高い生徒は、もう片方の点数も高い傾向にある」と言うことができます。
●正の相関
片方がプラスになると、もう片方もプラスになる。
●負の相関
片方がプラスになると、もう片方はマイナスになる。
これで親御さんに、まずは一科目の勉強から頑張るように伝えることができるね
そうだね。でも実は共分散て万能じゃないんだ。そのあたりも説明するね。
共分散は万能ではない
二つのデータ群が存在するとき、データの関係性を示すために共分散は便利であることが分かりました。しかし共分散は決して万能とは言えません。それは主に２つの理由があるからです。
１：数値イメージがわかない
２：共分散同士の比較ができない
数値イメージがわかない
今回のケースだけを見れば「二つの関係性は共分散で340.25。つまり正の相関にあります！」が正解でした。しかし共分散の単位は点数ベースではありません。これだとちょっと親御さんへの説明には向かないですよね。
共分散同士の比較ができない
共分散で算出される値は比較をするデータの数値の大きさに依存します。今回のケースでは国語（100点満点）と数学（200点満点）の共分散を求めました。仮に国語（100点満点）と英語（100点満点）の共分散を求めた場合、点数のベースが異なります。なので共分散同士の比較というのは基本的にしません。
また今回は学習の理解を優先したのですが、他の角度からこの分析を見た場合に「単純に国語が得意な生徒は数学が得意」という見方も可能です。
こうした理由から共分散は万能ではありません。ここで新たにデータの関係性の比較をする指標として相関係数が登場します。相関係数ではベースの異なる共分散を同じ指標としてパーセンテージで表現することが可能なのです。しかも様々データの関係性の強さを一覧にして示すこともできるので比較をするには打ってつけです。
共分散からの発展内容として、相関係数の求め方の解説を別にご用意しました。詳しくは下記の記事を参考にして下さい。
まとめ
Excelの分析ツールを使用して共分散を求める方法を解説しました。既に解説しましたが共分散を単体で使うことはほとんどありません。どちらかというとその後に算出する相関係数の方が統計的な比較に利用することの方が多いかもしれません。とはいえ共分散の説明を省くと相関関係の理解が不足しますし、回帰分析の学習をする際の助けとなるかもしれません。共分散だけでトピックが立てられますので、重要な内容には変わりありません。
この記事が皆さまの統計学を理解する助けとなることを心より願っています。
ちなみに今回は Excelを使用する方法をご紹介しました。実はスプレッドシートでも全く同じことができますので、気になる方は下記の記事を参照して下さい。
もし共分散も含め、ベースから統計学の学びを深めたいという方には統計検定の受験がオススメです。以下に記事をまとめたのでご参考程度にお読み下さい、
お読みいただきありがとうございました！
ありがとうございました！