Riješite sistem jednačina koristeći metodu najmanjih kvadrata u Excelu. Primjena metode najmanjih kvadrata u Excelu. Nekoliko riječi o ispravnosti početnih podataka korištenih za predviđanje

Metoda najmanjih kvadrata je matematički postupak za konstruisanje linearne jednadžbe koja najviše odgovara skupu dva niza brojeva. Svrha korištenja ove metode je minimiziranje ukupne kvadratne greške. Excel ima alate koji vam mogu pomoći da primijenite ovu metodu u vašim proračunima. Hajde da shvatimo kako se to radi.

· Korišćenje metode u Excel-u

o Omogućavanje dodatka “Solution Search”.

o Problemski uslovi

o Rješenje

Korištenje metode u Excelu

Metoda najmanjih kvadrata (LSM) je matematički opis zavisnosti jedne varijable od druge. Može se koristiti za predviđanje.

Omogućavanje dodatka Find Solution

Da biste koristili MNC u Excelu, morate omogućiti dodatak "Pronalaženje rješenja", koji je podrazumevano onemogućen.

1. Idite na karticu "File".

2. Kliknite na naziv odjeljka "Opcije".

3. U prozoru koji se otvori odaberite pododjeljak "Dodaci".

4. U bloku "kontrola", koji se nalazi na dnu prozora, postavite prekidač u položaj "Excel dodaci"(ako ima drugačiju vrijednost) i kliknite na dugme "idi...".

5. Otvara se mali prozor. Stavljamo kvačicu pored parametra "Pronalaženje rješenja". Kliknite na dugme "UREDU".

Sada funkcija Pronalaženje rješenja u Excelu je aktiviran, a njegovi alati se pojavljuju na traci.

lekcija: Pronalaženje rješenja u Excelu

Uslovi problema

Hajde da opišemo primjenu LSM-a na konkretan primjer. Imamo dva reda brojeva x I y, čiji je redoslijed prikazan na slici ispod.

Ova zavisnost se najpreciznije može opisati funkcijom:

Istovremeno, poznato je da kada x=0 y takođe jednaka 0 . Stoga se ova jednačina može opisati zavisnošću y=nx.

Moramo pronaći minimalni zbir kvadrata razlike.

Rješenje

Pređimo na opis direktne primjene metode.

1. Lijevo od prve vrijednosti x stavi broj 1 . Ovo će biti približna vrijednost vrijednosti prvog koeficijenta n.

2. Desno od kolone y dodaj još jednu kolonu - nx. U prvoj ćeliji ove kolone upisujemo formulu za množenje koeficijenta n po ćeliji prve varijable x. Istovremeno, vezu na polje sa koeficijentom činimo apsolutnim, jer se ova vrijednost neće promijeniti. Kliknite na dugme Enter.

3. Koristeći marker za popunjavanje, kopirajte ovu formulu u cijeli raspon tabele u koloni ispod.

4. U posebnoj ćeliji izračunajte zbir razlika između kvadrata vrijednosti y I nx. Da biste to učinili, kliknite na dugme "Insert Function".

5. U otvorenom "Čarobnjak funkcija" traži ulaz "SUMMKVARNA". Odaberite ga i pritisnite dugme "UREDU".

6. Otvara se prozor sa argumentima. Na terenu "Niz_x" y. Na terenu "Niz_y" unesite opseg ćelija kolone nx. Da biste unijeli vrijednosti, jednostavno postavite kursor u polje i odaberite odgovarajući raspon na listu. Nakon unosa, kliknite na dugme "UREDU".

7. Idite na karticu "Podaci". Na vrpci u kutiji s alatima "analiza" kliknite na dugme "Pronalaženje rješenja".

8. Otvara se prozor parametara za ovaj alat. Na terenu “Optimiziraj funkciju cilja” navedite adresu ćelije sa formulom "SUMMKVARNA". U parametru "prije" obavezno postavite prekidač u položaj "minimum". Na terenu "Promjena ćelija" navedite adresu sa vrijednošću koeficijenta n. Kliknite na dugme "Pronađi rješenje".

9. Rješenje će biti prikazano u ćeliji koeficijenta n. Ova vrijednost će biti najmanji kvadrat funkcije. Ako rezultat zadovoljava korisnika, kliknite na dugme "UREDU" u dodatnom prozoru.

Kao što vidite, primjena metode najmanjih kvadrata je prilično složen matematički postupak. Pokazali smo to na djelu koristeći jednostavan primjer, ali ima mnogo složenijih slučajeva. Međutim, Microsoft Excel alati su dizajnirani da pojednostave proračune što je više moguće.

http://multitest.semico.ru/mnk.htm

Opće odredbe

Kako manji broj u apsolutnoj vrijednosti, bolje je odabrana prava linija (2). Kao karakteristiku tačnosti odabira prave linije (2) možemo uzeti zbir kvadrata

Minimalni uslovi za S će biti

	(6)
	(7)

Jednačine (6) i (7) se mogu napisati na sljedeći način:

	(8)
	(9)

Iz jednadžbi (8) i (9) lako je pronaći a i b iz eksperimentalnih vrijednosti xi i y i. Prava (2), definirana jednadžbama (8) i (9), naziva se linija dobivena metodom najmanjih kvadrata (ovaj naziv naglašava da zbir kvadrata S ima minimum). Jednačine (8) i (9), iz kojih je određena prava linija (2), nazivaju se normalne jednačine.

