W Przewyższać Istnieje jeszcze szybszy i wygodniejszy sposób wykreślenia regresji liniowej (a nawet głównych typów regresji nieliniowych, jak omówiono poniżej). Można to zrobić w następujący sposób:

1) wybierz kolumny z danymi X I Y(powinny być w tej kolejności!);

2) zadzwoń Kreator wykresu i wybierz w grupie Typ – Miejsce i natychmiast naciśnij Gotowy;

3) nie odznaczając schematu, wybrać pojawiającą się pozycję menu głównego Diagram, w którym należy wybrać element Dodaj linię trendu;

4) w wyświetlonym oknie dialogowym Linia trendu w zakładce Typ wybierać Liniowy;

5) w zakładce Opcje możesz aktywować przełącznik Pokaż równanie na diagramie, co pozwoli zobaczyć równanie regresji liniowej (4.4), w którym zostaną obliczone współczynniki (4.5).

6) W tej samej zakładce możesz aktywować przełącznik Umieść na wykresie przybliżoną wartość niezawodności (R^2).. Wartość ta jest kwadratem współczynnika korelacji (4,3) i pokazuje, jak dobrze obliczone równanie opisuje zależność eksperymentalną. Jeśli R 2 jest bliskie jedności, to teoretyczne równanie regresji dobrze opisuje zależność eksperymentalną (teoria dobrze zgadza się z eksperymentem), a jeśli R 2 jest bliskie zeru, to równanie to nie nadaje się do opisu zależności eksperymentalnej (teoria nie zgadza się z eksperymentem).

W wyniku wykonania opisanych czynności otrzymasz diagram z wykresem regresji i jego równaniem.

§4.3. Główne typy regresji nieliniowej

Regresja paraboliczna i wielomianowa.

Paraboliczny zależność wartości Y od rozmiaru X nazywa się zależnością wyrażoną funkcją kwadratową (parabolą drugiego rzędu):

To równanie nazywa się równanie regresji parabolicznej Y NA X. Opcje A, B, Z są nazywane współczynniki regresji parabolicznej. Obliczanie współczynników regresji parabolicznej jest zawsze kłopotliwe, dlatego do obliczeń zaleca się wykorzystanie komputera.

Równanie (4.8) regresji parabolicznej jest szczególnym przypadkiem bardziej ogólnej regresji zwanej wielomianem. Wielomian zależność wartości Y od rozmiaru X nazywa się zależnością wyrażoną wielomianem N-ta kolejność:

gdzie są liczby i ja (I=0,1,…, N) są nazywane współczynniki regresji wielomianowej.

Regresja mocy.

Moc zależność wartości Y od rozmiaru X nazywa się zależnością postaci:

To równanie nazywa się równanie regresji mocy Y NA X. Opcje A I B są nazywane współczynniki regresji mocy.

ln = ln A+B· ln X. (4.11)

Równanie to opisuje prostą na płaszczyźnie o logarytmicznych osiach współrzędnych ln X i ln. Dlatego kryterium stosowalności regresji potęgowej jest wymóg, aby punkty logarytmów danych empirycznych ln x ja i ln tak, ja były najbliżej linii prostej (4.11).

Regresja wykładnicza.

Orientacyjny(Lub wykładniczy) zależność wartości Y od rozmiaru X nazywa się zależnością postaci:

(Lub ). (4.12)

To równanie nazywa się równanie wykładnicze(Lub wykładniczy) regresja Y NA X. Opcje A(Lub k) I B są nazywane współczynniki wykładnicze(Lub wykładniczy) regresja.

Jeśli weźmiemy logarytm obu stron równania regresji mocy, otrzymamy równanie

ln = X ln A+ln B(lub ln = k x+ln B). (4.13)

Równanie to opisuje liniową zależność logarytmu jednej wielkości ln od innej wielkości X. Dlatego kryterium stosowalności regresji mocy jest wymóg, aby punkty danych empirycznych o tej samej wartości x ja i logarytmy innej wielkości ln tak, ja były najbliżej linii prostej (4,13).

Regresja logarytmiczna.

Logarytmiczny zależność wartości Y od rozmiaru X nazywa się zależnością postaci:

=A+B· ln X. (4.14)

To równanie nazywa się równanie regresji logarytmicznej Y NA X. Opcje A I B są nazywane współczynniki regresji logarytmicznej.

Regresja hiperboliczna.

Hiperboliczny zależność wartości Y od rozmiaru X nazywa się zależnością postaci:

To równanie nazywa się równanie regresji hiperbolicznej Y NA X. Opcje A I B są nazywane współczynniki regresji hiperbolicznej i są wyznaczane metodą najmniejszych kwadratów. Zastosowanie tej metody prowadzi do wzorów:

We wzorach (4.16-4.17) sumowanie odbywa się po indeksie I od jednego do liczby obserwacji N.

Niestety, w Przewyższać nie ma funkcji obliczających współczynniki regresji hiperbolicznej. W przypadkach, gdy nie wiadomo, czy mierzone wielkości są powiązane ze sobą metodą odwrotnej proporcjonalności, zaleca się zamiast równania regresji hiperbolicznej szukać równania regresji potęgowej, tak aby w Przewyższać istnieje procedura jego znalezienia. Jeżeli między mierzonymi wielkościami przyjmie się zależność hiperboliczną, wówczas jej współczynniki regresji trzeba będzie obliczyć, korzystając z pomocniczych tablic obliczeniowych i operacji sumowania, korzystając ze wzorów (4.16-4.17).

Wiadomo, że jest przydatny w różnych dziedzinach działalności, w tym w takiej dyscyplinie jak ekonometria, gdzie to oprogramowanie narzędziowe jest wykorzystywane w pracy. W zasadzie wszystkie czynności zajęć praktycznych i laboratoryjnych wykonywane są w programie Excel, co znacznie ułatwia pracę poprzez szczegółowe objaśnienie poszczególnych czynności. Zatem jedno z narzędzi analitycznych „Regresja” służy do wyboru wykresu dla zbioru obserwacji metodą najmniejszych kwadratów. Przyjrzyjmy się, czym jest to narzędzie programu i jakie korzyści niesie ze sobą dla użytkowników. Poniżej znajdziesz także krótką, ale przejrzystą instrukcję budowy modelu regresji.

Główne zadania i rodzaje regresji

Regresja reprezentuje związek między danymi zmiennymi, umożliwiając w ten sposób przewidzenie przyszłego zachowania tych zmiennych. Zmienne to różne zjawiska okresowe, w tym zachowania człowieka. Ten rodzaj analizy Excela służy do analizy wpływu wartości jednej lub kilku zmiennych na konkretną zmienną zależną. Przykładowo na sprzedaż w sklepie ma wpływ kilka czynników, m.in. asortyment, ceny i lokalizacja sklepu. Dzięki regresji w Excelu możesz określić stopień wpływu każdego z tych czynników na podstawie wyników dotychczasowej sprzedaży, a następnie wykorzystać uzyskane dane do prognozy sprzedaży na kolejny miesiąc lub dla innego, zlokalizowanego w pobliżu sklepu.

Zazwyczaj regresję przedstawia się jako proste równanie, które ujawnia zależności i siły relacji pomiędzy dwiema grupami zmiennych, gdzie jedna grupa jest zależna lub endogeniczna, a druga jest niezależna lub egzogeniczna. Jeśli istnieje grupa powiązanych ze sobą wskaźników, zmienna zależna Y jest wyznaczana na podstawie logiki rozumowania, a pozostałe pełnią rolę niezależnych zmiennych X.

Główne zadania budowy modelu regresji są następujące:

1. Wybór istotnych zmiennych niezależnych (X1, X2, ..., Xk).
2. Wybór rodzaju funkcji.
3. Konstruowanie szacunków dla współczynników.
4. Konstrukcja przedziałów ufności i funkcji regresji.
5. Sprawdzenie znaczenia obliczonych szacunków i skonstruowanego równania regresji.

Istnieje kilka rodzajów analizy regresji:

• sparowane (1 zmienna zależna i 1 niezależna);
• wielokrotne (kilka niezależnych zmiennych).

Istnieją dwa typy równań regresji:

1. Liniowy, ilustrujący ścisłą liniową zależność między zmiennymi.
2. Nieliniowe — równania, które mogą zawierać potęgi, ułamki zwykłe i funkcje trygonometryczne.

Instrukcja budowy modelu

Aby wykonać daną konstrukcję w programie Excel należy postępować zgodnie z instrukcją:

Do dalszych obliczeń użyj funkcji „Linear()”, podając wartości Y, wartości X, stałą i statystykę. Następnie określ zbiór punktów na linii regresji za pomocą funkcji „Trend” - Wartości Y, Wartości X, Nowe wartości, Stała. Korzystając z podanych parametrów, oblicz nieznaną wartość współczynników, bazując na zadanych warunkach problemu.

Analiza regresji w programie Microsoft Excel - najbardziej kompleksowy przewodnik po wykorzystaniu programu MS Excel do rozwiązywania problemów analizy regresji z zakresu analityki biznesowej. Konrad Carlberg jasno tłumaczy zagadnienia teoretyczne, których znajomość pozwoli uniknąć wielu błędów zarówno przy samodzielnym przeprowadzaniu analizy regresji, jak i ocenie wyników analiz wykonanych przez inne osoby. Cały materiał, od prostych korelacji i testów t po wielokrotną analizę kowariancji, opiera się na przykładach ze świata rzeczywistego i towarzyszą mu szczegółowe procedury krok po kroku.

Książka omawia dziwactwa i kontrowersje związane z funkcjami regresji w Excelu, bada implikacje każdej opcji i argumentu oraz wyjaśnia, jak wiarygodnie stosować metody regresji w różnych obszarach, od badań medycznych po analizę finansową.

Konrada Carlberga. Analiza regresji w programie Microsoft Excel. – M.: Dialektyka, 2017. – 400 s.

Pobierz notatkę w formacie lub, przykłady w formacie

Rozdział 1: Ocena zmienności danych

Statystycy mają do dyspozycji wiele miar zmienności. Jedną z nich jest suma kwadratów odchyleń poszczególnych wartości od średniej. W programie Excel służy do tego funkcja KWADRAT(). Częściej jednak stosuje się wariancję. Dyspersja jest średnią kwadratów odchyleń. Wariancja jest niewrażliwa na liczbę wartości w badanym zbiorze danych (podczas gdy suma kwadratów odchyleń rośnie wraz z liczbą pomiarów).

