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

No.11080　エクセル近似式のR2について　　【sakura】　2009/10/17(Sat) 18:52

以前どこかで，読んだ記憶があるのですが，思い出せませんので，質問させていただきます。
エクセルで散布図を描いて，近似式を追加した時，寄与率R2が出力されますが，このR2の精度はどの程度なのでしょうか？
信頼性の点で使えるものなのかどうか？ご教示ください。

No.11083　Re: エクセル近似式のR2について　　【青木繁伸】　2009/10/17(Sat) 20:06

正しく適用された場合の信頼性は十分でしょう。

問題が出てくるのは，以下のような場合

1) 原点を通る場合の直線回帰式。
　　この場合には，そもそも，Excel では，R2 を計算する方法が間違えています。
　　具体的に言えば，R2 が負になってしまうことがあります。

2) 非線形回帰ではそもそも R2 は定義できない
　　にもかかわらず，Excel では，どんなモデルについても R2 を表示する。

Alas!!

No.11084　Re: エクセル近似式のR2について　　【青木繁伸】　2009/10/17(Sat) 20:25

x = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
y = 1, 2, 2, 4, 3, 3, 2, 2, 3,  4

について，原点を通る直線式に当てはめるという分析をしてみる

R だとどうなるか分析してみる。　

> x <- 1:10
> y <- c(1, 2, 2, 4, 3, 3, 2, 2, 3, 4)
> ans <- lm(y ~ x-1) # 原点を通る直線に回帰してみる
> summary(ans)

Call:
lm(formula = y ~ x - 1)

Residuals:
Min 1Q Median 3Q Max
-1.2623 -0.5221 0.5727 0.9149 2.3688

Coefficients:
Estimate Std. Error t value Pr(>|t|)
x 0.40779 0.05879 6.936 6.79e-05 ***
---
Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 1.154 on 9 degrees of freedom
Multiple R-squared: 0.8424, Adjusted R-squared: 0.8249 # これ(Multiple R-squared)が,正しい定義による R2
F-statistic: 48.11 on 1 and 9 DF, p-value: 6.788e-05

Excel で分析すると以下のようになる。。。。

「分析ツール」による結果と，「近似曲線の追加」による結果が違う（前者は正しい。しかし，これより前のバージョンでは，どちらも，間違いだった）。
「前者は正しい」と書いたが，正しい答えを表示するまでに,ずいぶんな年数が経ってしまった。結局，いまだなお，システムとして正しい統一的な答えを表示できるまでには，至っていない。だれも，気づかない訳でもないだろうに。

あなたの Excel は，どうなっているでしょうか？
画像は，クリックすると原寸大に拡大されます

No.11086　Re: エクセル近似式のR2について　　【青木繁伸】　2009/10/17(Sat) 21:36
x = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
y = 1, 2, 2, 4, 3, 3, 2, 2, 3,  4
について，y = a*exp(b*x) という非線形回帰式に当てはめるという分析をしてみる
R で分析すると，以下の通り。
> ans <- nls(y ~ a*exp(b*x), start=list(a=1.5572, b=0.0802))
> summary(ans)

Formula: y ~ a * exp(b * x)

Parameters:
  Estimate Std. Error t value Pr(>|t|)   
a  1.81758    0.48705   3.732  0.00577 **
b  0.06233    0.03793   1.643  0.13893   
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1 

Residual standard error: 0.8762 on 8 degrees of freedom

Number of iterations to convergence: 4 
Achieved convergence tolerance: 1.022e-06 
これに対して，エクセルで，指数近似曲線を表示させるということは，以下のようにしている。Excel の分析結果は，どうだかなあ，ということでしょう。

1) 従属変数を対数変換して，重回帰分析で近似する
2) R2 は，そのときの決定係数
3) 得られた回帰式の切片を逆対数変換して，指数曲線のパラメータとしている
どれも，不適切

図をクリックすると原寸表示します

No.11088　Re: エクセル近似式のR2について　　【sakura】　2009/10/18(Sun) 09:13

青樹先生，早速のご教示，ありがとうございます。
明快にしかも詳細にご教示いやだき，いたみいります。

胸の中のモヤモヤがすっきりいたしました。

No.11089　Re: エクセル近似式のR2について　　【sakura】　2009/10/18(Sun) 14:00

遅くなりました。再現結果は先生の内容と同じになりました。
Excel2007 for Winを使用しました。