Možete naznačiti jednostavan i opći način sastavljanja normalnih jednačina. Koristeći eksperimentalne tačke (1) i jednačinu (2), možemo napisati sistem jednačina za a i b

Pomnožimo lijevu i desnu stranu svake od ovih jednadžbi koeficijentom prve nepoznate a (tj. sa x 1, x 2, ..., x n) i dodajmo rezultirajuće jednadžbe, što rezultira prvom normalnom jednadžbom (8) .

Pomnožimo lijevu i desnu stranu svake od ovih jednačina sa koeficijentom druge nepoznate b, tj. sa 1, i zbrojimo rezultirajuće jednačine, rezultat je druga normalna jednačina (9).

Ova metoda dobivanja normalnih jednačina je općenito: pogodna je, na primjer, za funkciju

postoji konstantna vrijednost i ona se mora odrediti iz eksperimentalnih podataka (1).

Sistem jednačina za k se može napisati:

Pronađite pravu liniju (2) koristeći metodu najmanjih kvadrata.

Rješenje. Mi nalazimo:

X i =21, y i =46,3, x i 2 =91, x i y i =179,1.

Pišemo jednačine (8) i (9)91a+21b=179.1,

21a+6b=46.3, odavde nalazimo
a=0,98 b=4,3.

Koja nalazi najširu primenu u raznim oblastima nauke i praktične delatnosti. To može biti fizika, hemija, biologija, ekonomija, sociologija, psihologija i tako dalje, tako dalje. Voljom sudbine, često moram da se bavim ekonomijom, i zato ću danas za vas organizovati putovanje u neverovatnu zemlju tzv. Ekonometrija=) ...Kako to ne želiš?! Tamo je jako dobro - samo treba da se odlučite! ...Ali ono što sigurno želite je naučiti kako rješavati probleme metoda najmanjih kvadrata. A posebno marljivi čitaoci naučiće da ih rešavaju ne samo precizno, već i VEOMA BRZO ;-) Ali prvo opšta izjava o problemu+ prateći primjer:

Proučavajmo indikatore u određenoj predmetnoj oblasti koji imaju kvantitativni izraz. Istovremeno, postoje svi razlozi za vjerovanje da indikator ovisi o indikatoru. Ova pretpostavka bi mogla biti kao naučna hipoteza, i biti zasnovani na osnovnom zdravom razumu. Ostavimo, međutim, nauku po strani i istražimo privlačnija područja – naime, trgovine prehrambenim proizvodima. Označimo sa:

– maloprodajni prostor trgovine, m2,
– godišnji promet prehrambene prodavnice, milion rubalja.

Apsolutno je jasno da što je veća površina prodavnice, to će u većini slučajeva biti veći njen promet.

Pretpostavimo da nakon izvođenja zapažanja/eksperimenata/proračunavanja/plesa uz tamburu imamo na raspolaganju numeričke podatke:

Sa prehrambenim prodavnicama mislim da je sve jasno: - ovo je površina 1. prodavnice, - njen godišnji promet, - površina 2. prodavnice, - njen godišnji promet itd. Uzgred, uopšte nije potrebno imati pristup klasifikovanim materijalima - prilično tačna procena trgovinskog prometa može se dobiti pomoću matematičke statistike. Ipak, nemojmo se ometati, kurs komercijalne špijunaže je već plaćen =)

Tabelarni podaci se također mogu napisati u obliku tačaka i prikazati u poznatom obliku Kartezijanski sistem .

Odgovorimo na jedno važno pitanje: Koliko bodova je potrebno za kvalitativnu studiju?

Što veće, to bolje. Minimalni prihvatljivi set se sastoji od 5-6 bodova. Osim toga, kada je količina podataka mala, “anomalni” rezultati se ne mogu uključiti u uzorak. Tako, na primjer, mala elitna radnja može zaraditi redove veličine više od "njegovih kolega", čime se iskrivljuje opšti obrazac, to je ono što trebate pronaći!

Vrlo jednostavno rečeno, moramo odabrati funkciju, raspored koji prolazi što bliže tačkama . Ova funkcija se zove aproksimirajući (aproksimacija - aproksimacija) ili teorijska funkcija . Uopšteno govoreći, ovdje se odmah pojavljuje očigledan "konkurent" - polinom visok stepen, čiji graf prolazi kroz SVE tačke. Ali ova opcija je komplikovana i često jednostavno netočna. (pošto će se grafikon stalno "petljati" i loše odražavati glavni trend).

Dakle, tražena funkcija mora biti prilično jednostavna i istovremeno adekvatno odražavati ovisnost. Kao što možete pretpostaviti, jedna od metoda za pronalaženje takvih funkcija se zove metoda najmanjih kvadrata. Prvo, pogledajmo njegovu suštinu općenito. Neka neka funkcija aproksimira eksperimentalne podatke:


Kako ocijeniti tačnost ove aproksimacije? Izračunajmo i razlike (odstupanja) između eksperimentalne i funkcionalne vrijednosti (učimo crtež). Prva misao koja vam pada na pamet je procijeniti koliki je zbroj, ali problem je što razlike mogu biti negativne (Na primjer, ) a odstupanja kao rezultat takvog zbrajanja će se poništiti. Stoga, kao procjenu tačnosti aproksimacije, treba uzeti zbir moduli odstupanja:

ili srušeno: (u slučaju da neko ne zna: – ovo je ikona sume, i – pomoćna varijabla „brojača“, koja uzima vrijednosti od 1 do ).

