Excelの近似曲線で求めた式の検算が合わない？

質問

Excel2007
Windows Vista です。
A1～A5のセルに順次
8809
8876
8831
8929
8917
と入力しました。
B1～B5のセルに順次
215014
225187
233321
218311
213570
と入力しました。
これらのデータから、
挿入→散布図→散布図(マーカーのみ)→レイアウト→近似曲線→
その他の近似曲線オプション→多項式近似→次数(4とした)→グラフに数式を表示する→
グラフにR-2乗値を表示する
と操作すると、
y=0.0001X^4-3.962X^3+50923X^2-3E+08X+6E+11
R^2=1
と表示されます。
ここで検算の意味で
X=8809 と入力すると　y=-1.97285E+11 となって　y=215014 とはなりません。
どこがまずいのでしょうか。
宜しくお願いいたします。

回答No.1

DJ-Potato · ベストアンサー

有効数字ですね。
特に、最大の＋の要因であるX^4の係数が有効数字一桁で切られているので、マイナス分が過剰になってしまっていたようです。
[近似曲線のデータラベルを右クリック]→[近似曲線ラベルの書式設定]で、
小数点以下15桁で表示させた結果、以下の精度で検算が得られました。

Y = 0.000115454270704X^4 - 3.961956632690910X^3 + 50,922.8587046962X^2 - 290,514,141.856105X + 620,647,077,162.018

X=8809の時、Y=215015.1515

お礼

DJ-Potatoさん、感謝いたします。

回答No.2

payapapaya

面白いことにチャレンジしましたね。
４次式ですから単に解きたいだけなら解の公式に入れるだけですが、
今回の問題はEXCELが小数点以下何十桁の細かいところは丸めてしまう
という点と、単純に表示上簡略化（丸めて）しているという点に起因しているようです。

例えば、検算の８８０９に対して合わせ込もうとすれば
X^4の項を
0.0001⇒0.00013276329にしてあげればだいたい合うと思います。（この小数点以下５桁を四捨五入しているんですね）
これだけでは他のXに対して変な数字を返すと思いますが、３乗、２乗の項ももっと細かいところまで表示されていれば正しい解が得られます。
ですが、それぞれの項を正確に書くと式が非常に長くなってしまい、さらに長くしても長くしても正確にならない問題というのもありますので「この際この枠内に収まるところまででいいや」と割り切っているのがEXCELさんです。
※試しにお手元で８８７６に合うX^4の項を求めようとしてみてください。少し変えるだけでYの値が大きくぶれることを実感していただけると思います。

こういう考え方はEXCELに限らず無理数や長い少数を扱うときに「有効桁数」という考え方で出てきます。
また、これとは別にEXCELは電子計算機という特性上概念としての「１」を持っていないので１に非常に近いある数字で代用しているとか言う問題もありますが、今回はそこまで突っ込む話でもなさそうですので、割愛します。

ご参考までに