Pentru ce este o linie de tendință în Excel? Instrumente de prognoză în Microsoft Excel

Complot

Analiza regresiei

Ecuația de regresie Y din X numită dependență funcțională y = f (x), iar graficul său este o dreaptă de regresie.

Excel vă permite să creați diagrame și grafice de o calitate destul de acceptabilă. Excel are un instrument special - Chart Wizard, sub îndrumarea căruia utilizatorul parcurge toate cele patru etape ale procesului de construire a unei diagrame sau a unui grafic.

De regulă, trasarea începe cu evidențierea intervalului care conține datele pe care ar trebui să fie reprezentată. Un astfel de început simplifică cursul ulterior al complotării. Cu toate acestea, intervalul cu datele originale poate fi împărțit în a doua etapă a dialogului cu MAESTRU DE DIAGRAME... În Excel 2003 MAESTRU DE DIAGRAME 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. MAESTRU DE DIAGRAMEîn Excel 2007

Cel mai simplu mod de a selecta un interval de date sursă în care aceste date sunt în rândurile adiacente (coloane sau rânduri) este 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 intervalului. Atunci când selectați date situate în rânduri necontigue, cursorul mouse-ului este tras de-a lungul rândurilor selectate în timp ce țineți apăsată tasta Ctrl. Dacă una dintre seriile de date are o celulă cu nume, restul rândurilor selectate trebuie să aibă și o celulă corespunzătoare, chiar dacă aceasta este goală.

Cel mai bun mod de a efectua o analiză de regresie este să utilizați o diagramă cu puncte (Figura 30). Când îl construiește, Excel percepe primul rând al intervalului selectat de date sursă ca un set de valori ale argumentului 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 valorilor date ale argumentului situat în primul rând al intervalului selectat).

În Excel 2007, numele axelor sunt plasate în fila meniu DISPOSARE(fig. 32).

Orez. 32. Setarea titlurilor axelor diagramei în Excel 2007

Pentru a obține un model matematic, este necesar să trasați o linie de tendință pe diagramă. În Excel 2003 și 2007, trebuie să faceți clic dreapta 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 TYPE, puteți selecta următoarele tipuri de linii: liniară, logaritmică, exponențială, putere, polinom, filtrare liniară.

Orez. 34. Fereastra LINIA DE TENDINȚĂîn Excel 2003

În fila PARAMETRI(Fig. 35) setați caseta de selectare de lângă elementele AFIȚĂ ECUAȚIA PE DIAGRĂ, apoi modelul matematic al acestei dependențe va apărea pe diagramă. De asemenea, punem o bifă în fața articolului ARĂTAȚI PE SCHEMA VALOAREA FIABILITĂȚII APROXIMĂRII (R ^ 2). Cu cât valoarea de încredere a aproximării este mai aproape de 1, cu atât curba selectată se apropie de punctele din grafic. Apoi, faceți clic pe butonul O.K... Linia de tendință, ecuația corespunzătoare și valoarea fiabilității aproximării vor apărea pe grafic.

Orez. 35. Tab PARAMETRI

În Excel 2007, după clic dreapta pe punctele graficului, apare o listă de elemente de meniu, din care SELECTAREA ADĂUGAȚI LINIE DE TENDINȚĂ(fig. 36).

Orez. 36. ADĂUGAȚI LINIA DE TENDINȚĂ

Orez. 37. Tab PARAMETRI DE LINIE DE TENDINȚĂ

Setați casetele de selectare necesare și faceți clic pe butonul ÎNCHIDE.

Linia de tendință, ecuația corespunzătoare și valoarea fiabilității aproximării vor apărea pe grafic.

Privind orice set de date distribuit de-a lungul timpului (serie temporală), putem determina vizual scăderile și creșterile indicatorilor pe care îi conține. Modelul de urcușuri și coborâșuri se numește tendință, care poate indica dacă datele noastre sunt în creștere sau în scădere.

Poate, voi începe o serie de articole despre prognoză cu cel mai simplu - construirea unei funcții de trend. De exemplu, să luăm date despre vânzări și să construim un model care descrie dependența vânzărilor de timp.

Noțiuni de bază

Cred că din moment ce la școală toată lumea este familiarizată cu funcția liniară, aceasta se află tocmai în centrul tendinței:

Y (t) = a0 + a1 * t + E

Y este volumul vânzărilor, variabila pe care o vom explica în timp și de care depinde, adică Y (t);

t - numărul perioadei (numărul ordinal al lunii), care explică planul de vânzări Y;

a0 este coeficientul de regresie zero, care arată valoarea lui Y (t), în absența influenței factorului explicativ (t = 0);

