Linearne regresije modeliraju odnos između ovisnih i neovisnih statističkih varijabli podataka. Jednostavnije rečeno, ističu trend između dva stupca tablice u proračunskoj tablici. Na primjer, ako postavite tablicu proračunske tablice Excel sa stupcem mjesec x i zabilježite skup podataka za svaki od mjeseci u susjednom stupcu y, linearna regresija će istaknuti trend između varijabli x i y dodavanjem linija trenda u tablicu grafovima. Ovako možete dodati linearnu regresiju Excelovim grafovima.
Dodavanje linije trenda linearne regresije na grafikon
- Prvo otvorite praznu Excel proračunsku tablicu, odaberite ćeliju D3 i unesite 'Mjesec' kao naslov stupca, što će biti varijabla x.
- Zatim kliknite ćeliju E3 i unesite 'Vrijednost Y' kao naslov stupca varijable y. Ovo je u osnovi tablica sa zabilježenim nizom vrijednosti podataka za mjesece siječanj-svibanj.
- Unesite mjesece u ćelije D4 do D8 i vrijednosti podataka za njih u ćelije E4 do E8 kao što je prikazano na snimci izravno ispod.
Sada možete postaviti graf raspršenja za tu tablicu.
- Pokazivačem odaberite sve ćelije u tablici.
- Kliknite karticu Umetanje i odaberite Raspršiti > Razbacajte se samo s oznakama da dodate grafikon u proračunsku tablicu kao u nastavku. Alternativno, možete pritisnuti prečac Alt + F1 za umetanje stupčastog grafikona.
- Zatim biste trebali desnom tipkom miša kliknuti grafikon i odabrati Promjena vrste grafikona >X Y (raspršeno) >Razbacajte se samo s oznakama.
Zatim možete dodati liniju trenda na dijagram raspršenja
- Odaberite jednu od točaka podataka na dijagramu raspršenja i kliknite desnom tipkom miša da otvorite kontekstni izbornik koji uključuje Dodajte liniju trenda opcija.
- Izaberi Dodajte liniju trenda za otvaranje prozora prikazanog na snimci ispod. Taj prozor ima pet kartica koje uključuju različite opcije oblikovanja za linije trenda linearne regresije.
3. Kliknite Opcije trenda i odatle odaberite vrstu regresije. Možete odabrati Eksponencijalno, Linearna, Logaritamski, Pomični prosjek, Vlast i Polinom opcije tipa regresije od tamo.
4. Odaberite Linearna i kliknite Zatvoriti da dodate tu liniju trenda na grafikon kao što je prikazano izravno ispod.
Linija trenda linearne regresije u gornjem grafikonu naglašava da postoji opći odnos prema gore između varijabli x i y unatoč nekoliko padova na grafikonu. Imajte na umu da linija trenda linearne regresije ne preklapa nijednu od točaka podataka na grafikonu, tako da nije isto što i vaš prosječni linijski grafikon koji povezuje svaku točku.
Formatiranje linije trenda linearne regresije
Formatiranje linije trenda linearne regresije važan je alat u stvaranju čitljivih, jasnih grafikona u excelu.
- Za početak oblikovanja linije trenda, trebate je kliknuti desnom tipkom miša i odabrati Oblikujte liniju trenda.
- To će ponovno otvoriti prozor Format Trendline iz kojeg možete kliknuti Boja linije.
- Izaberi Puna linija i kliknite na Boja okvir za otvaranje palete iz koje možete odabrati alternativnu boju za liniju trenda.
- Da biste prilagodili stil linije, kliknite karticu Stil linije. Zatim možete podesiti širinu strelice i konfigurirati postavke strelice.
- pritisni Postavke strelice gumbe za dodavanje strelica na liniju.
Također možete dodati efekte svojoj liniji trenda u estetske svrhe
- Dodajte efekt sjaja na liniju trenda klikom Sjaj i Meki rubovi. To će otvoriti karticu u nastavku s koje možete dodati sjaj klikom na Unaprijed postavljene postavke dugme.
- Zatim odaberite varijaciju sjaja za odabir efekta. Klik Boja za odabir alternativnih boja za efekt, a možete povući Veličina i Transparentnost trake za dodatno konfiguriranje sjaja linije trenda.
Predviđanje vrijednosti s linearnom regresijom
Nakon što formatirate liniju trenda, s njom možete predvidjeti i buduće vrijednosti. Na primjer, pretpostavimo da trebate predvidjeti vrijednost podataka tri mjeseca nakon svibnja za kolovoz, koja nije uključena u našu tablicu.
- kliknite Opcije trenda i unesite '3' u okvir za tekst naprijed.
- Linija trenda linearne regresije naglašava da će vrijednost kolovoza vjerojatno biti nešto iznad 3500, kao što je prikazano u nastavku.
Svaka linija trenda linearne regresije ima vlastitu jednadžbu i r kvadratnu vrijednost koju možete dodati grafikonu.
- Kliknite na Prikaz jednadžbe na grafikonu potvrdite okvir za dodavanje jednadžbe na grafikon. Ta jednadžba uključuje vrijednost nagiba i presjeka.
- Da biste grafikonu dodali vrijednost kvadrata r, kliknite na Prikaži vrijednost R-kvadrata na grafikonu potvrdni okvir. To dodaje r na kvadrat na graf odmah ispod jednadžbe kao na snimku ispod.
- Povucite okvir jednadžbe i korelacije da promijenite njegov položaj na dijagramu raspršenja.
Funkcije linearne regresije
Excel također uključuje funkcije linearne regresije pomoću kojih možete pronaći vrijednosti nagiba, presjeka i r kvadrata za nizove podataka y i x.
- Odaberite ćeliju proračunske tablice kojoj želite dodati jednu od tih funkcija, a zatim pritisnite Umetanje funkcije dugme. Funkcije linearne regresije su statističke, stoga odaberite Statistički s padajućeg izbornika kategorije.
- Zatim možete odabrati RSQ, NAGIB ili PRESREĆANJE kako biste otvorili svoje prozore funkcija kao u nastavku.
RSQ, SLOPE i INTERCEPT prozori su prilično isti. Oni uključuju kutije Known_y i Known_x koje možete odabrati da dodate vrijednosti varijabli y i x iz svoje tablice. Imajte na umu da ćelije moraju sadržavati samo brojeve, stoga zamijenite mjesece u tablici odgovarajućim brojkama kao što su 1 za siječanj, 2 za veljaču itd. Kliknite u redu za zatvaranje prozora i dodavanje funkcije u proračunsku tablicu.
Dakle, sada možete dotjerati svoje Excel proračunske tablice s linijama trenda linearne regresije. Oni će istaknuti opće trendove za podatkovne točke grafova, a s regresijskim jednadžbama također su zgodni alati za predviđanje.
Imate li savjete, trikove ili pitanja u vezi s linijama trenda linearne regresije u Excelu? Javite nam u odjeljku za komentare u nastavku.