Excel regresijska analiza (vsebina)

  • Regresijska analiza v Excelu
  • Pojasnilo regresije matematično
  • Kako izvajati linearno regresijo v Excelu?
    • # 1 - regresijsko orodje z orodjem Analysis ToolPak v Excelu
    • # 2 - Regresijska analiza z uporabo Scatterplota s Trendline v Excelu

Regresijska analiza v Excelu

Linearna regresija je statistična tehnika, ki preučuje linearno razmerje med odvisno spremenljivko in eno ali več neodvisnimi spremenljivkami.

  • Odvisna spremenljivka (aka spremenljivka odziv / izid): je spremenljivka, ki vas zanima, in ki ste jo želeli napovedati na podlagi podatkov, ki so na voljo za neodvisne spremenljivke.
  • Neodvisna spremenljivka (aka pojasnjevalna / prediktorjeva spremenljivka): je / so spremenljivka (-e), od katere je odvisna spremenljivka odziva. Kar pomeni, da so to spremenljivke, s pomočjo katerih je mogoče spremeniti odzivno spremenljivko.

Linearni odnos pomeni, da sprememba neodvisne spremenljivke povzroči spremembo odvisne spremenljivke.

V osnovi sta tudi dve vrsti linearnih odnosov.

  1. Pozitivno linearno razmerje: Ko se neodvisna spremenljivka poveča, se poveča tudi odvisna spremenljivka.
  2. Negativno linearno razmerje: Ko se neodvisna spremenljivka poveča, se odvisna spremenljivka zmanjša.

To je bilo nekaj predpogojev, preden se dejansko lotite regresijske analize.

Obstajata dva osnovna načina za izvajanje linearne regresije v excelu z uporabo:

  • Orodje za regresijo prek Analysis ToolPak
  • Scatter diagram s trendno črto

Obstaja še ena metoda, ki uporablja ročne formule za izračun linearne regresije. Toda zakaj bi se lotili tega, ko excel za vas izračune?

Zato bomo govorili samo o dveh zgoraj omenjenih metodah.

Recimo, da imate podatke o višini in teži 10 posameznikov. Če narišete te podatke prek grafikona, poglejmo, kaj daje.

Kot kaže zgornji posnetek zaslona, ​​je linearno razmerje v grafu mogoče najti v višini in teži. Zdaj se ne vključujte veliko v graf, vseeno ga bomo kopali globoko v drugem delu tega članka.

Pojasnilo regresije matematično

Spodaj imamo matematični izraz za linearno regresijo:

Y = aX + b + ε

Kje,

  • Y je odvisna spremenljivka ali odzivna spremenljivka.
  • X je neodvisna spremenljivka ali napovedovalec.
  • a je naklon regresijske črte. Kar pomeni, da kadar se X spremeni, pride do spremembe Y s "a" enotami.
  • b je prestrezanje. To je vrednost Y, ko je vrednost X enaka nič.
  • ε je izraz naključne napake. Zgodi se, ker predvidena vrednost Y nikoli ne bo popolnoma enaka dejanski vrednosti za dani X. Ta izraz o napaki nam ni treba skrbeti. Ker obstaja nekaj programske opreme, ki izračun tega izraza napake opravi za vas. Excel je ena od teh programov.

V tem primeru enačba postane oz.

Y = aX + b

Ki jih lahko predstavimo kot:

Teža = a * Višina + b

Vrednosti teh a in b bomo poskušali ugotoviti z uporabo zgoraj opisanih metod.

Kako izvajati linearno regresijo v Excelu?

Nadaljnji članek v excelu razlaga osnove regresijske analize in prikazuje nekaj različnih načinov, kako narediti linearno regresijo v Excelu.

Predlogo za Excel predlogo za regresijsko analizo lahko prenesete tukaj - Predloga za napredek Regression Analysis

# 1 - regresijsko orodje z orodjem Analysis ToolPak v Excelu