Aproksimacijom eksperimentalnih tačaka različitim funkcijama dobićemo različita značenja, i očito, gdje je ovaj iznos manji, ta funkcija je tačnija.

Takav metod postoji i zove se metoda najmanjeg modula. Međutim, u praksi je postao mnogo rašireniji metoda najmanjeg kvadrata, u kojem se moguće negativne vrijednosti eliminiraju ne modulom, već kvadriranjem odstupanja:

, nakon čega se radi na odabiru funkcije takve da je zbir kvadrata odstupanja bio što manji. Zapravo, odatle potiče naziv metode.

A sada se vraćamo na nešto drugo važna tačka: kao što je gore navedeno, odabrana funkcija bi trebala biti prilično jednostavna - ali postoji i mnogo takvih funkcija: linearno , hiperbolično, eksponencijalna, logaritamski, kvadratni itd. I, naravno, ovdje bih odmah želio „smanjiti polje aktivnosti“. Koju klasu funkcija trebam odabrati za istraživanje? Primitivna, ali efikasna tehnika:

– Najlakši način je da prikažete tačke na crtežu i analizirati njihovu lokaciju. Ako imaju tendenciju da trče u pravoj liniji, onda biste trebali potražiti jednačina prave sa optimalnim vrijednostima i . Drugim riječima, zadatak je pronaći TAKVE koeficijente tako da zbir kvadrata odstupanja bude najmanji.

Ako se tačke nalaze, na primjer, uzduž hiperbola, onda je očito jasno da će linearna funkcija dati lošu aproksimaciju. U ovom slučaju tražimo najpovoljnije koeficijente za jednadžbu hiperbole – oni koji daju minimalni zbir kvadrata .

Sada imajte na umu da u oba slučaja govorimo funkcije dvije varijable, čiji su argumenti pretraživali parametre zavisnosti:

A u suštini moramo riješiti standardni problem - pronaći minimalna funkcija dvije varijable.

Prisjetimo se našeg primjera: pretpostavimo da se tačke "prodavnice" obično nalaze u pravoj liniji i postoji svaki razlog vjerovati da linearna zavisnost promet od maloprodajnog prostora. Nađimo TAKVE koeficijente “a” i “be” takve da je zbir kvadrata odstupanja bio najmanji. Sve je kao i obično - prvo Parcijalni derivati ​​1. reda. Prema pravilo linearnosti Možete razlikovati odmah ispod ikone sume:

Ako želite da koristite ove informacije za esej ili nastavni rad - bit ću vrlo zahvalan na linku na listi izvora; ovako detaljne proračune ćete naći na nekoliko mjesta:

Kreirajmo standardni sistem:

Svaku jednačinu smanjujemo za "dva" i, pored toga, "razbijamo" zbrojeve:

Bilješka : nezavisno analizirati zašto se “a” i “be” mogu izdvojiti izvan ikone zbira. Inače, formalno se to može učiniti sa sumom

Prepišimo sistem u "primijenjenom" obliku:

nakon čega počinje da se pojavljuje algoritam za rješavanje našeg problema:

Znamo li koordinate tačaka? Mi znamo. Iznosi možemo li ga naći? Lako. Hajde da napravimo najjednostavnije sistem dvije linearne jednadžbe u dvije nepoznate(“a” i “biti”). Rešavamo sistem, npr. Cramerova metoda, kao rezultat toga dobijamo stacionarnu tačku. Provjeravam dovoljan uslov za ekstrem, možemo potvrditi da je u ovom trenutku funkcija dostiže tačno minimum. Provjera uključuje dodatne proračune i stoga ćemo je ostaviti iza scene (ako je potrebno, okvir koji nedostaje može se vidjeti). Izvlačimo konačan zaključak:

Funkcija najbolji način (barem u usporedbi s bilo kojom drugom linearnom funkcijom) približava eksperimentalne tačke . Grubo govoreći, njegov graf prolazi što je moguće bliže ovim tačkama. U tradiciji ekonometrija rezultirajuća aproksimirajuća funkcija se također poziva uparena jednačina linearne regresije .

Problem koji se razmatra ima veliki praktični značaj. U našem primjeru, jednadžba. omogućava vam da predvidite koji trgovinski promet ("Igrek") trgovina će imati jednu ili drugu vrijednost prodajnog prostora (jedno ili drugo značenje "x"). Da, rezultirajuća prognoza će biti samo prognoza, ali će se u mnogim slučajevima pokazati prilično tačnom.

Analizirat ću samo jedan problem sa "pravim" brojevima, jer u tome nema poteškoća - svi proračuni su na nivou školski program 7-8 razreda. U 95 posto slučajeva od vas će se tražiti da pronađete samo linearnu funkciju, ali na samom kraju članka pokazaću da nije teže pronaći jednadžbe optimalne hiperbole, eksponencijalne i nekih drugih funkcija.

U stvari, ostaje samo distribuirati obećane dobrote - tako da možete naučiti rješavati takve primjere ne samo precizno, već i brzo. Pažljivo proučavamo standard:

Zadatak

Kao rezultat proučavanja odnosa između dva indikatora, dobijeni su sljedeći parovi brojeva:

