以前に配布した資料のデータに関する回帰分析を行います。講義では手計算で回帰直線の切片と傾きを求めましたが、今日は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に入力したデータの値を変更してみてください。即座に散布図や、回帰直線などが再計算されます。
