Excelグラフの任意の数値出力について

質問

こんばんは、初めて書き込みいたします。

Excelでグラフを作成しました。
近似式ではカーブがうまく乗らなかったので他の方法があればどなたかご教授願えればと思い書き込みました。
具体的には下記の通りです。
ｘ軸　　　　　y軸
0.146---- 25
0.19----- 30
0.323---- 40
0.72----- 50
1.35----- 60
2.265---- 70
3.525---- 80
5.225---- 90
7.44----- 100
9.95----- 110
11.375--- 115
の数値で散布図グラフにすると右肩上がりですが、急に上がってから緩やかなカーブになります。
必要なのは任意のx値の時のy値がでる関数があればということです。

多項式近似5次式の近似曲線を足してみましたが蛇行したカーブになりますので使えません
そこで任意のx値からy値を出せる関数があればと思いました。

ちなみに逆にx軸とy軸を入れ替えて5次式近似を足しましたところカーブはピタリと乗ります。
数式は、y=-2.0887E-9^5+6.6981E-7x^4-7.3059E-5x^3+4.6719x^2-1.3980E-1x+1.6337とこのように出ます。ところがこのやり方ではx値を逆算で求める数式が存在しないようなのです。
このx,y入れj変えた5次式のx値が出る関数でもいいのですが・・・
どなたかご存知でしたらご教授お願いします。

回答No.4

keithin

既出回答でも指摘が寄せられていますが、理論的裏付けのない近似式が利用できるのは、基本あくまでも「内挿」です。
外にはみ出してどこまで使えるかは、近似曲線の書式設定のオプションで「補外」してみることで、ある程度（＝カンジニアリングで）見て取ることはできます。
ご相談のデータから実際に行ってみると、例えば５次の近似ではＹの値がまぁ１３０ぐらいまでが限度かなとか、小さい側は内挿でも既にあやしいといった具合です。
一方、仮にご相談のデータが「大きい方にはもう暫くは一様に増加していく」といった何らかの裏情報があれば、４次で止めておいた方が「もう少し外まで使える『かも』」しれません。
もっとも何を使ったところで理論式の裏付けが無い以上、ただの「数字遊び」に過ぎないのですけどね。

それからご相談者さんを含めて、グラフに書かせた「近似式」で実際に近似値を計算してみようとする方が大変多くいらっしゃいますが、それは絶対にやってはいけません。
近似式の妥当性云々より遥かに以前の問題として、係数の有効桁数不足でロクな近似値は計算できません。

多項式近似を計算する数式の組み方は既に回答しましたので、参考にしてください。

お礼

keithinさんありがとうございます。
たしかに近似式はExcelが勝手に選びだしたものなので一部分はあってても
他が合わない場合もありますよね
社内で検討した結果、間は補間で求める数式を組むことにしました。

回答No.3

noname#249717

回答者No.1です。補足です。

5次式近似のR自乗値が高いという回答者さんもいらっしゃいますが、xが11.375以上でどうかという保証は、ありません。

別の言い方をすれば、今回質問者さんが示された数値が物理的に意味のあるもので、xが11.375以上でも予測する必要がある場合は、5次式はおすすめできません。予測を大幅に逸脱します。
あくまでも、xが11.375以下でR自乗値が高いというだけです。

物理学的には指数的近似が最適と思われます。

回答No.2

keithin

＞必要なのは任意のx値の時のy値がでる関数

必要なのは確かに
y=f(x)
ですが、いまは逆算したx=g(y)のｇが５次関数になっているという設定です。
つまりy=f(x)のｆは５次関数の逆関数を実際に数式にしないと、計算は出来ないという事です。

＃補足
x=g(y)を５次で近似した場合の適合度合い（Ｒ２）は0.999978216ですが、４次でも0.999957383とほとんど遜色ありません。４次関数での近似に留めて逆関数の数式を持ってきて、計算するというのも一つの方法です。
まぁそれを言うと二次で近似してもＲ２＝0.999035157ですから、決して悪くはありません（ただし実際にグラフに書いてみると、アタマの方がちょっと浮いて見えるのは確かにイマイチっぽいですけど）。二次関数の解の公式なら流石に憶えてますよね。

閑話休題。
で。5次関数の逆関数の数式を自分で探してくるのは確かにメンドクサイので、実際に手早く答えを得る方法としては、エクセルに逆関数の値を探索させてしまいます。

準備：
Ａ列にＸの値を記入
Ｂ列にＹの値を記入

今欲しいのはＸからＹを求めることですが、暫定的にＹから５次の式でＸを計算させます。
Ｄ１にＹと記入
Ｄ２に暫定的に５０を記入
Ｅ１にＸと記入
Ｅ２に
=TREND(A2:A12,B2:B12^COLUMN(A2:E2),D2^COLUMN(A2:E2))
と記入、このＥ２が欲しい値になるＤ２が、欲しい答えです。

手順：
Ｅ２を選択
ご利用のエクセルのバージョンが不明ですが、データタブ（データメニュー）で「ゴールシーク」を開始
　数式入力セルはＥ２
　目標値はたとえば２を記入
　変化させるセルはＤ２
をそれぞれ指定してＯＫします。

＃補足１
初期値（上述で暫定的にＤ２に５０を記入したこと）によって、答えが間違える（あるいは探索できなくなる）場合があります。失敗した場合は初期値を変えて実行してください。

＃補足２
より精度を必要とする場合は、ご利用のエクセルのバージョンに応じた方法でアドインとして「ソルバー」を追加し利用します。

＃補足３
今回使用した一連の数式や方式は、古いエクセルではバグにより間違った答えになる可能性があります。少なくともエクセル２００３以降をご利用ください。

回答No.1

noname#249717

近似式の一致度はR自乗値が1.0に近ければ近いほど
一致しているという目安になりますが、
今回、エクセルに搭載されている近似式で一番近いのは
「累乗近似」になるようです。

結果は、
　y = 52.72x＾0.329
　（R^2=0.99）

有効桁数が分かりませんでしたので、
有効桁=4桁で出しました。
実際にエクセル上でお試しください。