a1 - coeficient de regresie, care arată cât de mult depinde indicatorul de vânzări Y studiat de factorul de influență t;

E - perturbații aleatorii care reflectă influența altor factori neluați în considerare în model, cu excepția timpului t.

Construirea modelului

Deci, știm volumul vânzărilor din ultimele 9 luni. Iată care este farfuria noastră:

Următorul lucru pe care trebuie să-l facem este să stabilim coeficienții a0și a1 pentru a prezice volumul vânzărilor pentru luna a 10-a.

Determinarea coeficienților modelului

Construim un program. Pe orizontal vedem lunile amanate, pe verticala volumul vanzarilor:

În Foi de calcul Google, alegeți Editor de diagrame -> Adiţionalși pune o căpușă lângă Linii de tendință... În setări, selectați Eticheta — Ecuațiași Arată R ^ 2.

Dacă faceți totul în MS Excel, faceți clic dreapta pe diagramă și selectați „Adăugați o linie de tendință” în meniul derulant.

În mod implicit, este construită o funcție liniară. În dreapta, selectați „Afișați ecuația pe diagramă” și „Valoarea fiabilității aproximării R ^ 2”.

Iată ce sa întâmplat:

Pe grafic, vedem ecuația funcției:

y = 4856 * x + 105104

Descrie volumul vânzărilor în funcție de numărul lunii pentru care dorim să anticipăm aceste vânzări. În continuare vedem coeficientul de determinare R ^ 2, care vorbește despre calitatea modelului și cât de bine descrie vânzările noastre (Y). Cu cât este mai aproape de 1, cu atât mai bine.

Am R ^ 2 = 0,75. Acesta este un indicator mediu, spune că modelul nu ține cont de alți factori semnificativi în afară de timpul t, de exemplu, poate fi sezonalitate.

Noi prezicem

y = 4856 * 10 + 105104

Avem 153.664 de vânzări luna viitoare. Dacă adăugăm un nou punct la diagramă, vedem imediat că R ^ 2 s-a îmbunătățit.

Astfel, puteți prognoza datele pentru câteva luni înainte, dar fără a ține cont de alți factori, prognoza dvs. se va situa pe linia de tendință și nu va fi atât de informativă pe cât v-ați dori. În plus, prognoza pe termen lung făcută în acest fel va fi foarte aspră.

Puteți îmbunătăți acuratețea modelului adăugând sezonalitate la funcția de tendință, ceea ce vom face în articolul următor.

O linie de tendință este utilizată pentru a ilustra vizual tendințele prețurilor. Elementul analizei tehnice este o imagine geometrică a valorilor medii ale indicatorului analizat.

Să aruncăm o privire la cum să adăugați o linie de tendință la un grafic în Excel.

Adăugarea unei linii de tendință la un grafic

De exemplu, să luăm prețurile medii ale petrolului din 2000 din surse deschise. Vom introduce datele pentru analiză în tabel:

O linie de tendință în Excel este un grafic al unei funcții de aproximare. Pentru ce este - pentru a face prognoze pe baza datelor statistice. În acest scop, este necesar să se extindă linia și să se determine valorile acesteia.

Dacă R2 = 1, atunci eroarea de aproximare este zero. În exemplul nostru, alegerea unei potriviri liniare a dat încredere scăzută și rezultate slabe. Prognoza va fi inexactă.

Atenţie!!! Linia de tendință nu poate fi adăugată la următoarele tipuri de grafice și diagrame:

• petală;
• circular;
• suprafaţă;
• inelar;
• volum;
• stivuite.



Ecuația liniei de tendință în Excel

În exemplul de mai sus, a fost aleasă o aproximare liniară doar pentru a ilustra algoritmul. După cum arată valoarea fiabilității, alegerea nu a avut succes în totalitate.

Ar trebui să alegeți tipul de afișare care ilustrează cel mai bine tendința de modificări în introducerea utilizatorului. Să ne dăm seama care sunt opțiunile.

Aproximație liniară

Imaginea sa geometrică este dreaptă. Prin urmare, o aproximare liniară este utilizată pentru a ilustra o metrică care crește sau descrește la o rată constantă.

Luați în considerare numărul condiționat de contracte încheiate de manager pe parcursul a 10 luni:

Pe baza datelor din tabelul Excel, să construim o diagramă de dispersie (va ajuta la ilustrarea tipului de linie):

Selectați graficul - „adăugați o linie de tendință”. În parametri, selectați tipul liniar. Adăugați valoarea preciziei aproximării și a ecuației liniei de tendință în Excel (trebuie doar să bifați casetele din partea de jos a ferestrei „Parametri”).

Obtinem rezultatul:

Notă! Cu o aproximare de tip liniar, punctele de date sunt situate cât mai aproape de linia dreaptă. Această vizualizare folosește următoarea ecuație:

y = 4,503x + 6,1333

• unde 4,503 este indicatorul de pantă;
• 6,1333 - decalaje;
• y - succesiune de valori,
• x - numărul perioadei.

Linia dreaptă de pe grafic arată creșterea stabilă a calității muncii managerului. Valoarea acurateței aproximării este de 0,9929, ceea ce indică faptul că linia dreaptă calculată este în acord cu datele inițiale. Prognozele trebuie să fie exacte.

Pentru a prezice numărul de contracte încheiate, de exemplu, în perioada 11, trebuie să înlocuiți 11 în loc de x în ecuație. Pe parcursul calculelor aflăm că în a 11-a perioadă acest manager va încheia 55-56 de contracte.

Linie de tendință exponențială

Acest tip este util dacă valorile de intrare se modifică la o rată în continuă creștere. Aproximarea exponențială nu se aplică în prezența caracteristicilor zero sau negative.

Să construim o linie de tendință exponențială în Excel. Să luăm, de exemplu, valorile condiționate ale furnizării productive de energie electrică în regiunea X:

Construim un program. Adăugați o linie exponențială.

Ecuația este următoarea:

y = 7,6403e ^ -0,084x

• unde 7,6403 și -0,084 sunt constante;
• e este baza logaritmului natural.

Indicatorul fiabilității aproximării a fost 0,938 - curba corespunde datelor, eroarea este minimă, iar prognozele vor fi precise.

Linie de tendință logaritmică în Excel

Este utilizat pentru următoarele modificări ale indicatorului: mai întâi, creștere sau declin rapid, apoi - stabilitate relativă. Curba optimizată se adaptează bine acestui „comportament” al valorii. Tendința logaritmică este potrivită pentru a prezice vânzările unui produs nou care tocmai este introdus pe piață.

În etapa inițială, sarcina producătorului este de a crește baza de clienți. Când un produs are un client, acesta trebuie păstrat și întreținut.

Să construim un grafic și să adăugăm o linie de tendință logaritmică pentru a estima vânzările unui produs condiționat:

R2 este aproape ca valoare de 1 (0,9633), ceea ce indică eroarea minimă de aproximare. Să prognozăm volumele vânzărilor în perioadele ulterioare. Pentru a face acest lucru, trebuie să înlocuiți numărul perioadei din ecuație în loc de x.

De exemplu:

Perioadă	14	15	16	17	18	19	20
Prognoza	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

Pentru calcularea cifrelor de prognoză a fost utilizată o formulă de forma: = 272,14 * LN (B18) +287,21. Unde B18 este numărul perioadei.

Linia de tendință polinomială în Excel

Această curbă se caracterizează prin creștere și scădere variabilă. Pentru polinoame (polinoame), gradul este determinat (de numărul de valori maxime și minime). De exemplu, o extremă (minimă și maximă) este gradul doi, două puncte extreme sunt gradul al treilea, trei sunt al patrulea.

O tendință polinomială în Excel este utilizată pentru a analiza un set mare de date cu o valoare instabilă. Să ne uităm la exemplul primului set de valori (prețurile petrolului).

Pentru a obține o astfel de valoare a preciziei aproximării (0,9256), a fost necesar să se pună gradul 6.

Dar această tendință face posibilă realizarea de prognoze mai mult sau mai puțin precise.

Ce să faci dacă nu există măsurători de timp pentru anumite volume/dimensiuni ale produselor? Sau numărul de măsurători nu este suficient și nu pot fi efectuate observații suplimentare în viitorul apropiat? Cel mai bun mod de a rezolva această problemă este de a construi dependențe calculate (ecuații de regresie) folosind linii de tendință în MS Excel.

Să luăm în considerare o situație reală: în depozit, pentru a stabili valoarea costurilor cu forța de muncă pentru selecția casetei comenzii, s-au efectuat observații de sincronizare. Rezultatele acestor observații sunt prezentate în Tabelul 1 de mai jos.

Ulterior, a devenit necesar să se determine timpul necesar pentru selecția a 0,6 și 0,9 mc de marfă/comandă. Datorită imposibilității de a efectua studii suplimentare de sincronizare, timpul petrecut pentru selectarea acestor volume de comenzi a fost calculat folosind ecuații de regresie în MS Excel. Pentru aceasta, tabelul 1 a fost convertit în tabelul 2.

Selectarea unui grafic de dispersie, fig. unu

