Metoda najmanjih kvadrata za određivanje koeficijenata. Aproksimacija eksperimentalnih podataka

Stol 4

x n

y n

Stol 5

y i

0,705

0,495

0,426

0,357

0,368

0,406

0,549

0,768

Tablica 6

№0

x

0,1

0,2

0,3

0,4

0,5

0,6

0,7

0,8

g

3,030

3,142

3,358

3,463

3,772

3,251

3,170

3,665

№ 1

3,314

3,278

3,262

3,292

3,332

3,397

3,487

3,563

№ 2

1,045

1,162

1,264

1,172

1,070

0,898

0,656

0,344

№ 3

6,715

6,735

6,750

6,741

6,645

6,639

6,647

6,612

№ 4

2,325

2,515

2,638

2,700

2,696

2,626

2,491

2,291

№ 5

1.752

1,762

1,777

1,797

1,821

1,850

1,884

1,944

№ 6

1,924

1,710

1,525

1,370

1,264

1,190

1,148

1,127

№ 7

1,025

1,144

1,336

1,419

1,479

1,530

1,568

1,248

№ 8

5,785

5,685

5,605

5,545

5,505

5,480

5,495

5,510

№ 9

4,052

4,092

4,152

4,234

4,338

4,468

4,599

Primjer.

Eksperimentalni podaci o vrijednostima varijabli x I na dati su u tablici.

Kao rezultat njihovog poravnanja dobiva se funkcija

Korištenje metoda najmanjih kvadrata, te podatke aproksimirajte linearnom ovisnošću y=ax+b(pronađi parametre A I b). Utvrdite koja od dvije linije bolje (u smislu metode najmanjih kvadrata) usklađuje eksperimentalne podatke. Napravite crtež.

Suština metode najmanjih kvadrata (LSM).

Zadatak je pronaći koeficijente linearne ovisnosti pri kojima je funkcija dviju varijabli A I b uzima najmanju vrijednost. Odnosno dano A I b zbroj kvadrata odstupanja eksperimentalnih podataka od nađene ravne linije bit će najmanji. Ovo je cijela poanta metode najmanjih kvadrata.

Dakle, rješavanje primjera se svodi na pronalaženje ekstrema funkcije dviju varijabli.

Izvođenje formula za određivanje koeficijenata.

Sastavlja se i rješava sustav dviju jednadžbi s dvije nepoznanice. Određivanje parcijalnih derivacija funkcije po varijablama A I b, te izvodnice izjednačujemo s nulom.

Dobiveni sustav jednadžbi rješavamo bilo kojom metodom (npr metodom supstitucije ili Cramerova metoda) i dobiti formule za pronalaženje koeficijenata metodom najmanjih kvadrata (LSM).

S obzirom A I b funkcija uzima najmanju vrijednost. Dokaz ove činjenice je dan ispod u tekstu na kraju stranice.

To je cijela metoda najmanjih kvadrata. Formula za pronalaženje parametra a sadrži zbrojeve ,,, i parametar n- količina eksperimentalnih podataka. Preporučujemo da se vrijednosti ovih iznosa izračunaju zasebno. Koeficijent b pronađeno nakon proračuna a.

Vrijeme je da se prisjetimo izvornog primjera.

Riješenje.

U našem primjeru n=5. Ispunjavamo tablicu radi lakšeg izračunavanja iznosa koji su uključeni u formule potrebnih koeficijenata.

Vrijednosti u četvrtom retku tablice dobivene su množenjem vrijednosti 2. retka s vrijednostima 3. retka za svaki broj. ja.

Vrijednosti u petom redu tablice dobivene su kvadriranjem vrijednosti u 2. redu za svaki broj ja.

Vrijednosti u posljednjem stupcu tablice su zbrojevi vrijednosti u redovima.

Za pronalaženje koeficijenata koristimo se formulama metode najmanjih kvadrata A I b. Zamjenjujemo odgovarajuće vrijednosti iz posljednjeg stupca tablice u njih:

Stoga, y = 0,165x+2,184- željena aproksimativna ravna linija.

Ostaje otkriti koji od redaka y = 0,165x+2,184 ili bolje aproksimira izvorne podatke, odnosno daje procjenu metodom najmanjih kvadrata.

Procjena pogreške metode najmanjih kvadrata.

Da biste to učinili, morate izračunati zbroj kvadrata odstupanja izvornih podataka od ovih redaka I , manja vrijednost odgovara liniji koja bolje aproksimira izvorne podatke u smislu metode najmanjih kvadrata.

Od , zatim ravno y = 0,165x+2,184 bolje približava izvorne podatke.

Grafički prikaz metode najmanjih kvadrata (LS).

Na grafikonima se sve jasno vidi. Crvena linija je pronađena ravna linija y = 0,165x+2,184, plava linija je , ružičaste točkice su izvorni podaci.

U praksi, pri modeliranju različitih procesa - posebno ekonomskih, fizičkih, tehničkih, društvenih - naširoko se koristi jedna ili druga metoda izračunavanja približnih vrijednosti funkcija iz njihovih poznatih vrijednosti na određenim fiksnim točkama.

Ova vrsta problema aproksimacije funkcije često se javlja:

pri izradi približnih formula za izračunavanje vrijednosti karakterističnih veličina procesa koji se proučava pomoću tabličnih podataka dobivenih kao rezultat eksperimenta;

u numeričkoj integraciji, diferencijaciji, rješavanju diferencijalnih jednadžbi itd.;

ako je potrebno, izračunajte vrijednosti funkcija u srednjim točkama razmatranog intervala;

pri određivanju vrijednosti karakterističnih veličina procesa izvan razmatranog intervala, posebice pri predviđanju.