Koristeći metodu najmanjih kvadrata, pronađite linearnu funkciju koja najbolje aproksimira empirijsku (iskusan) podaci. Napravite crtež na kojem ćete konstruirati eksperimentalne točke i graf aproksimirajuće funkcije u kartezijanskom pravokutnom koordinatnom sistemu . Pronađite zbroj kvadrata odstupanja između empirijske i teorijske vrijednosti. Saznajte da li bi ova funkcija bila bolja (sa stanovišta metode najmanjih kvadrata) približiti eksperimentalne tačke.

Imajte na umu da su značenja “x” prirodna, a ovo ima karakteristično smisleno značenje, o kojem ću govoriti malo kasnije; ali oni, naravno, mogu biti i razlomci. Osim toga, ovisno o sadržaju određenog zadatka, vrijednosti "X" i "igra" mogu biti potpuno ili djelomično negativne. Pa, dobili smo zadatak „bez lica“ i počinjemo ga rješenje:

Nalazimo koeficijente optimalne funkcije kao rješenje sistema:

U svrhu kompaktnijeg snimanja, varijabla “counter” može se izostaviti, jer je već jasno da se zbrajanje vrši od 1 do .

Pogodnije je izračunati potrebne količine u obliku tabele:


Izračuni se mogu izvršiti na mikrokalkulatoru, ali je mnogo bolje koristiti Excel - i brže i bez grešaka; pogledajte kratak video:

Tako dobijamo sledeće sistem:

Ovdje možete pomnožiti drugu jednačinu sa 3 i oduzmi 2. od 1. jednačine član po član. Ali to je sreća - u praksi sistemi često nisu dar, au takvim slučajevima štedi Cramerova metoda:
, što znači da sistem ima jedinstveno rješenje.

Hajde da proverimo. Razumijem da ne želite, ali zašto preskakati greške tamo gdje se apsolutno ne mogu propustiti? Zamenimo pronađeno rešenje u levu stranu svake jednačine sistema:

Dobijene su desne strane odgovarajućih jednačina, što znači da je sistem ispravno riješen.

Dakle, željena aproksimirajuća funkcija: – od sve linearne funkcije Ona je ta koja najbolje aproksimira eksperimentalne podatke.

Za razliku od ravno zavisnost prometa prodavnice od njene površine, pronađena zavisnost je obrnuto (princip "što više, to manje"), a tu činjenicu odmah otkriva negativac nagib. Funkcija nam govori da povećanjem određenog indikatora za 1 jedinicu, vrijednost zavisnog indikatora opada prosjek za 0,65 jedinica. Kako kažu, što je veća cijena heljde, to se manje prodaje.

Da bismo nacrtali graf aproksimirajuće funkcije, nalazimo njene dvije vrijednosti:

i izvedite crtež:


Konstruisana prava linija se zove linija trenda (naime, linearna linija trenda, tj. u opštem slučaju, trend nije nužno ravna linija). Svima je poznat izraz “biti u trendu” i mislim da ovaj termin ne treba dodatno komentarisati.

Izračunajmo zbir kvadrata odstupanja između empirijskih i teorijskih vrijednosti. Geometrijski, ovo je zbir kvadrata dužina segmenata "maline" (od kojih su dva toliko mala da se ni ne vide).

Sumiramo proračune u tabeli:


Opet, mogu se raditi ručno; za svaki slučaj, dat ću primjer za 1. točku:

ali mnogo je efikasnije to učiniti na već poznati način:

Ponavljamo još jednom: Šta znači dobijeni rezultat? Od sve linearne funkcije y funkcija indikator je najmanji, odnosno u svojoj porodici je najbolja aproksimacija. I ovdje, usput, konačno pitanje problema nije slučajno: šta ako je predložena eksponencijalna funkcija da li bi bilo bolje približiti eksperimentalne tačke?

Nađimo odgovarajući zbir kvadrata odstupanja - da bismo ih razlikovali, označit ću ih slovom "epsilon". Tehnika je potpuno ista:


I opet, za svaki slučaj, kalkulacije za 1. tačku:

U Excelu koristimo standardnu ​​funkciju EXP (sintaksu možete pronaći u Excel pomoći).

Zaključak: , što znači da eksponencijalna funkcija aproksimira eksperimentalne tačke lošije od prave linije .

Ali ovdje treba napomenuti da je „gore“. ne znači još, šta nije uredu. Sada sam napravio graf ove eksponencijalne funkcije - i on takođe prolazi blizu tačaka - toliko da je bez analitičkog istraživanja teško reći koja je funkcija preciznija.

Ovim je rješenje završeno i vraćam se na pitanje prirodnih vrijednosti argumenta. U različitim studijama, obično ekonomskim ili sociološkim, prirodni "X" se koriste za brojenje mjeseci, godina ili drugih jednakih vremenskih intervala. Razmotrite, na primjer, sljedeći problem.

Metoda najmanjih kvadrata (OLS) pripada području regresione analize. Ima mnogo namjena jer omogućava približan prikaz datu funkciju drugi su jednostavniji. LSM može biti izuzetno koristan u obradi opservacija, a aktivno se koristi za procjenu nekih veličina na osnovu rezultata mjerenja drugih koji sadrže slučajne greške. U ovom članku ćete naučiti kako implementirati izračune najmanjih kvadrata u Excelu.

Iskazivanje problema na konkretnom primjeru

Pretpostavimo da postoje dva indikatora X i Y. Štaviše, Y zavisi od X. Budući da nas OLS zanima sa stanovišta regresione analize (u Excelu se njegove metode implementiraju pomoću ugrađenih funkcija), treba odmah preći na razmatranje konkretan problem.