Excel oferuje dwie funkcje zwracające wariancję: DISP.G() i DISP.V():

• Użyj funkcji DISP.G(), jeśli wartości do przetworzenia tworzą populację. Oznacza to, że wartości zawarte w zakresie są jedynymi wartościami, które Cię interesują.
• Użyj funkcji DISP.B() jeśli wartości do przetworzenia pochodzą z próbki z większej populacji. Zakłada się, że istnieją dodatkowe wartości, których wariancję można również oszacować.

Jeśli wielkość taka jak średnia lub współczynnik korelacji jest obliczana na podstawie populacji, nazywa się ją parametrem. Podobną wielkość obliczoną na podstawie próby nazywamy statystyką. Liczenie odchyleń od średniej w danym zbiorze otrzymasz sumę kwadratów odchyleń o mniejszej wielkości, niż gdybyś odliczył je od jakiejkolwiek innej wartości. Podobne stwierdzenie dotyczy wariancji.

Im większa wielkość próby, tym dokładniejsza jest obliczona wartość statystyczna. Nie ma jednak próby mniejszej niż wielkość populacji, dla której można mieć pewność, że wartość statystyczna odpowiada wartości parametru.

Załóżmy, że masz zbiór 100 wzrostów, których średnia różni się od średniej populacji, niezależnie od tego, jak mała jest różnica. Obliczając wariancję dla próbki, otrzymasz wartość, powiedzmy 4. Ta wartość jest mniejsza niż jakakolwiek inna wartość, którą można uzyskać, obliczając odchylenie każdej ze 100 wartości wzrostu w stosunku do dowolnej wartości innej niż średnia próbki , w tym w odniesieniu do prawdziwej średniej populacji ogólnej. Dlatego obliczona wariancja będzie inna i mniejsza od wariancji, którą uzyskasz, jeśli w jakiś sposób odkryjesz i wykorzystasz parametr populacji, a nie średnią z próby.

Średnia suma kwadratów wyznaczona dla próby pozwala uzyskać niższą estymację wariancji populacji. Obliczoną w ten sposób wariancję nazywamy przesiedlony ocena. Okazuje się, że aby wyeliminować błąd systematyczny i uzyskać bezstronne oszacowanie, wystarczy podzielić sumę kwadratów odchyleń nie przez N, Gdzie N- wielkość próbki oraz n – 1.

Ogrom n – 1 nazywa się liczbą (liczbą) stopni swobody. Istnieją różne sposoby obliczenia tej wielkości, chociaż wszystkie polegają albo na odjęciu pewnej liczby od wielkości próby, albo na policzeniu liczby kategorii, do których należą obserwacje.

Istota różnicy pomiędzy funkcjami DISP.G() i DISP.V() jest następująca:

• W funkcji VAR.G() suma kwadratów jest dzielona przez liczbę obserwacji, co stanowi obciążone oszacowanie wariancji, czyli prawdziwą średnią.
• W funkcji DISP.B() suma kwadratów jest dzielona przez liczbę obserwacji minus 1, tj. przez liczbę stopni swobody, co daje dokładniejsze, bezstronne oszacowanie wariancji populacji, z której została pobrana próba.

Odchylenie standardowe odchylenie standardowe, SD) – jest pierwiastkiem kwadratowym wariancji:

Podniesienie odchyleń do kwadratu powoduje przekształcenie skali pomiarowej w inną metrykę, która jest kwadratem pierwotnej: metry - na metry kwadratowe, dolary - na dolary kwadratowe itp. Odchylenie standardowe jest pierwiastkiem kwadratowym wariancji i dlatego przywraca nas do pierwotnych jednostek miary. Cokolwiek jest wygodniejsze.

Często konieczne jest obliczenie odchylenia standardowego po tym, jak dane zostały poddane manipulacji. I chociaż w tych przypadkach wyniki są niewątpliwie odchyleniami standardowymi, zwykle nazywa się je standardowe błędy. Istnieje kilka rodzajów błędów standardowych, w tym standardowy błąd pomiaru, standardowy błąd proporcji i standardowy błąd średniej.

Załóżmy, że zebrałeś dane o wzroście 25 losowo wybranych dorosłych mężczyzn w każdym z 50 stanów. Następnie obliczasz średni wzrost dorosłych mężczyzn w każdym stanie. Z kolei otrzymane 50 wartości średnich można uznać za obserwacje. Na tej podstawie można obliczyć ich odchylenie standardowe, tj błąd standardowy średniej. Ryż. 1. porównuje rozkład 1250 surowych wartości indywidualnych (dane dotyczące wzrostu dla 25 mężczyzn w każdym z 50 stanów) z rozkładem 50 średnich stanowych. Wzór na oszacowanie błędu standardowego średniej (czyli odchylenia standardowego średnich, a nie pojedynczych obserwacji):

gdzie jest błąd standardowy średniej; S– odchylenie standardowe obserwacji oryginalnych; N– liczba obserwacji w próbie.

Ryż. 1. Różnice w średnich między stanami są znacznie mniejsze niż różnice w indywidualnych obserwacjach.

W statystyce obowiązuje konwencja dotycząca używania liter greckich i łacińskich do przedstawiania wielkości statystycznych. Zwyczajowo oznacza się parametry populacji ogólnej literami greckimi, a przykładowe statystyki literami łacińskimi. Dlatego też mówiąc o odchyleniu standardowym populacji, zapisujemy je jako σ; jeśli weźmiemy pod uwagę odchylenie standardowe próbki, wówczas używamy zapisu s. Jeśli chodzi o symbole oznaczania średnich, nie zgadzają się one ze sobą tak dobrze. Średnia populacji jest oznaczona grecką literą μ. Jednak symbol X̅ jest tradycyjnie używany do przedstawienia średniej próbki.

wynik Z wyraża pozycję obserwacji w rozkładzie w jednostkach odchylenia standardowego. Na przykład z = 1,5 oznacza, że ​​obserwacja jest oddalona od średniej o 1,5 odchylenia standardowego. Termin wynik Z wykorzystywane do ocen indywidualnych, tj. dla wymiarów przypisanych do poszczególnych elementów próbki. Termin używany w odniesieniu do takich statystyk (takich jak średnia stanowa) wynik Z:

gdzie X̅ to średnia próbki, μ to średnia populacji, to błąd standardowy średnich zbioru próbek:

gdzie σ jest błędem standardowym populacji (pomiary indywidualne), N- wielkość próbki.

Załóżmy, że pracujesz jako instruktor w klubie golfowym. Byłeś w stanie mierzyć odległość swoich strzałów przez długi czas i wiesz, że średnia wynosi 205 jardów, a odchylenie standardowe wynosi 36 jardów. Zaproponowano ci nowy kij, twierdząc, że zwiększy on twoją odległość uderzenia o 10 jardów. Prosisz każdego z kolejnych 81 bywalców klubu o wykonanie zdjęcia próbnego z nowym kijem i zapisanie dystansu zamachu. Okazało się, że średni dystans z nowym kijem wyniósł 215 jardów. Jakie jest prawdopodobieństwo, że różnica 10 jardów (215–205) wynika wyłącznie z błędu próbkowania? Albo ujmując to inaczej: jakie jest prawdopodobieństwo, że w bardziej szczegółowych testach nowy klub nie wykaże wzrostu odległości uderzenia w porównaniu z istniejącą długoterminową średnią wynoszącą 205 jardów?

Możemy to sprawdzić, generując wynik Z. Błąd standardowy średniej:

Następnie wynik Z:

Musimy znaleźć prawdopodobieństwo, że średnia próbki będzie różnić się o 2,5 σ od średniej populacji. Jeśli prawdopodobieństwo jest małe, różnice nie wynikają z przypadku, ale z jakości nowego klubu. Excel nie posiada gotowej funkcji służącej do wyznaczania prawdopodobieństwa z-score. Można jednak użyć wzoru =1-ROZKŁ.ST.NORMALNY(wynik z,PRAWDA), gdzie funkcja ROZKŁ.NORMALNY.ST.() zwraca obszar pod krzywą normalną na lewo od wyniku z (rysunek 2).

Ryż. 2. Funkcja ROZKŁ.NORMALNY.ST.() zwraca pole pod krzywą na lewo od wartości z; Aby powiększyć obraz, kliknij go prawym przyciskiem myszy i wybierz Otwórz obraz w nowej karcie

Drugi argument funkcji ROZKŁ.NORMALNY.ST.() może przyjmować dwie wartości: PRAWDA – funkcja zwraca pole pola pod krzywą na lewo od punktu określonego w pierwszym argumencie; FAŁSZ – funkcja zwraca wysokość krzywej w punkcie określonym przez pierwszy argument.

Jeżeli średnia populacji (μ) i odchylenie standardowe (σ) nie są znane, stosuje się wartość t (patrz szczegóły). Struktury z-score i t-score różnią się tym, że do znalezienia wyniku t wykorzystuje się odchylenie standardowe s uzyskane z wyników próby, a nie znaną wartość parametru populacji σ. Krzywa normalna ma jeden kształt, a kształt rozkładu wartości t zmienia się w zależności od liczby stopni swobody df. stopnie swobody) próbki, którą reprezentuje. Liczba stopni swobody próbki jest równa n – 1, Gdzie N- wielkość próbki (ryc. 3).

Ryż. 3. Kształt rozkładów t powstających w przypadkach, gdy parametr σ jest nieznany, różni się od kształtu rozkładu normalnego

Excel ma dwie funkcje dla rozkładu t, zwanego także rozkładem Studenta: ROZKŁ.STUDENT() zwraca pole pod krzywą na lewo od danej wartości t, a ROZKŁ.STUDENT.PH() zwraca pole pod krzywą na lewo od danej wartości t Prawidłowy.

Rozdział 2. Korelacja

Korelacja jest miarą zależności pomiędzy elementami zbioru uporządkowanych par. Charakteryzuje się korelacją Współczynniki korelacji Pearsona-R. Współczynnik może przyjmować wartości z zakresu od –1,0 do +1,0.

Gdzie Sx I S y– odchylenia standardowe zmiennych X I Y, Sxy– kowariancja:

W tym wzorze kowariancja jest dzielona przez odchylenia standardowe zmiennych X I Y, usuwając w ten sposób z kowariancji efekty skalowania związane z jednostkami. Excel używa funkcji CORREL(). Nazwa tej funkcji nie zawiera elementów kwalifikujących Г i В, które są używane w nazwach funkcji takich jak STANDARDEV(), VARIANCE() czy KOWARIANCJA(). Chociaż współczynnik korelacji próbki pozwala na zafałszowane oszacowanie, przyczyna błędu jest inna niż w przypadku wariancji czy odchylenia standardowego.

W zależności od wielkości ogólnego współczynnika korelacji (często oznaczanego grecką literą ρ ), Współczynnik korelacji R daje stronnicze oszacowanie, którego efekt błędu wzrasta wraz ze zmniejszaniem się wielkości próby. Nie staramy się jednak korygować tego błędu w taki sam sposób, jak to zrobiliśmy na przykład przy obliczaniu odchylenia standardowego, gdy do odpowiedniego wzoru podstawialiśmy nie liczbę obserwacji, a liczbę stopni swobody. W rzeczywistości liczba obserwacji wykorzystanych do obliczenia kowariancji nie ma wpływu na wielkość.

Standardowy współczynnik korelacji jest przeznaczony do stosowania ze zmiennymi, które są ze sobą powiązane liniowo. Obecność nieliniowości i/lub błędów w danych (wartości odstające) prowadzi do nieprawidłowego obliczenia współczynnika korelacji. Aby zdiagnozować problemy z danymi, zaleca się utworzenie wykresów punktowych. Jest to jedyny typ wykresu w programie Excel, który traktuje oś poziomą i pionową jako oś wartości. Wykres liniowy definiuje jedną z kolumn jako oś kategorii, co zniekształca obraz danych (rys. 4).

Ryż. 4. Linie regresji wydają się takie same, ale porównaj ich równania ze sobą

Obserwacje użyte do skonstruowania wykresu liniowego są rozmieszczone w równych odległościach wzdłuż osi poziomej. Etykiety podziału wzdłuż tej osi to tylko etykiety, a nie wartości numeryczne.

Chociaż korelacja często oznacza, że ​​istnieje związek przyczynowo-skutkowy, nie można jej użyć do udowodnienia, że ​​tak jest. Statystyki nie służą do wykazania, czy teoria jest prawdziwa, czy fałszywa. Aby wykluczyć konkurencyjne wyjaśnienia wyników obserwacji, włóż planowane eksperymenty. Statystyki służą do podsumowywania informacji zebranych podczas takich eksperymentów oraz do ilościowego określenia prawdopodobieństwa, że ​​podjęta decyzja może być błędna, biorąc pod uwagę dostępną bazę dowodów.

Rozdział 3: Prosta regresja

Jeżeli dwie zmienne są ze sobą powiązane tak, że wartość współczynnika korelacji przekracza, powiedzmy, 0,5, to w tym przypadku można przewidzieć (z pewną dokładnością) nieznaną wartość jednej zmiennej ze znanej wartości drugiej . Aby uzyskać prognozowane wartości cen na podstawie danych pokazanych na ryc. 5, możesz użyć dowolnej z kilku możliwych metod, ale prawie na pewno nie użyjesz tej pokazanej na ryc. 5. Mimo to warto się z nim zapoznać, gdyż żadna inna metoda nie pozwala tak wyraźnie wykazać związku korelacji z przewidywaniem. Na ryc. 5 w zakresie B2:C12 pokazuje losową próbkę dziesięciu domów i dostarcza danych o powierzchni każdego domu (w stopach kwadratowych) i jego cenie sprzedaży.

Ryż. 5. Prognozowane wartości cen sprzedaży tworzą linię prostą

Znajdź średnie, odchylenia standardowe i współczynnik korelacji (zakres A14:C18). Oblicz współczynniki Z dla obszaru (E2:E12). Na przykład komórka E3 zawiera formułę: =(B3-$B$14)/$B$15. Oblicz wyniki Z prognozowanej ceny (F2:F12). Na przykład komórka F3 zawiera formułę: =ЕЗ*$В$18. Konwertuj wyniki Z na ceny w dolarach (H2:H12). W komórce NZ formuła wygląda następująco: =F3*$C$15+$C$14.

Należy zauważyć, że przewidywana wartość zawsze ma tendencję do przesuwania się w kierunku średniej wynoszącej 0. Im współczynnik korelacji jest bliższy zeru, tym bliższy zera jest przewidywany wynik z. W naszym przykładzie współczynnik korelacji powierzchni z ceną sprzedaży wynosi 0,67, a prognozowana cena wynosi 1,0 * 0,67, tj. 0,67. Odpowiada to przekroczeniu wartości powyżej średniej równej dwóm trzecim odchylenia standardowego. Jeżeli współczynnik korelacji byłby równy 0,5, wówczas prognozowana cena wyniosłaby 1,0 * 0,5, tj. 0,5. Odpowiada to przekroczeniu wartości powyżej średniej równej tylko połowie odchylenia standardowego. Ilekroć wartość współczynnika korelacji różni się od wartości idealnej, tj. większy niż -1,0 i mniejszy niż 1,0, wynik przewidywanej zmiennej powinien być bliższy jej średniej niż wynik zmiennej predykcyjnej (niezależnej) od jej własnego. Zjawisko to nazywa się regresją do średniej lub po prostu regresją.

Excel ma kilka funkcji służących do określania współczynników równania linii regresji (zwanego w Excelu linią trendu) y =kx + B. Do ustalenia k spełnia funkcję

=NACHYLENIE(znane_wartości_y, znane_wartości_x)

Tutaj Na jest przewidywaną zmienną, oraz X– zmienna niezależna. Należy ściśle przestrzegać tej kolejności zmiennych. Nachylenie linii regresji, współczynnik korelacji, odchylenia standardowe zmiennych i kowariancja są ze sobą ściśle powiązane (rysunek 6). Funkcja INTERMEPT() zwraca wartość przechwyconą przez linię regresji na osi pionowej:

=LIMIT(znane_wartości_y, znane_wartości_x)

Ryż. 6. Zależność odchyleń standardowych przekształca kowariancję na współczynnik korelacji i nachylenie linii regresji

Należy pamiętać, że liczba wartości x i y podanych jako argumenty funkcji SLOPE() i INTERCEPT() musi być taka sama.

W analizie regresji stosuje się inny ważny wskaźnik - R2 (R-kwadrat) lub współczynnik determinacji. Określa, jaki udział w ogólnej zmienności danych ma relacja między X I Na. W programie Excel istnieje do tego funkcja o nazwie CVPIERSON(), która przyjmuje dokładnie te same argumenty, co funkcja CORREL().

Mówi się, że dwie zmienne, których współczynnik korelacji jest niezerowy, wyjaśniają wariancję lub mają wyjaśnioną wariancję. Zwykle wyjaśnianą wariancję wyraża się w procentach. Więc R 2 = 0,81 oznacza, że ​​wyjaśniono 81% wariancji (rozrzutu) dwóch zmiennych. Pozostałe 19% wynika z wahań losowych.

Excel posiada funkcję TREND, która ułatwia obliczenia. Funkcja TREND():

• akceptuje znane wartości, które podajesz X i znane wartości Na;
• oblicza nachylenie linii regresji i stałą (przecinek);
• zwraca przewidywane wartości Na, określone poprzez zastosowanie równania regresji do znanych wartości X(ryc. 7).

Funkcja TREND() jest funkcją tablicową (jeśli nie spotkałeś się wcześniej z takimi funkcjami, polecam).

Ryż. 7. Użycie funkcji TREND() pozwala przyspieszyć i uprościć obliczenia w porównaniu do użycia pary funkcji SLOPE() i PRZECIĘCIE().

Aby wprowadzić funkcję TREND() jako formułę tablicową w komórkach G3:G12 należy zaznaczyć zakres G3:G12, wpisać formułę TREND (NW:S12;V3:B12), nacisnąć i przytrzymać klawisze i dopiero potem naciśnij klawisz . Należy pamiętać, że formuła jest ujęta w nawiasy klamrowe: ( i ). W ten sposób Excel informuje Cię, że ta formuła jest postrzegana jako formuła tablicowa. Nie wprowadzaj nawiasów samodzielnie: jeśli spróbujesz wprowadzić je samodzielnie jako część formuły, Excel potraktuje wprowadzone dane jak zwykły ciąg tekstowy.

Funkcja TREND() ma jeszcze dwa argumenty: nowe_wartości_x I konst. Pierwsza pozwala na sporządzenie prognozy na przyszłość, a druga może wymusić przejście linii regresji przez początek układu współrzędnych (wartość PRAWDA nakazuje Excelowi użycie obliczonej stałej, wartość FAŁSZ nakazuje Excelowi użycie stałej = 0 ). Excel umożliwia narysowanie na wykresie linii regresji tak, aby przechodziła przez początek układu współrzędnych. Zacznij od narysowania wykresu punktowego, a następnie kliknij prawym przyciskiem myszy jeden ze znaczników serii danych. Wybierz element w menu kontekstowym, które zostanie otwarte Dodaj linię trendu; Wybierz opcję Liniowy; w razie potrzeby przewiń panel w dół, zaznacz pole Ustaw skrzyżowanie; Upewnij się, że powiązane z nim pole tekstowe jest ustawione na 0.0.

Jeśli masz trzy zmienne i chcesz określić korelację między dwiema z nich, eliminując jednocześnie wpływ trzeciej, możesz użyć częściowa korelacja. Załóżmy, że interesuje Cię zależność pomiędzy odsetkiem mieszkańców miasta, którzy ukończyli studia, a liczbą książek w miejskich bibliotekach. Zebrałeś dane dla 50 miast, ale... Problem w tym, że oba te parametry mogą zależeć od dobrostanu mieszkańców danego miasta. Oczywiście bardzo trudno znaleźć inne 50 miast charakteryzujących się dokładnie takim samym poziomem dobrostanu mieszkańców.

Stosując metody statystyczne do kontroli wpływu zamożności zarówno na wsparcie finansowe bibliotek, jak i przystępność cenową uczelni, można uzyskać bardziej precyzyjne określenie siły związku między interesującymi zmiennymi, a mianowicie liczbą książek i liczbą absolwentów. Taka korelacja warunkowa między dwiema zmiennymi, gdy wartości innych zmiennych są stałe, nazywa się korelacją częściową. Jednym ze sposobów obliczenia tego jest użycie równania:

Gdzie RC.B. . W- współczynnik korelacji pomiędzy zmiennymi College i Books po wyłączeniu wpływu (stałej wartości) zmiennej Wealth; RC.B.- współczynnik korelacji pomiędzy zmiennymi Uczelnia i Książki; RCW- współczynnik korelacji pomiędzy zmiennymi Kolegium i Opieki Społecznej; RB.W.- współczynnik korelacji pomiędzy zmiennymi Książki i Opieka społeczna.

Natomiast korelację cząstkową można obliczyć na podstawie analizy reszt, tj. różnice pomiędzy przewidywanymi wartościami a związanymi z nimi wynikami rzeczywistych obserwacji (obie metody przedstawiono na ryc. 8).

Ryż. 8. Korelacja cząstkowa jako korelacja reszt

Aby uprościć obliczenia macierzy współczynników korelacji (B16:E19), należy skorzystać z pakietu analiz Excel (menu Dane –> Analiza –> Analiza danych). Domyślnie ten pakiet nie jest aktywny w programie Excel. Aby go zainstalować, przejdź do menu Plik –> Opcje –> Dodatki. Na dole otwartego okna OpcjePrzewyższać znajdź pole Kontrola, wybierać DodatkiPrzewyższać, Kliknij Iść. Zaznacz pole obok dodatku Pakiet analityczny. Kliknij A analiza danych, Wybierz opcję Korelacja. Jako interwał wejściowy określ $B$2:$D$13 i zaznacz pole Etykiety w pierwszej linii, określ $B$16:$E$19 jako interwał wyjściowy.

Inną możliwością jest określenie korelacji półczęściowej. Badasz na przykład wpływ wzrostu i wieku na wagę. Zatem masz dwie zmienne predykcyjne – wzrost i wiek oraz jedną zmienną predykcyjną – wagę. Chcesz wykluczyć wpływ jednej zmiennej predykcyjnej na inną, ale nie na zmienną predykcyjną:

gdzie H – wzrost, W – waga, A – wiek; W przypadku wskaźnika współczynnika korelacji półczęściowej nawiasy wskazują, która zmienna jest usuwana i z której zmiennej. W tym przypadku zapis W(H.A) wskazuje, że wpływ zmiennej Wiek jest usuwany ze zmiennej Wzrost, ale nie ze zmiennej Waga.

Wydawać by się mogło, że poruszana kwestia nie jest istotna. W końcu najważniejsze jest to, jak dokładnie działa równanie regresji ogólnej, podczas gdy problem względnych udziałów poszczególnych zmiennych w całkowitej wyjaśnionej wariancji wydaje się być drugorzędny. Jednak tak nie jest. Kiedy już zaczniesz się zastanawiać, czy w ogóle warto używać zmiennej w równaniu regresji wielokrotnej, kwestia staje się istotna. Może to mieć wpływ na ocenę poprawności wyboru modelu do analizy.

Rozdział 4. Funkcja LINEST().

Funkcja REGLINP() zwraca 10 statystyk regresji. Funkcja LINEST() jest funkcją tablicową. Aby go wprowadzić, wybierz zakres zawierający pięć wierszy i dwie kolumny, wpisz formułę i kliknij (ryc. 9):

REGLINP(B2:B21,A2:A21,PRAWDA,PRAWDA)

Ryż. 9. Funkcja LINEST(): a) wybierz zakres D2:E6, b) wprowadź formułę tak, jak pokazano na pasku formuły, c) kliknij

Funkcja REGLINP() zwraca:

• współczynnik regresji (lub nachylenie, komórka D2);
• segment (lub stała, komórka E3);
• błędy standardowe współczynnika regresji i stałej (zakres D3:E3);
• współczynnik determinacji R2 dla regresji (komórka D4);
• błąd standardowy oszacowania (komórka E4);
• Test F dla pełnej regresji (komórka D5);
• liczba stopni swobody dla resztowej sumy kwadratów (komórka E5);
• suma kwadratów regresji (komórka D6);
• resztowa suma kwadratów (komórka E6).

Przyjrzyjmy się każdej z tych statystyk i ich wzajemnemu oddziaływaniu.

Standardowy błąd w naszym przypadku jest to odchylenie standardowe obliczone dla błędów próbkowania. Oznacza to, że jest to sytuacja, w której populacja ogólna ma jedną statystykę, a próba inną. Dzielenie współczynnika regresji przez błąd standardowy daje wartość 2,092/0,818 = 2,559. Innymi słowy, współczynnik regresji wynoszący 2,092 to dwa i pół błędu standardowego od zera.

Jeżeli współczynnik regresji wynosi zero, wówczas najlepszym oszacowaniem przewidywanej zmiennej jest jej średnia. Dwa i pół błędu standardowego to dość dużo i można śmiało założyć, że współczynnik regresji dla populacji jest różny od zera.

Prawdopodobieństwo otrzymania współczynnika regresji próbki wynoszącego 2,092, jeśli jego rzeczywista wartość w populacji wynosi 0,0, można określić za pomocą funkcji

ROZKŁ.STUDENTA.PH (kryterium t = 2,559; liczba stopni swobody = 18)

Ogólnie liczba stopni swobody = n – k – 1, gdzie n to liczba obserwacji, a k to liczba zmiennych predykcyjnych.

Ta formuła zwraca wartość 0,00987, czyli zaokrągloną do 1%. Mówi nam, że jeśli współczynnik regresji dla populacji wynosi 0%, to prawdopodobieństwo otrzymania próby 20 osób, dla której oszacowany współczynnik regresji wynosi 2,092, wynosi skromne 1%.

Test F (komórka D5 na rys. 9) spełnia te same funkcje w odniesieniu do regresji pełnej, co test t w odniesieniu do współczynnika prostej regresji parami. Test F służy do sprawdzenia, czy współczynnik determinacji R 2 dla regresji jest na tyle duży, aby odrzucić hipotezę, że w populacji przyjmuje on wartość 0,0, co oznacza, że ​​nie ma wariancji wyjaśnionej przez predyktor i zmienną przewidywaną. Gdy istnieje tylko jedna zmienna predykcyjna, test F jest dokładnie równy kwadratowi testu t.

Do tej pory przyglądaliśmy się zmiennym przedziałowym. Jeśli masz zmienne, które mogą przyjmować wiele wartości, reprezentujące proste nazwy, na przykład Mężczyzna i Kobieta lub Gad, Płaz i Ryba, przedstaw je jako kod numeryczny. Takie zmienne nazywane są nominalnymi.

Statystyki R2 określa ilościowo proporcję wyjaśnionej wariancji.

Standardowy błąd szacunku. Na ryc. Rysunek 4.9 przedstawia przewidywane wartości zmiennej Waga, uzyskane na podstawie jej związku ze zmienną Wzrost. Zakres E2:E21 zawiera wartości rezydualne dla zmiennej Waga. Dokładniej, reszty te nazywane są błędami – stąd określenie standardowy błąd estymacji.

Ryż. 10. Zarówno R 2, jak i błąd standardowy oszacowania wyrażają trafność prognoz uzyskanych za pomocą regresji

Im mniejszy błąd standardowy oszacowania, tym dokładniejsze równanie regresji i tym bardziej można się spodziewać, że przewidywania uzyskane na podstawie równania będą odpowiadać rzeczywistej obserwacji. Standardowy błąd oszacowania umożliwia ilościowe określenie tych oczekiwań. Waga 95% osób o określonym wzroście będzie mieścić się w przedziale:

(wysokość * 2,092 – 3,591) ± 2,092 * 21,118

Statystyka F jest stosunkiem wariancji międzygrupowej do wariancji wewnątrzgrupowej. Nazwę tę wprowadził statystyk George Snedecor na cześć Sir, który na początku XX wieku opracował analizę wariancji (ANOVA, Analysis of Variance).

Współczynnik determinacji R 2 wyraża część całkowitej sumy kwadratów związanej z regresją. Wartość (1 – R 2) wyraża udział całkowitej sumy kwadratów związanych z resztami – błędami prognozowania. Test F można uzyskać za pomocą funkcji REGLINP (komórka F5 na rys. 11), stosując sumy kwadratów (zakres G10:J11), stosując proporcje wariancji (zakres G14:J15). Wzory można przestudiować w załączonym pliku Excel.

Ryż. 11. Obliczanie kryterium F

W przypadku stosowania zmiennych nominalnych stosuje się kodowanie fikcyjne (rysunek 12). Aby zakodować wartości, wygodnie jest użyć wartości 0 i 1. Prawdopodobieństwo F oblicza się za pomocą funkcji:

F.ROZKŁAD.PH(K2;I2;I3)

Tutaj funkcja F.DIST.PH() zwraca prawdopodobieństwo otrzymania kryterium F zgodnego z centralnym rozkładem F (rys. 13) dla dwóch zbiorów danych z liczbami stopni swobody podanymi w komórkach I2 i I3, którego wartość pokrywa się z wartością podaną w komórce K2.

Ryż. 12. Analiza regresji z wykorzystaniem zmiennych fikcyjnych

Ryż. 13. Centralny rozkład F przy λ = 0

Rozdział 5. Regresja wielokrotna

Kiedy przechodzisz od prostej regresji parami z jedną zmienną predykcyjną do regresji wielokrotnej, dodajesz jedną lub więcej zmiennych predykcyjnych. Przechowuj wartości zmiennych predykcyjnych w sąsiednich kolumnach, takich jak kolumny A i B w przypadku dwóch predyktorów lub A, B i C w przypadku trzech predyktorów. Przed wprowadzeniem formuły zawierającej funkcję REGLINP() wybierz pięć wierszy i tyle kolumn, ile jest zmiennych predykcyjnych, plus jeszcze jedna dla stałej. W przypadku regresji z dwiema zmiennymi predykcyjnymi można zastosować następującą strukturę:

REGLINP(A2: A41; B2: C41;;PRAWDA)

Podobnie w przypadku trzech zmiennych:

REGLINP(A2:A61;B2:D61;PRAWDA)

Załóżmy, że chcesz zbadać możliwy wpływ wieku i diety na poziom LDL – lipoprotein o małej gęstości, które, jak się uważa, są odpowiedzialne za powstawanie blaszek miażdżycowych powodujących zakrzepicę tętnic (ryc. 14).

Ryż. 14. Regresja wielokrotna

