概要

• 演習⑤ではエクセルで相関係数と回帰係数を求めます。手作業で相関係数を求めるのは手間がかかりますが、エクセルならば比較的簡単にできます。ここでは練習として2種類の方法で相関係数を求めます。
• 以下を読みながらサンプル通りに入力して処理を確認しましょう。なお、計算式は自分で考えます。

新しいシートを作る

• 前回まで使ったのと同じファイルを使います。エクセル下部のシート名の近くにある「＋」ボタンを押し、新しいシートを挿入します。そしてシート名を「5-1」にしてください。
• 不要なシートがある場合は、シート名を右クリックして「削除」を選びます。

相関係数　方法Ⅰ：計算の手順通りにする

• 別紙のデータを元に相関係数を求めます。
• 相関係数は下の式で表されるので、偏差平方和と積和を求める必要があります。

• まずは下のように、新しいシートを開いて必要な準備をしてください。
• 準備ができたら、表の空欄を全て埋めていきましょう。その際は必ず計算式を使ってください。
• 合計するには「=sum( )」を、平均を求めるには「=average( )」という関数を使います。いずれも( )の中には、計算させるセルを指定します。

• 「偏差、偏差平方、積」でオートフィルをする方法。

• 表の計算が終われば、下のようになります。

• 続いて、相関係数を求めます。相関係数に必要なのは、偏差平方和と積和でした。既に必要な数字は揃っているので、D18セルにこの ｒ の計算式を入力します。偏差平方和と積和はセルを指定しましょう。平方根は「sqrt( )」という関数を使います。カッコ内の引数に、「ルート記号の中身」を指定します。例えば「ルート2」は sqrt(2) 、C14セルの平方根を求めたければ sqrt(C14) と表記します。
• 以下の相関係数を求める式は、関数を使ってどのようにエクセルの計算式で表せるか考えてください。

• 計算式ができれば、下のようになるはずです。

相関係数　方法Ⅱ：関数で計算する

• エクセルには相関係数を求める関数があります。ここで配列1・2は二つの変数データが入っているそれぞれのセル範囲を指定します。
  　=correl(配列1, 配列2)
  
• 例えば、xがC4からC13に、yがD4からD13にあるならば、
  　=correl(C4:C13, D4:D13)　と指定します。
• 「セル番号のどこからどこ」を指定する際は、「:」（コロン）という記号を使います。ただし、キーボードで入力するよりは、マウスで指定する方がミスを防げます。

• 正しく設定できれば、このようになるはずです。当然、「表から求めた数字」と「関数で求めた数字」は同じになります。

回帰係数を求める

• 回帰係数は下の式で表されるので、上で求めた数字を使って計算式を作ります。

• 正しくできれば、このようになるはずです。

散布図を書く

• エクセルではグラフを書くのも簡単です。
• まずは散布図にしたい2変数のデータが入っているセルを範囲選択します。

• 「挿入」タブで「グラフ」の「散布図」を選びます。

• 散布図が挿入されました。

• グラフ中の任意の●を右クリックし、「近似曲線の追加」を選びます。

• 右側に出てきた項目の最下部にある「グラフに数式を表示する」にチェックマークを付けます。

• 回帰直線と近似式が表示されました。

• 式を選択してフォントサイズを大きくし、見やすい場所に動かし、仕上げます。

全体のレイアウトを整える

• 必要に応じてセルの背景や文字に色を付けたりして見やすくします。
• グラフのサイズや位置を変えたりして全体のレイアウトを整えましょう。

課題

• テキストp.138の身長データをもとに、相関係数、回帰係数を求め、散布図を書いてください。
• なお、表などをゼロから作成する必要は無く、シート「5-1」をコピーして「5-2」を作りましょう。シートのコピーはシート名を右クリックし、「移動またはコピー」を選び、「コピーを作成する」にチェックマークを付け、OKをクリックします。
• データの件数を増やしたければ、表の行数を増やします。それにはシート左端の行番号を複数選択し、右クリックして「挿入」を選びます。
• 回帰係数が求まったら、 それを用いて「父親の身長が170cmのときの息子の身長」を予測してください。y=ax+bに代入して求めるということですが、aとbについては回帰係数が入っているセル番号を指定してください。