Dakle, neka je X maloprodajni prostor trgovine, mjereno u kvadratnih metara, a Y je godišnji promet, određen u milionima rubalja.

Potrebno je napraviti prognozu koliki će promet (Y) trgovina imati ako ima ovaj ili onaj maloprodajni prostor. Očigledno, funkcija Y = f (X) raste, jer hipermarket prodaje više robe od tezge.

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

Recimo da imamo tabelu napravljenu koristeći podatke za n prodavnica.

Prema matematičke statistike, rezultati će biti manje-više tačni ako se ispitaju podaci o najmanje 5-6 objekata. Osim toga, "anomalni" rezultati se ne mogu koristiti. Konkretno, elitni mali butik može imati promet koji je nekoliko puta veći od prometa velikih maloprodajnih objekata klase „masmarket“.

Suština metode

Podaci tabele mogu se prikazati na kartezijanskoj ravni u obliku tačaka M 1 (x 1, y 1), ... M n (x n, y n). Sada će se rješenje problema svesti na izbor aproksimirajuće funkcije y = f (x), koja ima graf koji prolazi što bliže tačkama M 1, M 2, .. M n.

Naravno, možete koristiti polinom visokog stupnja, ali ova opcija nije samo teška za implementaciju, već je i jednostavno netočna, jer neće odražavati glavni trend koji treba otkriti. Najrazumnije rješenje je traženje prave linije y = ax + b, koja najbolje aproksimira eksperimentalne podatke, tačnije, koeficijente a i b.

Procjena tačnosti

Uz bilo kakvu aproksimaciju, procjena njegove tačnosti je od posebne važnosti. Označimo sa e i razliku (odstupanje) između funkcionalne i eksperimentalne vrijednosti za tačku x i, tj. e i = y i - f (x i).

Očigledno, da biste procijenili tačnost aproksimacije, možete koristiti zbir odstupanja, odnosno, kada birate pravu liniju za približni prikaz zavisnosti X od Y, treba dati prednost onoj s najmanjom vrijednošću zbir e i u svim tačkama koje se razmatraju. Međutim, nije sve tako jednostavno, jer će uz pozitivne devijacije biti i negativnih.

Problem se može riješiti korištenjem modula odstupanja ili njihovih kvadrata. Posljednja metoda je najčešće korištena. Koristi se u mnogim oblastima, uključujući regresijsku analizu (implementirana u Excelu pomoću dvije ugrađene funkcije), i odavno je dokazala svoju učinkovitost.

Metoda najmanjeg kvadrata

Excel, kao što znate, ima ugrađenu funkciju AutoSum koja vam omogućava da izračunate vrijednosti svih vrijednosti koje se nalaze u odabranom rasponu. Dakle, ništa nas neće spriječiti da izračunamo vrijednost izraza (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

U matematičkoj notaciji ovo izgleda ovako:

Pošto je prvobitno donesena odluka da se aproksimira pomoću prave linije, imamo:

Dakle, zadatak pronalaženja prave linije koja najbolje opisuje specifičnu zavisnost veličina X i Y svodi se na izračunavanje minimuma funkcije dvije varijable:

Da biste to učinili, morate parcijalne derivacije u odnosu na nove varijable a i b izjednačiti sa nulom, i riješiti primitivni sistem koji se sastoji od dvije jednadžbe sa 2 nepoznate forme:

Nakon nekoliko jednostavnih transformacija, uključujući dijeljenje sa 2 i manipulaciju suma, dobijamo:

Rješavajući ga, na primjer, Cramerovom metodom, dobijamo stacionarnu tačku sa određenim koeficijentima a * i b *. Ovo je minimum, tj. da se predvidi koliki će promet imati trgovina za određeno područje, prikladna je prava linija y = a * x + b *, što je regresijski model za predmetni primjer. Naravno, to vam neće omogućiti da pronađete točan rezultat, ali će vam pomoći da steknete ideju o tome hoće li se kupovina određenog područja na kredit u trgovini isplatiti.

Kako implementirati najmanje kvadrate u Excelu

Excel ima funkciju za izračunavanje vrijednosti pomoću najmanjih kvadrata. Ima sljedeći oblik: “TREND” (poznate Y vrijednosti; poznate X vrijednosti; nove X vrijednosti; konstanta). Primijenimo formulu za izračunavanje OLS-a u Excelu na našu tablicu.

Da biste to učinili, unesite znak “=” u ćeliju u kojoj bi trebao biti prikazan rezultat izračuna primjenom metode najmanjih kvadrata u Excelu i odaberite funkciju “TREND”. U prozoru koji se otvori popunite odgovarajuća polja, naglašavajući:

• raspon poznatih vrijednosti za Y (in u ovom slučaju podaci za trgovinski promet);
• raspon x 1 , …x n , odnosno veličina maloprodajnog prostora;
• i poznate i nepoznate vrijednosti x, za koje morate saznati veličinu prometa (za informacije o njihovoj lokaciji na radnom listu, pogledajte dolje).

Dodatno, formula sadrži logičku varijablu “Const”. Ako u odgovarajuće polje unesete 1, to će značiti da trebate izvršiti proračune, pod pretpostavkom da je b = 0.

Ako trebate saznati prognozu za više od jedne vrijednosti x, onda nakon unosa formule ne biste trebali pritisnuti "Enter", već morate upisati kombinaciju "Shift" + "Control" + "Enter" na tastaturi.

Neke karakteristike

Regresiona analiza može biti dostupna čak i lutkama. Excel formulu za predviđanje vrijednosti niza nepoznatih varijabli – TREND – mogu koristiti čak i oni koji nikada nisu čuli za najmanje kvadrate. Dovoljno je samo znati neke od karakteristika njegovog rada. posebno:

• Ako raspoređujete raspon poznatih vrijednosti varijable y u jednom redu ili stupcu, tada će svaki red (kolona) s poznatim vrijednostima x program percipirati kao zasebnu varijablu.
• Ako raspon sa poznatim x nije naveden u prozoru TREND, tada će ga, kada se koristi funkcija u Excelu, program tretirati kao niz koji se sastoji od cijelih brojeva, čiji broj odgovara rasponu sa datim vrijednostima varijabla y.
• Za izlaz niza "predviđenih" vrijednosti, izraz za izračunavanje trenda se mora unijeti kao formula niza.
• Ako nove vrijednosti x nisu specificirane, funkcija TREND ih smatra jednakim poznatim. Ako nisu specificirani, tada se niz 1 uzima kao argument; 2; 3; 4;…, što je srazmerno opsegu sa već date parametre y.
• Raspon koji sadrži nove vrijednosti x mora imati iste ili više redova ili stupaca kao raspon koji sadrži date vrijednosti y. Drugim riječima, mora biti proporcionalan nezavisnim varijablama.
• Niz sa poznatim x vrijednostima može sadržavati više varijabli. Međutim, ako govorimo samo o jednom, onda je potrebno da opsezi sa datim vrijednostima x i y budu proporcionalni. U slučaju više varijabli, potrebno je da raspon sa datim y vrijednostima stane u jednu kolonu ili jedan red.

Funkcija PREDICTION

Regresiona analiza u Excel-u se implementira pomoću nekoliko funkcija. Jedna od njih se zove “PREDIKCIJA”. Sličan je "TREND", tj. daje rezultat proračuna metodom najmanjih kvadrata. Međutim, samo za jedan X, za koji je vrijednost Y nepoznata.

Sada znate formule u Excelu za lutke koje vam omogućavaju da predvidite buduću vrijednost određenog indikatora prema linearnom trendu.

Metoda najmanjih kvadrata (LS) zasniva se na minimiziranju sume kvadrata odstupanja odabrane funkcije od podataka koji se proučavaju. U ovom članku ćemo aproksimirati dostupne podatke pomoću linearne funkcijey = a x + b .

Metoda najmanjeg kvadrata(engleski) Obicno Najmanje Kvadrati , O.L.S.) je jedna od osnovnih metoda regresione analize u smislu procjene nepoznatih parametara regresijski modeli prema uzorku podataka.