Wartość R2 regresji wielokrotnej (odzwierciedlona w komórce F13) jest większa niż wartość R2 dowolnej regresji prostej (E4, H4). Regresja wielokrotna wykorzystuje jednocześnie wiele zmiennych predykcyjnych. W tym przypadku R2 prawie zawsze wzrasta.

W przypadku dowolnego prostego równania regresji liniowej z jedną zmienną predykcyjną zawsze będzie istniała doskonała korelacja pomiędzy wartościami przewidywanymi a wartościami zmiennej predykcyjnej, ponieważ równanie mnoży wartości predyktorów przez jedną stałą i dodaje kolejną stałą do każdy produkt. Efekt ten nie utrzymuje się w przypadku regresji wielokrotnej.

Wyświetlanie wyników zwróconych przez funkcję LINEST() dla regresji wielokrotnej (Rysunek 15). Współczynniki regresji są wyprowadzane jako część wyników zwracanych przez funkcję LINEST(). w odwrotnej kolejności zmiennych(G–H–I odpowiada C–B–A).

Ryż. 15. Współczynniki i ich błędy standardowe są wyświetlane na arkuszu w odwrotnej kolejności.

Zasady i procedury stosowane w analizie regresji zmiennej z jednym predyktorem można łatwo dostosować do uwzględnienia wielu zmiennych predykcyjnych. Okazuje się, że znaczna część tej adaptacji polega na wyeliminowaniu wzajemnego wpływu zmiennych predykcyjnych. Z tym ostatnim wiążą się korelacje częściowe i półczęściowe (ryc. 16).

Ryż. 16. Regresję wielokrotną można wyrazić poprzez regresję reszt parami (wzory można znaleźć w pliku Excel)

W programie Excel istnieją funkcje dostarczające informacji o rozkładach t i F. Funkcje, których nazwy zawierają część ODLEGŁOŚĆ, takie jak ROZKŁ.STUDENT() i ROZKŁ.F(), przyjmują jako argument test t lub test F i zwracają prawdopodobieństwo zaobserwowania określonej wartości. Funkcje, których nazwy zawierają część OBR, takie jak STUDENT.INV() i F.INR(), przyjmują jako argument wartość prawdopodobieństwa i zwracają wartość kryterium odpowiadającą określonemu prawdopodobieństwu.

Ponieważ szukamy wartości krytycznych rozkładu t, który odcina krawędzie jego obszarów ogonowych, przekazujemy 5% jako argument jednej z funkcji STUDENT.INV(), która zwraca wartość odpowiadającą temu prawdopodobieństwu (ryc. 17, 18).

Ryż. 17. Dwustronny test t

Ryż. 18. Jednostronny test t

Ustanawiając regułę decyzyjną dla jednostronnego regionu alfa, zwiększasz moc statystyczną testu. Jeżeli rozpoczynając eksperyment, masz pewność, że masz podstawy spodziewać się dodatniego (lub ujemnego) współczynnika regresji, powinieneś przeprowadzić test jednostronny. W takim przypadku prawdopodobieństwo podjęcia właściwej decyzji o odrzuceniu hipotezy o zerowym współczynniku regresji w populacji będzie większe.

Statystycy wolą używać tego terminu próba skierowana zamiast terminu test jednoogniskowy i termin próba nieukierunkowana zamiast terminu próba dwustronna. Preferowane są terminy ukierunkowany i nieukierunkowany, ponieważ podkreślają typ hipotezy, a nie naturę ogonów rozkładu.

Podejście do oceny wpływu predyktorów w oparciu o porównanie modeli. Na ryc. Rysunek 19 przedstawia wyniki analizy regresji, która sprawdza udział zmiennej Diet w równaniu regresji.

Ryż. 19. Porównanie dwóch modeli poprzez sprawdzenie różnic w ich wynikach

Wyniki funkcji LINEST() (zakres H2:K6) są powiązane z tym, co nazywam pełnym modelem, który regresuje zmienną LDL w zmiennych Diet, Wiek i HDL. Zakres H9:J13 przedstawia obliczenia bez uwzględnienia zmiennej predykcyjnej Dieta. Nazywam to modelem limitowanym. W modelu pełnym 49,2% wariancji zmiennej zależnej LDL zostało wyjaśnione zmiennymi predykcyjnymi. W modelu ograniczonym jedynie 30,8% LDL można wyjaśnić zmiennymi Wiek i HDL. Strata R2 w wyniku wykluczenia zmiennej Dieta z modelu wynosi 0,183. W zakresie G15:L17 przeprowadzane są obliczenia, które pokazują, że prawdopodobieństwo, że wpływ zmiennej Dieta będzie losowy, wynosi jedynie 0,0288. W pozostałych 97,1% dieta ma wpływ na LDL.

Rozdział 6: Założenia i przestrogi dotyczące analizy regresji

Termin „założenie” nie jest dostatecznie zdefiniowany, a sposób jego użycia sugeruje, że jeśli założenie nie zostanie spełnione, wówczas wyniki całej analizy będą co najmniej wątpliwe, a nawet nieważne. W rzeczywistości tak nie jest, choć z pewnością zdarzają się przypadki, w których naruszenie założenia zasadniczo zmienia obraz. Podstawowe założenia: a) reszty zmiennej Y mają rozkład normalny w dowolnym punkcie X wzdłuż linii regresji; b) Wartości Y są liniowo zależne od wartości X; c) rozproszenie reszt jest w przybliżeniu takie samo w każdym punkcie X; d) nie ma zależności pomiędzy resztami.

Jeśli założenia nie odgrywają znaczącej roli, statystycy twierdzą, że analiza jest odporna na naruszenie założeń. W szczególności, gdy używasz regresji do testowania różnic między średnimi grupowymi, założenie, że wartości Y – a tym samym reszty – mają rozkład normalny, nie odgrywa znaczącej roli: testy są odporne na naruszenia założenia normalności. Ważne jest, aby analizować dane za pomocą wykresów. Na przykład zawarte w dodatku Analiza danych narzędzie Regresja.

Jeżeli dane nie spełniają założeń regresji liniowej, do Państwa dyspozycji pozostają podejścia inne niż regresja liniowa. Jednym z nich jest regresja logistyczna (ryc. 20). W pobliżu górnych i dolnych granic zmiennej predykcyjnej regresja liniowa daje nierealistyczne przewidywania.

Ryż. 20. Regresja logistyczna

Na ryc. Rysunek 6.8 przedstawia wyniki dwóch metod analizy danych mających na celu zbadanie zależności pomiędzy rocznym dochodem a prawdopodobieństwem zakupu domu. Oczywiście prawdopodobieństwo dokonania zakupu będzie rosło wraz ze wzrostem dochodów. Wykresy ułatwiają dostrzeżenie różnic między wynikami regresji liniowej przewidującej prawdopodobieństwo zakupu domu a wynikami, które można uzyskać przy zastosowaniu innego podejścia.

W żargonie statystyka odrzucenie hipotezy zerowej, choć w rzeczywistości jest ona prawdziwa, nazywa się błędem pierwszego rodzaju.

W dodatku Analiza danych oferuje wygodne narzędzie do generowania liczb losowych, pozwalające określić pożądany kształt rozkładu (np. Normalny, Dwumianowy lub Poissona), a także średnią i odchylenie standardowe.

Różnice pomiędzy funkcjami rodziny ROZKŁ.STUDENT(). Począwszy od Excela 2010, dostępne są trzy różne formy tej funkcji, które zwracają proporcję rozkładu na lewo i/lub na prawo od danej wartości testu t. Funkcja STUDENT.DIST() zwraca część pola pod krzywą rozkładu na lewo od określonej wartości testu t. Załóżmy, że masz 36 obserwacji, więc liczba stopni swobody dla analizy wynosi 34, a wartość testu t = 1,69. W tym przypadku formuła

ROZKŁAD.STUDENTA(+1,69;34;PRAWDA)

zwraca wartość 0,05, czyli 5% (rysunek 21). Trzeci argument funkcji STUDENT.DIST() może mieć wartość PRAWDA lub FAŁSZ. Jeśli ustawiona na TRUE, funkcja zwraca skumulowaną powierzchnię pod krzywą na lewo od określonego testu t, wyrażoną jako proporcja. Jeśli ma wartość FAŁSZ, funkcja zwraca względną wysokość krzywej w punkcie odpowiadającym testowi t. Inne wersje funkcji STUDENT.DIST() - STUDENT.DIST.PH() i STUDENT.DIST.2X() - jako argumenty przyjmują jedynie wartość testu t oraz liczbę stopni swobody i nie wymagają podawania trzeciego argument.

Ryż. 21. Ciemniej zacieniony obszar w lewym ogonie rozkładu odpowiada proporcji pola pod krzywą na lewo od dużej dodatniej wartości testu t

Aby wyznaczyć pole na prawo od testu t, należy skorzystać z jednego ze wzorów:

1 — STIODENT.DIST (1, 69;34;TRUE)

ROZKŁAD.STUDENTA.PH(1,69;34)

Całe pole pod krzywą musi wynosić 100%, więc odejmując od 1 ułamek pola po lewej stronie wartości testu t zwracany przez funkcję, otrzymasz ułamek pola po prawej stronie wartości testu t. Może się okazać, że lepszym rozwiązaniem będzie bezpośrednie uzyskanie interesującego Cię ułamka pola za pomocą funkcji STUDENT.DIST.PH(), gdzie PH oznacza prawy koniec rozkładu (rys. 22).

Ryż. 22. 5% obszaru alfa dla testu kierunkowego

Użycie funkcji STUDENT.DIST() lub STUDENT.DIST.PH() oznacza, że ​​wybrałeś kierunkową hipotezę roboczą. Kierunkowa hipoteza robocza w połączeniu z ustawieniem wartości alfa na 5% oznacza, że ​​wszystkie 5% zostanie umieszczone w prawym ogonie rozkładów. Hipotezę zerową będziesz musiał odrzucić tylko wtedy, gdy prawdopodobieństwo otrzymanej wartości testu t wynosi 5% lub mniej. Hipotezy kierunkowe zazwyczaj skutkują bardziej czułymi testami statystycznymi (ta większa czułość nazywana jest również większą mocą statystyczną).

W teście nieukierunkowanym wartość alfa pozostaje na tym samym poziomie 5%, ale rozkład będzie inny. Ponieważ należy uwzględnić dwa wyniki, prawdopodobieństwo wyniku fałszywie dodatniego należy rozłożyć pomiędzy dwa ogony rozkładu. Ogólnie przyjmuje się, że prawdopodobieństwo to rozkłada się równomiernie (ryc. 23).

