エクセルではグラフの機能で近似曲線の追加というのはありますが、任意の式で近似したい場合や、近似式の係数を取得したい場合にはエクセルの近似曲線の機能だけでは物足りない場合があります。
そんなときにエクセルでまじめに最小二乗法で解く方法を紹介します。
まずは、最小二乗法を解くとき、逆行列を用いるところまではご理解願います。
詳しくはこちらで紹介しています。
また、Excelで行列を計算する方法はこちらを参照下さい。
以下では円の最小二乗法を例にとって説明をしたいと思います。
円の最小二乗法を解くときの円の一般式を
X2 + Y2 + 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(a2 + b2 – C)
なので、
となり、円の近似式を求めることができます。
このエクセルによる円の最小二乗法のファイルはこちら↓
また、一般的なｎ次式による最小二乗法を解いたのファイルはこちら↓
コメント
早速の回答有難う御座います。
確かに得られた近似円との距離で考えれば、算出できそうですね。
ご教示いただいた方法でR2値を算出すること、トライしてみます。
イメージングソリューション Akiraさま
複数の点を通る円の式を求める方法をネットで検索していて、貴兄のHPを見つけ非常に参考になりました。
有難う御座います。
現在､HP掲載の内容を参考に､当方の使用目的に則した形の計算シートをエクセルで作成しようとしているところで、実測（既知）の座標点が4点以上あるとき、算出式のR2値（決定係数）も算出できるようにしようとしていますが、１件、質問させていただきます。
R2値の算出に際しては、HP掲載の円の基本式（X^2＋Y^2＋A･X＋B･Y＋C＝0）から
Y＝（－B±SQRT(B^2－4×(X^2＋A･X＋C))）／2 として
実測のXiに対するYの値（Yi’）を算出しようとしたのですが、
実測のXiの値が近似円の外にくる場合（HP掲載の計算例では､(Xi＝0,Yi＝20)ならびに(Xi＝40,Yi＝20)の点）、SQRの中がマイナスとなってYi’が算出できません（近似円の外にXの値がくるため､不解となるのは当然と云えば当然なのですが）。
このような場合に､R2値を求める良い方法があれば、教えていただきたくお願いします。
※）最初は､Yi’が不解となる点は除いた形でR2値を算出することも考えたのですが、そうすると実態よりも誤差は小さく（R2値としては大きく）表されてしまうと考え問い合わせさせていただくものです。
高畠さん、コメント頂きありがとうございます。
ちょっと外しているかも？しれませんが、私の考えを書くと。。
残差をY座標に関する差として捉えると、解けない点が出てくるので、確かに難しそうです。
これを、「中心からの距離」と「半径」との差として捉えては如何でしょうか？
中心座標からの距離は、求めた中心座標（a,b）と、測定点(Xi,Yi)との距離で求まるので、
この値と半径rとの差を使えば、いけそうな気がします。