Ecuația liniei de tendință. Versiune profesională: selectarea unei linii de tendință și setarea parametrilor. Determinarea coeficienților de model
Tendință imediat după introducerea datelor disponibile în matrice. Pentru a face acest lucru, pe foaia cu tabelul de date, selectați cel puțin două celule din intervalul pentru care va fi construit graficul și imediat după aceea introduceți diagrama. Puteți utiliza astfel de tipuri de diagrame precum grafic, scatter, histogramă, bubble, stoc. Alte tipuri de diagrame nu acceptă funcția de tendințe.
Din meniul Diagramă, selectați Adăugare linie de tendință. În fereastra care se deschide, în fila „Tip”, selectați tipul dorit de linie de tendință, care în echivalent matematic înseamnă și metoda de aproximare a datelor. Când utilizați metoda descrisă, va trebui să o faceți „cu ochi”, deoarece Nu calcule matematice nu ai făcut nicio cercetare pentru a construi un grafic.
Prin urmare, trebuie să vă dați seama ce tip de funcție îi corespunde cel mai bine graficul datelor disponibile: liniară, logaritmică, exponențială sau altele. Dacă aveți îndoieli cu privire la alegerea tipului de aproximare, puteți desena mai multe linii, iar pentru o mai mare acuratețe a prognozei, în fila „Parametri” din aceeași fereastră, bifați caseta „plasați valoarea de fiabilitate a aproximării (R^2) pe diagrama.”
Compararea valorilor R^2 pentru linii diferite, puteți alege tipul de grafic care vă caracterizează datele cel mai precis și, prin urmare, construiește cea mai fiabilă prognoză. Cu cât valoarea R^2 este mai aproape de unu, cu atât ați ales mai precis tipul de linie. Aici, în fila „Parametri”, trebuie să specificați perioada pentru care se face prognoza.
Această metodă de a construi o tendință este foarte aproximativă, deci este mai bine să faceți cel puțin cel mai primitiv prelucrare statistică datele disponibile. Acest lucru vă va permite să construiți o prognoză mai precisă.
Dacă presupuneți că datele disponibile sunt descrise ecuație liniară, pur și simplu le selectați cu cursorul și completați automat pentru numărul necesar de perioade sau numărul de celule. ÎN în acest caz, nu este nevoie să găsim valoarea lui R^2, deoarece ați adaptat anterior prognoza la ecuația unei linii drepte.
Dacă credeți că valorile cunoscute ale unei variabile pot fi descrise cel mai bine folosind o ecuație exponențială, selectați, de asemenea, intervalul original și completați automat cantitatea necesară celule în timp ce țin tasta dreapta soareci. Utilizând completarea automată, nu veți putea construi alte tipuri de linii în afară de cele două indicate.
Prin urmare, pentru cea mai mare acuratețe în realizarea unei prognoze, va trebui să utilizați una dintre mai multe functii statistice: „PREDICȚI”, „TENDINȚĂ”, „CREȘTERE”, „LINIE” sau „LGRFPRIBL”. În acest caz, va trebui să calculați manual valoarea pentru fiecare perioadă de prognoză ulterioară. Dacă trebuie să efectuați o analiză de regresie mai complexă asupra datelor dvs., veți avea nevoie de suplimentul Analysis Pack, care nu este inclus în instalare standard MS Office.
Ceea ce face diferită efectuarea sarcinilor pentru a construi o linie de tendință este faptul că datele sursă pot fi un set de numere care nu au legătură între ele.
Prognoza folosind un program regulat este imposibilă, deoarece coeficientul său de determinism (R^2) va fi aproape de zero.
Acesta este motivul pentru care se folosesc funcții speciale.
Acum le vom construi, configura și analiza.
Versiune de construcție ușoară
Procesul de construire a unei linii de tendință constă în trei etape: introducerea datelor inițiale în Excel, trasarea unui grafic, selectarea unei linii de tendință și a parametrilor acesteia.
Să începem cu introducerea datelor.
1. Creați în Foaie de calcul Excel cu datele originale.
(Imaginea 1)
2. Selectați celulele B3:B17 și mergeți la fila „Insert” și selectați „Graph”.
(Figura 2)
3. Odată ce graficul este creat, puteți adăuga subtitrări și un titlu.
Mai întâi, faceți clic stânga pe marginea graficului pentru a-l selecta.
Apoi accesați fila „Design” și selectați „Layout 1”.
(Figura 3)
4. Să trecem la construirea unei linii de tendințe. Pentru a face acest lucru, selectați din nou diagrama și accesați fila „Aspect”.
(Figura 4)
5. Faceți clic pe butonul „Linie de tendință” și selectați „potrivire liniară” sau „potrivire exponențială”.
(Figura 5)
Așa am construit o linie de tendință primară, care poate să nu corespundă prea mult cu realitatea.
Acesta este rezultatul nostru intermediar.
(Figura 6)
Și, prin urmare, va trebui să ajustam parametrii liniei noastre de tendință sau să selectăm o altă funcție.
Versiune profesională: selectarea unei linii de tendință și setarea parametrilor
6. Faceți clic pe butonul „Linie de tendință” și selectați „Parametri suplimentari și linii de tendință”.
(Figura 7)
7. În fereastra „Format linie de tendință”, bifăm caseta de lângă „plasați valoarea de fiabilitate a aproximării R^2 pe diagramă și faceți clic pe butonul „închidere”.
Vedem în diagramă coeficientul R^2= 0,6442
(Figura 8)
8. Anulați modificările. Selectați graficul, faceți clic pe fila „Aspect”, butonul „Linie de tendință” și selectați „Nu”.
9. Accesați fereastra „Format linie de tendință”, dar pentru a selecta linia de tendință „Polinom”, schimbăm gradul, realizând indicatorii de coeficienți R^2 = 0,8321
(Figura 9)
Prognoza
Dacă trebuie să ghicim ce date ar putea fi obținute în următoarea măsurare, în fereastra „Format linie tendință”, indicăm numărul de perioade pentru care se face prognoza.
(Figura 10)
Pe baza prognozei, putem presupune că pe 25 ianuarie numărul de puncte marcate ar fi cuprins între 60 și 70.
Concluzie
Și, în sfârșit, dacă sunteți interesat de formula prin care este construită tendința, în caseta de selectare „Format de linie de tendință” de lângă „afișați ecuația pe diagramă”.
Acum știți cum să finalizați sarcina și să construiți o linie de tendințe, chiar și într-un program precum Excel 2010.
Nu ezitați să puneți întrebări.
Grafic
Analiza regresiei
Ecuația de regresie Y din X numit dependenta functionala y=f(x), iar graficul său este o dreaptă de regresie.
Excel vă permite să creați diagrame și grafice destul de calitate acceptabilă. Excel disponibil remediu special- Chart Wizard, care ghidează utilizatorul prin toate cele patru etape ale procesului de creare a unei diagrame sau a unui grafic.
De regulă, trasarea începe prin selectarea unui interval care conține datele pe care ar trebui să fie reprezentată. Acest început simplifică progresul în continuare al complotării. Cu toate acestea, intervalul cu datele originale poate fi împărțit în a doua etapă a dialogului cu DIAGRAMĂ MASTER. În Excel 2003 DIAGRAMĂ MASTER situat în meniu ca un buton sau o diagramă poate fi creat făcând clic pe filă INTRODUCE iar în lista care se deschide găsiți articolul DIAGRAMĂ.În Excel 2007 găsim și fila INTRODUCE(Fig. 31).
Orez. 31. DIAGRAMĂ MASTERîn Excel 2007
Cel mai simplu mod este de a selecta o serie de date sursă în care aceste date sunt situate în rândurile adiacente (coloane sau rânduri) - trebuie să faceți clic pe celula din stânga sus a intervalului și apoi să trageți cursorul mouse-ului în celula din dreapta jos a intervalul. Când selectați date situate pe rânduri neadiacente, trageți cursorul mouse-ului de-a lungul rândurilor selectate în timp ce apăsați Tasta Ctrl. Dacă una dintre seriile de date are o celulă cu un nume, seria selectată rămasă trebuie să aibă și o celulă corespunzătoare, chiar dacă aceasta este goală.
Pentru analiza regresiei Cel mai bine este să utilizați o diagramă Scatter (Fig. 30). Cu ea construirea Excel percepe primul rând al intervalului selectat de date sursă ca un set de valori ale argumentelor funcțiilor ale căror grafice trebuie să fie reprezentate (același set pentru toate funcțiile). Următoarele rânduri sunt percepute ca seturi de valori ale funcțiilor în sine (fiecare rând conține valorile uneia dintre funcțiile corespunzătoare valori date argument situat în primul rând al intervalului selectat).
În Excel 2007, numele axelor sunt plasate în fila meniu DISPOSARE(Fig. 32).
Orez. 32. Setarea numelor axelor graficului în Excel 2007
Pentru a obține un model matematic, este necesar să trasați o linie de tendință pe grafic. În Excel 2003 și 2007 trebuie să faceți clic Click dreapta mouse-ul pe punctele graficului. Apoi în Excel 2003 va apărea o filă cu o listă de articole din care selectăm ADĂUGAȚI LINIA DE TENDINȚĂ(Fig. 33).
Orez. 33. ADĂUGAȚI LINIA DE TENDINȚĂ
După ce faceți clic pe element ADĂUGAȚI LINIA DE TENDINȚĂ va apărea o fereastră LINIA DE TENDINȚĂ(Fig. 34). În fila TIP puteți selecta următoarele tipuri linii: liniare, logaritmice, exponențiale, de putere, polinom, filtrare liniară.
Orez. 34. Fereastra LINIA DE TENDINȚĂîn Excel 2003
În filă OPȚIUNI(Fig. 35) bifați caseta de lângă articolele SHOW EQUATION ON DIAGRAM, apoi va apărea pe grafic model matematic această dependență. De asemenea, punem o casetă de selectare lângă articol ARĂTAȚI PE SCHEMA VALOAREA FIABILITĂȚII APROXIMĂRII (R^2). Cu cât valoarea fiabilității aproximării este mai aproape de 1, cu atât curba selectată se apropie mai mult de punctele din grafic. Apoi, faceți clic pe butonul Bine. Pe grafic vor apărea o linie de tendință, ecuația corespunzătoare și valoarea fiabilității aproximării.
Orez. 35. Tab OPȚIUNI
În Excel 2007, după ce facem clic dreapta pe punctele graficului, apare o listă de elemente de meniu, din care SELECTAȚI ADĂUGAȚI LINIA DE TENDINȚĂ(Fig. 36).
Orez. 36. ADĂUGAȚI LINIA DE TENDINȚĂ
Orez. 37. Tab PARAMETRI DE LINIE DE TENDINȚĂ
Bifați casetele necesare și apăsați butonul ÎNCHIDE.
Pe grafic vor apărea o linie de tendință, ecuația ei corespunzătoare și valoarea fiabilității aproximării.
Pentru a prezice un eveniment pe baza datelor existente, dacă nu există timp, puteți utiliza o linie de tendință. Folosind-o, puteți înțelege vizual ce dinamică au datele din care este construit graficul. Pachetul de software Microsoft are un minunat Capacitate Excel, care vă va ajuta să creați o prognoză destul de precisă folosind acest instrument - linie de tendință în Excel. Construirea acestui instrument de analiză este destul de simplă, este prezentată mai jos descriere detaliata proces și tipuri de linii de tendință.
Linia de tendință în Excel. Procesul de construcție
O linie de tendință este unul dintre principalele instrumente de analiză a datelor
Pentru a forma o linie de tendință, este necesar să parcurgeți trei etape, și anume:
1. Creați un tabel;
2.
3. Selectați tipul de linie de tendință.
După ce a strâns toate informatie necesara, puteți trece direct la pașii spre obținerea rezultatului final.
Mai întâi, ar trebui să creați un tabel cu datele sursă. Apoi, selectați intervalul necesar și mergeți la fila „Inserare” și selectați funcția „Graph”. După construcție, pe rezultat final poate fi aplicat Caracteristici suplimentare, sub formă de titluri și legende. Pentru a face acest lucru, pur și simplu faceți clic stânga pe diagramă, selectați fila numită „Designer” și selectați „Aspect”. Următorul lucru pe care trebuie să-l faceți este pur și simplu să introduceți titlul.
Următorul pas este construirea liniei de tendință în sine. Așadar, pentru a face acest lucru, trebuie să evidențiați din nou diagrama și să selectați fila „Aspect” din panglica sarcinii. Următorul în acest meniu trebuie să faceți clic pe butonul „Linie de tendință” și să selectați „aproximare liniară” sau „aproximare exponențială”.
Diverse l variațiilinii de tendință
În funcție de caracteristicile datelor introduse de utilizator, merită să alegeți una dintre opțiunile prezentate mai jos este o descriere a tipurilor de linii de tendință
Aproximație exponențială. Dacă rata de modificare a datelor de intrare crește și în mod continuu, atunci este această linie va fi cel mai util. Cu toate acestea, dacă datele care au fost introduse în tabel conțin zero sau caracteristici negative, acest tip inacceptabil.
Aproximație liniară. Această linie este de natură dreaptă și este de obicei folosită în cazuri elementare când funcția crește sau scade la o viteză aproximativ constantă.
Aproximație logaritmică. Dacă valoarea inițial crește corect și rapid sau, dimpotrivă, scade, dar apoi, după o anumită valoare, se stabilizează, atunci această linie de tendință va fi utilă.
Aproximație polinomială. Creșterea și scăderea variabile sunt caracteristicile care sunt caracteristice acestei linii. Mai mult, gradul polinoamelor (polinoamelor) în sine este determinat de numărul de maxime și minime.
Aproximarea puterii. Caracterizează creșterea și scăderea monotonă a unei valori, dar aplicarea acesteia este imposibilă dacă datele au valori negative și zero.
Media mobilă. Este folosit pentru a arăta în mod clar dependența directă a unuia de celălalt, prin netezirea tuturor punctelor de oscilație. Acest lucru se realizează prin luarea valorii medii între două puncte adiacente. Astfel, graficul este mediat, iar numărul de puncte este redus la valoarea care a fost selectată în meniul „Puncte” de către utilizator.
Modul în care este folosit? D Pentru a prezice opțiuni economice, este exact polinom o linie al cărei grad polinom se determină pe baza mai multor principii: maximizarea coeficientului de determinare, precum și a dinamicii economice a indicatorului în perioada pentru care se cere prognoza.
Urmând toate etapele de formare și înțelegerea caracteristicilor, puteți construi doar o linie de tendință primară, care corespunde doar vag previziunilor reale. Dar după setarea parametrilor, putem vorbi deja despre o imagine mai realistă a prognozei.
Linia de tendință în Excel. Setările parametrilorîn linia funcţională
Făcând clic pe butonul „Linie de tendință”, selectați meniul necesar numit „ Opțiuni suplimentare" În fereastra care apare, faceți clic pe „Format de linie de tendință”, apoi bifați caseta de lângă valoarea „plasați valoarea de fiabilitate a aproximării R^2 pe diagramă”. După aceea, închideți meniul făcând clic pe butonul corespunzător. Pe diagrama propriu-zisă, apare coeficientul R^2 = 0,6442.
După aceasta, anulăm modificările făcute. Selectând diagrama și făcând clic pe fila „Layout”, apoi faceți clic pe „Trend line” și faceți clic pe „Nu”. Apoi, mergând la funcția „Format linie de tendință”, faceți clic pe polinom linie și încercați să obțineți valoarea R^2= 0,8321 prin modificarea gradului.
Pentru a vizualiza formule sau pentru a crea alte variații de prognoză care diferă de cele standard, este suficient să nu vă fie frică să experimentați cu valori și mai ales cu polinoame. Astfel, folosind doar unul programul Excel, puteți crea o prognoză destul de precisă pe baza datelor de intrare.
Prognoza este un element foarte important în aproape orice domeniu de activitate, de la economie la inginerie. Există un numar mare de software, specializată în acest domeniu. Din păcate, nu toți utilizatorii știu asta de obicei procesor de masă Excel are în arsenal instrumente pentru efectuarea prognozelor, care nu sunt cu mult inferioare în eficacitatea lor programe profesionale. Să aflăm care sunt aceste instrumente și cum să facem o prognoză în practică.
Scopul oricărei prognoze este identificarea trendul curent, și determinarea rezultatului așteptat în raport cu obiectul studiat la un anumit moment în timp în viitor.
Metoda 1: linia de tendință
Unul dintre cele mai populare tipuri de prognoză grafică în Excel este extrapolarea realizată prin construirea unei linii de tendință.
Să încercăm să prezicem valoarea profitului întreprinderii în 3 ani pe baza datelor despre acest indicator pentru ultimii 12 ani.
Metoda 2: operator FORECAST
Extrapolarea datelor tabelare se poate face folosind functie standard excela PREDICȚIE. Acest argument este clasificat ca instrument statistic și are următoarea sintaxă:
PREDICȚIE(X, valori_cunoscute_y, valori_cunoscute_x)
"X" este un argument pentru care trebuie determinată valoarea funcției. În cazul nostru, argumentul va fi anul pentru care ar trebui făcută prognoza.
„Valori y cunoscute”— o bază de valori cunoscute ale funcției. În cazul nostru, rolul său este jucat de valoarea profitului din perioadele anterioare.
„Valori cunoscute ale lui x”— acestea sunt argumentele care corespund valorilor cunoscute ale funcției. În rolul lor, folosim numerotarea anilor pentru care au fost colectate informații despre profiturile anilor anteriori.
Desigur, argumentul nu trebuie să fie neapărat o perioadă de timp. De exemplu, poate fi temperatura, iar valoarea funcției poate fi nivelul de expansiune al apei atunci când este încălzită.
Când se calculează în acest mod, se utilizează metoda regresiei liniare.
Să ne uităm la nuanțele utilizării operatorului PREDICȚIE pe exemplu concret. Să luăm aceeași masă. Va trebui să cunoaștem prognoza de profit pentru 2018.
Dar nu uitați că, la fel ca atunci când construiți o linie de tendință, perioada de timp înainte de perioada de prognoză nu trebuie să depășească 30% din întreaga perioadă pentru care a fost acumulată baza de date.
Metoda 3: operatorul TREND
Pentru prognoză, puteți utiliza încă o funcție - TENDINŢĂ. De asemenea, se încadrează în categoria operatorilor statistici. Sintaxa sa este în multe privințe similară cu sintaxa instrumentului PREDICȚIE si arata cam asa:
TREND(Valori_y cunoscute; valori_x_cunoscute; valori_x_nou; [const])
După cum vedem, argumentele „Valori y cunoscute”Și „Valori cunoscute ale lui x” corespund pe deplin elementelor similare ale operatorului PREDICȚIE, și argumentul „Noi valori x” se potrivește cu argumentul "X" instrumentul anterior. În plus, TENDINŢĂ există un argument suplimentar "Constant", dar nu este obligatoriu și se folosește numai în prezența unor factori constanți.
Acest operator este utilizat cel mai eficient atunci când există o dependență liniară a funcției.
Să vedem cum va funcționa acest instrument cu aceeași matrice de date. Pentru a compara rezultatele obținute, vom defini 2019 ca punct de prognoză.
Metoda 4: operator GROWTH
O altă funcție care poate fi folosită pentru a face prognoze în Excel este operatorul GROWTH. De asemenea, aparține grupului statistic de instrumente, dar, spre deosebire de cele anterioare, în calcul folosește nu o metodă de dependență liniară, ci una exponențială. Sintaxa acestui instrument arată astfel:
CREȘTERE(Valori cunoscute_y; valori cunoscute_x; valori_noi_x; [const])
După cum puteți vedea, argumentele acestei funcții repetă exact argumentele operatorului TENDINŢĂ, așa că nu ne vom opri asupra descrierii lor a doua oară, ci vom trece imediat la utilizarea acestui instrument în practică.
Metoda 5: operator LINEST
Operator LINEST Când calculează, folosește metoda aproximării liniare. Aceasta nu trebuie confundată cu metoda relației liniare utilizată de instrument TENDINŢĂ. Sintaxa sa arată astfel:
LINIE(Valori_y_cunoscute; valori_x_cunoscute; valori_x_nou;[const];[statistici])
Ultimele două argumente sunt opționale. Suntem familiarizați cu primele două metode anterioare. Dar poate ați observat că acestei funcție îi lipsește un argument care indică noile valori. Adevărul este că acest instrument determină doar modificarea sumei veniturilor pe unitate de perioadă, care în cazul nostru este egală cu un an, dar trebuie să calculăm separat rezultatul total adăugând rezultatul calculului operatorului la ultima valoare reală a profitului LINEST, înmulțit cu numărul de ani.
După cum putem vedea, profitul proiectat, calculat prin metoda aproximării liniare, în 2019 va fi de 4.614,9 mii de ruble.
Metoda 6: operator LGRFPRIBL
Ultimul instrument la care ne vom uita va fi LGRFPRIBL. Acest operator efectuează calcule pe baza metodei de potrivire exponențială. Sintaxa sa are următoarea structură:
LGRFPRIBL (Valori cunoscute_y; valori cunoscute_x; valori_nou_x; [const]; [statistici])
După cum puteți vedea, toate argumentele repetă complet elementele corespunzătoare funcția anterioară. Algoritmul de calcul al prognozei se va modifica ușor. Funcția va calcula o tendință exponențială, care va arăta de câte ori se va schimba suma veniturilor într-o perioadă, adică într-un an. Va trebui să găsim diferența de profit dintre ultima perioadă reală și prima perioadă planificată, să o înmulțim cu numărul de perioade planificate (3) și adăugați suma ultimei perioade efective la rezultat.
Valoarea estimată a profitului în 2019, care a fost calculată folosind metoda de aproximare exponențială, va fi de 4639,2 mii de ruble, ceea ce, din nou, nu este foarte diferit de rezultatele obținute prin calculul folosind metodele anterioare.
Am aflat cum să facem prognoze în Excel. Acest lucru se poate face grafic prin utilizarea unei linii de tendință și analitic prin utilizarea întreaga linie funcții statistice încorporate. Prelucrarea datelor identice de către acești operatori poate duce la rezultate diferite. Dar acest lucru nu este surprinzător, deoarece toți folosesc metode diferite calcul. Dacă fluctuația este mică, atunci toate aceste opțiuni aplicabile unui anumit caz pot fi considerate relativ fiabile.
