Trasarea liniilor de tendință în Excel

Excel oferă un set de instrumente robust pentru ilustrarea tendințelor. Puteți face acest lucru prin reprezentarea grafică în dvs. linii de trend diagrame Excel pentru a oferi o vizuală a datelor . Aici descoperiți cum să reprezentați linii de tendință logaritmice, linii de tendință de putere și linii de tendință polinomiale în Excel.

Trasarea unei linii de tendință logaritmică în Excel

O tendință logaritmică este aceea în care datele cresc sau scad foarte repede la început, dar apoi încetinesc și se nivelează în timp. Un exemplu de tendință logaritmică este modelul de vânzări al unui produs nou foarte așteptat, care de obicei se vinde în cantități mari pentru o perioadă scurtă de timp și apoi se stabilește.

Pentru a vizualiza o astfel de tendință, puteți trasa o linie de tendință logaritmică. Aceasta este o linie curbă prin punctele de date unde diferențele dintre punctele de pe o parte a liniei și cele de pe cealaltă parte a liniei se anulează reciproc.

Iată pașii de urmat pentru a reprezenta o linie de tendință logaritmică în Excel:

Faceți clic pe diagramă pentru a o selecta.

Dacă diagrama dvs. are mai multe serii de date, faceți clic pe seria pe care doriți să o analizați.

Alegeți Design → Adăugați element de diagramă → Linie de tendință → Mai multe opțiuni pentru linii de tendință.
Apare panoul Format Trendline.

Faceți clic pe fila Opțiuni pentru linia tendințelor.

Selectați butonul radio Logaritmic.
Excel trasează linia de tendință logaritmică.

(Opțional) Bifați caseta de selectare Afișare ecuație pe diagramă.
Dacă doriți doar să vedeți linia de tendință, nu ezitați să treceți peste pașii 6 și 7.

(Opțional) Bifați caseta de selectare Afișare R-Squared Value on Chart.

Faceți clic pe Închidere.

Excel afișează ecuația de regresie și valoarea R2. Această imagine arată o diagramă cu linia de tendință exponențială reprezentată, ecuația de regresie și valoarea R2.

Un grafic cu o linie de tendință logaritmică.

Când linia de tendință cea mai potrivită este o curbă logaritmică, ecuația de regresie ia următoarea formă generală:

y = m * ln(x) + b

y este variabila dependentă; x este variabila independentă; b și m sunt constante; iar ln este logaritmul natural, pentru care puteți folosi funcția Excel LN.

Excel nu are o funcție care să calculeze direct valorile lui b și m. Cu toate acestea, puteți utiliza funcția LINEST dacă „îndreptați” curba logaritmică utilizând o scală logaritmică pentru valorile independente:

{=LINEST( cunoscute_și , LN( cunoscute_xs ), const , statistici )}

Trasarea unei linii de tendință de putere în Excel

În multe cazuri de analiză de regresie , cea mai bună potrivire este oferită de o tendință de putere, în care datele cresc sau descresc constant. O astfel de tendință în mod clar nu este exponențială sau logaritmică, ambele implicând un comportament extrem, fie la sfârșitul tendinței (în cazul exponențialului), fie la începutul tendinței (în cazul logaritmului). Exemple de tendințe de putere includ veniturile, profiturile și marjele companiilor de succes, toate acestea indicând creșteri constante ale ratei de creștere an de an.

O tendință de putere sună liniar, dar trasarea liniei de tendință de putere arată o linie curbă cea mai potrivită prin punctele de date. În analiza dvs. a unor astfel de date, este de obicei cel mai bine să încercați mai întâi o linie de tendință liniară. Dacă acest lucru nu oferă o potrivire bună, treceți la o linie de tendință de putere.

Urmați acești pași pentru a trasa o linie de tendință de putere în Excel:

Faceți clic pe diagramă pentru a o selecta.

Dacă diagrama dvs. are mai multe serii de date, faceți clic pe seria pe care doriți să o analizați.

Alegeți Design → Adăugați element de diagramă → Linie de tendință → Mai multe opțiuni pentru linii de tendință.
Apare panoul Format Trendline.

