Excelで操る！ソルバーの最小化機能を用いた重回帰分析

Excelを用いた科学技術計算が第2版になりました　30年10月！

amazon：Excelで操る! ここまでできる科学技術計算第2版

楽天： Excelで操る! ここまでできる科学技術計算 [ 神足　史人 ]

　　　　　　　図１　重回帰分析の例題

統計学が最強の学問である

たった1日で即戦力になるExcelの教科書

入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス)

Excel VBAのプログラミングのツボとコツがゼッタイにわかる本

数値解析

重回帰分析とは

　目的変数yとそれに影響を与えるいくつかの説明変数xi（i=1,2,3,・・・m)の関係を(1)式のように設定し、変数の計測値を用いて
(1)式の係数ai(i=0,1,2,・・・,m)を推定し、推定式を作るのが重回帰分析である。

　　推定式　：　y = a₀+a₁・x₁+a₂・x₂+ ・・・+a_m・x_m 　　　　・・・　(1)

次式の目的関数が最小となる係数を探索することにより、係数を推定する。

　　目的関数　：　J = Σ(yj - yj' )² 　　　　・・・ (2)
　　　　　　　　　　　
　　　　　　　　　　　Σ：計測点数(n)で集計する。
　　　　　　　　　　　 yj：j番目の目的変数の計測値
　　　　　　　　　　　 yj'：j番目の説明変数の計測値を推定式(1)に代入した推定値

　図１のように、ある15人のクラスで体重、身長、胸囲を計測した。ここでは、身長と胸囲から体重を推定する式をつくることとする。

(1)式はあくまで仮定であるため、真に適合しているかどうか不安である。(1)式の適合ぐあいを検定するのか次式の寄与率(R²)である。

　　　　寄与率　：　R² = 1 - Ve/Vc　　　　　　・・・　(3)

　　　　　　　　　　　　　　Ve：目的変数の計測値の分散
　　　　　　　　　　　　　　Vc：誤差(計測値-推定値)の分散
　　　　　　　　　　　　　　R：重相関係数
　　　　　　　　　　　　　　0≦R²≦1

　R2が1に近いほど、推定式はよく適合しているといえる。

Excelで操る！ソルバーを用いた重回帰分析

　図２のように、推定式の係数に適当な値を設定し、目的関数(誤差の二乗和)を計算する。
ちなみに、Excel関数は二乗和：SUMSQ(セルの範囲)、平均：AVERAGE(セルの範囲)、分散：VAR(セルの範囲)である。

　　　　　　　　　　　　　　　　　　　　　　　　図2 重回帰分析(ソルバー実行前)

　メニューバーのツール→ソルバーを選択すると、図３のソルバー：パラメータ設定画面が現れる。ここでは、目標値として最小値を選択し、変化させるセルを推定式のとし、目的セルは誤差の二乗和とする。制約条件はない。

　　　　　　　　　　　　　図3 ソルバー：パラメータ設定画面

　実行ボタンをクリックすると、図４のように、推定式の係数が求まる。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　図４　重回帰分析(ソルバー実行後)

エクセルのソルバーを用いた科学技術計算

Excelで操る！誤差の絶対値和を最小にする(最小絶対値法)

エクセルのソルバーを用いた最小二乗法

エクセルで操る！ソルバーを用いた主成分分析

EXCELを用いた科学技術計算

EXCELを用いた構造力学(材料力学)計算

Excelを用いた地球温暖化計算

Excelを用いた確率・統計計算

〒６７３－００３６　兵庫県明石市松江６２－１４
(有)ゴッドフット企画

感想をお聞かせ下さい