【Excel】相関、相関係数とは?COOREL関数、PEASON関数、分析ツールで算出しよう【演習問題】

【Excel】相関、相関係数とは?COOREL関数、PEASON関数、分析ツールで算出しよう【演習問題】

 

科学データを解析する際に、まずある結果(や要因)とある結果(や要因)の間に関係があるのかどうかを解析する場合が良くあります。

 

このある結果とある結果の関係のことを相関と呼び、データの統計的解析を行う際の基本となり、こちらのページでは相関に関する以下の内容を解説しています。

 

・相関とは何か?相関関係を表したグラフとは?

 

・相関係数とは?式は?

 

・Excel関数(CORREL関数、PEASON関数)を使用して相関係数を算出してみよう

 

・Excel分析ツールを用いて相関係数を求めてみよう

 

・Excel関数、分析ツールを使用せずに相関係数を求めてみよう

 

というテーマで解説しています。

 

(※相関分析と同じくらい重要な統計的な分析法として回帰分析と呼ばれるものがあり、各種回帰分析に関しましては以下の関連記事にて解説しています。)

 

 

相関とは何か?相関関係を表したグラフとは?

ある二つの結果を表したデータ(もしくは二つの要因を表したデータ)があり、二つの変量間の関係のことを相関と呼びます

 

そして、この相関を分析する方法のことを相関分析と呼びます。

 

簡単に解説しますと、片方のデータが大きくなるにつれ、もう片方のデータも大きくなる場合、正の相関があると言います。

 

逆に片方が大きくなるにつれ、もう片方のパラメータが小さくなる場合は、負の相関があるといいます。

 

そして、相関関係の度合を表す量に相関係数というものがあります。

 

散布図を描くことで直感的に相関関係の有無を判断することも出来ます。

 

相関関係を表した散布図は一度は目にしたことがあるのではないでしょうか?

 

以下ののようなもののことを指します。まずは正の相関があるグラフで後程解説する相関係数が0.995と1にかなり近いため、非常に相関が強いといえます。

 

 

 

また、負の相関を表したグラフは以下のようになります。こちらも相関係数が0.967程度と非常に大きい値となっており、非常に相関が強いといえます。

 

 

 

逆に、相関関係がほとんどない場合は以下のようになります。こちらでは相関係数が0.087と0に近く、ほとんど相関がないと判断できます。

 

 

 

それでは、次に相関係数とは何かを考えていきましょう。

 

関連記事

 

Forecast関数とは?使用方法は?単回帰分析の方法
Forecast関数による単回帰分析の使用例(電池における直流抵抗(DCR)の算出)
二元配置の分散分析とは?
多重比較法とは?
定量的なデータの重回帰分析をExcel分析ツールを用いて行う方法
質的データの重回帰分析をExcel分析ツールを用いて行う方法 数量化Ⅰ類とは?

 

 

相関係数とは?式は?

 

相関係数の定義は共分散を各変量の標準偏差(分散のルート)で割ったものであり式にすると以下の通りです。

 

 

ここで共分散と分散、標準偏差について簡単に解説します。

 

共分散とは以下の式のように、i番目のxの値からxの平均値を引いたものと、i番目のyの値からyの平均値を引いたものを掛け合わせたものを足し、最後にデータ数nでわったもののことを指します(不偏推定量の場合はn-1で割る。)

 

共分散の演習問題等詳細は別ページで記載していますので、参考にしてみてください。

 

 

 

また、標準偏差は以下の通りであり(念のため分散の式も記載しますね)、相関係数の分母は各標準偏差の和を掛け合わせたものになります。

 

分散の式は以下の通りです。

 

 

 

標準偏差の式は以下の通りです。

 

 

 

すると、相関係数の分母はxとyの標準偏差の和の掛け合わせであるため、以下の式に変形されます。

 

共分散の式と異なるのはΣがxとy各々の頭についていることです

 

 

 

よって、相関係数の式をxとyを用いて表しますと、以下のようになります。

 

 

 

 

後程具体的な演習問題を記載していますので、理解を深めるためにも解いてみましょう。

 

 

この相関係数は値-1≦r≦1をとります。

 

r=1に近づくと正の相関があり、r=-1に近づくほど負の相関があり、0に近づくほど相関がないといえます。

 

ただし、2点のみのデータでプロットしたとしても相関係数は1になりますが、相関があるかどうかはデータが少なすぎて、わからない場合がありますので、きちんと散布図にして相関関係があるかどうかの判断を確認することが重要です。

 

相関関係の有無を判定する方法としましては無相関の検定と呼ばれるものもありますが、一般的には散布図と相関係数から判断して問題ないと言えるでしょう。

 

関連記事

 

Forecast関数とは?使用方法は?単回帰分析の方法
Forecast関数による単回帰分析の使用例(電池における直流抵抗(DCR)の算出)
二元配置の分散分析とは?
多重比較法とは?
定量的なデータの重回帰分析をExcel分析ツールを用いて行う方法
質的データの重回帰分析をExcel分析ツールを用いて行う方法 数量化Ⅰ類とは?

 

 

Excel関数(CORREL関数、PEASON関数)を使用して相関係数を算出してみよう

