■　最小二乗法の練習問題

このページでは，Excelのソルバーの利用として，回帰分析に登場する最小二乗法を扱います．
■解説
(1) 一番簡単な最小二乗法

　右の表は，2005.1月〜2006.12月までの月ごと（5日頃）のNYダウ平均株価(単位ドル)と日経平均株価（単位円）です．
　　　　　　　［資料の出所：http://finance.yahoo.com/ のデータベース］

　これらを散布図に表わすと下のグラフのようになります．

　
　このとき，ｙの値（日経平均）を１つの定数 a で近似するには，図の青で示した横線（y=a）を求めることになります．
　図の赤で示した測定値と青で示した予測値の差は，灰色の線の長さで示されますが，正負の符号があるので，単純にデータの個数分足せば消えてしまいますので，２乗を考え，（測定値-予測値）2の和：

　S=(y1-a)2+ (y2-a)2+ (y3-a)2+ ･･･+ (yn-a)2　･･･(A)

が最小となる a の値を求めることにします．
　この問題のように，（測定値-予測値）2の和が最小となるような予測値を求める方法は最小二乗法と呼ばれます．
　 A B C
1 Date x
NYダウ($)		 y
日経平均(\)
2 2005.1月 10,489.94 11,387.59
3 2月 10,766.23 11,740.60
4 3月 10,503.76 11,668.95
5 4月 10,192.51 11,008.90
6 5月 10,467.48 11,276.59
7 6月 10,274.97 11,584.01
8 7月 10,640.91 11,899.60
9 8月 10,481.60 12,413.60
10 9月 10,568.70 13,574.30
11 10月 10,440.07 13,606.50
12 11月 10,805.87 14,872.15
13 12月 10,717.50 16,111.43
14 2006.1月 10,864.86 16,649.82
15 2月 10,993.41 16,205.43
16 3月 11,109.32 17,059.66
17 4月 11,367.14 16,906.23
18 5月 11,168.31 15,467.33
19 6月 11,150.22 15,505.18
20 7月 11,185.68 15,456.81
21 8月 11,381.15 16,140.76
22 9月 11,679.07 16,127.58
23 10月 12,080.73 16,399.39
24 11月 12,221.93 16,274.33
25 12月 12,501.52 16,417.82

［求め方］
■ソルバーを利用するための準備：

1)　定数a を書き込むセルを１つ用意します．
セルA27に1と記入します．これは初期値（仮の値）で，後にソルバーによって，この値を増減させて条件に合うものを探すことになります．（ここに記入するのは，数値です．計算式ではありません．）なお，自分自身が分りやすいように，その上に「定数」などと書き込んでおきましょう．

2)　セルD2に =(C2-$A$30)^2 と記入し，この式をコピーし，D3からD25に貼り付けます．
　D2に書かれた式は(A)の(y1-a)2に対応します．
　D3は(A)の(y2-a)2という式に対応します．
　･･･（以下同様）･･･
　※　セルD2に記入するものは， =(C2-A27)^27 ではありません．もしそのように書き込むと，その式をコピーし，D3に貼り付けたとき =(C3-A28)^2 となって，引く側もずれます．

3)　セルD27で，ショトカットアイコンのΣを押し，範囲をD2：D25とします．（=SUM(D2:D25)と直接書き込んでもよい．または，メニュー→「挿入」→「関数」→「数学／三角」→SUM→数値１をD2:D25とします．）

■これで準備ができたので，ソルバーを用いて最小二乗法により，「A27の値を変化させて，D27が最小となれば止まる」ようにします：
4)　メニュー→「ツール」→「ソルバー」→
　　目的セル：$D$27
　　目標値：最小値
　　変化させるセル：$A$27
　　オプション：反復回数1000，精度0.001，収束0.001，反復結果の表示にチェック･･･この程度から始めると収束しやすいようです．高い精度が必要なときは，一旦値を書き込んでから「精度」や「収束」を厳しく（0.0001などに）するとよいでしょう．

