Analiza regresji jest statystyczną metodą badań, która pozwala pokazać zależność jednego lub drugiego parametru od jednej lub kilku zmiennych niezależnych. W erze przed komputerem jego użycie było dość trudne, szczególnie w przypadku dużych ilości danych. Dzisiaj, ucząc się jak budować regresję w Excelu, możesz rozwiązać złożone problemy statystyczne dosłownie za kilka minut. Poniżej znajdują się konkretne przykłady z dziedziny ekonomii.
Rodzaje regresji
Koncepcja ta została wprowadzona do matematyki przez Francisa Galtona w 1886 roku. Regresja się dzieje:
liniowy;
paraboliczne;
stopień;
wykładniczy;
hiperboliczny;
orientacyjny;
logarytmiczny.
Przykład 1
Rozważenie zadania polegającego na ustaleniu zależności liczby zwolnionych członków zespołu od przeciętnego wynagrodzenia w 6 przedsiębiorstwach przemysłowych.
Zadania. Sześć firm przeanalizowało średnie miesięczne wynagrodzenie i liczbę pracowników, którzy przeszli na emeryturę we własnym zakresie. W formie tabelarycznej mamy:
A
B
C
1
X
43) Wynagrodzenie
2
y
30000 rubli
58) 3
1
60
35000 rubli
4
2
35
40000 rubli 88)
5
3
20
45000 rubli
6
, 4
20 ,
50000 rubli
, 7
5
15
55,000 rubli
8
6
15
60000 rubli
dla określenia problemu liczby zwalnianych pracowników średnia płaca przez 6 firmy model regresji wygląda równanie y = a 0 + a 1 x 1 ++ i kxk, gdzie xi są zależne od zmiennych, ai - współczynniki regresji, ak - liczba czynników. Do tego celu Y - miara oddalone pracowników oraz czynniki wpływające - zarobki, które oznaczają X.
Wykorzystanie mocy kalkulacyjnego Excel „”
Analiza regresji Excel musi poprzedzać zastosowanie istniejących danych w tabeli funkcji wbudowanych. Jednak do tych celów lepiej jest użyć bardzo przydatnego dodatku "Pakiet analityczny". Aby aktywować wymagane:
na zakładce "File" idź do "Ustawienia";
w oknie wybierz wiersz "Dodatki";
kliknij przycisk "Dalej" znajdujący się poniżej, na prawo od wiersza "Zarządzanie";
umieścił znacznik wyboru obok nazwy "Pakiet analityczny" i potwierdzi twoje działania, klikając "OK".
Jeśli wykonane prawidłowo, wymagane będzie po prawej stronie w zakładce „dane”, zlokalizowanych na przycisk arkusza „excel”.
regresji liniowej w programie Excel
Teraz, kiedy jest pod ręką wszystkie niezbędne narzędzia do wdrożenia wirtualnych obliczeń ekonometrycznych może rozpocząć się rozwiązać nasz problem. Dlato:
kliknij przycisk "Analiza danych";
W oknie naciśnij przycisk "Regresja";
W wyświetlonej zakładce wprowadź zakres wartości Y (liczba zwolnionych pracowników) i X (ich wynagrodzenia);
W rezultacie program automatycznie wypełni nowy arkusz procesora tabeli danymi z analizy regresji. Zwróć uwagę! Excel ma możliwość niezależnego określenia miejsca, które preferujesz w tym celu. Na przykład może to być ta sama litera, która zawiera wartości Y i X, a nawet nowa książka zaprojektowana specjalnie do przechowywania takich danych.
Analiza wyników regresji R-kwadrat
W programie Excel dane uzyskane podczas przetwarzania danych rozpatrywanego przykładu mają postać:
Przede wszystkim należy zwrócić uwagę na wartość kwadratu R. Jest to współczynnik determinacji. W tym przykładzie R-kwadrat = 0755 (755%), czyli obliczone parametry modelu wyjaśniają zależność między rozważanymi parametrami o 755%. Im wyższa wartość współczynnika determinacji, wybrany model jest uważany za bardziej odpowiedni do określonego zadania. Uważa się, że poprawnie opisuje on rzeczywistą sytuację z wartością R-kwadratu powyżej 08. Jeśli R-kwadrat z Analizy współczynników Numer 641428 pokazuje wartość Y, jeżeli wszystkie zmienne xi w naszym modelu są zresetowane. Innymi słowy, można argumentować, że na wartości analizowanego parametru wpływają inne czynniki, które nie są opisane w konkretnym modelu. Kolejnym czynnikiem -016285 znajdującym się w komórce B18 jest ciężarWpływ zmiennej X do Y. Oznacza to, że średnia płaca pracowników w modelach dotknięte przez liczbę zwalnianych masy -016,285, że stopień wpływu dość mała. Znak "-" wskazuje, że współczynnik ma wartość ujemną. Jest to oczywiste, ponieważ każdy wie, że wyższe płace w przedsiębiorstwie, tym mniej osób wyraziło chęć wypowiedzenia umowy o pracę lub odrzucić.
wiele regresji
W ten termin odnosi się do równania komunikacji z wieloma zmiennymi niezależnymi postać: y = f (x 1 + x 2 + xm) + ?, gdzie y - zmienną Otrzymany (zmienna zależna) ix 1, x 2, xm są współczynnikami atrybutu (zmiennymi niezależnymi).
Ocena
W wielokrotnej regresji (MR) jego wykonania, przy użyciu metody najmniejszych kwadratów (MNK). Dla równań liniowych postaci Y = a + b 1 x 1 ++ b m x m +? Budujemy układ normalnych równań (patrz poniżej)
Aby zrozumieć zasadę tej metody, rozważmy przypadek dwuczynnikowy. Następnie mamy sytuację opisaną wzorem
Stamtąd otrzymujemy:
gdzie? - jest wariancją odpowiedniego znaku, odzwierciedloną w indeksie. MNC zostaną zastosowane do równania MR w znormalizowanej skali. W tym przypadku otrzymujemy równanie:
gdzie Y, T, Tx 1 t xm - zmienne standartyzyruemыe dla której średnie wynoszą 0; ? i - standaryzowane współczynniki regresji i średnie odchylenie kwadratowe - 1. Zauważ, że wszystko? I w tym przypadku określona jako znormalizowana i tsentralyzyruemыe, więc porównywanie ich ze sobą jest poprawne i aktualne. PonadtoPrzyjmuje się odliczanie czynników, odrzucając te, które mają najmniejsze wartości.
Zadania z wykorzystaniem równania regresji liniowej
Przykładowo, istnieje tabela dynamiki cen dla określonego produktu N w ciągu ostatnich 8 miesięcy. Konieczne jest podjęcie decyzji o celowości zakupu jego partii po cenie 1850 rubli za tonę.
, A
, B
C (198 )
numer miesiąca
nazwa miesiąca
cena towarów N
2
1
styczeń
1750 rubli na tonę
3
2
luty (242 )
1,755 rubli na tonę
, 4
3
marzec
1,767 rubli na tonę
5
, 4
(271 ) Kwiecień
1760 rubli z tonę
6
5
maja
1770 rubli na tonę
, 7
6
czerwiec
1,790 rubli na tonę
8
, 7
lipiec
1,810 rubli na tonę
, 9
8
, , sierpień
1,840 rubli na tonę
w celu rozwiązania tego problemu w arkuszu "Excel" są wymagane do korzystania ludzie Idoma przedstawiony w powyższym przykładzie funkcji „analizy danych.” Następnie wybierz sekcję "Regresja" i zapytajparametry Musimy pamiętać, że w przedziale pola wejściowego Y »wprowadziła szereg wartości zmiennej zależnej (w tym przypadku ceny towarów w danym miesiącu roku) i” Input przedział X »- do samodzielnego (liczba miesięcy). Potwierdź działania, klikając "OK". Na nowym arkuszu (jeśli podano) otrzymujemy dane do regresji. Budowanie mu równanie liniowe w postaci y = ax + b, gdzie jak parametry a i b są współczynnikami działania łańcucha z nazwą i numerem miesiąca i czynniki linii «Y przecięcia” literą wyników analizy regresji. Tak więc równanie regresji liniowej (SD) 3 Problem jest zapisana jako: cena towaru * N = 11714 + numer 172754. miesiąc algebraicznym y = x + 11 714 172 754
Analiza
W celu podjęcia decyzji, czy otrzymała adekwatny równanie regresji liniowej stosować wielokrotne współczynniki korelacji (CCM), i ustalenie kryterium i Fisher i testu Studenta. W tabeli "Excel" z wynikami regresji, działają one odpowiednio jako wielokrotne R-kwadraty, F-statystyka i t-statystyka. KMK R daje możliwość oszacowania bliskości probabilistycznej zależności między zmiennymi niezależnymi i zależnymi. Jego wysoka wartość wskazuje na dość silny związek między zmiennymi "Miesiąc miesiąca" i "Ceną towarów w rublach za 1 tonę". Jednak natura tego połączenia pozostaje nieznana. Kwadrat R2 współczynnik korelacji (n) jest liczbowa opis Łączny udział rozprzestrzeniania i pokazuje, że rozprzestrzeniające się z danych eksperymentalnych, która odpowiada wartości zmiennej zależnejrównanie regresji liniowej. W rozpatrywanym problemie wartość ta wynosi 848%, czyli dane statystyczne o wysokim stopniu dokładności są opisane przez otrzymaną SD. Statystyka F, zwana także kryterium Fishera, służy do oceny znaczenia zależności liniowej, odrzucając lub potwierdzając hipotezę jej istnienia. Wartość statystyki t-Studenta (test) pomaga ocenić znaczenie nieznanego czynnika lub stały termin relacji liniowej. Jeśli wartość kryterium t> & gt; t cr, wówczas hipoteza o znikomości swobodnego członka równania liniowego zostaje odrzucona. W tym problemie z bezpłatnych narzędzi w państwie „Excel” uzyskano że t = 16920903 p = 289 i E-12, które mają zerowe szanse, że będzie odrzucić hipotezę o nieistotności wolnego członka. Dla współczynnika o nieznanym t = 579405 i p = 0001158. Innymi słowy, prawdopodobieństwo odrzucenia poprawnej hipotezy o znikomości współczynnika pod niewiadomą wynosi 012%. Można więc argumentować, że otrzymane równanie regresji liniowej jest odpowiednie.
ustawiona na możliwości zakupu akcji
Regresja wieloraka w Excelu przeprowadzone za pomocą tego samego narzędzia, „analizy danych.” Rozważ konkretny problem z aplikacją. Spółka Zarządzająca „nnn” musi zdecydować, czy do zakupu 20% akcji JSC „MMM”. Koszt pakietu (JV) to 70 milionów USD. Specjaliści "NNN" zgromadzili dane o podobnych umowach. Podjęto decyzję o oszacowaniu wartości pakietu akcji zgodnie z parametrami wyrażonymi wmiliony USD, jako:
Rozrachunki z dostawcami (VK);
roczny obrót (VO);
należności (VD);
wartość środków trwałych (SOF).
Ponadto, Spółka korzystała zaległości opcją wynagrodzenia (V3 AP) tysięcy dolarów amerykańskich.
Decyzja oznacza, arkusza kalkulacyjnego Excel
Przede wszystkim, trzeba zrobić tabelę danych wyjściowych. Ma następujący kształt:
kliknij na ikonę z czerwoną strzałkę po prawej stronie okna „Interwał wejściowe X» i zwolnić zakresu arkusza kolumny wartości B, C, D, F.
Należy zwrócić uwagę na artykuł «Nowy arkusz» i kliknij «OK» . Uzyskaj analizę regresji dla tego zadania.
Badanie Wyniki i wnioski
"zbierać" z danymi zaokrąglone przedstawiony powyżej w arkuszu blachy Excel równanie regresji SP = 0103 + 0541 OFA * VO - 0031 + 0405 * VK * VD +0691 * VZP - 265844. w bardziej zwykłej postaci matematycznej można zapisać jako: y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + x5 0691 * - 265 844 dane dla JSC "MMM" przedstawione w poniższej tabeli:
OFA, USD
VO USD
VK, USD
VD dolarów
VZP USD
wspólne ryzyka, USD
1025
5355
452
415 ,
2155
6472
Podstawiając je do równania regresji uzyskaćliczba 6472 milionów dolarów. Oznacza to, że nie należy kupować akcji "MMM" JSC, ponieważ ich wartość w 70 milionach dolarów jest dość zawyżona. Jak widać, zastosowanie arkusza kalkulacyjnego Excel i równania regresji pozwoliło nam podjąć rozsądną decyzję w sprawie wykonalności całkowicie określonej transakcji. Teraz wiesz, jaka jest regresja. Przykłady w Excelu, omówione powyżej, pomogą Ci rozwiązać praktyczne problemy z zakresu ekonometrii.
