回帰直線の求め方

(x,y）のデータが沢山あるとします。
そしてxとyの間に直線関係（比例関係）が認められそうです。
Excelを使って、最小二乗法により回帰直線（y=ax+b）を求める際、aの値を固定してbを求める、そしてその相関係数を求めることはできますでしょうか。

※単に回帰直線を計算させると、y=0.95x+bと算出されたのですが、
理屈から考えるとy=1x+bの関係がありそうなので、そのときのもっとも適当なbの値を知りたいのです。

No.2 ベストアンサー 回答者： sanori 回答日時： 2008/05/29 02:15

こんばんは。

相関係数というのは、直線の傾き（つまり、ａ）とデータとの一致度を評価するものですから、傾きを固定して考えるときには相関係数の概念はないはずです。

ｂを求めたいのであれば、下記。
最小二乗法の考え方を忠実に守った手順です。


回帰直線の方程式は
ｙ　＝　ａｘ＋ｂ
変形して
０　＝　ａｘ＋ｂ－ｙ

さて、データがｎ個あるとして、各データを（ｘk、ｙk）と表すことにします。
（ｋは、１からｎまで）

一組のデータ（ｘk、ｙk）を代入したとき、εk だけ誤差が発生するとして、
εk　＝　ａｘk ＋ ｂ － ｙk
と表すことができます。

その二乗誤差は、
εk^2　＝　（ａｘk ＋ ｂ － ｙk）^2

ｋ＝１からｋ＝ｎまでのデータについての二乗誤差の合計は、
Σ[k=1→n] εk^2　＝　Σ[k=1→n]（ａｘk ＋ ｂ － ｙk）^2

これを最小にすればよいわけです。

ここで、ａを定数としますが、データとして既知である ｘk と ｙk も定数です。
唯一、ｂだけが変数です。
よって、上の式をｂで微分したものがゼロになれば、極小値を取ることになります。

Σ[k=1→n]（ａｘk ＋ ｂ － ｙk）^2　をｂで微分したもの
　＝　Σ[k=1→n] ２（ａｘk ＋ ｂ － ｙk）・１
　＝　２・Σ[k=1→n] （ａｘk ＋ ｂ － ｙk）

これがゼロになるためには、
Σ[k=1→n]（ｂ　＋　ａｘk　－　ｙk）　＝　０

ここで、　Σ[k=1→n]ｂ　＝　ｎｂ　なので
ｎｂ　＋　Σ[k=1→n]（ａｘk　－　ｙk）　＝　０

よって、
ｂ　＝　１／ｎ・Σ[k=1→n]（ｙk　－　ａｘk）
　＝　１／ｎ[k=1→n]（ｙk　－　ａｘk）
　＝　Σ[k=1→n]（ｙk／ｎ）　－　ａ・Σ[k=1→n]（ｘk／ｎ）
　＝　［全部の点のｙ座標の平均］　－　[傾きａ]×［全部の点のｘ座標の平均］

つまり、平均値（average）の関数を使うだけで、ｂを求めることができるということなのでした。


最後に、
相関係数ではありませんが、誤差の度合いを評価する方法について。

上のほうに書いた式
Σ[k=1→n] εk^2　＝　Σ[k=1→n]（ａｘk ＋ ｂ － ｙk）^2
ですが、
これをｎ－１で割って、平方根を取る、つまり、
√｛１／（ｎ－１）・Σ[k=1→n]（ａｘk ＋ ｂ － ｙk）^2　｝
とすれば、縦方向（ｙ座標）のばらつきの度合い（一次元データにおける標準偏差に相当）が出ます。

この回答への補足

sanoriさま。
ご教示頂いた通りやってみました。
式も概ね理解できましたし、a=1に固定した場合のbの値も分かりました。一次元方向のばらつきも求めました。
xyグラフ上に全点をプロットし、求めた直線（y=1x+b）を乗せてみて、ビジュアル的にも納得できました。
ありがとうございました。

今度は#1のソルバーの方もトライしてみたいと思います。

補足日時：2008/06/01 23:26

この回答へのお礼

ご回答ありがとうございます。
パッと読んだところで、とりあえず分かったつもりになりました（笑）。
実はこれ、仕事（実験）の解析のところで使おうとしています。
あまり詳しくは書けませんが、xは試験片の処理前、yは処理後の値なのです。
xとyの関係。xを処理をすると、単に一定量増える（y切片がずれる）だけではないかという事が分かってきたのですが、目分量ではなく統計的に近似直線を求める方法を探していたのです。
「分かったつもり」で終わってしまうのか「近似直線とバラツキを把握できるのか」、結果については補足にてご報告致します。
暫しのお時間を。

お礼日時：2008/05/29 02:35

No.1 回答者： mistery200 回答日時： 2008/05/29 02:08

ソルバーを使います。

ＥＸＣＥＬのヘルプでソルバーと入力してください。
ソルバーのインストールも必要です。
インストール方法もヘルプに書いてあります。

(1)あらかじめ仮のｂの値をいれておく。
(2)仮のｂをつかってｙを求める。
(3)Ｚ＝（真のｙ－計算のｙ）の２乗を求める。
＃Ｚが負にならないようにするためです。
(4)ΣＺが最小になるようにｂを動かす。
＃ここでｂを求めるときソルバーを使います。

この回答への補足

mistery200さま。
ソルバー使えました。
そして＃２でご教示頂いた方法と同じ解が得られました（あたりまえか）。
もっと勉強していけば、複数の変数、条件つきの変数を最適化する場合など、色々と使えそうですね。
大変役にたちました。
ありがとうございました。

補足日時：2008/06/03 07:11

この回答へのお礼

さっそくの回答ありがとうございます。
”ソルバー”。
何者なのか分かりませんが使ってみます。
Excel、とても便利なのですが、決まりきった操作だけで、最近全然機能を開拓していません。
良い機会なのでトライしてみたいと思います。
結果は補足にてお伝えするつもりです。
暫しのお時間を。

お礼日時：2008/05/29 02:24