5)　「実行」ボタンを押す．（上の4)で反復結果の表示チェックを入れておくと，１回で目的の値にたどり着かないときに，「継続」ボタンを押して漸近していけます．）
何回か「継続」ボタンを押し，［最適解が見つかりました］と表示されたら，［解を記入する］の状態でＯＫ

6)　結果の検討
○　上で求めたaの値は，14406.4　となります．
○　裏付けで確かめる：（コンピュータで処理しても，入力ミス，転記ミス，操作ミスなどはつきものです．別の方法で確認できるときは確認する方がよい．右の参考に示すように，ここで求めたaの値はyの平均値となっており，=AVERAGE(C2:C25)の値と一致するはずです．）
　 A B C D
1 Date NYダウ 日経平均 差の二乗
2 2005.1月 10,489.94 11,387.59 129654431.8
3 2月 10,766.23 11,740.60 137818208.2
.. ･･･ ･･･ ･･･ ･･･
14 2006.1月 10,864.86 16,649.82 277183207.4
15 2月 10,993.41 16,205.43 262583551.6
.. ･･･ ･･･ ･･･ ･･･
25 12月 12,501.52 16,417.82 269511978.9
26 定数 　 　 　
27 1 　 　 5090602434
（参考）
ここで求めたa の値は，次の変形で分るようにy の平均値となっています：
S=(y1-a)2+ (y2-a)2+ (y3-a)2+ ･･･+ (yn-a)2が最小となるa の値を求める⇔２次関数Sが最小となるaの値を求める⇔頂点の座標aを求める
S=(y12 - 2y1a+a2)+(y22 - 2y2a+a2)+···+(yn2 - 2yna+a2)
=na2 - 2a(y1+y2+···+yn)+...
=n { a2 - 2a(y1+y2+···+yn) / n} + ...
=n(a - (y1+y2+···+yn) / n}2+...

の頂点の座標（軸）a は
a = (y1+y2+···+yn) / n
となり，a が y の平均値に等しいときに，S は最小となります．
■　(2)回帰直線を最小二乗法で求める
　日経平均とＮＹダウは，きれいな直線的関係にはなっていませんが，正の相関（一方が増えれば他方も増える関係）があるように見えます．そこで，右図のようにこれらの関係を表わす直線（回帰直線）の方程式を求めてみます．

　ただし，NYダウを独立変数（説明変数）x に，日経平均を従属変数（目的変数）y に選ぶものとします．･･･ＮＹダウから日経平均を求める･･･

　このとき，求めるものは，y=ax+b の2つの係数 a, b です．
　図の赤で示した測定値と青で示した予測値の差から，（測定値-予測値）2の和：

　S=(y1- ax1 -b)2+ (y2- ax2 -b)2+ (y3- ax3 -b)2+ ･･･+ (yn- axn -b)2　･･･(B)

が最小となる a , b の値を求めることにします．
［求め方］
■ソルバーを利用するための準備：

1)　係数a , b を書き込むセルを各々用意します．（後の処理を考えると，隣り合うセルを指定する方が簡単になります．）
セルB27に1，C27に1と記入します．これは初期値（仮の値）で，後にソルバーによって，この値を増減させて条件に合うものを探すことになります．（ここに記入するのは，数値です．計算式ではありません．）なお，自分自身が分りやすいように，その上に「係数a，定数b」などと書き込んでおきましょう．

2)　セルD2に =(C2-$B$27*B2-$C$27)^2 と記入し，この式をコピーし，D3からD25に貼り付けます．
　D2に書かれた式は(A)の（測定値-予測値）2 すなわち (y1 - ax1 - b)2に対応します．
　D3は(B)の(y2- ax2 - b)2という式に対応します．
　･･･（以下同様）･･･
　※　セルD2に記入するものは， =(C2-B27*B2-C27)^2 ではありません．もしそのように書き込むと，その式をコピーし，D3に貼り付けたとき =(C3-B28*B3-C28)^2 となって，引く側もずれます．

3)　セルD27で，ショトカットアイコンのΣを押し，範囲をD2：D25とします．（=SUM(D2:D25)と直接書き込んでもよい．または，メニュー→「挿入」→「関数」→「数学／三角」→SUM→数値１をD2:D25とします．）

