Metoda najmanjših kvadratov v Excelu energija naklona. Metoda najmanjših kvadratov v Excelu

Metoda najmanjših kvadratov (LSM) temelji na minimiziranju vsote kvadratov odstopanj izbrane funkcije od proučevanih podatkov. V tem članku bomo razpoložljive podatke aproksimirali z uporabo linearne funkcijel = a x + b .

Metoda najmanjših kvadratov(angleščina) Navadna Najmanj Kvadrati , O.L.S.) je ena izmed osnovnih metod regresijske analize v smislu ocenjevanja neznanih parametrov regresijski modeli po vzorčnih podatkih.

Oglejmo si aproksimacijo s funkcijami, ki so odvisne samo od ene spremenljivke:

• Linearno: y=ax+b (ta članek)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Opomba: V članku so obravnavani primeri aproksimacije s polinomom od 3. do 6. stopnje. Tukaj je obravnavana aproksimacija s trigonometričnim polinomom.

Linearna odvisnost

Zanima nas povezava med 2 spremenljivkama X in l. Obstaja domneva, da l odvisno od X po linearnem zakonu l = sekira + b. Za določitev parametrov tega razmerja je raziskovalec izvedel opazovanja: za vsako vrednost x i je bila opravljena meritev y i (glejte primer datoteke). V skladu s tem naj bo 20 parov vrednosti (x i; y i).

Opomba:Če je korak spremembe X je konstantna, nato graditi razpršene ploskve lahko uporabite, če ne, potem morate uporabiti vrsto grafikona Spot .

Iz diagrama je očitno, da je razmerje med spremenljivkama blizu linearnemu. Da bi razumeli, katera od številnih ravnih črt najbolj "pravilno" opisuje razmerje med spremenljivkami, je treba določiti merilo, po katerem se črte primerjajo.

Kot tak kriterij uporabljamo izraz:

kje ŷ i = a * x i + b ; n – število parov vrednosti (v našem primeru n=20)

Zgornji izraz je vsota kvadratov razdalj med opazovanimi vrednostmi y i in ŷ i in je pogosto označen kot SSE ( vsota od Na kvadrat Napake (Ostanki), vsota kvadratov napak (ostanki)) .

Metoda najmanjših kvadratov je izbrati takšno vrstico ŷ = sekira + b, za katerega ima zgornji izraz najmanjšo vrednost.

Opomba: Vsaka črta v dvodimenzionalnem prostoru je enolično določena z vrednostmi dveh parametrov: a (naklon) in b (premik).

Menijo, da manjša kot je vsota kvadratov razdalj, bolje se ustrezna črta približa razpoložljivim podatkom in jo je mogoče nadalje uporabiti za napovedovanje vrednosti y iz spremenljivke x. Jasno je, da tudi če v resnici ni povezave med spremenljivkami ali je povezava nelinearna, bo OLS še vedno izbral »najboljšo« vrstico. Tako metoda najmanjših kvadratov ne pove ničesar o prisotnosti resničnega razmerja med spremenljivkami; metoda preprosto omogoča izbiro takšnih funkcijskih parametrov a in b , za katerega je zgornji izraz minimalen.

Z izvajanjem ne zelo zapletenih matematičnih operacij (za več podrobnosti glejte) lahko izračunate parametre a in b :

Kot je razvidno iz formule, parameter a predstavlja razmerje kovariance in , zato v MS EXCEL za izračun parametra A Uporabite lahko naslednje formule (glejte Datoteka primera linearnega lista):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) oz

= COVARIANCE.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Tudi za izračun parametra A lahko uporabite formulo = NAGIB(C26:C45;B26:B45). Za parameter b uporabite formulo = LEG(C26:C45;B26:B45) .

Končno vam funkcija LINEST() omogoča izračun obeh parametrov hkrati. Za vnos formule LINEST(C26:C45;B26:B45) Izbrati morate 2 celici v vrsti in klikniti CTRL + SHIFT + ENTER(glej članek o). Vrednost bo vrnjena v levi celici A , na desni – b .

Opomba: Da se izognete zapletom z vnosom matrične formule boste morali dodatno uporabiti funkcijo INDEX(). Formula = INDEX(LINEST(C26:C45;B26:B45);1) ali samo = LINEST(C26:C45;B26:B45) vrne parameter, odgovoren za naklon črte, tj. A . Formula = INDEX(LINEST(C26:C45;B26:B45);2) vrne parameter, odgovoren za presečišče črte z osjo Y, tj. b .

