ヒガサラblog

サラリーマン向けに、仕事を効率良く進めていくための方法についてご紹介しています。プログラミングから対人スキルまでを幅広く掲載中。

ヒガサラblog

【エクセル】散布図の相関係数を一発算出!VBAへの組み込みまで!

f:id:yshgs_elec:20201003213311j:plain

データ分析などを行っていると、データ群の相関性を確認するということはよくあると思います。

 

そんな時、エクセルを使って

①散布図を作成

②近似曲線を追加

③R^2の相関係数を追加

という面倒な作業をしている人は多いのではないでしょうか?

 

この記事では、そんな面倒な作業をしないで、いきなり③の相関係数を算出する方法をご紹介します。

エクセルシート上での算出方法から、VBA上での算出方法まで、実際のエクセル画像も使いながら説しています。

エクセル初心者でも、わかりやすい内容になっているはずです。

ぜひ最後までごらんください。

 

さっそくやっていきます!

 

相関係数の算出方法

まずはじめに散布図の相関係数R^2の算出方法についてご紹介します。

相関係数R^2を算出するには以下の数式をセルに入力するだけです。

=RSQ (【y軸データ範囲】, 【x軸データ範囲】)

 

たったのこれだけ。

非常に簡単ですね。

 

エクセルでの使用例

次は実際の使用例を画像付きで解説していきます。

今回は以下のようなデータ群の相関係数を求めてみましょう。

f:id:yshgs_elec:20201003200246j:plain

このように、散布図を使って出力したR2=0.9793という値を散布図なしで求めていきます。

 

それでは先ほど紹介した数式を打ち込んでみましょう。

今回はB9セルに入力しています。

f:id:yshgs_elec:20201003200548j:plain

 

以下が実際の算出結果です。

f:id:yshgs_elec:20201003200817j:plain

このように右側の散布図に記載されている値と全く同じ値が算出されていることがわかると思います。

 

単純な相関性のみを確認したい場合は、この方法の方が圧倒的に簡単ですよね。

 

VBAへの組み込み方法

データ分析などを行う際に、VBAを用いて自動でデータファイルを読み込み、相関性を確認する、という作業を行っている人もいると思います。

というわけで次は、今回紹介している相関係数をVBA上で算出する方法についてご紹介していきます。

 

VBAへの組み込み方法は以下のとおりです。

=WorksheetFunction.RSq(【y軸データ範囲】, 【x軸データ範囲】)

 

 とはいえ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

 

 データ範囲の指定について詳細を勉強したい方は以下の記事を参考にしてください。

www.higashisalary.com

 

以下がこのコードの実行結果です。

先ほどと全く同じ数値が記載されいることがわかります。

f:id:yshgs_elec:20201003200735j:plain

 

VBAへの組み込みに関してもたったの一行で完了します。

ぜひこのスキルをマスターし、データ分析に活用していきましょう。

 

※今回の相関係数の他にも、散布図から算出できる切片や傾きについても以下の記事で解説しておりまうので、興味があればぜひご覧ください。

www.higashisalary.com

 

終わりに

というわけで、今回はデータ分析時などに役に立つデータの相関係数を算出する方法についてご紹介しました。

 

このように、このブログでは、エクセル(VBA)を中心に、様々な業務効率化スキルを紹介しています。

■業務の生産性を上げたい。

■早く帰って子供と遊びたい。

こんな人はぜひ他の記事も読んでみてください。

 

また、VBAを使ってこんなことがしたいんだけど、やり方がわからない、という悩みを抱えているかたは、お気軽にコメント欄から質問してください。

私にわかる範囲であればご協力いたしますので。(もちろん無料です。)

 

最後に・・・

このブログを応援してあげても良いよ、という方は以下のボタンをポチっていただけると嬉しいです。

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

にほんブログ村 IT技術ブログへ
にほんブログ村

 

Twitterもやっていますので、興味があれば覗いてみてください。

それではまた!!

 

 オススメ記事

www.higashisalary.com

www.higashisalary.com