Ako za modeliranje određenog procesa određenog tablicom konstruiramo funkciju koja približno opisuje taj proces na temelju metode najmanjih kvadrata, nazvat ćemo je aproksimirajuća funkcija (regresija), a sam problem konstruiranja aproksimirajućih funkcija nazvat ćemo problem aproksimacije.

U ovom se članku govori o mogućnostima MS Excel paketa za rješavanje ove vrste problema, osim toga, daje metode i tehnike za konstruiranje (kreiranje) regresija za tablične funkcije (što je temelj regresijske analize).

Excel ima dvije mogućnosti za izradu regresija.

Dodavanje odabranih regresija (trendova) dijagramu izgrađenom na temelju tablice podataka za karakteristiku procesa koja se proučava (dostupno samo ako je dijagram konstruiran);

Korištenje ugrađenih statističkih funkcija Excel radnog lista, koje vam omogućuju dobivanje regresija (linija trenda) izravno iz izvorne tablice podataka.

Dodavanje linija trenda na grafikon

Za tablicu podataka koja opisuje proces i predstavljena je dijagramom, Excel ima učinkovit alat za regresijsku analizu koji vam omogućuje da:

graditi na temelju metode najmanjih kvadrata i dodati pet tipova regresija dijagramu, koji modeliraju proces koji se proučava s različitim stupnjevima točnosti;

dodati konstruiranu regresijsku jednadžbu u dijagram;

odrediti stupanj podudarnosti odabrane regresije s podacima prikazanim na grafikonu.

Na temelju podataka grafikona, Excel vam omogućuje dobivanje linearnih, polinomskih, logaritamskih, potencijskih, eksponencijalnih vrsta regresija, koje su navedene jednadžbom:

y = y(x)

gdje je x nezavisna varijabla koja često uzima vrijednosti niza prirodnih brojeva (1; 2; 3; ...) i proizvodi, na primjer, odbrojavanje vremena procesa koji se proučava (karakteristike).

1 . Linearna regresija je dobra za modeliranje karakteristika čije vrijednosti rastu ili opadaju konstantnom brzinom. Ovo je najjednostavniji model za konstrukciju procesa koji se proučava. Konstruiran je u skladu s jednadžbom:

y = mx + b

gdje je m tangens nagiba linearne regresije na x-os; b - koordinata točke presjeka linearne regresije s osi ordinata.

2 . Polinomna linija trenda korisna je za opisivanje karakteristika koje imaju nekoliko različitih ekstrema (maksimuma i minimuma). Izbor stupnja polinoma određen je brojem ekstrema karakteristike koja se proučava. Prema tome, polinom drugog stupnja može dobro opisati proces koji ima samo jedan maksimum ili minimum; polinom trećeg stupnja - ne više od dva ekstrema; polinom četvrtog stupnja - ne više od tri ekstrema itd.

U ovom slučaju, linija trenda konstruirana je u skladu s jednadžbom:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

gdje su koeficijenti c0, c1, c2,... c6 konstante čije se vrijednosti određuju tijekom konstrukcije.

3 . Logaritamska linija trenda uspješno se koristi pri modeliranju karakteristika čije se vrijednosti u početku brzo mijenjaju, a zatim se postupno stabiliziraju.

y = c ln(x) + b

4 . Linija trenda zakona snage daje dobre rezultate ako vrijednosti proučavanog odnosa karakteriziraju stalne promjene u stopi rasta. Primjer takve ovisnosti je graf jednoliko ubrzanog gibanja automobila. Ako u podacima postoje nulte ili negativne vrijednosti, ne možete koristiti liniju trenda snage.

Konstruirano u skladu s jednadžbom:

y = c xb

gdje su koeficijenti b, c konstante.

5 . Eksponencijalnu liniju trenda treba koristiti kada se stopa promjene u podacima kontinuirano povećava. Za podatke koji sadrže nulte ili negativne vrijednosti, ova vrsta aproksimacije također nije primjenjiva.

Konstruirano u skladu s jednadžbom:

y = c ebx

gdje su koeficijenti b, c konstante.

Prilikom odabira linije trenda, Excel automatski izračunava vrijednost R2, koja karakterizira pouzdanost aproksimacije: što je vrijednost R2 bliža jedinici, to linija trenda pouzdanije aproksimira proces koji se proučava. Ako je potrebno, vrijednost R2 uvijek se može prikazati na grafikonu.

Određeno formulom:

Da biste nizu podataka dodali liniju trenda:

aktivirajte grafikon na temelju niza podataka, tj. kliknite unutar područja grafikona. U glavnom izborniku pojavit će se stavka Dijagram;

nakon klika na ovu stavku na ekranu će se pojaviti izbornik u kojem treba odabrati naredbu Dodaj liniju trenda.

Iste radnje mogu se jednostavno provesti pomicanjem pokazivača miša preko grafikona koji odgovara jednoj od serija podataka i desnim klikom; U kontekstnom izborniku koji se pojavi odaberite naredbu Dodaj liniju trenda. Na zaslonu će se pojaviti dijaloški okvir Trendline s otvorenom karticom Vrsta (slika 1).

Nakon ovoga trebate:

Odaberite potrebnu vrstu linije trenda na kartici Vrsta (vrsta Linearna odabrana je prema zadanim postavkama). Za tip Polinom u polju Stupanj odredite stupanj odabranog polinoma.

1 . Polje Izgrađeno na seriji navodi sve serije podataka u dotičnom grafikonu. Da biste dodali liniju trenda određenoj seriji podataka, odaberite njezin naziv u polju Izgrađeno na seriji.

Ako je potrebno, odlaskom na karticu Parametri (slika 2) možete postaviti sljedeće parametre za liniju trenda:

promijenite naziv linije trenda u polju Naziv aproksimirajuće (izglađene) krivulje.

postavite broj razdoblja (unaprijed ili unatrag) za prognozu u polju Prognoza;

prikazati jednadžbu linije trenda u području dijagrama, za što trebate omogućiti potvrdni okvir Prikaži jednadžbu na dijagramu;

