ＸＹＺ平面の近似式についてご教示ください

すみませんがお助けください。
任意の３次元座標（Ｘｉ，Ｙｉ，Ｚｉ）がｎ個あるとします。
Ｘｉ＞０，Ｙｉ＞０，Ｚｉは正負混合です。
上記ｎ個の座標から近似平面式Ｚ＝ａＸ＋ｂＹ＋ｃを構成する乗数ａ，ｂ，ｃを求めたいのですが下記理由により最小二乗法は使えません。
最小二乗法の場合は
Ｄ＝Σ(Ｚｉ－ａＸｉ－ｂＹｉ－ｃ）＾2が最小になるａ，ｂ，ｃを求めることになりますが、今回求めたいａ，ｂ，ｃは、
各座標のｄｉ＝(Ｚｉ－ａＸｉ－ｂＹｉ－ｃ）、すなわちｎ個のｄｉについて『ｄｉの最大値－ｄｉの最小値』が最も小さくなる場合のａ，ｂ，ｃです。
なお『ｄｉの最大値－ｄｉの最小値』についての補足ですが
ｄｉの最大値＝５，ｄｉの最小値＝２の場合
『ｄｉの最大値－ｄｉの最小値』は５－２＝３となり、
ｄｉの最大値＝５，ｄｉの最小値＝－２の場合
『ｄｉの最大値－ｄｉの最小値』は５－(－２)＝７となります

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

No.2 回答者： boobee0125 回答日時： 2008/11/20 22:29

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

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

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

この回答へのお礼

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

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

No.1 回答者： boobee0125 回答日時： 2008/11/19 16:51

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

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

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

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

この回答へのお礼

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

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

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