Metoda najmanjih kvadrata (MNC) pripada području regresijske analize. Ima mnogo aplikacija, jer vam omogućuje da izvedete približnu reprezentaciju dane funkcije drugim, jednostavnijim funkcijama. MNC se može pokazati iznimno korisnim u tretiranju opažanja i aktivno se koristi za procjenu nekih vrijednosti izmjerenih od drugih koje sadrže slučajne pogreške. Iz ovog članka naučit ćete kako implementirati izračun najmanjih kvadrata u Excelu.

Izjava o problemu u konkretnom primjeru

Pretpostavimo da postoje dva pokazatelja X i Y. Štoviše, Y ovisi o X. Budući da nas MNC zanimaju u smislu regresijske analize u Excelu, njegove se metode provode uz pomoć ugrađenih funkcija), odmah vrijedi. isto vrijedi i za razmatranje određenog zadatka.


Dakle, neka je X maloprodajni prostor trgovine, koji se mjeri u kvadratnim metrima, a Y je godišnji promet, koji se određuje u milijunima rubalja. Potrebno je predvidjeti koji će robni promet (Y) biti u blizini trgovine, ako ima jedno ili drugo trgovačko područje. Očito, funkcija Y = f (X) raste, jer hipermarket prodaje više robe nego štand.

Nekoliko riječi o ispravnosti izvornih podataka korištenih za predviđanje

Pretpostavimo da imamo tablicu izgrađenu prema podacima za n trgovina.

X



x 1



x 2






xn



Y



y 1



y 2









matematičke statistike, rezultati će biti više ili manje točni, ako se prouče podaci o najmanje 5-6 objekata. Osim toga, ne možete koristiti "abnormalne" rezultate. Konkretno, elitni mali butik može imati promet od više puta od prometa velikih prodajnih mjesta klase "masovno tržište".

Bit metode

Podaci tablice mogu se prikazati na kartezijanskoj ravnini u obliku točaka M 1 (x 1, y 1), M n (x n, y n). Sada se rješenje problema svodi na izbor aproksimacijske funkcije y = f (x), koja ima graf koji je što je moguće bliže točkama M 1 M 2 M n. Naravno, možete koristiti polinom visokog stupnja, ali takva opcija nije samo teško realizirana, već i netočna, jer neće odražavati glavni trend koji treba identificirati. Najrazumnije rješenje je tražiti pravac y = ax + b, koji najbolje aproksimira eksperimentalne podatke ili, točnije, koeficijente a i b. Nakon jednostavnih transformacija, uključujući podjelu na 2 i manipulaciju sa sumama, dobivamo: Rješavanjem, primjerice, Cramer-ovom metodom, dobivamo stacionarnu točku s određenim koeficijentima a * i b *. To je minimum, tj. Predvidjeti što će robni promet biti u trgovini na određenom području, te se pristupa ravnoj liniji y = a * x + b *, što je regresijski model za dotični primjer. Naravno, to vam neće dopustiti da pronađete točan rezultat, ali će vam pomoći da dobijete ideju da li ćete kupiti ili neodređeno područje za pohranu kredita.

Kako implementirati metodu najmanjih kvadrata u Excelu

U Excelu postoji funkcija za izračunavanje vrijednosti za MNC. Ona ima sljedeći oblik: "TREND" (budući da je vrijednost Y; vrijednost je X; nova vrijednost je X; const.). Primijenite formulu za izračunavanje MNC-ova u Excel tablici. Da biste to učinili, u ćeliji u kojoj treba prikazati rezultat izračunavanja metodom najmanjih kvadrata, uvodimo znak "=" i odabiremo funkciju "TREND". U otvorenom prozoru popunite odgovarajuća polja, ističući:

raspon poznatih vrijednosti za Y (u ovom slučaju podaci za promet);

raspon x 1, x n, tj. Veličina trgovačkog prostora;

poznate nepoznate vrijednosti x, za koje je potrebno utvrditi veličinu prometa (informacije o njihovom položaju na listu, vidi dolje).

Osim toga, u formuli postoji logička varijabla "Constant". Ako unesete polje 1 u odgovarajuće polje, to će značiti da se izračunava, s obzirom da b = 0.
Ako želite znati prognozu za više od jedne vrijednosti x, nakon unosa formule, kliknite na "Enter". ali morate upisati kombinaciju tipkovnice Shift + Ctrl + Enter.

Neke značajke

Regresijska analiza može biti dostupna čak i čajnicima. Excelova formula za predviđanje vrijednosti niza nepoznatih varijabli - "TREND" - mogu koristiti čak i oni koji nikada nisu čuli za metodu najmanjih kvadrata. Dovoljno je znati neke značajke njezina rada. Konkretno:

Ako podesite raspon poznatih vrijednosti varijable y u jednom retku ili stupcu, tadaSvaki redak (stupac) s poznatim vrijednostima x program će percipirati kao jednu varijablu.

Ako prozor TENDENCE ne određuje raspon s poznatim x, ako koristite funkciju u Excelu, program će je smatrati nizom koji se sastoji od cijelih brojeva čiji broj odgovara rasponu s navedenim vrijednostima varijable y.

Za dobivanje niza predviđenih vrijednosti na izlazu, izraz za izračunavanje trenda treba unijeti kao formulu niza.

Ako nisu specificirane nove vrijednosti x, funkcija "TREND" smatra ih jednakima poznatima. Ako nisu dani, onda se niz uzima kao argument 1; 2; 3; 4, koji je mjerljivi raspon s već postavljenim parametrima y.

Raspon koji sadrži nove vrijednosti x trebao bi se sastojati od istih ili više redaka ili stupaca kao raspon s navedenim y vrijednostima. Drugim riječima, to bi trebale biti odgovarajuće nezavisne varijable.

Niz poznatih vrijednosti x može sadržavati nekoliko varijabli. Međutim, ako se radi samo o jednom, rasponi s danim x i y vrijednostima trebali bi biti proporcionalni. U slučaju više varijabli, potrebno je da raspon s navedenim vrijednostima y bude u jednom retku ili u jednom retku.