近似式（z=ax+by+c）を取得したい

質問

エクセルの散布図だとｘ軸－ｙ軸で散布図を書き、近似式も自動で作成できますが、これを３次（ｘ軸－ｙ軸－ｚ軸）に拡張して、
ｚ＝ａｘ＋ｂｙ＋ｃ
のような近似式を得たいと考えています。データの特性上、線形（直線）に限定して問題ありません。(ｘ，ｙ，ｚ)のデータは十分揃っています。そのようなツール、もしくは計算式がありましたら教えてください。

よろしくお願いします。

回答No.3

HarukaV49 · ベストアンサー

例えば、エクセルで以下のようにデータが入力されているとします。
　（等幅フォントでご覧ください）
　　　　　A　　　　　　　B　　　　C　　　　　　D
----------------------------------------------------
1 |　　　　a　　　　　　b　　　　　c
2 |　0.235982387　-0.057736587　17.62863368　42.53502347
3 |
4 |　　　　x　　　　　　y　　　　　z
5 |　　　12.87　　　　392.4　　　 2.0　　　 15.92097601
6 |　　　 8.09　　　　254.4　　　 2.1　　　 7.559989098
7 |　　　12.01　　　　 36.0　　　21.0　　　 6.842069547
8 |　　　58.94　　　　193.2　　　20.0　　　 0.1464799
9 |　　　10.93　　　　255.2　　　 2.0　　　 12.06550891
. |　　　.....　　　　.....　　 .....　　　 .....
. |　　　.....　　　　.....　　 .....　　　 .....
n |　　　.....　　　　.....　　 .....　　　 .....
　　　　　　　　　　　　　　　　（nはデータ数+4）

＜入力する値＞
A2 = 1.0
B2 = 1.0
C2 = 1.0 (初期値)
A5:Cn 与データ

＜入力する数式＞
D2 = SUM(D5:Dn)
D5 = (A$2*A5+B$2*B5+C$2-C5)^2
　（ 以下、n行までCopy&Paste ）

ここで、ソルバーを起動して、
　目的セル;　　　 $D$2
　目標値；　　　　最小値
　変化させるセル; $A$2:$C$2
として、実行します。
A2:C2が求める係数です。
例示頂いたデータでは、
　Z = 0.235982387X - 0.057736587Y + 17.62863368
と、求まります。
これは、平面の方程式ですがこれでよかったでしょうか？ご確認ください。

簡単に、解説します。
D5:Dnに入力された式が与式 Z=aX+bY+c のそれぞれ具体値結果の２乗です。
２乗してあるところが１つのミソで、その和を求めてそれが最小値になっているとき、
目的の値が求まります。まずは、適当に簡単な問題でご検証ください。
（２乗する以外にも複数の自由度があります）

ソルバーのインストール法に関しては、適当なサイトをご参照ください。

お礼

ありがとうございました。解決しました。

回答No.2

HarukaV49

＞そのようなツール、もしくは計算式がありましたら教えてください。

エクセルには、ソルバーという多変量解析を行うツールが付属しています。
ちょっと、インターフェースがいまひとつで、使い方に戸惑うかもしれませんが、
ソルバー自体はエクセル本体がおまけで付いて来たのではないかとも思えるほどの
高性能なツールです。

具体的な使用方法等は、WEB検索していただければたくさん見つかりますので
とりあえず省略させていただきます。
なお、具体例を示していただけば、解析例を提示させていただくことも
可能かと存じます。

補足

(Z, X, Y)の値が
(12.87, 392.4, 2.0)
(8.09, 254.4, 2.1)
(12.01, 36.0, 21.0)
(58.94, 193.2, 20.0)
(10.93, 355.2, 2.0)
：
とあります。
これに対して近似式、Z=aX+bY+c を求めたいのです。
よろしくお願いします。

回答No.1

inara

数学カテですが、同じ質問（平面の計算方法　http://oshiete1.goo.ne.jp/qa2802443.html）がありますので、ご参考まで。

「OK Wave/教えて!Goo」の検索欄に「回帰」を入れて探してもいろいろ出てきます。