最小2乗法に関して

質問

はじめて質問させていただきます。よろしくお願いいたします。
数学が苦手でよくわからず助けてください。

a,b,cはパラメーターであり、
log(Yn)=log(a)+blog(n)-cn
の最小2乗法で各パラメーターを出すことができるみたいなのですが、どのように出すのでしょうか？
またYとnに関しては
Y=10,n=20
Y=50,n=60
Y=15,n=100
Y=13,n=180
といった感じです。
また、このパラメーターはエクセルで計算できるのでしょうか？
わかり難い内容となってしまいましたが、
大変困っております。どうぞ教えてください。
よろしくお願いいたします。

回答No.7

age_momo

とりあえず計算はできるようになったようですね。良かったです。
ところで最小二乗法は実データを曲線にFitさせるための手法です。
各データから最も誤差の少ない曲線を計算します。

質問者さんが示されたデータをExcel等で単にグラフにしてみて
ください。どう見ても極大点を持つようなデータでは有りませんね。

お礼

お忙しい中、どうもありがとうございました。

数学とエクセルが苦手な中、どんどん分かってきました。

どんどん分かってきて楽しかったです。

またお世話になることがあるかと思いますが、その際は宜しくお願い致します。

何回も教えていただきどうもありがとうございました。

回答No.6

age_momo

まず、結果の確認ですが、10-185,,24.8-22.2を計算して

係数
切片 2.86288113
X 値 1 -0.003511823
X 値 2 0.167975477

となりましたでしょうか？どちらの方法で計算しても
こうなります。
次にURLの計算結果ですが、確かに違いますね。ただ、
示されているグラフはn=185,Yn=22.2で終わっていません。
n=230,Yn=11ぐらいの点がプロットされています。
(理由は分かりません。こちらはそれほど詳しくないので)
このデータを入れて8個のデータで再計算すると

係数
切片 2.391160601
X 値 1 -0.007606822
X 値 2 0.364955766

cgiで表示される結果とほぼ同じになります。確認ください。

行列での計算ですがついでに自動計算するSheetを作ってしまいましょう。
以下、入力するセル番号と入力する式を入れていきます。
まず、A1-A100までnのデータを入れ、(全て埋める必要は有りません)
B1-B100までYnのデータを入れます。

C1　　　　　　　　=IF(A1="",0,LN(A1))
C2-C100,D1-D100　　C1をコピーしてペースト
G1　　　　　　　　=COUNT(A1:A100)
G2,H1　　　　　　=SUM(A1:A100)
G3,I1　　　　　　=SUM(C1:C100)
H2　　　　　　　　=SUMSQ(A1:A100)
I2,H3　　　　　　=SUMPRODUCT(A1:A100,C1:C100)
I3　　　　　　　　=SUMSQ(C1:C100)
J1　　　　　　　　=SUM(D1:D100)
J2　　　　　　　　=SUMPRODUCT(A1:A100,D1:D100)
J3　　　　　　　　=SUMPRODUCT(D1:D100,C1:C100)

G5-I7　　　　　　　　=MINVERSE(G1:I3)・・・・・・・***
J5-J7　　　　　　　　=MMULT(G5:I7,J1:J3)・・・・・・・***

最後の***の入力は例えば上はG5-G7を選択した状態で(色が付いた状態)で
式を入力し、最後にEnterを押すときにCtrl+Shiftを押しながら
Enterキーを押します。(参考URLも見てください)

参考URL：

http://www.nishitech.ac.jp/~zhou/text/Planing/No10/Excel.htm

補足

どうもありがとうございます。

エクセルの行列は行なうことができました。ありがとうございます。

ただ、http://www.ushi.jp/clip/200403/index1.html
のnとYで計算すると、だいたい良く似た曲線をひけるのですが、実際に私の牧場の結果を入力した場合、全く異なるグラフになってしまいました。係数が異なるためだと思います。

私の牧場のnとYを下に示しました。

(n,Y)の順で
(15,34.9),(16,46.4),(19,36.1),(35,33.7),(37,41.5),(38,41.1),(45,42.7),(48,33.2),(53,40.3),(55,30.1),(58,35.4),(59,44.0),(66,41.1),(93,39.2),(95,31.8),(95,31.9),(99,29.6),(124,32.9),(129,24.3),(130,15.1),(144,27.3),(173,34.9),(192,37.1),(276,26.9),(330,15.9),(330,23.5),(330,23.9)(422,11.6),(426,20.8)

これを計算すると、切片が3.78387073、Ｘ値1は-0.0019353、Ｘ値2は-0.0232764　になるかと思います。

これをグラフ化するために、Ｘ軸(n)に1,2,3,4,5,6,,,,,,,348,349,350とし、
それに対応するＹの値を43.9859705*exp(-0.0019353*n)*n^-0.0232764
から計算し、グラフの散布図で示しました。
そうしますと、ピークの現れない、どんどん下がっていくグラフになりました。わたしはピークのでるようなグラフが出来るかと思っていたのですが、、、、
宜しくお願い致します。

回答No.5

age_momo

#4です。

Excelで表すのなら

=0.102*exp(-0.0238*n)*n^1.728

です。nをnの実データがあるセルを入力すればいいです。
下の例で言えばA1には20が入っているはずなので

=0.102*exp(-0.0238*A1)*A1^1.728

という感じです。(このままコピペしていいです)
ただ、例は余りよくFitしていないようですね。

補足

大変ありがとうございます。お忙しい中、申し訳ないのですが、あと2点教えてください。

１）下のURLで、woodの公式を見ると、

分娩後日数(n)は10 ,36 ,62 ,98 ,,,,,,
乳量(Y)は　　　24.8,28.7,27.6,26.8,,,,

と続きまして、その式を見ると（グラフ上部の解説を表示にマウスをあてる）、
係数0は2.39、係数1は0.36、係数2は0.076となっています。
教えていただいたエクセルの分析ツールを用いて、上記のnとYを入力してみましたが、係数がちがうものができましたがどうしてでしょうか？

http://www.ushi.jp/clip/200403/index1.html

２）エクセルで行列を計算する方法も教えていただいてよろしいでしょうか？

申し訳ございませんが教えてください。

回答No.4

age_momo

2通りの方法を書いておきます。使える方をどうぞ。

共通の作業として

A1-A4までnのデータを入力(例の場合、20,60,100,180)
B1に=LN(A1)　を入力してB2-B4にペースト
D1-D4にYのデータを入力(例の場合、10,50,15,13)
C1に=LN(D1)　を入力してC2-C4にペースト

方法１．アドイン...で分析ツールを選択する。
　　ツール→分析ツール→回帰分析　を選択

入力Yの範囲には$C$1:$C$4
入力Xの範囲には$A$1:$B$4
を入力、デフォルトで新しいシートを作成になっているはずなので
そのままOK

Sheet4が新たに作られそこに表が作成されます。一番下の表を見てください。
　　　　係数
切片　　-2.2826
X1 -0.0238
X2 1.728

log(Yn)=-2.2826-0.0238n+1.728log(n)

という結果です。(nから入力してますのでこの順番です)
log(a)=-2.2826なので
a=e^(-2.2826)=0.102
です。

方法２．ワークシート上で次の計算をします。
○n,log(n),log(y)それぞれの合計を求める。・・・・=sum(A1:A4)等で計算できます。
それぞれSn,Sln,SlYとします。

○n^2,{log(n)}^2それぞれの合計を求める。
20^2+60^2+100^2+180^2=46400・・・・=sumsq(A1:A4)等で計算できます。
Sn2,Sln2とします。

○n*log(Y)、log(n)*log(Y)、n*log(n)それぞれの合計を求める。
例　n*log(Y)の合計・・・・=sumproduct(A1:A4,C1:C4)で計算できます。
SnY,SlnY,Snlnとします。

例だと
Sn=360,Sln=16.88820387,Sly=11.48760766
Sn2=46400,Sln2=1700.824571
SnY=1013.268987,SlnY=48.70580198,Snln=1700.824571
になります。

三元連立方程式

4a+360b+16.88820387c=11.48760766
360a+46400b+1700.824571c=1013.268987
16.88820387a+1700.824571b+73.91246255c=48.70580198

を解いてa,b,cを求めます。上同様
a=-2.2826,b=-0.0238,c=1.728
となります。
なお、一番上のaの係数4はそれぞれデータの数が4つずつだったためです。
これは質問者さんがExcelで行列計算が可能なら簡単に求まります。
もし必要なら補足で要求してください。

補足

皆様お忙しい中ありがとうございます。
私は本当に、エクセルと数学が苦手なため、もう1点だけ教えてください。

log(Yn)=-2.2826-0.0238n+1.728log(n)

という結果になり、a=e^(-2.2826)=0.102　ですが、

http://www.ushi.jp/clip/200403/

の中にある、Yn=Anbe-cn（累乗は示せなかったため、上記アドレスを参照願います）に入れる場合、エクセル上では、どのような式になるのでしょうか？