prikazati vrijednost pouzdanosti aproksimacije R2 u području dijagrama, za što treba uključiti potvrdni okvir Postavi vrijednost pouzdanosti aproksimacije na dijagram (R^2);

postavite točku sjecišta linije trenda s osi Y, za koju treba uključiti potvrdni okvir za sjecište krivulje s osi Y u točki;

Pritisnite gumb U redu za zatvaranje dijaloškog okvira.

Kako biste počeli uređivati ​​već iscrtanu liniju trenda, postoje tri načina:

koristite naredbu Selected trend line iz izbornika Format, nakon što ste prethodno odabrali trend trend;

odaberite naredbu Format trend line iz kontekstnog izbornika koji se poziva desnim klikom na liniju trenda;

dvaput kliknite na liniju trenda.

Na ekranu će se pojaviti dijaloški okvir Trend Line Format (Slika 3), koji sadrži tri kartice: View, Type, Parameters, a sadržaj posljednja dva potpuno se podudara sa sličnim karticama dijaloškog okvira Trend Line (Slika 1). -2). Na kartici Pogled možete postaviti vrstu linije, njenu boju i debljinu.

Za brisanje linije trenda koja je već nacrtana, odaberite liniju trenda koju želite izbrisati i pritisnite tipku Delete.

Prednosti razmatranog alata za regresijsku analizu su:

relativna jednostavnost konstruiranja linije trenda na grafikonima bez stvaranja podatkovne tablice za nju;

prilično širok popis vrsta predloženih linija trenda, a ovaj popis uključuje najčešće korištene vrste regresije;

sposobnost predviđanja ponašanja procesa koji se proučava proizvoljnim (u granicama zdravog razuma) brojem koraka naprijed i unatrag;

sposobnost dobivanja jednadžbe linije trenda u analitičkom obliku;

mogućnost, ako je potrebno, dobivanja ocjene pouzdanosti aproksimacije.

Nedostaci uključuju sljedeće:

izgradnja linije trenda provodi se samo ako postoji dijagram izgrađen na nizu podataka;

proces generiranja serije podataka za karakteristiku koja se proučava na temelju jednadžbi linije trenda dobivenih za nju je donekle pretrpan: potrebne regresijske jednadžbe ažuriraju se sa svakom promjenom vrijednosti izvorne serije podataka, ali samo unutar područja grafikona , dok serija podataka formirana na temelju stare jednadžbe trenda ostaje nepromijenjena;

U izvješćima zaokretnog grafikona promjena prikaza grafikona ili pridruženog izvješća zaokretne tablice ne čuva postojeće crte trenda, što znači da prije crtanja linija trenda ili na drugi način formatirate izvješće zaokretnog grafikona, trebali biste provjeriti ispunjava li izgled izvješća potrebne zahtjeve.

Linije trenda mogu se koristiti za dopunu nizova podataka prikazanih na grafikonima kao što su grafikon, histogram, ravni nestandardizirani površinski grafikoni, stupčasti grafikoni, raspršeni grafikoni, mjehurasti grafikoni i burzovni grafikoni.

Ne možete dodavati linije trenda nizovima podataka u 3D, normaliziranim, radarskim, kružnim i kružnim grafikonima.

Korištenje ugrađenih funkcija programa Excel

Excel također ima alat za regresijsku analizu za iscrtavanje linija trenda izvan područja grafikona. Postoji niz funkcija statističkih radnih listova koje možete koristiti u tu svrhu, ali sve vam one omogućuju samo izradu linearnih ili eksponencijalnih regresija.

Excel ima nekoliko funkcija za izradu linearne regresije, posebno:

TREND;

• KOSINA i USJEK.

Kao i nekoliko funkcija za konstruiranje eksponencijalne linije trenda, posebno:

LGRFPRIBL.

Treba napomenuti da su tehnike za konstruiranje regresija pomoću funkcija TREND i GROWTH gotovo iste. Isto se može reći i za par funkcija LINEST i LGRFPRIBL. Za ove četiri funkcije, stvaranje tablice vrijednosti koristi značajke programa Excel kao što su formule polja, što donekle otežava proces izgradnje regresija. Napominjemo također da se konstrukcija linearne regresije, po našem mišljenju, najlakše postiže korištenjem funkcija SLOPE i INTERCEPT, gdje prva od njih određuje nagib linearne regresije, a druga određuje segment presječen regresijom na y -os.

Prednosti alata ugrađenih funkcija za regresijsku analizu su:

prilično jednostavan, ujednačen proces generiranja serije podataka karakteristike koja se proučava za sve ugrađene statističke funkcije koje definiraju linije trenda;

standardna metodologija za konstruiranje linija trenda na temelju generiranih serija podataka;

sposobnost predviđanja ponašanja procesa koji se proučava potrebnim brojem koraka naprijed ili natrag.

Nedostaci uključuju činjenicu da Excel nema ugrađene funkcije za stvaranje drugih (osim linearnih i eksponencijalnih) vrsta linija trenda. Ova okolnost često ne dopušta odabir dovoljno preciznog modela procesa koji se proučava, kao i dobivanje prognoza koje su bliske stvarnosti. Osim toga, kada se koriste funkcije TREND i GROWTH, jednadžbe linija trenda nisu poznate.

Treba napomenuti da autori nisu imali za cilj cjelovito prikazati tijek regresijske analize. Njegova glavna zadaća je na konkretnim primjerima pokazati mogućnosti Excel paketa pri rješavanju aproksimacijskih problema; pokazati koje učinkovite alate Excel ima za izradu regresija i predviđanja; ilustriraju kako takve probleme relativno lako može riješiti čak i korisnik koji nema opsežno znanje o regresijskoj analizi.

Primjeri rješavanja konkretnih problema

