もめんの格闘日記

主にPowerPointを中心としたPCのTips的なものを書いていく予定です。

【Excel】ソルバーを使用した近似曲線の引き方(2次関数の例)

f:id:momen40:20200731204241p:plain


この記事では、Excelでソルバーを使用した近似曲線の引き方について、書きたいと思います。

例として、2次曲線、ガウス関数、2つのガウス関数が重なったグラフで近似曲線を求めてみます。

 

便利な機能なので、実験データを扱っている人には役に立つと思います。

では、どうぞ。

(Excel2016で動作確認しています)

 

元データを用意する

特に説明はいらないと思いますが、まずは元データのxの値と、yの値を用意します。

後で必要になるので、グラフも表示しています。

f:id:momen40:20200724232440p:plain

 

 

ソルバーのアドオンを有効にする

初期状態だとソルバーが使えない状態の為、アドインなるものを有効にします。 

手順は以下になります。

 

ファイルから

f:id:momen40:20200724232806p:plain

 

オプションを選択 

f:id:momen40:20200724233313p:plain

 

アドイン→設定をクリック 

f:id:momen40:20200729210154p:plain

 

ソルバーアドインにチェックを入れる→OK 

f:id:momen40:20200724233720p:plain

 

これでソルバーが使えるようになります。

 

 

近似曲線のパラメータを用意する

元データがいかにも2次関数っぽいので、2次曲線で近似します。

式としては下のようになり、a, b, cを変化させて元データに近くなる値を求めます。

 y=ax^2+bx+c  

 

まず、変化させるパラメータとしてa, b, cを用意します。

f:id:momen40:20200729211827p:plain

仮にa=1, b=2, c=3としました。

 

a, b, c式に入れた値を元データの横に計算します。

f:id:momen40:20200729212239p:plain

この例ではC2に「=$H$3*A2^2+$I$3*A2+$J$3」と入力しています。

 

式とパラメータの対応は下のようになっています。

f:id:momen40:20200729213045p:plain
C3以降はC2をコピーします。

 

 

(元データ-近似曲線)の2乗和を求める

個人的には、この操作が最初 よくわからなかったので、少し説明してみます。

 

近似曲線を求める際、ソルバーでは単一セルの値を最小化します。

でも、元データも近似曲線も複数の値を持っているので、両データの差をとって合計します。

差が正負に振れるとなんだかよくわからなくなるので、差を2乗して足し合わせます。

故に「(元データ-近似曲線)の2乗和」となります。

この説明でわかるかな。。。

 

操作としては、B列、C列の各値で差を求めて2乗します。

f:id:momen40:20200729214233p:plain

この例ではD2に「=(B2-C2)^2」と入力しています。

D3以降はコピー。

 

D列の合計を計算します。

f:id:momen40:20200729214526p:plain

この例ではF3に「=SUM(D2:D102)」と入力しています。

ソルバーではこの合計値が最小になるよう計算してくれます。

 

 

ソルバーを実行する

ついにソルバーを実行します。

 

データのタブの左端にあるソルバーを選択します。

f:id:momen40:20200729214930p:plain

 

ウィンドウが開くので、パラメーターを設定します。

f:id:momen40:20200729215652p:plain

  • 目的セルとしては、2乗和を求めたF3を設定
  • 目標値は「最小値」に設定
  • 変数セルとしては、a, b, cのパラメータを入れたH3, I3, J3を設定

 

最後に解決を押せば計算が開始されます。

f:id:momen40:20200729221619p:plain

結果の画面。

a, b, cのパラメータが変更され、グラフの近似曲線が元データにほぼ重なっているのがわかります。

 

 

終わりに

この記事はソルバーの使い方のさわりの部分について書きましたが、いかがだったでしょうか。

ガウス関数での例も入れたかったですが、長くなった為、別記事で書きます。

 

今回の例はうまくいっていますが、初期値の設定でうまくいかないこともあります。

その辺りの詳細は別の方の記事を参考にして頂ければと思います。

 

ソルバーを最初使ったとき、取っ付きにくさを感じたので、できるだけわかり易く説明できたらと思い、この記事を書きました。

誰かのお役に立てたのであれば幸いです。