u disciplini "Ekonometrija"
« Sveobuhvatna analiza međuodnosi finansijskih i ekonomskih pokazatelja poslovanja preduzeća"
Opcija br. 12
Završeno:
student grupe EET-312
Logunov N.Yu.
Provjereno:
vanr. Ishkhanyan M.V.
Moskva 2015
Formulacija problema
1. Kompilacija korelacijske matrice. Izbor faktora
2. Konstrukcija višestruke jednačine linearna regresija. Interpretacija parametara jednadžbe
3. Koeficijent determinacije, višestruki koeficijent korelacije
4. Procjena kvaliteta jednačine višestruke linearne regresije
4.1. Prosječna relativna greška aproksimacije
4.2.Provjera statistički značaj jednačine višestruka regresija ukupno koristeći Fišerov F test
4.3.Provjera statističke značajnosti parametara jednačine višestruke regresije. Intervalne procjene parametara
5.Primjena regresijskog modela
5.1. Tačkasta prognoza
5.2 Parcijalni koeficijenti elastičnosti i prosječni koeficijenti parcijalne elastičnosti
6. Analiza reziduala regresijskog modela (provjera premisa Gauss-Markovljeve teoreme)
6.1. Ocjene matematičko očekivanje ostaci
6.2.Provjera autokorelacije u rezidualima
7. Kriterijum Gregory Chow
Formulacija problema
Navedene su vrijednosti 6 indikatora koji karakterišu privrednu aktivnost 53 preduzeća. Obavezno:
1. Kreirajte matricu korelacije. Podesite skup nezavisnih varijabli (odaberite 2 faktora).
4.2. Testirajte statističku značajnost jednačine višestruke regresije u cjelini koristeći Fišerov F test. Izvucite zaključke
4.3. Provjerite statističku značajnost parametara jednačine višestruke regresije. Build intervalne procjene parametri. Izvucite zaključke.
5. Primjena regresijskog modela:
5.1. Koristeći konstruiranu jednačinu, dajte tačka prognoza. Nađite vrijednost proučavanog parametra y, ako je vrijednost prvog faktora (najbliže y) 110% njegove prosječne vrijednosti, vrijednost drugog faktora je 80% njegove prosječne vrijednosti. Dajte ekonomsku interpretaciju rezultata.
5.2. Pronađite koeficijente parcijalne elastičnosti i prosječne parcijalne koeficijente elastičnosti. Interpretirajte rezultate. Izvucite zaključke.
6. Analizirajte ostatke regresijskog modela (provjerite zahtjeve Gauss-Markovljeve teoreme):
6.1. Pronađite procjene matematičkog očekivanja reziduala.
6.2. Provjerite autokorelaciju u reziduama. Izvucite zaključak.
7. Podijelite uzorak na dva jednaka dijela. Uzimajući u obzir prvo i posljednje opažanje kao nezavisne uzorke, testirajte hipotezu o mogućnosti njihovog kombiniranja u jedan uzorak koristeći Gregory-Chow kriterij.
Izrada korelacione matrice. Izbor faktora
| Preduzeće br. | Y3 | X10 | X12 | X5 | X7 | X13 | |
| 13,26 | 1,45 | 167,69 | 0,78 | 1,37 | |||
| 10,16 | 1,3 | 186,1 | 0,75 | 1,49 | |||
| 13,72 | 1,37 | 220,45 | 0,68 | 1,44 | |||
| 12,85 | 1,65 | 169,3 | 0,7 | 1,42 | |||
| 10,63 | 1,91 | 39,53 | 0,62 | 1,35 | |||
| 9,12 | 1,68 | 40,41 | 0,76 | 1,39 | |||
| 25,83 | 1,94 | 102,96 | 0,73 | 1,16 | |||
| 23,39 | 1,89 | 37,02 | 0,71 | 1,27 | |||
| 14,68 | 1,94 | 45,74 | 0,69 | 1,16 | |||
| 10,05 | 2,06 | 40,07 | 0,73 | 1,25 | |||
| 13,99 | 1,96 | 45,44 | 0,68 | 1,13 | |||
| 9,68 | 1,02 | 41,08 | 0,74 | 1,1 | |||
| 10,03 | 1,85 | 136,14 | 0,66 | 1,15 | |||
| 9,13 | 0,88 | 42,39 | 0,72 | 1,23 | |||
| 5,37 | 0,62 | 37,39 | 0,68 | 1,39 | |||
| 9,86 | 1,09 | 101,78 | 0,77 | 1,38 | |||
| 12,62 | 1,6 | 47,55 | 0,78 | 1,35 | |||
| 5,02 | 1,53 | 32,61 | 0,78 | 1,42 | |||
| 21,18 | 1,4 | 103,25 | 0,81 | 1,37 | |||
| 25,17 | 2,22 | 38,95 | 0,79 | 1,41 | |||
| 19,4 | 1,32 | 81,32 | 0,77 | 1,35 | |||
| 1,48 | 67,26 | 0,78 | 1,48 | ||||
| 6,57 | 0,68 | 59,92 | 0,72 | 1,24 | |||
| 14,19 | 2,3 | 107,34 | 0,79 | 1,40 | |||
| 15,81 | 1,37 | 512,6 | 0,77 | 1,45 | |||
| 5,23 | 1,51 | 53,81 | 0,8 | 1,4 | |||
| 7,99 | 1,43 | 80,83 | 0,71 | 1,28 | |||
| 17,5 | 1,82 | 59,42 | 0,79 | 1,33 | |||
| 17,16 | 2,62 | 36,96 | 0,76 | 1,22 | |||
| 14,54 | 1,75 | 91,43 | 0,78 | 1,28 | |||
| 6,24 | 1,54 | 17,16 | 0,62 | 1,47 | |||
| 12,08 | 2,25 | 27,29 | 0,75 | 1,27 | |||
| 9,49 | 1,07 | 184,33 | 0,71 | 1,51 | |||
| 9,28 | 1,44 | 58,42 | 0,74 | 1,46 | |||
| 11,42 | 1,4 | 59,4 | 0,65 | 1,27 | |||
| 10,31 | 1,31 | 49,63 | 0,66 | 1,43 | |||
| 8,65 | 1,12 | 391,27 | 0,84 | 1,5 | |||
| 10,94 | 1,16 | 258,62 | 0,74 | 1,35 | |||
| 9,87 | 0,88 | 75,66 | 0,75 | 1,41 | |||
| 6,14 | 1,07 | 123,68 | 0,75 | 1,47 | |||
| 12,93 | 1,24 | 37,21 | 0,79 | 1,35 | |||
| 9,78 | 1,49 | 53,37 | 0,72 | 1,4 | |||
| 13,22 | 2,03 | 32,87 | 0,7 | 1,2 | |||
| 17,29 | 1,84 | 45,63 | 0,66 | 1,15 | |||
| 7,11 | 1,22 | 48,41 | 0,69 | 1,09 | |||
| 22,49 | 1,72 | 13,58 | 0,71 | 1,26 | |||
| 12,14 | 1,75 | 63,99 | 0,73 | 1,36 | |||
| 15,25 | 1,46 | 104,55 | 0,65 | 1,15 | |||
| 31,34 | 1,6 | 222,11 | 0,82 | 1,87 | |||
| 11,56 | 1,47 | 25,76 | 0,8 | 1,17 | |||
| 30,14 | 1,38 | 29,52 | 0,83 | 1,61 | |||
| 19,71 | 1,41 | 41,99 | 0,7 | 1,34 | |||
| 23,56 | 1,39 | 78,11 | 0,74 | 1,22 |
1. Kreirajte matricu korelacije. Podesite skup nezavisnih varijabli (odaberite 2 faktora).
Razmotrimo rezultujući znak Y3 i faktorske karakteristike X10, X12, X5, X7, X13 .
Kreirajmo matricu korelacije koristeći opciju “Analiza podataka→Korelacija” u MS Excel-u:
| Y3 | X10 | X12 | X5 | X7 | X13 | |
| Y3 | 1,0000 | 0,3653 | 0,0185 | 0,2891 | 0,1736 | 0,0828 |
| X10 | 0,3653 | 1,0000 | -0,2198 | -0,0166 | -0,2061 | -0,0627 |
| X12 | 0,0185 | -0,2198 | 1,0000 | 0,2392 | 0,3796 | 0,6308 |
| X5 | 0,2891 | -0,0166 | 0,2392 | 1,0000 | 0,4147 | 0,0883 |
| X7 | 0,1736 | -0,2061 | 0,3796 | 0,4147 | 1,0000 | 0,1939 |
| X13 | 0,0828 | -0,0627 | 0,6308 | 0,0883 | 0,1939 | 1,0000 |
Odabiremo 2 faktora prema kriterijima:
1) veza između Y i X treba da bude maksimalna
2) veza između Xmi treba biti minimalna
Stoga će se u sljedećim paragrafima raditi sa faktorima X10 , X5.
Izrada višestruke linearne regresijske jednačine. Interpretacija parametara jednadžbe.
2. Konstruirajte jednadžbu višestruke linearne regresije. Dajte tumačenje parametara jednačine.
Kreirajmo regresijski model koristeći paket analize “Analiza podataka→Regresija” u MS Excel-u:
| Odds | |
| Y | -20,7163 |
| X 10 | 5,7169 |
| X 5 | 34,9321 |
Jednačina regresije će izgledati ovako:
ŷ = b 0 + b 10 * x 10 + b 5 * x 5
ŷ = -20,7163-5,7169* x 10 +34,9321* x 5
1) b10 je pozitivan;
2) b5 je pozitivan;
Koeficijent determinacije, koeficijent višestruke korelacije
3. Naći koeficijent determinacije, koeficijent višestruke korelacije. Izvucite zaključke.
U regresionoj analizi koja je izvedena pomoću paketa analize “Analiza podataka → Regresija” u MS Excel-u, nalazimo tabelu “Statistika regresije”:
Višestruka R-veza između Y3 i X10,X5 je slaba
R-kvadrat - 22,05% varijacije u osobini Y objašnjava se varijacijom u osobinama X10 i X5
Procjena kvaliteta višestruke linearne regresijske jednačine
4. Procijenite kvalitet jednačine višestruke linearne regresije:
Prosječna relativna greška aproksimacije
4.1. Pronađite prosječnu relativnu grešku aproksimacije. Izvucite zaključke.
Izračunajmo predviđene vrijednosti za svako zapažanje ili koristimo kolonu „Predviđeno Y“ u tabeli „Rezidualni izlaz“ u regresionoj analizi koja se izvodi pomoću paketa analize „Analiza podataka→Regresija“ u MS Excel-u)
Izračunajmo relativne greške za svako opažanje koristeći formulu:
Izračunajmo prosječnu relativnu grešku aproksimacije koristeći formulu:
zaključak: 20% < А < 50%, качество уравнения среднее (удовлетворительное).
Prosječna greška aproksimacije- prosječno odstupanje izračunatih vrijednosti od stvarnih:Gdje je y x izračunata vrijednost iz jednadžbe.
Prosječna greška aproksimacije do 15% ukazuje na dobro prilagođen model jednačine.
Za sedam teritorija Uralske regije za 199X poznate su vrijednosti dvije karakteristike.
Obavezno:1. Da biste okarakterizirali ovisnost y od x, izračunajte parametre sljedećih funkcija:
a) linearni;
b) moć;
c) demonstrativna;
d) jednakostranična hiperbola (također morate smisliti kako unaprijed linearizirati ovaj model).
2. Procijenite svaki model do kraja prosječna greška aproksimacije A cf i Fišerov F-test.
Rješenje provodimo korištenjem online kalkulator Jednačina linearne regresije.
a) jednačina linearne regresije;
Korišćenjem grafičke metode.
Ova metoda se koristi za vizuelni prikaz oblika povezanosti proučavanih ekonomskih pokazatelja. Da biste to učinili, crta se graf u pravokutnom koordinatnom sistemu, pojedinačne vrijednosti rezultujuće karakteristike Y iscrtavaju se duž ordinatne ose, a pojedinačne vrijednosti faktorske karakteristike X crtaju se duž ose apscise.
Skup tačaka rezultantnih i faktorskih karakteristika se naziva korelaciono polje.
Na osnovu korelacionog polja, možemo pretpostaviti (za populaciju) da je odnos između svih mogućih vrijednosti X i Y linearan.
Jednačina linearne regresije je y = bx + a + ε
Ovdje je ε slučajna greška (odstupanje, poremećaj).
Razlozi za postojanje slučajne greške:
1. Neuključivanje značajnih objašnjavajućih varijabli u regresijski model;
2. Agregacija varijabli. Na primjer, funkcija ukupne potrošnje je pokušaj da se općenito izrazi zbir pojedinačnih odluka o potrošnji. Ovo je samo aproksimacija pojedinačnih odnosa koji imaju različite parametre.
3. Netačan opis strukture modela;
4. Neispravna funkcionalna specifikacija;
5. Greške u mjerenju.
Budući da su odstupanja ε i za svako specifično opažanje i slučajna i njihove vrijednosti u uzorku su nepoznate, tada:
1) iz opažanja x i i y i mogu se dobiti samo procjene parametara α i β
2) Procene parametara α i β regresionog modela su vrednosti a i b, respektivno, koje su slučajne prirode, jer odgovaraju slučajnom uzorku;
Tada će jednadžba regresije procjene (konstruirana iz podataka uzorka) imati oblik y = bx + a + ε, gdje su e i uočene vrijednosti (procjene) grešaka ε i , a a i b su procjene parametri α i β regresionog modela koji treba pronaći.
Za procjenu parametara α i β - metoda najmanjih kvadrata (metod najmanjih kvadrata).
Dobijamo b = -0,35, a = 76,88
Jednačina regresije:
y = -0,35 x + 76,88
| x | y | x 2 | y 2 | x y | y(x) | (y i -y cp) 2 | (y-y(x)) 2 | |y - y x |:y |
| 45,1 | 68,8 | 2034,01 | 4733,44 | 3102,88 | 61,28 | 119,12 | 56,61 | 0,1094 |
| 59 | 61,2 | 3481 | 3745,44 | 3610,8 | 56,47 | 10,98 | 22,4 | 0,0773 |
| 57,2 | 59,9 | 3271,84 | 3588,01 | 3426,28 | 57,09 | 4,06 | 7,9 | 0,0469 |
| 61,8 | 56,7 | 3819,24 | 3214,89 | 3504,06 | 55,5 | 1,41 | 1,44 | 0,0212 |
| 58,8 | 55 | 3457,44 | 3025 | 3234 | 56,54 | 8,33 | 2,36 | 0,0279 |
| 47,2 | 54,3 | 2227,84 | 2948,49 | 2562,96 | 60,55 | 12,86 | 39,05 | 0,1151 |
| 55,2 | 49,3 | 3047,04 | 2430,49 | 2721,36 | 57,78 | 73,71 | 71,94 | 0,172 |
| 384,3 | 405,2 | 21338,41 | 23685,76 | 22162,34 | 405,2 | 230,47 | 201,71 | 0,5699 |
Napomena: vrijednosti y(x) se nalaze iz rezultirajuće regresijske jednadžbe:
y(45.1) = -0.35*45.1 + 76.88 = 61.28
y(59) = -0,35*59 + 76,88 = 56,47
... ... ...
Greška aproksimacije
Procijenimo kvalitetu jednadžbe regresije koristeći grešku apsolutne aproksimacije. Prosječna greška aproksimacije- prosječno odstupanje izračunatih vrijednosti od stvarnih:
Pošto je greška manja od 15%, onda zadata jednačina može se koristiti kao regresija.
F-statistika. Fisherov kriterijum.
3. Tabelarna vrijednost se određuje iz Fisherove distributivne tablice za dati nivo značajnosti, uzimajući u obzir da je broj stupnjeva slobode za ukupan zbir kvadrata (veća varijansa) 1 i broj stupnjeva slobode za ostatak zbir kvadrata (manja varijansa) u linearnoj regresiji je n-2.
4. Ako je stvarna vrijednost F-testa manja od vrijednosti u tabeli, onda kažu da nema razloga za odbacivanje nulte hipoteze.
U suprotnom, nulta hipoteza se odbacuje i alternativna hipoteza o statističkom značaju jednačine u cjelini se prihvata s vjerovatnoćom (1-α).
< Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).
b) regresija moći ;
Rješenje se provodi korištenjem usluge Nelinearna regresija. Prilikom odabira navedite Snaga y = ax b
c) eksponencijalna regresija;
d) model jednakostranične hiperbole.
Sistem normalnih jednačina.
Za naše podatke sistem jednačina ima oblik
7a + 0,1291b = 405,2
0,1291a + 0,0024b = 7,51
Iz prve jednačine izražavamo a i zamjenjujemo ga u drugu jednačinu
Dobijamo b = 1054,67, a = 38,44
Jednačina regresije:
y = 1054,67 / x + 38,44
Greška aproksimacije.
Procijenimo kvalitetu jednadžbe regresije koristeći grešku apsolutne aproksimacije.
Pošto je greška manja od 15%, ova jednačina se može koristiti kao regresija.
Fisherov kriterijum.
Testiranje značajnosti regresijskog modela provodi se korištenjem Fišerovog F testa, čija se izračunata vrijednost nalazi kao omjer varijanse originalne serije zapažanja indikatora koji se proučava i nepristrasne procjene varijanse zaostalog niza za ovaj model.
Ako je izračunata vrijednost sa k1=(m) i k2=(n-m-1) stepenima slobode veća od tabelarne vrijednosti na datom nivou značajnosti, tada se model smatra značajnim.
gdje je m broj faktora u modelu.
Statistička značajnost uparene linearne regresije se procjenjuje korištenjem sljedećeg algoritma:
1. Postavlja se nulta hipoteza da je jednadžba u cjelini statistički beznačajna: H 0: R 2 =0 na nivou značajnosti α.
2. Zatim odredite stvarnu vrijednost F-kriterijuma:
gdje je m=1 za parnu regresiju.
Tabelarna vrijednost kriterija sa stupnjevima slobode k1=1 i k2=5, Fkp = 6,61
Budući da je stvarna vrijednost F< Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).
Za teritorije regiona dati su podaci za 200X.
| Broj regije | Prosječna životna plata po glavi stanovnika po danu jedne radno sposobne osobe, rub., x | Prosječna dnevna plata, rub., god |
|---|---|---|
| 1 | 78 | 133 |
| 2 | 82 | 148 |
| 3 | 87 | 134 |
| 4 | 79 | 154 |
| 5 | 89 | 162 |
| 6 | 106 | 195 |
| 7 | 67 | 139 |
| 8 | 88 | 158 |
| 9 | 73 | 152 |
| 10 | 87 | 162 |
| 11 | 76 | 159 |
| 12 | 115 | 173 |
vježba:
1. Konstruirajte korelacijsko polje i formulirajte hipotezu o obliku veze.
2. Izračunajte parametre jednačine linearne regresije
4. Koristeći prosječni (opšti) koeficijent elastičnosti, dajte komparativnu ocjenu jačine veze između faktora i rezultata.
7. Izračunajte predviđenu vrijednost rezultata ako se predviđena vrijednost faktora poveća za 10% od njegovog prosječnog nivoa. Odredite interval pouzdanosti prognoze za nivo značajnosti.
Rješenje:
Rešimo ovaj problem koristeći Excel.
1. Upoređujući dostupne podatke x i y, na primjer, rangirajući ih rastućim redom faktora x, može se uočiti postojanje direktne veze između karakteristika, kada povećanje prosječnog životnog nivoa po glavi stanovnika povećava prosječni dnevni nadnica. Na osnovu toga možemo pretpostaviti da je odnos između karakteristika direktan i da se može opisati jednolinskom jednadžbom. Isti zaključak potvrđuje i grafička analiza.
Da biste izgradili polje korelacije, možete koristiti Excel PPP. Unesite početne podatke redom: prvo x, zatim y.
Odaberite područje ćelija koje sadrži podatke.
Zatim odaberite: Insert / Scatter Plot / Scatter with Markers kao što je prikazano na slici 1.
Slika 1 Konstrukcija korelacionog polja
Analiza korelacionog polja pokazuje prisustvo zavisnosti bliske linearnoj, jer se tačke nalaze skoro u pravoj liniji.
2. Izračunati parametre jednačine linearne regresije
Koristimo ugrađenu statističku funkciju LINEST.
Za ovo:
1) Otvorite postojeću datoteku koja sadrži analizirane podatke;
2) Odaberite područje 5x2 praznih ćelija (5 redaka, 2 stupca) za prikaz rezultata statistike regresije.
3) Aktivirajte Čarobnjak za funkcije: u glavnom meniju izaberite Formule / Funkcija umetanja.
4) U prozoru Kategorija ti uzimaš Statistički, u funkcijskom prozoru - LINEST. Kliknite na dugme uredu kao što je prikazano na slici 2;
Slika 2 Dijaloški okvir čarobnjaka za funkcije
5) Popunite argumente funkcije:
Poznate vrijednosti za
Poznate vrijednosti x
Konstantno- logička vrijednost koja ukazuje na prisustvo ili odsustvo slobodnog člana u jednačini; ako je Konstanta = 1, onda se slobodni termin izračunava na uobičajen način, ako je Konstanta = 0, onda je slobodni termin 0;
Statistika- logička vrijednost koja pokazuje da li treba prikazati dodatne informacije o regresionoj analizi ili ne. Ako je Statistika = 1, tada se prikazuju dodatne informacije, ako je Statistika = 0, tada se prikazuju samo procjene parametara jednadžbe.
Kliknite na dugme uredu;
Slika 3 Dijaloški okvir Argumenti funkcije LINEST
6) Prvi element konačne tabele će se pojaviti u gornjoj lijevoj ćeliji odabranog područja. Za otvaranje cijele tabele pritisnite dugme
Dodatna statistika regresije će biti ispisana redoslijedom prikazanim na sljedećem dijagramu:
| Vrijednost koeficijenta b | Koeficijent vrijednost |
| Standardna greška b | Standardna greška a |
| Standardna greška y | |
| F-statistika | |
| Regresijski zbir kvadrata
|
Slika 4 Rezultat izračunavanja funkcije LINEST
Dobili smo nivo regresije:
Zaključujemo: Uz povećanje prosječne egzistencije po glavi stanovnika za 1 rub. prosječna dnevna plata raste u prosjeku za 0,92 rublje.
Znači 52% varijacije plate(y) objašnjava se varijacijom faktora x - prosječnog egzistencijalnog nivoa po glavi stanovnika, i 48% - djelovanjem drugih faktora koji nisu uključeni u model.
Koristeći izračunati koeficijent determinacije, može se izračunati koeficijent korelacije: 
.
Veza se ocenjuje kao bliska.
4. Pomoću prosječnog (općeg) koeficijenta elastičnosti određujemo jačinu utjecaja faktora na rezultat.
Za jednadžbu pravolinijske, prosječni (ukupni) koeficijent elastičnosti određujemo pomoću formule:
Prosječne vrijednosti ćemo pronaći odabirom područja ćelija sa x vrijednostima i odabirom Formule / AutoSum / Average, a isto ćemo učiniti sa vrijednostima y.
Slika 5 Izračunavanje prosječnih vrijednosti funkcije i argumenta
Dakle, ako se prosječni troškovi života po glavi stanovnika mijenjaju za 1% od njegove prosječne vrijednosti, prosječna dnevna plata će se promijeniti u prosjeku za 0,51%.
Korištenje alata za analizu podataka Regresija dostupno:
- rezultate regresijske statistike,
- rezultate analize varijanse,
- rezultati intervala povjerenja,
- reziduali i grafovi uklapanja regresijskih linija,
- reziduali i normalna vjerovatnoća.
Procedura je sljedeća:
1) provjerite pristup Paket analiza. U glavnom meniju izaberite: Fajl/Opcije/Dodaci.
2) U padajućoj listi Kontrola odaberite stavku Excel dodaci i pritisnite dugme Idi.
3) U prozoru Dodaci označite polje Paket analiza a zatim kliknite na dugme uredu.
Ako Paket analiza nije na listi polja Dostupni dodaci, pritisnite dugme Pregled da izvršite pretragu.
Ako dobijete poruku koja ukazuje da paket za analizu nije instaliran na vašem računaru, kliknite Da da ga instalirate.
4) U glavnom meniju izaberite: Podaci / Analiza podataka / Alati za analizu / Regresija a zatim kliknite na dugme uredu.
5) Popunite dijaloški okvir parametara ulaznih i izlaznih podataka:
Interval unosa Y- opseg koji sadrži podatke rezultujućeg atributa;
Interval unosa X- opseg koji sadrži podatke faktorske karakteristike;
Oznake- zastavicu koja pokazuje da li prvi red sadrži nazive kolona ili ne;
Konstanta - nula- zastavicu koja označava prisustvo ili odsustvo slobodnog člana u jednačini;
Izlazni interval- dovoljno je naznačiti gornju lijevu ćeliju budućeg raspona;
6) Novi radni list - možete odrediti proizvoljno ime za novi list.
Zatim kliknite na dugme uredu.
Slika 6 Dijaloški okvir za unos parametara za alat Regresija
rezultate regresiona analiza za probleme sa podacima prikazani su na slici 7.
Slika 7 Rezultat korištenja alata za regresiju
5. Ocijenimo kvalitet jednačina koristeći prosječnu grešku aproksimacije. Koristimo rezultate regresione analize prikazane na slici 8.
Slika 8 Rezultat korištenja alata za regresiju “Povlačenje ostatka”
Hajde da komponujemo novi sto kao što je prikazano na slici 9. U koloni C izračunavamo relativnu grešku aproksimacije koristeći formulu:
Slika 9 Proračun prosječne greške aproksimacije
Prosječna greška aproksimacije se izračunava pomoću formule:
Kvalitet izrađenog modela ocjenjuje se kao dobar, jer ne prelazi 8 - 10%.
6. Iz tabele sa statistikom regresije (slika 4) zapisujemo stvarnu vrijednost Fisherovog F-testa: 
Zbog 
na nivou značajnosti od 5%, onda možemo zaključiti da je jednačina regresije značajna (odnos je dokazan).
8. Procijenit ćemo statističku značajnost parametara regresije koristeći Studentovu t-statistiku i izračunavanjem intervala povjerenja za svaki indikator.
Postavili smo hipotezu H 0 o statistički beznačajnoj razlici između indikatora i nule:
.
za broj stepeni slobode
Slika 7 ima stvarne t-statističke vrijednosti:
T-test za koeficijent korelacije može se izračunati na dva načina:
Metoda I:
Gdje 
- slučajna greška koeficijenta korelacije.
Podatke za proračun ćemo uzeti iz tabele na slici 7.
Metoda II:
Stvarne vrijednosti t-statistike premašuju vrijednosti tablice:
Stoga se hipoteza H 0 odbacuje, odnosno regresijski parametri i koeficijent korelacije ne razlikuju se slučajno od nule, već su statistički značajni.
Interval pouzdanosti za parametar a definiran je kao
Za parametar a, granice od 95% kao što je prikazano na slici 7 bile su:
Interval pouzdanosti za koeficijent regresije je definiran kao
Za koeficijent regresije b, granice od 95% kao što je prikazano na slici 7 bile su:
Analiza gornje i donje granice intervala povjerenja dovodi do zaključka da s vjerovatnoćom 
parametri a i b, koji su u navedenim granicama, ne uzimaju nulte vrijednosti, tj. nisu statistički beznačajne i značajno se razlikuju od nule.
7. Dobijene procjene jednačine regresije omogućavaju njeno korištenje za prognoziranje. Ako su predviđeni troškovi života:
Tada će predviđena vrijednost troškova života biti:
Izračunavamo grešku prognoze koristeći formulu:
Gdje 
Također ćemo izračunati varijansu koristeći Excel PPP. Za ovo:
1) Aktivirajte Čarobnjak za funkcije: u glavnom meniju izaberite Formule / Funkcija umetanja.
3) Popunite opseg koji sadrži numeričke podatke faktorske karakteristike. Kliknite uredu.
Slika 10 Izračun varijanse
Dobili smo vrijednost varijanse 
Za brojanje rezidualna varijansa za jedan stepen slobode koristićemo rezultate analize varijanse kao što je prikazano na slici 7.
Intervali povjerenja za predviđanje pojedinačnih vrijednosti y sa vjerovatnoćom od 0,95 određeni su izrazom:
Interval je prilično širok, prvenstveno zbog malog obima posmatranja. Općenito, prognoza za prosječnu mjesečnu platu pokazala se pouzdanom.
Uslov problema je preuzet iz: Radionica o ekonometriji: Proc. dodatak / I.I. Eliseeva, S.V. Kurysheva, N.M. Gordeenko i drugi; Ed. I.I. Eliseeva. - M.: Finansije i statistika, 2003. - 192 str.: ilustr.
Greška aproksimacije je jedan od najčešćih problema koji se javljaju prilikom primjene određenih metoda aproksimacije izvornih podataka. Postoje različite vrste aproksimacijskih grešaka:
Greške povezane s greškama izvornih podataka;
Greške povezane s neslaganjem između aproksimativnog modela i strukture aproksimiranih podataka.
Excel ima dobro razvijenu linearnu funkciju za obradu podataka i aproksimacije koja koristi sofisticiranu matematiku. Da bismo imali predstavu o tome, okrenimo se (putem F1) opisnom dijelu ovog razvoja, koji predstavljamo skraćenicama i nekim promjenama u notaciji.
Izračunava statistiku za niz koristeći najmanje kvadrate za izračunavanje prave linije koja najbolji način aproksimira dostupne podatke. Funkcija vraća niz koji opisuje rezultirajuću liniju. Budući da se vraća niz vrijednosti, funkcija mora biti navedena kao formula niza.
Jednačina za pravu liniju je:
y=a+b1*x1+b2*x2+...bn*xn
sintaksa:
LINEST(y;x;konst;statistika)
Niz y - poznate vrednosti y.
Niz x - poznate vrijednosti x. X niz može sadržavati jedan ili više skupova varijabli.
Const je logička vrijednost koja određuje da li lažni termin a mora biti 0.
Ako je argument const TRUE, 1 ili je izostavljen, tada se a procjenjuje kao i obično. Ako je argument const FALSE ili 0, tada je a postavljeno na 0.
Statistics je Boolean vrijednost koja pokazuje da li treba vratiti dodatnu statistiku regresije. Ako je statistika TRUE ili 1, tada LINEST vraća dodatni regresijska statistika. Ako je statistika FALSE, 0 ili je izostavljena, tada LINEST vraća samo koeficijente i presek.
Dodatna statistika regresije:
se1,se2,...,sen - vrijednosti standardne greške za koeficijente b1,b2,...,bn.
sea - standardna vrijednost greške za konstantu a (sea = #N/A ako je const FALSE).
r2 je koeficijent determinizma. Uspoređuju se stvarne vrijednosti y i vrijednosti dobijene iz jednačine linije; Na osnovu rezultata poređenja izračunava se koeficijent determinizma, normalizovan od 0 do 1. Ako je jednak 1, postoji potpuna korelacija sa modelom, tj. nema razlike između stvarne i procijenjene vrijednosti y. U suprotnom slučaju, ako je koeficijent determinacije 0, tada je jednadžba regresije neuspješna u predviđanju vrijednosti y. Za informacije o tome kako se izračunava r2, pogledajte "Napomene" na kraju ovog odjeljka.
sey- standardna greška za procjenu y.
F-statistika ili F-opažena vrijednost. F-statistika se koristi za određivanje da li je uočeni odnos između zavisnih i nezavisnih varijabli rezultat slučajnosti ili ne.
df - stepeni slobode. Stupnjevi slobode su korisni za pronalaženje F-kritičnih vrijednosti u statističkoj tabeli. Da biste odredili razinu pouzdanosti modela, usporedite vrijednosti u tablici sa F-statistikom koju vraća funkcija LINEST.
ssreg je regresijski zbir kvadrata.
ssresid je rezidualni zbir kvadrata.
Slika ispod pokazuje redoslijed po kojem se vraćaju dodatne statistike regresije.
Bilješke
Odabrane informacije iz funkcije mogu se dobiti putem funkcije INDEX, na primjer:
Y-presretak (slobodni termin):
INDEX(LINEST(y,x),2)
Preciznost aproksimacije pomoću prave linije izračunate funkcijom LINEST zavisi od stepena rasipanja podataka. Što su podaci bliži pravoj liniji, to je tačniji model koji koristi funkcija LINEST. Funkcija LINEST koristi najmanje kvadrate kako bi odredila najbolje uklapanje u podatke.
Izvođenjem regresione analize, Microsoft Excel izračunava za svaku tačku kvadrat razlike između predviđene vrijednosti y i stvarne vrijednosti y. Zbir ovih kvadrata razlika naziva se rezidualni zbir kvadrata. Microsoft Excel zatim izračunava zbir kvadrata razlika između stvarnih y vrijednosti i srednje vrijednosti y, koji se naziva ukupan zbir kvadrata (regresijski zbir kvadrata + preostali zbir kvadrata). Što je manji preostali zbir kvadrata u poređenju sa ukupnim zbirom kvadrata, to je više vrijednosti koeficijent determinacije r2, koji pokazuje koliko dobro jednačina dobijena regresionom analizom objašnjava odnose između varijabli.
Imajte na umu da y vrijednosti predviđene jednadžbom regresije možda neće biti tačne ako su izvan raspona vrijednosti y koje su korištene za definiranje jednadžbe.
Primjer 1 Nagib i Y-presjek
LINEST((1;9;5;7);(0;4;2;3)) je jednako (2;1), nagib = 2 i y-presjek = 1.
Korištenje F i R2 statistike
Možete koristiti F statistiku da odredite da li je rezultat visoka vrijednost r2 je slučajan. Ako je F-opaženi veći od F-kritičnog, tada postoji odnos između varijabli. F-kritični se može dobiti iz tabele F-kritičnih vrijednosti u bilo kojoj referentnoj knjizi matematičke statistike. Da biste pronašli ovu vrijednost pomoću jednostranog testa, postavite vrijednost Alpha (vrijednost Alpha se koristi za označavanje vjerovatnoće pogrešnog zaključivanja da postoji jaka veza) jednaku 0,05, a za broj stupnjeva slobode ( obično označavaju v1 i v2), stavimo v1 = k = 4 i v2 = n - (k + 1) = 11 - (4 + 1) = 6, gdje je k broj varijabli, a n broj tačaka podataka . Iz referentne tabele, F-kritični je 4,53. Uočena F-vrijednost je 459,753674 (ova vrijednost je dobijena u primjeru koji smo izostavili), što je primjetno veće od F-kritična vrijednost 4.53. Dakle, rezultirajuća jednadžba regresije korisno za predviđanje željenog rezultata.