Faceți clic pe fila Opțiuni pentru linia tendințelor.

Selectați butonul radio Pornire.
Excel trasează linia de tendință a puterii.

(Opțional) Bifați caseta de selectare Afișare ecuație pe diagramă.
Dacă doriți doar să vedeți linia de tendință, săriți peste pașii 6 și 7.

(Opțional) Bifați caseta de selectare Afișare R-Squared Value on Chart.

Faceți clic pe Închidere.

Excel afișează ecuația de regresie și valoarea R2 (descrisă mai jos). Următoarea imagine arată o diagramă cu linia de tendință a puterii trasată, ecuația de regresie și valoarea R2.

Un grafic cu o linie de tendință de putere.

Când linia de tendință cea mai potrivită este o curbă de putere, ecuația de regresie ia următoarea formă generală:

y = m * xb

y este variabila dependentă; x este variabila independentă; iar b și m sunt constante.

Nu există nicio funcție de foaie de lucru disponibilă pentru a calcula direct valorile lui b și m. Cu toate acestea, puteți utiliza funcția LINEST dacă „îndreptați” curba puterii aplicând o scară logaritmică valorilor dependente și independente:

{=LINEST(LN( cunoscut_y ), LN( cunoscut_xs ), const , statistici )}

Trasarea unei linii de tendință polinomială în Excel

În multe scenarii din lumea reală, relația dintre variabilele dependente și independente nu se mișcă într-o singură direcție. Ar fi prea ușor. De exemplu, mai degrabă decât să crească constant - uniform, ca într-o tendință liniară, brusc, ca într-o tendință exponențială sau logaritmică, sau constant, ca într-o tendință de putere - date precum vânzările unitare, profiturile și costurile se pot deplasa în sus și în jos .

Pentru a vizualiza o astfel de tendință, puteți trasa o linie de tendință polinomială, care este o linie cea mai potrivită de mai multe curbe derivate folosind o ecuație care utilizează puteri multiple ale lui x. Numărul puterilor lui x este de ordinul ecuației polinomiale. În general, cu cât ordinea este mai mare, cu atât curba se potrivește mai strânsă cu datele existente, dar cu atât valorile estimate sunt mai imprevizibile.

Dacă aveți deja o diagramă, urmați acești pași pentru a adăuga o linie de tendință polinomială în Excel:

Faceți clic pe diagramă pentru a o selecta.

Dacă diagrama dvs. are mai multe serii de date, faceți clic pe seria pe care doriți să o analizați.

Alegeți Design → Adăugați element de diagramă → Linie de tendință → Mai multe opțiuni pentru linii de tendință.
Apare panoul Format Trendline.

Faceți clic pe fila Opțiuni pentru linia tendințelor.

Selectați butonul radio Polinom.

Faceți clic pe săgețile butonului de rotație pentru a seta ordinea ecuației polinomiale dorite.

Excel trasează linia de tendință polinomială.

(Opțional) Bifați caseta de selectare Afișare ecuație pe diagramă.
Dacă doriți doar să vedeți linia de tendință, ocoliți pașii 7 și 8.

(Opțional) Bifați caseta de selectare Afișare R-Squared Value on Chart.

Faceți clic pe Închidere.

Excel afișează ecuația de regresie și valoarea R2. Imaginea de mai jos arată o diagramă cu linia de tendință polinomială reprezentată, ecuația de regresie și valoarea R2.

Un grafic cu o linie de tendință polinomială.

Când linia de tendință cea mai potrivită este o curbă polinomială, ecuația de regresie ia următoarea formă:

y = m n x n + … + m2x2 + m1x + b

y este variabila dependentă; x este variabila independentă; iar b și m n până la m1 sunt constante.

Pentru a calcula valorile b și mil prin m1, puteți utiliza LINEST dacă măriți known_xs valorile la puterile de la 1 la n pentru n polinom th-comandă:

{=LINEST( cunoscute_și , cunoscute_xs ^ {1,2,…, n }, const , statistici )}

Alternativ, puteți utiliza funcția TREND:

{= TENDINȚA ( cunoscute_și , cunoscute_xs ^ {1,2,…, n }, new_xs , const )}