Metoda najmanjših kvadratov (MNC) spada na področje regresijske analize. Ima veliko aplikacij, saj vam omogoča, da izvedete približno predstavitev dane funkcije z drugimi, enostavnejšimi. MNC se lahko izkaže za izjemno koristno pri obravnavanju opazovanj in se aktivno uporablja za vrednotenje nekaterih vrednosti, ki jih merijo drugi, ki vsebujejo naključne napake. Iz tega članka se boste naučili, kako izvesti izračun najmanjših kvadratov v Excelu.
Izjava problema v konkretnem primeru
Recimo, da obstajata dva kazalnika X in Y. Poleg tega je Y odvisen od X. Ker so nas MNC zanimive v smislu regresijske analize v Excelu, se njene metode izvajajo s pomočjo vgrajenih funkcij), je vredno takoj obravnava določene naloge.
Tako je X maloprodajni prostor trgovine z živili, ki se meri v kvadratnih metrih, in Y je letni promet, ki se določi v milijonih rubljev. Potrebno je napovedati, kakšen bo promet blaga (Y) v bližini trgovine, če ima eno ali drugo trgovsko območje. Očitno je, da funkcija Y = f (X) narašča, saj hipermarket prodaja več blaga kot stojnica.
Nekaj besed o pravilnosti izvornih podatkov, uporabljenih za napoved
Recimo, da imamo tabelo, zgrajeno po podatkih za n trgovin.
X
x 1
x 2
xn
Y
y 1
y 2
yn
matematične statistike, bodo rezultati bolj ali manj pravilni, če se preučijo podatki o vsaj 5-6 predmetih. Poleg tega ne morete uporabiti "nenormalnih" rezultatov. Še posebej, lahko elitni majhen butik ima promet krat več kot promet velikih maloprodajnih mest razreda "množični trg".
Bistvo metode
Podatki tabele so lahko prikazani na kartezični ravnini v obliki točk M 1 (x 1, y 1), M n (x n, y n). Rešitev problema se zdaj skrči na izbiro aproksimacijske funkcije y = f (x), ki ima graf, ki poteka čim bližje točkam M1M2Mn. Seveda lahko uporabite polinom visoke stopnje, vendar takšna možnost ni samo težko realizirana, ampak je tudi nepravilna, saj ne bo odražala glavnega trenda, ki ga je treba identificirati. Najprimernejša rešitev je iskanje ravne črte y = ax + b, ki najbolje približuje eksperimentalne podatke, natančneje koeficienta a in b.
Ocena natančnosti
Pri vsakem približku je ocena njegove točnosti posebnega pomena. Razliko (odstopanje) med funkcionalno in eksperimentalno vrednostjo za točko x i, to je e i = y i - f (x i), označimo z e i. Očitno je, da lahko za oceno natančnosti približevanja uporabimo vsoto odstopanj, to pomeni, da pri izbiri ravne črte za približen prikaz odvisnosti X na Y moramo dati prednost tisti z najmanjšo vrednostjo vsote e i v vseh obravnavanih točkah. Vendar pa ni vse tako preprosto, saj bodo poleg pozitivnih odstopanj praktično negativni. To vprašanje lahko rešite z uporabomodulov ali njihovih kvadratov. Zadnja metoda je bila najbolj razširjena. Uporablja se na številnih področjih, vključno z regresijsko analizo v Excelu, njena izvedba se izvaja z dvema vgrajenima funkcijama) in je že dolgo dokazala svojo učinkovitost.
Metoda najmanjših kvadratov
Excel, kot je znano, ima vgrajeno funkcijo samodejnega seštevanja, ki vam omogoča izračun vrednosti vseh vrednosti v izbranem območju. Zato nam nič ne preprečuje, da bi izračunali vrednost izraza (e 1 2 + e 2 2 + e 3 2 + e n 2). V matematičnem zapisu ima obliko:
Ker je bilo prvotno odločeno, da se približa s pomočjo ravne črte, imamo:
Zato je problem iskanja ravne črte, ki najbolje opisuje specifično odvisnost količin X in Y, zmanjšan na izračun minimalne funkcije dveh spremenljivk:
V ta namen moramo delne delne derivate novih spremenljivk a in b enačiti z nič in rešiti primitivni sistem, ki ga sestavljata dve enačbi dveh neznanih vrst:
Po preprostih transformacijah, vključno z delitvijo na 2 in manipulacijo s vsotami, dobimo:
Z reševanjem, na primer s Cramerjevo metodo, dobimo stacionarno točko z določenimi koeficienti a * in b *. To je minimum, to je predvideti, kakšen bo promet blaga v trgovini na določenem območju, pri čemer se približuje ravna črta y = a * x + b *, ki je regresijski model za zadevni primer. Seveda vam to ne bo omogočilo, da bi našli točen rezultat, vendar vam bo pomagal, da dobite idejo, ali boste kupili ali neobmočje za shranjevanje kreditnih točk.
Kako izvajati metodo najmanjših kvadratov v Excelu
V Excelu obstaja funkcija za izračun vrednosti za MNC. Ima naslednjo obliko: "TREND" (ker je vrednost Y; vrednost je X; nova vrednost je X; konst.). Uporabite formulo za izračun MNC v Excelovi tabeli. Če želite to narediti, v celici, v kateri naj se prikaže rezultat izračuna z metodo najmanjših kvadratov Excel, vpišemo znak "=" in izberemo funkcijo "TREND". V odprtem oknu izpolnite ustrezna polja in označite:
obseg znanih vrednosti za Y (v tem primeru podatke o prometu);
razpon x 1, x n, to je velikost trgovalnega prostora;
znane, neznane vrednosti x, za katere je treba ugotoviti velikost prometa (informacije o njihovi lokaciji na listu, glej spodaj).
Poleg tega je v formuli logična spremenljivka "Constant". Če v ustrezno polje vnesete polje 1, bo to pomenilo, da je treba izračunati, glede na to, da b = 0. Če želite vedeti napoved za več kot eno vrednost x, potem po vnosu formule ne kliknete na "Enter" vendar morate vnesti na tipkovnici kombinacijo Shift + Ctrl + Enter.
Nekatere značilnosti
Regresijska analiza je lahko celo na voljo čajnikom. Excelova formula za napovedovanje vrednosti niza neznanih spremenljivk - "TREND" - lahko uporabljajo tudi tisti, ki še nikoli niso slišali za metodo najmanjših kvadratov. Dovolj je, da poznate nekatere značilnosti njenega dela. Še posebej:
Če nastavite obseg znanih vrednosti spremenljivke y v eni vrstici ali stolpcu, potemVsaka vrstica (stolpec) z znanimi vrednostmi x bo program zaznal kot eno spremenljivko.
Če okno TENDENCE ne določa območja z znanim x, potem, če uporabljate funkcijo v Excelu, jo bo program obravnaval kot polje, sestavljeno iz celih števil, katerih število ustreza območju z navedenimi vrednostmi spremenljivke y.
Za pridobitev niza predvidenih vrednosti na izhodu je treba izraz za izračun trenda vnesti kot matrično formulo.
Če nove vrednosti x niso določene, funkcija "TREND" meni, da so enake znanim. Če niso podane, se matrika vzame kot argument 1; 2; 3; 4, ki je merljivo območje z že nastavljenimi parametri y.
Območje, ki vsebuje nove vrednosti x, mora biti sestavljeno iz enakih ali več vrstic ali stolpcev kot obseg z navedenimi vrednostmi y. Z drugimi besedami, to morajo biti ustrezne neodvisne spremenljivke.
Niz znanih vrednosti x lahko vsebuje več spremenljivk. Če pa je približno ena, potem morajo biti razponi z danimi vrednostmi x in y sorazmerni. V primeru več spremenljivk je potrebno, da je obseg z navedenimi vrednostmi y v eni vrstici ali v eni vrstici.
PREDVIDENA funkcija
Regresijska analiza v Excelu se izvaja z več funkcijami. Ena od njih se imenuje "NAPOVED". Podobno je "TRENDOM", kar pomeni, da proizvaja rezultat izračunov z metodo najmanjših kvadratov. Vendar pa samo za enega X, za katerega je neznana vrednost Y. Zdaj poznate formule v Excelu za lutke, ki to dovoljujejonapovedati vrednost prihodnje vrednosti enega ali drugega kazalnika v skladu z linearnim trendom.