Korzystając z tej samej uzyskanej wartości testu t i tej samej liczby stopni swobody, co w poprzednim przykładzie, skorzystaj ze wzoru

ROZKŁ.STUDENTA.2Х(1,69;34)

Bez konkretnego powodu funkcja STUDENT.DIST.2X() zwraca kod błędu #LICZBA!, jeśli jej pierwszym argumentem jest ujemna wartość testu t.

Jeżeli próbki zawierają różną ilość danych, należy skorzystać z testu t dla dwóch próbek z różnymi wariancjami zawartego w pakiecie Analiza danych.

Rozdział 7: Stosowanie regresji do testowania różnic między średnimi grupowymi

Zmienne, które wcześniej występowały pod nazwą zmienne predykcyjne, będą w tym rozdziale nazywane zmiennymi wynikowymi, a zamiast terminu zmienne predykcyjne będzie używany termin zmienne czynnikowe.

Najprostszym podejściem do kodowania zmiennej nominalnej jest fikcyjne kodowanie(ryc. 24).

Ryż. 24. Analiza regresji w oparciu o kodowanie fikcyjne

Korzystając z dowolnego rodzaju kodowania fikcyjnego, należy przestrzegać następujących zasad:

• Liczba kolumn zarezerwowanych dla nowych danych musi być równa liczbie poziomów współczynników minus
• Każdy wektor reprezentuje jeden poziom czynnika.
• Podmioty na jednym z poziomów, które często stanowią grupę kontrolną, są kodowane 0 we wszystkich wektorach.

Formuła w komórkach F2:H6 =REGLINP(A2:A22,C2:D22;TRUE) zwraca statystyki regresji. Dla porównania na ryc. Rysunek 24 przedstawia wyniki tradycyjnej analizy ANOVA zwrócone przez narzędzie. Jednokierunkowa ANOVA dodatki Analiza danych.

Kodowanie efektów. W innym typie kodowania zwanym kodowanie efektów,Średnią każdej grupy porównuje się ze średnią średnich grupowych. Ten aspekt kodowania efektów wynika z użycia -1 zamiast 0 jako kodu dla grupy, która otrzymuje ten sam kod we wszystkich wektorach kodu (rysunek 25).

Ryż. 25. Kodowanie efektów

Gdy używane jest kodowanie fikcyjne, stała wartość zwracana przez funkcję LINEST() jest średnią grupy, do której przypisano kody zerowe we wszystkich wektorach (zwykle jest to grupa odniesienia). W przypadku kodowania efektów stała jest równa średniej ogólnej (komórka J2).

Ogólny model liniowy jest użytecznym sposobem konceptualizacji składników wartości zmiennej wynikowej:

Y ij = μ + α jot + ε ij

Użycie w tym wzorze liter greckich zamiast liter łacińskich podkreśla fakt, że odnosi się ono do populacji, z której pobierane są próbki, ale można je przepisać tak, aby wskazywało, że odnosi się do próbek pobranych z danej populacji:

Y ij = Y̅ + aj + e ij

Pomysł jest taki, że każdą obserwację Y ij można postrzegać jako sumę następujących trzech składników: średniej ogólnej, μ; efekt leczenia j i j; wartość e ij, która reprezentuje odchylenie indywidualnego wskaźnika ilościowego Y ij od połączonej wartości średniej ogólnej i efektu j-tego zabiegu (ryc. 26). Celem równania regresji jest minimalizacja sumy kwadratów reszt.

Ryż. 26. Obserwacje w rozkładzie na składowe ogólnego modelu liniowego

Analiza czynników. Jeśli jednocześnie bada się związek między zmienną wynikową a dwoma lub większą liczbą czynników, wówczas w tym przypadku mówimy o zastosowaniu analizy czynnikowej. Dodanie jednego lub większej liczby czynników do jednokierunkowej analizy ANOVA może zwiększyć moc statystyczną. W jednoczynnikowej analizie wariancji wariancję zmiennej wynikowej, której nie można przypisać czynnikowi, uwzględnia się w średnim kwadracie reszt. Może się jednak zdarzyć, że ta różnica jest związana z innym czynnikiem. Następnie tę zmianę można usunąć z błędu średniokwadratowego, którego zmniejszenie prowadzi do wzrostu wartości testu F, a co za tym idzie do wzrostu mocy statystycznej testu. Nadbudowa Analiza danych zawiera narzędzie przetwarzające dwa czynniki jednocześnie (ryc. 27).

Ryż. 27. Narzędzie Dwukierunkowa analiza wariancji z powtórzeniami pakietu analitycznego

Narzędzie ANOVA użyte na tym rysunku jest przydatne, ponieważ zwraca średnią i wariancję zmiennej wynikowej, a także wartość licznika dla każdej grupy uwzględnionej w projekcie. Na stole Analiza wariancji wyświetla dwa parametry, których nie ma w wynikach jednoczynnikowej wersji narzędzia ANOVA. Zwróć uwagę na źródła zmienności Próbka I Kolumny w wierszach 27 i 28. Źródło zmienności Kolumny odnosi się do płci. Źródło zmienności Próbka odnosi się do dowolnej zmiennej, której wartości zajmują różne linie. Na ryc. W wierszach 2-6 znajduje się 27 wartości dla grupy KursLech1, w wierszach 7-11 grupa KursLech2, a w wierszach 12-16 grupa KursLechZ.

Najważniejsze jest to, że oba czynniki, Płeć (etykieta Kolumny w komórce E28) i Leczenie (etykieta Próbka w komórce E27), są uwzględnione w tabeli ANOVA jako źródła zmienności. Środki dla mężczyzn różnią się od środków dla kobiet, co stwarza źródło zmienności. Środki dla trzech zabiegów również się różnią, co stanowi kolejne źródło zmienności. Istnieje również trzecie źródło, interakcja, które odnosi się do łącznego wpływu zmiennych Płeć i Leczenie.

Rozdział 8. Analiza kowariancji

Analiza kowariancji, czyli ANCOVA (analiza kowariancji), zmniejsza błąd systematyczne i zwiększa moc statystyczną. Przypomnę, że jednym ze sposobów oceny wiarygodności równania regresji są testy F:

F = regresja stwardnienia rozsianego/resztkowa stwardnienie rozsiane

gdzie MS (średni kwadrat) jest średnim kwadratem, a wskaźniki regresji i resztowe wskazują odpowiednio regresję i składniki resztkowe. Pozostałość MS oblicza się ze wzoru:

Pozostałość MS = Pozostałość SS / Pozostałość df

gdzie SS (suma kwadratów) jest sumą kwadratów, a df jest liczbą stopni swobody. Po dodaniu kowariancji do równania regresji część całkowitej sumy kwadratów jest uwzględniana nie w SS ResiduaI, ale w regresji SS. Prowadzi to do zmniejszenia pozostałości SS, a co za tym idzie pozostałości MS. Im mniejsza wartość MS Residual, tym większy test F i tym większe prawdopodobieństwo odrzucenia hipotezy zerowej o braku różnicy między średnimi. W rezultacie następuje redystrybucja zmienności zmiennej wynikowej. W ANOVA, gdy nie bierze się pod uwagę kowariancji, zmienność staje się błędem. Jednak w ANCOVA część zmienności wcześniej przypisanej do składnika błędu jest przypisana do współzmiennej i staje się częścią regresji SS.

Rozważmy przykład, w którym ten sam zestaw danych jest analizowany najpierw za pomocą ANOVA, a następnie ANCOVA (Rysunek 28).

Ryż. 28. Analiza ANOVA wskazuje, że wyniki uzyskane z równania regresji są niewiarygodne

W badaniu porównano względny wpływ ćwiczeń fizycznych, które rozwijają siłę mięśni, i ćwiczeń poznawczych (rozwiązywanie krzyżówek), które stymulują aktywność mózgu. Badani zostali losowo przydzieleni do dwóch grup, tak aby na początku eksperymentu obie grupy były narażone na te same warunki. Po trzech miesiącach mierzono sprawność poznawczą badanych. Wyniki tych pomiarów przedstawiono w kolumnie B.

Zakres A2:C21 zawiera dane źródłowe przekazane do funkcji LINEST() w celu przeprowadzenia analizy przy użyciu kodowania efektów. Wyniki funkcji REGLINP() podane są w zakresie E2:F6, gdzie komórka E2 wyświetla współczynnik regresji powiązany z wektorem wpływu. Komórka E8 zawiera test t = 0,93, a komórka E9 sprawdza wiarygodność tego testu t. Wartość zawarta w komórce E9 wskazuje, że prawdopodobieństwo napotkania różnicy między średnimi grupowymi obserwowanymi w tym eksperymencie wynosi 36%, jeśli średnie grupowe są równe w populacji. Niewielu uważa ten wynik za istotny statystycznie.

Na ryc. Rysunek 29 pokazuje, co się stanie po dodaniu współzmiennej do analizy. W tym przypadku do zbioru danych dodałem wiek każdej osoby. Współczynnik determinacji R 2 dla równania regresji wykorzystującego współzmienną wynosi 0,80 (komórka F4). Wartość R 2 w zakresie F15:G19, w której replikowałem wyniki ANOVA uzyskane bez współzmiennej, wynosi tylko 0,05 (komórka F17). Dlatego równanie regresji zawierające współzmienną przewiduje wartości zmiennej Wynik poznawczy znacznie dokładniej niż przy użyciu samego wektora Wpływ. W przypadku ANCOVA prawdopodobieństwo przypadkowego uzyskania wartości testu F wyświetlanej w komórce F5 jest mniejsze niż 0,01%.

Ryż. 29. ANCOVA przedstawia zupełnie inny obraz

28 paź

Dzień dobry, drodzy czytelnicy bloga! Dziś porozmawiamy o regresji nieliniowej. Rozwiązanie regresji liniowej można obejrzeć pod adresem LINK.

Metodę tę wykorzystuje się głównie w modelowaniu i prognozowaniu ekonomicznym. Jego celem jest obserwacja i identyfikacja zależności pomiędzy dwoma wskaźnikami.

Główne typy regresji nieliniowych to:

• wielomian (kwadratowy, sześcienny);
• hiperboliczny;
• stateczny;
• wskazujący;
• logarytmiczny

