Kuidas teha Excelis regressioonanalüüsi

Regressioon on analüüsivahend, mida kasutame Microsoft Excelis suurte andmemahtude analüüsimiseks ning prognooside tegemiseks.

Kas soovite tulevikku ennustada? Ei, me ei hakka astroloogiat õppima. Oleme numbritega ja õpime täna Excelis regressioonanalüüsi.

Tulevaste hinnangute ennustamiseks uurime:

• REGRESSIOONIANALÜÜS EXCEL -FUNKTSIOONIDE KASUTAMISEL (MANUAL REGRESSION FINDING)

• REGRESSIOONIANALÜÜS, KASUTATES EXCELI ANALÜÜSITÖÖRIISTAPAKENDI LISA

• REGRESSIOONITABEL EXCELIS

Teeme seda…

Stsenaarium:

Oletame, et müüte karastusjooke. Kui lahe see on, kui saate ennustada:

• Kui palju karastusjooke müüakse järgmisel aastal eelmise aasta andmete põhjal?
• Millistele valdkondadele tuleb keskenduda?
• Ja kuidas saate strateegiat muutes oma müüki suurendada?

See saab olema tulusalt vinge. Õige?… Ma tean. Nii et alustame.

Teil on 11 rekordit müügimeeste ja karastusjookide kohta.

Nüüd soovite nende andmete põhjal ennustada müügimeeste arvu, mis on vajalik karastusjookide 2000 müügi saavutamiseks.

Regressioonivõrrand on vahend selliste lähedaste hinnangute tegemiseks. Selleks peame kõigepealt teadma regressiooni.

REGRESSIOONIANALÜÜS EXCEL -FUNKTSIOONIDE KASUTAMISEL (MANUAL REGRESSION FINDING)

See osa aitab teil mõista regressiooni paremini kui lihtsalt öelda Exceli regressiooniprotseduur.

Sissejuhatus:

Lihtne lineaarne regressioon:

Kahe muutuja vahelise seose uurimist nimetatakse lihtsaks lineaarseks regressiooniks. Kus üks muutuja sõltub teisest sõltumatust muutujast. Sõltuvat muutujat nimetatakse sageli selliste nimedega nagu Driven, Response ja Target. Ja sõltumatut muutujat hääldatakse sageli sõidu-, ennustaja- või lihtsalt sõltumatu muutujana. Need nimed kirjeldavad neid selgelt.

Nüüd võrdleme seda teie stsenaariumiga. Sa tahad teada, mitu müügimeest on vaja saavutada 2000 müüki. Seega on siin sõltuv muutuja müügimeeste arv ja sõltumatu muutuja karastusjoogid.

Sõltumatut muutujat tähistatakse enamasti kui x ja sõltuv muutuja nagu y.

Meie puhul müüakse karastusjooke x ja müügimeeste arv on y.

Kui me tahame teada, kui palju karastusjooke müüakse, kui me seda nimetame 200 müügimeest, siis on stsenaarium vastupidi.

Liigub edasi.

Lineaarse regressioonivõrrandi „lihtne” matemaatika:

Noh, see pole lihtne. Kuid Excel tegi selle tegemise lihtsaks.

Peame prognoosima kõigi 11 juhtumi jaoks vajaliku müügimeeste arvu, et saada 12. lähim ennustus.

Ütleme:

Karastusjook Müüdud on x

Number müügimeestest on y

Ennustatud y (müügimeeste arv) ka helistas Regressioonivõrrand, oleks

x*Kallak+pealtkuulamine (lõdvestu, mul on see kaetud)

Nüüd peate ilmselt mõtlema, kus stat kas saate kallaku ja vahele. Ärge muretsege, Excelil on nende jaoks funktsioone. Te ei pea õppima, kuidas leida kallakut ja seda käsitsi kinni püüda.

Soovi korral koostan selleks eraldi õpetuse. Andke mulle teada kommentaaride jaotises. Need on mõned olulised andmeanalüüsi tööriistad.

Nüüd asume oma arvutuste juurde:

Samm 1: Valmistage see väike laud ette

2. samm: Leidke regressioonijoone kalle

Exceli funktsioon nõlvade jaoks on

= SLOPE (tuntud_y, tuntud_x)

Teie teadaolevad aastad on vahemikus B2: B12 ja tuntud_x on vahemikus C2: C12

Lahtris B16, kirjuta allpool olev valem