Pogledajmo rješavanje konkretnih problema pomoću navedenih Excel alata.

Problem 1

Uz tablicu podataka o dobiti autotransportnog poduzeća za 1995.-2002. morate učiniti sljedeće:

Izgradite dijagram.

Dodajte linearne i polinomske (kvadratne i kubične) linije trenda na grafikon.

Pomoću jednadžbi linije trenda dobiti tablične podatke o dobiti poduzeća za svaku liniju trenda za 1995.-2004.

Napravite prognozu dobiti poduzeća za 2003. i 2004. godinu.

Rješenje problema

U raspon ćelija A4:C11 radnog lista programa Excel unesite radni list prikazan na sl. 4.

Odabravši raspon ćelija B4:C11, gradimo dijagram.

Aktiviramo konstruirani dijagram i, prema gore opisanoj metodi, nakon odabira vrste linije trenda u dijaloškom okviru Trend Line (vidi sl. 1), dijagramu naizmjenično dodajemo linearne, kvadratne i kubične linije trenda. U istom dijaloškom okviru otvorite karticu Parametri (vidi sl. 2), u polje Naziv aproksimirajuće (izglađene) krivulje unesite naziv trenda koji se dodaje, au polje Prognoza naprijed za: razdoblja postavite vrijednost 2, jer se planira napraviti prognoza dobiti za dvije godine unaprijed. Za prikaz jednadžbe regresije i vrijednosti pouzdanosti aproksimacije R2 u području dijagrama, omogućite potvrdne okvire za prikaz jednadžbe na zaslonu i postavite vrijednost pouzdanosti aproksimacije (R^2) na dijagram. Za bolju vizualnu percepciju mijenjamo vrstu, boju i debljinu konstruiranih linija trenda, za što koristimo karticu View dijaloškog okvira Format linije trenda (vidi sl. 3). Rezultirajući dijagram s dodanim linijama trenda prikazan je na sl. 5.

Za dobivanje tabličnih podataka o dobiti poduzeća za svaku liniju trenda za 1995.-2004. Upotrijebimo jednadžbe linije trenda prikazane na sl. 5. Da biste to učinili, u ćelije raspona D3:F3 unesite tekstualne informacije o vrsti odabrane linije trenda: Linearni trend, Kvadratni trend, Kubični trend. Zatim unesite formulu linearne regresije u ćeliju D4 i pomoću markera za popunjavanje kopirajte ovu formulu s relativnim referencama na raspon ćelija D5:D13. Treba napomenuti da svaka ćelija s formulom linearne regresije iz raspona ćelija D4:D13 ima kao argument odgovarajuću ćeliju iz raspona A4:A13. Slično, za kvadratnu regresiju ispunite raspon ćelija E4:E13, a za kubičnu regresiju ispunite raspon ćelija F4:F13. Tako je sastavljena prognoza dobiti poduzeća za 2003. i 2004. godinu. koristeći tri trenda. Rezultirajuća tablica vrijednosti prikazana je na sl. 6.

Problem 2

Izgradite dijagram.

Grafikonu dodajte logaritamske, potencijske i eksponencijalne linije trenda.

Izvedite jednadžbe dobivenih linija trenda, kao i vrijednosti pouzdanosti aproksimacije R2 za svaku od njih.

Pomoću jednadžbi linije trenda dobiti tablične podatke o dobiti poduzeća za svaku liniju trenda za 1995.-2002.

Napravite prognozu dobiti poduzeća za 2003. i 2004. koristeći ove linije trenda.

Rješenje problema

Slijedeći metodologiju danu u rješavanju problema 1, dobivamo dijagram s dodanim logaritamskim, potencijskim i eksponencijalnim linijama trenda (slika 7). Zatim, koristeći dobivene jednadžbe linije trenda, ispunjavamo tablicu vrijednosti za dobit poduzeća, uključujući predviđene vrijednosti za 2003. i 2004. godinu. (slika 8).

Na sl. 5 i sl. vidljivo je da model s logaritamskim trendom odgovara najnižoj vrijednosti aproksimacijske pouzdanosti

R2 = 0,8659

Najveće vrijednosti R2 odgovaraju modelima s polinomskim trendom: kvadratni (R2 = 0,9263) i kubni (R2 = 0,933).

Problem 3

Uz tablicu podataka o dobiti autoprijevoznog poduzeća za 1995.-2002., danu u zadatku 1, morate izvršiti sljedeće korake.

Dobijte serije podataka za linearne i eksponencijalne linije trenda pomoću funkcija TREND i GROW.

Pomoću funkcija TREND i RAST napravite prognozu dobiti poduzeća za 2003. i 2004. godinu.

Konstruirajte dijagram za izvorne podatke i rezultirajuće serije podataka.

Rješenje problema

Upotrijebimo radni list za problem 1 (vidi sliku 4). Počnimo s funkcijom TREND:

odaberite raspon ćelija D4: D11, koji treba ispuniti vrijednostima funkcije TREND koje odgovaraju poznatim podacima o dobiti poduzeća;

Pozovite naredbu Function iz izbornika Insert. U dijaloškom okviru čarobnjaka za funkcije koji se pojavi odaberite funkciju TREND iz kategorije Statistika, a zatim kliknite gumb U redu. Ista se operacija može izvesti klikom na gumb (Umetni funkciju) na standardnoj alatnoj traci.

U dijaloškom okviru Argumenti funkcije koji se pojavi unesite raspon ćelija C4:C11 u polje Known_values_y; u polju Poznate_vrijednosti_x - raspon ćelija B4:B11;

Kako bi unesena formula postala formula polja, upotrijebite kombinaciju tipki + + .

Formula koju smo unijeli u traku formule izgledat će ovako: =(TREND(C4:C11,B4:B11)).

Kao rezultat toga, raspon ćelija D4:D11 ispunjen je odgovarajućim vrijednostima funkcije TREND (slika 9).

