【Excel】共分散とは?COVAR関数,COVARIANCE.P関数を使用して共分散を求めてみよう COVARIANCE.P関数と.S関数の違いは?【演習問題】

【Excel】共分散とは?COVAR関数,COVARIANCE.P関数を使用して共分散を求めてみよう【演習問題】

 

科学データを解析する際に、相関分析を回帰分析などを行う場合が良くあります。

 

これらの解析を行う際に共分散と呼ばれるパラメータを含んでいる場合があり、こちらのページでは共分散に関する以下のテーマで解説しています。

 

・共分散とは何か?共分散の定義は?

 

・Excel関数(COVAR関数、COVARIAN.P関数)を使用して相関係数を算出してみよう

 

・Excel分析ツールを用いて共分散を求めてみよう

 

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

 

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

 

 

共分散とは何か?共分散の定義は?

共分散とは、一言で説明しますとある二つのデータ(結果)における規格化されていない相関係数のようなものと言えます

 

つまり、共分散が大きい場合、片方のデータが大きくなるともう片方のデータが大きくなり、共分散が小さい場合、片方のデータが大きくなるともう片方のデータが小さくなり、0に近いと各々のデータの関連性が低いと言えます。

 

ただし、共分散ではデータの値により、その値が大きく変わり、例えば桁数が大きく異なるデータを使用するとその分桁数が大きい共分散が現れやすくなるため、データによって比較ができません。

 

そのために規格化する(-1〜1の範囲に収める)ことで各データを比較することが出来るようになり、上述のように規格化したものが相関係数です。

 

相関係数rにおいてはこの値が-1≦r≦1となり、1に近いほど正の相関があり-1に近いほど負の相関があり、0に近いほど関連性が低くなると、共分散と似ていることがわかります。

 

そして、共分散の定義式は以下の通りです。

 

ここでの共分散とは母集団、もしくはある標本データそのものの共分散の定義式であり、標本から母集団の共分散を推定する不偏共分散でないため気を付けましょう。

 

 

関連記事

 

Forecast関数とは?使用方法は?単回帰分析の方法
Forecast関数による単回帰分析の使用例(電池における直流抵抗(DCR)の算出)
二元配置の分散分析とは?
多重比較法とは?
定量的なデータの重回帰分析をExcel分析ツールを用いて行う方法
質的データの重回帰分析をExcel分析ツールを用いて行う方法 数量化T類とは?
相関とは?相関係数を求める方法は?CORREL関数、PEARSON関数
標本から母集団の関係 不偏推定量とは?

 

 

Excel関数(COVAR関数、COVARIENCE.P関数)を使用して共分散を算出してみよう

上の定義式を自動で計算してくれるExcel関数があり、これを用いて共分散(英語でcovarience)を算出してみましょう。

 

COVAR関数とCOVARIENCE.P関数と呼ばれる関数を使用します。

 

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

 

当サイトのメインテーマでもあるリチウムイオン電池の容量と質量の関係の表した以下のデータを得たとします。このデータの共分散を算出してみましょう。

 

=COVAR(xの範囲,yの範囲)と指定することで算出できます(もしくは=COVARIENCE.P関数)。

 

 

 

すると7332.5となり、0から大きいため共分散は大きいと考えられると判断できます。

 

判断できますと記載したのは使用するデータの値がさらに値が大きい場合、0から離れていた値をとっていたとしても、規格化した場合(相関係数で考えると)だと比較的0に近くなる場合があるからです。

 

なお、このデータの相関係数は0.995となるため、共分散も大きいと判断してよいです。

 

散布図と照らし合わせますと直感的に理解できます。

 

 

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

 

関連記事

 

Forecast関数とは?使用方法は?単回帰分析の方法
Forecast関数による単回帰分析の使用例(電池における直流抵抗(DCR)の算出)
二元配置の分散分析とは?
多重比較法とは?
定量的なデータの重回帰分析をExcel分析ツールを用いて行う方法
質的データの重回帰分析をExcel分析ツールを用いて行う方法 数量化T類とは?
ピアソンのχ2検定とは?
相関とは?相関係数を求める方法は?CORREL関数、PEARSON関数
標本から母集団の関係 不偏推定量とは?

 