上の定義式を自動で計算してくれるExcel関数があり、これを用いて相関係数を算出してみましょう。

 

CORREL関数とPEARSON関数と呼ばれる関数を使用します。

 

どちらも同じ処理を行います。

 

上述の正の相関の例にした際のデータをもとに相関係数を算出してみましょう。

 

=CORREL(xの範囲,yの範囲)と指定することで算出できます(もしくは=PEARSON)。

 

(ここでPEARSONはノンパラメトリック手法など統計学で有名なピアソンさんの名前をとっています)

 

 

グラフでは以下の通りであり、相関係数は0.995となります。

 

非常に便利ですので、覚えておきましょう。

 

 

同様にExcel分析ツールを使用しても算出することが出来ます。

 

関連記事

 

Forecast関数とは?使用方法は?単回帰分析の方法
Forecast関数による単回帰分析の使用例(電池における直流抵抗(DCR)の算出)
二元配置の分散分析とは?
多重比較法とは?
定量的なデータの重回帰分析をExcel分析ツールを用いて行う方法
質的データの重回帰分析をExcel分析ツールを用いて行う方法 数量化Ⅰ類とは?
ピアソンのχ2検定とは?

 

 

Excel分析ツールを用いて相関係数を求めてみよう

 

Excel関数と同様に分析ツールを用いて相関係数を求めることが出来ます。
 
 
 
上記のようなデータが存在するとします。(架空のデータです)

 

散歩図を作ってみますと相関があるかどうかが直感的にわかります。

 

 

 

分析ツールを起動後、下記のよう、入力範囲を選びます。(乗車距離がラベルに対応し、ここから入力範囲を選んだ場合は、ラベルにチェックを入れます)(今回は入れています)

 

すると以下のような結果が出力されます。

 


 
左下の項目が相関係数であり、r≒0.999に近い値となり、非常に強い正の相関があると判断されました。

 

関連記事

 

Forecast関数とは?使用方法は?単回帰分析の方法
Forecast関数による単回帰分析の使用例(電池における直流抵抗(DCR)の算出)
二元配置の分散分析とは?
多重比較法とは?
定量的なデータの重回帰分析をExcel分析ツールを用いて行う方法
質的データの重回帰分析をExcel分析ツールを用いて行う方法 数量化Ⅰ類とは?
ピアソンのχ2検定とは?

 

Excel関数、分析ツールを使用せずに相関係数を求めてみよう

(工事中)

 

 

また、当サイトのメインテーマであるリチウムイオン電池、電気化学関連の用語は以下でまとめています。

 

興味がある方は参考にしてみてください。

 

【Excel】相関、相関係数とは?COOREL関数、PEASON関数、分析ツールで算出しよう【演習問題】 関連ページ

