Excel regresijska analiza (sadržaj)

  • Regresijska analiza u Excelu
  • Matematičko objašnjenje regresije
  • Kako izvesti linearnu regresiju u Excelu?
    • # 1 - Regresijski alat pomoću analize ToolPak u Excelu
    • # 2 - Regresijska analiza korištenjem Scatterplota s trend-linijom u Excelu

Regresijska analiza u Excelu

Linearna regresija je statistička tehnika koja ispituje linearni odnos između ovisne varijable i jedne ili više neovisnih varijabli.

  • Ovisna varijabla (aka varijabla odgovora / ishoda): je li varijabla vašeg interesa i koju ste željeli predvidjeti na temelju podataka dostupnih nezavisnih varijabli.
  • Nezavisna varijabla (aka varijabla objašnjenja / prediktor): Je / su varijabla (i) o kojoj ovisi varijabla odgovora. Što znači da su to varijable pomoću kojih se varijabla odgovora može predvidjeti.

Linearni odnos znači da promjena neovisne varijable uzrokuje promjenu ovisne varijable.

Postoje također dvije vrste linearnih odnosa.

  1. Pozitivni linearni odnos: Kada se neovisna varijabla povećava, povećava se i ovisna varijabla.
  2. Negativni linearni odnos: Kada se neovisna varijabla poveća, ovisna varijabla se smanjuje.

To su bili neki od preduvjeta prije nego što zapravo nastavite prema regresijskoj analizi.

Postoje dva osnovna načina za izvođenje linearne regresije u excelu koristeći:

  • Regresijski alat putem Analysis ToolPak-a
  • Scatter karta s trend-linijom

Zapravo postoji još jedna metoda koja koristi ručne formule za izračunavanje linearne regresije. Ali zašto biste se zalagali za to, ako excel izračunava za vas?

Stoga ćemo govoriti o dvije gore spomenute metode.

Pretpostavimo da imate podatke o visini i težini od 10 jedinki. Ako planirate ove podatke putem grafikona, pogledajmo što oni daju.

Kao što gore prikazano snimka zaslona, ​​linearni odnos može se vidjeti u grafičkom prikazu Visina i Masa. Nemojte se sad previše uključiti u grafikon, svejedno ćemo to iskopati u drugom dijelu ovog članka.

Matematičko objašnjenje regresije

Imamo matematički izraz za linearnu regresiju kao što slijedi:

Y = aX + b + ε

Gdje,

  • Y je ovisna varijabla ili varijabla odgovora.
  • X je neovisna varijabla ili prediktor.
  • a je nagib regresijske linije. Što predstavlja da kad se X promijeni, dolazi do promjene Y od strane "a" jedinica.
  • b je presretanje. To je vrijednost koju Y uzima kada je vrijednost X jednaka nuli.
  • ε je izraz slučajne pogreške. Javlja se jer predviđena vrijednost Y nikada neće biti potpuno jednaka stvarnoj vrijednosti za dati X. Ovaj pojam pogreške, ne moramo se brinuti. Budući da postoje neki softveri koji izračunavaju ovaj izraz pogreške u nadoknadi za vas. Excel je jedan od tih programa.

U tom slučaju, jednadžba postaje,

Y = aX + b

Koje se mogu predstaviti kao:

Težina = a * Visina + b

Pokušat ćemo saznati vrijednosti ovih a i b pomoću metoda koje smo gore raspravljali.

Kako izvesti linearnu regresiju u Excelu?

Daljnji članak objašnjava osnove regresijske analize u excelu i pokazuje nekoliko različitih načina vršenja linearne regresije u Excelu.

Ovdje možete preuzeti ovaj obrazac Excel predloška za regresijsku analizu - Predložak Excela za regresijsku analizu

# 1 - Regresijski alat pomoću analize ToolPak u Excelu

