2009年09月15日

ソルバーを使った最小二乗法/Excel/データ分析

最小二乗法(Least squares)とは、実際の測定で得られたデータ等を一次関数や二次関数、対数曲線などの関数を用いて近似する際、実際のデータとの差の二乗(残差の二乗)の総和が最小になる様に関数の係数を想定する方法です。手計算で行うと偏微分方程式等を解かなければなりませんが、Excelの"ソルバー"を用いると瞬時に繰り返し計算を行って係数の推定が行えます。

直線で近似する場合
まずサンプルの元データを用意します。
20140426-701.jpg

この様なX,Yのデータから最小二乗法を用いて直線で近似してみます。解りやすいようにグラフでも表示してみます。
このデータは y=xのデータ(直線)から、データを適当にばらつかせたものです。
では直線の式 f(x)=ax+b の式を当てはめてみます。元々y=x ですから、a=1、b=0としてみます。
20140426-702.jpg

列Cに f(x)=ax+b (a=1,B=0) のデータを計算させています。計数aの値はC28、bの値はC29に入力し、f(x) の計算はそれらを引用して計算させます。(ソルバーではこの部分を最適化させます)
20140426-703.jpg

そして赤線でグラフを書いています。また列Dには(f(x)-y)^2のデータつまり残差の二乗を計算させています。(具体的には列Cの値から列Bの値を引いてから二乗しています)
20140426-704.jpg

そして、27行目にその総和(合計)を算出しています。
20140426-705.jpg

元々このデータからばらつかせたものですからこの赤い直線で十分近似しているようにもみえますが、残差の総和は"124.86"あります。
では早速、ソルバーを使って最小二乗法を実行してみましょう。

ソルバーの実行
まず、メニューの"データ"から"ソルバー"を選択します。ソルバーが表示されない場合は"オプション"の"アドイン"から、ソルバーを設定してください。
20140426-706.jpg

するとこのようなパラメータ設定画面が開きます。
ここでは、"目的セル"と"目標値"、"変化させるセル"を設定します。
20140426-707.jpg

まず、"目的セル"は残差の総和のセルにします。
20140426-708.jpg

変化させるセルは、a,bの値のセルにします。
20140426-709.jpg

目標値を最小値(つまり残差の総和を小さくする)にして実行します。
20140426-710.jpg

すると、自動的にa,bの値が変わり、グラフも書き替りました。
20140426-711.jpg
ソルバー実行後の画像aが0.905965、bが0.382456になり残差の総和は"113.906"となりました。

残差を更に小さくする
残差を小さくするとはどういうグラフになるでしょうか?
それは青いばらついている点それぞれに赤線が近づく曲線のグラフになります。つまりうねった曲線になります。
一般的に近似式の次数を大きくすると残差は少なくなります。先ほどのデータを3次式で近似してみます。
近似式は f(x)=ax^3+bx^2+cx+d とし、ソルバーを実行します。
20140426-712.jpg

すると、このように赤いグラフが多少うねっています。残差の総和は"113.8683"となり、直線で近似した場合より小さくなりました。
この近似曲線は元のデータを近似してると言えるかどうか?単に残差を小さくするためにばらつきを拾っているとも言えます。
データを分析するために最小二乗法を使って近似式を推定する場合は、対象のデータがどのようなものか、何のために近似するかなどの条件によって適切な式f(x)を想定する必要があります。近似式f(x)の設定が間違っているとソルバーを使っても適切な近似は不可能です。
例えば、f(x)=a/x+b にしてしまうと。。。
こんなことになってしまいます。
20140426-713.jpg


posted by haku1569 at 23:56| Comment(0) | Excelデータ分析 | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。