Można również stosować różne kombinacje. Na przykład do analizy szeregów czasowych w bankowości, ubezpieczeniach i badaniach demograficznych stosuje się krzywą Gompzera, która jest rodzajem regresji logarytmicznej.

W prognozowaniu za pomocą regresji nieliniowej najważniejsze jest znalezienie współczynnika korelacji, który pokaże nam, czy istnieje ścisły związek między dwoma parametrami, czy nie. Z reguły, jeśli współczynnik korelacji jest bliski 1, wówczas istnieje związek, a prognoza będzie dość dokładna. Kolejnym ważnym elementem regresji nieliniowych jest średni błąd względny ( A ), jeśli znajduje się w przedziale<8…10%, значит модель достаточно точна.

Na tym prawdopodobnie zakończymy blok teoretyczny i przejdziemy do obliczeń praktycznych.

Mamy tabelę sprzedaży samochodów na przestrzeni 15 lat (oznaczmy to X), liczba kroków pomiarowych będzie argumentem n, mamy też przychody za te okresy (oznaczmy to Y), musimy przewidzieć, co dochód będzie w przyszłości. Zbudujmy następującą tabelę:

Do badania będziemy musieli rozwiązać równanie (zależność Y od X): y=ax 2 +bx+c+e. Jest to regresja kwadratowa parami. W tym przypadku stosujemy metodę najmniejszych kwadratów, aby znaleźć nieznane argumenty - a, b, c. Doprowadzi to do układu równań algebraicznych postaci:

Do rozwiązania tego układu posłużymy się np. metodą Cramera. Widzimy, że sumy zawarte w systemie są współczynnikami niewiadomych. Aby je obliczyć dodamy do tabeli kilka kolumn (D,E,F,G,H) i podpiszemy zgodnie ze znaczeniem obliczeń - w kolumnie D podniesiemy x do kwadratu, w E do sześcianu, w F pomnożymy wykładniki x i y, w H podnosimy x do kwadratu i mnożymy przez y.

Otrzymasz tabelę formularza wypełnioną elementami potrzebnymi do rozwiązania równania.

Stwórzmy macierz A układ składający się ze współczynników niewiadomych po lewej stronie równań. Umieśćmy go w komórce A22 i nazwijmy go „ A=„. Postępujemy zgodnie z układem równań, który wybraliśmy do rozwiązania regresji.

Oznacza to, że w komórce B21 musimy umieścić sumę kolumny, w której podnieśliśmy wskaźnik X do czwartej potęgi - F17. Odwołajmy się do komórki - „=F17”. Następnie potrzebujemy sumy kolumny, w której X został przekrojony w kostkę - E17, następnie idziemy ściśle według systemu. Będziemy zatem musieli wypełnić całą macierz.

Zgodnie z algorytmem Cramera napiszemy macierz A1, podobną do A, w której zamiast elementów pierwszej kolumny należy umieścić elementy prawych stron równań układu. Oznacza to, że suma kolumny X do kwadratu pomnożona przez Y, suma kolumny XY i suma kolumny Y.

Będziemy potrzebować jeszcze dwóch macierzy - nazwijmy je A2 i A3, w których druga i trzecia kolumna będą się składać ze współczynników prawej strony równań. Obraz będzie taki.

Kierując się wybranym algorytmem, będziemy musieli obliczyć wartości wyznaczników (wyznaczników, D) powstałych macierzy. Skorzystajmy ze wzoru MOPRED. Wyniki umieścimy w komórkach J21:K24.

Współczynniki równania według Cramera w komórkach naprzeciwko odpowiednich wyznaczników obliczymy, korzystając ze wzoru: A(w komórce M22) - „=K22/K21”; B(w komórce M23) - „=K23/K21”; Z(w komórce M24) - „=K24/K21”.

Otrzymujemy pożądane równanie sparowanej regresji kwadratowej:

y=-0,074x2 +2,151x+6,523

Oceńmy bliskość zależności liniowej za pomocą wskaźnika korelacji.

Aby obliczyć, dodaj do tabeli dodatkową kolumnę J (nazwijmy ją y*). Obliczenia będą następujące (zgodnie z otrzymanym równaniem regresji) - „=$m$22*B2*B2+$M$23*B2+$M$24.” Umieśćmy go w komórce J2. Pozostaje tylko przeciągnąć znacznik autouzupełniania do komórki J16.

Aby obliczyć sumy (średnia Y-Y) 2, dodaj do tabeli kolumny K i L z odpowiednimi wzorami. Średnią dla kolumny Y obliczamy za pomocą funkcji ŚREDNIA.

W komórce K25 umieścimy wzór na obliczenie wskaźnika korelacji - „=ROOT(1-(K17/L17))”.

Widzimy, że wartość 0,959 jest bardzo bliska 1, co oznacza, że ​​istnieje ścisła nieliniowa zależność pomiędzy sprzedażą a latami.

Pozostaje ocenić jakość dopasowania otrzymanego równania regresji kwadratowej (wskaźnik determinacji). Oblicza się go za pomocą wzoru na kwadratowy wskaźnik korelacji. Oznacza to, że formuła w komórce K26 będzie bardzo prosta - „=K25*K25”.

Współczynnik 0,920 jest bliski 1, co świadczy o wysokiej jakości dopasowania.

Ostatnim krokiem jest obliczenie błędu względnego. Dodajmy kolumnę i wpiszmy tam formułę: „=ABS((C2-J2)/C2), ABS - moduł, wartość bezwzględna. Przeciągnij znacznik w dół i w komórce M18 wyświetl wartość średnią (ŚREDNIA), przypisz do komórek format procentowy. Uzyskany wynik – 7,79% mieści się w dopuszczalnych granicach błędów<8…10%. Значит вычисления достаточно точны.

Jeśli zajdzie taka potrzeba, możemy zbudować wykres wykorzystując uzyskane wartości.

W załączeniu przykładowy plik - LINK!

Kategorie:// z 28.10.2017

Analiza regresji jest jedną z najpopularniejszych metod badań statystycznych. Można go wykorzystać do ustalenia stopnia wpływu zmiennych niezależnych na zmienną zależną. Microsoft Excel posiada narzędzia przeznaczone do wykonywania tego typu analiz. Przyjrzyjmy się, czym są i jak z nich korzystać.

Podłączenie pakietu analitycznego

Aby jednak skorzystać z funkcji umożliwiającej wykonanie analizy regresji, należy najpierw aktywować Pakiet Analiz. Dopiero wtedy na wstążce Excela pojawią się narzędzia niezbędne do tej procedury.

1. Przejdź do zakładki „Plik”.
2. Przejdź do sekcji „Ustawienia”.
3. Otworzy się okno Opcje programu Excel. Przejdź do podsekcji „Dodatki”.
4. Na samym dole okna, które zostanie otwarte, przesuń przełącznik w bloku „Zarządzanie” do pozycji „Dodatki Excel”, jeśli znajduje się w innej pozycji. Kliknij przycisk „Idź”.
5. Otworzy się okno dostępnych dodatków Excela. Zaznacz pole obok „Pakiet analityczny”. Kliknij przycisk „OK”.

Teraz, gdy przejdziemy do zakładki „Dane”, na wstążce w bloku narzędziowym „Analiza” zobaczymy nowy przycisk - „Analiza danych”.

Rodzaje analizy regresji

Istnieje kilka rodzajów regresji:

• paraboliczny;
• stateczny;
• logarytmiczny;
• wykładniczy;
• wskazujący;
• hiperboliczny;
• regresja liniowa.

Bardziej szczegółowo o wykonaniu ostatniego rodzaju analizy regresji w Excelu porozmawiamy później.

Regresja liniowa w Excelu

Poniżej dla przykładu znajduje się tabela przedstawiająca średnią dobową temperaturę powietrza na zewnątrz oraz liczbę klientów sklepu w danym dniu roboczym. Przekonajmy się, korzystając z analizy regresji, jak dokładnie warunki pogodowe w postaci temperatury powietrza mogą wpływać na frekwencję w placówce handlowej.

Ogólne równanie regresji liniowej jest następujące: Y = a0 + a1x1 +…+ akhk. W tym wzorze Y oznacza zmienną, na którą staramy się badać wpływ czynników. W naszym przypadku jest to liczba kupujących. Wartość x to różne czynniki wpływające na zmienną. Parametry a są współczynnikami regresji. Oznacza to, że to oni określają znaczenie danego czynnika. Indeks k oznacza całkowitą liczbę tych samych czynników.

Analiza wyników analizy

Wyniki analizy regresji wyświetlane są w formie tabeli w miejscu określonym w ustawieniach.

Jednym z głównych wskaźników jest R-kwadrat. Świadczy to o jakości modelu. W naszym przypadku współczynnik ten wynosi 0,705, czyli około 70,5%. Jest to akceptowalny poziom jakości. Zależność mniejsza niż 0,5 jest zła.

Kolejny ważny wskaźnik znajduje się w komórce na przecięciu wiersza „Przecięcie Y” i kolumny „Współczynniki”. Wskazuje to, jaką wartość będzie miało Y, a w naszym przypadku jest to liczba kupujących, przy wszystkich pozostałych czynnikach równych zero. W tej tabeli wartość ta wynosi 58,04.

Wartość na przecięciu kolumn „Zmienna X1” i „Współczynniki” pokazuje poziom zależności Y od X. W naszym przypadku jest to poziom zależności liczby klientów sklepu od temperatury. Współczynnik 1,31 jest uważany za dość wysoki wskaźnik wpływu.

Jak widać, korzystając z programu Microsoft Excel, utworzenie tabeli analizy regresji jest dość łatwe. Ale tylko przeszkolona osoba może pracować z danymi wyjściowymi i zrozumieć ich istotę.

Cieszymy się, że mogliśmy pomóc Ci rozwiązać problem.

Zadaj pytanie w komentarzach, szczegółowo opisując istotę problemu. Nasi specjaliści postarają się odpowiedzieć tak szybko, jak to możliwe.

Czy ten artykuł był pomocny?

Metoda regresji liniowej pozwala opisać linię prostą, która najlepiej pasuje do szeregu uporządkowanych par (x, y). Poniżej podano równanie prostej, zwane równaniem liniowym:

ŷ – oczekiwana wartość y dla danej wartości x,

x - zmienna niezależna,

a - odcinek na osi y dla prostej,