COVARIENCE.P関数とCOVARIENCE.S関数の違いは?

STDEV関数とSTDEVP関数の違いでも解説させて頂きましたが、標本データそのものや母集団のデータそのものの標準偏差や共分散などの統計的データを算出する際に、データ数(自由度n)で割る項を含んでいました。

 

これに対して、標本から母集団を予測する場合(不偏推定量)はこの自由度をnではなく、n-1とします。

 

COVARIENCE.P関数とCOVARIENCE.S関数でもこの違いがあり、COVARIENCE.P関数やCOVAR関数では上述の共分散の式中にて自由度nで割っているのに対して、COVARIENCE.S関数ではn-1で置き換えたもので割っています

 

そのため、COVARIENCE.P関数の方が値としては大きくなります。

 

標本データや母集団のデータそのものの値の共分散を求めたいのか、標本データから母集団のデータの予想を行いたいのかで適宜使い分けましょう。

 

 

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

 

Excel関数と同様に分析ツールを用いて相関係数を求めることが出来ます。
 
Excelのデータタブ、データ分析を選択し、共分散を選びましょう。

 

(念のため上にて解説のデータを再度載せておきます)

 

 

関数では、各配列ごとに選択していましたが、分析ツールはデータ範囲をすべて選びます。

 

ここでラベルとは上のデータでいうと質量や容量の行のことであり、今回は範囲にいれていないため、先頭行をラベルとして使用のチェックを入れません。
 
 
 
すると結果は以下のようになります。

 

左下の部分が共分散であり、対角線上は各分散を表しています。

 

値が関数で算出した時と同じであることがわかります。

 

 

 

関連記事

 

Forecast関数とは?使用方法は?単回帰分析の方法
Forecast関数による単回帰分析の使用例(電池における直流抵抗(DCR)の算出)
二元配置の分散分析とは?
多重比較法とは?
定量的なデータの重回帰分析をExcel分析ツールを用いて行う方法
質的データの重回帰分析をExcel分析ツールを用いて行う方法 数量化T類とは?
ピアソンのχ2検定とは?
相関とは?相関係数を求める方法は?CORREL関数、PEARSON関数
標本から母集団の関係 不偏推定量とは?

 

 

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

 

関数、分析ツールを使用せずに共分散を求める方法について解説します。

 

定義式に従い、地道に算出していきましょう。

 

まずは、x(下では質量)、y(下では容量)の平均値を求めましょう。

 

各々を足してサンプル数10で割ります。

 

質量の平均は(100+250+500+ ・・・ + 2000) /10 =950です。

 

容量の平均は(3+7+12+ ・・・ + 50) / 10 = 23.5です。

 

そして各々の値から各平均を引いた値を求めましょう。

 

質量-質量の平均は各々、100-950= -850、250-950= -700・・・となり、容量-容量の平均は各々、3-23.5=-20.5、7-23.5=-16.5・・・となります。

 

そして、これらの積を求め足し合わせますと73325となり、サンプル数10であり、共分散7332.5が算出されました。

 

上と一致していますね。

 

 

関連記事

 

Forecast関数とは?使用方法は?単回帰分析の方法
Forecast関数による単回帰分析の使用例(電池における直流抵抗(DCR)の算出)
二元配置の分散分析とは?
多重比較法とは?
定量的なデータの重回帰分析をExcel分析ツールを用いて行う方法
質的データの重回帰分析をExcel分析ツールを用いて行う方法 数量化T類とは?
ピアソンのχ2検定とは?
相関とは?相関係数を求める方法は?CORREL関数、PEARSON関数
標本から母集団の関係 不偏推定量とは?

 

 


【統計処理代行】行っています


統計学や統計解析の本などは多々ありますが、実際にその統計解析が正しいのかどうかがわからないことがないでしょうか?


例えば、標準偏差を求めるときにSTDEV関数やSTDEV.S関数、STDEV.P関数など似たようなものがあったり、統計的検定を行う場合は等分散を仮定したtt検定であったり等分散を仮定しないt検定があったり等々。
そのような【統計処理を代わりに行ってほしい方々のため】に当サイト管理人であるYCが【統計解析代行】を行っています。


