７．２　回帰曲線 身長と体重…関係がありそう？ ??? 身長と体重の関係をグラフで観察する

関係を見る 散布図が便利 独立変数（説明変数：身長）と 関係を求めたい従属変数（目的変数：体重）で 散布図を描いてみよう

（１） 散布図を描いてみる データ範囲の指定とグラフ挿入 （１）　散布図を描いてみる データ範囲の指定とグラフ挿入 散布図を描くデータの範囲を指定して，「挿入」「グラフ」を指定します。 身長・体重表はこちらから

散布図の選択 「グラフウィザード」でグラフの種類として「散布図」を選択し，その他のパラメータを指定します。

凡例の削除調整 このままでも良いのですが，目盛りのタイトルをつけましたので，凡例を消しておきましょう。 凡例のところで右ボタンをクリックして

凡例の削除 「クリア」を選択する

サイズの調整 見やすくサイズを調整しましょう。

（２）関係はあるのかな 直感で直線を引いてみる とにかく直感で直線を引いてみると… ここで左ボタンを離す 左ボタンを押したまま，線を引く

おおよその傾向 適当だけど… こんな感じかな？ だけど… 適当じゃ～ 人によって 違うし？ ???

回帰曲線 線分の長さをD i （ i=1～N)として，S = D12 + D22 + ・ ・・ + DN2 の Ｓを最小にするように　Ｙ ＝ a X + b　の a，ｂ　を求めます。 これを回帰直線と呼びます。Ｓ／Ｎは分散です。 Ｓ/Nは， 平均のところで 出てきた分散と 同じだよ！！

用語 説明変数が１つのとき ： 単回帰分析 説明変数が２つ以上のとき ： 重回帰分析 二乗の和を最小にする方法 ： 最小二乗法 言葉を覚えよう… 説明変数が１つのとき ： 単回帰分析 説明変数が２つ以上のとき ： 重回帰分析 二乗の和を最小にする方法 ： 最小二乗法

（３）最小二乗法 で近似することを考える。 残差平方和は 残差平方和が最低となるには，それぞれの係数で微分した値が０となればよい。 すなわち，以下の連立方程式を解くことで，係数を得ることができる。

直線の係数を求める 連立方程式を解くと とおいて

あえて式定義で求めてみよう 式定義

結果

あえて式定義で求めてみよう 結果 a0 = 10.03 a1 = 2.51

N次の多項式による近似 で近似することを考える。 残差平方和は 残差平方和が最低となるには，それぞれの係数で微分して得られた式を 整理した，以下の連立方程式を掃出し法等の手法を使って解けばよい。

VBAで２次以上の式で近似 シートの定義

VBA記述 ①データ宣言とデータ設定 Private データ数 As Integer Private 次元 As Integer Private X() As Double Private Y() As Double Private A() As Double Private Sub データ設定() With Worksheets("Sheet2") 次元 = Val(.Cells(2, 4)) i = 2 ‘データ数カウント Do While .Cells(i, 2) <> "" i = i + 1 Loop データ数 = i - 2 ReDim X(データ数), Y(データ数) ReDim A(次元 + 1, 次元 + 2) For i = 1 To データ数 X(i) = Val(.Cells(i + 1, 2)) Y(i) = Val(.Cells(i + 1, 3)) Next End With End Sub

VBA記述 ②結果設定とボタンのＣｌｉｃｋイベントハンドラ Private Sub 結果設定() With Worksheets("Sheet2") N = 次元 + 1: N1 = N + 1 For i = 1 To 次元 + 1 .Cells(i + 1, 5) = A(i, N1) Next End With End Sub Sub ボタン1_Click() データ設定 最小二乗法 A, X, Y, 次元, データ数 結果設定

VBA記述 ③連立方程式の係数設定 Private Sub 係数設定(A, X, Y, 次元, データ数) m = 次元 + 1 For i = 1 To m A(i, m + 1) = 0 For k = 1 To データ数 A(i, m + 1) = A(i, m + 1) + (X(k) ^ (i - 1)) * Y(k) Next For j = 1 To m A(i, j) = 0 A(i, j) = A(i, j) + X(k) ^ (i + j - 2) End Sub