= KALLE (B2: B12, C2: C12)

(Märkus: kallakut nimetatakse ka regressioonivõrrandi x -i koefitsiendiks)

Sa saad 0.058409. Ümardage kuni kahe kümnendkohani ja saate 0.06.

3. samm: Leidke regressioonijoone lõikepunkt

Lõikamise Exceli funktsioon on

=INTERCEPT (tuntud_y, tuntud_x)

Me teame, mis meie tuntud x ja y

Lahtris B17, kirjutage see valem üles

= INTERCEPT (B2: B12, C2: C12)

Saate väärtuse -1.1118969. Ümardage kuni 2 kohta pärast koma. Sa saad -1.11.

Meie lineaarne regressioonivõrrand on = x*0,06 + (-1,11). Nüüd saame hõlpsalt ennustada võimalikku y sõltuvalt sihtmärgist x.

4. samm: D2 -s kirjutage allpool olev valem

=C2*$ B $ 16+$ B $ 17(Regressioonivõrrand)

Saate väärtuse 13.55.

Valige D2 kuni D13 ja vajutage CTRL+D vahemiku valemi täitmiseks D2: D13

Lahtris D13 teil on vajalik arv müügimehi.

Seega eesmärgi saavutamiseks 2000 Karastusjookide müük, vajate hinnanguliselt 115,71 müüjat või ütleme 116, kuna inimeste tükeldamine on ebaseaduslik.

Seda kasutades saate Excelis hõlpsasti läbi viia Mis-Ifi analüüsi. Muutke lihtsalt müügi arvu ja see näitab teile, et selle müügieesmärgi saavutamiseks kulub palju müügimehi.

Mängige selle ümber, et teada saada:

Kui palju tööjõudu vajate müügi suurendamiseks?

Kui palju müüki suureneb, kui suurendate oma müügimehi?

Muutke oma hinnang usaldusväärsemaks:

Nüüd teate, et 2000 müügi tegemiseks vajate 116 müügimeest.

Analüütikas ei räägita ega usuta midagi. Hinnangul peate esitama protsendi usaldusväärsusest. See on nagu võrranditunnistuse andmine.

Korrelatsioonikordaja valem:

Järgmisena küsitakse, kui palju need kaks muutujat on omavahel seotud. Staatilises mõttes peate ütlema korrelatsioonikoefitsiendi.

Exceli funktsioon korrelatsiooniks on

= CORREL (massiiv1, massiiv2)

Teie puhul on teadaolevad_x ja Know_y omad sõltumatult massiiv1 ja massiiv2.

Sisestage lahtrisse B18 see valem

