EXCELで最小二乗法(1)

　大学で実験講師をやってるんだけど、理論式や経験式が存在する実験データを最小二乗法を使って近似するということを知らない学生が多いのでちょっと驚いた。といってもサラリーマン研究者でも知らない人が多い。理系でも分野によっては必要性を感じないままで済むこともあるしね。でも知っておいて損はない。特に理系ならなおさら。 　最小二乗法の考え方は至って簡単。まずモデル式を立てて実験値とモデル値の差を求める。この差の合計値が最小になるような定数の組み合わせを見つければそれが近似式になる。しかし、単に差を合計すると正負で相殺されてしまうので、すべて正の数になるように差を二乗してから合計する。これ（差の二乗和）が最小になる定数を求めてあげる。これが最小二乗法による近似。 　例えば次のような実験結果があったとする。 　これが指数関数的に減衰すると推定される場合 こんな式で近似することになる。ここでAは減衰の下限値、Bは減衰量（A+Bが初期値になる）、Cが減衰速度を表す定数。A、B、Cに適当な数値をあてはめてグラフを書くと次のようになる。 　このとき実験値とモデル式の値との差（図の赤線部分）の合計が最も小さくなるようなA、B、Cの組み合わせを見つければそれが近似式になるわけだ。ただし近似値が実験値の上に出るか下に出るかで差の値は正数になったり負数になったりするので単に合計しただけだと変なことになる。だから全部正数になるように差を二乗してから合計して、この合計値が最小になるA、B、Cの組み合わせを見つけよう。ということになる。 　差の二乗和を求める式を立てて最小値を求めればいいんだが、そのためには式を微分して・・・と考えると考えるだけでイヤになる。ていうかできない、オレ。そんなときはパソコンにやってもらうのが一番。何を隠そうパソコンは電子計算機なのだからな。しかも普及率が高いEXCELを使う方法を覚えておけばどこに就職してもOKだ。 　まずA列に独立変数(X)、B列に従属変数(Y)を入力する。次に近似式で使う定数をどこか適当なセルに用意する。この例では指数関数で近似しようとしているので、A、B、Cの３つを用意する。 　次にC列に、A列の値と定数を使って指数関数を計算する列を作る。 　D列にB列とC列の差の２乗を計算する。最後にD列の合計値を求めるセルを用意する。これで準備完了。 　試しにA, B, Cの定数を適当にいじってみる。当たり前だけど、セルの値を変えればモデル式の計算結果が変わるので差の二乗和も変化する。 　ということなのであとはA, B, Cの値を手当り次第に変化させて差の二乗和が最小になる組み合わせを見つけ出せばいいんだが、その気が遠くなるような作業をEXCELに自動的にやってもらおうというわけだ。 　使うのはEXCELの「ソルバー」という機能。ソルバーは、あらかじめ指定したセルの値を変化させて、特定のセルの値が設定した目標になるような値を見つけてくれる機能です。経営の最適化シミュレーションなどで使われるものですが、最小二乗法にも使えます。Windows版のEXCELに標準で搭載されている機能ですが初期状態では隠されているので、まずはソルバー機能を有効化する必要があります。方法はマイクロソフト社のオフィス製品ヘルプのページに書いてあります。EXCEL2007の場合はココ 　ちなみにMac版EXCELにはソルバー機能がついてない。なんでだっ！！　私はMacユーザだ。そんなカワウソなMacユーザには「Solver for Microsoft Excel 2008」。独立したソフトだがEXCELと連動してソルバー機能を提供してくれる。ありがたいことに無料。ダウンロードはhttp://www.solver.com/mac/からどうぞ。ということでMacユーザな私はここからSolver for Microsoft Excel 2008を使って説明するけど、Windows版EXCEL内蔵のソルバーも見た目も機能もほぼ同じなのでたぶんわかると思う。 話が長くなったので続きは明日。

Continue reading...