VBA記述 ④確認用途中経過設定 （理解のための表示なので実際の実行ではいらない） Private Sub 途中経過設定(A, N) '実際の実行のときは，このルーチンはいらない With Worksheets("Sheet2") For i = 1 To N .Cells(i + 10, 1) = i Next For j = 1 To N + 1 .Cells(10, j + 1) = j .Cells(i + 10, j + 1) = A(i, j) End With End Sub

VBA記述 ⑤最小二乗法メイン Private Sub 最小二乗法(A, X, Y, 次元, データ数) ‘連立方程式を解く前の行列表示（実際の実行ではいらない） 途中経過設定 A, 次元 + 1 消去法 A, 次元 + 1, 0.00001 End Sub

結果のグラフ化 散布図に近似式を追加します。 （以下の近似式は0.05刻みで表示しています） 直線だけでなく 色々な近似が できるよ～

一般化（１） で近似するとき， 残差平方和は 残差平方和が最低となるには，それぞれの係数で微分した値が０となればよい。

一般化（２） すなわち， 以下の連立方程式を解くことに帰着する。

確認 一般式において， のとき直線近似と同じであることを確認せよ

（４）Ｅｘｃｅｌの機能を使った近似 グラフ上のデータ部分で右クリックして， 「近似曲線の追加」を選択します。 直線だけでなく 色々な近似が できるよ～

Ｅｘｃｅｌで関数当てはめ 「オプション」タブで「グラフに数式を表示する」， 「グラフにＲ－２乗値を表示する」にチェックを入れます。

R 2値 寄与率，決定係数ともいう。 （予測値の２乗和）／（実測値の２乗和） R 2 ＝ 0.8 のとき， 　（予測値の２乗和）／（実測値の２乗和） R 2 ＝ 0.8 のとき， 「この回帰式は，80％くらい説明している」 と表現する。

式の移動 式が見にくいので，移動しましょう。 ②左ボタンを押したまま， 移動先まで移動して， 左ボタンを離します。 　 移動先まで移動して， 　 左ボタンを離します。 ①式のところで左ボタンを押し

式の当てはめ これで式を当てはめることができました。 余裕のある人は， この式をＥｘｃｅｌのセルに定義して 実際の体重との差から 標準偏差を計算して 見てください。

２次式への当てはめ 2次式に当てはめてみました。 Ｒ２値がちょっとだけ 良くなったけど… あんまり変わらないね

特殊な式に比例することが分かっているとき （５）特殊な式への当てはめ 特殊な式に比例することが分かっているとき まず，基データに対して 演算した結果を説明変量とし， 目的変量との間で関数を 当てはめます。 この場合は， 　ｙ = 98.589/(1+exp(X))+29.93 とすることができます

（６）重回帰分析 重回帰分析 ： 説明変量が2つ以上のときの回帰分析 １変数のときと同様 で近似することを考える。 残差平方和は 残差平方和が最低となるには，それぞれの係数で微分した値が０となればよい。

残差平方和の偏微分 残差平方和が最低となるには， それぞれの係数で微分した値が０となればよい。

連立方程式 すなわち，以下の連立方程式を解くことに帰着する。 対称行列であるから，ハウスホルダ法等により３重対角化が可能である。

行列の特徴 対称行列である ハウスホルダ法等により，３重対角化が可能である。

単純な例 関数がすべて一次式のとき 解くべき連立方程式は

VBAで記述　①シート定義 結果確認のセルもあらかじめ式定義しておく

VBA記述 ①データ宣言とデータ設定 Private N As Integer Private M As Integer Private X() As Double Private Y() As Double Private A() As Double Sub データ設定() N = 10: M = 3 ReDim X(N, M), Y(N), B(M), A(M, M + 1) With Worksheets("Sheet2") For i = 1 To N Y(i) = .Cells(i + 1, M + 1) For j = 1 To M X(i, j) = .Cells(i + 1, j) Next End With End Sub