= CORREL ((B2: B12, C2: C12)

Sul on 0.919090. Vormindage lahter B2 protsendiks. Nüüd on 92% korrelatsioonist.

Nüüd, mis see 92% tähendab. See tähendab, seal 92% müügivõimaluste kasv, kui suurendate müügimeeste arvu ja 92% müük väheneb, kui vähendate müügimeeste arvu. Seda nimetatakse Positiivne korrelatsioonikordaja.

R Squire (R^2):

R Squire väärtus ütleb teile, mitu protsenti ei ole teie regressioonivõrrand juhus. Kui palju see on esitatud andmete põhjal täpne.

Exceli funktsioon R squire jaoks on RSQ.

RSQ (tuntud_ajad, teadaolevad_x -id)

Meie puhul saame lahtris B19 ruutu väärtuse R.

Sisestage lahtrisse B19 see valem

= RSQ (B2: B12, C2: C12)

Seega on meil ruudu väärtusest 84%. Mis on väga hea seletus meie taandarengule. See ütleb, et 84% meie andmetest pole lihtsalt juhus. Y (müügimeeste arv) sõltub suuresti X -st (karastusjookide müük).

Nende andmetega saab teha palju muid teste, et tagada meie regressioon. Kuid käsitsi on see keeruline ja pikk protseduur. Seetõttu pakub Excel analüüsi tööriistapakki. Selle tööriista abil saame selle regressioonianalüüsi teha sekunditega.

REGRESSIOON EXCELIS, KASUTATES EXCELI ANALÜÜSITÖÖRIISTAKSID LISA

Kui teate juba, mis on regressioonivõrrandid, ja soovite lihtsalt kiireid tulemusi, siis see osa on teie jaoks. Kuid kui soovite regressioonivõrranditest hõlpsasti aru saada, kerige üles jaotiseni REGRESSION ANALYSIS, kasutades EXCEL -FUNKTSIOONE (MANUAL REGRESSION FINDING).

Excel pakub oma analüüsitööriistade paketis hulga tööriistu analüüsimiseks. Vaikimisi pole see vahekaardil Andmed saadaval. Peate selle lisama. Nii et lisame selle kõigepealt.

Analüüsi tööriistakomplekti lisamine Excelisse 2016

Kui te ei tea, kus Excelis on andmete analüüs, järgige neid samme

Samm: avage Exceli suvandid: Fail? Valikud? Lisandmoodulid

Samm: klõpsake lisandmoodulitel. Näete saadaolevate lisandmoodulite loendit.

Valige Analysis ToolPak ja leidke akna allservast haldamine. Valige halduses Exceli lisandmoodulid ja klõpsake nuppu GO.

Avaneb lisandmoodulite aken. Siin valige Analysis ToolPak. Seejärel klõpsake nuppu OK.

Nüüd pääsete vahekaardilt Andmed juurde kõigile andmeanalüüsi ToolPaki funktsioonidele.

Analüüsitööriistapaketi kasutamine regressiooni jaoks

Samm: minge vahekaardile Andmed, leidke andmete analüüs. Seejärel klõpsake sellel.

Ilmub dialoogiboks.

Samm: leidke analüüsivahendite loendist „Regressioon” ja klõpsake nuppu OK.

Taandareng avaneb sisestusaken. Näete mitmeid saadaolevaid sisestusvalikuid. Kuid praegu keskendume ainult Y ja X vahemikule, jättes kõik muu vaikimisi.

4. samm: sisendite esitamine:

Müügimeeste arv on Y

Karastusjookide müük on X

Seega

• Y vahemik = B2: B11

Ja

• X vahemik = C2: C11

Väljundvahemiku jaoks olen valinud samal lehel E4. Saate valida uue töölehe, et saada tulemusi sama töövihiku uuele töölehele või täielikult uuele töövihikule. Kui olete sisenditega lõpetanud, klõpsake nuppu OK.

Tulemused:

Teile esitatakse teie andmetest mitmesugust teavet. Ärge laske end üle koormata. Te ei pea kõiki nõusid tarbima.

Tegeleme ainult nende tulemustega, mis aitavad meil hinnata vajalikku müügimeeste arvu

5. samm: Me teame regressioonivõrrandit selle hindamiseks y, see on

x*Kallak+pealtkuulamine

Peame lihtsalt leidma Kallak ja Püüdmine tulemustes.

Ja siin nad on.

Lõikekoefitsient on selgelt mainitud.

Kallak on kirjutatud kui "X Muutuja 1’, Mõnikord mainiti seda ka X koefitsiendina. Ümardage need kokku ja saame -1.11 kui pealtkuulamine ja 0,06 kui kalle.

6. samm: Tulemuste põhjal saame juhtida regressioonivõrrandit. Ja see oleks

= x*(0,06) + (-1,11)

Valmistage see tabel ette Excelis.

Praeguseks, x on 2000, mis on lahtris E2.

Lahtrisse F2 sisestage see valem

= E2*F21+F20

Saate tulemuse 115.7052757.

Ümardamine annab meile 116 nõutavatest müügimeestest.

Nii oleme õppinud regressioonivõrrandit käsitsi moodustama ja analüüsi tööriistapakki kasutades. Kuidas saate seda võrrandit kasutada tulevase statistika hindamiseks?

Mõistame nüüd analüüsitööriista pakutud regressiooniväljundit.

Regressiooniväljundi mõistmine:

Pole kasu, kui teete regressioonianalüüsi, kasutades Exceli analüüsivahendite paketti ega saa selle tähendust tõlgendada.

Kokkuvõtte jaotis:

Nagu nimigi ütleb, on see andmete kokkuvõte.

1. 1. Mitu R: see näitab, kui sobiv on regressioonivõrrand andmetega. Seda nimetatakse ka korrelatsioonikoefitsiendiks.

Meie puhul see on 0.919090619 või 0.92 (kokku võtma). See tähendab, et kui suurendame müügimeeste arvu, on müügi suurenemise tõenäosus 92%.

1. 1. R ruut: See näitab leitud regressiooni usaldusväärsust. See ütleb meile, kui palju vaatlusi on osa meie regressiooniliinist. Meie puhul on see 0,844727566 või 0,85. See tähendab, et meie regressioon sobib 85%.
   2. Reguleeritud R -ruut: Reguleeritud ruut on lihtsalt R -ruudu tunnustatud versioon. Peamiselt kasulik mitme regressiooni analüüsis.
   3. Tavaline viga: Kui R. Squire ütleb teile, mitu andmepunkti langeb regressioonijoone lähedale, siis standardviga ütleb teile, kui kaugele võib andmepunkt regressioonijoonest minna.

Meie puhul see on 6.74.

1. Vaatlus: see on lihtsalt vaatluste arv, mis on meie näites 11.

Anova jaotis:

Seda jaotist ei kasutata lineaarses regressioonis.

1. df. See on vabaduse aste. Seda kasutatakse regressiooni käsitsi arvutamisel.
2. SS. Ruutude summa. See on lihtsalt dispersioonide ruutude summa. Kasutatakse R squire väärtuste leidmiseks.
3. PRL. See tähendab ruudu väärtust.
4. Ja 5. F ja F tähtsus. Kui F tähtsus (kalde p-väärtus) on väiksem kui F-test, saate nullhüpoteesi tagasi lükata ja oma hüpoteesi tõestada. Lihtsas keeles võib järeldada, et muutmisel on x -il mingi mõju y -le.

Meie puhul on F 48,96264 ja F tähtsus 0,000063. See tähendab, et meie regressioon sobib andmetega.

Regressiooniosa:

Selles jaotises on meie regressioonivõrrandi jaoks kaks kõige olulisemat väärtust.

1. Lõikamine: Meil ​​on siin pealtkuulamine, mis ütleb, kus x-lõikab Y-l. See on regressioonivõrrandi oluline osa. Meie puhul on see -1,11.
2. X muutuja 1 (Kallak). Seda nimetatakse ka koefitsiendiks x. See määratleb regressioonijoone puutuja.

REGRESSIOONITABEL EXCELIS

Excelis on lihtne regressioonidiagrammi joonistada. Lihtsalt järgige neid samme. Regressioonitabeli lisamiseks Excelis 2016, 2013 ja 2010 järgige neid lihtsaid samme.

Samm 1. Märkige esimeses veerus teadaolevad x -id ja teises y.

Meie puhul teame, et Known_ x on müüdud karastusjoogid. Ja teadaolevad on müügimehed.

2. samm. Valige oma tuntud x ja y vahemik.

3. samm: Minge vahekaardile Lisa ja klõpsake hajumisdiagrammil.

Saate diagrammi, mis näeb välja selline.

Samm 4. Lisage trendirida: Minge paigutusse ja leidke analüüsi jaotises trendijoone valik.

Valiku Trendline all klõpsake Lineaarne trendijoon.

Teie graafik näeb välja selline.

See on teie regressioonigraafik.

Nüüd, kui lisate allpool olevad andmed ja laiendate valitud andmeid. Näete oma graafikul muutust.

Näiteks lisasime müüdud karastusjookidele 2000 ja jätsime müügimehed tühjaks. Ja kui laiendame graafiku ulatust, saame selle.

See annab graafilisel kujul karastusjookide 2000 müügi tegemiseks vajaliku arvu müügimehi. Mis on graafikul veidi alla 120. Ja meie regressioonivõrrandist teame, et see on 116.

Selles artiklis proovisin katta kõike Exceli regressioonianalüüsi all. Ma selgitasin regressiooni Excelis 2016. Regressioon Excel 2010 ja Excel 2013 on sama mis Excel 2016.

Selle teema kohta lisateabe saamiseks kasutage kommentaaride jaotist. Esitage küsimus, avaldage arvamust või mainige lihtsalt minu grammatilisi vigu. Kõik on teretulnud. Ärge kartke kasutada kommentaaride jaotist.

Kuidas arvutada funktsiooni MODE Excelis

Kuidas arvutada Excelis keskmist funktsiooni

Kuidas luua standardhälbe graafikut

Kirjeldav statistika Microsoft Excel 2016 -s

Funktsiooni Excel NORMDIST kasutamine

Kuidas kasutada Pareto diagrammi ja analüüsi

Populaarsed artiklid:

50 Exceli otsetee tootlikkuse suurendamiseks

Funktsiooni VLOOKUP kasutamine Excelis

Funktsiooni COUNTIF kasutamine Excelis 2016

Funktsiooni SUMIF kasutamine Excelis