Po izračunu parametrov, raztreseni diagram lahko narišete ustrezno črto.

Drug način za risanje ravne črte z uporabo metode najmanjših kvadratov je orodje za graf Linija trenda. Če želite to narediti, izberite diagram, izberite v meniju Zavihek Postavitev, V skupinska analiza kliknite Linija trenda, potem Linearni približek .

Če v pogovornem oknu potrdite polje »prikaži enačbo v diagramu«, se lahko prepričate, da se zgornji parametri ujemajo z vrednostmi v diagramu.

Opomba: Da se parametri ujemajo, mora biti vrsta diagrama . Bistvo je, da pri sestavljanju diagrama Urnik Vrednosti osi X uporabnik ne more določiti (uporabnik lahko določi samo oznake, ki ne vplivajo na lokacijo točk). Namesto vrednosti X se uporablja zaporedje 1; 2; 3; ... (za številčenje kategorij). Zato, če gradite linija trenda na tipskem diagramu Urnik, potem bodo namesto dejanskih vrednosti X uporabljene vrednosti tega zaporedja, kar bo vodilo do napačnega rezultata (razen če seveda dejanske vrednosti X ne sovpadajo z zaporedjem 1; 2; 3; ...).

No, v službi smo prijavili inšpekcijo, članek je bil napisan doma za konferenco - zdaj lahko pišemo na blog. Medtem ko sem obdeloval svoje podatke, sem ugotovil, da si ne morem kaj, da ne bi pisal o zelo kul in potrebnem dodatku v Excelu, imenovanem . Tako bo članek posvečen temu posebnemu dodatku in o njem vam bom povedal na primeru uporabe metoda najmanjših kvadratov(LSM) za iskanje neznanih koeficientov enačbe pri opisovanju eksperimentalnih podatkov.

Kako omogočiti dodatek »iskanje rešitve«.

Najprej ugotovimo, kako omogočiti ta dodatek.

1. Pojdite v meni »Datoteka« in izberite »Možnosti Excela«.

2. V oknu, ki se prikaže, izberite »Išči rešitev« in kliknite »Pojdi«.

3. V naslednjem oknu potrdite polje zraven »iskanje rešitve« in kliknite »V redu«.

4. Dodatek je aktiviran - zdaj ga najdete v meniju »Podatki«.

Metoda najmanjših kvadratov

Zdaj pa na kratko o metoda najmanjših kvadratov (LSM) in kje se lahko uporablja.

Recimo, da imamo nabor podatkov, potem ko smo izvedli nekakšen eksperiment, kjer smo proučevali vpliv vrednosti X na vrednost Y.

Ta vpliv želimo opisati matematično, tako da lahko potem uporabimo to formulo in vemo, da če spremenimo vrednost X za toliko, bomo dobili vrednost Y takšno in takšno ...

Vzel bom super preprost primer (glej sliko).

Ni pametno, da se točke nahajajo ena za drugo kot v ravni črti, zato varno domnevamo, da je naša odvisnost opisana linearna funkcija y=kx+b. Hkrati smo popolnoma prepričani, da ko je X enak nič, je tudi vrednost Y enaka nič. To pomeni, da bo funkcija, ki opisuje odvisnost, še enostavnejša: y=kx (spomnite se šolskega kurikuluma).

Na splošno moramo najti koeficient k. To je tisto, s čimer bomo naredili MNC z uporabo dodatka »iskanje rešitev«.

Metoda je, da (tukaj - pozor: o tem morate razmišljati) je vsota kvadratov razlik med eksperimentalno pridobljenimi in ustreznimi izračunanimi vrednostmi minimalna. To pomeni, da ko je X1=1 dejanska izmerjena vrednost Y1=4,6 in je izračunana vrednost y1=f (x1) 4, bo kvadrat razlike (y1-Y1)^2=(4-4,6)^2= 0,36 . Enako je z naslednjim: ko je X2=2, dejanska izmerjena vrednost Y2=8,1 in je izračunani y2 8, bo kvadrat razlike (y2-Y2)^2=(8-8,1)^2 =0,01. In vsota vseh teh kvadratov mora biti čim manjša.