Razmotrimo aproksimaciju funkcijama koje zavise samo od jedne varijable:

• Linearno: y=ax+b (ovaj članak)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+s
• : y=ax 2 +bx+c

Bilješka: U ovom članku se razmatraju slučajevi aproksimacije polinomom od 3. do 6. stepena. Ovdje se razmatra aproksimacija trigonometrijskim polinomom.

Linearna zavisnost

Zanima nas veza između 2 varijable X I y. Postoji pretpostavka da y zavisi od X prema linearnom zakonu y = sjekira + b. Da bi odredio parametre ovog odnosa, istraživač je napravio zapažanja: za svaku vrijednost x i, mjereno je y i (pogledajte datoteku primjera). Prema tome, neka postoji 20 parova vrijednosti (x i; y i).

Bilješka: Ako je korak promjene X je konstantan, zatim da se gradi raspršene dijagrame može se koristiti, ako ne, onda morate koristiti tip grafikona Tacka .

Iz dijagrama je očigledno da je odnos između varijabli blizak linearnom. Da bi se razumjelo koja od mnogih pravih linija „najtačnije“ opisuje odnos između varijabli, potrebno je odrediti kriterij po kojem će se linije upoređivati.

Kao takav kriterijum koristimo izraz:

Gdje ŷ i = a * x i + b ; n – broj parova vrijednosti (u našem slučaju n=20)

Gornji izraz je zbir kvadrata udaljenosti između opaženih vrijednosti y i i ŷ i i često se označava kao SSE ( Suma of Na kvadrat Greške (Ostaci), zbir grešaka na kvadrat (ostaci)) .

Metoda najmanjeg kvadrata je odabrati takvu liniju ŷ = sjekira + b, za koji gornji izraz uzima minimalnu vrijednost.

Bilješka: Bilo koja linija u dvodimenzionalnom prostoru jedinstveno je određena vrijednostima 2 parametra: a (nagib) i b (smjena).

Vjeruje se da što je manji zbroj kvadrata udaljenosti, to odgovarajuća linija bolje aproksimira dostupne podatke i može se dalje koristiti za predviđanje vrijednosti y iz varijable x. Jasno je da čak i ako u stvarnosti ne postoji odnos između varijabli ili je odnos nelinearan, onda će OLS i dalje odabrati „najbolju“ liniju. Dakle, metoda najmanjih kvadrata ne govori ništa o prisutnosti realnog odnosa između varijabli; metoda vam jednostavno omogućava da odaberete takve parametre funkcije a I b , za koji je gornji izraz minimalan.

Izvođenjem ne baš složenih matematičkih operacija (za više detalja pogledajte), možete izračunati parametre a I b :