b jest nachyleniem linii prostej.

Poniższy rysunek ilustruje tę koncepcję graficznie:

Powyższy rysunek przedstawia prostą opisaną równaniem ŷ =2+0,5x. Punkt przecięcia y to punkt, w którym linia przecina oś y; w naszym przypadku a = 2. Nachylenie linii b, stosunek wzniesienia linii do jej długości, ma wartość 0,5. Nachylenie dodatnie oznacza, że ​​linia wznosi się od lewej do prawej. Jeśli b = 0, linia jest pozioma, co oznacza, że ​​nie ma związku pomiędzy zmiennymi zależnymi i niezależnymi. Innymi słowy, zmiana wartości x nie wpływa na wartość y.

ŷ i y są często mylone. Wykres przedstawia 6 uporządkowanych par punktów i linię, zgodnie z podanym równaniem

Rysunek ten przedstawia punkt odpowiadający uporządkowanej parze x = 2 i y = 4. Należy zauważyć, że oczekiwana wartość y zgodnie z linią w X= 2 to ŷ. Możemy to potwierdzić za pomocą następującego równania:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Wartość y reprezentuje rzeczywisty punkt, a wartość ŷ jest oczekiwaną wartością y przy użyciu równania liniowego dla danej wartości x.

Kolejnym krokiem jest wyznaczenie równania liniowego, które najlepiej pasuje do zbioru par uporządkowanych, o czym mówiliśmy w poprzednim artykule, gdzie ustaliliśmy postać równania metodą najmniejszych kwadratów.

Używanie programu Excel do definiowania regresji liniowej

Aby skorzystać z wbudowanego w Excel narzędzia do analizy regresji należy aktywować dodatek Pakiet analityczny. Znajdziesz go klikając w zakładkę Plik -> Opcje(2007+) w wyświetlonym oknie dialogowym OpcjePrzewyższać przejdź do zakładki Dodatki. W polu Kontrola wybierać DodatkiPrzewyższać i kliknij Iść. W wyświetlonym oknie zaznacz pole obok Pakiet analityczny, Kliknij OK.

W zakładce Dane w grupie Analiza pojawi się nowy przycisk Analiza danych.

Aby zademonstrować działanie dodatku, wykorzystajmy dane z poprzedniego artykułu, w którym chłopak i dziewczyna dzielą stół w łazience. Wprowadź dane z naszego przykładu wanny w kolumnach A i B pustego arkusza.

Przejdź do zakładki Dane, w grupie Analiza Kliknij Analiza danych. W wyświetlonym oknie Analiza danych wybierać Regresja jak pokazano na rysunku i kliknij OK.

W oknie ustaw niezbędne parametry regresji Regresja, jak pokazano na obrazku:

Kliknij OK. Poniższy rysunek przedstawia uzyskane wyniki:

Wyniki te są zgodne z tymi, które uzyskaliśmy, wykonując własne obliczenia w poprzednim artykule.

Analiza regresji to statystyczna metoda badań, która pozwala wykazać zależność konkretnego parametru od jednej lub większej liczby zmiennych niezależnych. W erze przedkomputerowej jego użycie było dość trudne, szczególnie w przypadku dużych ilości danych. Dziś, nauczywszy się budować regresję w Excelu, możesz rozwiązać złożone problemy statystyczne w ciągu zaledwie kilku minut. Poniżej konkretne przykłady z dziedziny ekonomii.

Rodzaje regresji

Samo to pojęcie zostało wprowadzone do matematyki przez Francisa Galtona w 1886 roku. Regresja ma miejsce:

• liniowy;
• paraboliczny;
• stateczny;
• wykładniczy;
• hiperboliczny;
• wskazujący;
• logarytmiczny.

Przykład 1

Rozważmy problem określenia zależności liczby członków zespołu, którzy odeszli, od przeciętnego wynagrodzenia w 6 przedsiębiorstwach przemysłowych.

Zadanie. W sześciu przedsiębiorstwach analizowano przeciętne miesięczne wynagrodzenie oraz liczbę pracowników, którzy odeszli dobrowolnie. W formie tabelarycznej mamy:

Dla zadania określenia zależności liczby odchodzących pracowników od przeciętnego wynagrodzenia w 6 przedsiębiorstwach model regresji ma postać równania Y = a0 + a1×1 +…+аkxk, gdzie хi są zmiennymi wpływającymi, ai są współczynnikami regresji, a k jest liczbą czynników.

Dla tego zadania Y jest wskaźnikiem odejścia pracowników, a czynnikiem wpływającym jest wynagrodzenie, które oznaczamy przez X.

Wykorzystanie możliwości procesora arkuszy kalkulacyjnych Excel

Analizę regresji w programie Excel należy poprzedzić zastosowaniem wbudowanych funkcji do istniejących danych tabelarycznych. Jednak do tych celów lepiej jest skorzystać z bardzo przydatnego dodatku „Analytic Pack”. Aby go aktywować, potrzebujesz:

• z zakładki „Plik” przejdź do sekcji „Opcje”;
• w oknie, które zostanie otwarte, wybierz wiersz „Dodatki”;
• kliknij przycisk „Przejdź” znajdujący się poniżej, po prawej stronie linii „Zarządzanie”;
• zaznacz pole przy nazwie „Pakiet analityczny” i potwierdź swoje działania klikając „OK”.

Jeśli wszystko zostało wykonane poprawnie, wymagany przycisk pojawi się po prawej stronie zakładki „Dane”, znajdującej się nad arkuszem Excel.

Regresja liniowa w Excelu

Teraz, gdy mamy pod ręką wszystkie niezbędne wirtualne narzędzia do przeprowadzania obliczeń ekonometrycznych, możemy przystąpić do rozwiązywania naszego problemu. Dla tego:

• Kliknij przycisk „Analiza danych”;
• w oknie, które zostanie otwarte, kliknij przycisk „Regresja”;
• w zakładce, która się pojawi, wprowadź zakres wartości dla Y (liczba odchodzących pracowników) i dla X (ich wynagrodzenia);
• Nasze działania potwierdzamy wciśnięciem przycisku „Ok”.

W rezultacie program automatycznie wypełni nowy arkusz kalkulacyjny danymi analizy regresji. Notatka! Excel umożliwia ręczne ustawienie preferowanej w tym celu lokalizacji. Może to być na przykład ten sam arkusz, w którym znajdują się wartości Y i X, lub nawet nowy skoroszyt specjalnie zaprojektowany do przechowywania takich danych.

Analiza wyników regresji dla R-kwadrat

W programie Excel dane uzyskane podczas przetwarzania danych w rozważanym przykładzie mają postać:

Przede wszystkim należy zwrócić uwagę na wartość R-kwadrat. Reprezentuje współczynnik determinacji. W tym przykładzie R-kwadrat = 0,755 (75,5%), czyli obliczone parametry modelu wyjaśniają zależność pomiędzy rozpatrywanymi parametrami w 75,5%. Im wyższa wartość współczynnika determinacji, tym lepiej wybrany model jest dostosowany do konkretnego zadania. Za prawidłowy opisuje się sytuację rzeczywistą, gdy wartość R-kwadrat jest większa niż 0,8. Jeżeli R-kwadrat wynosi tcr, to hipoteza o nieistotności wolnego składnika równania liniowego zostaje odrzucona.

W rozpatrywanym problemie dla terminu wolnego, korzystając z narzędzi Excela, uzyskano, że t = 169,20903, a p = 2,89E-12, czyli mamy zerowe prawdopodobieństwo, że poprawna hipoteza o nieistotności terminu wolnego zostanie odrzucona . Dla współczynnika dla nieznanego t=5,79405 i p=0,001158. Innymi słowy, prawdopodobieństwo odrzucenia prawidłowej hipotezy o nieistotności współczynnika dla niewiadomej wynosi 0,12%.

Można zatem argumentować, że otrzymane równanie regresji liniowej jest wystarczające.

Problem możliwości nabycia pakietu akcji

Regresję wielokrotną w programie Excel wykonuje się przy użyciu tego samego narzędzia analizy danych. Rozważmy konkretny problem aplikacji.

Zarząd spółki NNN musi podjąć decyzję o celowości zakupu 20% udziałów w MMM JSC. Koszt pakietu (SP) to 70 milionów dolarów amerykańskich. Specjaliści NNN zebrali dane na temat podobnych transakcji. Postanowiono wycenić wartość pakietu akcji według takich parametrów, wyrażonych w milionach dolarów amerykańskich, jak:

• zobowiązania (VK);
• roczny wolumen obrotu (VO);
• należności (VD);
• koszt środków trwałych (COF).

Dodatkowo wykorzystuje się parametr zaległości płacowych przedsiębiorstwa (V3 P) w tysiącach dolarów amerykańskich.

Rozwiązanie wykorzystujące procesor arkuszy kalkulacyjnych Excel

Przede wszystkim należy utworzyć tabelę danych źródłowych. To wygląda tak:

• wywołaj okno „Analiza danych”;
• wybierz sekcję „Regresja”;
• W polu „Przedział wejściowy Y” należy wpisać zakres wartości zmiennych zależnych z kolumny G;
• Kliknij na ikonę z czerwoną strzałką znajdującą się po prawej stronie okna „Przedział wprowadzania X” i zaznacz na arkuszu zakres wszystkich wartości z kolumn B, C, D, F.

Zaznacz element „Nowy arkusz” i kliknij „OK”.

Uzyskaj analizę regresji dla zadanego problemu.

Badanie wyników i wnioski

„Zbieramy” równanie regresji z zaokrąglonych danych przedstawionych powyżej w arkuszu kalkulacyjnym Excel:

SP = 0,103*SOF + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.

W bardziej znanej formie matematycznej można to zapisać jako:

y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844

Dane dla MMM SA przedstawia tabela:

Podstawiając je do równania regresji, otrzymujemy liczbę 64,72 miliona dolarów amerykańskich. Oznacza to, że nie warto kupować akcji MMM JSC, gdyż ich wartość wynosząca 70 mln dolarów jest mocno zawyżona.

Jak widać, zastosowanie arkusza kalkulacyjnego Excel i równania regresji pozwoliło na podjęcie świadomej decyzji co do możliwości realizacji bardzo konkretnej transakcji.

Teraz już wiesz, czym jest regresja. Omówione powyżej przykłady Excela pomogą Ci rozwiązać praktyczne problemy z zakresu ekonometrii.