Izraditi prognozu dobiti poduzeća za 2003. i 2004. godinu. potrebno:

odaberite raspon ćelija D12:D13 gdje će se unijeti vrijednosti predviđene funkcijom TREND.

pozovite funkciju TREND i u dijaloškom okviru Function Arguments koji se pojavi unesite u polje Known_values_y - raspon ćelija C4:C11; u polju Poznate_vrijednosti_x - raspon ćelija B4:B11; a u polju Nove_vrijednosti_x - raspon ćelija B12:B13.

pretvorite ovu formulu u formulu polja pomoću kombinacije tipki Ctrl + Shift + Enter.

Unesena formula izgledat će ovako: =(TREND(C4:C11;B4:B11;B12:B13)), a raspon ćelija D12:D13 popunit će se predviđenim vrijednostima funkcije TREND (vidi sl. 9).

Niz podataka se na sličan način popunjava pomoću funkcije GROWTH, koja se koristi u analizi nelinearnih ovisnosti i radi na potpuno isti način kao i njen linearni pandan TREND.

Slika 10 prikazuje tablicu u načinu prikaza formule.

Za početne podatke i dobivene serije podataka, dijagram prikazan na Sl. jedanaest.

Problem 4

Uz tablicu podataka o primitku zahtjeva za usluge od strane dispečerske službe autoprijevoznika za razdoblje od 1. do 11. tekućeg mjeseca, morate izvršiti sljedeće radnje.

Dohvaćanje serije podataka za linearnu regresiju: ​​pomoću funkcija SLOPE i INTERCEPT; pomoću funkcije LINEST.

Pribavite niz podataka za eksponencijalnu regresiju pomoću funkcije LGRFPRIBL.

Koristeći navedene funkcije napravite prognozu zaprimanja zahtjeva u dispečersku službu za razdoblje od 12. do 14. u tekućem mjesecu.

Napravite dijagram za izvornu i primljenu seriju podataka.

Rješenje problema

Imajte na umu da, za razliku od funkcija TREND i GROWTH, nijedna od gore navedenih funkcija (SLOPE, INTERCEPT, LINEST, LGRFPRIB) nije regresija. Ove funkcije igraju samo pomoćnu ulogu, određujući potrebne regresijske parametre.

Za linearne i eksponencijalne regresije izgrađene pomoću funkcija SLOPE, INTERCEPT, LINEST, LGRFPRIB, izgled njihovih jednadžbi je uvijek poznat, za razliku od linearnih i eksponencijalnih regresija koje odgovaraju funkcijama TREND i GROWTH.

1 . Izgradimo linearnu regresiju s jednadžbom:

y = mx+b

pomoću funkcija SLOPE i INTERCEPT, pri čemu je regresijski nagib m određen funkcijom SLOPE, a slobodni član b funkcijom INTERCEPT.

Da bismo to učinili, provodimo sljedeće radnje:

unesite izvornu tablicu u raspon ćelija A4:B14;

vrijednost parametra m bit će određena u ćeliji C19. Odaberite funkciju Slope iz Statističke kategorije; unesite raspon ćelija B4:B14 u polje poznate_vrijednosti_y i raspon ćelija A4:A14 u polje poznate_vrijednosti_x. Formula će se unijeti u ćeliju C19: =NAGIB(B4:B14,A4:A14);

Sličnom tehnikom određuje se vrijednost parametra b u ćeliji D19. A njegov sadržaj će izgledati ovako: =SEGMENT(B4:B14,A4:A14). Dakle, vrijednosti parametara m i b potrebne za konstrukciju linearne regresije bit će pohranjene u ćelijama C19, odnosno D19;

Zatim unesite formulu linearne regresije u ćeliju C4 u obliku: =$C*A4+$D. U ovoj formuli ćelije C19 i D19 ispisane su apsolutnim referencama (adresa ćelije se ne smije mijenjati tijekom eventualnog kopiranja). Apsolutni referentni znak $ može se upisati s tipkovnice ili pomoću tipke F4, nakon postavljanja kursora na adresu ćelije. Koristeći ručicu za punjenje, kopirajte ovu formulu u raspon ćelija C4:C17. Dobivamo potrebne serije podataka (slika 12). Zbog činjenice da je broj zahtjeva cijeli broj, trebate postaviti format broja s brojem decimalnih mjesta na 0 na kartici Broj u prozoru Format ćelije.

2 . Izgradimo sada linearnu regresiju danu jednadžbom:

y = mx+b

pomoću funkcije LINEST.

Za ovo:

Unesite funkciju LINEST kao formulu polja u raspon ćelija C20:D20: =(LINEST(B4:B14,A4:A14)). Kao rezultat dobivamo vrijednost parametra m u ćeliji C20, a vrijednost parametra b u ćeliji D20;

unesite formulu u ćeliju D4: =$C*A4+$D;

kopirajte ovu formulu pomoću markera za popunjavanje u raspon ćelija D4:D17 i dobijete željenu seriju podataka.

3 . Gradimo eksponencijalnu regresiju pomoću jednadžbe:

pomoću funkcije LGRFPRIBL izvodi se na sličan način:

U raspon ćelija C21:D21 unosimo funkciju LGRFPRIBL kao formulu polja: =( LGRFPRIBL (B4:B14,A4:A14)). U ovom slučaju, vrijednost parametra m bit će određena u ćeliji C21, a vrijednost parametra b bit će određena u ćeliji D21;

formula se unosi u ćeliju E4: =$D*$C^A4;

korištenjem markera za popunjavanje, ova se formula kopira u raspon ćelija E4:E17, gdje će se nalaziti serija podataka za eksponencijalnu regresiju (vidi sliku 12).

Na sl. Slika 13 prikazuje tablicu u kojoj možete vidjeti funkcije koje koristimo s potrebnim rasponima ćelija, kao i formule.

