エクセルのソルバーを用いた最小二乗法

Excelを用いた科学技術計算が第2版になりました　30年10月！
大学の教科書にも採択されています

amazon：Excelで操る! ここまでできる科学技術計算第2版

楽天： Excelで操る! ここまでできる科学技術計算 [ 神足　史人 ]

　
　最小二乗法は目的関数(誤差の２乗の合計)が最小となる係数を探索します。

この機能はエクセルのソルバー機能と完全に一致します。

微分やマトリックス計算などの高等数学を用いて、最小二乗法を活用するのが一般的です。

しかし、エクセルのソルバーを用いると、簡単な四則演算の知識のみで、最小二乗法が使いこなせます。

また、制約条件付き最小二乗法、重み付き最小二乗法も容易に設定できます。

エクセルのソルバーを用いた最小二乗法を大いに活用して下さい。

　目的関数(誤差の絶対値の和)を最小とする最小絶対値法のページもつくりました。
　フランスの数学者、物理学者、天文学者であるガウスは最小二乗法を考案した。
しかしながら、当初ガウスは誤差の絶対値和の最小化を模索したようである。

Excelで操る！誤差の絶対値和を最小にする(最小絶対値法)

国産汎用数理計画法パッケージ Numerical Optimizer(旧NUOPT)　～業務に最適化を取り入れる方のプロツール～
無料セミナー開催！

ポートフォリオ最適化と数理計画法

こちらの著者の一人である田辺隆人は NUOPT（現 Numerical Optimizer ）
の開発責任者です．

　　　　　　　図１　最小二乗法で計測点列の近似曲線を作成した例のグラフ

たった1日で即戦力になるExcelの教科書

入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス)

Excel VBAのプログラミングのツボとコツがゼッタイにわかる本

すぐに使える！ Excel関数逆引き辞典

データを「見える化」するExcelグラフ大事典

数値解析

点列の近似関数計算

図２のような、誤差を含むデータ（X,Y）を計測したとします。
近似関数をつくり、計測点以外のデータを取り出したい場合、最小二乗法がよく使われます。

　　　　　　　　　　　　　　　　　　　　　　　　　　図２　計測点列の例題

最小二乗法の近似関数

一般的には最小二乗法の近似関数はｎ次関数にします。ここでは、３次関数で近似しようと思います。

近似関数を
　　
　f(X) = aX³ + bX² + cX + d　　・・・・・　(1)

とおきます。

次数が高いほど、点列に近い近似関数がつくれます。しかし、次数が高すぎると誤差を拾う可能性があり、
ほどほどの次数にする必要があります。

最小二乗法は点列の近辺を通るなめらかな近似関数をつくります。そのため、誤差を含むデータの近似方法
としては良い方法です。

計測点列を絶対に通る近似関数を作りたい場合は、ラグランジュの補間法、スプラインの補間法等をつかいます。

最小二乗法では、ｎ次関数以外の関数も適応できます。周期性のある点列の場合、３角関数などを検討して下さい。

最小二乗法の説明

計測点がXの時、Yと　近似関数　f(X) の誤差　（Y-f(X))を計算します。
誤差の２乗　（Y－ｆ(x) )² が小さければ、Yとf(X)は近い値となります。
ほんとうは、２乗より、絶対値　|Y-f(X)|をとる方が良いのでしょうが、絶対値を使用すると、計算処理が複雑となる。

一箇所の計測点だけが近い値となるだけではだめです。近似関数をすべての計測点に近づける必要がある。

そのため、全ての点で、誤差の２乗を合計します。

