配付資料から回帰直線を求める

以前に配布した資料のデータに関する回帰分析を行います。講義では手計算で回帰直線の切片と傾きを求めましたが、今日はExcelを使って計算してみましょう。

データの入力

まず、説明変数 x と被説明変数 y を入力します。

散布図の作成

このデータの散布図を描いて見ましょう。セルB1からC5までをドラッグして、「挿入（I)」−「グラフ（H)...」とクリックすると、グラフウイザードが表示されますので、グラフの種類を散布図に、形式を点のみに指定します。下図のようになるはずです。

「次へ＞」ボタンをクリックし、さらにもう一度「次へ＞」ボタンをクリックすると、グラフのタイトル等を決めるダイアログ・ボックスが表示されますので、次のように設定しましょう。

「次へ＞」−「完了（F)」の順でクリックすれば、ワークシート上にグラフが表示されます。グラフのサイズと位置は、通常のウインドウと同様に変更できますので、とりあえず計算の邪魔にならない位置に移動してください。

平均の計算

次に、x, y の平均、分散、標準偏差を求めます。まず、x, 合計と平均を求めましょう。B６とB7に以下の数式を入力します。

「COUNT(B2:B5)」は、セルB2からB5の間にいくつのセルがあるか数える関数です。この場合、COUNT(B2:B5)＝４ですね。次のような計算結果になります。

セルB6からC7までドラッグし、「編集（E)」−「フィル（I)」をクリックし、右方向へコピーしてください。ｙについての合計、平均が計算されます。

分散、共分散の計算

次に、x, yの分散、共分散を求める作業を行います。1行目に次の文字列を入力してください。

(x-xBAR)は、(x-xBAR)*(y-yBAR)は、(x-xBAR)^2は、のつもりで書いています。

問題

(1) セルD2に適当な数式を入力し、セルD5まで下方向へコピーしましょう。

(2) 同様にセルE2,F2,G2,G2に適当な数式を入力し、下方向へコピーしましょう。

結果は次のようになります。

D列からH列の合計を6行目に求めましょう。セルC6からH6までドラッグして右方向へコピーしてください。

D列とE列の合計は必ず0になることは統計学で学習しました。覚えていますか？

次に、F列、G列、H列の合計を（データ数−1）で割ることによって、x,yの共分散と、x,yの分散を求めましょう。

セルF8に「=F6/(COUNT(F2:F5)-1)」と入力し、H2まで右方向にコピーしてください。

相関係数の計算

次にセルB10に相関係数を計算します。次の式を入力してください。

SQRTというのは平方根を計算するための関数です。

回帰直線を求める

ｘを説明変数、ｙを被説明変数としたときの回帰直線を求めましょう。まず直線の傾きｂをセルB11に求めます。

次に直線の切片ａをセルB12に求めます。

これより、回帰直線は y=2+0.7x であることがわかります。

決定係数の計算
単回帰の場合、決定係数は相関係数の2乗と等しくなりました。セルB13に決定係数を求めましょう。

決定係数は0以上1以下の値をとることを思い出してください。

演習が済んで時間が余っている人は、セルB2からC5に入力したデータの値を変更してみてください。即座に散布図や、回帰直線などが再計算されます。