エクセルの近似曲線

質問

エクセルの近似曲線を表示させたのですが
この近似曲線だけを取り出して別のグラフに表示させる
ことは可能でしょうか？

回答No.4

oyaoya65

補足の回答です。

＞この近似式のデータ値そのものも取り出したいのですが、やはりエクセルの機能の問題で無理なのでしょうか？

質問者さんのデータの場合では無理なようです。

その理由は、Excelでは
近似式のグラフは正しく書きますが、近似式の多項式の係数を保存する段階で係数データの記憶する段階で、係数の絶対値の大きさがほぼ同じでないと、係数の有効数字を他の数値の絶対値に桁の範囲を合わせる処理をして、係数の有効桁数を減らして多項式の係数を記憶し表示してしまいます。係数は桁落ちした場合の近似多公式で、変数が1から大きくずれる場合は計算誤差が更に増加します。

近似式で正しく計算できる場合は、A列とB列のデータの絶対値がほぼ同じ範囲におさまり、かつ近似式の変数が１に近いこと（この影響は近似多公式の次数が増えるほど顕著になる）が条件です。

ですから、A#2で私が示した例題は上記の条件を満たしていますが、質問者さんのExcelのデータの例は、上記の条件から極端に満たしていませんので、近似計算式の係数の有効桁数落ち、近似多項式変数が1に比べて数100倍でしかも3次多項式近似となっていることで、近似計算が正しくできない分けです。

Excelでは有効数字6桁ぐらいしか計算していませんので近似多公式のxの３乗項とxや定数項の絶対値の桁数の差はxの絶対値が１より桁数が外れるほど計算桁数の差が大きくなり、近似多公式の計算結果が有効数字６桁くらいでは正しく計算できないということですね。

お礼

返事が遅くなり申し訳ございません。
違う方法でやってみることにしました。

回答No.3

oyaoya65

質問者さんの例ではA列のデータの絶対値の数値に対してB列のデータの絶対値の数値が桁数的に5桁小さな数であるため近似式は間違いないですが、その係数の有効桁数の３次の項が２桁しかありません。つまり３次多項式での近似式の３次の係数の計算誤差が大きく影響しているわけです。近似式で逆にB列の計算が行えないと言うことですね。

EXCELでの数値計算の有効桁数が少ないことがこのようなことが起きる原因ですね。
この方法は諦めた方がいいですね。

近似曲線を描いたグラフエリアを左クリックして、編集→コピー
でクリップボードにグラフを取り込んで、それをカーソルで指定した位置に、編集→貼り付け
で貼り付けてやれば、別のグラフになります。それを加工編集（データのマーカーを表示させない）してやれば当初の質問の目的は果たせるでしょう。

グラフィックエリアの加工編集は、グラフィックエリアをデータを右クリック→データ要素の書式設定→パターンタブ→マーカーなし→OK
でデーターのマーカーを表示させないようにします。

これで近似式だけのグラフが取り出せたことになります。

補足

ありがとうございました。この方法で近似式だけの
グラフが取り出せました。最初の質問に書いてなかったのですが、この近似式のデータ値そのものも取り出したいのですが、やはりエクセルの機能の問題で無理なのでしょうか？

回答No.2

oyaoya65

#1です。

＞私の明らかに近似曲線に対応した値が算出されませんでした。私の場合多項式近似を使ったのですが、直線でしかこの方法はできないのでしょうか

私は2次式、3次式の多項式近似で確認しましたが問題なくできましたよ。

やったエクセルのテーブルのサンプルをつけます。
確認してみてください。
____A_____B______C
1__1.1___2_______2.061884
2__2.5___1.8_____1.513
3__3.5___1.5_____2.0027
4__4_____2.8_____2.5231

グラフ上に表示させた近似式（二次多項式近似の場合）
y = 0.3674x2 - 1.7147x + 3.5035

C1の関数式
=0.3674*A1^2 -1.7147*A1 +3.5035
C2以降、C1をドラッグで一気にC4までコピー
コピー内容は（C2～C4まで）
=0.3674*A2^2 -1.7147*A2 +3.5035
=0.3674*A3^2 -1.7147*A3 +3.5035
=0.3674*A4^2 -1.7147*A4 +3.5035
ですね。

ただし、滑らかな曲線で結んでいますので2次式近似になっていません。
もっと正確に描きたい場合は下記のように近似関数のXを細かく取りなおしてプロットすればいいですね。
ここで、C6は近似式から
=0.3674*A6^2 -1.7147*A6 +3.5035
以下ドラッグで一気にコピーです。
以下をプロットすればより正確な近似曲線が描けますね。

_____A______B______C
6___1____________2.1562
7___1.2__________1.974916
8___1.4__________1.823024
9___1.6__________1.700524
10__1.8__________1.607416
11__2____________1.5437
12__2.2__________1.509376
13__2.4__________1.504444
13__2.6__________1.528904
14__2.8__________1.582756
15__3____________1.666
16__3.2__________1.778636
17__3.4__________1.920664
18__3.6__________2.092084
19__3.8__________2.292896
20__4____________2.5231

確認してみてください。

補足

詳しい解説ありがとうございました。確かにサンプルでは
きちんとできました。しかし私のデータでは明らかに値が違うのです。下記にデータを示します。
____A_____B___________C
1__400___0_______ -2172
2__398___-0.05519_____-2090.3372
3__396___-0.02877_____-2058.91
4__394___0.015273_____-2027.28

y = 0.0013x3 - 1.5817x2 + 625.48x - 82442
=0.0013*A1^3 - 1.5817*A1^2 + 625.48*A1 - 82442
としたのですが上のように-2172となり明らかにグラフと違います。

回答No.1

oyaoya65

私のやり方
A列にXの値
B列にYの値
があるとします。

散布図でX-Yのグラフを描いた後、近似曲線（直線）を表示し、近似式も表示させます。
この近似式をコピペ（コピーアンドペースト）でC1等に貼り付け、yを削除,xをA1で置き換えます。
C列のC2以降はC1をドラッグで参照コピーをします。
そうすると近似曲線に対応した値が計算されて表示されます。

ABC列を散布図でグラフ表示させた後、B列のグラフ上をクリックして＜Delete＞キーでB列のグラフを削除します。
これで近似曲線だけのグラフとなります。

補足

ご回答ありがとうございました。やってみたのですが
私の明らかに近似曲線に対応した値が算出されませんでした。私の場合多項式近似を使ったのですが、直線でしかこの方法はできないのでしょうか・