Iscrtavanje linija trenda u Excelu

Excel pruža robustan skup alata za ilustraciju trendova. To možete učiniti iscrtavanjem linija trenda u vašim Excel grafikonima kako biste ponudili vizualni prikaz vaših podataka . Ovdje ćete otkriti kako nacrtati logaritamske linije trenda, linije trenda snage i polinomske trendove u Excelu.

Iscrtavanje logaritamske linije trenda u Excelu

Logaritamska trend je onaj u kojem se podaci raste ili pada vrlo brzo na početku, ali onda se usporava, a razina s vremenom. Primjer logaritamskog trenda je prodajni obrazac dugo očekivanog novog proizvoda, koji se obično prodaje u velikim količinama na kratko vrijeme, a zatim se izjednači.

Da biste vizualizirali takav trend, možete nacrtati logaritamsku liniju trenda. Ovo je zakrivljena linija kroz podatkovne točke gdje se razlike između točaka s jedne strane linije i onih s druge strane linije međusobno poništavaju.

Evo koraka koje treba slijediti za crtanje logaritamske linije trenda u Excelu:

Kliknite grafikon da biste ga odabrali.

Ako vaš grafikon ima više serija podataka, kliknite niz koji želite analizirati.

Odaberite Dizajn → Dodaj element grafikona → Linija trenda → Više opcija linije trenda.
Pojavljuje se okno Format Trendline.

Kliknite karticu Opcije trenda.

Odaberite radio gumb Logaritamski.
Excel crta logaritamsku liniju trenda.

(Izborno) Označite potvrdni okvir Prikaži jednadžbu na grafikonu.
Ako samo želite vidjeti liniju trenda, slobodno prijeđite preko koraka 6 i 7.

(Neobavezno) Označite potvrdni okvir Prikaži vrijednost R-kvadrata na grafikonu.

Kliknite Zatvori.

Excel prikazuje jednadžbu regresije i vrijednost R2. Ova slika prikazuje grafikon s ucrtanom eksponencijalnom linijom trenda, regresijskom jednadžbom i vrijednošću R2.

Grafikon s logaritamskom linijom trenda.

Kada je linija trenda koja najbolje odgovara logaritamska krivulja, regresijska jednadžba poprima sljedeći opći oblik:

y = m * ln(x) + b

y je zavisna varijabla; x je nezavisna varijabla; b i m su konstante; a ln je prirodni logaritam, za koji možete koristiti Excel funkciju LN.

Excel nema funkciju koja izravno izračunava vrijednosti b i m. Međutim, možete koristiti funkciju LINEST ako "izravnate" logaritamsku krivulju korištenjem logaritamske ljestvice za neovisne vrijednosti:

{=LINEST( poznati_ys , LN( poznati_xs ), const , statistika )}

Iscrtavanje linije trenda snage u Excelu

U mnogim slučajevima regresijske analize , najbolje odgovara trend snage, u kojem se podaci stalno povećavaju ili smanjuju. Takav trend očito nije eksponencijalan ili logaritamski, a oba impliciraju ekstremno ponašanje, bilo na kraju trenda (u slučaju eksponencijalnog) ili na početku trenda (u slučaju logaritamskog). Primjeri trendova moći uključuju prihode, dobit i marže u uspješnim tvrtkama, a svi oni pokazuju stalan porast stope rasta iz godine u godinu.

Trend snage zvuči linearno, ali crtanje linije trenda snage pokazuje zakrivljenu liniju koja najbolje odgovara kroz podatkovne točke. U vašoj analizi takvih podataka obično je najbolje prvo isprobati liniju linearnog trenda. Ako vam to ne odgovara, prijeđite na liniju trenda snage.

Slijedite ove korake za crtanje linije trenda snage u Excelu:

Kliknite grafikon da biste ga odabrali.

Ako vaš grafikon ima više serija podataka, kliknite niz koji želite analizirati.

Odaberite Dizajn → Dodaj element grafikona → Linija trenda → Više opcija linije trenda.
Pojavljuje se okno Format Trendline.

Kliknite karticu Opcije trenda.

