「統計学関連なんでもあり」の過去ログ---031

★ Excelでの 2 次関数の当てはめ ★

5337.　Excelでの 2 次関数の当てはめ　田原　2004/12/24 (金) 20:27
└5338.　Re: Excelでの 2 次関数の当てはめ　青木繁伸　2004/12/24 (金) 21:41
　└5339.　Re^2: Excelでの 2 次関数の当てはめ　田原　2004/12/24 (金) 22:01

5337.　Excelでの 2 次関数の当てはめ　田原　　2004/12/24 (金) 20:27

本来であれば直接マイクロソフト社に問い合わせるべき質問かもしれませんが，もしお分かりの方がいらっしゃいましたらお教えください。以下に私が行った手順を書きます。

Excelで以下のデータに“近似曲線の追加”を行い，2 次関数の当てはめました。
x　　　y
243　　-23.97
247　　-24.03
252　　-24.05
253　　-24.06
259　　-23.82
260　　-23.92
263　　-23.71

この結果 y = 0.002x^2 -0.9916x +99.814 が得られました。この式をセルに入力して各 x 値を代入し，y の理論値を求めました。これらの理論値を同じグラフ内に重ねてプロットしました。
問題はここでおきました。私の理解（あるいは手順）が間違えていなければ，この理論値は先に追加した近似式に重なるはずです。しかし，プロットした値は近似式より 1 程度大きくなっていました。ちなみに，理論値に近似式を追加したところ，当然（？）y = 0.002x^2 -0.9916x +99.814 が得られました。

このような現象は，
http://aoki2.si.gunma-u.ac.jp/Hanasi/StatTalk/11-1.html
にあるような例と同様の問題でしょうか？あるいは，私が何か根本的に間違えているのでしょうか？

私は，Windows XP，Excel 2000 を使用しています。

　　　　　[このページのトップへ]

5338.　Re: Excelでの 2 次関数の当てはめ　青木繁伸　　2004/12/24 (金) 21:41

> このような現象は，
> http://aoki2.si.gunma-u.ac.jp/Hanasi/StatTalk/11-1.html
> にあるような例と同様の問題でしょうか？あるいは，私が何か根本的に間違えているのでしょうか？

両方ですね。
x^2 を扱うので桁数が大きくなっている。これはExcelの罪。
もう一つは，近似曲線の追加により表示される係数は有効桁数が少なすぎる。これはExcelの罪であり，疑いを知らない貴方のミス。

x を 1/1000にして，なおかつ答えの表示桁数を増やすために，分析ツールの中の回帰分析を使うと，いいでしょうね。
そのままの値を使って分析ツールー＞回帰分析
係数
切片 9.98143309535173E+01
x　　 -9.91582537364980E-01
x2 1.98436016982584E-03
1/1000にした値を使って分析ツールー＞回帰分析では
係数
切片 9.98143310868477E+01
x/1000 -9.91582538419015E+02
（x・1000）＾2 1.98436017190750E+03
微妙に違いますが，どっちも実は精度的には問題があるかな。

R だと，本の値を使ったときには
> r <- lm(y～x+x2,d)
> coefficients(r)
(Intercept) x x2
99.8143308616693616386 -0.9915825366387666095 0.0019843601683913752
1/1000にした値を使うと
> r2 <- lm(y～x+x2,d2)
> coefficients(r2)
(Intercept) x x2
99.81433086165522 -991.58253663865548 1984.36016839115632
ということになる。
予測値を計算するときに，係数の桁数をケチってはいけない。特に高次項を含む多項式回帰の時には。

　　　　　[このページのトップへ]

5339.　Re^2: Excelでの 2 次関数の当てはめ　田原　　2004/12/24 (金) 22:01

すばやい対応ありがとうございます。

> 予測値を計算するときに，係数の桁数をケチってはいけない。特に高次項を含む多項式回帰の時には。

この点はこれまであまり気にとめていませんでした。まさに私のミスでした。

　　　　　[このページのトップへ]