VBA記述 ②結果設定とＣｌｉｃｋイベントハンドラ Sub 結果設定() With Worksheets("Sheet2") For i = 1 To M .Cells(i + 1, 5) = A(i, M + 1) ‘ 消去法の結果 Next End With End Sub Sub Sheet2_ボタン1_Click() データ設定 重回帰 結果設定

VBA記述 ③係数設定と重回帰 Sub 係数設定() For k = 1 To M For j = 1 To M T = 0 For i = 1 To N T = T + X(i, k) * X(i, j) Next A(k, j) = T T = T + X(i, k) * Y(i) A(k, M + 1) = T Sub 重回帰() 係数設定 消去法 A, M, 0.0001 End Sub

VBA記述　④結果の確認 ［実行結果］

ハウスホルダー法の適用①データ宣言とデータ設定 ハウスホルダ法を適用したプログラム（シート定義は前述と同じ） Private N As Integer Private M As Integer Private X() As Double Private Y() As Double Private B() As Double Private Rank As Integer Private ConditionNumber As Double Sub データ設定() N = 10: M = 3 ReDim X(N, M), Y(N), B(M) With Worksheets("Sheet1") For i = 1 To N Y(i) = .Cells(i + 1, M + 1) For j = 1 To M X(i, j) = .Cells(i + 1, j) Next End With End Sub

②結果の設定とＣＬＩＣＫイベントハンドラ Sub 結果設定() With Worksheets("Sheet1") For i = 1 To M .Cells(i + 1, 5) = B(i) Next End With End Sub Sub ボタン1_Click() データ設定 Householder 結果設定

③ハウスホルダー法（その１） Sub Householder() Dim U() As Double Dim IPV() As Integer Dim INorm() As Double ReDim U(N), IPV(M), INorm(M) For k = 1 To M IPV(k) = k: INorm(k) = 1 Next ConditionNumber = M MaxRNorm = 0 For j = k To M IJ = IPV(j) If INorm(IJ) > 0 Then S = 0 For i = k To N S = S + X(i, IJ) * X(i, IJ) 　　　　If k = 1 Then INorm(IJ) = S: T = S Else T = S / INorm(IJ) End If

④ハウスホルダー法（その２） If T > MaxRNorm Then MaxRNorm = T: MaxNormSq = S: MaxP = j End If Next If MaxRNorm > 0 Then IK = IPV(MaxP): IPV(MaxP) = IPV(k): IPV(k) = IK Else Rank = k - 1: ConditionNumber = 0: Exit For For i = k To N U(i) = X(i, IK) 　　S = Sqr(MaxNormSq): If U(k) < 0 Then S = -S U(k) = U(k) + S: X(k, IK) = -S T = 1 / (U(k) * S) S = 0 S = S + U(i) * Y(i)

⑤ハウスホルダー法（その３） S = S * T For i = k To N Y(i) = Y(i) - S * U(i) Next For j = k + 1 To M IJ = IPV(j) S = 0 S = S + U(i) * X(i, IJ) X(i, IJ) = X(i, IJ) - S * U(i)

⑥ハウスホルダー法（その４） If ConditionNumber <> 0 Then Rank = M: ConditionNumber = 1 / Sqr(MaxRNorm) End If For k = Rank + 1 To M B(IPV(k)) = 0 Next For k = Rank To 1 Step -1 IK = IPV(k) S = Y(k) For j = k + 1 To Rank IJ = IPV(j): S = S - X(k, IJ) * B(IJ) B(IK) = S / X(k, IK) End Sub

（７）Ｅｘｃｅｌ使って重回帰分析 重回帰分析 ： 説明変量が2つ以上のときの回帰分析 身長・ウェスト・体重表はこちらから 何か関係が 見つかるかな？ 身長，ウェスト，体重の 統計データをみてみよう 身長・ウェスト・体重表はこちらから

まずは，３つの変量間の関係を 大ざっぱに，ながめてみよう！！ 身長と体重，ウェストと体重，身長とウェストの関係を散布図にしてみよう。 身長と体重， ウェストと体重は， 関係あるみたいだけど， 身長とウェストは あまり関係なさそうだね。

そこで… 説明変量　：　身長，ウェスト 目的変量　：　体重 身長とウェストから 体重を推定できないか ってことだよ～

