Excelのソルバーを使ったカーブフィッティング 非線形最小二乗法

実験データを理論式にあてはめて、もっとも可能性の高い、あり得そうな理論曲線（直線）を引くことをフィッテング（fitting）という。この方法としては最小二乗法が使われることがほとんどである。

■　最小二乗法

最小二乗法とは、グラフ上で実験データから理論曲線まで縦軸に平行に線を引き、その線の長さの二乗を全データについて合計したものが最小になるような理論曲線を求める方法である（間違えやすいが、理論曲線に垂直に下した線の長さではなく、縦軸に平行に引いた線の長さを考える）。これで、もっともあり得そうな曲線が求まるのは、理論曲線（真の値としよう）から、dの距離だけ外れる確率はexp(-d²)に比例すると考えるからである。上記の線の長さの二乗の合計が最小になれば、exp(-d²)を全データについて乗じたものは最大となり、もっとも確率の高い、あり得そうな曲線ということになる。

■　直線へのフィッティングは容易

この最小二乗法、曲線でなく、直線にあてはめるのは比較的簡単である。Excelでグラフ（散布図を選ぶ）を描いた後、「近似曲線の追加」で、グラフの「種類」は「線形近似」を選び、「オプション」タブで、「グラフに数式を表示する」、「グラフにR2乗値を表示する」にチェックを入れればよい。R二乗値は文字通り、二乗の数字が表示されているので注意。平方根をとったものがR値、いわゆる相関係数である。また、理論的根拠がはっきりしないのに、「多項式近似」や「累乗近似」を選んではならない。

しかし、あてはめる理論式が直線でなく、曲線になる場合は苦労することが多かった。自作のプログラムを書く人もいるし、そういう用途専用のソフトウェアを使う人も多い。古い話だが、1990年の段階では曲線にあてはめる非線形最小二乗法はPCでは無理で、大型計算機に置いたFORTRANのプログラムで解析していたものである。Simpson法、Marquardt法などがあった。

現在は、Excelでも容易に計算できる。以下は、そういった非線形の最小二乗法、すなわち理論式が直線でない場合のフィッテングをExcelを使って行う方法のノート。

■　Excelでソルバーを使えるようにする

Excel 2007の場合は、「データ」タブの右側に表示される「分析」グループに、「ソルバー」（Solver）が表示されている必要がある。表示されていない場合は、左上のオフィスボタン→「Excelのオプション」→「アドイン」→「設定」で、「アドイン」のウィンドウが開くので、「ソルバーアドイン」にチェックを入れて、OKする。

Excel 2003の場合は、「ツール」メニューで「アドイン」を選び、表示されたボックスで「ソルバーアドイン」にチェックを入れて、OKする。

ソルバーは、パラメータを変化させて、与えた式が目標値になるところを探し出す機能をもっている。

■　ピークデータをローレンツ型関数でフィッテング

ピークの形状は、ローレンツ型、ガウス型、またはこれら2つを畳み込んだフォークト関数のいずれかでフィッティングされることが多い。ここでは、もっとも式が簡単なローレンツ型でフィッテングしてみる。

f(x) = h/(1+(x-u)²/w²) + b

ここで、hはピークの高さ、uはピーク位置、wは半値幅、bはバックグラウンドである。バックグラウンドは一定値とした。

A列に横軸の数値、B列に縦軸の数値を読み込む。グラフ（散布図）を描いて、およその形を確認する。ピークの高さh、バックグラウンドの高さb、ピークの位置u、ピークの半値幅wを目分量で読み取って、初期値とする（少しずれてもよい）。

この例の場合は、h=140, u=39, w=0.1, b=30と見積もって初期値とした。

この初期値を使って計算した曲線を以下の操作で、一緒に表示するようにする。すなわち、これらの初期値をローレンツ型関数に代入して求めた値を、C列に記入していく。このとき、初期値をC列に入力するのではなく、

F1セルに140、G1セルに39、H1セルに0.1、I1セルに30
と別のセルに初期値を入力し、これらのセルを固定参照して、C列に式を入力するようにする。

C1セルには　=$F$1/(1+(A1-$G$1)^2/$H$1^2)+$I$1
と入力し、これをC2より下のセルにコピーする。（$をつけた部分は固定参照になるので、コピーしても変化しない。A1だけが変化していくことになる）。これで、A列を横軸、B列、C列を縦軸にしたグラフを描く。F1、G1、H1、I1の値を変えればグラフも自動的に変わる。

次に、D列に、B列とC列の差の二乗（残差二乗）を表示する。すなわち
D1セルに　=(B1-C1)^2
と入力して、D2より下のセルにコピーする。

E1セルにD列の合計（残差二乗和）を表示する。すなわち
E1セルに　=SUM(D1:D351)　　　※ここではD351までデータがあるとした
と入力する。

さて、いよいよソルバーの登場である。

E1の値が最小になるような、F1、G1、H1、I1の値を求めるわけだ。

「データ」タブの「分析」グループの中から「ソルバー」を選び、「目的セル」のところに最小にしたいセル（ここではE1）を選び、目標値は「最小値」を選び（最小になるところを探すので）、変化させるセルは変化させるパラメータの入っているセル（ここではF1からI1）を選ぶ。「実行」をクリックすると、結果が表示されるので「解を記入する」を選べば、残差二乗和が最小となるパラメータがF1からI1に記入される。グラフもフィットするグラフになっているはずである。

初期値がでたらめな場合はでたらめな値に収束する。非線形最小二乗法は厳密に言うと残差二乗和の最小値ではなく、極小値を探索しているためである。