Torej, začnimo z usposabljanjem o uporabi LSM in Excelovi dodatki"iskanje rešitve" .

Uporaba dodatka za iskanje rešitve

1. Če niste omogočili dodatka »iskanje rešitve«, se vrnite na točko Kako omogočiti dodatek »iskanje rešitve« in ga vklopiti 🙂

2. V celico A1 vnesite vrednost »1«. Ta enota bo prvi približek dejanske vrednosti koeficienta (k) našega funkcionalnega razmerja y=kx.

3. V stolpcu B imamo vrednosti parametra X, v stolpcu C imamo vrednosti parametra Y. V celice stolpca D vnesemo formulo: »koeficient k, pomnožen z vrednostjo X. ” Na primer, v celico D1 vnesemo »=A1*B1«, v celico D2 vnesemo »=A1*B2« itd.

4. Menimo, da je koeficient k enak ena in je funkcija f (x)=y=1*x prvi približek naši rešitvi. Izračunamo lahko vsoto kvadratov razlik med izmerjenimi vrednostmi Y in tistimi, izračunanimi po formuli y=1*x. Vse to lahko storimo ročno tako, da vnesemo ustrezne sklice na celice v formulo: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... itd. Na koncu smo se zmotimo in ugotovimo, da smo izgubili veliko časa. V Excelu obstaja posebna formula za izračun vsote kvadratov razlik, ki bo vse naredila namesto nas in nastavila začetne podatke : območje izmerjenih vrednosti Y (stolpec C) in območje izračunanih vrednosti Y (stolpec D).

4. Vsota razlik kvadratov je bila izračunana - zdaj pojdite na zavihek "Podatki" in izberite "Išči rešitev".

5. V meniju, ki se prikaže, izberite celico A1 (tisto s koeficientom k) kot celico, ki jo želite spremeniti.

6. Izberite celico A2 kot cilj in nastavite pogoj »nastavi enako najmanjši vrednosti«. Zapomnimo si, da je to celica, kjer izračunamo vsoto kvadratov razlik med izračunanimi in izmerjenimi vrednostmi, ta vsota pa naj bo minimalna. Kliknite »izvedi«.

7. Koeficient k je bil izbran. Zdaj lahko preverite, ali so izračunane vrednosti zdaj zelo blizu izmerjenim.

P.S.

Na splošno seveda za približevanje eksperimentalnih podatkov v Excelu obstajajo posebna orodja, ki vam omogočajo opisovanje podatkov z linearnimi, eksponentnimi, potenčnimi in polinomskimi funkcijami, tako da lahko pogosto storite brez njih. dodatki »iskanje rešitev«.. O vseh teh metodah približevanja sem govoril v svojem, tako da če vas zanima, si oglejte. Ko pa gre za kakšno eksotično funkcijo z enim neznanim koeficientom ali težave z optimizacijo, potem tukaj nadgradnja ne bi mogel priti ob boljšem času.

Dodatek za iskanje rešitev lahko uporabimo za druge naloge, glavna stvar je razumeti bistvo: obstaja celica, v kateri izberemo vrednost, in obstaja ciljna celica, v kateri je določen pogoj za izbiro neznanega parametra.
To je to! V naslednjem članku vam bom povedal pravljico o počitnicah, da ne bi zamudili objave članka,

Ima veliko aplikacij, saj omogoča približno predstavitev dane funkcije z drugimi preprostejšimi. LSM je lahko izjemno uporaben pri obdelavi opazovanj in se aktivno uporablja za ocenjevanje nekaterih količin na podlagi rezultatov meritev drugih, ki vsebujejo naključne napake. V tem članku se boste naučili, kako izvajati izračune najmanjših kvadratov v Excelu.

Izjava problema na konkretnem primeru

Recimo, da obstajata dva indikatorja X in Y. Poleg tega je Y odvisen od X. Ker nas OLS zanima z vidika regresijske analize (v Excelu so njegove metode implementirane z vgrajenimi funkcijami), bi morali takoj preiti na obravnavo specifičen problem.

Naj bo torej X maloprodajni prostor trgovine z živili, merjen v kvadratnih metrih, Y pa letni promet, merjen v milijonih rubljev.