Excelデータ分析ツール使用の前準備
度数分布表とヒストグラム
【Excel】平均とは?(算術平均と加重平均) AVERAGEIF関数で条件付き(~以上かつ以下、~以上かつ未満、不等号、日付の範囲指定)の平均値を算出してみよう
【Excel】平均とは?2 (幾何平均、移動平均)
【Excel】分散と標準偏差とは?基本統計量とは?Excel関数(VARP関数、STDEVP関数)で分散と標準偏差を計算してみよう
【Excel】変動係数(CV)とは?エクセルで計算してみよう!【演習問題】
標本と母集団
信頼区間の推定
信頼区間の推定をExcelを用いて行ってみよう!
【演習問題】信頼区間の推定を実際に行ってみよう CONFIDENSE.T関数とCONFIDENCE関数の違いは?【Excel】
二項分布
【Excel】正規分布とは?NORM.S.DIST関数で正規分布を描いてみよう【演習問題】
【Excel】ポアソン分布とは?POISSON関数、POISSON.DIST関数の使用方法【演習問題】
正規分布の応用技術(自動運転車におけるAI)
推測統計学とt分布
χ2(カイ二乗)分布とは
【Excel】正規分布における歪度と尖度をSKEW関数、KURT関数で計算してみよう【演習問題】
【Excel】RAND関数、RANDBETWEEN関数を用いて乱数を作ってみよう 正規分布に従う乱数発生方法は?【演習問題】
F分布
統計的検定の概要
2群の差の検定(t検定)と検定フロー
F検定(等分散かどうかの検定)
F検定(等分散かどうかの検定)を分析ツールを使用せずに行う方法
Excel関数(FINV、F.INV.RT関数)でF検定時の上側確率に対応するF値を算出する方法
Excel関数(TINV、T.INV.2RT関数)でt検定時の両側確率に対応するt値を算出する方法
等分散の時のt検定
等分散でない時のt検定(ウェルチの検定)
パラメトリック手法とノンパラメトリック手法の違い ノンパラの紹介
ピアソンのχ2検定(ノンパラメトリック手法) 適合度の検定、独立性の検定
マン・ホイットニーのU検定
【Excel】マクネマー検定とは?Excelを使用して演習問題を解いてみよう!
【Excel】Wilcoxonの符号付順位和検定とは?Excelを使用して演習問題を解いてみよう!
【Excel】共分散とは?COVAR関数,COVARIANCE.P関数を使用して共分散を求めてみよう COVARIANCE.P関数と.S関数の違いは?【演習問題】
【Excel】階乗の計算方法 FACT関数で階乗を求めてみよう【演習問題】
参考文献
【Excel】関数を使わずにデータを間引く方法
【Excel】関数を使用してデータを間引く方法(INDIRECT関数)
【Excel】関数を使わずにn行ずつ空欄を追加する方法
【Excel】数字の間にハイフンを一括して入れる方法
【Excel】-(ハイフン)を入力した郵便番号の-を消す、再度つける方法
【Excel】Forecast関数で直線補間してみよう!Trend関数との違い
【Excel】SUMPRODUCT関数で積の合計を計算しよう!SUM関数との違い
【Excel】SUMIFS関数で複数条件の和の計算を行ってみよう!~以上かつ以下、~以上かつ未満、不等号、日付の範囲指定【演習問題】
【Excel】°(度)とrad(ラジアン)の変換方法【計算の考え方】
【Excel】勾配の計算方法 Excelを用いて勾配を計算してみよう
【Excel】sin曲線(サインカーブ)・cos曲線(コサインカーブ)を書く方法
【Excel】RMS(Root Mean Square:二乗平均平方根)と標準偏差の違いは?RMSの計算問題を解いてみよう【演習問題】
【Excel】工程能力指数のCP,CPK,PPKとは?CPKから不良率を算出する方法は?計算問題を解いてみよう【演習問題】
【Excel】Vlookup関数の使用方法
【Excel】STDEV関数(STDEV.S関数)とSTDEVP関数の違い
【Excel】VAR関数(VAR.S関数)とVARP関数の違い
【Excel】5ずつ切り上げる方法 1-5を5、6-10を10とする方法
【Excel】2つのif関数でデータを3種類に分類する方法 (A以上B以下)
【Excel】条件に合うデータの数量の数え上げ Countif,Countifs関数
【Excel】Excelソルバーで最適化問題を解こう!生産計画,線形計画問題【演習問題】
【Excel】Excelソルバーで最適化問題を解こう!二次関数の問題【演習問題】
【Excel】非表示列や行の再表示を一括で行う方法
【Excel】文の先頭に同じ文字列を一括で追加する方法【一括で追加】
対数の変換における2.303とは?常用対数と自然対数の変換
両側検定と片側検定の違い
分散分析 対応の無い場合の一元配置の分散分析をExcelで行ってみよう
分散分析 対応の有る場合の一元配置の分散分析をExcelで行ってみよう
分散分析 繰り返しの無い、有る場合の二元配置の分散分析をExcelで行ってみよう
多重比較法とは?分散分析との違い Tukeyの方法
数量化I類とは?Excelを用いて定性的なデータ(質的データ)の重回帰分析を行ってみよう
重回帰分析とは?Excel分析ツールで定量データの重回帰分析を行ってみよう!【リチウムイオン電池のデータ解析】
【Excel】最小二乗法とは?INTERCEPT関数とSLOPE関数の使用方法【単回帰分析、重回帰分析】
【Excelまとめ】Excel関数、分析ツールで統計解析を行おう
品質工学におけるFTA(故障の木解析)とは何か?FTAの原理とやり方
品質工学におけるFMEA(故障モード影響解析)とは何か?FMEAの原理とやり方
多変量解析 主成分分析と因子分析とは?違いは?
実験計画法
【Excel】Excelで対数関数の計算・グラフを書いてみよう!
【Excel】アークサインsin-1・アークコサインcos-1・アークタンジェントtan-1の計算方法【Excel】
材料証明書とミルシートの違い
【Excel】折れ線グラフで途中から点線にする方法
【Excel】折れ線グラフで途中の一部を消す方法
【Excel】折れ線グラフで途中から色を変える方法【色分け】
【Excel】数値の末尾に0を表示させ有効数字をそろえる方法
【Excel】円グラフで並び替えを行う方法【大きい順・小さい順】
【Excel】RANK関数を使用して数値の抽出・順位づけを行ってみよう
【Excel】エクセルを使用して割合や構成比を求める方法
【Excel】エクセルで文字を丸囲みする方法
【Excel】エクセルで必要ないスペースを一括で削除する方法
【Excel】エクセルで上付き文字と下付き文字を同時で行う方法
【Excel】エクセルでレ点(チェックマーク)を入力・編集・削除する方法
【Excel】「どっちが安いか」を計算する方法【値段の比較】
【Excel】前年比・伸び率・増減率(増加率・減少率)を計算する方法
【Excel】工場や施設の稼働率の定義と計算方法【演習問題】
【Excel】比率の計算方法(x:yに分ける方法)
【Excel】倍率の計算方法【入試の倍率・競争倍率】
【Excel】エクセルにおいて可視セルのみをコピーする方法
【Excel】途中で切れている折れ線グラフをつなげる方法【データ要素を線で結ぶ】
【Excel】利益と粗利の違い 利益率(粗利率)を計算する方法
【Excel】エクセルでセルに色を付けると枠線が消えるときの対処方法

HOME プロフィール お問い合わせ