Veličina R 2 nazvao koeficijent odlučnosti.

Zadatak konstruiranja regresijske ovisnosti je pronaći vektor koeficijenata m modela (1) pri kojem koeficijent R poprima najveću vrijednost.

Za procjenu značajnosti R koristi se Fisherov F test izračunat pomoću formule

Gdje n- veličina uzorka (broj pokusa);

k je broj koeficijenata modela.

Ako F prijeđe neku kritičnu vrijednost za podatke n I k i prihvaćenu vjerojatnost pouzdanosti, tada se vrijednost R smatra značajnom. Tablice kritičnih vrijednosti F dane su u referentnim knjigama o matematičkoj statistici.

Dakle, značaj R je određen ne samo njegovom vrijednošću, već i omjerom između broja eksperimenata i broja koeficijenata (parametara) modela. Doista, omjer korelacije za n=2 za jednostavan linearni model jednak je 1 (jedna ravna linija uvijek se može povući kroz 2 točke na ravnini). Međutim, ako su eksperimentalni podaci slučajne varijable, takvoj vrijednosti R treba vjerovati s velikim oprezom. Obično, za dobivanje značajnog R i pouzdane regresije, nastoje osigurati da broj eksperimenata značajno premašuje broj koeficijenata modela (n>k).

Za izradu modela linearne regresije potrebno vam je:

1) pripremite popis od n redaka i m stupaca koji sadrže eksperimentalne podatke (stupac koji sadrži izlaznu vrijednost Y mora biti prvi ili zadnji na popisu); Na primjer, uzmimo podatke iz prethodnog zadatka, dodamo stupac pod nazivom "Br. razdoblja", numeriramo brojeve razdoblja od 1 do 12. (to će biti vrijednosti x)

2) idite na izbornik Podaci/Analiza podataka/Regresija

Ako nedostaje stavka "Analiza podataka" u izborniku "Alati", trebali biste otići na stavku "Dodaci" u istom izborniku i potvrditi okvir "Paket analize".

3) u dijaloškom okviru "Regresija" postavite:

· ulazni interval Y;

· ulazni interval X;

· izlazni interval - gornja lijeva ćelija intervala u koji će se smjestiti rezultati izračuna (preporuča se smjestiti ih na novi radni list);

4) kliknite "U redu" i analizirajte rezultate.

Bit metode je da je kriterij kvalitete rješenja koji se razmatra zbroj kvadrata pogrešaka, koji se nastoji minimizirati. Da bi se to primijenilo, potrebno je provesti što više mjerenja nepoznate slučajne varijable (što ih je više, to je točnost rješenja veća) i određeni skup procijenjenih rješenja iz kojih treba odabrati najbolje. Ako je skup rješenja parametriziran, tada treba pronaći optimalnu vrijednost parametara.

Zašto su kvadratne pogreške minimizirane, a ne same pogreške? Činjenica je da u većini slučajeva pogreške idu u oba smjera: procjena može biti veća od mjerenja ili manja od nje. Ako zbrojimo pogreške s različitim predznacima, one će se međusobno poništiti, a kao rezultat toga zbroj će nam dati netočnu predodžbu o kvaliteti ocjene. Često se, kako bi konačna procjena imala istu dimenziju kao i izmjerene vrijednosti, uzima kvadratni korijen zbroja kvadrata pogrešaka.

LSM se koristi u matematici, posebice u teoriji vjerojatnosti i matematičkoj statistici. Ova metoda se najčešće koristi u problemima filtriranja, kada je potrebno odvojiti korisni signal od šuma koji je na njemu superponiran.

Također se koristi u matematičkoj analizi za aproksimaciju reprezentacije dane funkcije pomoću jednostavnijih funkcija. Drugo područje primjene najmanjih kvadrata je rješavanje sustava jednadžbi s brojem nepoznanica manjim od broja jednadžbi.

Došao sam do još nekoliko vrlo neočekivanih područja primjene MNC-a, o kojima bih želio govoriti u ovom članku.

OLS i greške pri upisu

Pošast automatskih prevoditelja i tražilica su tipfeleri i pravopisne pogreške. Doista, ako se riječ razlikuje samo za jedno slovo, program je tretira kao drugu riječ i pogrešno je prevodi/traži ili je ne prevodi/uopće ne nalazi.

Imao sam sličan problem: imao sam dvije baze podataka s adresama moskovskih kuća i trebao sam ih spojiti u jednu. Ali adrese su bile napisane različitim stilovima. Jedna baza podataka sadržavala je standard KLADR (sveruski klasifikator adresa), na primjer: "ULICA BABUŠKINA LETČIKA, D10K3." A u drugoj bazi postojao je poštanski stil, na primjer: “St. Pilota Babuškina, zgrada 10, zgrada 3.” Čini se da u oba slučaja nema pogrešaka, ali automatizacija procesa je nevjerojatno teška (svaka baza ima 40 tisuća zapisa!). Iako je bilo i dosta tipfelera... Kako natjerati računalo da shvati da 2 gornje adrese pripadaju istoj kući? Tu mi je MNC dobro došao.

Što sam učinio? Pronašavši sljedeće slovo u prvoj adresi, potražio sam isto slovo u drugoj adresi. Ako su oba bila na istom mjestu, tada sam postavio grešku za to slovo na 0. Ako su bili na susjednim pozicijama, tada je greška bila 1. Ako je došlo do pomaka za 2 mjesta, greška je bila 2 itd. Ako u drugoj adresi uopće nije bilo takvog slova, tada se pretpostavlja da je greška jednaka n+1, gdje je n broj slova u 1. adresi. Tako sam izračunao zbroj kvadrata pogrešaka i spojio one zapise u kojima je taj zbroj minimalan.