■これで準備ができたので，ソルバーを用いて最小二乗法により，「A27の値を変化させて，D27が最小となれば止まる」ようにします：
4)　メニュー→「ツール」→「ソルバー」→
　　目的セル：$D$27
　　目標値：最小値
　　変化させるセル：$B$27:$C$27
　　オプション：反復回数1000，精度0.001，収束0.001，反復結果の表示にチェック･･･この程度から始めると収束しやすいようです．高い精度が必要なときは，一旦値を書き込んでから「精度」や「収束」を厳しく（0.0001などに）するとよいでしょう．

5)　「実行」ボタンを押す．（上の4)で反復結果の表示チェックを入れておくと，１回で目的の値にたどり着かないときに，「継続」ボタンを押して漸近していけます．）
何回か「継続」ボタンを押し，［最適解が見つかりました］と表示されたら，［解を記入する］の状態でＯＫ

6)　結果の検討
○　上で求めたa , b の値は各々，2.5953 , -14147.87 となります．
○　裏付け：（右の欄参照）
　 A B C D
1 Date NYダウ 日経平均 差の二乗
2 2005.1月 10,489.94 11,387.59 2853938.628
3 2月 10,766.23 11,740.60 4216505.727
　 ･･･ ･･･ ･･･ ･･･
13 12月 10,717.50 16,111.43 5972584.025
14 2006.1月 10,864.86 16,649.82 6759114.749
15 2月 10,993.41 16,205.43 3318993.597
　 ･･･ ･･･ ･･･ ･･･
25 12月 12,501.52 16,417.82 3533788.58
26 　 係数a 定数ｂ 　
27 　 2.5953 -14147.86927 50862273.4
○　裏付けで確かめる：

※　先の例の式(A)と異なり，
S=(y1- ax1 -b)2+ (y2- ax2 -b)2+ (y3- ax3 -b)2+ ･･･+ (yn- axn -b)2　･･･(B)
のような式を変形するのは大変ですので，何を求めているのかということさえ分れば，実際の計算はコンピュータにまかせます．

1)　係数 a ,b をExcelで求める他の方法として，「散布図に近似直線を表示し，その方程式を表示する」には次のようにします．
　上の表でB1，C1のラベルを含めてB1:C25の範囲を選択して，グラフウィザードにより散布図を表示します．
　次に，グラフ領域をクリック（選択）しておいてから，メニュー→「グラフ」→「近似曲線の追加」→「線形近似」
　さらに，表示された直線をダブルクリックし，「オプション」で［グラフに数式を表示する］にチェックを入れます．
　これで，回帰直線（近似直線）の方程式が表示されるので，自分で求めた定数項とｘの係数と比較することができます．

2)　係数 a ,b をExcelで求める他の方法として，ツールの回帰分析を用いる方法があります．
メニュー→「ツール」→「分析ツール」→「回帰分析」→
入力ｙ範囲：$C$1:$C$25　･･･（ラベルも含める）
入力ｘ範囲：$B$1:$B$25　･･･（ラベルも含める）
ラベル：チェックを入れる
　※入力範囲のｙとｘを逆にすると，日経平均でＮＹダウを説明する（ＮＹ=従属，日経=独立）ことになり，意味が変るので注意

　出力される表のうち，左下の方に表示される次の値が各々b , a の値となります．
　 係数
切片 -14148.21469
NYダウ 2.595357841
■　(3)２次の回帰曲線を最小二乗法で求める
　この期間の日経平均とＮＹダウは，直線関係というよりは２次関数に近い関係にも見えます．そこで，より正確な予測式を求めるために y を x の２次関数で表わすことを考えてみます．
　ただし，NYダウを独立変数（説明変数）x に，日経平均を従属変数（目的変数）y に選ぶものとします．･･･ＮＹダウから日経平均を求める･･･

　このとき，求めるものは，y=ax2+bx+c の3つの係数 a, b , c です．
　図の赤で示した測定値と青で示した予測値の差から，（測定値-予測値）2の和：

　S=(y1- ax1 2-bx1-c)2+ (y2- ax2 2-bx2-c)2+･･･+ (yn- axn 2-bxn-c)2　･･･(B)

