Metoda najmanjih kvadrata u programu Excel. Regresijska analiza

Metoda najmanjih kvadrata (OLS) odnosi se na područje regresijske analize. Ima mnogo koristi, jer omogućuje približnu reprezentaciju određene funkcije drugim jednostavnijim. MNCs može biti vrlo koristan u liječenju promatranja, a aktivno se koristi za procjenu vrijednosti nekih drugih mjernih rezultata koji sadrže slučajne pogreške. Iz ovog članka saznat ćete kako implementirati izračun najmanjih kvadrata u programu Excel.

Izjava problema na konkretnom primjeru

Pretpostavimo da postoje dva parametra X i Y. i Y ovisi o X. Budući da nam MNCs interesa u smislu regresijska analiza (Excel u su njegove metode provodi uz pomoć ugrađenih funkcija), te bi trebao odmah ići na razmatranje određenog zadatka.

Dakle, neka X - tržište trgovine s namirnicama prostor, u kvadratnim metrima, a Y - godišnji promet, utvrđuje se u milijunima rubalja.

Potrebno je napraviti predviđanje, koji će promet (Y) biti u trgovini, ako ima jedno ili drugo prodajno područje. Očito, funkcija Y = f (X) raste, budući da hipermarket prodaje više robe od stana.

Nekoliko riječi o ispravnosti početnih podataka koji se koriste za predviđanje

Pretpostavimo da imamo stol prema podacima za n trgovine.

Prema matematičkim statistikama, rezultati će biti više ili manje točni ako se istražuju podaci o barem 5-6 predmeta. Osim toga, ne možete koristiti "anomalous" rezultate. Konkretno, luksuzni mali butik može imati promet od puta veći od prometa velikih komercijalnih razreda „masmarket” bodova.

Bit metode

Tablični podaci mogu se prikazati na kartezijanskoj ravnini u obliku točaka M₁ (x₁, y₁) hellip- M_n (x_n, y_n). Sada se rješenje problema svodi na odabir aproksimirajuće funkcije y = f (x) s grafikonom koja prolazi što bliže točkama M₁ M_{2, ..}M_n.

Naravno, možete koristiti polinom visokog stupnja, ali ta je mogućnost ne samo da je teško implementirati, već jednostavno nije točna, jer neće odražavati glavni trend koji morate otkriti. Najudaljenije je rješenje pronaći pravu liniju y = ax + b, koja najbolje približava eksperimentalne podatke, točnije koeficijenti - a i b.

Procjena točnosti

Za svaku približnu procjenu njegove točnosti postaje posebno važna. Označavamo pomoću e_ja razlika (odstupanje) između funkcionalnih i eksperimentalnih vrijednosti za točku x_ja, to jest, e_ja = y_ja- f (x_ja).

Očito je da za procjenu točnosti aproksimacije može se koristiti zbroj odstupanja, t. E. U odabira linije za približno zastupljenost ovisnosti X na Y, potrebno je dati prednost onome koji ima najmanju vrijednost zbroja e_ja u svim razmatranim točkama. Međutim, nije sve tako jednostavno, jer uz pozitivna odstupanja praktički će biti negativni.

Problem možete riješiti pomoću modula odstupanja ili njihovih kvadrata. Potonji je postupak najčešće korišten. Koristi se u mnogim područjima, uključujući regresijsku analizu (u programu Excel, implementirano je pomoću dvije ugrađene funkcije), a odavno je dokazano učinkovito.

Metoda najmanjih kvadrata

U programu Excel, kao što znate, postoji ugrađena funkcija automatskog popunjavanja koja vam omogućuje da izračunate vrijednosti svih vrijednosti koje se nalaze u odabranom rasponu. Tako ništa ne sprječava izračunavanje vrijednosti izraza (npr₁² + e₂² + e₃²+ ... e_n²).

U matematičkom zapisu, to ima oblik:

Budući da je u početku donesena odluka da se približi ravnoj liniji, imamo:

Dakle, problem pronalaženja ravnu liniju koja najbolje opisuje određenu odnos varijabli X i Y, svode na obračun minimalno funkcija dviju varijabli:

Da bismo to učinili, moramo jednoznačiti nultu parcijalne derivate s obzirom na nove varijable a i b i riješiti primitivni sustav koji se sastoji od dvije jednadžbe s 2 nepoznanice:

Nakon jednostavnih transformacija, uključujući podjelu po 2 i manipuliranje s iznosima, dobivamo:

Rješavanjem, na primjer, metodom Cramer dobivamo stacionarno mjesto s nekim koeficijentima a^* i b^*. To je minimalni, odnosno predvidjeti koji će promet biti u trgovini za određeno područje, ravnu liniju y = a^*x + b^*, što je regresijski model za dotični primjer. Naravno, neće vam omogućiti da pronađete točan rezultat, ali će vam pomoći da dobijete ideju o tome hoće li kupnja određenog područja isplatiti zajam.

Kako implementirati metodu najmanjih kvadrata u programu Excel

U "Excel" postoji funkcija za izračunavanje vrijednosti OLS-a. Ima sljedeći oblik: "TRENDS" (poznate vrijednosti Y-poznatih vrijednosti X-novih vrijednosti X-const.). Na našu tablicu primjenjujemo formulu za izračunavanje OLS-a u Excelu.

Da biste to učinili, unesite znak "=" u ćeliju u kojoj će se prikazati rezultat izračuna pomoću metode najmanje kvadrata u programu Excel i odabrati funkciju "TRENDS". U otvorenom prozoru popunite odgovarajuća polja, naglašavajući:

• raspon poznatih vrijednosti za Y (u ovom slučaju, podaci za promet);
• raspon x₁, hellip-x_n, tj. veličinu maloprodajnog prostora;
• i poznate i nepoznate vrijednosti x, za koje trebate saznati veličinu prometa (za informacije o njihovom položaju na radnom listu, vidi dolje).

Osim toga, formula sadrži logičku varijablu "Konst". Ako unesete 1 u odgovarajući polje, to znači da trebate izvršiti izračune, pod pretpostavkom da je b = 0.

Ako želite znati prognozu za više od jedne vrijednosti x, a zatim nakon ulaska u formulu, pritisnite a ne na „Enter”, a vi trebate upisati na kombinaciju tipkovnice za «Shift» + «Kontrola» + «Enter» ( «puštanje»).

Neke značajke

Regresijska analiza može se pristupiti i kotlićima. Excel formula za predviđanje vrijednosti niz nepoznatih varijabli - "TRENDS" - može se koristiti čak i onima koji nikada nisu čuli za metodu najmanjih kvadrata. Dovoljno je samo znati neke od značajki njezina rada. Posebno:

• Ako organizirate raspon poznatih vrijednosti varijable y u jednom retku ili stupcu, program će kao zasebnu varijablu primijetiti svaki red (stupac) s poznatim vrijednostima x.
• Ako se u „trend” nije određena s nizom poznatih x, zatim u slučaju funkcije u Excel programu će ga tretiraju kao niz cijelih brojeva, a broj odgovara rasponu sa zadanim vrijednostima varijable Y.
• Da bi se dobila niz "predviđenih" vrijednosti na izlazu, izraz za izračunavanje trenda mora biti unesen kao formula polja.
• Ako nova vrijednost x nije navedena, funkcija "TRENDS" smatra da su one jednako poznate. Ako nisu navedeni, polje 1 - 2 - 3 - 4 - hellip - uzima se kao argument, koji je proporcionalan rasponu s već navedenim parametrima y.
• Raspon koji sadrži nove vrijednosti x mora se sastojati od iste ili više redaka ili stupaca kao raspona s danim vrijednostima y. Drugim riječima, ona mora biti razmjerna nezavisnim varijablama.
• Niz s poznatim vrijednostima x može sadržavati nekoliko varijabli. Međutim, ako je to samo jedan, potrebno je da su rasponi s danim vrijednostima x i y proporcionalni. U slučaju nekoliko varijabli potrebno je da raspon s danim vrijednostima y bude sadržan u jednom stupcu ili u jednoj liniji.

Funkcija "PREDICTION"

Regresijska analiza u programu Excel implementirana je pomoću nekoliko funkcija. Jedan od njih zove se "PREDICTION". Slično je "TRENDS", to jest, daje rezultat izračuna pomoću metode najmanjih kvadrata. Međutim, samo za jedan X, za koje vrijednost Y nije poznata.

Sada znate formulu u Excelu za početnike, čime predvidjeti veličinu buduće vrijednosti pokazatelja prema linearnom trendu.