概要は以下の通りですので、興味ある方はお気軽にご連絡ください。


お問い合わせはこちらにお願いいたします。



【統計処理代行の概要】


・データの標準偏差、分散等の統計的基礎量の算出


・信頼区間の算出(点推定や区間推定)


・データに相関があるかどうかの判断のための相関分析


・多変量解析の代表である重回帰分析を始めとした回帰分析


・統計において非常に重要な位置付けである2群の有意差の検定(F検定やt検定等)


・二元配置分散分析を始めとした分散分析


・その他統計処理を行う前のExcel関数を使用したデータの整理


等、対応が可能です。


Excelにて統計解析を行うため、データをExcelにてご送付いただけましたら、幸いです。


また、対応可能かどうかを確認させて頂いた上でのやりとりさせて頂きたく、まずはこちらにご一報ください


【Excel】共分散とは?COVAR関数,COVARIANCE.P関数を使用して共分散を求めてみよう COVARIANCE.P関数と.S関数の違いは?【演習問題】 関連ページ

Excelデータ分析ツール使用の前準備
度数分布表とヒストグラム
【Excel】平均とは?(算術平均と加重平均) AVERAGEIF関数で条件付き(〜以上かつ以下、〜以上かつ未満、不等号、日付の範囲指定)の平均値を算出してみよう
【Excel】平均とは?2 (幾何平均、移動平均)
【Excel】分散と標準偏差とは?基本統計量とは?Excel関数(VARP関数、STDEVP関数)で分散と標準偏差を計算してみよう
標本と母集団
信頼区間の推定
信頼区間の推定を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】相関、相関係数とは?COOREL関数、PEASON関数、分析ツールで算出しよう【演習問題】
【Excel】階乗の計算方法 FACT関数で階乗を求めてみよう【演習問題】
参考文献
【Excel】関数を使わずにデータを間引く方法
【Excel】関数を使用してデータを間引く方法(INDIRECT関数)
【Excel】関数を使わずにn行ずつ空欄を追加する方法
【Excel】数字の間にハイフンを一括して入れる方法
【Excel】−(ハイフン)を入力した郵便番号の−を消す、再度つける方法
【Excel】Forecast関数で直線補間してみよう!Trend関数との違い
【Excel】SUMPRODUCT関数で積の合計を計算しよう!SUM関数との違い
【Excel】SUMIFS関数で複数条件の和の計算を行ってみよう!〜以上かつ以下、〜以上かつ未満、不等号、日付の範囲指定【演習問題】
【Excel】°(度)とrad(ラジアン)の変換方法【計算の考え方】
【Excel】勾配の計算方法 Excelを用いて勾配を計算してみよう
【Excel】Vlookup関数の使用方法
【Excel】STDEV関数とSTDEVP関数の違い
【Excel】5ずつ切り上げる方法 1-5を5、6-10を10とする方法
【Excel】2つのif関数でデータを3種類に分類する方法 (A以上B以下)
【Excel】条件に合うデータの数量の数え上げ Countif,Countifs関数
【Excel】Excelソルバーで最適化問題を解こう!生産計画,線形計画問題【演習問題】
【Excel】Excelソルバーで最適化問題を解こう!二次関数の問題【演習問題】
両側検定と片側検定の違い
分散分析 対応の無い場合の一元配置の分散分析をExcelで行ってみよう
分散分析 対応の有る場合の一元配置の分散分析をExcelで行ってみよう
分散分析 繰り返しの無い、有る場合の二元配置の分散分析をExcelで行ってみよう
多重比較法とは?分散分析との違い Tukeyの方法
数量化I類とは?Excelを用いて定性的なデータ(質的データ)の重回帰分析を行ってみよう
重回帰分析とは?Excel分析ツールで定量データの重回帰分析を行ってみよう!【リチウムイオン電池のデータ解析】
【Excel】最小二乗法とは?INTERCEPT関数とSLOPE関数の使用方法【単回帰分析、重回帰分析】
【Excelまとめ】Excel関数、分析ツールで統計解析を行おう
多変量解析 主成分分析と因子分析とは?違いは?
実験計画法

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