エクセルで解く最小二乗法

エクセルではグラフの機能で近似曲線の追加というのはありますが、任意の式で近似したい場合や、近似式の係数を取得したい場合にはエクセルの近似曲線の機能だけでは物足りない場合があります。
そんなときにエクセルでまじめに最小二乗法で解く方法を紹介します。

 

まずは、最小二乗法を解くとき、逆行列を用いるところまではご理解願います。
詳しくはこちらで紹介しています。

 

最小二乗法
一般式による最小二乗法

 

また、Excelで行列を計算する方法はこちらを参照下さい。

 

行列の積、逆行列、転置行列の計算

 

以下では円の最小二乗法を例にとって説明をしたいと思います。

 

円の最小二乗法を解くときの円の一般式を

 

X² + Y² + AX + BY + C = 0

 

としたときの行列の式は

 

 

です。
まず、行列の式に使われている∑の値を求めます。
∑はエクセル関数の「SUM」関数を用いればよいので、簡単に求まります。

 

 

まず、行列の式の各値を各セルに書いておきます。
次に逆行列を求めるのですが、エクセル関数に「MINVERSE」というのがあります。

 

逆行列を表示したい複数のセル（下記の例では３×３のセル）を選択します。
次に

 

=MINVERSE(逆行列を求める複数のセル)

 

と入力します。ここでは文字を入力するだけでEnterキーは押さないこと

 

 

 

で、次が最大のポイント！
通常ではEnterキーを押したいところですが、行列の計算では

 

Ctrlキー  + Shiftキー + Enterキー

 

を同時に押します。
これで、逆行列の値が求まります。

 

 

あとは行列の積が計算できれば、求めたいA,B,Cの値を求めることができるのですが、
行列の積の計算にはエクセル関数の「MMULT」という関数を用います。

 

逆行列を計算したときと同様に行列の積の結果を表示したいセル複数のセルを選択し、

 

=MMULT(行列１,行列2)

 

を入力します。ここでも最後はEnterキーだけではなく

 

Ctrlキー  + Shiftキー + Enterキー

 

を押すので注意して下さい。

 

 

これで求めたかった未知数A,B,Cが求まります。

 

 

今回は円の近似なので、A,B,Cの値を用いで円の中心座標(a,b)、円の半径rを求めるには

 

a = -A / 2
b = -B / 2
r = SQRT(a² + b² – C)

 

なので、

 

 

となり、円の近似式を求めることができます。

 

このエクセルによる円の最小二乗法のファイルはこちら↓

LSM_Circle.xls

 

また、一般的なｎ次式による最小二乗法を解いたのファイルはこちら↓

n_LeastSquare.xls

 

←使える数学へ戻る