Kao što se vidi iz formule, parametar a predstavlja omjer kovarijance i , stoga u MS EXCEL-u za izračunavanje parametra A može biti korišteno slijedeće formule(cm. Datoteka primjera linearnog lista):

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

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

Takođe za izračunavanje parametra A možete koristiti formulu = TILT(C26:C45;B26:B45). Za parametar b koristite formulu = NOGA(C26:C45;B26:B45) .

Konačno, funkcija LINEST() vam omogućava da izračunate oba parametra odjednom. Za unos formule LINEST(C26:C45;B26:B45) Morate odabrati 2 ćelije u nizu i kliknuti CTRL + SHIFT + ENTER(vidi članak o). Vrijednost će biti vraćena u lijevu ćeliju A , desno - b .

Bilješka: Da biste izbjegli petljanje sa ulazom formule niza morat ćete dodatno koristiti funkciju INDEX(). Formula = INDEX(LINEST(C26:C45,B26:B45),1) ili samo = LINEST(C26:C45;B26:B45)će vratiti parametar odgovoran za nagib linije, tj. A . Formula = INDEX(LINEST(C26:C45,B26:B45),2)će vratiti parametar odgovoran za presek linije sa Y osom, tj. b .

Nakon izračunavanja parametara, dijagram raspršivanja možete nacrtati odgovarajuću liniju.

Drugi način za crtanje prave linije koristeći metodu najmanjih kvadrata je alat graf Linija trenda. Da biste to učinili, odaberite dijagram, odaberite iz izbornika Kartica Layout, V grupna analiza kliknite Linija trenda, onda Linearna aproksimacija .

Označavanjem okvira „prikaži jednačinu u dijagramu“ u dijagramu možete se uvjeriti da se gore pronađeni parametri poklapaju s vrijednostima ​​na dijagramu.

Bilješka: Da bi se parametri poklapali, tip dijagrama mora biti . Poenta je da prilikom konstruisanja dijagrama Raspored Vrijednosti osi X ne može odrediti korisnik (korisnik može odrediti samo oznake koje ne utječu na lokaciju tačaka). Umjesto X vrijednosti, koristi se sekvenca 1; 2; 3; ... (za kategorije numeracije). Stoga, ako gradite linija trenda na dijagramu tipa Raspored, tada će se umjesto stvarnih vrijednosti X koristiti vrijednosti ovog niza, što će dovesti do pogrešnog rezultata (osim ako, naravno, stvarne vrijednosti X se ne podudaraju sa sekvencom 1; 2; 3; ...).

Ima mnogo aplikacija, jer omogućava približan prikaz date funkcije drugim jednostavnijim. LSM može biti izuzetno koristan u obradi opservacija, a aktivno se koristi za procjenu nekih veličina na osnovu rezultata mjerenja drugih koji sadrže slučajne greške. U ovom članku ćete naučiti kako implementirati izračune najmanjih kvadrata u Excelu.

Iskazivanje problema na konkretnom primjeru

Pretpostavimo da postoje dva indikatora X i Y. Štaviše, Y zavisi od X. Budući da nas OLS zanima sa stanovišta regresione analize (u Excelu se njegove metode implementiraju pomoću ugrađenih funkcija), treba odmah preći na razmatranje konkretan problem.

Dakle, neka je X maloprodajni prostor prodavnice prehrambenih proizvoda, mjeren kvadratnim metrima, a Y godišnji promet, mjeren milionima rubalja.

Potrebno je napraviti prognozu koliki će promet (Y) trgovina imati ako ima ovaj ili onaj maloprodajni prostor. Očigledno, funkcija Y = f (X) raste, jer hipermarket prodaje više robe od tezge.

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

Recimo da imamo tabelu napravljenu koristeći podatke za n prodavnica.

Prema matematičkoj statistici, rezultati će biti manje-više tačni ako se ispitaju podaci o najmanje 5-6 objekata. Osim toga, "anomalni" rezultati se ne mogu koristiti. Konkretno, elitni mali butik može imati promet koji je nekoliko puta veći od prometa velikih maloprodajnih objekata klase „masmarket“.

Suština metode

Podaci tabele mogu se prikazati na kartezijanskoj ravni u obliku tačaka M 1 (x 1, y 1), ... M n (x n, y n). Sada će se rješenje problema svesti na izbor aproksimirajuće funkcije y = f (x), koja ima graf koji prolazi što bliže tačkama M 1, M 2, .. M n.

Naravno, možete koristiti polinom visokog stupnja, ali ova opcija nije samo teška za implementaciju, već je i jednostavno netočna, jer neće odražavati glavni trend koji treba otkriti. Najrazumnije rješenje je traženje prave linije y = ax + b, koja najbolje aproksimira eksperimentalne podatke, tačnije, koeficijente a i b.

Procjena tačnosti

Uz bilo kakvu aproksimaciju, procjena njegove tačnosti je od posebne važnosti. Označimo sa e i razliku (odstupanje) između funkcionalne i eksperimentalne vrijednosti za tačku x i, tj. e i = y i - f (x i).

Očigledno, da biste procijenili tačnost aproksimacije, možete koristiti zbir odstupanja, odnosno, kada birate pravu liniju za približni prikaz zavisnosti X od Y, treba dati prednost onoj s najmanjom vrijednošću zbir e i u svim tačkama koje se razmatraju. Međutim, nije sve tako jednostavno, jer će uz pozitivne devijacije biti i negativnih.