V našem primeru bomo poskušali prilagoditi regresijo za vrednosti teže (ki je odvisna spremenljivka) s pomočjo višinskih vrednosti (kar je neodvisna spremenljivka).

  • V preglednici Excel kliknite Podatki o analizi podatkov (v Analizni skupini) pod Podatki.

  • Poiščite regresijo . Izberite ga in pritisnite OK.

  • V podoknu regresije, ki se odpre, uporabite naslednje vhode.

  • Obseg vnosa Y : izberite celice, ki vsebujejo vašo odvisno spremenljivko (v tem primeru B1: B11)

  • Območje vnosa X : Izberite celice, ki vsebujejo vašo neodvisno spremenljivko (v tem primeru A1: A11).

  • Potrdite polje z imenom Nalepke, če imajo vaši podatki imena stolpcev (v tem primeru imamo imena stolpcev).

  • Raven zaupanja je privzeto nastavljena na 95%, kar lahko spremenite glede na zahteve uporabnikov.

  • Pod možnostmi Izhod lahko prilagodite, kje želite videti rezultate regresijske analize v Excelu. V tem primeru želimo videti izhod na istem listu. Zato glede na razpon.

  • Pod možnostjo Preostali viri imate na voljo izbirne vnose, kot so ostanki, preostali načrti, standardizirani ostanki, linijski vmesniki, ki jih lahko izberete glede na vaše potrebe. V tem primeru potrdite polje Preostali, da bomo lahko videli razpršenost med predvidenimi in dejanskimi vrednostmi.

  • Pod možnostjo Normalna verjetnost lahko izberete ploskve normalne verjetnosti, s pomočjo katerih lahko preverite normalnost napovedovalcev. Kliknite V redu .

  • Excel bo v nekaj sekundah za vas izračunal regresijsko analizo.

Do tukaj je bilo enostavno in ne tako logično. Vendar je tolmačenje tega izida in iz njega dragocene vpoglede zahtevna naloga.

Pomemben del tega celotnega izida je R Square / Prilagojena R Square pod tabelo POVZETEK IZHODA. Kar zagotavlja informacije, kako dober je naš model. V tem primeru je vrednost R kvadratna 0, 9547. Kar razlaga, da ima model 95, 47% natančnost (dobro prileganje). Ali v drugem jeziku je podatek o spremenljivki Y 95, 47% razložen s spremenljivko X.

Drugi pomemben del celotne proizvodnje je tabela koeficientov. Daje vrednosti koeficientov, ki jih je mogoče uporabiti za oblikovanje modela za prihodnje napovedi.

Zdaj naša, regresijska enačba za napoved postane:

Teža = 0, 6746 * Višina - 38, 45508 (vrednost naklona za višino je 0, 6746…, prestrezanje pa -38, 45508…)

Ste dobili to, kar ste opredelili? Določili ste funkcijo, pri kateri morate zdaj le vstaviti vrednost Višine in dobili boste vrednost Teža.

# 2 - Regresijska analiza z uporabo Scatterplota s Trendline v Excelu

Zdaj bomo videli, kako lahko v excelu prilagodimo regresijsko enačbo na samem raztresevalnem pogonu.

  • Izberite celotna dva stolpca (vključno z glavami).
  • Kliknite Vstavi in v razdelku grafov izberite Scatter Plot, kot je prikazano na spodnji sliki.

  • Glej izhodni graf.

  • Zdaj moramo na tem grafu imeti najmanj regresijsko črto. Če želite dodati to vrstico, z desno miškino tipko kliknite katero koli od podatkovnih točk na grafu in izberite možnost Dodaj trendno linijo.

  • Omogočil vam bo, da imate trend spodnje regresije, kot je spodaj.

  • Pod možnostjo Oblika v trendu potrdite polje za prikaz enačbe na grafikonu.

  • Omogoča vam, da na grafu vidite enačbo regresijske črte z najmanj kvadratne črte.

To je enačba, s katero lahko predvidimo vrednosti teže za kateri koli niz višinskih vrednosti.

Spomniti se je treba o regresijski analizi v Excelu

  • Postavitev trendne linije lahko pod možnostjo Oblika trendovske črte spremenite v razpredelnici.
  • Vedno je priporočljivo, da si ogledate preostale parcele, medtem ko izvajate regresijsko analizo z uporabo Data Tool ToolPak v Excelu. Omogoča vam boljše razumevanje širjenja dejanskih vrednosti Y in ocenjenih vrednosti X.
  • Enostavna linearna regresija v excelu za preverjanje ne potrebuje ANOVA in prilagojenega kvadratka R. Te značilnosti je mogoče upoštevati pri večkratni linearni regresiji. Kar je zunaj obsega tega članka.

Priporočeni članki

To je vodnik za regresijsko analizo v Excelu. Tukaj razpravljamo o tem, kako narediti regresijsko analizo v Excelu skupaj s primeri excela in predlogo za excel, ki jo lahko naložite. Ogledate si lahko tudi druge naše predlagane članke -

  1. Excel orodje za analizo podatkov
  2. Izračunajte ANOVA v Excelu
  3. Kako najti Excel premikajoča se povprečja
  4. Z TEST primeri v Excelu

Kategorija:

Excel Nasveti