Excelで操る!誤差の絶対値和を最小にする
(ガウスの夢:最小絶対値法)

        

Excelを用いた科学技術計算が第2版になりました 30年10月
大学の教科書にも採択されています。
amazon:Excelで操る! ここまでできる科学技術計算 第2版

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


 
 最小二乗法は目的関数(誤差の2乗和)が最小となる関数の係数を探索する。しかし、次式のように、誤差の絶対値和を最小にするほうがより直接的であり、より良い近似と思われる。
 ドイツの数学者、物理学者、天文学者であるガウスは最小二乗法を考案した。しかしながら、当初ガウスは誤差の絶対値和の最小化を模索したようである。

    目的関数: T(合計) = Σ|Yi-f(Xi)|    ・・・ (1)

      Yi:i番目の座標(Xi)における計測値
      f(Xi):近似関数



 図1 最小絶対値法で計測点列の近似曲線を作成した例のグラフ


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


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

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

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


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

数値解析

楽天カードで2000ptゲット


最小絶対値法は線形計画法(LP)


 (1)式のように絶対値の式があると、目的関数は極端な非線形となり、数学的に取り扱いが困難となる。しかし、次のように、絶対値は等価な2個の線形式に分離できる。

 
 絶対値の不等式:    M ≧ |K|
  等価な2個の線形式:  M≧K, M≧-K

ここで、i番目の座標における誤差を変数(Zi)と定義すると、次式が成立する。

   Zi≧|Yi-f(Xi)|      ・・・(2)

すると、ここでの問題は、次の目的関数と制約条件を持つ線形計画法(LP)問題にモデル化できる。

   目的関数: T=ΣZi
   制約条件: Zi≧Yi-f(Xi), Zi≧-Yi+f(Xi)

線形計画法(LP)問題であればExcelのソルバーで容易に解ける。



最小絶対値法の近似関数


近似関数を次の3次関数で近似する。
  
 f(X) = aX3 + bX2 + cX + d  ・・・・・ (1)

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

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

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

最小絶対値法では、n次関数以外の関数も適応できます。正規分布関数などを検討して下さい。



EXCELで操る!最小絶対値法による近似関数計算


 図2のような、計測点(X,Y)があり、最小絶対値法を用いて、近似関数を求める。

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

(2)計測値(Y)と近似関数f(X)の誤差の査 f(X)-Y,Y-f(X) を計算する。

(3)最後にZに適当な値を設定しT(合計)を計算する。


                                   図2 最小絶対値法(ソルバー実行前)

 メニューバーのツール→ソルバーを選択すると、図3のソルバー:パラメータ設定画面が現れる。ここでは、目標値として最小値を選択し、変化させるセルを近似関数の係数(a,b,c,d)およびZとし、目的セルはT(合計)とする。制約条件として、Zi≧Yi-f(Xi), Zi≧-Yi+f(Xi)を設定する。


             図3 ソルバー:パラメータ設定画面

 実行ボタンをクリックすると、図4のように、近似関数が点列に近づく。


                            図4 最小絶対値法(ソルバー実行後)



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

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

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


 





EXCELを用いた科学技術計算

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

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

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



〒673−0036 兵庫県明石市松江62−14
(有)ゴッドフット企画



感想をお聞かせ下さい



JR福知山線 脱線事故シミュレーション

脱線事故シミュレーションもEXCELで
(Yahoo Japan 掲載)


Copyright (C) GODFOOT 2010 All Rights Reserved.