Următorul pas: cursorul mouse-ului a fost plasat pe unul dintre punctele diagramei și cu butonul din dreapta al mouse-ului a fost apelat un meniu contextual, în care a fost selectat elementul: „adăugare linie de tendință” (fig. 2).

Adăugarea unei linii de tendință, Fig. 2

În fereastra apărută pentru setarea formatului liniei de tendință (fig. 3) au fost selectate secvențial: tipul de linie este liniar/putere și s-au verificat următoarele elemente: „arătați ecuația pe diagramă” și „puneți valoarea fiabilității aproximării (R ^ 2) pe diagramă” (coeficient de determinare).

Format de linie de tendință, Fig. 3

Ca urmare, graficele prezentate în Fig. 4 și 5.

Dependența liniară calculată, Fig. 4

Dependența calculată prin lege putere, Fig. 5

O analiză vizuală a graficelor mărturisește fără ambiguitate apropierea dependențelor obținute. În plus, valoarea fiabilității aproximării (R ^ 2), care se mai numește și coeficient de determinare, în cazul ambelor dependențe este aceeași valoare 0,97. Se știe că cu cât coeficientul de determinare este mai aproape de 1, cu atât linia de tendință corespunde realității. Se mai poate afirma ca modificarea cu 97% a timpului alocat procesarii comenzii se explica prin modificarea cantitatii de marfa. Prin urmare, în acest caz, nu contează: care a calculat dependența pentru a alege ca principală pentru calculul ulterior al costurilor de timp.

Să o luăm ca principală - o dependență liniară calculată. Apoi, valorile timpului petrecut în funcție de cantitatea de mărfuri vor fi determinate de formula: y = 54,511x + 0,1489. Rezultatele acestor calcule pentru cantitatea de mărfuri pentru care au fost efectuate anterior observațiile temporale sunt prezentate în Tabelul 3 de mai jos.

Să determinăm abaterea medie a costurilor de timp calculate prin ecuația de regresie de la costurile de timp calculate conform datelor observațiilor de timp: (-0,05 + 0,10-0,05 + 0,01) / 4 = 0,0019. Astfel, costurile de timp calculate folosind ecuația de regresie diferă de costurile de timp calculate folosind datele de observare a timpului doar prin 0,19%. Discrepanța dintre date este neglijabilă.

Folosind formula: y = 54,511x + 0,1489, vom seta consumul de timp pentru cantitatea de mărfuri pentru care nu s-au făcut observații de timp anterior. (tabelul 4).

Astfel, construirea dependențelor calculate folosind linii de tendință în MS Excel - aceasta este o modalitate excelentă de a determina timpul petrecut în operațiuni care, din diverse motive, nu au fost acoperite de observațiile de timp.

Efectuarea sarcinilor pentru construirea unei linii de tendință se distinge prin faptul că datele inițiale pot fi un set de numere care nu sunt legate între ele.

Prognoza conform orarului obișnuit 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ă din trei etape: introducerea datelor inițiale în Excel, construirea unui grafic, alegerea unei linii de tendință și a parametrilor acesteia.

Să începem prin a introduce datele.

1. Creați un tabel cu datele inițiale în Excel.

(Imaginea 1)

2. Selectați celulele B3: B17 și mergeți la fila „Insert” și selectați „Graph”.

(Figura 2)

3. După ce graficul este construit, puteți adăuga subtitrări și un titlu.

Mai întâi, faceți clic stânga pe chenarul diagramei pentru ao 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 „aproximare liniară” sau „aproximare exponențială”.

(Figura 5)

Așa că am construit linia de tendințe primară, care poate să nu corespundă realității.

Acesta este rezultatul nostru intermediar.

(Figura 6)

Și, prin urmare, va trebui să ajustați parametrii liniei noastre de tendințe sau să selectați o altă funcție.

Versiune profesională: alegerea 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 liniei de tendință”, punem caseta de selectare de lângă „puneți pe diagramă valoarea fiabilității aproximării R ^ 2 și faceți clic pe butonul „închidere”.

Vedem pe diagramă coeficientul R ^ 2 = 0,6442

(Figura 8)

8. Anulați modificările. Selectați graficul, faceți clic pe fila „Layout”, butonul „Trendline” și selectați „Nu”.

9. Accesați fereastra „Format linie de tendință”, dar pentru a selecta linia de tendință „Polinom”, modificăm gradul, realizând indicatorii coeficientului R ^ 2 = 0,8321

(Figura 9)

Prognoza

Dacă trebuie să ghicim ce date ar putea fi obținute în următoarea dimensiune, în fereastra „Format de 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 fost între 60 și 70.

Concluzie

Și în concluzie, dacă vă interesează formula prin care se construiește trendul, în căsuța „Format liniei de tendință” bifați caseta 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.