Problem se može riješiti korištenjem modula odstupanja ili njihovih kvadrata. Posljednja metoda je najčešće korištena. Koristi se u mnogim oblastima, uključujući regresijsku analizu (implementirana u Excelu pomoću dvije ugrađene funkcije), i odavno je dokazala svoju učinkovitost.

Metoda najmanjeg kvadrata

Excel, kao što znate, ima ugrađenu funkciju AutoSum koja vam omogućava da izračunate vrijednosti svih vrijednosti koje se nalaze u odabranom rasponu. Dakle, ništa nas neće spriječiti da izračunamo vrijednost izraza (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

U matematičkoj notaciji ovo izgleda ovako:

Pošto je prvobitno donesena odluka da se aproksimira pomoću prave linije, imamo:

Dakle, zadatak pronalaženja prave linije koja najbolje opisuje specifičnu zavisnost veličina X i Y svodi se na izračunavanje minimuma funkcije dvije varijable:

Da biste to učinili, morate parcijalne derivacije u odnosu na nove varijable a i b izjednačiti sa nulom, i riješiti primitivni sistem koji se sastoji od dvije jednadžbe sa 2 nepoznate forme:

Nakon nekoliko jednostavnih transformacija, uključujući dijeljenje sa 2 i manipulaciju suma, dobijamo:

Rješavajući ga, na primjer, Cramerovom metodom, dobijamo stacionarnu tačku sa određenim koeficijentima a * i b *. Ovo je minimum, tj. da se predvidi koliki će promet trgovina imati za određeno područje, prikladna je prava linija y = a * x + b *, koja je regresijski model za predmetni primjer. Naravno, to vam neće omogućiti da pronađete točan rezultat, ali će vam pomoći da steknete ideju o tome hoće li se kupovina određenog područja na kredit u trgovini isplatiti.

Kako implementirati najmanje kvadrate u Excelu

Excel ima funkciju za izračunavanje vrijednosti pomoću najmanjih kvadrata. Ima sljedeći oblik: “TREND” (poznate Y vrijednosti; poznate X vrijednosti; nove X vrijednosti; konstanta). Primijenimo formulu za izračunavanje OLS-a u Excelu na našu tablicu.

Da biste to učinili, unesite znak “=” u ćeliju u kojoj bi trebao biti prikazan rezultat izračuna primjenom metode najmanjih kvadrata u Excelu i odaberite funkciju “TREND”. U prozoru koji se otvori popunite odgovarajuća polja, naglašavajući:

• raspon poznatih vrijednosti za Y (u ovom slučaju podaci za trgovinski promet);
• raspon x 1 , …x n , odnosno veličina maloprodajnog prostora;
• i poznate i nepoznate vrijednosti x, za koje morate saznati veličinu prometa (za informacije o njihovoj lokaciji na radnom listu, pogledajte dolje).

Dodatno, formula sadrži logičku varijablu “Const”. Ako u odgovarajuće polje unesete 1, to će značiti da trebate izvršiti proračune, pod pretpostavkom da je b = 0.

Ako trebate saznati prognozu za više od jedne vrijednosti x, onda nakon unosa formule ne biste trebali pritisnuti "Enter", već morate upisati kombinaciju "Shift" + "Control" + "Enter" na tastaturi.

Neke karakteristike

Regresiona analiza može biti dostupna čak i lutkama. Excel formulu za predviđanje vrijednosti niza nepoznatih varijabli – TREND – mogu koristiti čak i oni koji nikada nisu čuli za najmanje kvadrate. Dovoljno je samo znati neke od karakteristika njegovog rada. posebno:

• Ako raspoređujete raspon poznatih vrijednosti varijable y u jednom redu ili stupcu, tada će svaki red (kolona) s poznatim vrijednostima x program percipirati kao zasebnu varijablu.
• Ako raspon sa poznatim x nije naveden u prozoru TREND, tada će ga, kada se koristi funkcija u Excelu, program tretirati kao niz koji se sastoji od cijelih brojeva, čiji broj odgovara rasponu sa datim vrijednostima varijabla y.
• Za izlaz niza "predviđenih" vrijednosti, izraz za izračunavanje trenda se mora unijeti kao formula niza.
• Ako nove vrijednosti x nisu specificirane, funkcija TREND ih smatra jednakim poznatim. Ako nisu specificirani, tada se niz 1 uzima kao argument; 2; 3; 4;…, što je srazmerno opsegu sa već navedenim parametrima y.
• Raspon koji sadrži nove vrijednosti x mora imati iste ili više redova ili stupaca kao raspon koji sadrži date vrijednosti y. Drugim riječima, mora biti proporcionalan nezavisnim varijablama.
• Niz sa poznatim x vrijednostima može sadržavati više varijabli. Međutim, ako govorimo samo o jednom, onda je potrebno da opsezi sa datim vrijednostima x i y budu proporcionalni. U slučaju više varijabli, potrebno je da raspon sa datim y vrijednostima stane u jednu kolonu ili jedan red.

Funkcija PREDICTION

Implementirano korištenjem nekoliko funkcija. Jedna od njih se zove “PREDIKCIJA”. Sličan je "TREND", tj. daje rezultat proračuna metodom najmanjih kvadrata. Međutim, samo za jedan X, za koji je vrijednost Y nepoznata.

Sada znate formule u Excelu za lutke koje vam omogućavaju da predvidite buduću vrijednost određenog indikatora prema linearnom trendu.