Exceli regressioonanalüüs (sisukord)
- Regressioonianalüüs Excelis
- Regressiooni selgitus matemaatiliselt
- Kuidas teostada lineaarset regressiooni Excelis?
- # 1 - regressioonitööriist, kasutades Exceli analüüsitööriistaPak
- # 2 - regressioonanalüüs, kasutades Scatterploti ja Trendline'i Excelis
Regressioonianalüüs Excelis
Lineaarne regressioon on statistiline tehnika, mis uurib sõltuva muutuja ja ühe või mitme sõltumatu muutuja vahelist lineaarset suhet.
- Sõltuv muutuja (aka vastuse / tulemuse muutuja): on teie huvipakkuv muutuja, mida soovisite sõltumatu (te) muutuja (te) kohta olemasoleva teabe põhjal ennustada.
- Sõltumatu muutuja (aka selgitav / ennustav muutuja): Kas on muutuja (d), millest sõltuv reageerimismuutuja sõltub. Mis tähendab, et need on muutujad, mille abil saab vastuse muutujat ennustada.
Lineaarsuhe tähendab muutust sõltumatutes muutujates, mis muudavad sõltuva muutuja.
Põhimõtteliselt on olemas ka kahte tüüpi lineaarsed seosed.
- Positiivne lineaarsuhe: kui sõltumatu muutuja suureneb, suureneb ka sõltuv muutuja.
- Negatiivne lineaarsuhe: kui sõltumatu muutuja suureneb, siis sõltuv muutuja väheneb.
Need olid mõned eeltingimused, enne kui asuda excelis regressioonianalüüsi poole.
Excelis lineaarse regressiooni teostamiseks on kaks peamist võimalust:
- Regressioonitööriist läbi Analysis ToolPak
- Hajumisdiagramm trendijoonega
Tegelikult on veel üks meetod, mis kasutab lineaarse regressiooni arvutamiseks käsitsi valemeid. Kuid miks peaksite seda otsima, kui excel arvutab teie eest?
Seetõttu räägime ainult kahest eespool käsitletud meetodist.
Oletame, et teil on andmeid 10 inimese pikkuse ja kaalu kohta. Kui joonistate selle teabe diagrammi kaudu, vaatame, mida see annab.
Nagu ülaltoodud ekraanipilt näitab, võib lineaarse suhte graafiku kaudu leida kõrgusest ja kaalust. Ärge nüüd graafikuga eriti palju tegelege, me kavatseme seda igatahes sügavuti uurida selle artikli teises osas.
Regressiooni selgitus matemaatiliselt
Lineaarse regressiooni matemaatiline avaldis on järgmine:
Y = aX + b + ε
Kus,
- Y on sõltuv muutuja või reageerimise muutuja.
- X on sõltumatu muutuja või ennustaja.
- a on regressioonisirge kalle. Mis tähistab seda, et kui X muutub, siis Y muutub a-ühikute järgi.
- b on pealtkuulamine. See on väärtus Y, kui X väärtus on null.
- ε on juhusliku vea mõiste. Tekib seetõttu, et Y ennustatud väärtus ei ole kunagi täpselt sama kui antud X tegelik väärtus. Selle vea termini puhul ei pea me muretsema. Kuna on mõni tarkvara, mis arvutab selle vea termini teie taustaprogrammis. Excel on üks sellest tarkvarast.
Sel juhul võrrandist saab järgmine:
Y = aX + b
Mida saab esindada järgmiselt:
Kaal = a * kõrgus + b
Proovime nende a ja b väärtusi välja selgitada ülaltoodud meetodite abil.
Kuidas teostada lineaarset regressiooni Excelis?
Selles artiklis selgitatakse excelis regressioonianalüüsi põhialuseid ja näidatakse mõnda erinevat viisi, kuidas Excelis lineaarset regressiooni teha.
Selle regressioonianalüüsi Exceli malli saate alla laadida siit - Regressioonianalüüsi Exceli mall# 1 - regressioonitööriist, kasutades Exceli analüüsitööriistaPak
Meie näites proovime sobitada kaalu väärtuste (mis on sõltuv muutuja) regressiooni kõrguse väärtuste (mis on iseseisev muutuja) abil.
- Klõpsake Exceli arvutustabelis jaotises Andmed nuppu Andmeanalüüs (asub analüüsirühma all) .
- Otsige välja regressioon . Valige see ja vajutage ok.
- Kasutage avanenud paanil Regressiooni järgmisi sisendeid.
- Sisendi Y vahemik : valige lahtrid, mis sisaldavad teie sõltuvat muutujat (antud näites B1: B11)
- Sisendi X vahemik : valige lahtrid, mis sisaldavad teie sõltumatut muutujat (selles näites A1: A11).
- Märkige ruut nimega Sildid, kui teie andmetel on veerunimed (selles näites on meil veerunimed).
- Usaldusväärsuse tase on vaikimisi seatud 95% -le, mida saab muuta vastavalt kasutaja nõudmistele.
- Jaotises Väljundvalikud saate kohandada seda, kus soovite Excelis regressioonianalüüsi väljundit näha. Sel juhul tahame näha väljundit samal lehel. Seetõttu antud vahemik vastavalt.
- Valiku Jääkide all on teil valikulised sisendid, näiteks jäägid, jääkkrundid, standardiseeritud jäägid, liini sobitamise graafikud, mida saate vastavalt oma vajadusele valida. Sel juhul märkige ruut Jäägid, et saaksime näha jaotust ennustatud ja tegelike väärtuste vahel.
- Jaotises Tavaline tõenäosus saate valida tavalise tõenäosuse graafikud, mis aitavad teil ennustajate normaalsust kontrollida. Klõpsake nuppu OK .
- Excel arvutab teile regressioonanalüüsi sekundi murdosa jooksul.
Siiani oli see lihtne ja mitte nii loogiline. Selle väljundi tõlgendamine ja sellest väärtuslike teadmiste saamine on aga keeruline ülesanne.
Selle väljundi üks oluline osa on R-ruut / Kohandatud R-ruut tabeli KOKKUVÕTE väljundi all. Mis annab teavet, kui hea meie mudel sobib. Sel juhul on R ruudu väärtus 0, 9547. Mis tõlgendab, et mudeli täpsus on 95, 47% (hea sobivus). Või mõnes muus keeles selgitatakse Y muutuja kohta teavet 95, 47% X muutujaga.
Teine oluline osa kogu väljundist on koefitsientide tabel. See annab koefitsientide väärtused, mida saab kasutada mudeli ehitamiseks tulevaste ennustuste jaoks.
Nüüd saab meie ennustamise regressioonivõrrand:
Kaal = 0, 6746 * kõrgus - 38, 45508 ( kõrguse kalde väärtus on 0, 6746… ja pealtkuulamise väärtus –38, 45508…)
Kas saite selle, mille olete määratlenud? Olete määratlenud funktsiooni, mille puhul peate nüüd lihtsalt sisestama kõrguse väärtuse ja saate kaalu väärtuse.
# 2 - regressioonanalüüs, kasutades Scatterploti ja Trendline'i Excelis
Nüüd näeme, kuidas excelis suudame regressioonivõrrandi hajutatud plaadile ise sobitada.
- Valige kogu oma kaks veerus olevat teavet (sh päised).
- Klõpsake nupul Lisa ja valige jaotise Graafikud all Scatter Plot, nagu on näidatud alloleval pildil.
- Vaadake väljundgraafikut.
- Nüüd peab sellel graafikul olema kõige vähem ruuduline regressioonisirge. Selle rea lisamiseks paremklõpsake mis tahes andmepunkti graafikul ja valige suvand Lisa Trendline .
- See võimaldab teil saavutada madalaima regressioonitasemega trendijoone nagu allpool.
- Märkige jaotises Vorminda trendisuund ruut Kuva võrrand diagrammil.
- See võimaldab teil graafikul näha väikseima ruuduga regressioonijoone võrrandit.
See on võrrand, mille abil saame ennustada mis tahes kõrguse väärtuste komplekti kaalu väärtusi.
Regressioonianalüüsi meeldejäävad asjad Excelis
- Trendijoone paigutust saate muuta jaotise Format Trendline suvandis hajumisgraafikul.
- Regressioonianalüüsi ajal on Excelis Data Analysis ToolPak, kasutades regressioonianalüüsi, alati soovitatav vaadata jääkrundid. See annab teile parema ülevaate tegelike Y väärtuste ja hinnanguliste X väärtuste levikust.
- Lihtne lineaarne regressioon excelis ei vaja kontrollimiseks ANOVA-d ja kohandatud R-ruutu. Neid funktsioone saab mitme lineaarse regressiooni puhul arvestada. Mis jääb selle artikli reguleerimisalast välja.
Soovitatavad artiklid
See on olnud juhend regressioonanalüüsiks Excelis. Siin arutatakse, kuidas teha Excelis regressioonanalüüsi koos exceli näidete ja allalaaditava exceli malliga. Võite vaadata ka meie teisi soovitatud artikleid -
- Andmete analüüsi Exceli tööriist
- Arvutage ANOVA Excelis
- Kuidas leida Exceli liikuvaid keskmisi
- Z Testi näited Excelis