Odluči se problem aproksimacije eksperimentalni podaci - znači da se izgradi jednačina regresije. Problem aproksimacije nastaje, ako je potrebno, analitički, odnosno u obliku matematičke zavisnosti, za opisivanje stvarnih pojava, čija su zapažanja data u obliku tabele koja sadrži vrednosti indikatora u različitim vremenima ili at različita značenja nezavisni argument. Na primjer,
Pokazatelji profita su poznati (mogu se odrediti Y) zavisno od visine ulaganja ( X);
Poznat je obim prodaje kompanije ( Y) za šest sedmica njenog rada. U ovom slučaju, X To je niz sedmica.
Ponekad kažu da je potrebno izgraditi empirijski model . Empirijski se zove model izgrađen na osnovu stvarnih zapažanja. Ako se model pronađe, moguće je predvideti ponašanje proučavane pojave i procesa u budućnosti i, eventualno, izabrati optimalni pravac njegovog razvoja.
Uglavnom problem aproksimacije eksperimentalni podaci imaju sljedeće inscenacija :
Neka podaci dobijeni na praktičan način (u tokun eksperimenti ili zapažanja), koji se mogu predstaviti parovima brojeva (X i; na i) ... Odnos između njih je prikazan u tabeli:
| X | x 1 | x 2 | x 3 | … | x n |
| Y | y 1 | y 2 | y 3 | … | y n |
Postoji klasa različitih funkcijaF ... Potrebno je pronaći analitički (tj. matematički) izraz odnosa između ovih indikatora, odnosno iz skupa funkcija F izabrati funkcijuf , takav da. koji najbolji način bi izgladio eksperimentalni odnos između varijabli i, što je preciznije moguće, odražavao opšti trend odnosa izmeđuX i Y, isključujući greške mjerenja i slučajna odstupanja.
Oblik funkcije možete saznati ili iz teorijskih razmatranja, ili analizom lokacije tačaka (x i; y i) na koordinatnoj ravni.
Riješite grafički problem aproksimacije znači nacrtati takvu krivu, čije tačke (x i; ŷ i) bio bi što bliže polaznim tačkama (x i; y i) prikazivanje eksperimentalnih podataka.
Za rješenja problemi aproksimacije koristiti metoda najmanjeg kvadrata .
U ovom slučaju, funkcija se smatra najboljom aproksimacijom ako za nju ima zbir kvadrata odstupanja "teorijskih" vrijednosti pronađenih empirijskom formulom od odgovarajućih eksperimentalnih vrijednosti najmanju vrijednost u poređenju sa drugim funkcijama od kojih se bira željena aproksimacija.
Matematička notacija metoda najmanjih kvadrata je:
gdje n - broj posmatranja indikatora.
Dakle, problem aproksimacije se dijeli na dva dijela.
Prvo se utvrđuje vrsta zavisnosti i, shodno tome, tip empirijske formule, odnosno odlučuje da li je linearna, kvadratna, logaritamska ili neka druga. Ako nema teorijskih razmatranja za izbor oblika formule, funkcionalna zavisnost se obično bira između najjednostavnijih, upoređujući njihove grafove sa grafom date funkcije.
Nakon toga se određuju numeričke vrijednosti nepoznatih parametara odabrane empirijske formule, za koje se ispostavlja da je aproksimacija zadanoj funkciji najbolja.
Najjednostavniji tip empirijskog modela sa dva parametra koji se koriste za aproksimaciju eksperimentalnih rezultata je linearna regresija, koju opisuje linearna funkcija:
gdje a, b su potrebni parametri.
Za model linearne regresije, metoda najmanjih kvadrata (1) će biti napisana:
Za rješavanje (2) u odnosu na a i b, parcijalni derivati su izjednačeni sa nulom:
Kao rezultat, da biste pronašli a i b, potrebno je riješiti sistem linearnih algebarskih jednadžbi oblika:
(3)
Postoje različiti načini za implementaciju metode najmanjih kvadrata za linearnu regresiju u Excelu.
1 način. Konstruirajte sistem linearnih algebarskih jednadžbi, zamjenjujući sve poznate vrijednosti u (3), i riješite ga, na primjer, matričnom metodom (vidi problem 4).
U formuličkom obliku, element tabele proračuna prikazan je na Sl. 26.
Metoda 2. Riješite problem optimizacije (2) u Excelu primjenom Pronalaženje rješenja(vidi doc. 5).
Napomena 1. Treba napomenuti da je za funkciju cilja S zgodno koristiti ugrađenu matematička funkcija SUMKVRAZN (niz1, niz2), kao rezultat čega se izračunava zbir kvadrata razlika dva niza. U našem slučaju, trebali bismo navesti raspon početnih vrijednosti kao niz1, a "teorijske" vrijednosti izračunate po formuli kao niz2, gdje je a i b - ovo su adrese ćelija sa željenim vrijednostima.
Napomena 2. U dijaloškom okviru komande Find solution treba navesti ciljnu ćeliju, smjer cilja - na minimum i ćelije koje treba promijeniti (Sl. 28). Ovaj zadatak ne sadrži nikakva ograničenja.
Napomena 3. Nelinearni modeli oblika mogu se koristiti i kao empirijski modeli sa dva parametra:
Opisani metod za rješavanje metoda najmanjih kvadrata primjenjiv je za nelinearne ovisnosti.
Metoda 3. Da biste pronašli vrijednosti parametara a i b za linearnu regresiju, možete koristiti sljedeće ugrađene statističke funkcije u Excelu:
SLOPE (poznati_y; poznati_x)
INTERCEPT (poznati_Y; poznati_X)
LINEST (poznati_y; poznati_x)
Štaviše, funkcija SLOPE () vraća vrijednost parametra a , INTERCEPT () funkcija vraća vrijednost parametra b. LINEST () vraća oba parametra linearne zavisnosti u isto vrijeme, budući da je to funkcija niza. Stoga, da biste unijeli funkciju LINEST () u tablicu, morate promatrati slijedeći pravila:
Odaberite dvije susjedne ćelije
Unesite formulu
· Na kraju istovremeno pritisnite kombinaciju tastera Ctrl + Shift + Enter.
Kao rezultat, lijeva ćelija će sadržavati vrijednost parametra a , a desno - vrijednost parametra b.
Za rješavanje problema aproksimacije grafički u Excelu morate napraviti grafikon na osnovu početnih podataka, na primjer, Scatter plot sa vrijednostima povezanim linijama za izravnavanje (vidi tačku 1). Excel može crtati na ovom grafikonu Linija trenda... Trend linija se može dodati bilo kojoj seriji podataka koristeći sledeće vrste grafikoni: dijagrami područja, linijski grafikoni, trakasti grafikoni, trakasti grafikoni ili razbacani grafikoni.
Kada kreirate liniju trenda u Excelu na osnovu podataka grafikona, primjenjuje se jedna ili druga aproksimacija. Excel vam omogućava da odaberete jednu od pet aproksimativnih linija ili izračunate liniju koja prikazuje pokretni prosjek.
Osim toga, Excel pruža mogućnost odabira vrijednosti presjeka linije trenda sa Y osom, kao i dodavanje aproksimacijske jednačine i vrijednosti pouzdanosti aproksimacije (R 2) na grafikon. Također, možete odrediti buduće i prošle vrijednosti podataka na osnovu linije trenda i povezane aproksimacijske jednačine.
Polinomska aproksimacija kontinuirane funkcije na segmentu.
Aproksimacija (od latinskog "približan" - "približiti se") - približan izraz bilo kojeg matematičkog objekta (na primjer, brojeva ili funkcija) kroz druge jednostavnije, prikladnije za korištenje ili jednostavno bolje poznate. U naučnim istraživanjima, aproksimacija se koristi za opisivanje, analizu, generalizaciju i dalju upotrebu empirijski rezultati.
Kao što znate, može postojati tačan (funkcionalni) odnos između veličina, kada jedna vrijednost argumenta odgovara jednoj specifičnoj vrijednosti, i manje precizan (korelacijski) odnos, kada jedna specifična vrijednost argumenta odgovara približnoj vrijednosti ili skup vrijednosti funkcije koje su više ili manje bliske jedna drugoj. Prilikom dirigovanja naučno istraživanje, obrada rezultata posmatranja ili eksperimenta obično se mora baviti drugom opcijom. Prilikom proučavanja kvantitativnih ovisnosti različitih pokazatelja, čije se vrijednosti određuju empirijski, u pravilu postoji određena varijabilnost. Djelomično je to uvjetovano heterogenošću proučavanih objekata nežive, a posebno žive prirode, dijelom zbog greške u promatranju i kvantitativne obrade materijala. Posljednju komponentu nije uvijek moguće potpuno isključiti, ona se može minimizirati samo pažljivim odabirom adekvatne metode istraživanja i tačnosti rada. Stoga, prilikom obavljanja bilo kakvog istraživačkog rada, javlja se problem utvrđivanja prave prirode zavisnosti proučavanih indikatora, ovaj ili onaj stepen je maskiran nedostatkom odgovornosti za varijabilnost vrijednosti. Za to se koristi aproksimacija - približan opis korelacione zavisnosti varijabli odgovarajućom jednačinom funkcionalna zavisnost, prenoseći glavni trend ovisnosti (ili njegov "trend").
Prilikom odabira aproksimacije treba polaziti od specifičnog istraživačkog problema. Obično, što se jednačina jednostavnija koristi za aproksimaciju, to će opis zavisnosti biti približniji.
Stoga je važno pročitati koliko su značajna i što je uzrokovalo odstupanja konkretnih vrijednosti od rezultirajućeg trenda. Kada se empirijski opisuje zavisnost određene vrijednosti mnogo veća tačnost se može postići upotrebom neke složenije, višeparametarske jednačine. Međutim, nema smisla težiti maksimalnom preciznošću prenošenja nasumičnih odstupanja vrijednosti u određenim serijama empirijskih podataka. Mnogo je važnije shvatiti opći obrazac koji u u ovom slučaju najlogičnije i sa prihvatljivom tačnošću izražava se upravo dvoparametarskom jednačinom funkcija snage... Stoga, birajući metodu aproksimacije, istraživač uvijek pravi kompromis: odlučuje u kojoj mjeri je u ovom slučaju svrsishodno i prikladno "žrtvovati" detalje i, shodno tome, koliko općenito treba izraziti ovisnost upoređenih varijabli. Uz identifikaciju obrazaca maskiranih nasumičnim odstupanjima empirijskih podataka od opšti obrazac, aproksimacija također omogućava rješavanje mnogih drugih važnih problema: formalizirati pronađenu zavisnost; pronađite nepoznate vrijednosti zavisne varijable interpolacijom ili, ako je primjenjivo, ekstrapolacijom.
Ovdje će biti riječi o polinomskoj aproksimaciji. To znači da je naš zadatak da je na osnovu početnih podataka (funkcija i segment) potrebno pronaći polinom čije će odstupanje linije od grafika početne funkcije biti minimalno.
Najpopularnija metoda polinomske aproksimacije je metoda najmanjih kvadrata. U Excelu se implementira pomoću grafikona i linije trenda.
Hajde da analiziramo ovu metodu u Excel-u.
Početni podaci:
Prvo, trebamo podijeliti ovaj segment korištenjem "Čebiševljevog" cijepanja, budući da dati pogled cijepanje uvijek daje precizniji rezultat.
U kolonu I (slika 1) upisujemo brojeve od 0 do 8, jer Segment dijelimo na 8 dijelova.
U koloni z, ćelije se izračunavaju pomoću formule: COS (3,141593 * I / 8). Za izračunavanje svake ćelije koristimo odgovarajući I.
Vrijednost svakog x nalazi se po formuli: 2 * z + 1.
Kolona F (x) izračunava vrijednost ove funkcije za svaki x.
Slika 1
Zatim u ćelijama H2, I2, J2 postavljamo početne vrijednosti koeficijenata a, b i c u željenom polinomu (slika 2).
Slika 2
U koloni F od ćelija 2 do 10 izračunavamo vrijednosti odstupanja, tj. modul razlike između vrijednosti početne funkcije i pronađenog polinoma.
Formula: ABS ((1 + x ^ 2) ^ 0,5 + 2 ^ (- x) - ($ H $ 2 * x ^ 2 + $ I $ 2 * x + $ J $ 2)).
Ćelija B11 izračunava zbir varijansi, a ćelija B12 izračunava prosječno odstupanje (slika 3).
Slika 3
Koristite Čarobnjak za grafikone da napravite dijagram raspršenosti na osnovu podataka u kolonama x i F (x). Sada u kartici „Dijagram“ izaberite „Dodaj liniju trenda“ i postavite potreban kvadratić za potvrdu kako bi se jednačina prikazala na dijagramu (slika 4).
Slika 4
Sada zamjenjujemo koeficijente iz rezultirajuće jednačine u ćelije H2, I2 i J2 (slika 5).
Slika 5
Kao što vidite, prosječno odstupanje je 0,117006252.
Pronađeni polinom: 0,363 * x² - 0,6901 * x + 2,2203.
Predlažemo drugu metodu polinomske aproksimacije.
Otvorite karticu "Usluga" i odaberite "Traži rješenja". U prozoru koji se pojavi navedite F11 kao ciljnu ćeliju, a ona je jednaka minimalnoj vrijednosti. U polju "promjena ćelija" navedite H2, I2 i J2.
Kliknite na dugme "Pokreni". Nakon završetka procedure vidimo da su se rezultati promijenili (slika 6).
Slika 6
Ovog puta prosječno odstupanje je 0,106084329.
Pronađeni polinom: 0,35724 * x² - 0,702 * x + 2,259158.
Ovaj rezultat je mnogo tačniji od prethodnog, što potvrđuje prednost korištenja minimizacije sume odstupanja u odnosu na metodu najmanjih kvadrata.
ZAVISNOSTI
Excel ima alate za predviđanje procesa. Problem aproksimacije nastaje kada je potrebno analitički opisati pojave koje se dešavaju u životu i date su u obliku tabela koje sadrže vrijednosti argumenta (argumenata) i funkcije. Ako se zavisnost pronađe, moguće je predvideti ponašanje sistema koji se proučava u budućnosti i, eventualno, izabrati optimalni pravac njegovog razvoja. Takva analitička funkcija (koja se naziva i trend) može imati različite vrste i različit nivo složenosti u zavisnosti od složenosti sistema i željene tačnosti reprezentacije.
10.1. Linearna regresija
Najjednostavniji i najpopularniji je pravolinijska aproksimacija - linearna regresija.
Pretpostavimo da imamo stvarne informacije o nivoima profita Y u zavisnosti od veličine X kapitalnih ulaganja - Y (X). Na sl. 10.1-1 prikazane su četiri takve tačke M (Y, X). Neka također imamo razloga pretpostaviti da je ova zavisnost linearna, tj. ima oblik Y = A + BX. Ako bismo bili u mogućnosti da pronađemo koeficijente A i B i iskoristimo ih za konstruiranje prave linije (na primjer, kao na slici), u budućnosti bismo mogli napraviti utemeljene pretpostavke o dinamici poslovanja i mogućem komercijalnom stanju preduzeće u budućnosti. Očigledno, zadovoljili bismo se pravom linijom koja se nalazi što bliže poznatim tačkama M (Y, X), tj. imaju minimalni iznos odstupanja ili količinu grešaka (na slici su odstupanja prikazana isprekidanim linijama). Poznato je da postoji samo jedna takva prava linija.
|
|
Za rješavanje ovog problema koristi se metoda najmanjih kvadrata grešaka. Razlika (greška) između poznata vrijednost Y1 tačke M1 (Y1, X1) i vrijednost Y (X1), izračunata jednadžbom prave linije za istu vrijednost X1, bit će
D1 = Y1 - A - B X1.
Ista razlika
za X = X2 će biti D2 = Y2 - A - B X2;
za X = X3 D3 = Y3 - A - B X3;
i za X = X4 D4 = Y4 - A - B X4.
Napišimo izraz za zbir kvadrata ovih grešaka
F (A, B) = (Y1 – A – B X1) 2 + (Y2 – A – B X2) 2 + (Y3 – A – B X3) 2 + (Y4 – A – B X4) 2
ili u skraćenom obliku F (B, A) = e (Yi - A - BXi) 2.
Ovdje znamo da su svi X i Y i koeficijenti A i B su nepoznati. Povucimo traženu pravu na način (tj. izaberimo A i B takve) tako da ovaj zbir kvadrata grešaka F (A, B) je minimalan. Uslovi minimalnosti su dobro poznate relacije
¶F (A, B) / ¶A = 0 i ¶F (A, B) / ¶B = 0.
Izvedemo ove izraze (izostavljamo indekse na predznaku zbira):
¶ [å (Yi – A – B Xi) 2] / ¶A = å (Yi – A – B Xi) (- 1)
¶ [å (Yi – A – B Xi) 2] / ¶B = å (Yi – A – B Xi) (- Xi).
Rezultirajuće formule transformiramo i izjednačavamo sa nulom
Rješavanje aproksimacijskih problema pomoću Excela
doktor fiz.- mat. nauka, profesor Gavrilenko V.V. asistent Parokhnenko L.M.
(Nacionalni transportni univerzitet)
Teorijske informacije. U praksi, pri modeliranju raznih
procesi, posebno ekonomski, fizički, tehnički, društveni,
široko se koriste različite metode izračunavanja približnih vrijednosti
funkcije od njihovih poznatih vrijednosti u nekim fiksnim tačkama.
Često se javljaju takvi problemi aproksimacije funkcija:
∙ prilikom konstruiranja približnih formula za izračunavanje vrijednosti karakteristike
vrijednosti procesa koji se proučava prema tabelama dobijenim u
rezultat fizičkog ili kompjuterskog eksperimenta;
∙ sa numeričkom integracijom, numeričkom diferencijacijom, numeričkom
n odluka diferencijalne jednadžbe itd.;
∙ ako je potrebno izračunati vrijednosti funkcija u međutočkama
kah razmatranog intervala;
∙ pri određivanju vrijednosti karakterističnih veličina procesa izvan
interval, posebno, ako je potrebno, ispitati
"Prošlost"), odnosno prilikom određivanja vrijednosti indikatora procesa prije
sati posmatranja;
∙ u prognoziranju, odnosno prilikom prijema preliminarne procjene Hoću-
vrijednosti indikatora procesa od interesa (sposobnost gledanja
u budućnost").
Ako, da simuliramo određeni proces dat u tabeli,
konstruirati približan opis ove procesne funkcije na osnovu
metoda najmanjih kvadrata, tada se naziva aproksimirajuća funkcija
(regresija), a naziva se i sam problem konstruisanja aproksimirajućih funkcija
problem aproksimacije.
Ovaj članak govori o mogućnostima Excel paketa prilikom rješavanja
aproksimacijski problemi, odnosno metode i tehnike konstruisanja
(kreiranje) regresija za funkcije definirane u tablici, što je osnova regresione analize.
Excel ima takve karakteristike za pravljenje regresije:
1) dodavanje odabranih regresija (linija trenda) dijagramu izgrađenom na osnovu tabele podataka za proučavanu karakteristiku procesa (ovaj alat se može koristiti samo ako postoji konstruisan dijagram).
2) koristeći ugrađene statističke funkcije radnog lista Excel,
omogućavajući dobijanje regresija (linija trenda) na osnovu tabele ishoda
podatke (upotreba ovog alata nije prethodno povezana
uz prisustvo odgovarajućeg dijagrama).
Dodavanje linija trenda grafikonu
Za tabelu podataka koja opisuje određeni proces i predstavlja dijagram, Excel ima efikasan alat regresiona analiza
za, dozvoljavajući:
∙ izgraditi na osnovu metode najmanjih kvadrata i dodati pet tipova regresija (linija trenda) dijagramu, koje su manje ili više tačne
sti simulirati proces koji se proučava;
∙ dodati jednadžbinu konstruisane regresije dijagramu;
∙ odredite stepen do kojeg odabrana regresija odgovara podacima prikazanim na grafikonu.
Konstruisani modeli procesa - pokazuju linije trenda
trend promjene podataka, omogućavaju određivanje vrijednosti studije
karakteristike u srednjim tačkama, predviđaju ponašanje ovog procesa u budućnosti (problem ekstrapolacije), a takođe sagledavaju njegovu prošlost.
Na osnovu podataka grafikona, Excel vam omogućava da dobijete ove vrste regresije
ovo ili linije trenda, kao što su linearne, polinomske, logaritamske, ste-
pjenasto, eksponencijalno, koje su date jednadžbom y = y (x), gdje x nije
Zavisna varijabla, koja često poprima vrijednosti niza prirodnih brojeva (1; 2; 3; ...) i proizvodi, na primjer, odbrojavanje vremena rada procesa koji se proučava.
1. Linearna regresija je dobra za modeliranje karakteristika koje se povećavaju ili smanjuju konstantnom brzinom. Ovo je najjednostavniji za izgradnju, ali najmanje tačan model procesa koji se proučava.
y = m x + b,
gdje je m ugao nagiba linearne regresije prema osi apscise; b - koordinata tačke preseka linearne regresije sa ordinatnom osom.
2. Polinomska linija trenda je korisna za opisivanje karakteristika,
ima nekoliko izraženih ekstrema (maksimuma i minimuma)
mov). Izbor stepena polinomske linije trenda (polinoma) određen je brojem ekstrema proučavane karakteristike. Dakle, polinom drugog stepena može dobro opisati karakteristiku koja ima samo jedan maksimum
mama ili minimum; polinom trećeg stepena - ne više od dva ekstrema; na-
linija četvrtog stepena - ne više od tri ekstrema, itd.
Konstruisano u skladu sa jednačinom
y = c0 + c1 x + c2 x2 + c3 x3 + c4 x4 + c5 x5 + c6 x6,
gdje su koeficijenti c 0, c 1, c 2, ... c 6 konstante.
3. Logaritamski linija trenda se uspješno koristi u modeliranju
karakteristike, čije se vrijednosti u početku brzo povećavaju ili smanjuju, a zatim se postupno stabiliziraju.
Konstruisano u skladu sa jednačinom
y = c × ln (x) + b,
4. Power trend line daje dobre rezultate ako su vrijednosti studije
Ove zavisnosti karakteriše stalna promena stope rasta.
Primjer takve zavisnosti je graf jednoliko ubrzanog kretanja.
niya car. Ako podaci sadrže nulte ili negativne vrijednosti, ne možete koristiti liniju trenda snage.
Konstruisano u skladu sa jednačinom
y = c × xb,
gdje su koeficijenti b, c konstante.
5. Eksponencijalno Linija trenda treba se koristiti kada se stopa promjene podataka kontinuirano povećava. Za podatke koji sadrže nulte ili negativne vrijednosti, ova vrsta aproksimacije nije primjenjiva.
Konstruisano u skladu sa jednačinom
y = c × eb × x,
gdje su koeficijenti b, c konstante.
Prilikom odabira linije trenda, Excel automatski izračunava vrijednost R 2, koja karakterizira tačnost aproksimacije: što je vrijednost R 2 bliža jedinici, to je pouzdanije linija trenda aproksimirana istraženom
moj proces. Ako je potrebno, vrijednost R 2 uvijek može biti prikazana
dijagram.
Određeno formulom
R 2 = 1- | Σ1 = ∑ (yj - Yj) 2 | S2 = ∑Y j 2 - | × (∑Yj) 2 | ||||||
Da dodate liniju trenda seriji podataka:
1. Aktivirajte grafikon izgrađen na osnovu serije podataka, tj. klik-
unutar oblasti grafikona. Dia-
2. Nakon klika na ovu stavku, na ekranu će se pojaviti meni u kojem treba izabrati komandu Dodaj liniju trenda.
zadržite pokazivač miša iznad grafika ucrtanog na niz podataka i kliknite desnim tasterom miša
mišem iu kontekstnom meniju koji se pojavi izaberite komandu Dodaj
linija trenda. Dijaloški okvir Trendline se pojavljuje na ekranu sa proširenim
kartica Tip (slika 1).
Slika 1. Kartica Tip u dijaloškom okviru Trendline Format
3. Izaberite željeni tip linije trenda na kartici Tip (podrazumevano je odabran Linearni tip). Za tip polinoma u polju Degree sljedeće
blow za postavljanje stepena odabranog polinoma.
4. Polje Iscrtano na seriji navodi sve serije podataka dotičnog grafikona. Da biste dodali liniju trenda određenoj seriji podataka, odaberite njeno ime u polju Iscrtano na seriji.
5. Ako je potrebno, odlaskom na karticu Parameters (Sl. 2), možete
trend set sledeće parametre:
∙ Promijenite naziv linije trenda u polju Naziv aproksimativnog polja
(zaglađena) kriva;
∙ Podesite broj perioda (unaprijed ili unazad) za prognozu u Pro-
∙ U području dijagrama prikazati jednadžbu linije trenda za koju je potrebno
Označite potvrdni okvir za opciju "prikaži jednadžbu na dijagramu".
∙ Prikaz vrijednosti pouzdanosti aproksimacije R 2 u području dijagrama,
za koje biste trebali označiti okvir za opciju "stavi na dijagram
identitet pouzdanosti aproksimacije (R ^ 2)".
∙ Postavite tačku preseka linije trenda sa Y osom, za koju treba da postavite
Označite okvir za opciju "presjek krivulje sa y-osom u tački:". 6. Pritisnite tipku OK.
sl. 2. Kartica Opcije dijaloškog okvira Trendline
Da biste uredili već izgrađenu liniju trenda, trebate:
1. Kliknite lijevu tipku miša na liniju trenda koju želite
promijeniti.
2. Pritisnite taster Format u glavnom meniju i kontekstni meni koji se pojavljuje
nude izaberite komandu Selected trend line.
Stavke 1-2 su takođe jednostavne za implementaciju sledeći trik: poslati
Pokazivačem miša na grafikon linije trenda, kliknite desnim tasterom miša i u kontekstnom meniju koji se pojavi izaberite naredbu Formatiraj liniju trenda.
Stavke 1-2 je još lakše implementirati: dvostrukim klikom na lijevu tipku miša na grafikonu linije trenda.
3. Na ekranu će se pojaviti okvir za dijalog Trendline Format (Slika 3) koji sadrži
koji sadrži tri kartice: Pogled, Vrsta, Parametri, a sadržaj kartica je Tip,
Parametri se u potpunosti poklapaju sa sličnim karticama dijaloškog okvira.
na liniji trenda (sl. 1–2).
4. Ako je potrebno, odlaskom na karticu View (slika 3), možete podesiti vrstu linije, njenu boju i debljinu za liniju trenda.
5. Pritisnite tipku OK.
Da biste izbrisali već izgrađenu liniju trenda, odaberite liniju trenda koju želite izbrisati i pritisnite tipku Delete.
Prednosti ovog alata za regresionu analizu su:
∙ relativna lakoća crtanja linija trenda na grafikonima bez kreiranja
tablice podataka za to;
∙ prilično široka lista tipova predloženih linija trenda, a ova lista uključuje najčešće korištene regresije;
∙ sposobnost da se nasumično predvidi ponašanje procesa koji se proučava
slobodan (u granicama zdravog razuma) broj koraka naprijed, kao i nazad;
∙ sposobnost dobijanja jednadžbe linije trenda u analitičkom obliku;
∙ mogućnost, ako je potrebno, da se dobije procena pouzdanosti
aproksimacija podataka.
Nedostaci uključuju sljedeće tačke:
∙ izgradnja linije trenda se izvodi samo ako postoji dijagram izgrađen na više podataka;
∙ proces formiranja serija podataka za istraživanje je donekle pretrpan
moje karakteristike zasnovane na jednadžbi linija trenda koje su dobijene za to
da, budući da se koeficijenti ovih jednačina sa svakom promjenom vrijednosti serije
da podaci se preračunavaju, ali samo unutar područja grafikona;
∙ u izvještajima zaokretnog grafikona kada promijenite prikaz grafikona ili povezanog izvještaja pivot table postojeće linije trenda nisu sačuvane,
odnosno prije crtanja linija trenda ili drugog formatiranja izvještaja, sažetka
dijagrama, trebali biste se pobrinuti da izgled izvještaja zadovoljava traženo
zahtjevi.
Slika 3. Kartica Prikaz dijaloškog okvira Format linije trenda
Linije trenda se mogu koristiti za dopunu niza podataka prikazanih na grafikonu.
fikovi, histogrami, ravni nenormalizirani grafikoni sa područjima, ili
nude, scatter, balon i berzanske grafikone.
Nemoguće je dodati linije trenda serijama podataka na volumetrijske, normalizirane
Kupatila, radarski, pita i krofni grafikoni. Prilikom promjene vrste dija-
grama na jedan od gore navedenih, a kada promijenite prikaz izvješća zaokretnog grafikona ili povezanog izvještaja izvedene tabele, odgovarajući
linije trenda koje odgovaraju podacima bit će izgubljene.
Korištenje ugrađenih Excel funkcija
Excel takođe pruža alat za regresijsku analizu za crtanje linija trenda izvan područja grafikona. Brojne statističke funkcije radnog lista mogu se koristiti za ovu svrhu, ali sve one dozvoljavaju samo linearne ili eksponencijalne regresije.
Excel ima nekoliko opcija za konstruisanje linearne regresije (da li
neuronski trend), posebno:
∙ korištenje funkcije TREND;
∙ korištenje funkcije LINEST;
∙ sa funkcijama TILT i INTERCEPT.
Excel također ima nekoliko opcija za izgradnju eksponencijalne linije trenda, posebno:
∙ korištenje funkcije GROWTH;
∙ koristeći funkciju LGRFPRIBL.
Treba napomenuti da tehnike za konstruisanje regresije koriste funkcije
TREND i RAST su skoro isti. Isto se može reći i za par funkcija LINEST i LGRFPRIBL. Za sve ove četiri funkcije, prilikom kreiranja tabele vrednosti, koriste se Excel funkcije kao što su formule niza, što donekle otežava proces pravljenja regresije.
ovo. Napominjemo da je konstrukciju (kreiranje) linearne regresije, po našem mišljenju, najlakše izvesti pomoću funkcija SLOPE i INTERCEPT,
gde prvi definiše nagib linearna regresija, a drugi
raj - segment odsječen regresijom na ordinatnoj osi.
Prednosti ovog alata za regresionu analizu su:
∙ prilično jednostavan proces istog tipa formiranja nizova podataka je
sljedeća karakteristika za sve ugrađene statističke funkcije,
davanje linija trenda;
∙ standardna tehnika za konstruisanje linija trenda na osnovu generisanih serija podataka;
∙ sposobnost predviđanja ponašanja procesa koji se proučava ako je potrebno
broj koraka naprijed ili nazad.
Nedostaci ovog alata uključuju činjenicu da Excel nema ugrađene funkcije za kreiranje drugih (osim za linearne i eksponencijalne).
različite) vrste linija trenda. Ova okolnost često ne dopušta odabir dovoljno preciznog modela pomoću gore navedenih ugrađenih funkcija.
del istraživanog procesa, kao i za dobijanje prognoza bliskih stvarnosti.
Također, kada se koriste funkcije TREND i GROWTH, jednadžbe linije trenda nisu poznate.
tyi - on konkretnim primjerima pokazati mogućnosti Excel paketa prilikom odlučivanja
nii problemi aproksimacije; pokazati koliko su alati efikasni
Excel ima alate za konstruisanje regresija i predviđanja; proil-
Da ilustruje kako relativno lako takve probleme može riješiti čak i korisnik koji nema duboko znanje o regresijskoj analizi.
Metodologija predložena u članku za ovladavanje vještinama rješavanja sredstava
mi Excel ove vrste problema (vidi i gdje su metode za rješavanje sistema linearnih algebarskih jednadžbi, nelinearne jednadžbe,
problemi optimizacije, problemi transporta) mogu biti korisni i zanimljivi korisnicima. To je zbog činjenice da je Excel instaliran na skoro svakom modernom računaru, dok su takvi poznati specijalizovani
matematički paketi kao što su Mathematica, Maple, Matlab, Mathcad,
sa snažnijim mogućnostima za konstruisanje regresija i
predviđanja koriste znatno manje prilagođene revizije
Ispod su rješenja za specifične probleme pomoću navedenih alata u Excel paketu.
Cilj 1. Za tabelu podataka o dobiti autotransportnog preduzeća za 1995-2002. potrebno je da uradite sledeće.
(Obratite pažnju na dodatni odjeljak od 04.06.2017. na kraju članka.)
Računovodstvo i kontrola! Oni stariji od 40 godina treba dobro da upamte ovu parolu iz doba izgradnje socijalizma i komunizma u našoj zemlji.
Ali bez dobro uspostavljenog računovodstva, efikasno funkcionisanje ni države, ni regiona, ni preduzeća, ni domaćinstvo u bilo kojoj društveno-ekonomskoj formaciji društva! Za izradu prognoza i planova aktivnosti i razvoja potrebni su početni podaci. Gdje ih nabaviti? Samo jedan pouzdan izvor je vaš statistički podaci iz prethodnih perioda.
Vode računa o rezultatima svojih aktivnosti, prikupljaju i evidentiraju informacije, obrađuju i analiziraju podatke, primjenjuju rezultate analize na usvajanje ispravne odluke u budućnosti treba, po mom shvatanju, svaka zdrava osoba. Ovo nije ništa drugo do akumulacija i racionalna upotreba vašeg životnog iskustva. Ako ne vodite evidenciju važnih podataka, onda ćete ih nakon određenog vremenskog perioda zaboraviti i, počevši ponovo da se bavite ovim problemima, ponovo ćete praviti iste greške koje ste napravili kada ste to prvi put uradili.
„Sjećam se da smo prije 5 godina pravili i do 1000 komada takvih artikala mjesečno, a sada jedva da skupimo 700!“. Otvaramo statistiku i vidimo da prije 5 godina nije proizvedeno 500 komada...
“Koliko pređe kilometar vašeg automobila, uzimajući u obzir od svega troškovi?" Statistika otvaranja - 6 rubalja / km. Putovanje na posao - 107 rubalja. Jeftinije od taksija (180 rubalja) više od jedan i po puta. A bilo je trenutaka kada je taksi bio jeftiniji...
"Koliko je vremena potrebno za proizvodnju metalnih konstrukcija za komunikacijski toranj visok 50 m?" Otvaramo statistiku - i za 5 minuta odgovor je spreman...
"Koliko će koštati renoviranje sobe u stanu?" Podižemo staru evidenciju, vršimo prilagođavanje inflaciji proteklih godina, uzimamo u obzir da smo zadnji put kupovali materijale 10% jeftinije tržišnu cijenu i - već znamo procijenjenu cijenu...
Vođenje evidencije o vašim profesionalna aktivnost, uvek ćete biti spremni da odgovorite na šefovo pitanje: "Kada !!! ???". Vodeći evidenciju o domaćinstvu, lakše je planirati troškove za velike kupovine, odmore i druge troškove u budućnosti, preduzimajući odgovarajuće mjere za dodatnu zaradu ili smanjenje nepotrebnih troškova danas.
U ovom članku sam na jednostavan primjer Pokazat ću kako možete obraditi prikupljene statističke podatke u Excel-u za mogućnost daljeg korištenja u predviđanju budućih perioda.
Aproksimacija statističkih podataka u Excelu analitičkom funkcijom.
U proizvodnom pogonu se izrađuju građevinske metalne konstrukcije od valjanog lima i profila. Sajt radi stabilno, narudžbe su iste vrste, broj radnika neznatno varira. Postoje podaci o proizvodnji proizvoda za prethodnih 12 mjeseci io količini valjanog metala prerađenog u tim vremenskim periodima po grupama: limovi, I-grede, kanali, uglovi, okrugle cijevi, profili. pravougaonog presjeka, okrugli valjani proizvodi. Nakon preliminarne analize početnih podataka, pretpostavljeno je da ukupna mjesečna proizvodnja metalnih konstrukcija značajno ovisi o broju uglova u narudžbi. Provjerimo ovu pretpostavku.
Prije svega, nekoliko riječi o aproksimaciji. Tražićemo zakon - analitičku funkciju, odnosno funkciju, dato jednačinom, koji bolje od ostalih opisuje ovisnost ukupne proizvodnje metalnih konstrukcija o broju uglova u izvršenim narudžbama. Ovo je aproksimacija, a pronađena jednadžba se zove aproksimirajuća funkcija za originalnu funkciju, data u obliku tabele.
1. Uključite Excel i postavite tabelu sa statističkim podacima na list.
2. Zatim gradimo i formatiramo dijagram raspršivanja, u kojem postavljamo vrijednosti argumenta duž X osi - broj obrađenih uglova u tonama. Na osi Y iscrtavamo vrijednosti izvorne funkcije - ukupne proizvodnje metalnih konstrukcija mjesečno, kako je navedeno u tabeli.
3. “Prelazimo” mišem preko bilo koje tačke na grafikonu i kliknemo desnim tasterom miša da bismo otvorili kontekstni meni (kao što kaže jedan moj dobar prijatelj – kada radite u nepoznatom programu, kada ne znate šta da radite, češće kliknuti desnom tipkom miša...). U padajućem izborniku odaberite "Dodaj liniju trenda...".
4. U prozoru "Trendline" koji se pojavi, na kartici "Type" odaberite "Linear".
6. Na grafikonu se pojavila ravna linija koja aproksimira našu tabelarnu zavisnost.
Pored same linije, vidimo jednačinu ove linije i, što je najvažnije, vidimo vrijednost parametra R 2 - veličinu pouzdanosti aproksimacije! Što je njena vrijednost bliža 1, odabrana funkcija točnije aproksimira tabelarne podatke!
7. Mi gradimo linije trenda koristeći stepene, logaritamske, eksponencijalne i polinomske aproksimacije, slično kao što smo izgradili linearnu liniju trenda.
Najbolje od svih odabranih funkcija aproksimira naše podatke polinomom drugog stepena, ima maksimalni faktor pouzdanosti R 2.
Međutim, želim da vas upozorim! Ako uzmete polinome viših stupnjeva, možda ćete dobiti još jedan vrhunski rezultati ali krive će biti zamršene... Ovdje je važno razumjeti da tražimo funkciju koja ima fizičko značenje... Šta to znači? To znači da nam je potrebna aproksimirajuća funkcija koja će dati adekvatne rezultate ne samo unutar razmatranog raspona vrijednosti X, već i izvan njega, odnosno da će odgovoriti na pitanje: „Kakav će biti izlaz metalnih konstrukcija ako se broj uglova obrađenih mjesečno je manje od 45, a više od 168 tona!" Stoga ne preporučujem da se zanosite polinomima visokog stepena i da pažljivo birate parabolu (polinom drugog stepena)!
Dakle, moramo odabrati funkciju koja ne samo da dobro interpolira tabelarne podatke unutar raspona vrijednosti X = 45 ... 168, već i omogućava adekvatnu ekstrapolaciju izvan ovog raspona. U ovom slučaju biram logaritamsku funkciju, iako vi možete odabrati linearnu, kao najjednostavniju. U ovom primjeru, prilikom odabira linearne aproksimacije u excelu, greške će biti veće nego kod odabira logaritamske, ali ne za mnogo.
8. Uklonite sve linije trenda iz polja grafikona, osim logaritamske funkcije. Da biste to učinili, desnom tipkom miša kliknite nepotrebne linije i odaberite "Obriši" u padajućem kontekstnom izborniku.
9. Na kraju, dodajte trake grešaka u tabelarne podatke. Da biste to uradili, kliknite desnim tasterom miša na bilo koju tačku na grafikonu i u kontekstualnom meniju izaberite "Format data series..." i podesite podatke na kartici "Y-errors" kao na slici ispod.
10. Zatim desnom tipkom miša kliknemo na bilo koju od linija raspona grešaka, u kontekstnom izborniku odaberemo "Formatiraj trake grešaka ..." i u prozoru "Formatiranje traka grešaka" na kartici "Prikaz" postavimo boju i debljine linija.
Svi drugi objekti grafikona formatirani su na isti način.Excel!
Konačni rezultat dijagrama prikazan je na sljedećem snimku ekrana.
Rezultati.
Rezultat svih prethodnih radnji bila je rezultirajuća formula za aproksimirajuću funkciju y = -172,01 * ln (x) +1188,2. Poznavajući to, kao i broj uglova u mesečnom skupu radova, moguće je sa velikim stepenom verovatnoće (± 4% - vidi trake grešaka) predvideti ukupnu proizvodnju metalnih konstrukcija za mesec dana! Na primjer, ako u mjesečnom planu ima 140 tona uglova, onda će ukupna proizvodnja, najvjerovatnije, uz ostale jednake stvari, biti 338 ± 14 tona.
Da bi se povećala pouzdanost aproksimacije statističkih podataka, trebalo bi da ih ima mnogo. Dvanaest parova vrijednosti nije dovoljno.
Iz prakse ću reći da pronalaženje aproksimativne funkcije sa faktorom pouzdanosti R 2> 0,87 treba smatrati dobrim rezultatom. Odličan rezultat- kada je R 2> 0,94.
U praksi može biti teško izdvojiti jedan najvažniji odlučujući faktor (u našem primjeru, masa uglova obrađenih za mjesec dana), ali ako pokušate, uvijek ga možete pronaći u svakom konkretnom zadatku! Naravno, ukupni mjesečni učinak zaista ovisi o stotinama faktora, koji zahtijevaju značajne troškove rada za postavljače standarda i druge stručnjake. Samo će rezultat i dalje biti približan! Dakle, da li je vrijedno troška ako postoji mnogo jeftinije matematičko modeliranje!
U ovom članku samo sam dotaknuo vrh ledenog brijega koji se zove prikupljanje, obrada i praktična upotreba statistički podaci. Bez obzira da li sam uspeo da pobudim vaše interesovanje za ovu temu, nadam se da ću saznati iz komentara i ocene članka na pretraživačima.
Postavljeno pitanje aproksimacije funkcije jedne varijable ima široku širinu praktična upotreba v različitim oblastimaživot. Ali rješenje problema aproksimacije funkcije je od mnogo veće koristi nekoliko nezavisnih varijable…. Pročitajte o ovome i više u sljedećim člancima na blogu.
Pretplatite se za najave članaka u prozoru koji se nalazi na kraju svakog članka ili u prozoru na vrhu stranice.
Nemoj zaboraviti potvrditi pretplatite se klikom na link u pismu koje će vam doći na navedenu poštu (može doći u folder « Neželjena pošta » )!!!
Sa zanimanjem ću čitati vaše komentare, dragi čitaoci! Pisati!
P.S. (04.06.2017.)
Vrlo precizna, lijepa zamjena za tabelarne podatke sa jednostavnom jednadžbom.
Niste zadovoljni dobijenom preciznošću aproksimacije (R 2<0,95) или вид и набор функций, предлагаемые MS Excel?
Da li veličina izraza i oblik linije aproksimirajućeg polinoma visokog stepena ne prijaju oku?
Kontaktirajte preko stranice "" za precizniji i kompaktniji rezultat aproksimacije vaših tabelarnih podataka i kako biste naučili jednostavnu tehniku rješavanja problema visoke preciznosti aproksimacije funkcijom jedne varijable.
Korištenjem predloženog algoritma akcija pronađena je vrlo kompaktna funkcija koja pruža najveću tačnost aproksimacije: R 2 = 0,9963 !!!