エクセルを用いた相関係数と回帰直線の計算

amazon：Excelで操る! ここまでできる科学技術計算 第2版 楽天： Excelで操る! ここまでできる科学技術計算 [ 神足　史人 ]

図１　数学と理科の得点相関図

図２　数学と英語の得点相関図

１６人のクラスで数学、理科、英語のテストをしました。その結果は図３です。 　　　　　　　　図３　数学、理科、英語のテスト結果 　図３のテスト結果に基づき、得点相関図(図１、図２）を作成しました。図１より、数学と理科の得点には相関関係が強く、数学の得意な人は理科も得意であることが定性的にわかります。２組のデータ間の相関関係を定量的に示す指標が相関係数です。 ２組のデータ(xi,yi),(i=1,2,3, ・・・・,n)が与えられた時、その相関係数(r)は(1)式となります。 　　r = Σ(xi - xav)(yi-yav)/(√(Σ(xi-xav)√(Σ(yi-yav))　・・・・・ (1) 　　　　　　　　　　　xav = (Σxi)/n ：xiの平均 　　　　　　　　　　　yav = (Σyi)/n ：yiの平均 また、(1)式は(xi,yi)の共分散をそれぞれの標準偏差で割ったものと同じです。 　相関係数(r)は -1< r < 1　の実数値であり、rが１に近いときは２組のテータに強い相関関係があり、rが-1に近いときは負の相関関係にある。たとえば、テレビを見る時間とテストの得点は負の相関係数あると思われる。また、rが0に近いときは２組のデータ間の相関は弱い。

統計学が最強の学問である たった1日で即戦力になるExcelの教科書 入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス) Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 定量分析実践講座―ケースで学ぶ意思決定の手法 統計数理は隠された未来をあらわにする Excelで学ぶ統計解析―統計学理論をExcelでシミュレーションすれば、視覚的に理解できる マンガでわかる統計学 ビジネスマンのための「数字力」養成講座 (ディスカヴァー携書 20) 完全独習 統計学入門

相関係数を求めるエクセルの関数はCORREL(X配列,Y配列)又はピアソンの積率相関係数を求めるPEARSON(X配列,Y配列)です。図４のように、数学と理科、数学と英語の相関係数を求めます。 　　　　　　　図４　相関係数を求めるエクセルデータ

図５　数学と理科の得点相関図と回帰直線のグラフ

平均値を求める関数はAVERAGE(配列)、標準偏差を求める関数はSTDEVPA(配列)です。 図６のように、(2)式により回帰直線の傾きを求め、(3)式により回帰直線の始点と終点を計算します。 　　　　　　　　　　　　　　　　　　　　図６　回帰直線を求めるエクセルデータ

エクセルを用い平均値と標準偏差から偏差値を計算する