■Excel：重回帰分析(1)
■重回帰とは■
　重回帰とは何か，何から何を求めるものか，その際注意すべきことは何かなど，このページでは，(1)「代数的に解いた場合」，(2)「最小二乗法で解いた場合」　から「だいたいどういう話をしているか」というイメージをつかみ，最後に，(3)分析ツールまたはTREND()関数を用いて簡単に求める方法を考える．
　実務に適するのは(3)「分析ツール」で，操作は簡単で結果はすぐ出るが，そもそも何を求めているのかは前の２つ(1)(2)の解説で理解してください．

■要約■
　２つ以上の説明変数(独立変数)x1，x2，･･によって目的変数(従属変数)yを表わす方程式
y = a + bx1 + cx2 + ･･･
を重回帰式という．Excelワークシ-トにおいて，目的変数，説明変数1，説明変数2，・・・の順に列を作っておくと，「ツール」→「分析ツール」→「回帰分析」により係数ａ，ｂ，ｃ，･･･を求めることができる．また，TREND()関数を用いればその係数を代入したときの予測値も直ちに得られる．
例
　輸出型企業であるトヨタ自動車の株価は，円/USドル為替レートや日経平均株価の影響を受けるのではないかと考え，これらの値でトヨタ自動車の株価を表わす方程式を考える．
　右の表１のように，2007年1月から３月上旬までのトヨタ自動車の株価，円/ドル為替レート，日経平均株価(終値)を作成しておき，説明変数(独立変数)x1，x2によって目的変数(従属変数)yを表わす方程式
y = a + bx1 + cx2･･･(0)　を求めたい．
　係数ａ，ｂ，ｃを求めるためには，方程式が3個あればよい．
（「初等統計学」(P.G.ホーエル著/培風館)P.259の説明が分かりやすいので，以下の説明は，その考え方による．）

原理的には各々の値の平均値を用いるのであるが，式を簡単にするために両辺にデータの組数ｎを掛ければ各値の和（Σ）になる．

ｙ，x1，x2の縦の和をΣで表わすと
Σｙ=an + bΣx1+cΣx2　･･･(1)（45日間でn=45とする）
((1)の各値に同じ数を掛けてしまうと，方程式としては何も新しいものができないが，各値に異なる数を掛ければ別個の方程式となる．そこで各値に隣の列のx1やx2を掛けて別の式を作る．)
各変数にx1の値を掛けたものを作ると
Σｙx1=aΣx1 + bΣx12+cΣx1x2　･･･(2)
同様にして各変数にx2の値を掛けたものを作ると
Σｙx2=aΣx2 + bΣx1x2+cΣx22　･･･(3)
　この連立方程式を解いて，a=-1205.07，b=15.6969，c=0.4190
　したがって，
y = -1205.07 + 15.6969x1 + 0.4190x2　･･･(4)
が求める方程式となり，日経平均株価と為替レートを入力すればトヨタ自動車の株価が求められる．（年月があまり外れたらダメ）
　この方程式によって予測した値と実際の値を並べてグラフにすると右図１のようになり(相関係数0.93)，かなりよい予測となっていることが分かる．

○　この例のように，２つ以上の説明変数(独立変数)を用いて１つの目的変数(従属変数)の値を予測する式を重回帰式といい，特に１次関数で表わすものがよく使われる．		 表１
　 トヨタ自動車
y		 円/usドル
x1		 日経平均
x2
2007年1月4日 8,090 119.0199 17,354
2007年1月5日 7,900 118.5899 17,092
2007年1月9日 7,870 119.4 17,238
2007年1月10日 7,690 119.65 16,942
2007年1月11日 7,660 120.4499 16,838
･･･ ･･･ ･･･ ･･･
図1

※　左で求めた重回帰式(4)は，ｙとx1，ｙとx2各々別に求めた単回帰式の和ではない．
■(1)導入としてイメージ作りのために･･･数学的（代数的）に解いてみる
○　上の方程式を実際にExcelで求めて見よう