Odaberite radio gumb Napajanje.
Excel iscrtava liniju trenda snage.

(Izborno) Označite potvrdni okvir Prikaži jednadžbu na grafikonu.
Ako samo želite vidjeti liniju trenda, preskočite 6. i 7. korak.

(Neobavezno) Označite potvrdni okvir Prikaži vrijednost R-kvadrata na grafikonu.

Kliknite Zatvori.

Excel prikazuje jednadžbu regresije i vrijednost R2 (opisano u nastavku). Sljedeća slika prikazuje grafikon s ucrtanom linijom trenda snage, regresijskom jednadžbom i vrijednošću R2.

Grafikon s linijom trenda snage.

Kada je linija trenda koja najbolje odgovara krivulja snage, regresijska jednadžba poprima sljedeći opći oblik:

y = m * xb

y je zavisna varijabla; x je nezavisna varijabla; a b i m su konstante.

Nema dostupne funkcije radnog lista za izravno izračunavanje vrijednosti b i m. Međutim, možete koristiti funkciju LINEST ako "izravnate" krivulju snage primjenom logaritamske ljestvice na ovisne i neovisne vrijednosti:

{=LINEST(LN( poznati_ys ), LN( poznati_xs ), const , statistika )}

Iscrtavanje polinomske linije trenda u Excelu

U mnogim scenarijima iz stvarnog svijeta, odnos između ovisnih i nezavisnih varijabli ne kreće se u jednom smjeru. To bi bilo prelako. Na primjer, umjesto da stalno rastu - jednoliko, kao u linearnom trendu, oštro, kao u eksponencijalnom ili logaritamskom trendu, ili postojano, kao u trendu snage - podaci kao što su jedinična prodaja, dobit i troškovi mogu se kretati gore i dolje .

Da biste vizualizirali takav trend, možete nacrtati polinomsku liniju trenda, koja je najprikladnija linija višestrukih krivulja izvedenih pomoću jednadžbe koja koristi višestruke potencije x. Broj potencija x je red polinomske jednadžbe. Općenito, što je veći redoslijed, to se krivulja čvršće uklapa u vaše postojeće podatke, ali su vaše prognozirane vrijednosti nepredvidljivije.

Ako već imate grafikon, slijedite ove korake za dodavanje polinomske linije trenda u Excel:

Kliknite grafikon da biste ga odabrali.

Ako vaš grafikon ima više serija podataka, kliknite niz koji želite analizirati.

Odaberite Dizajn → Dodaj element grafikona → Linija trenda → Više opcija linije trenda.
Pojavljuje se okno Format Trendline.

Kliknite karticu Opcije trenda.

Odaberite radio gumb Polinom.

Kliknite strelice gumba Red spin kako biste postavili redoslijed polinomske jednadžbe koji želite.

Excel crta polinomsku liniju trenda.

(Izborno) Označite potvrdni okvir Prikaži jednadžbu na grafikonu.
Ako samo želite vidjeti liniju trenda, zaobiđite 7. i 8. korak.

(Neobavezno) Označite potvrdni okvir Prikaži vrijednost R-kvadrata na grafikonu.

Kliknite Zatvori.

Excel prikazuje jednadžbu regresije i vrijednost R2. Slika ispod prikazuje grafikon s ucrtanom polinomskom linijom trenda, regresijskom jednadžbom i R2 vrijednošću.

Grafikon s polinomskom linijom trenda.

Kada je linija trenda koja najbolje odgovara polinomska krivulja, regresijska jednadžba poprima sljedeći oblik:

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

y je zavisna varijabla; x je nezavisna varijabla; a b i m n do m1 su konstante.

Za izračunavanje vrijednosti b i mn do m1, možete koristiti LINEST ako povisite poznate_xs vrijednosti na stepene od 1 do n za polinom n -tog reda:

{=LINEST( poznati_ys , poznati_xs ^ {1,2,…, n }, const , statistika )}

Alternativno, možete koristiti funkciju TREND:

{=TREND( poznati_ys , poznati_xs ^ {1,2,…, n }, novi_xs , const )}