Regresja liniowa

[fezop]

Witam! Mam wyliczyć współczynnik korelacji ζ z czego prawdopodobnie wywiązałem się bez zarzutu.
Wykorzystując następujący wzorek:

\(\displaystyle{ \frac{\sum_{i=1}^{n} \left( X _{i}-X _{s} \right) \left( Y _{i} -Y _{s} \right)}{ \sqrt{\sum_{i=1}^{n} \left( X _{i} -X _{s} \right) ^{2} \sum_{i=1}^{n} \left( Y _{i} -Y _{s} \right) ^{2}} }}\)

gdzie
\(\displaystyle{ X _{s}}\)
oznacza wartość średnią
\(\displaystyle{ X _{i}}\)

No i teraz liczę sobie współczynniki a i b do równania regresji liniowej według wzorków:

\(\displaystyle{ a= \frac{\left[ n(\sum_{i=1}^{n}X _{i} Y _{i} )-( \sum_{i=1}^{n} X _{i} )( \sum_{i=1}^{n} Y _{i} )\right]}{n(\sum_{i=1}^{n}X _{i} ^{2} )-( \sum_{i=1}^{n} X _{i} ) ^{2} }}\)

\(\displaystyle{ b= \frac{\left[ (n \sum_{i=1}^{n} X _{i} ^{2} )( \sum_{i=1}^{n} Y _{i} )-\left( \sum_{i=1}^{n} X _{i} \right) \cdot \left( \sum_{i=1}^{n} X _{i}Y _{i} \right)\right]}{n(\sum_{i=1}^{n}X _{i} ^{2} )-( \sum_{i=1}^{n} X _{i} ) ^{2}}}\)

I wychodzą mi jakieś bzdury bo prosta, która w założeniu miałaby przebiegać gdzieś w pobliżu wyników pomiarów wędruje sobie w kosmos...

Natomiast gdy wykorzystam sobie w excelu polecenie "dodaj linię trendu" wszystko prawie elegancko hula jednakże a współczynniki które podaje exce są diametralnie odmienne od wyliczonych z użyciem powyższych wzorów i jakkolwiek bym nie próbował nie wychodzą mi nawet zbliżone wartości...
Proszę zatem o pomoc w zrozumieniu tego cholerstwa, bo już mnie krew zalewa a siedzę nad tym od conajmniej 5 dni, w internecie znalazłem dziesiątki wzorów, każdy inny i ostatecznie nie znam poprawnej metody liczenia współczynników do prostej regresji liniowej.

Dodatkowo nadmienię może czego dotyczy moje zadanie:
Sprawdzenie prawa Malusa.
Należy sporządzić metodą regresji liniowej \(\displaystyle{ I=f(cos ^{2} \alpha )}\)
Dla wartości które podam poniżej:

\(\displaystyle{ \begin{tabular}{|c|c|}
\hline
I _{s} & cos ^{2} \alpha \\ \hline
98,67 & 0,970\\ \hline
82,67 & 0,883\\ \hline
61,33 & 0,750\\ \hline
46,67 & 0,587\\ \hline
28,00 & 0,413\\ \hline
16,00 & 0,250\\ \hline
8,00 & 0,117\\ \hline
4,00 & 0,030\\ \hline
1,33 & 0,000\\ \hline
\end{tabular}}\)

Następnie wyznaczyć współczynnik korelacji (zrobione) i wyznaczyć Bóg jeden wie na jakiej podstawie jakieś współczynniki proporcjonalności B oraz C, które jak napisane jest w skrypcie, rzekomo "łatwo udowodnić, że podstawiając"...więc po prostu sam cud miód i malina (ale to nie jest istotne, bo jakoś sobie z tym pewnie poradzę).

Natomiast nie mam zielonego pojęcia jaki wzór zastosować do wyliczenia równania prostej regresji.
Więc proszę o pomoc w znalezieniu go, ewentualnie jakiś sensowny przykład jak to liczyć.

[pyzol]

Na pewno w mianowniku brakuje Ci kwadratu:
\(\displaystyle{ a= \frac{\left[ n(\sum_{i=1}^{n}X _{i} Y _{i} )-( \sum_{i=1}^{n} X _{i} )( \sum_{i=1}^{n} Y _{i} )\right]}{n(\sum_{i=1}^{n}X _{i} ^{2} )-( \sum_{i=1}^{n} X _{i} )^2}}\)-- 2 cze 2011, o 22:23 --o już jest, to napisz jakie a Ci wychodzi.

[fezop]

Excel zwraca mi mniej więcej coś takiego:
y = 95,355x - 3,8603
Kiedy zaznaczę opcję, żeby sam wyznaczył linię trendu i podał wzór.

A z wyliczeń mam coś takiego:
y=0,0102x + 8,227

Więc różnica jest dość duża...


/edit
Ewentualnie mogę załączyć lub przesłać wyliczenia w excelu, jeżeli to może być pomocne.

[pyzol]

Nie zamień dane jeśli chodzi a, masz odwrotną zależność policzoną

[fezop]

Tj. zamienić X z Y miejscami?

/edit
Wtedy:
a=0,0044
b=0,0373

co też mnie nie urządza :/

/edit
Ale na wszelki wypadek policzę to jeszcze raz i ręcznie, bo może excel mnie oszukuje...

[pyzol]

Tak. Za \(\displaystyle{ X_i}\) masz wstawić wartości z drugiej kolumny za \(\displaystyle{ Y_i}\) z pierwszej.-- 4 cze 2011, o 02:59 --Excel Cię nie oszukuje, sprawdzałem, napisz lepiej krok po kroku co i jak liczysz, chodzi mi głównie o współczynnik a.

[fezop]

Dobrze, opiszę, co prawda nie krok po kroku, bo napisanie tego w LATEX-ie zajęłoby mi ze dwa tygodnie, ale poniżej podam wartość jakie wyliczam, i mniej więcej na jakiej podstawie je wyliczam.

\(\displaystyle{ \sum_{i=1}^{n} X _{i}=3,9999}\)

\(\displaystyle{ \sum_{i=1}^{n} Y _{i}=346,6667}\)


Podnoszę sumę geometryczną Xi do kwadratu:
\(\displaystyle{ (\sum_{i=1}^{n} X _{i}) ^{2}=15,9990}\)


Tworzę tabelkę, w której przemnażam Xi z Yi w każdym wierszu, czyli mam 9 wyników
następnie sumuję otrzymane wyniki:
\(\displaystyle{ \sum_{i=1}^{n} X _{i}Y _{i}=258,6925}\)


Tworzą tabelkę, w której podnoszę każdą wartość Xi do kwadratu, czyli mam 9 wyników
następnie sumuję utrzymane wyniki:
\(\displaystyle{ \sum_{i=1}^{n} X _{i} ^{2} =2,8749}\)


\(\displaystyle{ n(\sum_{i=1}^{n} X _{i} ^{2}) =25,8737}\)

\(\displaystyle{ n(\sum_{i=1}^{n} X _{i}Y _{i})=2328,2327}\)

Dwa powyższe po prostu wymnażam dla \(\displaystyle{ n=9}\)



Teraz podstawiam otrzymane wartości do obu równań:

\(\displaystyle{ a= \left[ n(\sum_{i=1}^{n}X _{i} Y _{i} )-( \sum_{i=1}^{n} X _{i} )( \sum_{i=1}^{n} Y _{i} )\right] \cdot \frac{1}{X}}\)

\(\displaystyle{ b= \left[ (n \sum_{i=1}^{n} X _{i} ^{2} )( \sum_{i=1}^{n} Y _{i} )-\left( \sum_{i=1}^{n} X _{i} \right) \cdot \left( \sum_{i=1}^{n} X _{i}Y _{i} \right)\right] \cdot \frac{1}{X}}\)

dla
\(\displaystyle{ X=n(\sum_{i=1}^{n}X _{i} ^{2} )-( \sum_{i=1}^{n} X _{i} ) ^{2}}\)


\(\displaystyle{ a= \frac{2328,2327-(3,9999)(346,6667)}{25,8737-15,9990} =95,3554}\)



\(\displaystyle{ b= \frac{25,8737*346,6667-3,9999*258,6925}{25,8737-15,9990} =803,5467}\)



I tutaj jedna mała uwaga:
zauważyłem jedną prawidłowość, excel wylicza mi a takie jakie powinno wyjść, natomiast dla \(\displaystyle{ b}\) jest to wartość rzędu 803.
Natomiast gdy przy obliczaniu \(\displaystyle{ b}\) zamiast sumy geometrycznej \(\displaystyle{ Yi}\) użyję średniej arytmetycznej \(\displaystyle{ Yi}\), to wynik wychodzi z grubsza prawidłowy, tj. -3,86.
Przedstawiam to poniżej:

\(\displaystyle{ \sum_{i=1}^{n} Y _{i}= \frac{346,6667}{9} =38,5185}\)

\(\displaystyle{ b= \frac{25,8737*38,5185-3,9999*258,6925}{25,8737-15,9990} =-3,8603}\)


Czy zatem we wzorze na współczynnik \(\displaystyle{ b}\), powinienem użyć średniej arytmetycznej \(\displaystyle{ Yi}\) zamiast sumy geometrycznej \(\displaystyle{ Yi}\)?

[pyzol]

O b nie wspominałem, bo mi się ten wzór nie podoba. Nie chce mi się go sprawdzać.
Skorzystaj z tego:
\(\displaystyle{ b=\overline{Y}-a\overline{X}\\
\overline{X}=\frac{1}{n}\sum_{i=1}^n X_i}\).

[fezop]

\(\displaystyle{ \overline{X}=0,4444}\)
\(\displaystyle{ \overline{Y}=38,5185}\)

\(\displaystyle{ b=38,5185-9554*0,4444=-3,8603}\)

Wychodzi tak jak powinno być
Dzięki za pomoc, leci "pomógł"
Pozdrawiam