本当に素人でわからなく、ご迷惑をおかけいたしますが、よろしくお願いいたします。

回答No.3

sanori

＃１です。
そういうことですかー。なるほど。

私も＃２さんと同様、ｎとlogｎを独立変数と見るという点は、非常に疑問です。

あと、エクセルの機能を使って「重回帰もどき」をやってる例を見つけました。
http://homepage2.nifty.com/crop_shimane-u/multipleregression_excel.htm
しかし、これもイマイチです・・・。

でも、まー、まずは百歩譲ってｎとlogｎを独立変数としてやってみますか。
考え方は、こうです。
log(Yn)=log(a)+blog(n)-cn
ここで
log(Yn)＝Ｗ
log(a)＝Ａ
b＝Ｂ
log(n)＝Ｘ
-c＝Ｃ
n＝Ｙ
と置けば、回帰後の式（平面の方程式）は
Ｗ＝Ａ＋ＢＸ＋ＣＹ
と表わすことができます。
（以下、ｍは添え字）
１個１個のデータの誤差は
εm＝Ａ＋ＢＸm＋ＣＹm－Ｗm
その二乗は
εm＾２＝（Ａ＋ＢＸm＋ＣＹm－Ｗm）＾２
その合計は
Ｅ＝Σεm＾２＝Σ（Ａ＋ＢＸm＋ＣＹm－Ｗm）＾２
となります。
（左辺と右辺のΣには、どちらも横ちょに「k=m→1」が付きます）

二乗誤差の合計がＥなのですから、ということは、Ｅを最小すれば、最小二乗法が出来たことになります。

Ｅが最小のとき、すなわちＥが極値を取るときには、右辺をＡ、Ｂ、Ｃの各々で偏微分したものは全てゼロになります。すなわち

∂Ｅ／ｄＡ＝０
∂Ｅ／ｄＢ＝０
∂Ｅ／ｄＣ＝０

この続きを、ここに書きたいところですが、すいませんが、ご容赦ください。
上記が理解できれば、あとは下記サイトをご覧になって式を組み立てれば、係数Ａ、Ｂ、Ｃが求められると思います。

http://aoki2.si.gunma-u.ac.jp/lecture/Regression/mreg/mreg1.html

↑このサイトに書いてあるｂ０がＡ、ｂ１がＢ、ｂ２がＣに相当します。

頑張って下さい。

補足

どうもありがとうございます。

難しい数式が多く、苦戦しております。分からないが多く、時間がかかりそうですが、健闘します。

回答No.2

grothendieck

Excelで非線形最小二乗法を計算する方法については下記URLに詳細にかかれていますのでご参照下さい。当てはめる式が簡単なならばExcelの「データ系列に近似曲線を追加する」機能でもできますが、Wood泌乳曲線は無理だと思います。なおhttp://www.ushi.jp/clip/200403/　には「nとlog(n)を独立変数と見る」という記述がありますが、nとlog(n)は独立ではないのでこれは疑問です。

参考URL：

http://aoki2.si.gunma-u.ac.jp/Hanasi/StatTalk/solver.html

回答No.1

sanori

ええーっと・・・・・

確認したいので、補足してください。

どういう形の関数にフィッティング（近似）したいんですか？
log(Yn)=log(a)+blog(n)-cn　がそうですか？
それとも、log(Yn)=log(a)+blog(n)-cn　は途中の式ですか？

Ｃｎは、Ｃかけるｎですか？　それとも、ｎはＣの添え字ですか？

Y=10,n=20
Y=50,n=60
Y=15,n=100
Y=13,n=180
ってありますけど、ａ、ｂ、ｃは定数で、ｎだけが変数、つまり、Ｙをｎだけの関数とする、ということですか？

補足

数学が苦手で、良く分からない内容となってしまいまして申し訳ございません。

CnはCかけるnです。

どうしてこの式を求めたいか補足説明します。

私は酪農関係の仕事をしており、乳量を推測するためにこの式があるようです。nは搾乳している日数で、Yは乳量になります。例で示しましたYとnについて説明しますと、20日後(n)では、乳量(Y)は10kg、60日後(n)の乳量は60kg・・・といったように、数日(n)間、乳量を計ることで、この式が導かれるようです。
なんとかしてエクセルで計算したいと思います。
この式に関するURLを以下に示しました。
説明が下手で申し訳ございません。
宜しくお願い致します。
http://www.ushi.jp/clip/200403/