Metoda najmanjih kvadrata u programu Excel. Regresijska analiza

Upućuje se metoda najmanjih kvadrata (OLS)regresijska analiza. Ima mnogo koristi, jer omogućuje približnu reprezentaciju određene funkcije drugim jednostavnijim. OLS može biti vrlo korisno u obradi opažanja, a aktivno se koristi za procjenu određenih količina iz rezultata mjerenja drugih 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 pokazatelja X i Y. I Y ovisi o X. Budući da je OLS od interesa za nas s točke gledišta regresijske analize (u Excelu, njegove metode se provode uz pomoć ugrađenih funkcija), onda je odmah potrebno razmotriti određeni problem.

Dakle, neka X - trgovačko područje trgovine, mjereno u četvornih metara, i Y - godišnji promet, definiran u milijunima rubalja.

Potrebno je napraviti prognozu, koji promet (Y)će 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, rezultatibit će više ili manje ispravan ako se istražuju podaci o barem 5-6 objekata. Osim toga, ne možete koristiti "anomalous" rezultate. Konkretno, elitni mali butik može imati prometa s vremena na vrijeme veći od prometa velikih prodajnih mjesta klase "masmarket".

Bit metode

Tablični podaci mogu se prikazati na kartezijanskoj ravnini u obliku točaka M₁ (x₁, y₁), ... 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_1, M_{2, ..}M_n.

Naravno, može se koristiti polinom visokogali ta je mogućnost ne samo teško implementirati, već je to jednostavno pogrešna jer neće odražavati glavni trend koji treba 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, tj_ja = y_ja f (x_ja).

Očito, za procjenu točnosti aproksimacijemože se upotrijebiti zbroj odstupanja, to jest, pri odabiru linije za približnu reprezentaciju ovisnosti X na Y, treba dati prednost onoj koja ima najmanje 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 odstupanjaili njihove kvadrate. Potonji je postupak najčešće korišten. To se koristi u mnogim područjima, uključujući i regresijske analize (Excel u provedbi pomoću dvije ugrađene funkcije), a dugo su se pokazale učinkovite.

Metoda najmanjih kvadrata

U programu Excel, kao što znate, ugrađen jeAuto-sum funkcija koja omogućuje izračunavanje vrijednosti svih vrijednosti koje se nalaze u odabranom području. Tako ništa ne sprječava izračunavanje vrijednosti izraza (npr₁² + e₂² + e₃²+ ... e_n²).

U matematičkom zapisu ovo ima oblik:

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

Dakle, problem pronalaženja linije koja najbolje opisuje specifičnu ovisnost količina X i Y smanjuje se za izračunavanje minimalne funkcije dvije varijable:

Da bismo to učinili, moramo izjednačiti nultu parcijalnu izvedbu 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 zaprimjer u pitanju. 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 vrijednostiod strane MNCs. Ima sljedeći oblik: "TRENDS" (poznate vrijednosti Y, poznate vrijednosti X, nove vrijednosti X, konst.). Na našu tablicu primjenjujemo formulu za izračunavanje OLS-a u Excelu.

Za to je stanica u kojoj jepojavljuje se rezultat izračuna pomoću metode najmanjih kvadrata u programu Excel, unosimo znak "=" i odabiremo 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₁, ... 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 čak iteapots. 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 niz poznatih vrijednostivarijab y u jednom retku ili stupcu, tada će svaki red (stupac) s poznatim vrijednostima x primijetiti program kao zasebna varijabla.
• Ako raspon nije naveden u prozoru "TRENDS"poznato x, tada u slučaju korištenja funkcije u programu Excel program će ga tretirati kao niz koji se sastoji od cjelobrojnih brojeva, čiji broj odgovara rasponu s danim 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 nove vrijednosti x nisu navedene, tada je funkcija"TRENDOVI" smatraju da su jednako poznati. Ako nisu navedeni, polje 1 se uzima kao argument; 2; 3; 4; ..., što je proporcionalno rasponu s već navedenim parametrima y.
• Raspon koji sadrži nove vrijednosti x morasastoje se od iste ili više redaka ili stupaca kao raspona s danim vrijednostima y. Drugim riječima, ona mora biti razmjerna nezavisnim varijablama.
• U nizu s poznatim vrijednostima x,sadrži 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 spomoć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 formule u programu Excel for Dummies, omogućujući vam predviđanje vrijednosti buduće vrijednosti određenog pokazatelja prema linearnom trendu.