エクセル【バブル図】近似曲線の書き方について

質問

エクセルでのバブル図の近似曲線の書き方について教えてください。
X・Y・バブルのサイズの３個の項目を入れて、バブル図を書きました。
このばぶるに対して、右クリックから近似曲線の追加で近似曲線をかいたのですが、バブルのサイズがうまく反映されておりません。具体的なエクセルを画像添付します・この中で黒の線がエクセルの近似曲線になりますが、バブルのサイズが考慮されているならば、赤の線（手書きのオートシェイプ）のような形になるかと思います。バブル図の近似曲線にはバブルサイズは反映されないのでしょうか。また反映させる方法はないでしょうか。よろしくお願いします。

回答No.2

keithin

あー，スミマセン，ちょっと安易に回答しすぎました。回答１は撤回します。

たとえば
http://homepage1.nifty.com/gfk/square_solver.htm
などを参考にして，重み付き最小二乗法のやり方で近似係数を算出してみて下さい。

手元でさくっと計算してみた所では
Ｙ=0.446Ｘ+25.2
のような結果になりました。

他の近似で定式化した場合でも，全く同じ手順で計算できます。

回答No.1

keithin

挿入した近似曲線を右クリックして禁じ曲線の書式設定からオプションで数式を記入させてみると，エクセルが考慮している近似曲線は（Ｘ，Ｙ）関係式でしかない事が判ります。

そこで敢えてバブルサイズも重み付けしたいのでしたら，関数を使って別途近似計算を行う事も出来ます。
＃特殊な近似をしたいときは，あなたのヤリタイ事に合わせて下記回答をチューニングして下さい。

ただし，それをグラフ化しようとすると「Ｚ軸（重み軸）」が必要になってくる（言い換えると，近似「直線」に「バブルサイズ」を載せる事はできない）ので，仮に重みはゼロに戻した状態での近似値を計算させてみます。

作成例
Ａ列　Ｙ値　A2からA6まで
Ｂ列　Ｘ値　B2からB6まで
Ｃ列　重み　C2からC6まで
Ｄ列　Ｘ値再掲　=B2のように
Ｅ列　計算重み　全部ゼロを記入
Ｆ列　近似値

F2:
=TREND($A$2:$A$6,$B$2:$C$6,D2:E2)
F6までコピー

Ｅ列の値を変えると，各重みにおける近似値が計算できます。
適宜グラフに追加し，近似直線
Ｅ列にＣ列の値を再掲すれば該当するＸと重みによる推定値を計算することも出来ますので，これを「近似バブルチャート」の元データにすることも出来ますが，Ｚ軸（重み軸）が無いので表示しても何の事か理解出来ないグラフになります。

注意）
Excel2003以降のエクセルで計算しないと，計算が違っている場合があります。

補足

早速のご回答ありがとうございます。
教えて頂いたやり方は、線形近似のみ適用可能でしょうか？
近似曲線を指数関数や対数関数とした場合も同様のやり方でおこなえますでしょうか。
よろしくお願いします。