Excelで相関分析する方法を実践形式でわかりやすく解説してみたよ♪

本noteの想定読者と目的

本noteの読者は、日本企業の現場で働くビジネスパーソンで、ある程度Excelを触ったことのある方々を想定しています♪

目的は、Excelで相関分析する方法を実践することで、普段の仕事で統計分析を使い、業務効率や生産性を上げていただくことです♪

使用するサンプルExcelファイル

本noteで使用するデータソースは、映画、テレビ番組、ビデオ ゲームなどに関する情報が含まれているオンラインデータベースであるインターネット映画データベースです。
こちらのExcelファイルをダウンロードし、手元のExcelで以下の通りに操作してみてください♪

映画俳優データ.xlsx

13630 Bytes

ファイルダウンロードについて

ダウンロード

①データを確認しよう！

Excelファイルをダウンロードしたら、早速ファイルを開き、データを確認しましょう♪

51行×5列のデータで、
・1列目：映画俳優名
・2列目：映画出演数
・3列目：総興行収入(億ドル)
・4列目：平均興行収入(億ドル)
・5列目：出演映画例
のデータが欠損値なしで含まれていることがわかります♪

②散布図を作成しよう！

相関分析の目的は、2つのデータの間に相関関係がどの程度あるのかを相関係数で評価することです。

まずはどのデータに相関関係がありそうかの検討をつけるために、散布図を作成してみましょう♪

・2列目：映画出演数
・3列目：総興行収入(億ドル)
の散布図を作成するために、セル範囲「B2:C51」を選択し、[挿入]タブ→[グラフ]メニュー→[散布図(X,Y)またはバブルチャートの挿入]→[散布図]を選択してください。

すると、このような散布図が表示されます♪

[グラフのデザイン]タブ→[グラフ要素を追加]メニューから軸ラベルを追加し、グラフタイトルと縦横の軸ラベルを整えます。

完成した散布図がこちらです♪
プロットされた点を眺めると、正の相関(横軸の値(x)が増加すると縦軸の値(y)も増加するという関係のこと)がありそうだな、という仮説を持つことができますね。
相関分析を行うときは、散布図を見て自分なりの仮説を持つことが非常に大切です。なぜなら、実際の業務では、膨大なデータの中から相関関係のある2つのデータを見つけなくてはならないからです。効率的に相関係数を算出するために、相関関係がない2つのデータをあらかじめ特定しておき、相関係数を算出するデータの組み合わせを絞り込みます。

[グラフのデザイン]タブ→[グラフ要素を追加]メニューから[近似曲線]→[線形予測]を選択すると、右肩上がりの近似曲線が描かれます♪

上記と同じ流れで、
・3列目：総興行収入(億ドル)
・4列目：平均興行収入(億ドル)
の散布図と
・2列目：映画出演数
・4列目：平均興行収入(億ドル)
の散布図を作成してみましょう♪

ちなみに、
・2列目：映画出演数
・4列目：平均興行収入(億ドル)
の散布図は、シートをコピーして、別シートを作成し、「2列目：映画出演数」の列を3行目に入れ替えてから作成すると、効率的に作成できます♪

・3列目：総興行収入(億ドル)
・4列目：平均興行収入(億ドル)
の散布図がこちらです。あまり相関がなさそうなのと、1番上のデータが明らかに外れ値であることがわかりますね♪

・2列目：映画出演数
・4列目：平均興行収入(億ドル)
の散布図がこちらです。明らかに負の相関があることがわかります。また、1番左のデータが明らかに外れ値であり、このデータを取り除くことでより正確な相関係数を算出することができそうです♪

③相関係数を算出してみよう！

まず正の相関がありそうな
・2列目：映画出演数
・3列目：総興行収入(億ドル)
の相関係数を算出してみましょう！

相関係数を算出する際は、セルに関数「=CORREL(配列1,配列2)」を記入します♪

相関係数は、「0.4746088」と算出されました♪

相関係数は、正と負の方向と -1 から 1  までの数値によって、2つのデータの関係性を表します。あくまで目安ですが、以下のように捉えることができます。

・0～0.3未満：相関関係なし
・0.3～0.5未満：非常に弱い相関関係
・0.5～0.7未満：相関関係がある
・0.7～0.9未満：強い相関関係
・0.9以上：非常に強い相関関係

・2列目：映画出演数
・3列目：総興行収入(億ドル)
のデータについては、非常に弱い正の相関関係 or 正の相関関係がある、と捉えることができます♪

続いて、
・3列目：総興行収入(億ドル)
・4列目：平均興行収入(億ドル)
の相関係数を算出すると、「0.01425046」と算出されました♪

仮説通り、相関関係なし、と捉えて問題ありません。

最後に、
・2列目：映画出演数
・4列目：平均興行収入(億ドル)
の相関係数を算出すると、「-0.6273455」と算出されました♪

