Excelで自由に近似曲線を引く方法【ソルバーを使用したフィッティング-ガウス関数】

この記事ではExcelのソルバーツールを利用して、データに近似曲線をつける方法について解説します。

Excelグラフの近似曲線では表現できない…、この式でフィッティングしたい！と思う人向けです。

1：データのフィッティングまでの手順

・ソルバーアドインの追加
・データのグラフ化 (可視化) と近似式の決定 (重要)
・近似させたい式とデータのフィッティング (ソルバーの実行)

2：ソルバーアドインの追加

Excelにソルバーアドインを追加する方法です。すでに入れている方はスルーして大丈夫です。
Excel2013の画像ですが基本的にはどのバージョンでもあまり変わりません。

1．Excelファイル→オプションをクリック

2．アドイン→右下の設定をクリック

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

正常に追加されると下の画像のようにデータリボンの右端にソルバーが表示されます。
上手く出ない場合は一度Excelを閉じて再起動してみてください。

ソルバーの追加は以上で完了です！

3：データのグラフ化と近似式の設定

いきなりフィッティングを行う前にまず手元にあるデータをグラフにします。 (データの可視化)

これは初めて扱うデータでは必ずやっていただきたい作業です。
理由はグラフにすることでデータを視覚的にとらえることができ、使用すべき適当な近似式をイメージしやすいからです。

例えば下の例では上に凸の二次関数のようなデータですが、数字だけ見て直線の式でフィッティングしてしまい、式がデータの分布に合っていない状態です。

こういった問題は元データを可視化していればまず発生しないミスなので面倒でも一度確認することをお勧めします！

今回フィッティングしてみるサンプルデータのデータとグラフ化したものが下図です。

このデータも数字だけ見ていると全く近似式が頭に浮かんできませんよね？
ですが、可視化してみると正規分布みたいなデータだなあとわかりますね。
このようにデータの可視化は簡単ですが非常に重要なテクニックです。

グラフを見てこのデータは正規分布のような式でフィッティングするのがよさそうと分かりましたので正規分布の式でフィッティングに進みます！

４：モデル式 (近似式)の入力と元データとの誤差の計算

このステップでは、モデル式と元データの差を計算したセルを用意してソルバーでフィッティングする前処理を行います。

まず初めに使用する式を空いているセルにメモしておきます。
今回の式はこちらのガウス関数を使用します。

a：y軸の最大値、b：yが最大となるときのx座標、c：正規分布の横幅

aが大きいほど山の頂点が高く、bが山の頂点の位置、cが大きいほど細長く、小さくなると半円のような形になると簡単にイメージしてください！

それでは近似式と式から導いた近似値などを元データと同じシートに併記していきましょう。

こちらの配置は慣れてきたら自分の使いやすいようにカスタマイズしても大丈夫です！

a、b、cの値は適当な値を入れておいてください。この部分をソルバーがフィッティングしてくれます。

重要なところは、元データと近似値の差の二乗値の列、差の合計のセルを用意することです。

ここまで進んだら、元データと近似値を同じグラフに表示しておきましょう。

近似曲線が元データと一致していないことが分かります。
この近似曲線をソルバーが元データに近くなるよう計算してくれます！

5：ソルバーで近似式のフィッティング

ここまでのステップでソルバーの実行に必要な前処理を完了しましたので、計算を実行します。

・データ→ソルバーをクリック

以下の図のようにソルバーのパラメータにセルを選択or入力します。

入力が完了したら解決をクリックします。

計算が無事完了すると上記のウィンドウが出てきます。OKを押してグラフを確認しましょう！

元データに近似した曲線が表示されていることが分かりますよね！

a、b、cの値が差の合計が最小になるよう変化していますね。
何度かソルバーを実行し値が変動しなくなれば値が安定しています。
このようにソルバーは与えられた式と元データが最も近似するよう変数を計算してくれる非常に強力なツールです！！
このほかに計算時に制約条件も書けることができます(aの値を10～12の間でとどめるなど)。

応用すれば売り上げの予測や予算の割り振りの最適化などにも活用可能です！！

ソルバーを実行する際の注意点に関してはまた記事を追加します！ (初期値問題)

まとめ

1．ソルバーアドインを追加する

2．元データをグラフ (可視化)にして最適な近似式のモデルを立てる

3．近似値と元データの差と差の合計セルを作成し、ソルバーで最小値となるよう計算する。

以上のステップを実行して最適なモデルを作成してください！

独学以外で学習したい場合はオンラインの動画講座もお勧めです。

スポンサーリンク