Aproksimacija eksperimentalnih podataka je metoda koja se temelji na zamjeni eksperimentalno dobivenih podataka analitičkom funkcijom koja najbliže prolazi ili se podudara u čvornim točkama s izvornim vrijednostima (podaci dobiveni tijekom eksperimenta ili eksperimenta). Trenutno postoje dva načina za definiranje analitičke funkcije:
Konstruisanjem interpolacionog polinoma n stepena koji prolazi direktno kroz sve tačke dati niz podataka. IN u ovom slučaju aproksimirajuća funkcija je predstavljena kao: interpolacijski polinom u Lagrangeovom obliku ili interpolacijski polinom u Newtonovom obliku.
Konstruiranjem n-stepenog aproksimiranog polinoma koji prolazi u neposrednoj blizini tačaka iz datog niza podataka. Dakle, aproksimirajuća funkcija izglađuje sav slučajni šum (ili greške) koji se može pojaviti tijekom eksperimenta: izmjerene vrijednosti tijekom eksperimenta zavise od slučajnih faktora koji fluktuiraju u skladu sa svojim vlastitim slučajni zakoni(greške mjerenja ili instrumenta, nepreciznost ili eksperimentalne greške). U ovom slučaju, aproksimirajuća funkcija se određuje metodom najmanjih kvadrata.
Metoda najmanjeg kvadrata(u engleskoj literaturi Ordinary Least Squares, OLS) je matematička metoda zasnovana na određivanju aproksimativne funkcije koja se konstruiše u najbližoj blizini tačaka iz datog niza eksperimentalnih podataka. Bliskost izvorne i aproksimirajuće funkcije F(x) određena je numeričkom mjerom, odnosno: zbir kvadrata odstupanja eksperimentalnih podataka od aproksimirajuće krive F(x) treba da bude najmanji.
Aproksimirajuća kriva konstruirana metodom najmanjih kvadrata
Koristi se metoda najmanjih kvadrata:
Za rješavanje preodređenih sistema jednačina kada broj jednačina premašuje broj nepoznatih;
Za pronalaženje rješenja u slučaju običnog (ne poništenog) nelinearni sistemi jednadžbe;
Za aproksimaciju vrijednosti tačaka nekom aproksimirajućom funkcijom.
Aproksimirajuća funkcija metodom najmanjih kvadrata određena je iz uvjeta minimalnog zbira kvadrata odstupanja izračunate aproksimativne funkcije iz datog niza eksperimentalnih podataka. Ovaj kriterij metode najmanjih kvadrata zapisuje se kao sljedeći izraz:
Vrijednosti izračunate aproksimirajuće funkcije u čvornim točkama,
Dati niz eksperimentalnih podataka na čvornim tačkama.
Kvadratni kriterij ima niz “dobrih” svojstava, kao što je diferencijabilnost, pružajući jedinstveno rješenje problema aproksimacije sa polinomskim aproksimirajućim funkcijama.
U zavisnosti od uslova problema, aproksimirajuća funkcija je polinom stepena m
Stepen aproksimirajuće funkcije ne zavisi od broja čvornih tačaka, ali njena dimenzija uvek mora biti manja od dimenzije (broja tačaka) datog eksperimentalnog niza podataka.
∙ Ako je stepen aproksimirajuće funkcije m=1, tada tabelarnu funkciju aproksimiramo ravnom linijom (linearna regresija).
∙ Ako je stepen aproksimirajuće funkcije m=2, tada aproksimiramo tabelu funkciju kvadratna parabola(kvadratna aproksimacija).
∙ Ako je stepen aproksimirajuće funkcije m=3, tada tabelu funkciju aproksimiramo kubnom parabolom (kubična aproksimacija).
IN opšti slučaj kada je potrebno konstruisati aproksimirajući polinom stepena m za dati tablične vrijednosti, uslov za minimalnu sumu kvadrata odstupanja po svim čvornim tačkama prepisuje se u sljedećem obliku:
- nepoznati koeficijenti aproksimirajućeg polinoma stepena m;
Broj navedenih vrijednosti u tabeli.
Neophodan uslov za postojanje minimuma funkcije je jednakost sa nulom njenih parcijalnih izvoda u odnosu na nepoznate varijable . Kao rezultat dobijamo sledeći sistem jednadžbe:
Transformirajmo rezultat linearni sistem jednadžbe: otvorite zagrade i pomjerite slobodne članove na desnu stranu izraza. Rezultirajući sistem linearnog algebarski izrazi biće napisan u sledećem obliku:
Ovaj sistem linearnih algebarskih izraza može se prepisati u matričnom obliku:
Rezultat je bio sistem linearne jednačine dimenzija m+1, koja se sastoji od m+1 nepoznatih. Ovaj sistem se može riješiti bilo kojom metodom za rješavanje linearnih problema. algebarske jednačine(na primjer, Gaussovom metodom). Kao rezultat rješenja naći će se nepoznati parametri aproksimirajuće funkcije koji daju minimalni zbir kvadrata odstupanja aproksimirajuće funkcije od izvornih podataka, tj. najbolja moguća kvadratna aproksimacija. Treba imati na umu da ako se promijeni čak i jedna vrijednost izvornih podataka, svi koeficijenti će promijeniti svoje vrijednosti, jer su u potpunosti određeni izvornim podacima.
Aproksimacija izvornih podataka linearnom zavisnošću
(linearna regresija)
Kao primjer, razmotrite tehniku za određivanje aproksimativne funkcije, koja je data u obliku linearna zavisnost. U skladu sa metodom najmanjih kvadrata, uslov za minimum zbira kvadrata odstupanja zapisuje se u sledećem obliku:
Koordinate čvorova tablice;
Nepoznati koeficijenti aproksimirajuće funkcije, koja je specificirana kao linearna ovisnost.
Neophodan uslov za postojanje minimuma funkcije je jednakost nuli njenih parcijalnih izvoda u odnosu na nepoznate varijable. Kao rezultat dobijamo sledeći sistem jednačina:
Hajde da transformišemo rezultujući linearni sistem jednačina.
Rešavamo rezultujući sistem linearnih jednačina. Koeficijenti aproksimirajuće funkcije u analitičkom obliku određuju se na sljedeći način (Cramerova metoda):
Ovi koeficijenti osiguravaju konstrukciju linearne aproksimirajuće funkcije u skladu s kriterijem minimiziranja sume kvadrata aproksimirajuće funkcije iz zadanih tabličnih vrijednosti (eksperimentalnih podataka).
Algoritam za implementaciju metode najmanjih kvadrata
1. Početni podaci:
Naveden je niz eksperimentalnih podataka sa brojem mjerenja N
Specificira se stepen aproksimirajućeg polinoma (m).
2. Algoritam proračuna:
2.1. Koeficijenti se određuju za konstruisanje sistema jednačina sa dimenzijama
Koeficijenti sistema jednadžbi ( lijeva strana jednadžbe)
- indeks broja kolone kvadratne matrice sistema jednačina
Slobodni članovi sistema linearnih jednačina ( desni deo jednadžbe)
- indeks broja reda kvadratne matrice sistema jednačina
2.2. Formiranje sistema linearnih jednadžbi sa dimenzijom .
2.3. Rješavanje sistema linearnih jednadžbi za određivanje nepoznatih koeficijenata aproksimirajućeg polinoma stepena m.
2.4. Određivanje sume kvadrata odstupanja aproksimirajućeg polinoma od originalnih vrijednosti u svim čvornim točkama
Pronađena vrijednost zbira kvadrata odstupanja je najmanja moguća.
Aproksimacija pomoću drugih funkcija
Treba napomenuti da se prilikom aproksimacije izvornih podataka u skladu s metodom najmanjih kvadrata ponekad koristi logaritamska funkcija kao aproksimirajuća funkcija, eksponencijalna funkcija i funkciju snage.
Logaritamska aproksimacija
Razmotrimo slučaj kada je aproksimirajuća funkcija data logaritamskom funkcijom oblika:
Suština metode najmanjih kvadrata je u pronalaženju parametara modela trenda koji najbolje opisuje tendenciju razvoja bilo koje slučajne pojave u vremenu ili prostoru (trend je linija koja karakteriše tendenciju ovog razvoja). Zadatak metode najmanjih kvadrata (LSM) svodi se na pronalaženje ne samo nekog trend modela, već na pronalaženje najboljeg ili optimalnog modela. Ovaj model će biti optimalan ako je zbroj kvadratnih odstupanja između uočenih stvarnih vrijednosti i odgovarajućih izračunatih vrijednosti trenda minimalan (najmanji):
Gdje - standardna devijacija između posmatrane stvarne vrednosti
i odgovarajuću izračunatu vrijednost trenda,
Stvarna (uočena) vrijednost fenomena koji se proučava,
Izračunata vrijednost modela trenda,
Broj zapažanja fenomena koji se proučava.
MNC se vrlo rijetko koristi samostalno. U pravilu se najčešće koristi samo kao neophodna tehnička tehnika u studijama korelacije. Treba imati na umu da informaciona osnova MNK može biti samo pouzdana statističke serije, a broj zapažanja ne bi trebao biti manji od 4, inače OLS procedure izglađivanja mogu izgubiti zdrav razum.
MNC komplet alata se svodi na sljedeće procedure:
Prva procedura. Ispostavlja se postoji li uopće tendencija promjene rezultantnog atributa kada se promijeni odabrani faktor-argument, ili drugim riječima, postoji li veza između “ at " i " X ».
Drugi postupak. Utvrđuje se koja linija (trajektorija) može najbolje opisati ili okarakterizirati ovaj trend.
Treći postupak.
Primjer. Recimo da imamo informacije o prosječnom prinosu suncokreta za farmu koja se proučava (Tabela 9.1).
Tabela 9.1
|
Broj zapažanja |
||||||||||
|
Produktivnost, c/ha |
Budući da je nivo tehnologije proizvodnje suncokreta u našoj zemlji ostao praktično nepromenjen u poslednjih 10 godina, to znači da su, očigledno, fluktuacije prinosa u analiziranom periodu u velikoj meri zavisile od fluktuacija vremenskih i klimatskih uslova. Da li je ovo zaista istina?
Prva OLS procedura. Ispituje se hipoteza o postojanju trenda promene prinosa suncokreta u zavisnosti od promena vremenskih i klimatskih uslova tokom analiziranih 10 godina.
U ovom primjeru, za " y " preporučljivo je uzeti prinos suncokreta, a za " x » – broj posmatrane godine u analiziranom periodu. Testiranje hipoteze o postojanju bilo kakvog odnosa između " x " i " y „može se uraditi na dva načina: ručno i korišćenjem kompjuterskih programa. Naravno, ako je dostupno kompjuterska oprema ovaj problem se rješava sam od sebe. Ali da bismo bolje razumjeli MNC alate, preporučljivo je testirati hipotezu o postojanju veze između “ x " i " y » ručno, kada su pri ruci samo olovka i običan kalkulator. U takvim slučajevima hipotezu o postojanju trenda najbolje je vizualno provjeriti lokacijom grafičke slike analizirane serije dinamike – korelacijskog polja:
Korelacijsko polje u našem primjeru nalazi se oko linije koja se polako povećava. To samo po sebi ukazuje na postojanje određenog trenda promjene prinosa suncokreta. Nemoguće je govoriti o prisutnosti bilo kakve tendencije samo kada korelacijsko polje izgleda kao krug, krug, strogo vertikalni ili striktno horizontalni oblak, ili se sastoji od haotično raštrkanih tačaka. U svim ostalim slučajevima, hipoteza o postojanju veze između “ x " i " y “, i nastaviti istraživanje.
Druga OLS procedura. Utvrđuje se koja linija (traktorija) najbolje može opisati ili okarakterizirati trend promjene prinosa suncokreta u analiziranom periodu.
Ako imate kompjutersku tehnologiju, odabir optimalnog trenda se dešava automatski. Prilikom ručne obrade, izbor optimalna funkcija provodi se, po pravilu, vizualno - po lokaciji korelacionog polja. Odnosno, na osnovu tipa grafa, bira se jednačina linije koja najbolje odgovara empirijskom trendu (stvarnoj putanji).
Kao što je poznato, u prirodi postoji ogromna raznolikost funkcionalnih ovisnosti, pa je vrlo teško vizualno analizirati čak i mali dio njih. Srećom, u realnoj ekonomskoj praksi većina odnosa može se prilično precizno opisati ili parabolom, ili hiperbolom, ili pravom linijom. S tim u vezi, uz “ručnu” opciju odabira najbolje funkcije, možete se ograničiti samo na ova tri modela.
|
hiperbola: |
||
|
|
|
Parabola drugog reda: 
:
Lako je uočiti da je u našem primjeru trend promjene prinosa suncokreta u analiziranih 10 godina najbolje okarakterisan ravnom linijom, pa će jednačina regresije biti jednačina prave linije.
Treći postupak. Parametri su izračunati jednadžba regresije koja karakteriše datu liniju, ili drugim rečima, određuje se analitička formula koja opisuje najbolji model trend.
Pronalaženje vrijednosti parametara jednadžbe regresije, u našem slučaju parametara i , je srž OLS-a. Ovaj proces se svodi na rješavanje sistema normalnih jednačina.
(9.2)
Ovaj sistem jednačina može se prilično lako riješiti Gaussovom metodom. Podsjetimo da su kao rezultat rješenja, u našem primjeru, pronađene vrijednosti parametara i. Dakle, pronađena jednačina regresije će imati sljedeći oblik: 
Ima mnogo aplikacija, jer omogućava približan prikaz date funkcije drugim jednostavnijim. LSM može biti izuzetno koristan u obradi zapažanja, a aktivno se koristi za procjenu nekih veličina na osnovu rezultata mjerenja drugih koji sadrže slučajne greške. U ovom članku ćete naučiti kako implementirati izračune najmanjih kvadrata u Excelu.
Iskazivanje problema na konkretnom primjeru
Pretpostavimo da postoje dva indikatora X i Y. Štaviše, Y zavisi od X. Budući da nas OLS zanima sa stanovišta regresione analize (u Excelu se njegove metode implementiraju pomoću ugrađenih funkcija), treba odmah preći na razmatranje konkretan problem.
Dakle, neka je X maloprodajni prostor prodavnice prehrambenih proizvoda, mjeren kvadratnim metrima, a Y godišnji promet, mjeren milionima rubalja.
Potrebno je napraviti prognozu koliki će promet (Y) trgovina imati ako ima ovaj ili onaj maloprodajni prostor. Očigledno, funkcija Y = f (X) raste, jer hipermarket prodaje više robe od tezge.
Nekoliko riječi o ispravnosti početnih podataka korištenih za predviđanje
Recimo da imamo tabelu napravljenu koristeći podatke za n prodavnica.
Prema matematičke statistike, rezultati će biti manje-više tačni ako se ispitaju podaci o najmanje 5-6 objekata. Osim toga, "anomalni" rezultati se ne mogu koristiti. Konkretno, elitni mali butik može imati promet koji je nekoliko puta veći od prometa velikih maloprodajnih objekata klase „masmarket“.
Suština metode
Podaci tabele mogu se prikazati na kartezijanskoj ravni u obliku tačaka M 1 (x 1, y 1), ... M n (x n, y n). Sada će se rješenje problema svesti na izbor aproksimirajuće funkcije y = f (x), koja ima graf koji prolazi što bliže tačkama M 1, M 2, .. M n.
Naravno, možete koristiti polinom visokog stupnja, ali ova opcija nije samo teška za implementaciju, već je i jednostavno netočna, jer neće odražavati glavni trend koji treba otkriti. Najrazumnije rješenje je traženje prave linije y = ax + b, koja najbolje aproksimira eksperimentalne podatke, tačnije, koeficijente a i b.
Procjena tačnosti
Uz bilo kakvu aproksimaciju, procjena njegove tačnosti je od posebne važnosti. Označimo sa e i razliku (odstupanje) između funkcionalne i eksperimentalne vrijednosti za tačku x i, tj. e i = y i - f (x i).
Očigledno, da biste procijenili tačnost aproksimacije, možete koristiti zbir odstupanja, odnosno, kada birate pravu liniju za približni prikaz zavisnosti X od Y, morate dati prednost onoj sa najmanju vrijednost sume e i u svim razmatranim tačkama. Međutim, nije sve tako jednostavno, jer će uz pozitivne devijacije biti i negativnih.
Problem se može riješiti korištenjem modula odstupanja ili njihovih kvadrata. Posljednja metoda je najčešće korištena. Koristi se u mnogim oblastima, uključujući regresijsku analizu (implementirana u Excelu pomoću dvije ugrađene funkcije), i odavno je dokazala svoju učinkovitost.
Metoda najmanjeg kvadrata
Excel, kao što znate, ima ugrađenu funkciju AutoSum koja vam omogućava da izračunate vrijednosti svih vrijednosti koje se nalaze u odabranom rasponu. Dakle, ništa nas neće spriječiti da izračunamo vrijednost izraza (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
IN matematička notacija izgleda:
Pošto je prvobitno donesena odluka da se aproksimira pomoću prave linije, imamo:
Dakle, zadatak pronalaženja linije koja najbolje opisuje specifična zavisnost veličine X i Y, svodi se na izračunavanje minimuma funkcije dvije varijable:
Da biste to učinili, morate parcijalne derivacije u odnosu na nove varijable a i b izjednačiti sa nulom, i riješiti primitivni sistem koji se sastoji od dvije jednadžbe sa 2 nepoznate forme:
Nakon nekoliko jednostavnih transformacija, uključujući dijeljenje sa 2 i manipulaciju suma, dobijamo:
Rješavajući ga, na primjer, Cramerovom metodom, dobijamo stacionarnu tačku sa određenim koeficijentima a * i b *. Ovo je minimum, tj. da se predvidi koliki će promet trgovina imati za određeno područje, prikladna je ravna linija y = a * x + b *, koja je regresijski model za predmetni primjer. Naravno, to vam neće omogućiti da pronađete točan rezultat, ali će vam pomoći da steknete ideju o tome hoće li se kupovina određenog područja na kredit u trgovini isplatiti.
Kako implementirati najmanje kvadrate u Excelu
Excel ima funkciju za izračunavanje vrijednosti pomoću najmanjih kvadrata. Ima sljedeći oblik: “TREND” (poznate Y vrijednosti; poznate X vrijednosti; nove X vrijednosti; konstanta). Primijenimo formulu za izračunavanje OLS-a u Excelu na našu tablicu.
Da biste to učinili, unesite znak “=” u ćeliju u kojoj bi trebao biti prikazan rezultat izračuna primjenom metode najmanjih kvadrata u Excelu i odaberite funkciju “TREND”. U prozoru koji se otvori popunite odgovarajuća polja, naglašavajući:
- raspon poznatih vrijednosti za Y (u ovom slučaju podaci za trgovinski promet);
- raspon x 1 , …x n , odnosno veličina maloprodajnog prostora;
- i poznati i nepoznate vrijednosti x, za koje trebate saznati veličinu prometa (za informacije o njihovoj lokaciji na radnom listu, pogledajte dolje).
Dodatno, formula sadrži logičku varijablu “Const”. Ako u odgovarajuće polje unesete 1, to će značiti da trebate izvršiti proračune, pod pretpostavkom da je b = 0.
Ako trebate saznati prognozu za više od jedne vrijednosti x, onda nakon unosa formule ne biste trebali pritisnuti "Enter", već morate upisati kombinaciju "Shift" + "Control" + "Enter" na tastaturi.
Neke karakteristike
Regresiona analiza može biti dostupna čak i lutkama. Excel formulu za predviđanje vrijednosti niza nepoznatih varijabli – TREND – mogu koristiti čak i oni koji nikada nisu čuli za najmanje kvadrate. Dovoljno je samo znati neke od karakteristika njegovog rada. posebno:
- Ako raspoređujete raspon poznatih vrijednosti varijable y u jedan red ili kolonu, tada svaki red (kolona) sa poznate vrednosti x će program tretirati kao zasebna varijabla.
- Ako prozor TREND ne pokazuje raspon sa poznatim x, onda ako se funkcija koristi u Excel programće ga tretirati kao niz koji se sastoji od cijelih brojeva, čiji broj odgovara rasponu sa datim vrijednostima varijable y.
- Za izlaz niza "predviđenih" vrijednosti, izraz za izračunavanje trenda se mora unijeti kao formula niza.
- Ako nove vrijednosti x nisu specificirane, funkcija TREND ih smatra jednakim poznatim. Ako nisu specificirani, tada se niz 1 uzima kao argument; 2; 3; 4;…, što je srazmerno opsegu sa već navedenim parametrima y.
- Raspon koji sadrži nove vrijednosti x mora imati iste ili više redova ili stupaca kao raspon koji sadrži date vrijednosti y. Drugim riječima, mora biti proporcionalan nezavisnim varijablama.
- Niz sa poznatim x vrijednostima može sadržavati više varijabli. Međutim, ako govorimo samo o jednom, onda je potrebno da opsezi sa datim vrijednostima x i y budu proporcionalni. U slučaju više varijabli, potrebno je da raspon sa datim y vrijednostima stane u jednu kolonu ili jedan red.
Funkcija PREDICTION
Implementirano korištenjem nekoliko funkcija. Jedna od njih se zove “PREDIKCIJA”. Sličan je "TREND", tj. daje rezultat proračuna metodom najmanjih kvadrata. Međutim, samo za jedan X, za koji je vrijednost Y nepoznata.
Sada znate formule u Excelu za lutke koje vam omogućavaju da predvidite buduću vrijednost određenog indikatora prema linearnom trendu.
Primjer.
Eksperimentalni podaci o vrijednostima varijabli X I at date su u tabeli.
Kao rezultat njihovog poravnanja, dobija se funkcija 
Koristeći metoda najmanjeg kvadrata, aproksimira ove podatke linearnom zavisnošću y=ax+b(pronaći parametre A I b). Saznajte koja od dvije linije bolje (u smislu metode najmanjih kvadrata) poravnava eksperimentalne podatke. Napravite crtež.
Suština metode najmanjih kvadrata (LSM).
Zadatak je pronaći koeficijente linearne zavisnosti na kojima je funkcija dvije varijable A I b
uzima najmanju vrijednost. Odnosno, dato A I b zbir kvadrata odstupanja eksperimentalnih podataka od pronađene prave će biti najmanji. Ovo je cijela poenta metode najmanjih kvadrata.
Dakle, rješavanje primjera se svodi na pronalaženje ekstrema funkcije dvije varijable.
Izvođenje formula za pronalaženje koeficijenata.
Sastavlja se i rješava sistem dvije jednačine sa dvije nepoznate. Pronalaženje parcijalnih izvoda funkcije 
po varijablama A I b, izjednačavamo ove izvode sa nulom. 
Rezultirajući sistem jednačina rješavamo bilo kojom metodom (npr metodom supstitucije ili Cramerova metoda) i dobiti formule za pronalaženje koeficijenata metodom najmanjih kvadrata (LSM). 
Dato A I b funkcija 
uzima najmanju vrijednost. Dokaz ove činjenice je dat ispod u tekstu na kraju stranice.
To je cijela metoda najmanjih kvadrata. Formula za pronalaženje parametra a sadrži sume ,,, i parametar n- količina eksperimentalnih podataka. Preporučujemo da se vrijednosti ovih iznosa izračunaju zasebno. Koeficijent b pronađeno nakon izračuna a.
Vrijeme je da se prisjetimo originalnog primjera.
Rješenje.
U našem primjeru n=5. Ispunjavamo tablicu radi praktičnosti izračunavanja iznosa koji su uključeni u formule potrebnih koeficijenata. 
Vrijednosti u četvrtom redu tabele dobijaju se množenjem vrijednosti 2. retka sa vrijednostima 3. reda za svaki broj i.
Vrijednosti u petom redu tabele dobijaju se kvadriranjem vrijednosti u 2. redu za svaki broj i.
Vrijednosti u posljednjoj koloni tabele su zbroji vrijednosti u redovima.
Za pronalaženje koeficijenata koristimo formule metode najmanjih kvadrata A I b. U njih zamjenjujemo odgovarajuće vrijednosti iz posljednje kolone tabele: 
dakle, y = 0,165x+2,184- željena aproksimirajuća prava linija.
Ostaje da saznamo koja od linija y = 0,165x+2,184 ili 
bolje aproksimira originalne podatke, odnosno pravi procjenu metodom najmanjih kvadrata.
Procjena greške metode najmanjih kvadrata.
Da biste to učinili, morate izračunati zbir kvadrata odstupanja originalnih podataka od ovih linija 
I 
, manja vrijednost odgovara liniji koja bolje aproksimira originalne podatke u smislu metode najmanjih kvadrata. 
Od , onda ravno y = 0,165x+2,184 bolje aproksimira originalne podatke.
Grafička ilustracija metode najmanjih kvadrata (LS).
Sve je jasno vidljivo na grafikonima. Crvena linija je pronađena ravna linija y = 0,165x+2,184, plava linija je 
, ružičaste tačke su originalni podaci.
U praksi, prilikom modeliranja različitih procesa - posebno ekonomskih, fizičkih, tehničkih, društvenih - široko se koristi jedna ili ona metoda izračunavanja približnih vrijednosti funkcija iz njihovih poznatih vrijednosti u određenim fiksnim točkama.
Ova vrsta problema aproksimacije funkcije često se javlja:
prilikom konstruiranja približnih formula za izračunavanje vrijednosti karakterističnih veličina procesa koji se proučava pomoću tabličnih podataka dobivenih kao rezultat eksperimenta;
u numeričkoj integraciji, diferencijaciji, rješenju diferencijalne jednadžbe itd.;
ako je potrebno, izračunajte vrijednosti funkcija u srednjim točkama razmatranog intervala;
pri određivanju vrijednosti karakterističnih veličina procesa izvan razmatranog intervala, posebno kod prognoziranja.
Ako za modeliranje određenog procesa određenog tablicom konstruiramo funkciju koja približno opisuje ovaj proces na temelju metode najmanjih kvadrata, ona će se zvati aproksimirajuća funkcija (regresija), a sam problem konstruiranja aproksimirajućih funkcija će se zvati problem aproksimacije.
Ovaj članak govori o mogućnostima MS Excel paketa za rješavanje ove vrste problema, osim toga daje metode i tehnike za konstruiranje (kreiranje) regresija za tabelarne funkcije (što je osnova regresione analize).
Excel ima dvije opcije za pravljenje regresije.
Dodavanje odabranih regresija ( linije trenda- linije trenda) u dijagram izgrađen na osnovu tabele podataka za karakteristike procesa koji se proučava (dostupno samo ako postoji konstruisani dijagram);
Koristeći ugrađene statističke funkcije Excel radnog lista, što vam omogućava da dobijete regresije (linije trenda) direktno iz izvorne tabele podataka.
Dodavanje linija trenda grafikonu
Za tabelu podataka koja opisuje proces i predstavljena je dijagramom, Excel ima efikasan alat za analizu regresije koji vam omogućava:
izgraditi na osnovu metode najmanjih kvadrata i dodati pet tipova regresija dijagramu, koji modeliraju proces koji se proučava sa različitim stepenom tačnosti;
dodati konstruisanu jednadžbu regresije dijagramu;
odrediti stepen korespondencije odabrane regresije sa podacima prikazanim na grafikonu.
Na osnovu podataka grafikona, Excel vam omogućava da dobijete linearne, polinomske, logaritamske, stepenaste, eksponencijalne tipove regresije, koje su određene jednadžbom:
y = y(x)
gdje je x nezavisna varijabla koja često uzima vrijednosti niza prirodnih brojeva (1; 2; 3; ...) i proizvodi, na primjer, odbrojavanje vremena procesa koji se proučava (karakteristike).
1 . Linearna regresija je dobra za modeliranje karakteristika čije se vrijednosti povećavaju ili smanjuju konstantnom brzinom. Ovo je najjednostavniji model za konstruisanje za proces koji se proučava. Konstruiše se u skladu sa jednačinom:
y = mx + b
gdje je m tangenta ugla nagiba linearna regresija do ose apscise; b - koordinata tačke preseka linearne regresije sa ordinatnom osom.
2 . Polinomska linija trenda korisna je za opisivanje karakteristika koje imaju nekoliko različitih ekstrema (maksimuma i minimuma). Izbor stepena polinoma određen je brojem ekstrema karakteristike koja se proučava. Dakle, polinom drugog stepena može dobro opisati proces koji ima samo jedan maksimum ili minimum; polinom trećeg stepena - ne više od dva ekstrema; polinom četvrtog stepena - ne više od tri ekstrema, itd.
U ovom slučaju, linija trenda se konstruiše u skladu sa jednadžbom:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
gdje su koeficijenti c0, c1, c2,...c6 konstante čije se vrijednosti određuju tokom izgradnje.
3 . Logaritamska linija trenda se uspješno koristi pri modeliranju karakteristika čije se vrijednosti u početku brzo mijenjaju, a zatim se postupno stabiliziraju.
y = c ln(x) + b
4 . Linija trenda po stepenu daje dobre rezultate ako vrijednosti odnosa koji se proučavaju karakterizira stalna promjena stope rasta. Primjer takve zavisnosti je graf jednoliko ubrzanog kretanja automobila. Ako u podacima postoje nula ili negativne vrijednosti, ne možete koristiti liniju trenda snage.
Konstruisano u skladu sa jednačinom:
y = c xb
gdje su koeficijenti b, c konstante.
5 . Eksponencijalnu liniju trenda treba koristiti kada se stopa promjene podataka kontinuirano povećava. Za podatke koji sadrže nulte ili negativne vrijednosti, ova vrsta aproksimacije također nije primjenjiva.
Konstruisano u skladu sa jednačinom:
y = c ebx
gdje su koeficijenti b, c konstante.
Prilikom odabira linije trenda, Excel automatski izračunava vrijednost R2, koja karakterizira pouzdanost aproksimacije: nego bliža vrijednost R2 na jedinicu, što pouzdanije linija trenda aproksimira proces koji se proučava. Ako je potrebno, vrijednost R2 se uvijek može prikazati na grafikonu.
Određeno formulom:
Da dodate liniju trenda seriji podataka:
aktivirajte grafikon na osnovu niza podataka, tj. kliknite unutar područja grafikona. Stavka Dijagram će se pojaviti u glavnom meniju;
nakon klika na ovu stavku, na ekranu će se pojaviti meni u kojem treba izabrati komandu Dodaj liniju trenda.
Iste radnje se lako mogu implementirati pomicanjem pokazivača miša preko grafa koji odgovara jednoj od serija podataka i desnim klikom; U kontekstualnom meniju koji se pojavi izaberite naredbu Dodaj liniju trenda. Dijalog Trendline će se pojaviti na ekranu sa otvorenom karticom Type (Slika 1).
Nakon ovoga trebate:
Odaberite željeni tip linije trenda na kartici Tip (Linearni tip je odabran prema zadanim postavkama). Za tip polinoma, u polju Stepen navedite stepen izabranog polinoma.
1 . Polje Izgrađene serije navodi sve serije podataka u dotičnom grafikonu. Da biste dodali liniju trenda određenoj seriji podataka, odaberite njeno ime u polju Izgrađena serija.
Ako je potrebno, odlaskom na karticu Parameters (Slika 2), možete podesiti sljedeće parametre za liniju trenda:
promijenite naziv linije trenda u polju Naziv aproksimativne (izglađene) krive.
podesite broj perioda (unaprijed ili unazad) za prognozu u polju Prognoza;
prikazati jednadžbu linije trenda u oblasti dijagrama, za koju treba da omogućite potvrdni okvir za prikaz jednačine na dijagramu;
prikažite vrijednost pouzdanosti aproksimacije R2 u području dijagrama, za šta biste trebali omogućiti potvrdni okvir Postavi vrijednost pouzdanosti aproksimacije na dijagram (R^2);
postavite tačku preseka linije trenda sa Y osom, za koju treba da omogućite checkbox za presek krive sa Y osom u tački;
Kliknite na dugme OK da zatvorite dijaloški okvir.
Da biste počeli uređivati već nacrtanu liniju trenda, postoje tri načina:
koristite naredbu Odabrana linija trenda iz izbornika Format, nakon što ste prethodno odabrali liniju trenda;
izaberite komandu Format linije trenda iz kontekstnog menija, koja se poziva desnim klikom na liniju trenda;
dvaput kliknite na liniju trenda.
Na ekranu će se pojaviti dijaloški okvir Trend Line Format (slika 3), koji sadrži tri kartice: Pogled, Tip, Parametri, a sadržaj posljednje dvije potpuno se poklapa sa sličnim karticama dijaloga Trend Line (Sl. 1). -2). Na kartici Prikaz možete postaviti vrstu linije, njenu boju i debljinu.
Za brisanje linije trenda koja je već nacrtana, odaberite liniju trenda koju želite izbrisati i pritisnite tipku Delete.
Prednosti razmatranog alata regresione analize su:
relativna lakoća konstruisanja linije trenda na grafikonima bez kreiranja tabele podataka za to;
prilično široka lista tipova predloženih linija trenda, a ova lista uključuje najčešće korištene vrste regresije;
sposobnost predviđanja ponašanja procesa koji se proučava proizvoljnim (u granicama zdravog razuma) brojem koraka naprijed i nazad;
sposobnost dobijanja jednadžbe linije trenda u analitičkom obliku;
mogućnost, ako je potrebno, dobijanja procjene pouzdanosti aproksimacije.
Nedostaci uključuju sljedeće:
izgradnja linije trenda se izvodi samo ako postoji dijagram izgrađen na nizu podataka;
proces generiranja nizova podataka za karakteristiku koja se proučava na temelju jednadžbi linije trenda dobivenih za nju je donekle pretrpan: potrebne regresijske jednadžbe se ažuriraju sa svakom promjenom vrijednosti izvorne serije podataka, ali samo unutar područja dijagrama , dok serije podataka, generisan na osnovu stare jednadžbe linije trenda, ostaje nepromijenjen;
U izveštajima zaokretnog grafikona, promena prikaza grafikona ili povezanog izveštaja izvedene tabele ne čuva postojeće linije trenda, što znači da pre nego što nacrtate linije trenda ili na drugi način formatirate izveštaj zaokretnog grafikona, trebalo bi da se uverite da izgled izveštaja ispunjava zahtevane zahteve.
Linije trenda se mogu koristiti za dopunu nizova podataka predstavljenih na grafikonima kao što su grafikoni, histogrami, ravni nestandardizirani dijagrami područja, trakasti grafikoni, dijagrami raspršivanja, mjehurasti grafikoni i berzanski grafikoni.
Ne možete dodati linije trenda serijama podataka u 3D, normaliziranim, radarskim, tortnim i krofnim grafikonima.
Korištenje ugrađenih funkcija programa Excel
Excel takođe ima alat za regresijsku analizu za crtanje linija trenda izvan područja grafikona. Postoji veliki broj statističkih funkcija radnog lista koje možete koristiti u tu svrhu, ali sve vam dozvoljavaju samo da izgradite linearne ili eksponencijalne regresije.
Excel ima nekoliko funkcija za konstruiranje linearne regresije, posebno:
KOSINA i REZ.
TREND;
Kao i nekoliko funkcija za konstruiranje eksponencijalne linije trenda, posebno:
LGRFPRIBL.
Treba napomenuti da su tehnike za konstruisanje regresija korišćenjem funkcija TREND i RAST skoro iste. Isto se može reći i za par funkcija LINEST i LGRFPRIBL. Za ove četiri funkcije, kreiranje tablice vrijednosti koristi Excel funkcije kao što su formule niza, što donekle otežava proces izgradnje regresija. Napomenimo i to da se konstrukcija linearne regresije, po našem mišljenju, najlakše ostvaruje korištenjem funkcija SLOPE i INTERCEPT, pri čemu prva određuje nagib linearne regresije, a druga segment koji se presječe regresijom na y osi.
Prednosti ugrađenog funkcijskog alata za regresionu analizu su:
prilično jednostavan, ujednačen proces generisanja serije podataka karakteristike koja se proučava za sve ugrađene statističke funkcije koje definiraju linije trenda;
standardna metodologija za konstruisanje linija trenda na osnovu generisanih serija podataka;
sposobnost predviđanja ponašanja procesa koji se proučava potrebnim brojem koraka naprijed ili nazad.
Nedostaci uključuju činjenicu da Excel nema ugrađene funkcije za kreiranje drugih (osim linearnih i eksponencijalnih) tipova linija trenda. Ova okolnost često ne dozvoljava odabir dovoljno preciznog modela procesa koji se proučava, kao i dobijanje prognoza koje su bliske stvarnosti. Osim toga, kada se koriste funkcije TREND i GROWTH, jednadžbe linija trenda nisu poznate.
Treba napomenuti da autori nisu imali za cilj da predstave tok regresione analize sa bilo kojim stepenom potpunosti. Njegov glavni zadatak je da na konkretnim primjerima pokaže mogućnosti Excel paketa pri rješavanju aproksimacijskih problema; demonstrirati koje efikasne alate Excel ima za pravljenje regresija i predviđanja; ilustruju kako takve probleme može relativno lako riješiti čak i korisnik koji nema opsežno znanje o regresijskoj analizi.
Primjeri rješavanja konkretnih problema
Pogledajmo rješavanje konkretnih problema pomoću navedenih Excel alata.
Problem 1
Sa tabelom podataka o dobiti autotransportnog preduzeća za 1995-2002. potrebno je da uradite sledeće:
Napravite dijagram.
Dodajte linearne i polinomske (kvadratne i kubične) linije trenda na grafikon.
Koristeći jednačine linije trenda, pribavite tabelarne podatke o dobiti preduzeća za svaku liniju trenda za 1995-2004.
Napravite prognozu dobiti preduzeća za 2003. i 2004. godinu.
Rješenje problema
U opseg ćelija A4:C11 Excel radnog lista unesite radni list prikazan na sl. 4.
Nakon odabira raspona ćelija B4:C11, gradimo dijagram.
Aktiviramo konstruisani dijagram i, prema gore opisanoj metodi, nakon odabira tipa linije trenda u dijaloškom okviru Trend Linija (vidi sliku 1), naizmenično dodajemo linearne, kvadratne i kubične linije trenda dijagramu. U istom dijaloškom okviru otvorite karticu Parametri (pogledajte sliku 2), u polje Naziv aproksimirajuće (izglađene) krive unesite naziv trenda koji se dodaje, a u polje Forecast forward for: periods postavite vrijednost 2, budući da je planirana prognoza dobiti za dvije godine unaprijed. Za prikaz jednačine regresije i vrijednosti pouzdanosti aproksimacije R2 u području dijagrama, omogućite potvrdne okvire za prikaz jednačine na ekranu i postavite vrijednost pouzdanosti aproksimacije (R^2) na dijagram. Za bolju vizuelnu percepciju, menjamo vrstu, boju i debljinu konstruisanih linija trenda, za šta koristimo karticu View dijaloškog okvira Trend Line Format (vidi sliku 3). Rezultirajući dijagram sa dodanim linijama trenda prikazan je na Sl. 5.
Dobiti tabelarne podatke o dobiti preduzeća za svaku liniju trenda za 1995-2004. Koristimo jednadžbe linije trenda predstavljene na Sl. 5. Da biste to učinili, u ćelije raspona D3:F3 unesite tekstualne informacije o tipu odabrane linije trenda: Linearni trend, Kvadratični trend, Kubni trend. Zatim unesite formulu linearne regresije u ćeliju D4 i, koristeći marker za popunjavanje, kopirajte ovu formulu s relativnim referencama na raspon ćelija D5:D13. Treba napomenuti da svaka ćelija sa formulom linearne regresije iz opsega ćelija D4:D13 ima kao argument odgovarajuću ćeliju iz opsega A4:A13. Slično, za kvadratnu regresiju popunite raspon ćelija E4:E13, a za kubičnu regresiju popunite raspon ćelija F4:F13. Tako je napravljena prognoza dobiti preduzeća za 2003. i 2004. godinu. koristeći tri trenda. Dobivena tabela vrijednosti prikazana je na Sl. 6.
Problem 2
Napravite dijagram.
Dodajte logaritamske, stručne i eksponencijalne linije trenda na grafikon.
Izvesti jednadžbe dobijenih linija trenda, kao i vrijednosti pouzdanosti aproksimacije R2 za svaku od njih.
Koristeći jednačine linije trenda, dobiti tabelarne podatke o dobiti preduzeća za svaku liniju trenda za 1995-2002.
Napravite prognozu dobiti kompanije za 2003. i 2004. koristeći ove trendove.
Rješenje problema
Prateći metodologiju datu u rješavanju problema 1, dobijamo dijagram sa dodanim logaritamskim, potencijskim i eksponencijalnim linijama trenda (slika 7). Zatim, koristeći dobijene jednadžbe linije trenda, popunjavamo tabelu vrijednosti za profit preduzeća, uključujući i predviđene vrijednosti za 2003. i 2004. godinu. (Sl. 8).
Na sl. 5 i sl. može se vidjeti da model sa logaritamskim trendom odgovara najnižoj vrijednosti pouzdanosti aproksimacije
R2 = 0,8659
Najveće vrijednosti R2 odgovaraju modelima sa polinomskim trendom: kvadratni (R2 = 0,9263) i kubični (R2 = 0,933).
Problem 3
Uz tabelu podataka o dobiti autotransportnog preduzeća za 1995-2002, datu u zadatku 1, morate izvršiti sljedeće korake.
Dobijte serije podataka za linearne i eksponencijalne linije trenda koristeći funkcije TREND i GROW.
Koristeći funkcije TREND i RAST, napravite prognozu dobiti preduzeća za 2003. i 2004. godinu.
Konstruirajte dijagram za originalne podatke i rezultirajući niz podataka.
Rješenje problema
Koristimo radni list za zadatak 1 (vidi sliku 4). Počnimo s funkcijom TREND:
odaberite raspon ćelija D4:D11, koje treba popuniti vrijednostima funkcije TREND koje odgovaraju poznatim podacima o dobiti poduzeća;
Pozovite komandu Funkcija iz menija Insert. U dijalogu Čarobnjak za funkcije koji se pojavi, izaberite funkciju TREND iz kategorije Statistike, a zatim kliknite na dugme U redu. Ista operacija se može izvršiti klikom na dugme (Insert Function) na standardnoj traci sa alatkama.
U dijaloškom okviru Argumenti funkcije koji se pojavi unesite opseg ćelija C4:C11 u polje Poznate_vrijednosti_y; u polju Poznate_vrijednosti_x - opseg ćelija B4:B11;
Da unesena formula postane formula niza, koristite kombinaciju tipki + +.
Formula koju smo uneli u traku sa formulama će izgledati ovako: =(TREND(C4:C11,B4:B11)).
Kao rezultat toga, raspon ćelija D4:D11 je ispunjen odgovarajućim vrijednostima funkcije TREND (slika 9).
Da se napravi prognoza dobiti preduzeća za 2003. i 2004. godinu. potrebno:
odaberite raspon ćelija D12:D13 u koje će biti unesene vrijednosti predviđene funkcijom TREND.
pozovite funkciju TREND i u dijaloškom okviru Argumenti funkcije koji se pojavi unesite u polje Poznate_vrijednosti_y - opseg ćelija C4:C11; u polju Poznate_vrijednosti_x - opseg ćelija B4:B11; a u polju Nove_vrijednosti_x - opseg ćelija B12:B13.
pretvorite ovu formulu u formulu niza koristeći kombinaciju tipki Ctrl + Shift + Enter.
Unesena formula će izgledati ovako: =(TREND(C4:C11;B4:B11;B12:B13)), a opseg ćelija D12:D13 će biti popunjen predviđenim vrijednostima funkcije TREND (vidi Sl. 9).
Niz podataka se na sličan način popunjava pomoću funkcije GROWTH, koja se koristi u analizi nelinearnih zavisnosti i radi na potpuno isti način kao i njen linearni pandan TREND.
Slika 10 prikazuje tabelu u načinu prikaza formule.
Za početne podatke i dobijene serije podataka, dijagram prikazan na Sl. jedanaest.
Problem 4
Sa tabelom podataka o prijemu zahtjeva za usluge od strane dispečerske službe autotransportnog preduzeća za period od 1. do 11. u tekućem mjesecu, morate izvršiti sljedeće radnje.
Dobijte niz podataka za linearnu regresiju: korištenjem funkcija SLOPE i INTERCEPT; koristeći funkciju LINEST.
Dobijte seriju podataka za eksponencijalnu regresiju koristeći LGRFPRIBL funkciju.
Koristeći navedene funkcije, napravite prognozu o prijemu prijava u dispečersku službu za period od 12. do 14. u tekućem mjesecu.
Napravite dijagram za originalnu i primljenu seriju podataka.
Rješenje problema
Imajte na umu da, za razliku od funkcija TREND i GROWTH, nijedna od gore navedenih funkcija (SLOPE, INTERCEPT, LINEST, LGRFPRIB) nije regresija. Ove funkcije igraju samo pomoćnu ulogu, određujući potrebne parametre regresije.
Za linearne i eksponencijalne regresije izgrađene pomoću funkcija SLOPE, INTERCEPT, LINEST, LGRFPRIB, izgled njihovih jednačina je uvijek poznat, za razliku od linearnih i eksponencijalnih regresija koje odgovaraju funkcijama TREND i GROWTH.
1 . Izgradimo linearnu regresiju sa jednadžbom:
y = mx+b
koristeći funkcije SLOPE i INTERCEPT, pri čemu je nagib regresije m određen funkcijom SLOPE, a slobodni termin b funkcijom INTERCEPT.
Da bismo to učinili, provodimo sljedeće radnje:
unesite originalnu tablicu u raspon ćelija A4:B14;
vrijednost parametra m će biti određena u ćeliji C19. Odaberite funkciju nagiba iz kategorije Statistike; unesite opseg ćelija B4:B14 u polje poznate_vrijednosti_y i raspon ćelija A4:A14 u polje poznate_vrijednosti_x. Formula će biti unesena u ćeliju C19: =SLOPE(B4:B14,A4:A14);
Sličnom tehnikom određuje se vrijednost parametra b u ćeliji D19. Njegov sadržaj će izgledati ovako: =SEGMENT(B4:B14,A4:A14). Dakle, vrijednosti parametara m i b potrebnih za konstruiranje linearne regresije bit će pohranjene u ćelijama C19, D19;
Zatim unesite formulu linearne regresije u ćeliju C4 u obliku: =$C*A4+$D. U ovoj formuli ćelije C19 i D19 su napisane sa apsolutnim referencama (adresa ćelije ne bi trebalo da se menja tokom mogućeg kopiranja). Apsolutni referentni znak $ može se otkucati ili sa tastature ili pomoću tastera F4, nakon postavljanja kursora na adresu ćelije. Koristeći ručicu za popunjavanje, kopirajte ovu formulu u raspon ćelija C4:C17. Dobijamo traženu seriju podataka (slika 12). Zbog činjenice da je broj zahtjeva cijeli broj, trebali biste postaviti format broja sa brojem decimalnih mjesta na 0 na kartici Broj prozora Format ćelije.
2 . Sada napravimo linearnu regresiju datu jednadžbom:
y = mx+b
koristeći funkciju LINEST.
Za ovo:
Unesite funkciju LINEST kao formulu niza u rasponu ćelija C20:D20: =(LINEST(B4:B14,A4:A14)). Kao rezultat, dobijamo vrijednost parametra m u ćeliji C20, a vrijednost parametra b u ćeliji D20;
unesite formulu u ćeliju D4: =$C*A4+$D;
kopirajte ovu formulu koristeći marker za popunjavanje u raspon ćelija D4:D17 i dobijte željenu seriju podataka.
3 . Gradimo eksponencijalnu regresiju sa jednadžbom:
pomoću funkcije LGRFPRIBL se izvodi slično:
U opseg ćelija C21:D21 unosimo funkciju LGRFPRIBL kao formulu niza: =( LGRFPRIBL (B4:B14,A4:A14)). U ovom slučaju, vrijednost parametra m će biti određena u ćeliji C21, a vrijednost parametra b će biti određena u ćeliji D21;
formula se unosi u ćeliju E4: =$D*$C^A4;
korišćenjem markera za popunjavanje, ova formula se kopira u opseg ćelija E4:E17, gde će se nalaziti serija podataka za eksponencijalnu regresiju (vidi sliku 12).
Na sl. Slika 13 prikazuje tabelu u kojoj možete vidjeti funkcije koje koristimo sa potrebnim rasponima ćelija, kao i formule.
Magnituda R 2 pozvao koeficijent odlučnosti.
Zadatak konstruisanja regresijske zavisnosti je da se pronađe vektor koeficijenata m modela (1) na kome koeficijent R poprima maksimalnu vrednost.
Za procjenu značaja R koristi se Fišerov F test, izračunat pomoću formule
Gdje n- veličina uzorka (broj eksperimenata);
k je broj koeficijenata modela.
Ako F premašuje neku kritičnu vrijednost za podatke n I k i prihvaćenu pouzdanu vjerovatnoću, tada se vrijednost R smatra značajnom. Stolovi kritične vrijednosti F su dati u priručniku o matematičkoj statistici.
Dakle, značaj R ne određuje samo njegova vrijednost, već i odnos između broja eksperimenata i broja koeficijenata (parametara) modela. Zaista, korelacijski odnos za n=2 za jednostavan linearni model je jednak 1 (jedna prava linija se uvijek može povući kroz 2 tačke na ravni). Međutim, ako su eksperimentalni podaci slučajne varijable, takvoj vrijednosti R treba vjerovati s velikim oprezom. Obično, da bi dobili značajan R i pouzdanu regresiju, oni nastoje osigurati da broj eksperimenata značajno premašuje broj koeficijenata modela (n>k).
Za izradu modela linearne regresije potrebno vam je:
1) pripremiti listu od n redaka i m stupaca koji sadrže eksperimentalne podatke (kolona koja sadrži izlaznu vrijednost Y mora biti prvi ili zadnji na listi); Na primjer, uzmimo podatke iz prethodnog zadatka, dodajući kolonu pod nazivom "Period No.", numerirajte brojeve perioda od 1 do 12. (ovo će biti vrijednosti X)
2) idite na meni Podaci/Analiza podataka/Regresija
Ako nedostaje stavka "Analiza podataka" u meniju "Alati", onda treba da odete na stavku "Dodaci" u istom meniju i označite polje za potvrdu "Paket analize".
3) u dijaloškom okviru "Regresija" postavite:
· interval unosa Y;
· ulazni interval X;
· izlazni interval - gornja lijeva ćelija intervala u koji će biti smješteni rezultati proračuna (preporučljivo je postaviti ih na novi radni list);
4) kliknite na "OK" i analizirajte rezultate.
Metoda najmanjeg kvadrata koristi se za procjenu parametara regresione jednadžbe.Jedna od metoda za proučavanje stohastičkih odnosa između karakteristika je regresiona analiza.
Regresiona analiza je izvođenje regresione jednačine, uz pomoć koje se pronalazi prosječna vrijednost slučajne varijable (atribut rezultata) ako je poznata vrijednost druge (ili druge) varijabli (faktor-atributa). Uključuje sljedeće korake:
- izbor oblika veze (vrsta analitičke regresione jednačine);
- procjena parametara jednadžbe;
- procjena kvaliteta analitičke regresione jednačine.
U slučaju linearne parne veze, jednačina regresije će imati oblik: y i =a+b·x i +u i . Parametri a i b ove jednačine su procijenjeni iz podataka statističko posmatranje x i y. Rezultat takve procjene je jednadžba: , gdje su procjene parametara a i b , je vrijednost rezultirajućeg atributa (varijable) dobijene iz jednačine regresije (izračunata vrijednost).
Najčešće se koristi za procjenu parametara metoda najmanjih kvadrata (LSM).
Metoda najmanjih kvadrata daje najbolje (dosljedne, efikasne i nepristrasne) procjene parametara regresione jednačine. Ali samo ako su ispunjene određene pretpostavke u vezi sa slučajnim članom (u) i nezavisnom varijablom (x) (vidi OLS pretpostavke).
Problem procjene parametara jednadžbe linearnog para metodom najmanjih kvadrata je kako slijedi: da se dobiju takve procjene parametara , , kod kojih je zbroj kvadrata odstupanja stvarnih vrijednosti rezultujuće karakteristike - y i od izračunatih vrijednosti - minimalan.
Formalno OLS test može se napisati ovako: 
.
Klasifikacija metoda najmanjih kvadrata
- Metoda najmanjeg kvadrata.
- Metoda maksimalne vjerovatnoće (za normalan klasični model linearne regresije, postulira se normalnost reziduala regresije).
- Generalizirana metoda najmanjih kvadrata OLS se koristi u slučaju autokorelacije grešaka iu slučaju heteroskedastičnosti.
- Metoda ponderiranih najmanjih kvadrata ( poseban slučaj OLS sa heteroskedastičnim rezidualima).
Hajde da ilustrujemo poentu klasična metoda najmanji kvadrati grafički. Da bismo to uradili, konstruisaćemo dijagram raspršenja na osnovu podataka posmatranja (x i, y i, i=1; n) u pravougaonom koordinatnom sistemu (takav dijagram raspršenja naziva se korelaciono polje). Pokušajmo odabrati pravu liniju koja je najbliža tačkama korelacionog polja. Prema metodi najmanjih kvadrata, linija se bira tako da zbir kvadrata vertikalnih udaljenosti između tačaka korelacionog polja i ove prave bude minimalan. 
Matematička notacija za ovaj problem: 
.
Poznate su nam vrijednosti y i i x i =1...n; ovo su podaci opservacije. U S funkciji predstavljaju konstante. Varijable u ovoj funkciji su potrebne procjene parametara - , . Da bi se pronašao minimum funkcije dvije varijable, potrebno je izračunati parcijalne izvode ove funkcije za svaki od parametara i izjednačiti ih sa nulom, tj. 
.
Kao rezultat, dobijamo sistem od 2 normalne linearne jednadžbe: 
Odlučivanje ovaj sistem, nalazimo potrebne procjene parametara: 
Ispravnost proračuna parametara regresione jednačine može se provjeriti poređenjem iznosa (može doći do neslaganja zbog zaokruživanja proračuna).
Da biste izračunali procjene parametara, možete napraviti tabelu 1.
Znak koeficijenta regresije b ukazuje na smjer odnosa (ako je b >0, odnos je direktan, ako je b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalno, vrijednost parametra a je prosječna vrijednost y sa x jednakim nuli. Ako faktor-atribut nema i ne može imati nultu vrijednost, onda gornja interpretacija parametra a nema smisla.
Procjena bliskosti odnosa između karakteristika
izvršeno korištenjem koeficijenta linearne parne korelacije - r x,y. Može se izračunati pomoću formule: 
. Osim toga, koeficijent korelacije linearnog para može se odrediti preko koeficijenta regresije b: 
.
Raspon prihvatljivih vrijednosti koeficijenta linearne korelacije para je od –1 do +1. Znak koeficijenta korelacije ukazuje na smjer odnosa. Ako je r x, y >0, onda je veza direktna; ako je r x, y<0, то связь обратная.
Ako je ovaj koeficijent po veličini blizu jedinice, onda se odnos između karakteristika može tumačiti kao prilično blizak linearni. Ako je njegov modul jednak jednom ê r x , y ê =1, tada je odnos između karakteristika funkcionalno linearan. Ako su karakteristike x i y linearno nezavisne, tada je r x,y blizu 0.
Za izračunavanje r x,y možete koristiti i tabelu 1.
Tabela 1
| N zapažanja | x i | y i | x i ∙y i | ||
| 1 | x 1 | y 1 | x 1 y 1 | ||
| 2 | x 2 | y 2 | x 2 y 2 | ||
| ... | |||||
| n | x n | y n | x n y n | ||
| Column Sum | ∑x | ∑y | ∑xy | ||
| Prosječna vrijednost |
 |
 |
,
gdje je d 2 varijansa y objašnjena jednadžbom regresije;
e 2 - rezidualna (neobjašnjena jednadžbom regresije) varijansa y;
s 2 y - ukupna (ukupna) varijansa y.
Koeficijent determinacije karakteriše udio varijacije (disperzije) rezultujućeg atributa y objašnjen regresijom (i, posljedično, faktorom x) u ukupnoj varijaciji (disperziji) y. Koeficijent determinacije R 2 yx ima vrijednosti od 0 do 1. Shodno tome, vrijednost 1-R 2 yx karakterizira udio varijanse y uzrokovane utjecajem drugih faktora koji nisu uzeti u obzir u modelu i greškama u specifikaciji.
Sa uparenom linearnom regresijom, R 2 yx =r 2 yx.
