Metoda najmanjih kvadrata i pretraživanje rješenja u Excelu. Primjena metode najmanjih kvadrata u Izračun Excel po najmanju ruku XLS kvadrata

Pa, na poslu prije nego što je inspekcija izvijestio, napisan je članak kod kuće za konferenciju - sada možete pisati na blog. Sve dok se ti podaci obrađuju, shvatio sam da ne mogu pisati o vrlo cool i nužnoj nadgradnji u Excelu, koji se zove. Tako da će članak biti posvećen ovoj nadstrukturi, a ja ću vam reći o primjeru korištenja metoda najmanjih kvadrata (MNC) za pretraživanje nepoznatih koeficijenata jednadžbe prilikom opisuju eksperimentalne podatke.

Kako omogućiti dodatak "pretraživanje rješenja"

Prvo ćemo razumjeti kako uključiti ovu nadgradnju.

1. Idite na izbornik "File" i odaberite stavku Excel postavke.

2. U prozoru koji se pojavljuje, odaberite "Rješenje rješenje" i kliknite "Go".

3. U sljedećem prozoru stavljamo kvačicu nasuprot stavci "Traženje rješenja" i kliknite U redu.

4. Add-in aktiviran je - sada se može naći u stavci izbornika "Data".

Najmanje kvadratna metoda

Sada ukratko O. metoda najmanjih kvadrata (MNC) I o tome gdje se može primijeniti.

Pretpostavimo da imamo skup podataka nakon što napravimo neki eksperiment, gdje smo proučavali učinke vrijednosti vrijednosti Y.

Želimo taj učinak opisati matematički, koristiti ovu formulu za korištenje i znati da ako promijenimo količinu X na toliko toga, dobivamo vrijednost y takve ...

Uzet ću super jednostavan primjer (vidi sl.).

Jasno je da se točke nalaze jedni za druge kao u ravnoj liniji, i stoga hrabro pretpostavljamo da je naša ovisnost opisana linearnom funkcijom y \u003d kx + b. U isto vrijeme, precizno smo sigurni da je X jednak nuli, vrijednost Y je također nula. Dakle, funkcija koja opisuje ovisnost će biti još lakše: y \u003d KX (zapamtite školski program).

Općenito, moramo pronaći koeficijent k. To je ono što ćemo učiniti Mna uz korištenje dodatka otopine za rješenja.

Metoda je da (ovdje - pažnja: morate razmišljati) zbroj kvadrata razlike u eksperimentalno dobivenim i odgovarajućim izračunanim vrijednostima je minimalno. To jest, kada je X1 \u003d 1, zapravo izmjerena vrijednost Y1 \u003d 4.6, a procijenjena Y1 \u003d F (X1) je 4, kvadrat razlike će (Y1-Y1) ^ 2 \u003d (4-4.6) ^ 2 \u003d 0,36. Slijedeći na isti način: kada je X2 \u003d 2, zapravo izmjerena vrijednost Y2 \u003d 8.1, a izračunati U2 je 8, kvadrat razlike će biti (Y2-y2) ^ 2 \u003d (8-8,1) ^ 2 \u003d 0,01. A zbroj svih tih kvadrata treba biti minimalni mogući.

Dakle, mi ćemo nastaviti s treningom o korištenju MNK i dodaj-in Excel "Rješenje Traži" .

Pretraživanje aplikacije Traži rješenje

1. Ako niste uključili dodatak "pretraživanje rješenja", vraćamo se na stavku Kako omogućiti dodatak otopinu "rješenje" i uključite se 🙂

2. U A1 stanici uvozimo vrijednost "1". Ova jedinica će biti prva aproksimacija stvarne vrijednosti koeficijenta (k) naše funkcionalne ovisnosti Y \u003d KX.

3. U stupcu B, smjestili smo vrijednosti parametra X, u C stupcu - vrijednosti parametra y. U kolonskim stanicama, uvozimo formulu: "ceficient k pomnoženo na X vrijednost. Na primjer, u stanici D1 ulazimo u "\u003d A1 * B1", u D2 ćeliji unosimo "\u003d A1 * B2", itd.

4. Vjerujemo da je koeficijent k jednak jedan i funkcija F (x) \u003d y \u003d 1 * X je prva aproksimacija našem otopini. Možemo izračunati zbroj kvadrata razlika između izmjerenih vrijednosti vrijednosti Y i izračunati u skladu s formulom Y \u003d 1 * x. Možemo sve to ručno, odvezete u formuli odgovarajuće linkove na stanice: "\u003d (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ..., itd. U kraj koje smo pogrešno i razumijemo da su izgubili hrpu vremena. U Excelu postoji posebna formula za izračun zbroja kvadrata razlika, koji će se uvesti u A2 ćeliju i postaviti izvorne podatke: raspon izmjerenih Vrijednosti Y (stupac c) i raspon izračunatih vrijednosti Y (stupac d).

