Metoda najmanjših kvadratov v Excelu. Regresijska analiza

Metoda najmanjših kvadratov (OLS) se nanaša na področje regresijske analize. Ima veliko uporab, saj omogoča približno predstavitev dane funkcije drugim preprostejšim. OLS je lahko zelo koristna pri obdelavi opazovanj in se aktivno uporablja za ovrednotenje določenih količin iz rezultatov meritev drugih, ki vsebujejo naključne napake. V tem članku boste izvedeli, kako izvesti izračun najmanjših kvadratov v Excelu.

Izjava o problemu na konkretnem primeru

Recimo, da obstajata dve parametri X in Y. In Y je odvisna od X. Ker nas MNCs zanima v zvezi z regresijsko analizo (Excel v so njegove metode izvajajo s pomočjo vgrajenih funkcij), morate takoj šel v obravnavo določene naloge.

Torej, pustite X - trgovsko območje prodajalne, merjeno v kvadratnih metrih, in Y - letni promet, ki je opredeljen v milijonih rublej.

Zahtevati je treba napoved, kateri promet (Y) bo v trgovini, če ima eno ali drugo prodajno območje. Očitno je, da funkcija Y = f (X) narašča, saj hipermarket prodaja več blaga kot stojnico.

Nekaj ​​besed o pravilnosti začetnih podatkov, uporabljenih za napovedovanje

Recimo, da imamo tabelo, zgrajeno glede na podatke za n trgovin.

Po matematičnih statistikah bodo rezultati bolj ali manj pravilni, če bodo raziskani podatki o vsaj 5-6 predmetih. Poleg tega ne morete uporabiti "anomaloznih" rezultatov. Zlasti elitni majhen butik ima lahko promet včasih večji od prometa velikih prodajnih mest razreda "masmarket".

Bistvo metode

Podatki tabele so lahko prikazani na kartezijanskem ravnini v obliki točk M₁ (x₁, y₁), hellip- M_n (x_n, y_n). Zdaj se rešitev problema zmanjša na izbiro aproksimacijske funkcije y = f (x), ki ima graf, ki poteka čim bližje točkama M_1, M_{2, ..}M_n.

Seveda lahko uporabite polinoma visoke stopnje, vendar to možnost ni le težko izvajati, ampak preprosto ni pravilna, saj ne bo odražala glavnega trenda, ki ga morate zaznati. Najbolj smiselna rešitev je najti ravno črto y = ax + b, ki najbolj ustreza eksperimentalnim podatkom, natančneje koeficientom - a in b.

Ocena natančnosti

Za vse približke je ocena njegove natančnosti še posebej pomembna. Mi označujemo z e_i razlika (odstopanje) med funkcionalnimi in eksperimentalnimi vrednostmi za točko x_i, to je, e_i = y_i- f (x_i).

Očitno je, da ocenjujemo točnost približka, lahko uporabimo vsoto odstopanj, to je, ko izberemo ravno črto za približno predstavitev odvisnosti X na Y, bi morali dati prednost tisti z najmanjšo vrednostjo vsote e_i v vseh obravnavanih točkah. Vendar ni vse tako preprosto, saj bodo skupaj s pozitivnimi odstopanji praktično negativne.

Težavo lahko rešite z uporabo deviacijskih modulov ali njihovih kvadratov. Slednja metoda je bila najpogosteje uporabljena. Uporablja se na številnih področjih, vključno z regresijsko analizo (v Excelu se izvaja z uporabo dveh vgrajenih funkcij) in je že dolgo dokazano učinkovito.

Metoda najmanjših kvadratov

V Excelu, kot veste, je vgrajena funkcija samodejnega vsote, ki vam omogoča izračun vrednosti vseh vrednosti v izbranem obsegu. Tako nič ne preprečuje, da bi izračunali vrednost izraza (npr₁² + e₂² + e₃²+ ... e_n²).

V matematični notaciji ima to obliko:

Ker je bila sprva sprejeta odločitev, da se približamo ravni ravni, imamo:

Tako je težava poiskati črto, ki najbolje opisuje specifično odvisnost količin X in Y, zmanjšana na izračun najmanjše vrednosti funkcije dveh spremenljivk:

Da bi to naredili, moramo enačiti na nič delnih derivatov glede na nove spremenljivke a in b in rešiti primitivni sistem, sestavljen iz dveh enačb z 2 neznanci:

Po preprostih transformacijah, vključno z delitvijo z 2 in manipulacijo z vsoto, dobimo:

Rešitev, na primer, po metodi Cramer, dobimo stacionarno točko z nekaterimi koeficienti a^* in b^*. To je najmanjše, to je, da napoveduje, kateri promet bo v skladišču za določeno območje, č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 pridobiti zamisel o tem, ali se bo nakup določenega območja odplačal na posojilo.

Kako izvajati metodo najmanjših kvadratov v Excelu

V "Excelu" je funkcija za izračun vrednosti OLS. Ima naslednjo obliko: "TRENDI" (znane vrednosti Y znanih vrednosti X-novih vrednosti X-const.). Formulo za izračun OLS v Excelu uporabimo za našo tabelo.

Če želite to narediti, vnesite znak "=" v celico, v kateri naj bo prikazan rezultat izračuna z uporabo metode najmanjših kvadratov v Excelu in izberite funkcijo "TRENDS". V odprtem oknu izpolnite ustrezna polja, ki označujejo:

• razpon znanih vrednosti za Y (v tem primeru podatki za promet);
• obseg x₁, hellip-x_n, to je velikost maloprodajnega prostora;
• znane in neznane vrednosti x, za katere morate ugotoviti velikost prometa (za informacije o njihovi lokaciji na delovnem listu, glej spodaj).

Poleg tega formula vsebuje logično spremenljivko "Konst." Če vnesete 1 v polje, ki ustreza, bo to pomenilo, da morate opraviti izračune, ob predpostavki, da je b = 0.

Če želite vedeti, da napoved za več kot eno vrednost x, nato pa po vstopu v formulo, pritisnite ne na tipko "Enter", in jih vnesti v kombinaciji tipkovnice za "Shift» + «Control» + «Enter" ( "zagon").

Nekatere funkcije

Regresijsko analizo lahko dostopate tudi s čajniki. Formulo Excel za napovedovanje vrednosti množice neznanih spremenljivk - "TRENDI" - lahko uporabijo tudi tisti, ki še niso slišali za metodo najmanjših kvadratov. Dovolj je le vedeti nekaj značilnosti njenega dela. Zlasti:

• Če razporedite znane vrednosti spremenljivke y v eni vrstici ali stolpcu, potem bo vsaka vrstica (stolpec) z znanimi vrednostmi x program kot ločena spremenljivka zaznala.
• Če obseg z znanimi x ni naveden v oknu "TRENDS", bo program v primeru uporabe funkcije v Excelu obdelal kot matriko, sestavljeno iz celih števil, katerih število ustreza obsegu z danimi vrednostmi spremenljivke y.
• Za pridobitev vrste "predvidenih" vrednosti na izhodu je treba izraz za izračun trenda vnesti kot matrično formulo.
• Če nove vrednosti x niso določene, funkcija "TRENDI" meni, da so enake znanim. Če niso navedeni, se kot array vzame array 1- 2-3- 4-hellip-, ki je sorazmerna z obsegom z že določenimi parametri y.
• Območje z novimi vrednostmi x mora vsebovati enake ali več vrstic ali stolpcev kot obseg z danimi vrednostmi y. Z drugimi besedami, mora biti sorazmerna z neodvisnimi spremenljivkami.
• Matrika z znanimi vrednostmi x lahko vsebuje več spremenljivk. Če pa je le ena, je potrebno, da so obsegi z danimi vrednostmi x in y sorazmerni. V primeru več spremenljivk je potrebno, da se obseg z danimi vrednostmi y nahaja v enem stolpcu ali v eni vrstici.

Funkcija "PREDNOST"

Regresijska analiza v Excelu se izvaja z uporabo več funkcij. Eden od njih se imenuje "PREDNOST". Podobno kot "TRENDI", to pomeni, da daje rezultat izračuna z uporabo metode najmanjših kvadratov. Vendar pa samo za eno X, za katerega je vrednost Y neznana.

Zdaj poznate formule v programu Excel for Dummies, ki vam omogočajo, da napovedujete vrednost prihodnje vrednosti danega indikatorja glede na linearni trend.