Naravno, kućni brojevi i brojevi zgrada obrađivani su odvojeno. Ne znam jesam li izmislio još jedan "bicikl", ili je to stvarno bio, ali problem je riješen brzo i učinkovito. Zanima me koristi li se ova metoda u tražilicama? Možda vrijedi jer svaka tražilica koja drži do sebe, kada naiđe na nepoznatu riječ, nudi zamjenu od poznatih riječi ("možda ste mislili ..."). Međutim, ovu analizu mogu napraviti na neki drugi način.

OLS i pretraživanje po slikama, licima i kartama

Ova se metoda također može koristiti za pretraživanje pomoću slika, crteža, karata, pa čak i lica ljudi.

Fotografija:

Sada sve tražilice, umjesto pretrage po slikama, u osnovi koriste pretragu po opisima slika. Ovo je bez sumnje korisna i praktična usluga, ali predlažem da je nadopunite pravim pretraživanjem slika.

Unosi se ogledna slika i sastavlja se ocjena za sve slike na temelju zbroja kvadrata odstupanja karakterističnih točaka. Određivanje tih najkarakterističnijih točaka samo po sebi nije trivijalan zadatak. No, to je potpuno rješivo: primjerice, za lica to su kutovi očiju, usne, vrh nosa, nosnice, rubovi i središta obrva, zjenice itd.

Uspoređujući ove parametre, možete pronaći lice koje je najsličnije uzorku. Već sam vidio stranice na kojima ova usluga radi, a možete pronaći slavnu osobu koja je najsličnija fotografiji koju predložite, pa čak i napraviti animaciju koja vas pretvara u slavnu osobu i natrag. Zasigurno ista metoda funkcionira u bazama podataka Ministarstva unutarnjih poslova koje sadrže fotografije kriminalaca.

Fotografija: pixabay.com

Da, i možete pretraživati ​​koristeći otiske prstiju koristeći istu metodu. Pretraživanje na kartama usmjereno je na prirodne nepravilnosti geografskih objekata - zavoje rijeka, planinske lance, obrise obala, šume i polja.

Ovo je tako divna i univerzalna metoda najmanjih kvadrata. Siguran sam da ćete vi, dragi čitatelji, sami moći pronaći mnoga neobična i neočekivana područja primjene ove metode.

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

Prikaz problema na konkretnom primjeru

Pretpostavimo da postoje dva indikatora X i Y. Štoviše, Y ovisi o X. Budući da nas OLS zanima sa stajališta regresijske analize (u Excelu su njegove metode implementirane pomoću ugrađenih funkcija), trebali bismo odmah prijeći na razmatranje konkretan problem.

Dakle, neka X bude maloprodajni prostor trgovine mješovitom robom, mjeren u kvadratnim metrima, a Y godišnji promet, mjeren u milijunima rubalja.

Potrebno je napraviti prognozu koliki će promet (Y) prodavaonica imati ako bude imala ovaj ili onaj prodajni prostor. Očito je da funkcija Y = f (X) raste, jer hipermarket prodaje više robe nego štand.

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

Recimo da imamo tablicu izgrađenu pomoću podataka za n trgovina.

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

Suština metode

Podaci iz tablice mogu se prikazati na kartezijanskoj ravnini u obliku toč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 je moguće bliže točkama M 1, M 2, .. M n.

Naravno, možete koristiti polinom visokog stupnja, ali ovu opciju nije samo teško implementirati, već je i jednostavno netočna, jer neće odražavati glavni trend koji treba otkriti. Najrazumnije rješenje je tražiti ravnu liniju y = ax + b, koja najbolje aproksimira eksperimentalne podatke, točnije koeficijente a i b.

Procjena točnosti

Kod svake aproksimacije, procjena njene točnosti je od posebne važnosti. Označimo s e i razliku (odstupanje) između funkcionalne i eksperimentalne vrijednosti za točku x i, tj. e i = y i - f (x i).

Očito, da biste procijenili točnost aproksimacije, možete koristiti zbroj odstupanja, tj. kada birate ravnu liniju za aproksimativni prikaz ovisnosti X o Y, trebali biste dati prednost onoj s najmanjom vrijednošću sum e i u svim točkama koje se razmatraju. No, nije sve tako jednostavno, jer će uz pozitivna odstupanja biti i negativna.

Problem se može riješiti pomoću modula odstupanja ili njihovih kvadrata. Posljednja metoda je najčešće korištena. Koristi se u mnogim područjima, 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ćuje izračunavanje 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čkom zapisu to izgleda ovako:

Budući da je prvotno donesena odluka da se aproksimacija koristi ravnom linijom, imamo:

Dakle, zadatak pronalaska ravne linije koja najbolje opisuje specifičnu ovisnost veličina X i Y svodi se na izračunavanje minimuma funkcije dviju varijabli:

Da biste to učinili, trebate izjednačiti parcijalne derivacije u odnosu na nove varijable a i b na nulu i riješiti primitivni sustav koji se sastoji od dvije jednadžbe s 2 nepoznanice oblika:

Nakon nekoliko jednostavnih transformacija, uključujući dijeljenje s 2 i manipulaciju zbrojevima, dobivamo:

Rješavajući ga, na primjer, pomoću Cramerove metode, dobivamo stacionarnu točku s određenim koeficijentima a * i b *. To je minimum, odnosno za predviđanje koliki će promet prodavaonica imati za određeno područje pogodna je pravac 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 kupnja određenog područja na kredit u trgovini isplatiti.

Kako implementirati metode najmanjih kvadrata u Excelu

Excel ima funkciju za izračunavanje vrijednosti korištenjem 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 metodom najmanjih kvadrata u Excelu i odaberite funkciju “TREND”. U prozoru koji se otvori ispunite odgovarajuća polja, označivši:

• raspon poznatih vrijednosti za Y (u ovom slučaju podaci o trgovačkom prometu);
• raspon x 1 , …x n , tj. veličina prodajnog prostora;
• poznate i nepoznate vrijednosti x, za koje morate saznati veličinu prometa (za informacije o njihovom položaju na radnom listu, pogledajte dolje).

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

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