4. Izračunat je zbroj razlika u kvadratama - sada idemo na karticu "Data" i odaberite "pretraživanje rješenja".

5. U izborniku koji se pojavljuje kao varijabilna stanica, odaberite A1 ćeliju (onaj s K koeficijentom).

6. Kao cilj, odaberite A2 ćeliju i postaviti stanje na "Uspostaviti jednaku minimalnu vrijednost". Zapamtite da je to stanica u kojoj napravimo izračun zbroja kvadrata razlika između izračunatih i izmjerenih vrijednosti, a iznos bi trebao biti minimalan. Kliknite "Pokreni".

7. Odabran je koeficijent k. Sada možete biti sigurni da su izračunate vrijednosti sada vrlo blizu izmjerene.

p.s.

Općenito, naravno, za aproksimaciju eksperimentalnih podataka u Excelu postoje posebni alati koji vam omogućuju da opišite podatke pomoću linearne, eksponencijalne funkcije energije i polinoma, tako da je često moguće bez ponovno "otopina otopine", Rekao sam o svim tim metodama aproksimacije na svoj način, pa ako se pitam, pogledaj. Ali kada je riječ o nekoj egzotičnoj funkciji s jednim nepoznatim koeficijentom ili zadatke optimizacije, a zatim ovdje nadgradnja kao što je nemoguće usput.

Dodaj-in "pretraživanje rješenja" Također možete koristiti za druge zadatke, glavna stvar je razumjeti suštinu: postoji stanica u kojoj odabiremo vrijednost, ali postoji ciljna stanica u kojoj je uvjet navedeno za odabir nepoznatog parametra.
To je sve! U sljedećem članku ću vam reći bajku o odmoru, tako da ne bi zaštitili izlaz članka,

Najmanje kvadratna metoda Koristi se za procjenu parametara, regresijske jednadžbe.

Jedna od metoda za proučavanje stohastičkih veza između znakova je regresijska analiza.
Regresijska analiza je zaključak regresijske jednadžbe, s kojom postoji prosječna vrijednost slučajne varijable (rezultat znaka), ako je poznata vrijednost druge (ili druge) varijable (čimbenici). Uključuje sljedeće korake:

odabir obrasca za komunikaciju (vrsta analitičke regresije jednadžbe);
procjenu parametara jednadžbe;
procjenu kvalitete analitičke regresije jednadžbe.

Najčešće se koristi linearni oblik za opisivanje statističke veze znakova. Upozorenje za linearnu komunikaciju je zbog jasne ekonomske interpretacije svojih parametara, ograničenih varijabli varijabli iu većini slučajeva, nelinearni oblici komunikacije za izračune se transformiraju (logarhingming ili zamjenom varijabli) u linearni oblik.
U slučaju linearnog para veze, regresijska jednadžba će uzeti oblik: y i \u003d a + b · x i + u i. Parametri ove jednadžbe A i B procjenjuju se prema statističkom promatranju X i Y. Rezultat takve procjene je jednadžba:, gdje - procjene parametara A i B, - vrijednost rezultirajuće značajke (varijabla) dobivene regresijskom jednadžbom (izračunata vrijednost).

Najčešće za procjenu parametara metoda najmanjih kvadrata (MNC).
Metoda najmanjih kvadrata daje najbolje (bogate, učinkovite i otključane) procjene parametara regresije jednadžbe. Ali samo ako se određeni preduvjeti se izvode u odnosu na slučajni pojam (u) i neovisnu varijablu (X) (vidi pozadine MNC-a).

Problem procjene parametara jednadžbe linearnog para po metodi najmanjih kvadrata Sastoji se u sljedećem: za dobivanje takvih procjena parametara, na kojima je zbroj kvadrata odstupanja stvarnih vrijednosti efektivnog znaka - y i na izračunate vrijednosti je minimalan.
Formalno kriterij mnk. Možete pisati ovako: .

Klasifikacija metoda najmanjih kvadrata

Najmanje kvadratna metoda.
Maksimalna istinita metoda (za normalan klasični linearni regresijski model, normalnost regresijskih ostataka odgođena je).
Generalizirana metoda manjih kvadrata OMNA koristi se u slučaju autokorelacije pogrešaka iu slučaju heterosdastičnosti.
Metoda suspendiranih najmanjih kvadrata (poseban slučaj OMNA s heter-vizesnim ostacima).

