データ分析などを行っていると、データ群の相関性を確認するということはよくあると思います。
そんな時、エクセルを使って
①散布図を作成
②近似曲線を追加
③R^2の相関係数を追加
という面倒な作業をしている人は多いのではないでしょうか？
この記事では、そんな面倒な作業をしないで、いきなり③の相関係数を算出する方法をご紹介します。
エクセルシート上での算出方法から、VBA上での算出方法まで、実際のエクセル画像も使いながら解説しています。
エクセル初心者でも、わかりやすい内容になっているはずです。
ぜひ最後までごらんください。
さっそくやっていきます！
相関係数の算出方法
まずはじめに散布図の相関係数R^2の算出方法についてご紹介します。
相関係数R^2を算出するには以下の数式をセルに入力するだけです。
たったのこれだけ。
非常に簡単ですね。
エクセルでの使用例
次は実際の使用例を画像付きで解説していきます。
今回は以下のようなデータ群の相関係数を求めてみましょう。
このように、散布図を使って出力したR2=0.9793という値を散布図なしで求めていきます。
それでは先ほど紹介した数式を打ち込んでみましょう。
今回はB9セルに入力しています。
以下が実際の算出結果です。
このように右側の散布図に記載されている値と全く同じ値が算出されていることがわかると思います。
単純な相関性のみを確認したい場合は、この方法の方が圧倒的に簡単ですよね。
VBAへの組み込み方法
データ分析などを行う際に、VBAを用いて自動でデータファイルを読み込み、相関性を確認する、という作業を行っている人もいると思います。
というわけで次は、今回紹介している相関係数をVBA上で算出する方法についてご紹介していきます。
VBAへの組み込み方法は以下のとおりです。
とはいえVBAにあまり詳しくない人はイメージがつきにくいと思いますので、実際のコードについてもご紹介しておきましょう。
ここでは先ほどと全く同じ状態を再現するVBAコードを記述しています。
Sub Macro1()
Cells(9, 2) = WorksheetFunction.RSq _
(Range(Cells(2, 1), Cells(8, 1)), Range(Cells(2, 2), Cells(8, 2)))
End Sub
データ範囲の指定について詳細を勉強したい方は以下の記事を参考にしてください。
以下がこのコードの実行結果です。
先ほどと全く同じ数値が記載されいることがわかります。
VBAへの組み込みに関してもたったの一行で完了します。
ぜひこのスキルをマスターし、データ分析に活用していきましょう。
※今回の相関係数の他にも、散布図から算出できる切片や傾きについても以下の記事で解説しておりまうので、興味があればぜひご覧ください。
おわりに
というわけで、今回はデータ分析時などに役に立つデータの相関係数を算出する方法についてご紹介しました。
このように、このブログでは、エクセル（VBA)を中心に、様々な業務効率化スキルを紹介しています。
■業務の生産性を上げたい。
■早く帰って子供と遊びたい。
こんな人はぜひ他の記事も読んでみてください。
また、VBAを使ってこんなことがしたいんだけど、やり方がわからない、という悩みを抱えているかたは、お気軽にコメント欄から質問してください。
私にわかる範囲であればご協力いたしますので。（もちろん無料です。）
最後に・・・
このブログを応援してあげても良いよ、という方は以下のボタンをポチっていただけると嬉しいです。
Twitterもやっていますので、興味があれば覗いてみてください。
それではまた！！
コメント