Excelを用いた最小二乗法

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

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

たった1日で即戦力になるExcelの教科書 入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス) Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 すぐに使える！ Excel関数逆引き辞典 データを「見える化」するExcelグラフ大事典 数値解析

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

一般的にはｎ次関数で近似します。ここでは、３次関数で近似しようと思います。 ３次関数　f(X) = aX3 + bX2 + cX + d　　　・・・・・　(1) 次数が高いほど、点列に近い関数がつくれます。しかし、高すぎると誤差を拾う可能性があり、ほどほどの次数にする必要があります。 最小二乗法は点列の近辺を通るなめらかな関数をつくります。そのため、誤差を含むデータの近似方法としては良い方法です。 計測点列を絶対に通る近似関数を作りたい場合は、ラグランジュの補間法、スプラインの補間法等をつかいます。 最小二乗法では、ｎ次関数以外の関数も使えます。周期性のある点列の場合、３角関数などを検討して下さい。

Excelで学ぶ統計解析―統計学理論をExcelでシミュレーションすれば、視覚的に理解できる エクセルこれだけ関数事典90―ハンディで見やすい! Excelでマスターする ビジネスデータ分析 実践の極意

(1)式の３次関数を(2)式に代入すると次の(3)式となる。 　T ＝ Σ（ Y - aX3 - bX2 - cX - d )2　　　・・・・・ (3) (3)式のTは係数(a,b,c,d)の関数と見ることができる。 (3)式のTを係数(a,b,c,d)で微分した式 が　０ のとき、(3)式のTは最小となる。 すなわち 　　δT/δd = 0 　　δT/δc = 0 　　δT/δb = 0 　　δT/δa = 0 となる。 これを解いて、整理すると、４元連立方程式（下の行列計算式）となる。 　４元連立方程式は下記マトリックス(行列)式で表記できる。 　 　　A　×x = b　　　　・・・・・ (4) この行列式(4)を解き、係数a,b,c,dを算出する。 一般的に、ｎ次式の関数で近似すると、n+1元連立方程式となる。

EXCELを用い(4)式のマトリックス(行列)AとベクトルBの成分を計算し、マトリックス(行列)AとベクトルBを設定します。 　　　　　　　　　　　　　　　　　　　　　　　　　　図３　(4)式のマトリックス(行列)とベクトルを計算するエクセルシート

EXCELで連立方程式　A × x = b を解いてみよう。 �@　逆マトリックス( A-1 ) の計算 　任意位置に逆マトリックス　A-1　のエリアを確保し、 関数 MINVERSE( マトリックス A )を設定する。 注）　複数エリアに関数を設定する場合　Ctrl Shift キーを押しながら、 　　　Enterをキー入力する。 �A　逆マトリックス( A-1 ) ×　ベクトル( b ) の計算 　　任意位置に解　( x ) のエリアを確保し、 関数　MMULT(　逆マトリックス( A-1 ),　ベクトル( b )　)を設定すると、 解　( x ) が求まる。 　　　　　　　　　　　　　　図４　(4)の連立方程式(行列計算)を解くエクセルシート

連立方程式を解くことにより、近似関数の係数(a,b,c,d)が求まった。 この係数を使い、関数を計算すると、計測点列に近い近似曲線が求る。 　　　図５ (1)式の近似関数を計算するエクセルシート 　　　　　　　　　　　　　　　　図６ (1)式の近似曲線と計測点列を比較したグラフ 　　グラフ作成方法の参考　：　エクセル(EXCEL)でグラフを作ってみよう

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