この記事では、Excelでソルバーを使用した近似曲線の引き方について、書きたいと思います。
ソルバーの導入→実行までの流れと、2次曲線のグラフで近似曲線を求めた例を紹介します。
便利な機能なので、実験データを扱っている人には役に立つと思います。
では、どうぞ。
(Excel2016で動作確認しています)
元データを用意する
特に説明はいらないと思いますが、まずは元データのxの値と、yの値を用意します。
後で必要になるので、グラフも表示しています。
ソルバーのアドオンを有効にする
初期状態だとソルバーが使えない状態の為、アドインなるものを有効にします。
手順は以下になります。
ファイルから
オプションを選択
アドイン→設定をクリック
ソルバーアドインにチェックを入れる→OK
これでソルバーが使えるようになります。
近似曲線のパラメータを用意する
元データがいかにも2次関数っぽいので、2次曲線で近似します。
式としては下のようになり、a, b, cを変化させて元データに近くなる値を求めます。
まず、変化させるパラメータとしてa, b, cを用意します。
仮にa=1, b=2, c=3としました。
a, b, c式に入れた値を元データの横に計算します。
この例ではC2に「=$H$3*A2^2+$I$3*A2+$J$3」と入力しています。
式とパラメータの対応は下のようになっています。
C3以降はC2をコピーします。
(元データ-近似曲線)の2乗和を求める
個人的には、この操作が最初 よくわからなかったので、少し説明してみます。
近似曲線を求める際、ソルバーでは単一セルの値を最小化します。
でも、元データも近似曲線も複数の値を持っているので、両データの差をとって合計します。
差が正負に振れるとなんだかよくわからなくなるので、差を2乗して足し合わせます。
故に「(元データ-近似曲線)の2乗和」となります。
この説明でわかるかな。。。
操作としては、B列、C列の各値で差を求めて2乗します。
この例ではD2に「=(B2-C2)^2」と入力しています。
D3以降はコピー。
D列の合計を計算します。
この例ではF3に「=SUM(D2:D102)」と入力しています。
ソルバーではこの合計値が最小になるよう計算してくれます。
ソルバーを実行する
ついにソルバーを実行します。
データのタブの左端にあるソルバーを選択します。
ウィンドウが開くので、パラメーターを設定します。
- 目的セルとしては、2乗和を求めたF3を設定
- 目標値は「最小値」に設定
- 変数セルとしては、a, b, cのパラメータを入れたH3, I3, J3を設定
最後に解決を押せば計算が開始されます。
結果の画面。
a, b, cのパラメータが変更され、グラフの近似曲線が元データにほぼ重なっているのがわかります。
終わりに
この記事はソルバーの使い方のさわりの部分について書きましたが、いかがだったでしょうか。
ガウス関数での例も入れたかったですが、長くなった為、別記事で書きます。
今回の例はうまくいっていますが、初期値の設定でうまくいかないこともあります。
その辺りの詳細は別の方の記事を参考にして頂ければと思います。
ソルバーを最初使ったとき、取っ付きにくさを感じたので、できるだけわかり易く説明できたらと思い、この記事を書きました。
誰かのお役に立てたのであれば幸いです。