　T（合計）　＝　Σ　( Y - f(X) ) ² 　　　・・・・・(2)　　　Σ(シグマ）：　全ての点で合計するという記号

このT(合計)が最小となる関数の係数（a,b,c,d）を求めます。

係数が求まると、任意のXでの近似値
　f(X) = aX³ + bX² + cX + d
が求まります。

EXCELで誤差の2乗を合計をする

それでは、計測点と近似関数の誤差の２乗を合計します。

図３のように、エクセルシート上に近似関数の係数(a,b,c,d)を設定し、関数f(X) を計算します。

次に、計測値(Y)と近似関数f(X)の誤差の２乗 (f(X)-Y)² を計算します。

最後に誤差の２乗を集計し、T（合計）を計算して下さい。

試行錯誤で、近似関数の係数(a,b,c,d)を変更しても、近似関数を点列に近づけることは困難ですね。

　　　　　　　　　　図３　エクセルによる誤差の２乗合計の計算

　　　　　　　　　　図４　計測点列と適当な係数を設定した近似関数による近似曲線のグラフ

EXCELのソルバー

　EXCELのソルバーは変化させるセルを指定し、目的セルを最小（最大）にする機能です。
ソルバーはあまり使われていないようで、標準ではインストールされません。
ソルバーをインストールしていない場合、追加インストールをして下さい。

ここでは、変化させるセルを近似関数の係数(a,b,c,d)とし、目的セルはT(合計)となります。

メニューバーのツール - ソルバーを選択して下さい。

　　　　　　　　　　図５　ソルバー：パラメータ設定画面

図５の画面で、目的セル、変化させるセル領域を選択し、目標値は最小値を選択して下さい。
実行ボタンをクリックすると、計算結果がEXCEL上に反映されます。

EXCELのソルバー実行結果

点列に近い近似曲線が求まりました。

T(合計値)が4.86341より小さい値となる近似関数の係数(a,b,c,d)を見つけた人は、ソルバーに

不具合があると、マイクロソフト社にクレームを出して下さい。

　
　　　　　　　　　　　　　図６　ソルバーの実行結果

　　　　　　　　　　　　　　図７ソルバーの実行結果に基づく、近似曲線のグラフ

制約条件付きの最小二乗法

X = 5 の計測点を必ず通る等、近似関数に制約条件を設定したい場合があります。

その場合、ソルバーのパラメータ設定画面で制約条件を追加して下さい。

　　　　　図８　ソルバー：パラメータ設定画面で制約条件の追加

ソルバーを実行すると、図９のように、X = 5 の時のYとf(X)の値が一致します。

　　　　　　　　図９　制約条件付き最小二乗法の計算結果

　　　　　　　　　　　　　　図10　制約条件付き最小二乗法の計算結果グラフ

重み付き最小二乗法

　最小二乗法の目的関数　T（合計）　＝　Σ　( Y - f(X) ) ² に重み(W)を付けると、

目的関数は

　　T（合計）　＝　Σ　W・( Y - f(X) ) ² 　となります。

　近似曲線により近づけたい計測点がある場合、その点の重み(W)を大きく設定します。

計測点の両端のみ重みを１０とし、他の点の重みを１に設定し、

ソルバーを実行すると、次の結果が得られました。

　　　　　　　　　　図11　重み付き付き最小二乗法の計算結果

　　　　　　　　　　図12　重み付き最小二乗法の計算結果グラフ

図１２のグラフより、近似曲線は両端の計測点により近づきました。その代り、中央近辺の
誤差が大きくなっています。

　　グラフ作成方法の参考　：　エクセル(EXCEL)でグラフを作ってみよう

エクセルを用いた最小二乗法(点列の近似曲線を求める)

ソルバーを用いず、連立方程式を解くことにより点列の近似曲線を求めます。

エクセルのソルバーを用いた科学技術計算

　PCR検査(プール方式）の最適まとめ人数を計算してみました。

　　　　　　陽性率毎のPCR検査最適まとめ人数のグラフ

　　エクセルのソルバーで操る！PCR検査(プール方式)の最適まとめ人数計算

エクセルのソルバーを用いた最小作用の原理に基づく質点の軌道計算　
大発見、エクセルのソルバーで質点の軌道計算ができる。

EXCELを用いた科学技術計算

EXCELを用いた構造力学(材料力学)計算

Excelを用いた地球温暖化計算

Excelを用いた確率・統計計算

〒６７３－００３６　兵庫県明石市松江６２－１４
(有)ゴッドフット企画

感想をお聞かせ下さい