が最小となる a , b , c の値を求めることにします．
［求め方］

（おそらく）

　数学的には今までの場合と同様にできるように思われますが，これを直接行ってもうまくいきません．例えば，ＮＹダウは10000のオーダーなのでその２乗は100000000のオーダーとなり，係数aの値を１増加させるのが，係数ｃを100000000増加させるのと同じ効果を持っています．こんなにスケールの異なる数字を対等に並べて得られる解は，ずいぶんアバウトなものになるはずです．

（そこで）

　x と x2 が両方とも１のオーダーになるようにx を1.0前後になるような単位にするために，ＮＹダウの単位を万ドル，日経平均の単位を万円とすると（各々10000で割ると）うまくいきます．
（標準化（基準化）も考えられますが，復元が少し複雑）

　以下は，今までと同様に行えばできます．
a = -23.837, b = 56.414 , c = -31.685 となります．
Date NYダウ ＮＹダウ２乗 日経平均
2005.1月 1.0490 1.1004 1.1388
2月 1.0766 1.1591 1.1741
3月 1.0504 1.1033 1.1669
･･･ ･･･ ･･･ ･･･
12月 1.0718 1.1486 1.6111
2006.1月 1.0865 1.1805 1.6650
2月 1.0993 1.2086 1.6205
･･･ ･･･ ･･･ ･･･
12月 1.2502 1.5629 1.6418
○　裏付けで確かめる：
ア）　グラフで散布図を描いてから，メニュー→「グラフ」→「近似曲線の追加」→「多項式近似：２次」
　さらに，表示された曲線をダブルクリックし，「オプション」で［グラフに数式を表示する］にチェックを入れます．
（元のデータで行っている場合と万ドル，万円で行っている場合とでは， a , c が10000倍，10000分の１の数字になっています）

イ）　ツールの分析ツールで回帰分析を行うには，変数の数だけ列が必要になるので，あらかじめＮＹダウの２乗の列を追加しておきます．上の表参照．
　入力ｘ範囲にＮＹダウとＮＹダウの２乗の２列を入れます．
　以後の処理は，今までと同じです．出力結果が次のようになれば，上から順に c , b , a を表わしています．
　 係数
切片 -316854.1491
NYダウ 56.41498251
NYダウ２乗 -0.002383785
■　(4)重回帰式を最小二乗法で求める
　右の表のように説明変数が多変数の場合は，散布図として図示することはできませんが，最小二乗法を適用することはできますし，回帰式（重回帰式という）を求めることもできます．　（ただし，右の表は架空データ）
　求めるものは，y=ax1+bx2+cx3+d の４つの係数 a, b , c , d です．

　ここまでの解説と同様，係数 a, b , c , d の初期値を準備し，これを用いて，（測定値-予測値）2の和を求め，ソルバーを用いてその値が最小となる係数a, b , c , d を求めます．

　計算が長くなるので，ステップを分けて，右の表のように一旦予測値を求めて，次に（測定値-予測値）2を計算すると楽です．

○　裏付け：
　散布図はありませんが，「ツール」→「分析ツール」→「回帰分析」により求めることができます．（右下表のように出力されます．）
No. 変数1 変数2 変数3 目的変数
1 10.5 3.6 6.6 263
2 11.3 3.1 5.7 209
3 9.8 4.2 5.0 218
4 9.2 4.1 5.3 216
5 13.6 3.6 3.6 214
6 10.3 4.6 4.1 165
7 11.7 3.7 3.6 171
8 12.3 3.8 3.5 137
9 13.5 4.5 3.1 149
10 10.6 3.8 3.1 138
　 　 　 　 　
　 a b c d
　 7.1 4.3 35.5 -63.6　　

　 係数
切片 -63.61279882
変数1 7.111595024
変数2 4.27233032
変数3 35.48897218
■問題　(1)(2)(3)は(1)のときの表をドラッグ・コピーしてExcelに貼り付ければ取り込むことができます．(4)の表も同様です．これらを用いて，上の解説と同じ結果になるか確かめてください．
○===メニューに戻る