まず，分析ツールを有効に Ｅｘｃｅｌで重回帰分析を行うには，アドインの「分析ツール」を有効にしておく必要があります。 これって， 毎回やる必要はないよ。 最初に1回だけ やればいいからね。 ①「ツール」「アドイン」を 　　選択して ②分析ツール」に 　　チェックを入れて ③「ＯＫ」ボタンを 　　クリックする

「分析ツール」の中に回帰分析があることを確認して…はじめましょう。 できたかな？ さて，いよいよ…分析開始 「分析ツール」の中に回帰分析があることを確認して…はじめましょう。 ③回帰分析を 　　選択して 　　「ＯＫ」ボタンを 　　クリックします ①「ツール」「分析ツール」 　を選択して ②スクロールバーを 　 移動して

入力元を指定（１） 入力Ｙ範囲を指定 手順どおり やれば 大丈夫 入力Ｙ範囲を選択します

入力元を指定（２） 入力Ｙ範囲を指定 ドラッギング操作が 苦手な人は 慣れようね ①ここで左クリックして 左ボタンを押したまま 下に下げ， 　　左ボタンを押したまま 　　下に下げ， ②ここで左ボタンを 　　はなす。

入力元を指定（３） 入力Ｘ範囲を指定 入力Ｘ範囲を選択します なお，切片の定数を０にしたい場合は，ここをチェック。 余裕がでてきたら， 色々なオプションを 試してみることにして， 今は，この程度で… 入力Ｘ範囲を選択します なお，切片の定数を０にしたい場合は，ここをチェック。 しかし，今はやめときましょう。

入力元を指定（４） 入力Ｘ範囲を指定 ドラッギング操作が 苦手な人は 慣れようね ①ここで左クリックして 左ボタンを押したまま 下に下げ， 　　左ボタンを押したまま 　　下に下げ， ②ここで左ボタンを 　　はなす。

「ＯＫ」ボタンをクリックして，いよいよ重回帰分析開始 重回帰分析が 始まるよ 「ＯＫ」ボタンをクリックして

こんなシートが生成されますので，適当なセルを選択して範囲選択解除 結果 こんなシートが生成されますので，適当なセルを選択して範囲選択解除 色々なデータが 表示されているけど 普通はあんまり 気にしなくてもいいよ

結果の確認 この値が１.00に近いほど相関がある どこのセルでも良いから ＝TINV(両側危険度, 自由度） を入力してみよう。 　どこのセルでも良いから 　　＝TINV(両側危険度, 自由度） を入力してみよう。 　両側危険度を0.05程度にして,　 X値の ｔ がこの値より大きければ 関係が強いことになる。 ６８％程度の決定率 このあたりは 注意しようね これらの数値は　　 　　体重＝0.699295＊身長　 ＋ 　　　　　　 0.695397＊ウェスト－112.411 で近似できることを示す

結論 残差が大きい ： ばらつきが大きい 身長とウェストは体重と関係が強い ： 一定の関係がある 残差が大きい ： ばらつきが大きい 身長とウェストは体重と関係が強い ： 一定の関係がある ばらつきはあるが，身長とウェストは体重に対して 一定の関係があり，次の式で表現できる。　　 　　体重＝0.699295＊身長　 ＋ 　　　　　　 0.695397＊ウェスト－112.411 ばらつきを少なくするには，何らかの他の説明変量を 導入しなければならない。

（８）　重回帰分析での工夫 ①たとえば説明変量が2個（Ｘ１とＸ２とすると）のとき，Ｘ １＊Ｘ ２，Ｘ １２，Ｘ ２２の項を加えると重相関係数が良くなることが多い。 ②元のデータにある演算を施したものを説明変量とすると良い結果がでることがある。（単回帰曲線における成長関数） ③ 2次指数関数の場合，両辺の対数をとって考えると，良好な結果が得られることも多い。たとえば，理論的に， 　　　　Y＝ABX1CX2 　　　となる場合， 　　　　log(Y)　=　logA　+　X1・logB　+　X2・logC 　　とすることができるので，目的変量の対数をとって回帰分析を行えばよい。