EXCELで最小二乗法(2)

　昨日の続き。EXCELを使って最小二乗法による近似を行なうには「ソルバー」機能を使う。Windows版Excelには内蔵されているけど、Mac版ExcelにはないのでMacユーザな私はSolver for Mac Excel 2008というソフトを使う。Windows版Excel内蔵ソルバーもSolver for Mac Excel 2008も画面や動作はほぼ同じなのでWinな人も構わず読んでください。 　さて、Excel上に下記のデータを用意する。 A列、B列に実験値 適当なセルにモデル式に使う定数 C列にモデル値 D列に実験値とモデル値の差の二乗 適当なセルに差の二乗和 　そしたらソルバーを起動する。target cell（目的セル）に差の二乗和を計算しているセルを指定、equal to（目標値）は[min]（最小）を指定、Changing cells（変化させるセル）には定数を入力しているセルを指定する。 　これでSolve（実行）をクリックすると、ソルバーは[変化させるセル]の値をちょっとずつ動かしながら[目的セル]の値が[最小]になる組み合わせを見つけていく。本当に画面上でセルの値がガラガラと変化するので、なんか人間味を感じてしまう。 　ソルバーがアレやコレや考えた末に目標を達成すると（今回の場合は[目的セル]が[最小]になったら）ソルバーは終了する。思考結果を残すか、元に戻すか聞かれるので決めてあげる。たいていの場合は[OK]押して思考結果を残せばいい。 　ソルバーによる思考の結果、y=A+B*exp(C*x)のモデル式でA=9.45, B=20.9, C=-0.114とすると実験値を近似できるという結果が得られます。実験値に近似した式のグラフを重ねると次のようになります。 　この結果から、減衰の度合いは-0.114で表され、初期値30.35から最終的に9.45に収束する減衰挙動であると評価することができます。ソルバーを使う時のポイントはあらかじめ与える定数をできるだけそれっぽい値にしておくということ。ソルバーは与えられた値をちょっとずつ動かしながら試行錯誤を繰り返しながら目的セルが最小になる組み合わせを探しますが、正確にはこれは極小値です。初めに与えた値があまりにも不正確だと的外れな組み合わせを導いてしまいます。 　ところで、近似する際にある値だけは固定したい場合があります。そういうときはソルバーでSubject to the Constraints（制約条件）を設定しておけばできる。例えば初期値は実験結果である30に固定したい場合、実験値の初期値B2セルとモデル値の初期値C2セルの値が等しくなるという条件を追加する。 　これでソルバーを実行するとA=9.42, B=20.6, C=-0.112が得られる。グラフにするとy切片がきっちり30になる。 　てな具合で、ソルバーを使うと理論式や経験式への近似計算（計算というより探索）が簡単にできる。理系なら覚えておいて損はない。

Continue reading...