Excelで近似式の標準偏差を算出する方法について

質問

非常に基本的なことをうかがいます。

ExcelでＸとＹの値が下記のように変化する様子を散布図で描き，２変量の関係を多項式にて求めると下記の式が導出されました。

変数
Ｘ　　Ｙ
10　100
15　200
20　400
25　800
30　1600

近似式
y = 4.5714x2 - 110.86x + 780
R² = 0.9939

この近似式における標準偏差(正確に言うと予測されるyの標準偏差)はどのように算出すればいいのでしょうか？

よろしくお願いいたします。

回答No.4

井口 豊（@Iguchi_Y） · ベストアンサー

残差（y-y'）の標準偏差をSTDEVで求めたのですね。

では，標準偏差の定義式を考えてみましょう。

まず，データ（y-y'）の分散（不偏分散）とは，その平均（M)からの差の平方和をn-1で割ったものです。
Σ{(y-y')-M}^2/(n-1)

その平方根が標準偏差となります。

EXCELのSTDEVも，式は変形してありますが，そのような計算です。

データ1個づつ，実際にやってみます。

まず　Σ{(y-y'）の平均Mを求めると
M　=　0.07

以下に標準偏差の計算を書きます。　

x　　y　　　　　　　y'　　　　　　　y-y'　　　M　　{(y-y')-M}^2
10　　100　　　128.54　　　-28.54 　　　0.07　　818.5321
15　　200　　　145.665　　　54.335　　　0.07 　2944.690225
20　　400　　　391.36　　　　8.64　　　　0.07 　73.4449
25　　800　　　865.625　　-65.625　　　0.07 　4315.833025
30　　1600 　　1568.46　　　31.54　　　　0.07　　990.3609

　　　　　　　　　　　　Σ{(y-y')-M}^2　　　　　　　9142.86115
　　　　　　　　　　　　　　　n-1　　　　　　　　　　　　　　　4
　　　　　　　　Root(Σ{(y-y')-M}^2/(n-1))　　　　47.80915485

当然ですが，EXCELのSTDEVと同じ結果が出ます。

まず，この表の部分を，下に挙げた前回の私の表と比べてください。

x　　　　y　　　　　　y'　　　y-y'　　　　(y-y')^2
10　　100　　　128.54　　-28.54　　　814.5316
15　　200　　　145.665　　54.335　　2952.292225
20　　400　　　391.36　　　8.64　　　　74.6496
25　　800　　　865.625　　-65.625　　4306.640625
30　　1600　　1568.46 　　31.54　　　994.7716

違うところは，質問者が
Σ{(y-y')-M}^2

を求めるのに対し，私は
Σ(y-y')^2
つまり，M=0として求めているのです。

質問者は±σを考えたと言いますが，どこからの±σになると思いましたか？
もしかして，モデル曲線の両側σと考えましたか？

それなら間違いです。

質問者の計算は，

残差(y-y')の平均M=0.07の両側σ

になるのです。

つまり，曲線の上側0.07の両側σになるのです。

なぜこうなるのでしょうか？

実は，この計算，私も質問者も，理想的分布なら同じになるのです。

つまり，モデル曲線を挟んで，データが左右均等（例えば正規分布）ならば，
正誤差と負誤差が相殺され，平均M=０になるからです。

しかし，実際のデータは，そのような理想的なものではありません。

だから，曲線の両側のバラツキを考えるなら，強制的にM=0とした計算，つまり私がやった
データと曲線の差の2乗和
Σ{(y-y')＾２
にする必要があるのです。

次に，そのあとの計算について。

質問者は，求めた和
Σ{(y-y')-M}^2　＝　9142.86115

を
n-1 = 4
で割って，平方根を取って，標準偏差を出しました。
それが，STDEV関数の計算です。

Root[Σ{(y-y')-M}^2/(n-1)}]　= 　Root(9142.86115/4) = 47.80915

一方，私は，パラメータ数を引いた自由度
n-3=2
で割って，平方根を取りました。

Root{Σ(y-y')^2/(n-3)} = 67.61244578

前回，標準誤差と言いましたが，正確には，平均残差平方根，です。

y-y' という値を考えてください。

これは，もはや測定値（生のデータ）ではなく，加工された値なのです。
それも，2次関数モデルを使って，です。

だから，その際，推定された係数の数（３）の分だけ，自由度が減ってると考えるわけです。

実は，不偏分散を求める際に，n-1で割るのも，平均を求めるという自由度１が使われているため，
n-1で割るのだという説明もされるのです。

例えば，次のデータ

x
10　
15
20
25
30

この平均M，標準偏差S，不偏分散Vは，
EXCELのAVERAGE，STDEV，VAR関数を用いれば出ます。

M = 20
S = 7.90569415
V = 62.5

当然ですが，V = S^2

Xの散らばりを示すのに，通常は，横軸にこれらの数値をプロットしたり，ヒストグラムにします。

しかし，これを90゜回転させ，ｙ軸上にプロットしてみましょう。

つまり，

x　　y
0　　10
0　　15
0　　20
0　　25
0　　30

というデータセットを考えます。

これをEXCELなどで回帰分析してみます。

EXCELの場合は，ツール　→　分析ツール　とたどって，回帰分析を利用すれば良いし，その他の統計ソフトでも可能な場合も多い（xが０だけではエラーと出るのもあるが）。

EXCEL回帰分析を上記データで実施すると

分散分析表
　　　　自由度　　　変動　　　　　　　　　分散
回帰　　　　1　　　-2.84217E-14　　-2.84217E-14
残差　　　　4　　　　250　　　　　　　　　　62.5
合計　　　　5　　　　250

　　　　　　係数　　　　　標準誤差
切片　　　　20　　　　　3.535533906
X 値 1　　　0　　　　　　　　0

と出ます。

つまり，

つまり，傾き0で，ｙ=20という直線が適合し，
残差自由度４，分散62.5，標準誤差3.535533906
などが分かります。

これは，さきほど述べた，平均M，不偏分散V，それを求めるとき割ったn-1=4の値だと分かります。
標準誤差は，
Root(分散/データ数) = Root(62.5/5)
です。

つまり，n-1で割ったのは，y=aという直線を適合した時，
a=20（＝平均）というパラメータを求めるためだったのです。

したがって，残差自由度は
１次式　y=ax+b　なら，自由度n-2　
２次式　y=ax^2+bx+c　なら，自由度n-3

となるのです。

EXCELでは，できませんが，質問の2次関数を回帰分析すると

　　　　自由度　　　変動　　　　　　分散　　　　　　分散比　　　　 F確率
回帰　　2　　　　1478857.14　　　739428.57　　　161.7499491　　　0.006144395
残差　　2　　　　9142.86　　　　　4571.43
合計　　4　　　　1488000

です。

回帰の自由度２は，x　と　x^2　を利用したことによります。
残差自由度２は，サンプル数５からパラメータ３（a, b, c）を引いたものです。

***　結論　************
長くなりましたが，結局，質問者の示した，EXCELのSTDEVを使うやり方では，モデルからズレの評価で，次の2点で問題が起こるのです。

（１）モデル曲線からのズレでなく，ズレの平均からのズレ，を求めている。
（２）生データのパラメータ自由度n-1を使っていて，モデルのパラメータ自由度n-3が利用されていない。

EXCEL関数を使うと，出来そうな分析でも，これらは生データに利用されるものです。
その定義式を理解してないと，誤用が生じます。

最後に，

＞重ね重ね質問してしまいすみません

などと，どうぞ言わないでください。
正確に理解してもらうことは，とても大切だと思っています。

回帰分析の検定？
http://okwave.jp/qa/q6733154.html#answer
という質問に，

＞「傾き＝０」という回帰結果はありえない
とか
＞相関係数ｒが正であれば４５°，ｒが負であれば－４５°を表します

と回答した人がいたので，その説明は違う，と述べたところ，猛反発され，私は落胆しています。

質問者は沈黙してしまい，かえって申し訳ないと思っています。

でも，不明な点は質問し，正しく理解されたほうが良いと思うのです。

時間があれば，この「回帰分析の検定？」
http://okwave.jp/qa/q6733154.html#answer
も見てください。

こんな回答は，悲しい。

お礼

この度もご丁寧にありがとうございました。

自由度については以前から疑問に感じておりましたが，Excel関数にて勝手に修正してくれるものと思い込んでいたふしがあります。
今回の基本的なことについてもちゃんと理解していないと勘違いのまま分析を進めてしまうことになりますね…。
私の勉強不足でした。

本当にありがとうございました。

回答No.3

alice_44

予測値の誤差の標準偏差ではなくて、
予測値の標準偏差値を求めるんですね？
奇妙な話ですけど。

x のデータをワークシート上のどこかの列に並べ、
左隣のセルの値を x として予測値を計算する
セルで、x の列の右隣の列を埋めます。
一個だけ書けば、あとはコピペでいけますね。
予測値の並んだ列ができますから、
セルの範囲を与えて標準偏差を計算する関数を
呼び出すだけです。
ウィザード一発で設定できるはずですから、
エクセルのマニュアルをよく読みましょう。

ちなみに、エクセルの使用法に関する質問は、
カテゴリー違いです。

回答No.2

井口 豊（@Iguchi_Y）

2次関数で近似したんですね。

y '= 4.5714x2 - 110.86x + 780

として，xにデータを入れ，y'を求めます。

計算値と観測値の偏差の２乗(y-y')^2を計算し，
その総和　Σ(y-y')^2
を求めます。

３つのパラメータを求めたので，
データ数-3=2
が自由度です。

Σ(y-y')^2を自由度で割って平方根をとった
Root(Σ(y-y')^2/(n-3))＝67.61244578

が標準誤差です。

この場合，標準偏差ではなく，標準誤差（standard error SE)と呼ぶのが普通です。

x 　　　 y　 　 y'　 　 (y-y')^2
10 　 100 　 128.54 　 　 814.5316
15 　 200 　 145.665 　 　2952.292225
20 　 400 　 391.36 　　 74.6496
25 　 800 　 865.625 　 　 4306.640625
30 　1600 　 1568.46 　 　 994.7716

Σ(y-y')^2 = 9142.88565
n-3 = 2

Root(Σ(y-y')^2/(n-3)) = 67.61244578

お礼

この度もご丁寧にありがとうございます。

実は私もIguchi_Yさまと途中までは同様に考えたのです。

X Y Y' Y-Y'
10 100 128.54 -28.54
15 200 145.665 54.335
20 400 391.36 8.64
25 800 865.625 -65.625
30 1600 1568.46 31.54

以上まで求め，「Y-Y'」の列の値をエクセル関数「STDEV」を使用して標準偏差を求めればよいのかな…と思ったわけです。
その結果「47.8」という値を得ることができました。
この「47.8」が近似式における標準偏差だと思ったのですが，自信がなかったので質問させていただいた次第です。

この近似式を用いた予測値の－σ～＋σの範囲に包含されてある値のみを採用し，それ以外の値については除外するといった判断基準として用いようとして，それならば近似式の標準偏差はどうしたら求められるのだろう…といった疑問にたんを発しているわけで…。

私の計算した標準偏差とIguchi_Yさまの「標準誤差」は統計的にどのように違うのでしょうか？
当然のことながら意味合いが異なるので全然違う値となっているんですよね…。

重ね重ね質問してしまいすみません。

回答No.1

nattocurry

そのデータは、
Y=25*2^(X/5)
なんですけど、

そんなことよりも、【この近似式における標準偏差(正確に言うと予測されるyの標準偏差)はどのように算出】すれば良いのかを知りたいのでしょうか？