Narediti je treba napoved, kakšen promet (Y) bo imela trgovina, če bo imela ta ali oni maloprodajni prostor. Očitno je, da funkcija Y = f (X) narašča, saj hipermarket proda več blaga kot stojnica.

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

Recimo, da imamo tabelo, sestavljeno s podatki za n trgovin.

Glede na matematična statistika, bodo rezultati bolj ali manj pravilni, če se pregledajo podatki o vsaj 5-6 objektih. Poleg tega ni mogoče uporabiti "nenormalnih" rezultatov. Zlasti elitni mali butik ima lahko promet večkrat večji od prometa velikih maloprodajnih mest razreda "masmarket".

Bistvo metode

Podatke tabele lahko upodobimo na kartezični ravnini v obliki točk M 1 (x 1, y 1), ... M n (x n, y n). Zdaj se bo rešitev problema zmanjšala na izbiro aproksimativne funkcije y = f (x), ki ima graf, ki poteka čim bližje točkam M 1, M 2, .. M n.

Seveda lahko uporabite polinom visoke stopnje, vendar te možnosti ni le težko izvesti, ampak tudi preprosto napačna, saj ne bo odražala glavnega trenda, ki ga je treba zaznati. Najbolj smiselna rešitev je iskanje premice y = ax + b, ki najbolje približa eksperimentalne podatke oziroma natančneje koeficienta a in b.

Ocena točnosti

Pri vsakem približku je še posebej pomembna ocena njegove natančnosti. Označimo z e i razliko (odklon) med funkcionalno in eksperimentalno vrednostjo za točko x i, to je e i = y i - f (x i).

Očitno je, da lahko za oceno točnosti približka uporabite vsoto odstopanj, tj. pri izbiri ravne črte za približno predstavitev odvisnosti X od Y morate dati prednost tisti z najmanjšo vrednostjo vsota e i na vseh obravnavanih točkah. Vendar ni vse tako preprosto, saj bodo poleg pozitivnih odstopanj tudi negativna.

Zadevo je mogoče rešiti z moduli odstopanj ali njihovimi kvadrati. Zadnja metoda je najpogosteje uporabljena. Uporablja se na številnih področjih, vključno z regresijsko analizo (implementirana v Excelu z uporabo dveh vgrajenih funkcij), in je že dolgo dokazala svojo učinkovitost.

Metoda najmanjših kvadratov

