EXCELを用いた最小２乗法

図１　最小２乗法で求めた近似曲線のグラフ

やさしくわかるExcel VBA（ブイビーエー）プログラミング改訂版 すぐに使える！ Excel関数逆引き辞典 データを「見える化」するExcelグラフ大事典 知ったモン勝ち! 私はこの方法で資格を30取得しました ―無敵のラジカル・マスター学習法 生物と無生物のあいだ 無理なく続けられる 年収10倍アップ勉強法 「続ける」技術 「その他大勢」から一瞬で抜け出す技術 過小評価されているあなたを救うスピード・ブランディング

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

Excelで学ぶ統計解析―統計学理論をExcelでシミュレーションすれば、視覚的に理解できる よくわかるMicrosoft Office Excel 2007 (基礎) よくわかるMicrosoft Office Excel2007 エクセルこれだけ関数事典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)でグラフを作ってみよう

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