教えて!gooにおける不適切な投稿への対応について

すみませんがお助けください。
任意の3次元座標(Xi,Yi,Zi)がn個あるとします。
Xi>0,Yi>0,Ziは正負混合です。
上記n個の座標から近似平面式Z=aX+bY+cを構成する乗数a,b,cを求めたいのですが下記理由により最小二乗法は使えません。
最小二乗法の場合は
D=Σ(Zi-aXi-bYi-c)^2が最小になるa,b,cを求めることになりますが、今回求めたいa,b,cは、
各座標のdi=(Zi-aXi-bYi-c)、すなわちn個のdiについて『diの最大値-diの最小値』が最も小さくなる場合のa,b,cです。
なお『diの最大値-diの最小値』についての補足ですが
diの最大値=5,diの最小値=2の場合
『diの最大値-diの最小値』は5-2=3となり、
diの最大値=5,diの最小値=-2の場合
『diの最大値-diの最小値』は5-(-2)=7となります

お手数かけて恐縮ですが宜しくお願い申し上げます。

gooドクター

A 回答 (2件)

>ここの再度「ツール→ソルバー~」の操作を不要にして、Xi、Yi、Zi の一部数値を変更すると、即a,b,cの数値が変更される方法はありますでしょうか?



これはわかりません。マクロとかVBAとかで出来るのかどうか??ですが、エクセルに詳しい方の回答に期待するか、あるいは再度ここで質問されてみたらいかがでしょうか?

プログラムを組めばエクセルソルバーのような手作業は省けますが、この問題は最小自乗法ではないのでニュートン法などが使えず結構難しそうです。多少時間がかかっても問題なければ乱数を使ったモンテカルロ法か遺伝的アルゴリズムのような手法が使えるかもしれませんが。。。
    • good
    • 1
この回答へのお礼

最初のご回答が期待以上の内容で、大いに業務効率UPにつながりました。ここから先は自分で色々とやってみることにします。
本当にありがとうございました。

お礼日時:2008/11/21 11:35

エクセルのソルバーを使えば下記の手順で a、b、c が求まります。


以下は EXCEL2002の場合で、データ数は n = 20 とします。

1)B~D 列にデータ Xi、Yi、Zi を入力する。
(第1行は xi 等のラベルを入れ、データは第2行から第23行まで)
2)K2~K4 に可変パラメータ a、b、c の初期値(例えばゼロ)を入れる。
3)E2 に =D2-$K$2*B2-$K$3*C2-$K$4 を入れて、E3~E23 にそれをコピーする。   
4)G2 に =MAX(E2:E23) と入力する。
5)G3 に =MIN(E2:E23) と入力する。
6)G4 に =G2-G3 と入力する。
7)G4 を選択した状態でツール→ソルバーをクリックする。
(ここでソルバーのウインドウが開き、目的セルが $G$4 となっている。)
8)ソルバーウインドウの目標値を◎最小値に選ぶ。
9)ソルバーウインドウの変化させるセルを選択した後にワークシートのK2~K4を選択する。
10)ソルバーウインドウの実行ボタンをクリックする。
11)最適解が見つかったら◎解を記入するを選択してOKボタンをクリックする。
12)上記の結果 K2~k4 に解 a、b、c が得られる。

蛇足ながらツールの中にソルバーが見当たらない時はアドインする必要があります。
またこの問題では問題ないようですが最適解に収束しない場合は K2~K4 の初期値を変えます。
    • good
    • 0
この回答へのお礼

ありがとうございました。おかげさまで解決しました。

ところで一旦「ツール→ソルバー~」の操作をしてa、b、cを求めた後に、Xi、Yi、Zi の一部数値を変更した場合は再度「ツール→ソルバー~」の操作をしてa,b,cを求めることになりますが、ここの再度「ツール→ソルバー~」の操作を不要にして、Xi、Yi、Zi の一部数値を変更すると、即a,b,cの数値が変更される方法はありますでしょうか?もしあればご教示いただきたく存じます。

お礼日時:2008/11/20 11:10

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このカテゴリの人気Q&Aランキング