※　定数 a11，a12，a13，･･･，a33，b1，b2，b3　が与えられているとき，ｘ，ｙ，ｚ を未知数とする連立方程式
a11x + a12y +a13z = b1
a21x + a22y +a23z = b2
a31x + a32y +a33z = b3
は行列を用いて
と書けるが，係数行列の逆行列が存在すればこれを両辺に左から掛けて
が解となる．
※　はじめに，Excelの統計関数では説明変数が多変数になる場合に下図のように一連の範囲で指定する必要があるため，左端に目的変数，続いて説明変数1，説明変数2，･･･と必要に応じて列を追加して行けるようにしておくとよい．（Excel統計関数の引数は予測するｙの範囲を先に書き，説明変数ｘ1･･･の範囲を後に書くようになっている．実務上はその方が使いやすくなる．）
目的変数ｙ 説明変数x1 説明変数x2 ･･･ 説明変数xn
y1 x11 x21 ・・・ xn1
y2 x12 x22 ・・・ xn2
・・・ ・・・ ・・・ ・・・ ・・・
ym x1m x2m ・・・ xnm
　これは連立方程式の右辺と左辺を入れ替えて，
b1 = a11x + a12y +a13z
b2 = a21x + a22y +a23z
b3 = a31x + a32y +a33z
の形で書いているのと同じことになる．
(1)　本題に戻って，次の表のように日付順にデータを作成しておく．
　 トヨタ自動車
y		 円/usドル
x1		 日経平均
x2
2007年1月4日 8,090 119.0199 17,354
2007年1月5日 7,900 118.5899 17,092
2007年1月9日 7,870 119.4 17,238
2007年1月10日 7,690 119.65 16,942
2007年1月11日 7,660 120.4499 16,838
･･･ ･･･ ･･･ ･･･
(2)
Σｙ=an + bΣx1+cΣx2　･･･(1)
Σyx1=aΣx1 + bΣx12+cΣx1x2　･･･(2)
Σｙx2=aΣx2 + bΣx1x2+cΣx22　･･･(3)
の３つの方程式を（この段階では係数ａ，ｂ，ｃが未知数）を作るためにあらかじめ，次の和を準備する．
Σｙ ｎ Σｘ1 Σｘ2
Σｙｘ1 Σｘ1 Σｘ1＾2 Σｘ1ｘ2
Σｙｘ2 Σｘ2 Σｘ1ｘ2 Σｘ2＾2
例えばΣｙの欄には =SUM(B3:B47)などと書く．
ｎの欄には =COUNT(B3:B47)などと書く．次のような係数行列ができる．
　 A B C D E
･･･ ･･･ ･･･ ･･･ ･･･ ･･･
54 　 359230 45 5396 784701
55 　 43084915 5396 647244 94111418
56 　 6266956012 784701 94111418 13689672141
(3)　右の解説のようにして逆行列を作る．
(4)　右の解説のように行列の積 A-1b を作る．
(5)　結果が次のように出る．
-1205.071976
15.69689815
0.418952291
これより，a=-1205.07，b=15.6969，c=0.4190
　したがって，y = -1205.07 + 15.6969x1 + 0.4190x2
が求まる．		 ○　行列の積の簡単な解説は［このページ］
○　行列の積
をExcelで求めるには
=MMULT(左から掛ける行列,右から掛ける行列)　が使える．
　 A B C D E F
1 1 2 　 -2 　 0
2 3 4 　 1 　 -2
上のようにA1〜B2に２×２行列が入力されていて，これにD1からD2の２×１行列を掛けたものをF1からF2に出力したいとき
(1)　まずＦ1のセルに次のように記入する．(直接書き込むまたはメニューから挿入→関数→数学/三角で選択する)
=MMULT(A1:B2,D1:D2)
　これによりF1に1つの成分だけが書き込まれる．
(2)　答をExcelでいう配列の形（数学でいう行列の形）にするには，(1)の結果が書き込まれた後，積の結果の形２×１型に合わせてF1からF2までをドラッグで選択して（マウス形状十字のときに引っ張るのではない）反転表示にしてから，次のように「数式バー」と呼ばれる画面上のｆxと書かれた欄にマウスをあてて，Ctrl キーと Shiftキーの両方を押しながらEnterキーを押す．
　
○　逆行列の簡単な説明は［このページ］
○　正方行列の逆行列をExcelで求めるには
=MINVERSE(元の行列の範囲)　が使える．