Excel, kot veste, ima vgrajeno funkcijo AutoSum, ki vam omogoča izračun vrednosti vseh vrednosti, ki se nahajajo v izbranem obsegu. Tako nas nič ne ovira pri izračunavanju vrednosti izraza (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

V matematičnem zapisu je to videti takole:

Ker je bila sprva sprejeta odločitev za približek z uporabo ravne črte, imamo:

Tako se naloga iskanja ravne črte, ki najbolje opisuje določeno odvisnost količin X in Y, zmanjša na izračun minimuma funkcije dveh spremenljivk:

Če želite to narediti, morate parcialne odvode glede na novi spremenljivki a in b enačiti na nič in rešiti primitivni sistem, sestavljen iz dveh enačb z 2 neznankama oblike:

Po nekaj preprostih transformacijah, vključno z deljenjem z 2 in manipulacijo vsot, dobimo:

Če jo rešimo, na primer z uporabo Cramerjeve metode, dobimo stacionarno točko z določenimi koeficienti a * in b *. To je minimum, tj. za predvidevanje kolikšen promet bo imela trgovina za določeno območje je primerna ravna črta y = a * x + b *, ki je regresijski model za obravnavani primer. Seveda vam ne bo omogočilo, da bi našli točen rezultat, vendar vam bo pomagalo dobiti idejo o tem, ali se bo nakup določenega območja na kredit v trgovini izplačal.

Kako implementirati metodo najmanjših kvadratov v Excelu

Excel ima funkcijo za izračun vrednosti z uporabo najmanjših kvadratov. Ima naslednjo obliko: »TREND« (znane vrednosti Y; znane vrednosti X; nove vrednosti X; konstanta). Uporabimo formulo za izračun OLS v Excelu v naši tabeli.

To storite tako, da v celico, v kateri naj se izpiše rezultat izračuna po metodi najmanjših kvadratov v Excelu, vnesete znak “=” in izberete funkcijo “TREND”. V oknu, ki se odpre, izpolnite ustrezna polja in označite:

• obseg znanih vrednosti za Y (v tem primeru podatki o prometu trgovine);
• obseg x 1 , …x n , to je velikost prodajnega prostora;
• znane in neznane vrednosti x, za katere morate ugotoviti velikost prometa (za informacije o njihovi lokaciji na delovnem listu glejte spodaj).

Poleg tega formula vsebuje logično spremenljivko »Const«. Če v ustrezno polje vnesete 1, to pomeni, da morate izvesti izračune ob predpostavki, da je b = 0.

Če morate izvedeti napoved za več kot eno vrednost x, potem po vnosu formule ne smete pritisniti "Enter", ampak morate na tipkovnici vnesti kombinacijo "Shift" + "Control" + "Enter".

Nekatere funkcije

Regresijska analiza je lahko dostopen celo lutkam. Excelovo formulo za napovedovanje vrednosti niza neznanih spremenljivk – TREND – lahko uporabljajo tudi tisti, ki še nikoli niso slišali za najmanjše kvadrate. Dovolj je le poznati nekatere značilnosti njegovega dela. Zlasti:

• Če razporedite obseg znanih vrednosti spremenljivke y v eno vrstico ali stolpec, bo program vsako vrstico (stolpec) z znanimi vrednostmi x zaznal kot ločeno spremenljivko.
• Če obseg z znanim x ni določen v oknu TREND, ga bo program pri uporabi funkcije v Excelu obravnaval kot matriko, sestavljeno iz celih števil, katerih število ustreza obsegu z danimi vrednostmi y spremenljivka.
• Za izpis matrike "predvidenih" vrednosti je treba izraz za izračun trenda vnesti kot matrično formulo.
• Če nove vrednosti x niso določene, jih funkcija TREND šteje za enake znanim. Če niso navedeni, se kot argument vzame niz 1; 2; 3; 4;…, kar je sorazmerno z razponom z že podanimi parametri y.
• Obseg, ki vsebuje nove vrednosti x, mora imeti enakih ali več vrstic ali stolpcev kot obseg, ki vsebuje podane vrednosti y. Z drugimi besedami, mora biti sorazmeren z neodvisnimi spremenljivkami.
• Matrika z znanimi vrednostmi x lahko vsebuje več spremenljivk. Če pa govorimo samo o enem, potem je potrebno, da so razponi z danimi vrednostmi x in y sorazmerni. V primeru več spremenljivk je potrebno, da obseg z danimi vrednostmi y ustreza enemu stolpcu ali eni vrstici.

funkcija PREDICTION

Izvedeno z uporabo več funkcij. Eden od njih se imenuje "NAPOVED". Podoben je "TRENDU", tj. daje rezultat izračuna z uporabo metode najmanjših kvadratov. Vendar le za en X, za katerega vrednost Y ni znana.

Zdaj poznate formule v Excelu za lutke, ki vam omogočajo napovedovanje prihodnje vrednosti določenega kazalnika glede na linearni trend.

Metoda najmanjših kvadratov je matematični postopek za konstruiranje linearna enačba, ki bi se najbolj ujemala z nizom dveh nizov števil. Namen uporabe te metode je zmanjšati skupno kvadratno napako. Excel ima orodja, s katerimi lahko ta metoda med izračuni. Ugotovimo, kako se to naredi.

Metoda najmanjših kvadratov (LSM) je matematični opis odvisnosti ene spremenljivke od druge. Lahko se uporablja pri napovedovanju.

Omogočanje dodatka Find Solution

Če želite uporabljati MNC v Excelu, morate omogočiti dodatek "Iskanje rešitve", ki je privzeto onemogočen.

Zdaj pa funkcija Iskanje rešitve v Excelu je aktiviran, njegova orodja pa so prikazana na traku.

Problemski pogoji

Opišimo uporabo LSM na konkretnem primeru. Imamo dve vrstici številk x in l , katerega zaporedje je prikazano na spodnji sliki.

To odvisnost lahko najbolj natančno opišemo s funkcijo:

Ob tem je znano, ko x=0 l tudi enakovredna 0 . Zato lahko to enačbo opišemo z odvisnostjo y=nx .

Najti moramo najmanjšo vsoto kvadratov razlike.

rešitev

Preidimo na opis neposredne uporabe metode.

Kot lahko vidite, je uporaba metode najmanjših kvadratov precej zapleten matematični postopek. Pokazali smo ga v akciji na preprostem primeru, vendar obstajajo veliko bolj zapleteni primeri. Vendar so orodja Microsoft Excel zasnovana tako, da čim bolj poenostavijo izračune.

Metoda najmanjših kvadratov (OLS) spada na področje regresijske analize. Ima veliko aplikacij, saj omogoča približno predstavitev dane funkcije z drugimi preprostejšimi. LSM je lahko izjemno uporaben pri obdelavi opazovanj in se aktivno uporablja za ocenjevanje nekaterih količin na podlagi rezultatov meritev drugih, ki vsebujejo naključne napake. V tem članku se boste naučili, kako izvajati izračune najmanjših kvadratov v Excelu.

Izjava problema na konkretnem primeru

Recimo, da obstajata dva indikatorja X in Y. Poleg tega je Y odvisen od X. Ker nas OLS zanima z vidika regresijske analize (v Excelu so njegove metode implementirane z vgrajenimi funkcijami), bi morali takoj preiti na obravnavo specifičen problem.

Naj bo torej X maloprodajni prostor trgovine z živili, merjen v kvadratnih metrih, Y pa letni promet, merjen v milijonih rubljev.

Narediti je treba napoved, kakšen promet (Y) bo imela trgovina, če bo imela ta ali oni maloprodajni prostor. Očitno je, da funkcija Y = f (X) narašča, saj hipermarket proda več blaga kot stojnica.

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

Recimo, da imamo tabelo, sestavljeno s podatki za n trgovin.

Glede na matematično statistiko bodo rezultati bolj ali manj pravilni, če se pregledajo podatki o vsaj 5-6 objektih. Poleg tega ni mogoče uporabiti "nenormalnih" rezultatov. Zlasti elitni mali butik ima lahko promet večkrat večji od prometa velikih maloprodajnih mest razreda "masmarket".

Bistvo metode

Podatke tabele lahko upodobimo na kartezični ravnini v obliki točk M 1 (x 1, y 1), ... M n (x n, y n). Zdaj se bo rešitev problema zmanjšala na izbiro aproksimativne funkcije y = f (x), ki ima graf, ki poteka čim bližje točkam M 1, M 2, .. M n.

Seveda lahko uporabite polinom visoke stopnje, vendar te možnosti ni le težko izvesti, ampak tudi preprosto napačna, saj ne bo odražala glavnega trenda, ki ga je treba zaznati. Najbolj smiselna rešitev je iskanje premice y = ax + b, ki najbolje približa eksperimentalne podatke oziroma natančneje koeficienta a in b.

Ocena točnosti

Pri vsakem približku je še posebej pomembna ocena njegove natančnosti. Označimo z e i razliko (odklon) med funkcionalno in eksperimentalno vrednostjo za točko x i, to je e i = y i - f (x i).

Očitno je, da lahko za oceno točnosti približka uporabite vsoto odstopanj, tj. pri izbiri ravne črte za približno predstavitev odvisnosti X od Y morate dati prednost tisti z najmanjšo vrednostjo vsota e i na vseh obravnavanih točkah. Vendar ni vse tako preprosto, saj bodo poleg pozitivnih odstopanj tudi negativna.

Zadevo je mogoče rešiti z moduli odstopanj ali njihovimi kvadrati. Zadnja metoda je najpogosteje uporabljena. Uporablja se na številnih področjih, vključno z regresijsko analizo (implementirana v Excelu z uporabo dveh vgrajenih funkcij), in je že dolgo dokazala svojo učinkovitost.

Metoda najmanjših kvadratov

Excel, kot veste, ima vgrajeno funkcijo AutoSum, ki vam omogoča izračun vrednosti vseh vrednosti, ki se nahajajo v izbranem obsegu. Tako nas nič ne ovira pri izračunavanju vrednosti izraza (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

V matematičnem zapisu je to videti takole:

Ker je bila sprva sprejeta odločitev za približek z uporabo ravne črte, imamo:

Tako se naloga iskanja ravne črte, ki najbolje opisuje določeno odvisnost količin X in Y, zmanjša na izračun minimuma funkcije dveh spremenljivk:

Če želite to narediti, morate parcialne odvode glede na novi spremenljivki a in b enačiti na nič in rešiti primitivni sistem, sestavljen iz dveh enačb z 2 neznankama oblike:

Po nekaj preprostih transformacijah, vključno z deljenjem z 2 in manipulacijo vsot, dobimo:

Če jo rešimo, na primer z uporabo Cramerjeve metode, dobimo stacionarno točko z določenimi koeficienti a * in b *. To je minimum, tj. za predvidevanje kolikšen promet bo imela trgovina za določeno območje je primerna ravna črta y = a * x + b *, ki je regresijski model za obravnavani primer. Seveda vam ne bo omogočilo, da bi našli točen rezultat, vendar vam bo pomagalo dobiti idejo o tem, ali se bo nakup določenega območja na kredit v trgovini izplačal.

Kako implementirati metodo najmanjših kvadratov v Excelu

Excel ima funkcijo za izračun vrednosti z uporabo najmanjših kvadratov. Ima naslednjo obliko: »TREND« (znane vrednosti Y; znane vrednosti X; nove vrednosti X; konstanta). Uporabimo formulo za izračun OLS v Excelu v naši tabeli.

To storite tako, da v celico, v kateri naj se izpiše rezultat izračuna po metodi najmanjših kvadratov v Excelu, vnesete znak “=” in izberete funkcijo “TREND”. V oknu, ki se odpre, izpolnite ustrezna polja in označite:

• obseg znanih vrednosti za Y (v tem primeru podatki o prometu trgovine);
• obseg x 1 , …x n , to je velikost prodajnega prostora;
• znane in neznane vrednosti x, za katere morate ugotoviti velikost prometa (za informacije o njihovi lokaciji na delovnem listu glejte spodaj).

Poleg tega formula vsebuje logično spremenljivko »Const«. Če v ustrezno polje vnesete 1, to pomeni, da morate izvesti izračune ob predpostavki, da je b = 0.

Če morate izvedeti napoved za več kot eno vrednost x, potem po vnosu formule ne smete pritisniti "Enter", ampak morate na tipkovnici vnesti kombinacijo "Shift" + "Control" + "Enter".

Nekatere funkcije

Regresijska analiza je lahko dostopna tudi telebanom. Excelovo formulo za napovedovanje vrednosti niza neznanih spremenljivk – TREND – lahko uporabljajo tudi tisti, ki še nikoli niso slišali za najmanjše kvadrate. Dovolj je le poznati nekatere značilnosti njegovega dela. Zlasti:

• Če razporedite obseg znanih vrednosti spremenljivke y v eno vrstico ali stolpec, bo program vsako vrstico (stolpec) z znanimi vrednostmi x zaznal kot ločeno spremenljivko.
• Če obseg z znanim x ni določen v oknu TREND, ga bo program pri uporabi funkcije v Excelu obravnaval kot matriko, sestavljeno iz celih števil, katerih število ustreza obsegu z danimi vrednostmi y spremenljivka.
• Za izpis matrike "predvidenih" vrednosti je treba izraz za izračun trenda vnesti kot matrično formulo.
• Če nove vrednosti x niso določene, jih funkcija TREND šteje za enake znanim. Če niso navedeni, se kot argument vzame niz 1; 2; 3; 4;…, kar je sorazmerno z razponom z že podanimi parametri y.
• Obseg, ki vsebuje nove vrednosti x, mora imeti enakih ali več vrstic ali stolpcev kot obseg, ki vsebuje podane vrednosti y. Z drugimi besedami, mora biti sorazmeren z neodvisnimi spremenljivkami.
• Matrika z znanimi vrednostmi x lahko vsebuje več spremenljivk. Če pa govorimo samo o enem, potem je potrebno, da so razponi z danimi vrednostmi x in y sorazmerni. V primeru več spremenljivk je potrebno, da obseg z danimi vrednostmi y ustreza enemu stolpcu ali eni vrstici.

funkcija PREDICTION

Regresijska analiza v Excelu se izvaja z uporabo več funkcij. Eden od njih se imenuje "NAPOVED". Podoben je "TRENDU", tj. daje rezultat izračuna z uporabo metode najmanjših kvadratov. Vendar le za en X, za katerega vrednost Y ni znana.

Zdaj poznate formule v Excelu za lutke, ki vam omogočajo napovedovanje prihodnje vrednosti določenega kazalnika glede na linearni trend.