工具顕微鏡で測定した測定点の座標から、エクセルにて円の方程式を最小二乗法で求める方法をお教え下さい。
過去の質問から、「楕円」についてのご回答があり、参照させていただき、自分なりに応用(xa+yb+c=-(x^2+y^2)として)してみたのですが、測定点の座標から得られる行列P、及び、その転置行列P'との積の逆行列とP'と()内の式の右辺から得られる行列との積を計算することができません。(3点のデータでは計算できましたが、Pを入力し、P'を求め、…と云った段階的な計算方法を採りました。)
宜しくお願いします。
No.3ベストアンサー
- 回答日時:
【1】Excelの基本的機能を使って計算を行うworksheetを作る方法を説明します。
●A列にp(n)(x座標の測定値)、B列にq(n)(y座標の測定値)を並べます。説明のためにn=1~10としておきましょう。
●C列とD列にそれぞれ2p(n)と2q(n)を入れ、またE列には1を入れ、F列に(p(n)^2 + q(n)^2)を入れます。このためにC列1行目に「=2*A1」、D列1行目に「=2*B1」、E列1行目に「1」、F列1行目に「=A1^2+B1^2」と入力します。そしてC列1行目からF列10行目までを選択して、<下方向へコピー>をやる。
●H列1行目からK列3行目に正規方程式の係数表を作ります。
まずH列1行目に「=MMULT(TRANSPOSE(C1:E10),C1:F10)」と入力します。でもこれだけだと、エラーになりますよね。
H列1行目からK列3行目を選択すると数式バーに今入力した式「=MMULT(TRANSPOSE(C1:E10),C1:F10)」が表示されます。この式にカーソルを合わせて一度クリックし、それからCTRLキーを押しながらEnterキーを押します。(Macintoshならリンゴマークキーを押しながらenter。)
すると数式バーが「{=MMULT(TRANSPOSE(C1:E10),C1:F10)}」という表示に変わり、行列が表示されます。
●H列5行目からJ列7行目に、H列1行目からJ列3行目の3x3の行列に対する逆行列を作ります。このために、H列5行目に「=MINVERSE(H1:J3)」と入力します。そして、H列5行目からJ列7行目を選択し、数式バーに表示された「=MINVERSE(H1:J3)」をクリックして、それからCTRLキーを押しながらEnterキーを押します。(Macintoshならリンゴマークキーを押しながらenter。)
* なおここで、「H列5行目からJ列7行目を選択」する際には、まずH列5行目のセルをクリックし、次にshiftキーを押しながらJ列7行目のセルをクリックします。(先にH列5行目のセルをクリックしないと式が表示されません。)
●M列1行目からM列3行目に、a,b,cの値を計算します。M列1行目のセルに「=MMULT(H5:J7,K1:K3)」と入力し、M列1行目からM列3行目を選択し、数式バーに表示された「=MMULT(H5:J7,K1:K3)」をクリックして、それからCTRLキーを押しながらEnterキーを押します。(Macintoshならリンゴマークキーを押しながらenter。)
●M列5行目に、rを計算します。M列5行目のセルに「=SQRT(M3+M1^2+M2^2)」と入力するだけです。
【2】相関係数
二つのデータ系列の間で定義されるものですから、この問題の場合にはそのままでは馴染みません。必要なのは「フィッティングした円がどのぐらい、データと旨く合っているか」でしょう。そこで、各データ(p(n),q(n))と推定した中心(a,b)との距離と、推定した半径rとのずれを残差と考えるのが適当かと思います。すなわち
ε(n) = √((p(n)-a)^2+(q(n)-b)^2) - r
です。
●N列1行目から10行目にこの表を作ってみましょう。N列1行目に「=sqrt((A1-$M$1)^2+(B1-$M$2)^2)-$M$5」と入力し、N列1行目からN列10行目までを選択して、<下方向へコピー>をやる。
これがどのぐらいのばらつきであるかを見るために、平均(=average(N1:N10))と標準偏差(=stdev(N1:N10))を計算してみると良いでしょう。絶対値が最大のものを計算するにはセルに「=max(abs(N1:N10))」と入力して、数式バーに表示された式をクリックして、それからCTRLキーを押しながらEnterキーを押します。(Macintoshならリンゴマークキーを押しながらenter。)
No.2
- 回答日時:
直交座標で表された点の集合 (p(n),q(n)) に円をフィッティングするんですね。
円を表すモデルは
(p(n) - a)^2 + (q(n) - b)^2 = r^2
です。これを展開して
p(n)^2 - 2ap(n) + a^2 + q(n)^2 - 2bq(n) + b^2 - r^2 = 0
整理すると
(p(n)^2 + q(n)^2) = 2ap(n) + 2bq(n) - (a^2 +b^2 - r^2)
ここで、
c = -(a^2 +b^2 - r^2)
とおけば
(p(n)^2 + q(n)^2) = a(2p(n)) + b(2q(n)) + c
という、a,b,cに関する一次方程式になります。これがn=1,2,3....,N (N=測定点の個数)
だけ並んでいる連立一次方程式です。
こいつを最小二乗の意味で解く方法は、既におわかりなんでしょうか?(Excelの使い方の話に過ぎないんですが)
で、a,b,cが分かり、a,b,cからrが計算できます。
この回答への補足
ご回答ありがとうございます.
連立1次方程式をExcelで解く方法が良く分からずに苦労しております。
また、相関係数の導き方もお教え願えればとお願いする次第であります。
No.1
- 回答日時:
最小二乗法ではありませんが、次のようにすれば、頭をまったく使わず
目的を達成することができると思います。
まず、[ツール]->[アドイン]でソルバーを入れる
(あらかじめ、入っていれば、この操作は必要ありません。
ソルバーは非線形最適化問題を解くための道具です)
考え方としては、近似円の式と測定点との誤差の2乗
の総和をソルバー機能をつかって最小化させます。
白紙のシートに、次のようにセルに入力します
カンマごとにセルを変えて入力してください。
(xi,yi)は測定点の座標、
(Cx,Cy)は近似円の中心座標(初期値を適当にめぼしをつけて入れてください)
Rは近似円の半径(初期値を適当にめぼしをつけて入れてください)
-------------------------------------------------
x1,y1,Cx,Cy,R,=((a1-c1)^2+(b1-d1)^2-e1^2)^2
x2,y2,=c1,=d1,=e1,=((a2-c2)^2+(b2-d2)^2-e2^2)^2
x3,y3,=c2,=d2,=e2,=((a3-c3)^2+(b3-d3)^2-e3^2)^2
x3,y3,=c3,=d3,=e3,=((a4-c4)^2+(b4-d4)^2-e4^2)^2
......
xn,yn,=c(n-1),=d(n-1),=e1,=((an-cn)^2+(bn-dn)^2-e1^2)^2
=sum(f1:fn)
--------------------------------------------------
これでソルバーをつかって「=sum(f1:fn)」の入れてあるセルを
目的とするセルとして選択し、c1,d1,e1のセルを変化させるセルとして
選択し、最小化すれば、Cx,Cy,Rがわかります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 電気工事士 6.6kVケーブル単芯325sq-1.5kmの遮蔽銅テープ抵抗値は何Ω? 1 2023/05/02 21:06
- 数学 第4問 座標平面上に3点 A(1, 1),B(1, 5), C(7, 3) を頂点とするABCがある 2 2022/10/01 14:53
- 数学 複利毎月積み立てで年利からの計算方法 3 2023/01/11 15:56
- 投資・株式の税金 一般口座で同一銘柄の総平均法のことで 1 2023/02/27 22:08
- 数学 数2Bの数列の問題です。 自分は、 まず数列 an=ar^(n-1)と置き こちらの問題の、y= の 1 2022/07/07 16:26
- Visual Basic(VBA) vba 等間隔の列に対しての計算 6 2022/05/17 20:15
- 相続税・贈与税 相続税の、土地の計算法に関して、の質問です。 4 2022/07/05 23:12
- 物理学 誤差の問題についでです。 yがy=A+Bxの上に乗ると予想でき、以下の4個の測定値 (2,3.2±1 2 2023/04/25 00:54
- Excel(エクセル) アウトラインの小計のやり方 1 2023/03/20 11:51
- 数学 写真(URL)の問題の(1)についてですが、 円c1は 2点を通ると書いてあることから、 2点の座標 5 2023/02/14 19:44
このQ&Aを見た人はこんなQ&Aも見ています
-
外出時に「待たせる妻」vs イライラする「待つ夫」は日本だけ?見習いたい海外事情
夫の家事参加に積極的なイメージのある海外でも、同様の事例はあるのか。結婚カウンセラーの佐竹悦子さんに伺ってみた。
-
Excelを使用して円弧の半径を最小二乗法で求めたい
Excel(エクセル)
-
円の最小二乗法の公式
数学
-
空間上の測定された点群から最小二乗法により円(中心、直径)を算出する方法
数学
-
-
4
最小二乗法による直径及び、真円度について
数学
-
5
平面の計算方法
数学
-
6
三次元の座標の回転をExcelで計算したい。回転行列?
Excel(エクセル)
-
7
多点を通る円の中心
数学
-
8
エクセルの散布図グラフで、横比1:1の図形を作画したい
その他(Microsoft Office)
-
9
円の最小二乗法について
数学
-
10
機械計測 (三次元や真円度) に従事する者です。 とある非幾何学的円筒
物理学
-
11
エクセルでXY座標に並べられた数値配列を極座標平面に並び替えるにはどうすれば良いでしょうか?
Excel(エクセル)
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
確率の問題 数学と実生活と
-
過去に 「ii) f(z)=1/(z^2-1) r...
-
整数問題 兎に角 難問です 千葉...
-
「an=(n-1)/(n+1)のときlim[n→∞...
-
こちらの式はtan(z)のローラン...
-
これの極限値を求める問題で、 ...
-
指数関数と階乗。グラフで表し...
-
△ABCの辺BCの延長上の点Dを通る...
-
数学Aについて、4でも6でも割り...
-
返信の続きはありますか
-
iphone の自分の声が高すぎるん...
-
30分の動画を2倍速で見たら、3...
-
2024.4.7 03:42の質問に対する2...
-
逆三角関数の方程式の問題です...
-
連続的ポストごめんなさい
-
2022年 東京理科大 難易度判定
-
方程式の計算について
-
背景は何
-
【数学・標準偏差σ】標準偏差の...
-
ベクトル3重積
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
整数問題 兎に角 難問です 千葉...
-
えこれわかるひといますか?
-
長方形の分類(幾何学)
-
京都大学理系 過去問 整数問題
-
天孫降臨の神武天皇のY染色体...
-
中学数学の図形の問題です。
-
数学I アホらしい質問なのでそ...
-
正方行列Aについて
-
このルートを外す計算どうすれ...
-
素数についての一考察
-
これて最後どうやりますか??
-
この数学の問題はどうやって解...
-
高校数学の整数問題です。
-
暗闇で2人が出会うには両方動...
-
なんでですか?
-
大学入試の数学で、解答を進め...
-
n^2+n-4032はどうやって解くん...
-
(-1) ^2πってなんで1じゃないん...
-
標準偏差
-
一橋大学過去問 整数 素数 かな...
おすすめ情報