Neke značajke

Regresijska analiza može biti dostupna čak i lutkama. Excelovu formulu za predviđanje vrijednosti niza nepoznatih varijabli—TREND—mogu koristiti čak i oni koji nikada nisu čuli za metode najmanjih kvadrata. Dovoljno je samo znati neke od značajki njegovog rada. Posebno:

• Ako rasporedite raspon poznatih vrijednosti varijable y u jedan redak ili stupac, tada će svaki redak (stupac) s poznatim vrijednostima x program percipirati kao zasebnu varijablu.
• Ako raspon s poznatim x nije naveden u prozoru TREND, tada će ga, kada koristite funkciju u Excelu, program tretirati kao polje koje se sastoji od cijelih brojeva, čiji broj odgovara rasponu s danim vrijednostima varijabla y.
• Za izlaz niza "predviđenih" vrijednosti, izraz za izračun trenda mora se unijeti kao formula polja.
• Ako nove vrijednosti x nisu navedene, tada ih funkcija TREND smatra jednakima poznatima. Ako nisu navedeni, tada se niz 1 uzima kao argument; 2; 3; 4;…, što je razmjerno rasponu s već navedenim parametrima y.
• Raspon koji sadrži nove x vrijednosti mora imati iste ili više redaka ili stupaca kao raspon koji sadrži dane y vrijednosti. Drugim riječima, mora biti proporcionalan nezavisnim varijablama.
• Niz s poznatim vrijednostima x može sadržavati više varijabli. Međutim, ako govorimo samo o jednom, tada je potrebno da rasponi sa zadanim vrijednostima x i y budu proporcionalni. U slučaju više varijabli, potrebno je da raspon sa zadanim vrijednostima y stane u jedan stupac ili jedan red.

PREDICTION funkcija

Implementirano pomoću nekoliko funkcija. Jedan od njih se zove “PREDIKCIJA”. Slično je "TRENDU", tj. daje rezultat izrač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ćuju predviđanje buduće vrijednosti određenog pokazatelja prema linearnom trendu.

Metoda najmanjih kvadrata je matematički postupak za konstruiranje linearne jednadžbe koja najbolje odgovara skupu uređenih parova pronalaženjem vrijednosti za a i b, koeficijenata u jednadžbi pravca. Cilj najmanjih kvadrata je minimizirati ukupnu kvadratnu pogrešku između vrijednosti y i ŷ. Ako za svaku točku odredimo pogrešku ŷ, metoda najmanjih kvadrata minimizira:

gdje je n = broj uređenih parova oko pravca. što bliže podacima.

Ovaj koncept je ilustriran na slici

Na temelju slike, linija koja najbolje odgovara podacima, regresijska linija, smanjuje ukupnu kvadratnu pogrešku četiri točke na grafikonu. Pokazat ću vam kako to odrediti pomoću najmanjih kvadrata sa sljedećim primjerom.

Zamislite mladi par koji je nedavno uselio zajedno i dijeli toaletni stolić u kupaonici. Mladić je počeo primjećivati ​​da se polovica njegovog stola neumoljivo smanjuje, gubeći tlo pred pjenama za kosu i sojinim kompleksima. Tijekom proteklih nekoliko mjeseci tip je pomno pratio brzinu kojom se povećavao broj predmeta na njezinoj strani stola. Donja tablica prikazuje broj predmeta koje je djevojka nakupila na svom toaletnom ormariću u kupaonici u posljednjih nekoliko mjeseci.

Budući da je naš cilj saznati povećava li se broj stavki tijekom vremena, "Mjesec" će biti nezavisna varijabla, a "Broj stavki" će biti zavisna varijabla.

Koristeći metodu najmanjih kvadrata, određujemo jednadžbu koja najbolje odgovara podacima izračunavanjem vrijednosti a, y-odsječka, i b, nagiba linije:

a = y prosj. - bx prosj

gdje je x avg prosječna vrijednost x, nezavisne varijable, y avg je prosječna vrijednost y, nezavisne varijable.

Tablica u nastavku sažima izračune potrebne za ove jednadžbe.

Krivulja učinka za naš primjer kade bila bi dana sljedećom jednadžbom:

Budući da naša jednadžba ima pozitivan nagib od 0,976, tip ima dokaz da se broj stavki na stolu povećava tijekom vremena prosječnom stopom od 1 stavke mjesečno. Grafikon prikazuje krivulju učinka s uređenim parovima.

Očekivani broj stavki u sljedećih šest mjeseci (16. mjesec) izračunat će se na sljedeći način:

ŷ = 5,13 + 0,976x = 5,13 + 0,976(16) ~ 20,7 = 21 stavka

Dakle, vrijeme je da naš heroj nešto poduzme.

Funkcija TREND u Excelu

Kao što ste vjerojatno već pogodili, Excel ima funkciju za izračunavanje vrijednosti prema metoda najmanjih kvadrata. Ova funkcija se zove TREND. Sintaksa mu je sljedeća:

TREND (poznate vrijednosti Y; poznate vrijednosti X; nove vrijednosti X; konstanta)

poznate Y vrijednosti - niz zavisnih varijabli, u našem slučaju, broj objekata na tablici

poznate vrijednosti X – niz nezavisnih varijabli, u našem slučaju to je mjesec

nove X vrijednosti - nove X vrijednosti (mjeseci) za koje TREND funkcija vraća očekivanu vrijednost zavisnih varijabli (broj stavki)

const - izborno. Booleova vrijednost koja određuje treba li konstanta b biti 0.

Na primjer, slika prikazuje funkciju TREND koja se koristi za određivanje očekivanog broja predmeta na kupaonskom umivaoniku za 16. mjesec.