逆行列を求める計算
をExcelで行うには，
　 A B C D E F G
1 1 1 0 　 　 　 　
2 -2 1 2 　 　 　 　
3 2 3 1 　 　 　 　
　上のようにA1〜C3に３×３行列の成分が入力されているとき，この行列の逆行列をE1からG3に出力するには
(1)　E1に　=MINVERSE(A1:C3)　と記入する．（直接入力またはメニューから「挿入」→「関数」→「数学/三角」で選択し，入力範囲としてA1〜C3までを指定する．）
(2)　以上の操作で，セルE1に(1,1)成分が一つだけ書き込まれるので，次にこれを配列にして3×３の行列にする：
　そのためには，(1)の結果が書き込まれた後，逆行列３×３型の形に合わせてE1からG3の範囲をドラッグにより反転表示させてから，行列の積のときと同様に，数式バーにマウスを当ててCtrl+Shift+Enterとする．
■(2)実務に近い形で･･･最小二乗法で係数を求めてみる
　右図1において，赤や青で示した点は，実測値とする．

　これに対して，
y=a+bx1+cx2
の形の平面モデルを当てはめ，モデルから求まる予測値と実際の測定値との誤差がなるべく小さくなるように係数 a , b , c を求める．

　誤差としては，理論値の y (図で灰色の○で示したもの)と測定値の y (平面よりも上にあれば青で，下にあれば赤で示した)との「差の二乗」を考え，これらの総和が最小になるようにする．

　右図2のようなデータについて，次の操作を行う．
(1)　B51からD51に定数項 a ，係数 b , c の初期値（仮の値）を準備する．
(2)　F3に（予測値-測定値）2 の計算式を書き込む．
=(B$51+C$51*C3+D$51*D3-B3)^2
(3)　F3の式をコピーし，F4〜F47に貼り付ける．
(4)　F49にこれらの総和の計算式を書き込む．
=SUM(F3:F47)

　以上で準備ができたので，Excelのソルバーを用いて，最小二乗法で最適解 a ，b , c を求める．
(5)　メニュー→「ツール」→「ソルバー」→
目的セル：$F$49
目標値：最小値
変化させるセル：$B$51:$D$51
オプションとしては，次の程度に設定するとスムーズに行くようです．
　　反復回数：1000
　　精度：初めは緩く0.0001程度にし，一旦収束してから精度を上げる
　　反復結果の表示：チェックを入れる（うまくいかないときに何度も「継続」ボタンで漸近していける）

(6)　「実行」ボタンを押し，何度か「継続」ボタンを押すと，解が求まる．		 ※　Excelのソルバーの使い方は，このページ参照
図1
図2

■(3)場面理解ができたら･･･実務に使える「分析ツール」で求めてみる
上で求めた重回帰式の係数は，　
メニューから「ツール」→「分析ツール」→「回帰分析」として
入力　Y範囲を「トヨタ自動車」にB2：B47
入力　Ｘ範囲を「円/USドル〜日経平均」にC2：D47
ラベルにチェックを入れる　
によって出力される表のうち下端・左端の
　 係数
切片 -1205.071976
円/usドル 15.69689815
日経平均 0.418952291
の部分となっているので，「何を求めているかが分かる」ようになれば，以後は単にツールを用いれば求められる．

※　次の？部分の値を重回帰式(4)を求めずに，値として直接求めるには　（適当なセルを選んで）
=TREND(既知のｙ，既知のx，新しいx，定数)
と記入すればよい．（関数TREND()では既知のxに２つ以上の系列を指定して重回帰の予測値を計算できる．引数に指定する範囲は次の表の背景色に対応している．定数は省略または1でよい．）
目的変数 説明変数１ 説明変数２
480 6300 1554
562 5300 1504
580 8200 1446
1060 14900 2642
1398 18300 4063
1715 22600 4065
2230 23300 5620
5300 53200 13418
？ 30600 6793

※　ここで得られる重回帰式は，「当たらずといえども遠からず」「そこそこの予測」となっていますが，日経平均が出てから特定銘柄の株価が求まっても現実にはあまりうれしくない方が多いと考えられるので，説明変数と目的変数の選択にもう一工夫必要（二工夫も三工夫も必要：簡単なはずがない）であることが分ります．
■問題■
　別添ファイル（　toyota.xls　）Sheet1に上で扱った株価のファイルがあるので，これを用いて上記の結果を出せ．（株価は，http://quote.yahoo.co.jp/　による）
　また，Sheet2の電力会社の売上高，従業員数，総資産の概数から電力会社の売上高を従業員数，総資産で表わす重回帰式を作り，電力会社9の売上高を推定せよ．　　　　答え：予想値2707(十億円)･･･本物の公表値は2600（十億円）
確率統計のメニューに戻る 高校数学のメニューに戻る