Kuinka tehdä regressioanalyysi Excelissä

Regressio on analyysityökalu, jota käytämme suurten tietomäärien analysointiin ja ennusteiden tekemiseen Microsoft Excelissä.

Haluatko ennustaa tulevaisuuden? Ei, emme aio oppia astrologiaa. Olemme lukuihin ja opimme regressioanalyysin Excelissä tänään.

Tulevien arvioiden ennustamiseksi tutkimme:

• REGRESSIOANALYYSI EXCEL -TOIMINTOJEN KÄYTTÖÖN (MANUAL REGRESSION HINDING)

• REGRESSION ANALYSIS EXCEL'S ANALYSIS TOOLPAK ADD-IN

• REGRESSION CHART IN EXCEL

Tehdään se…

Skenaario:

Oletetaan, että myyt virvoitusjuomia. Kuinka siistiä se on, jos voit ennustaa:

• Kuinka monta virvoitusjuomaa myydään ensi vuonna edellisen vuoden tietojen perusteella?
• Mihin aloihin kannattaa keskittyä?
• Ja miten voit lisätä myyntiäsi muuttamalla strategiaasi?

Siitä tulee kannattavasti upea. Aivan?… Tiedän. Aloitetaan siis.

Sinulla on 11 tietuetta myyjistä ja virvoitusjuomista.

Näiden tietojen perusteella haluat ennustaa, kuinka paljon myyjiä tarvitaan virvoitusjuomien 2000 myyntiin.

Regressioyhtälö on työkalu tällaisten läheisten arvioiden tekemiseen. Tätä varten meidän on ensin tiedettävä regressio.

REGRESSIOANALYYSI EXCEL -TOIMINTOJEN KÄYTTÖÖN (MANUAALINEN REGRESSION FINDING)

Tämä osa auttaa sinua ymmärtämään regression paremmin kuin vain kertomaan Excel -regressiomenettelyn.

Johdanto:

Yksinkertainen lineaarinen regressio:

Kahden muuttujan välisen suhteen tutkimusta kutsutaan yksinkertaiseksi lineaariseksi regressioksi. Missä yksi muuttuja riippuu toisesta riippumattomasta muuttujasta. Riippuvaa muuttujaa kutsutaan usein nimillä Driven, Response ja Target. Ja riippumaton muuttuja lausutaan usein Driving-, Predictor- tai yksinkertaisesti Independent -muuttujaksi. Nämä nimet kuvaavat niitä selvästi.

Vertaillaan nyt tätä skenaarioosi. Haluat tietää, kuinka monta myyjää tarvitaan saavuttaakseen 2000 myyntiä. Joten tässä riippuva muuttuja on myyjien määrä ja riippumaton muuttuja myydään virvoitusjuomia.

Riippumaton muuttuja on useimmiten merkitty nimellä x ja riippuvainen muuttuja as y.

Meidän tapauksessamme myydään virvoitusjuomia x ja myyjien määrä on y.

Jos haluamme tietää, kuinka monta virvoitusjuomaa myydään, jos nimitämme 200 myyjää, silloin skenaario on päinvastoin.

Siirrytään eteenpäin.

Lineaarisen regressioyhtälön "yksinkertainen" matematiikka:

No, se ei ole yksinkertaista. Mutta Excel teki sen yksinkertaiseksi.

Meidän on ennustettava tarvittava myyntimäärä kaikille 11 tapaukselle, jotta saamme 12. lähimmän ennusteen.

Sanokaamme:

Virvoitusjuoma myyty on x

Numero myyjistä on y

Ennustettu y (myyjien määrä) myös soitti Regressioyhtälö, olisi

x*Kaltevuus+sieppaus (rentoudu, minulla on se peitetty)

Nyt täytyy varmaan ihmetellä, missä tila saatko kaltevuuden ja sieppauksen. Älä huoli, Excelillä on toimintoja heille. Sinun ei tarvitse oppia löytämään rinne ja siepata se manuaalisesti.

Jos haluat, valmistan siitä erillisen opetusohjelman. Kerro minulle kommenttiosiossa. Nämä ovat tärkeitä tietojen analysointityökaluja.

Siirrytään nyt laskelmiin:

Vaihe 1: Valmista tämä pieni pöytä

Vaihe 2: Etsi regressiolinjan kaltevuus

Excel -toiminto rinteille on

= KALLISTUS (tunnettu_y, tunnettu_x)

Tunnetut_si ovat kantaman sisällä B2: B12 ja tunnettu_x ovat alueella C2: C12

Solussa B16, kirjoita alla oleva kaava

= KALLISTUS (B2: B12, C2: C12)

(Huomaa: Kaltevuutta kutsutaan myös x -kerroimeksi regressioyhtälössä)

Sinä saat 0.058409. Pyöristä 2 desimaalinumeroa ja saat 0.06.

Vaihe 3: Etsi regressiolinjan leikkauspiste

Excel -toiminto sieppaukseen on

=INTERCEPT (tunnettu_y, tunnettu_x)

Tiedämme mitä meidän tunnetut x: t ja y: t

Solussa B17, kirjoita tämä kaava muistiin

= INTERCEPT (B2: B12, C2: C12)

Saat arvon -1,1118969. Pyöristä 2 desimaalin numeroon. Sinä saat -1.11.

Lineaarisen regressioyhtälömme on = x*0,06 + (-1,11). Nyt voimme helposti ennustaa mahdollisen y kohteen x mukaan.

Vaihe 4: Kirjoita D2 -kaava alla olevaan kaavaan

=C2*$ B $ 16+$ B $ 17(Regressioyhtälö)

Saat arvon 13.55.

Valitse D2 - D13 ja paina CTRL+D kaavan täyttämiseksi alueella D2: D13

Solussa D13 sinulla on tarvittava määrä myyjiä.

Näin ollen tavoitteen saavuttamiseksi 2000 Virvoitusjuomamyynti, tarvitset arviolta 115,71 tai 116 myyjää, koska ihmisten paloittelu on laitonta.

Nyt tämän avulla voit helposti tehdä mitä-jos-analyysin Excelissä. Muuta vain myyntien lukumäärää, ja se osoittaa, että monet myyjät tarvitsevat tämän myyntitavoitteen saavuttamiseksi.

Pelaa sen ympärillä selvittääksesi:

Kuinka paljon työvoimaa tarvitset myynnin lisäämiseksi?

Kuinka monta myyntiä kasvaa, jos lisäät myyjiäsi?

Tee arviostasi luotettavampi:

Nyt tiedät, että tarvitset 116 myyjää saadaksesi 2000 myyntiä.

Analytiikassa mitään ei vain sanota ja uskoa. Sinun on annettava arviosi prosenttiosuus luotettavuudesta. Se on kuin antaa todistus yhtälöstäsi.

Korrelaatiokertoimen kaava:

Seuraavaksi sinulta kysytään, kuinka paljon nämä kaksi muuttujaa liittyvät toisiinsa. Staattisessa mielessä sinun on kerrottava korrelaatiokerroin.

Excel -korrelaatiofunktio on

= CORREL (array1, array2)

Sinun tapauksessasi tunnettu_x ja Know_y ovat taulukko1 ja array2 riippumatta.

Syötä tämä kaava kohtaan B18

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

Sinulla tulee olemaan 0.919090. Muotoile solu B2 prosentteiksi. Nyt on 92% korrelaatiosta.

No mitäs tämä 92% tarkoittaa. Se tarkoittaa, siellä 92% myynnin mahdollisuudet kasvavat, jos lisäät myyjien määrää ja 92% myynti vähenee, jos vähennät myyjien määrää. Sitä kutsutaan Positiivinen korrelaatiokerroin.

R -apulainen (R^2):

R Squire -arvo kertoo, kuinka monta prosenttia regressioyhtälösi ei ole sattuma. Kuinka paljon se pitää paikkansa toimitettujen tietojen perusteella.

Excel -toiminto R squire on RSQ.

RSQ (tunnettu_y, tunnettu_x)

Meidän tapauksessamme saamme R -squire -arvon solussa B19.

Syötä tämä kaava kohtaan B19

= RSQ (B2: B12, C2: C12)

Joten meillä on 84% neliön arvosta. Mikä on erittäin hyvä selitys regressiollemme. Siinä sanotaan, että 84% datastamme ei ole sattumaa. Y (myyjien määrä) on hyvin riippuvainen X: stä (virvoitusjuomien myynti).

On monia muita testejä, joita voimme tehdä näille tiedoille taataksemme regressiomme. Mutta manuaalisesti se on monimutkainen ja pitkä toimenpide. Siksi Excel tarjoaa analyysityökalupaketin. Tämän työkalun avulla voimme tehdä tämän regressioanalyysin muutamassa sekunnissa.

REGRESSIO EXCELISSÄ EXCEL'S ANALYSISTOOLPAK ADD-IN

Jos tiedät jo mitä regressioyhtälöt ovat ja haluat vain tulokset nopeasti, tämä osa on sinua varten. Mutta jos haluat ymmärtää regressioyhtälöt helposti, vieritä kohtaan REGRESSION ANALYSIS EXCEL FUNCTIONS (MANUAL REGRESSION FINDING).

Excel tarjoaa analyysityökaluissaan joukon analyysityökaluja. Oletuksena se ei ole käytettävissä Tiedot -välilehdessä. Sinun on lisättävä se. Joten lisätään se ensin.

Analyysityökalupakin lisääminen Excel 2016: een

Jos et tiedä missä Excelin data -analyysi on, toimi seuraavasti

Vaihe 1: Siirry Excel -asetuksiin: Tiedosto? Vaihtoehdot? Lisäosat

Vaihe 2: Napsauta Lisäosat. Näet luettelon käytettävissä olevista apuohjelmista.

Valitse Analysis ToolPak ja etsi ikkunan alareunasta Manage. Hallinnassa valitse Excel-apuohjelmat ja napsauta GO.

Lisäosien ikkuna avautuu. Valitse tässä Analysis ToolPak. Napsauta sitten OK -painiketta.

Nyt voit käyttää kaikkia Data Analysis ToolPakin toimintoja Data -välilehdeltä.

Analyysityökalun käyttäminen regressioon

Vaihe 1: Siirry Data -välilehteen, Etsi data -analyysi. Napsauta sitä sitten.

Näyttöön tulee valintaikkuna.

Vaihe 2: Etsi Analyysityökalut -luettelosta "Regressio" ja paina OK -painiketta.

Taantuma syöttöikkuna avautuu. Näet useita käytettävissä olevia syöttövaihtoehtoja. Mutta toistaiseksi keskitymme vain Y -alueeseen ja X -alueeseen, jättäen kaiken muun oletusarvoon.

Vaihe 4: Anna tulot:

Myyjien määrä on Y

Virvoitusjuomien myynti on X

Siten

• Y -alue = B2: B11

Ja

• X -alue = C2: C11

Lähtöalueelle olen valinnut E4 samasta taulukosta. Voit valita uuden laskentataulukon saadaksesi tuloksia samassa työkirjassa olevalle uudelle laskentataulukolle tai kokonaan uudelle työkirjalle. Kun olet syöttänyt syötteesi, paina OK -painiketta.

Tulokset:

Sinulle näytetään erilaisia ​​tietoja tiedoistasi. Älä ylikuormitu. Sinun ei tarvitse syödä kaikkia ruokia.

Käsittelemme vain niitä tuloksia, jotka auttavat meitä arvioimaan tarvittavan myyntimäärän

Vaihe 5: Tiedämme regressioyhtälön y, tuo on

x*Kaltevuus+sieppaus

Meidän on vain löydettävä Kaltevuus ja Siepata tuloksissa.

Ja tässä he ovat.

Katkaisukerroin on selvästi mainittu.

Kaltevuus on kirjoitettu seuraavasti:X Muuttuja 1", Joskus mainittiin myös X -kerroimena. Pyöristä ne ylös ja saamme -1.11 Intercept ja 0.06 kaltevuutena.

Vaihe 6: Tulosten perusteella voimme ohjata regressioyhtälön. Ja se olisi

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

Valmista tämä taulukko Excelissä.

Toistaiseksi, x on 2000, joka on solussa E2.

Kirjoita tämä kaava soluun F2

= E2*F21+F20

Saat tuloksen 115.7052757.

Pyöristäminen antaa meille 116 pakollisista myyjistä.

Olemme siis oppineet muodostamaan regressioyhtälön manuaalisesti ja Analysis ToolPakin avulla. Kuinka voit käyttää tätä yhtälöä tulevien tilastojen arvioimiseen?

Ymmärrämme nyt Analysis Toolpakin antaman regressiotuloksen.

Regressiotuloksen ymmärtäminen:

Ei ole hyötyä, jos teet regressioanalyysin Excelin analyysityökalupaketilla etkä voi tulkita sen merkitystä.

Yhteenveto -osio:

Kuten nimestä voi päätellä, se on yhteenveto tiedoista.

1. 1. Useita R: Se kertoo kuinka sovi regressioyhtälö dataan. Sitä kutsutaan myös korrelaatiokerroimeksi.

Meidän tapauksessamme se on 0.919090619 tai 0.92 (pyöristää ylöspäin). Tämä tarkoittaa, että myynnin kasvun mahdollisuus on 92%, jos kasvatamme myyntimääräämme.

1. 1. R -neliö: Se kertoo löydetyn regression luotettavuudesta. Se kertoo kuinka monta havaintoa on osa regressiomme. Meidän tapauksessamme se on 0,844727566 tai 0,85. Se tarkoittaa, että regressiomme sopii 85%.
   2. Säädetty R -neliö: Säädetty neliö on vain todistettu versio R -neliöstä. Hyödyllinen pääasiassa usean regressioanalyysin yhteydessä.
   3. Vakiovirhe: Vaikka R. Squire kertoo, kuinka monta datapistettä on lähellä regressiolinjaa, vakiovirhe kertoo, kuinka pitkälle datapiste voi mennä regressiolinjasta.

Meidän tapauksessamme se on 6.74.

1. Havainto: Tämä on yksinkertaisesti havaintojen määrä, joka on esimerkissämme 11.

Anova -osio:

Tätä osaa ei juurikaan käytetä lineaarisessa regressiossa.

1. df. Se on vapauden aste. Sitä käytetään laskettaessa regressiota manuaalisesti.
2. SS. Neliöiden summa. Se on vain varianssien neliöiden summa. Käytetään R -ruutuarvojen löytämiseen.
3. NEITI. Tämä tarkoittaa neliöarvoa.
4. Ja 5. F ja F: n merkitys. Jos F: n merkitys (kaltevuuden p-arvo) on pienempi kuin F-testi, voit hylätä nollahypoteesin ja todistaa hypoteesisi. Yksinkertaisella kielellä voit päätellä, että x: llä on jonkin verran vaikutusta y: hen, kun sitä muutetaan.

Meidän tapauksessamme F on 48,96264 ja F: n merkitys on 0,000063. Se tarkoittaa, että regressiomme sopii dataan.

Regressio -osa:

Tässä osiossa on kaksi tärkeintä arvoa regressioyhtälöllemme.

1. Leikkaus: Meillä on täällä sieppaus, joka kertoo, missä x-leikkaus Y: ssä. Tämä on tärkeä osa regressioyhtälöä. Meidän tapauksessamme se on -1,11.
2. X muuttuja 1 (Kaltevuus). Kutsutaan myös kertoimeksi x. Se määrittää regressiolinjan tangentin.

REGRESSION CHART IN EXCEL

Excelissä on helppo piirtää regressiokaavio. Noudata vain näitä ohjeita. Voit lisätä regressiotaulukon Excelissä 2016, 2013 ja 2010 noudattamalla näitä yksinkertaisia ​​ohjeita.

Vaihe 1. Merkitse tunnetut x: t ensimmäiseen sarakkeeseen ja tiedä, että y on toisessa sarakkeessa.

Meidän tapauksessamme tiedämme, että Known_ x on myytyjä virvoitusjuomia. Ja tiedossa olevat ovat myyjiä.

Vaihe 2. Valitse tunnetut x- ja y -alueesi.

Vaihe 3: Siirry Lisää -välilehteen ja napsauta pistekaaviota.

Saat kaavion, joka näyttää tältä.

Vaihe 4. Lisää trendirivi: Siirry asetteluun ja etsi trendiviiva -vaihtoehto analyysiosasta.

Napsauta Trendline -vaihtoehdon alla Lineaarinen trendiviiva.

Saat kaaviosi tältä.

Tämä on regressiokaaviosi.

Jos nyt lisäät alla olevat tiedot ja laajennat valittuja tietoja. Näet kaaviossa muutoksen.

Esimerkissä lisäsimme myytyyn virvoitusjuomaan 2000 ja jätimme myyjät tyhjäksi. Ja kun laajennamme kaavion aluetta, meillä on tämä.

Se antaa tarvittavan määrän myyjiä, jotka voivat tehdä vuoden 2000 graafisten virvoitusjuomien myynnin. Mikä on kaaviossa hieman alle 120. Ja regressioyhtälöstämme tiedämme, että se on 116.

Tässä artikkelissa yritin kattaa kaiken Excel -regressioanalyysin alla. Selitin regression Excelissä 2016. Regressio Excel 2010: ssä ja Excel 2013: ssa on sama kuin Excel 2016: ssa.

Jos haluat kysyä lisää tästä aiheesta, käytä kommentteja. Esitä kysymys, anna mielipide tai mainitse vain kielioppivirheeni. Kaikki on tervetullutta. Älä epäröi käyttää kommenttiosaa.

TILA -toiminnon laskeminen Excelissä

Kuinka laskea keskiarvo -toiminto Excelissä

Kuinka luoda keskihajontakaavio

Kuvaavat tilastot Microsoft Excel 2016: ssa

Excelin NORMDIST -toiminnon käyttäminen

Pareto -kaavion ja analyysin käyttö

Suosittuja artikkeleita:

50 Excel -pikakuvake tuottavuuden lisäämiseksi

VLOOKUP -toiminnon käyttäminen Excelissä

COUNTIF -toiminnon käyttäminen Excel 2016: ssa

SUMIF -toiminnon käyttäminen Excelissä