仮説通り、負の相関関係がある、と捉えることができますね！

④外れ値を除外して相関係数を算出してみよう！

さて、③までできたら実務への応用はすでにできている状態になっているはずです♪

④では、知っておくとさらに実務への応用が利く方法をご紹介します。それが外れ値を除外して相関係数を算出する方法です。

外れ値は、ある特殊な状況のデータである可能性が非常に高いため、除外して相関係数を算出しないと、適切な分析ができないと言えます。

例えば、
・2列目：映画出演数
・3列目：総興行収入(億ドル)
の散布図をあらためて眺めると、下図の赤丸のデータは、映画出演数が極端に少ない＆極端に多いデータであり、外れ値を捉えることができます。

では、この外れ値が何のデータなのかを特定しましょう♪
まず[グラフのデザイン]タブ→[グラフ要素を追加]メニューから[データラベル]→[上]を選択します。

すると、散布図の点の上にデータラベルが表示されます。デフォルトで総興行収入(億ドル)がデータラベルに設定されているので、映画俳優名に変更します。

[書式]タブ→[グラフエリア]プルダウンから[系列1 データラベル]を選択します。

[書式]タブ→[書式ウィンドウ]メニューを選択し、ラベルオプションを表示させます。デフォルトで[ラベルの内容]の「Y値(Y)」にチェックが入っていますが、映画俳優名に変更します。

[ラベルの内容]の「セルの値」にチェックを入れると、[データラベル範囲の選択]というポップアップが表示されます。

表示されたポップアップの入力欄に映画俳優名のセルの範囲「A2:A51」を指定します。

[OK]を選択したら、散布図の点の上に映画俳優名が表示されます♪

「Y値(Y)」は不要なので、[ラベルの内容]の「Y値(Y)」のチェックを外します。

これで外れ値がどの映画俳優のデータなのかを特定することができました。
「アンソニー・ダニエルズ」と「ロバート・デ・ニーロ」です♪

では、「アンソニー・ダニエルズ」と「ロバート・デ・ニーロ」の行を削除した上で、散布図を作成してみましょう！
心なしか、近似曲線の傾きが少し右肩上がりになったような気がします♪

実際に相関係数を算出すると「0.56182973」でした♪
外れ値を取り除く前の相関係数が「0.4746088」だったので、約0.1ほど相関係数が上がりました。

さて、負の相関が見られた
・2列目：映画出演数
・4列目：平均興行収入(億ドル)
の散布図についても、下図の赤丸である外れ値を特定します。

同様の流れでデータラベルを付与すると、同じく「アンソニー・ダニエルズ」と「ロバート・デ・ニーロ」が外れ値であることがわかりました♪

「アンソニー・ダニエルズ」と「ロバート・デ・ニーロ」の行を削除した上で、散布図を作成してみました！
近似曲線の傾きがかなり右肩下がりになりましたね♪

相関係数も「-0.7757078」と、外れ値を除外する前の相関係数「-0.6273455」よりも 約-0.15 となり、強い相関関係があることがわかりました♪

おまけ：データの背景を紐解く

スター・ウォーズ好きな方はご存知だと思いますが、「アンソニー・ダニエルズ」は、スター・ウォーズのC-3PO役をしている映画俳優です♪
出演した映画本数は少ないですが、映画1本あたりの平均が高い俳優であることで有名なのです。
確かにC-3PO役ができる俳優さんって、「アンソニー・ダニエルズ」しかいませんよね^^笑

「ロバート・デ・ニーロ」はみなさんご存知である偉大な俳優であり、出演映画本数は最も多いですが、映画1本あたりの平均収入が最も低い俳優としても有名です。
俳優が出演する映画本数が多いほど、スタイルやジャンル、興行的な呼び物に関して、それらの映画の変動幅が大きくなる傾向にあります。
たとえば、ロバート・デ・ニーロは、高収入のアクション映画やコメディーに出演している一方で、優れているが、観客動員数の少ない小規模な映画にも多数出演しているため、映画1本当たりの平均収入が比較的低くなっています。

終わりに

Excelを使った実践形式の相関分析はいかがでしたでしょうか？

実際のビジネス現場では、今回のデータ数よりも遥かに大量のデータを扱いますが、今回学んでいただいたことを十分に活用していただける範囲のデータ量かと思います。（Excelで分析できないデータ量になると、SQLやPythonを使わないといけませんが、現場にいるビジネスパーソンはExcelによる統計分析ができれば、十分にデータ分析ができます♪）

また、最後のおまけに書いたような「データの背景を読み解く」ことで、さらにデータへの理解を深めることもおすすめです♪

ぜひビジネス現場におけるデータドリブンな業務効率化を実現し、日本企業のDXを推進していってください！！