Ilustriramo bit klasični najmanji kvadrat grafički grafički, Da bismo to učinili, konstruiramo raspored točke prema zapažanjima (x i, y, i \u003d 1; n) u pravokutnom koordinatnom sustavu (takva točka tablica naziva se polje korelacije). Pokušat ćemo odabrati ravnu liniju koja je najbliža točkama korelacijskog polja. Prema metodi najmanjih kvadrata, linija je odabrana tako da je zbroj kvadrata vertikalnih udaljenosti između točaka korelacijskog polja i ove linije bio minimalan.

Matematički zapis o ovom zadatku: .
Vrijednosti y i X i \u003d 1 ... N su nam poznati, to su opservacijski podaci. U funkciji s, oni su konstante. Varijable u ovoj značajki su željene procjene parametara -. Da biste pronašli minimalne funkcije s 2 varijable, potrebno je izračunati privatne derivate ove funkcije za svaki od parametara i izjednačiti im nulu, tj. .
Kao rezultat toga, dobivamo sustav od 2 normalne linearne jednadžbe:
Rješavanje ovog sustava naći ćemo željene procjene parametara:

Ispravnost izračuna parametara jednadžbe regresije može se testirati usporedbom količina (možda određenu razliku zbog izračuna zaokruživanja).
Da biste izračunali procjene parametara, možete izgraditi tablicu 1.
Znak koeficijenta regresije ukazuje na smjer komunikacije (ako je B\u003e 0, linija je izravna, ako B<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalno vrijednost parametra A je prosječna vrijednost Y s x jednaka nuli. Ako potpisnika nema i ne može imati nultu vrijednost, onda je gore navedeno interpretacija parametra i nema smisla.

Procjena nepropusnosti komunikacije između znakova Provodi se pomoću koeficijenta linearnog para korelacije - Rx, y. Može se izračunati pomoću formule: , Osim toga, koeficijent linearne uparive korelacije može se odrediti kroz regresijski koeficijent B: .
Područje dopuštenih vrijednosti linearnog koeficijenta paralacije para od -1 do +1. Znak koeficijenta korelacije označava smjer komunikacije. Ako je R x, y\u003e 0, onda je veza ravna; Ako r x, y<0, то связь обратная.
Ako je ovaj koeficijent blizu jedan, veza između značajki može se tumačiti kao prilično bliski linearni. Ako je njegov modul jednak jedinici ê r x, y ê \u003d 1, veza između znakova je funkcionalna linearna. Ako su znakovi X i Y linearno neovisni, onda je Rx, Y je blizu 0.
Da biste izračunali R x, Y također mogu koristiti tablicu 1.

Da bi se procijenila kvaliteta dobivene regresije jednadžbe, koeficijent teoretskog određivanja se izračunava - R2 YX:

,
gdje je D2 disperzija Y; objašnjeno regresijskom jednadžbom;
e 2 - preostala (neobjašnjena regresija jednadžba) disperzija y;
s 2 Y je ukupno (potpuna) disperzija Y.
Koeficijent određivanja karakterizira udio varijacije (disperzija) učinkovitog znaka Y, objašnjenih regresijom (i, posljedično, faktor X), u općoj varijaciji (disperzija) y. Koeficijent određivanja R2 YX uzima vrijednosti od 0 do 1. Prema tome, vrijednost 1-R2 yx karakterizira frakciju disperzije Y uzrokovanog utjecajem drugih nestalih čimbenika u model i specifikacijskih pogrešaka.
S uparenim linearnim regresijom R2 YX \u003d R2 YX.

4.1. Pomoću ugrađenih funkcija

Izračun koeficijenti recesije provodi se pomoću funkcije

Linena(Vrijednosti_y.; Vrijednosti_x.; Zatvoriti; statistika),

Vrijednosti_y. - niz vrijednosti y

Vrijednosti_x.- Izborni niz vrijednosti x.Ako je niz h. izostavljen, onda se pretpostavlja da je ovaj niz (1; 2; 3; ...) iste veličine kao i Vrijednosti_y.,

Zatvoriti- logička vrijednost koja ukazuje na to je li potrebno konstantno b. bio je jednak 0. ako Zatvoriti ima značenje PRAVI ili tada izostavljeni b. Izračunava se na uobičajeni način. Ako je argument Zatvoriti Bitno je laž b. oslanja se na jednake 0 i vrijednosti a. odabran tako da omjer y \u003d sjekira.

Statistika- logična vrijednost koja ukazuje na to da li će vratiti dodatnu statistiku regresije. Ako je argument Statistika ima značenje PRAVI, zatim funkcionirati Linena Vraća dodatnu statistiku regresije. Ako je argument Statistika ima značenje Lažan ili izostavljeni, onda funkcija Linena Vraća samo koeficijent a. I konstantno b..

Mora se pamtiti da je rezultat funkcija Linena ()je mnogo vrijednosti - niz.

Za izračun koeficijent korelacije Rabljena funkcija

Crnilu(Masive1.;Masive2.),

vraćanje vrijednosti koeficijenta korelacije gdje Masive1. - niz vrijednosti y., Masive2. - niz vrijednosti x.. Masive1. i Masive2. Mora biti jedna dimenzija.

Primjer 1., Ovisnost y.(x.) Predstavljen u tablici. Izgraditi regresija i izračunati koeficijent korelacije.

y.		0.5		1.5		2.5		3.5
X.		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Uvodimo tablicu vrijednosti MS Excel listu i izgradite raspored točke. Radni popis će se prikazati prikazano na Sl. 2.

Kako bi se izračunali vrijednosti koeficijenata regresije alii b.naglasci A7: B7, Okrenite se majstoru funkcija iu kategoriji Statistički Odaberite značajku Linena, Napunite dijaloški okvir pojavljuje kao prikazan na Sl. 3 i kliknite U REDU..

Kao rezultat toga, izračunata vrijednost će se pojaviti samo u ćeliji A6. (Sl.4). Kako bi se vrijednost pojavila u ćeliji B6. Morate unijeti način uređivanja (ključ F2)i zatim pritisnite kombinaciju tipki Ctrl + Shift + Enter.

Izračunati vrijednost koeficijenta korelacije u ćeliji C6. Uvedena je sljedeća formula:

C7 \u003d Corvela (B3: J3; B2: J2).

Znajući koeficijente regresije alii b. Izračunajte vrijednosti funkcije y.=sJEKIRA.+b. Za određeno x., Da biste to učinili, uvozimo formulu

B5 \u003d $ 7 * B2 + $ B $ 7

i kopirajte ga u raspon C5: J5.(Sl. 5).

Pokazat ću regresijsku liniju u dijagramu. Označite eksperimentalne točke na grafikonu, desnom tipkom miša i odaberite naredbu Početni podaci, U dijaloškom okviru koji se pojavljuje (Sl. 5), odaberite karticu Red i kliknite na gumb Dodati, Ispunite ulaznu polja, kao što je prikazano na Sl. 6 i pritisnite gumb u redu, Regresijska linija bit će dodan u eksperimentalni raspored podataka. Prema zadanim postavkama, njegov raspored će biti prikazan u obliku bodova koji nisu povezani s linijama za izglađivanje.

Sl. 6.

Za promjenu vrste regresije linije izvršavaju sljedeće korake. Desnom tipkom miša kliknite na točke koja prikazuje raspored retka, odaberite naredbu Tip dijagramai postaviti vrstu dijagrama točke, kao što je prikazano na Sl. 7.

Vrsta linije, njegova boja i debljina mogu se mijenjati na sljedeći način. Odaberite liniju na dijagramu, kliknite desnu tipku miša i odaberite naredbu u kontekstnom izborniku Format serije podataka ... Dalje, izradite instalacije, na primjer, kao što je prikazano na Sl. osam.

Kao rezultat svih transformacija dobivamo grafikon eksperimentalnih podataka i regresijsku liniju u jednom grafičkom području (sl. 9).

4.2. Pomoću linije trenda.

Izgradnja raznih aproksimacijskih ovisnosti u MS Excelu se provodi kao svojstva grafikona - trend linija.

Primjer 2., Kao rezultat eksperimenta određena je neka tablična ovisnost.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Odaberite i izgradite aproksimaciju ovisnosti. Izgradite grafiku tablične i odabrane analitičke ovisnosti.

Rješavanje problema može se podijeliti u sljedeće korake: Unos podataka o izvoru, izgradnju rasporeda točke i dodavanje trenda linije na ovu grafiku.

Razmotrite ovaj proces detaljno. Uvodimo izvorne podatke na radni list i konstruiramo graf eksperimentalnih podataka. Zatim odaberite eksperimentalne točke na grafikonu, desnom tipkom miša i koristite naredbu Dodatil. trend i trend (Sl. 10).

Dijaloški okvir koji se pojavljuje omogućuje izgradnju približnog ovisnosti.

Na prvoj kartici (sl. 11) ovog prozora je prikazana vrsta približnog ovisnosti.

Na drugoj (Sl. 12) definira parametre konstrukcije:

· Naziv ovisnosti o približavanju;

· Naprijed (natrag) na n. jedinice (ovaj parametar određuje koji broj jedinica ispred (leđa) mora se proširiti liniju trenda);

· Pokažite je li stražnji dio krivulje ravnom linijom y \u003d const.;

· Prikazati približnu funkciju na dijagramu ili ne (parametar za prikaz jednadžbe na dijagramu);

· Da li dijagram standardne devijacije ili ne (parametar se nalazi na dijagramu vrijednost točnosti aproksimacije).

Odabrali smo drugi stupanj polinom kao približnu ovisnost (sl. 11) i uklonite jednadžbu koja opisuje ovaj polinomi na grafikonu (Sl. 12). Dobiveni dijagram prikazan je na Sl. 13.

Slično s pomoći trend linije Možete odabrati parametre takvih ovisnosti kao

· Linearno y.=a ∙ X.+b.,

· Logaritam y.=∙ ln.(x.)+b.,

· Eksponencijalni y.=a ∙ e b,

· Snaga y.=a ∙ x b,

· Polinomi y.=a ∙ X. 2 +b ∙ X.+c., y.=a ∙ X. 3 +b ∙ X. 2 +c ∙ x + d I tako dalje, do polinoma od 6. stupnja,

· Linearno filtriranje.

4.3. Pomoću odlučujućeg bloka

Značajni interes je provedba parametara u MS Excelu koristeći manje kvadrate koji koriste odlučujući blok. Ova tehnika omogućuje vam da odaberete parametre funkcije bilo koje vrste. Razmotrite ovu značajku na primjeru sljedećeg zadatka.

Primjer 3., Kao rezultat eksperimenta, ovisnost z (t) prikazana u tablici

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Pokupite koeficijente ovisnosti Z (t) \u003d na 4 + bt 3 + ct 2 + dt + k Metoda najmanje kvadrata.

Ovaj zadatak je ekvivalent zadatku pronalaženja minimalne funkcije pet varijabli.

Razmotrite proces rješavanja problema optimizacije (Sl. 14).

Neka značenja ALI, U, IZ, D. i DO Pohranjeni u stanicama A7: E7., Izračunati teorijske vrijednosti funkcije Z(t.)=Na 4 + BT 3 + CT 2 + DT + K Za određeno t.(B2: J2.). To učiniti u ćeliji B4. Ulazimo u vrijednost funkcije u prvoj točki (ćeliju B2.):

B4 \u003d $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ e $ 7.

Kopirajte ovu formulu u rasponu C4: J4. i dobivamo očekivanu vrijednost funkcije na točkama, čiji su bijeg pohranjeni u stanicama B2: J2..

U ćeliji. B5. Uvodimo formulu koja izračunava kvadrat razlike između eksperimentalnih i izračunatih točaka:

B5 \u003d (B4-B3) ^ 2,

i kopirajte ga u raspon C5: J5., U ćeliji F7. Pohranit ćemo ukupnu kvadratnu pogrešku (10). Da biste to učinili, uvozimo formulu:

F7 \u003d Iznosi (B5: J5).

Koristimo tim Service®pousk rješenja i riješiti problem optimizacije bez ograničenja. Ispunite odgovarajuća polja za unos u dijaloškom okviru prikazan na Sl. 14 i pritisnite gumb Izvesti, Ako se pronađe rješenje, prozor prikazan na Sl. petnaest.

Rezultat odlučujućeg bloka bit će zaključak u ćeliji A7: E7.vrijednosti parametara Funkcije Z(t.)=Na 4 + BT 3 + CT 2 + DT + K, U stanicama B4: J4. Primati očekivana funkcija vrijednosti Na početnim točkama. U ćeliji F7. bit će pohranjen ukupna kvadratna pogreška.

Pitulirajte eksperimentalne točke i odabrana linija u jednom grafičkom području može biti ako je dodijeljeno raspon B2: J4.Poziv Glavni grafikonI zatim formatirajte izgled primljenih grafova.

Sl. 17 Prikazuje MS Excel radni popis nakon izvršenih izračuna.

5. Popis referenci

1. Alekseev e.r., chesnokova O.V., rješavanje problema računanja matematike u mathcad12 paketi, matlab7, maple9. - NT Press, 2006.-596c. : Il. - (tutorial)

2. Alekseev e.r., chesnokova o.v., e.a. Rudchenko, Scilab, rješavanje inženjerstva i matematičkih zadataka. -M., Binom, 2008.-260s.

3. Berezin I.S., Lovine N.P. Metode izračuna. - M.: Znanost, 1966.-632c.

4. Garnaev a.yu., koristeći MS Excel i VBA u ekonomiji i financijama. - Spb.: BHV - Petersburg, 1999.-332c.

5. Demidovich B.P., Maron i A., Shuvalova V.Z., numeričke metode analize. - M.: Znanost, 1967.-368c.

6. Kukuruz G., Korn T., matematički direktorij za znanstvenike i inženjere. - m., 1970, 720c.

7. Alekseev e.r., chesnokova O.V. Metodičke upute za izvođenje laboratorijskog rada u MS Excelu. Za studente svih specijaliteta. Donjeck, DonNtu, 2004. 112 str.

Ima mnogo aplikacija, jer vam omogućuje da obavite približan prikaz navedene funkcije drugim jednostavnijim. MNA može biti iznimno korisna u obradi opažanja, a aktivno se koristi za procjenu nekih vrijednosti prema rezultatima mjerenja drugih slučajnih pogrešaka. Iz ovog članka ćete naučiti kako provesti izračune koristeći metodu najmanjih kvadrata u Excelu.

Postavljanje zadatka na određeni primjer

Pretpostavimo da postoje dva pokazatelja x i y. I Y ovisi o X. Budući da je MNK zainteresiran za nas sa stajališta regresijske analize (u Excelu, njegove metode se provode pomoću ugrađenih funkcija), a zatim je potrebno odmah otići razmatranje određenog zadatka.

Dakle, neka X bude trgovačko područje prehrambene trgovine, mjereno u četvornim metrima i Y - godišnji promet, određen u milijunima rubalja.

Potrebno je napraviti prognozu koju će trgovina (y) biti u trgovini, ako ima jedno ili drugo područje shopping. Očito, funkcija y \u003d f (x) se povećava, jer hipermarket prodaje više proizvoda od štanda.

Nekoliko riječi o ispravnosti izvornih podataka koji se koriste za predviđanje

Pretpostavimo da imamo tablicu izgrađen prema podacima za N trgovina.

Prema matematičkim statistikama, rezultati će biti više ili manje ispravni ako se istražuju podaci za najmanje 5-6 objekata. Osim toga, nemoguće je koristiti "anomalne" rezultate. Konkretno, elitni mali boutique može imati promet s vremenom većim od trgovinskog prometa velikih trgovačkih točaka klasi masmarketa.

Bit metode

Tablica se mogu prikazati na deklačkoj ravnini u obliku bodova M 1 (x 1, y 1), ... M N (X N, Y N). Sada se rješenje problema svede na odabir aproksimacijske funkcije Y \u003d F (X), koji ima grafikon, što je moguće bliže točkama M 1, M 2, .. M n.

Naravno, moguće je koristiti polinom visoke stupnjeva, ali ova opcija nije samo teža, već je jednostavno netočna, jer neće odražavati glavni trend da je potrebno otkriti. Najrazumnije rješenje je potraga za izravnom Y \u003d AX + B, što najbolje dovodi eksperimentalne podatke, i točnije, koeficijenti - a i b.

Procjena točnosti

U bilo kojoj aproksimaciji, dobiva posebnu važnost za procjenu svoje točnosti. Označiti em i razlika (odstupanje) između funkcionalnih i eksperimentalnih vrijednosti za točku X i, tj. E i \u003d y i - f (x i).

Očito je da se količina odstupanja može koristiti za procjenu točnosti aproksimacije, tj sve razmatrane točke. Međutim, nije sve tako jednostavno, jer će zajedno s pozitivnim odstupanjima gotovo biti prisutno i negativno.

Problem možete riješiti pomoću modula devijacije ili njihovih kvadrata. Posljednja metoda bila je široko distribuirana. Koristi se u mnogim područjima, uključujući regresijsku analizu (u Excelu njegovu provedbu provodi se pomoću dvije ugrađene funkcije), te je dugo dokazala njegovu učinkovitost.

Najmanje kvadratna metoda

U Excelu, kao što je poznato, postoji ugrađena autosummy funkcija, koja vam omogućuje izračunavanje vrijednosti svih vrijednosti koje se nalaze u namjenskom rasponu. Dakle, ništa neće ometati nas za izračunavanje vrijednosti ekspresije (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

U matematičkom zapisu ima oblik:

Budući da je izvorno odlučeno približiti ravnom linijom, imamo:

Dakle, zadatak pronalaženja ravne linije, koji najbolje opisuje specifičnu ovisnost vrijednosti X i Y, smanjuje se na izračun minimalne funkcije dvije varijable:

Da biste to učinili, potrebno je izjednačiti s nula privatnim derivatima prema novim varijablama A i B, te riješiti primitivni sustav koji se sastoji od dvije jednadžbe s 2 nepoznate vrste:

Nakon jednostavnih transformacija, uključujući podjelu na 2 i manipulaciju samim, dobivamo:

Rješavanje, na primjer, na uređaju dobivamo stacionarnu točku s nekim koeficijentima a * i b *. To je minimum, to jest, za predviđanje, koji će trgovina biti u trgovini na određenom području, pravi crta Y \u003d X + B * je prikladna, što je regresijski model za primjer, o kojem se raspravlja. Naravno, neće dopustiti da pronađe točan rezultat, ali će pomoći da dobije ideju o tome hoće li kupnja betonskog područja biti kupljena.

Kako implementirati metodu najmanje kvadrata u Excelu

U "Excel" postoji funkcija za izračunavanje vrijednosti MNA. Ima sljedeći oblik: "trend" (poznat. Vrijednosti Y; poznate. X vrijednosti; nove vrijednosti x; const.). Primijenite formulu za izračun MNC-a u Excel na naš stol.

Da biste to učinili, u stanici u kojoj bi se pojavio rezultat izračuna manjih kvadrata u Excelu, uvodemo znak "\u003d" i odaberite "trend" funkciju. U otvorenom prozoru ispunite odgovarajuća polja, ističući:

raspon poznatih vrijednosti za y (u ovom slučaju, podaci za promet);
raspon X 1, ... X N, tj. Vrijednosti maloprodajnog prostora;
i dobro poznate i nepoznate vrijednosti X za koje je potrebno saznati veličinu prometa (za informacije o njihovoj lokaciji na radnom listu, vidi dolje).

Osim toga, formula ima logičku varijablu "COST". Ako uđete u odgovarajuće polje 1, to će značiti da bi se izračuni trebali napraviti, vjerujući da B \u003d 0.

Ako trebate saznati prognozu za više od jedne vrijednosti x, a zatim nakon ulaska u formulu, ne biste trebali kliknuti na "Enter", ali morate birati "Shift" + "Kontrola" + "Enter" na tipkovnica.

Neke značajke

Regresijska analiza može biti dostupna čak i za čajke. Excelova formula za predviđanje vrijednosti niza nepoznatih varijabli - "trend" - čak i onih koji nikada nisu čuli za metodu najmanje kvadrata mogu se koristiti. Dovoljno je znati neke osobine njezina rada. Posebno:

Ako organizirate raspon poznatih vrijednosti varijable Y u jednom retku ili stupcu, svaki redak (stupac) s poznatim X vrijednostima percipirat će se kao zasebna varijabla.
Ako "trend" prozor ne navode niz s poznatim X, zatim u slučaju korištenja funkcije u Excelu, program će ga uzeti u obzir kao niz koji se sastoji od cijelih brojeva, čiji broj odgovara određenim vrijednostima Varijable y.
Da bi se dobio niz "predviđenih" vrijednosti na izlazu, izraz za izračunavanje trenda mora se primijeniti kao formula polja.
Ako nova X vrijednosti nisu navedene, funkcija "trend" smatra ih jednakim poznatim. Ako nisu navedeni, niz 1 se uzima kao argument; 2; 3; 4; ..., što je razmjerno rasponu s već određenim parametrima Y.
Raspon koji sadrži nove vrijednosti X treba se sastojati od istih ili više redaka ili stupaca kao raspona s navedenim vrijednostima. Drugim riječima, mora biti razmjerna neovisnim varijablama.
U nizu s poznatim X vrijednostima može se sadržavati nekoliko varijabli. Međutim, ako govorimo samo o jednom, potrebno je da su rasponi s navedenim X i Y vrijednostima proporcionalni. U slučaju nekoliko varijabli, potrebno je da je raspon s navedenim vrijednostima Y smještaj u jednom stupcu ili u jednom redu.

Funkcija predicz

Implementirano pomoću više funkcija. Jedan od njih se zove "predviđa". To je slično "trend", to jest, to izdaje rezultat izračuna korištenjem metode najmanjih kvadrata. Međutim, samo za jedan x, za koji je y nepoznat.

Sada znate formulu u Excelu za čajnike, omogućujući predviđanje vrijednosti buduće vrijednosti jednog ili drugog pokazatelja prema linearnom trendu.

4.1. Pomoću ugrađenih funkcija

Izračun koeficijenti recesije provodi se pomoću funkcije

Linena(Vrijednosti_y.; Vrijednosti_x.; Zatvoriti; statistika),

Vrijednosti_y. - niz vrijednosti y

Vrijednosti_x.- Izborni niz vrijednosti x.Ako je niz h. izostavljen, onda se pretpostavlja da je ovaj niz (1; 2; 3; ...) iste veličine kao i Vrijednosti_y.,

Mora se pamtiti da je rezultat funkcija Linena ()je mnogo vrijednosti - niz.

Za izračun koeficijent korelacije Rabljena funkcija

Crnilu(Masive1.;Masive2.),

vraćanje vrijednosti koeficijenta korelacije gdje Masive1. - niz vrijednosti y., Masive2. - niz vrijednosti x.. Masive1. i Masive2. Mora biti jedna dimenzija.

Primjer 1., Ovisnost y.(x.) Predstavljen u tablici. Izgraditi regresija i izračunati koeficijent korelacije.

y.		0.5		1.5		2.5		3.5
X.		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Uvodimo tablicu vrijednosti MS Excel listu i izgradite raspored točke. Radni popis će se prikazati prikazano na Sl. 2.

Izračunati vrijednost koeficijenta korelacije u ćeliji C6. Uvedena je sljedeća formula:

C7 \u003d Corvela (B3: J3; B2: J2).

Znajući koeficijente regresije alii b. Izračunajte vrijednosti funkcije y.=sJEKIRA.+b. Za određeno x., Da biste to učinili, uvozimo formulu

B5 \u003d $ 7 * B2 + $ B $ 7

i kopirajte ga u raspon C5: J5.(Sl. 5).

Kao rezultat svih transformacija dobivamo grafikon eksperimentalnih podataka i regresijsku liniju u jednom grafičkom području (sl. 9).

4.2. Pomoću linije trenda.

Izgradnja raznih aproksimacijskih ovisnosti u MS Excelu se provodi kao svojstva grafikona - trend linija.

Primjer 2., Kao rezultat eksperimenta određena je neka tablična ovisnost.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Odaberite i izgradite aproksimaciju ovisnosti. Izgradite grafiku tablične i odabrane analitičke ovisnosti.

Rješavanje problema može se podijeliti u sljedeće korake: Unos podataka o izvoru, izgradnju rasporeda točke i dodavanje trenda linije na ovu grafiku.

Dijaloški okvir koji se pojavljuje omogućuje izgradnju približnog ovisnosti.

Na prvoj kartici (sl. 11) ovog prozora je prikazana vrsta približnog ovisnosti.

Na drugoj (Sl. 12) definira parametre konstrukcije:

· Naziv ovisnosti o približavanju;

· Naprijed (natrag) na n. jedinice (ovaj parametar određuje koji broj jedinica ispred (leđa) mora se proširiti liniju trenda);

· Pokažite je li stražnji dio krivulje ravnom linijom y \u003d const.;

· Prikazati približnu funkciju na dijagramu ili ne (parametar za prikaz jednadžbe na dijagramu);

· Da li dijagram standardne devijacije ili ne (parametar se nalazi na dijagramu vrijednost točnosti aproksimacije).

Odabrali smo drugi stupanj polinom kao približnu ovisnost (sl. 11) i uklonite jednadžbu koja opisuje ovaj polinomi na grafikonu (Sl. 12). Dobiveni dijagram prikazan je na Sl. 13.

Slično s pomoći trend linije Možete odabrati parametre takvih ovisnosti kao

· Linearno y.=a ∙ X.+b.,

· Logaritam y.=∙ ln.(x.)+b.,

· Eksponencijalni y.=a ∙ e b,

· Snaga y.=a ∙ x b,

· Polinomi y.=a ∙ X. 2 +b ∙ X.+c., y.=a ∙ X. 3 +b ∙ X. 2 +c ∙ x + d I tako dalje, do polinoma od 6. stupnja,

· Linearno filtriranje.

4.3. Korištenje alata za analizu opcije: pretraživanje rješenja.

Značajni interes je značajan interes za MS Excel Odabir parametara funkcionalne ovisnosti najmanjim kvadratima pomoću alata za analizu opcije: rješenje otopine. Ova tehnika omogućuje vam da odaberete parametre funkcije bilo koje vrste. Razmotrite ovu značajku na primjeru sljedećeg zadatka.

Primjer 3., Kao rezultat eksperimenta, ovisnost z (t) prikazana u tablici

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Pokupite koeficijente ovisnosti Z (t) \u003d na 4 + bt 3 + ct 2 + dt + k Metoda najmanje kvadrata.

Ovaj zadatak je ekvivalent zadatku pronalaženja minimalne funkcije pet varijabli.

Razmotrite proces rješavanja problema optimizacije (Sl. 14).

B4 \u003d $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ e $ 7.

Kopirajte ovu formulu u rasponu C4: J4. i dobivamo očekivanu vrijednost funkcije na točkama, čiji su bijeg pohranjeni u stanicama B2: J2..

U ćeliji. B5. Uvodimo formulu koja izračunava kvadrat razlike između eksperimentalnih i izračunatih točaka:

B5 \u003d (B4-B3) ^ 2,

i kopirajte ga u raspon C5: J5., U ćeliji F7. Pohranit ćemo ukupnu kvadratnu pogrešku (10). Da biste to učinili, uvozimo formulu:

F7 \u003d Iznosi (B5: J5).

Sl. 17 Prikazuje MS Excel radni popis nakon izvršenih izračuna.