U našem primjeru pokušat ćemo ukloniti regresiju za vrijednosti težine (koja je ovisna varijabla) uz pomoć visinskih vrijednosti (što je neovisna varijabla).

  • U proračunskoj tablici Excela kliknite na Analiza podataka (prisutna u grupi Analiza ) u odjeljku Podaci.

  • Potražite regresiju . Odaberite ga i pritisnite ok.

  • Upotrijebite sljedeće ulaze u oknu regresije koje se otvara.

  • Raspon unosa Y : odaberite ćelije koje sadrže vašu ovisnu varijablu (u ovom primjeru B1: B11)

  • Raspon unosa X : Odaberite ćelije koje sadrže vašu neovisnu varijablu (u ovom primjeru A1: A11).

  • Potvrdite okvir pod nazivom Oznake ako vaši podaci imaju nazive stupaca (u ovom primjeru imamo imena stupaca).

  • Razina pouzdanosti postavljena je na 95%, što se može promijeniti prema potrebama korisnika.

  • Pod opcijama Izlaz možete prilagoditi gdje želite vidjeti rezultate regresijske analize u Excelu. U ovom slučaju želimo vidjeti izlaz na istom listu. Stoga, s obzirom na raspon u skladu s tim.

  • U opciji Residuals imate opcionalne ulaze poput Residuals, Residual Plots, Standardized Residuals, Line Fit parcela koje možete odabrati prema potrebi. U tom slučaju potvrdite potvrdni okvir Residuals kako bismo vidjeli raspodjelu između predviđenih i stvarnih vrijednosti.

  • Pod opcijom Normal Vjerojatnost možete odabrati Normalne karte vjerojatnosti koje mogu pomoći provjeriti normalnost prediktora. Kliknite U redu .

  • Excel će izračunati regresijsku analizu za vas u djeliću sekunde.

Do ovdje je bilo lako i nije tako logično. Međutim, tumačenje ovog rezultata i stvaranje vrijednih uvida iz njega je težak zadatak.

Jedan važan dio ovog cjelokupnog izlaza je R Square / Prilagođeni R Square pod tablicom SAŽETAK IZLAZA. Što pruža informacije koliko je naš model dobar. U ovom slučaju, vrijednost R Square iznosi 0, 9547. Što tumači da model ima 95, 47% točnost (dobro uklapanje). Ili na nekom drugom jeziku, informacija o Y varijabli objašnjava se 95, 47% X varijabli.

Drugi važan dio cjelokupne proizvodnje je tablica koeficijenata. Daje vrijednosti koeficijenata pomoću kojih se može stvoriti model za buduća predviđanja.

Sada naša, regresijska jednadžba predviđanja postaje:

Težina = 0.6746 * Visina - 38.45508 (Vrijednost nagiba za Visinu je 0.6746…, a presretanje je -38.45508…)

Jeste li dobili ono što ste definirali? Definirali ste funkciju u kojoj sada jednostavno morate staviti vrijednost Height i dobit ćete vrijednost Weight.

# 2 - Regresijska analiza korištenjem Scatterplota s trend-linijom u Excelu

Sada ćemo vidjeti kako u excelu možemo stati regresijsku jednadžbu na sam rasipač.

  • Odaberite cijela dva stupacna podataka (uključujući zaglavlja).
  • Kliknite Umetanje i odaberite Scatter Plot pod odjeljkom grafova kao što je prikazano na slici ispod.

  • Pogledajte graf izlaza.

  • Sada na ovom grafikonu moramo imati najmanje kvadratnu regresijsku liniju. Da biste dodali ovaj redak, desnom tipkom miša kliknite bilo koju od podataka na grafikonu i odaberite opciju Dodaj trend .

  • Omogućit će vam da imate tendenciju najmanjeg regresije kao u nastavku.

  • Pod opcijom Oblikovanje trendi linije potvrdite okvir za prikaz jednadžbe na grafikonu.

  • To vam omogućuje da vidite grafičku jednadžbu regresijske linije najmanje kvadrata.

Ovo je jednadžba pomoću koje možemo predvidjeti vrijednosti mase za bilo koji skup vrijednosti visine.

Stvari koje treba zapamtiti o regresijskoj analizi u Excelu

  • Izgled trendline možete promijeniti u opciji Format Trendline u dijagramu rasipanja.
  • Uvijek se preporučuje da pogledate zaostale parcele dok radite regresijsku analizu pomoću Data Analysis ToolPak u Excelu. Daje vam bolje razumijevanje širenja stvarnih Y vrijednosti i procijenjenih X vrijednosti.
  • Jednostavna linearna regresija u excelu ne treba ANOVA i podešeni R kvadrat za provjeru. Ove se karakteristike mogu uzeti u obzir za višestruku linearnu regresiju. Što je izvan opsega ovog članka.

Preporučeni članci

Ovo je vodič za regresijsku analizu u Excelu. Ovdje smo raspravljali o tome kako napraviti regresijsku analizu u Excelu, uz primjere excela i preuzeti Excel predložak. Možete i pregledati naše druge predložene članke -

  1. Excel alat za analizu podataka
  2. Izračunajte ANOVA u Excelu
  3. Kako pronaći pokretne prosjeke Excela
  4. Z TEST Primjeri u Excelu

Kategorija: