Kako napraviti regresijsku analizu u Excelu

Sadržaj:

Anonim

Regresija je alat za analizu koji koristimo za analizu velikih količina podataka i izradu prognoza i predviđanja u programu Microsoft Excel.

Želite predvidjeti budućnost? Ne, nećemo učiti astrologiju. Bavimo se brojevima i danas ćemo u Excelu naučiti regresijsku analizu.

Kako bismo predvidjeli buduće procjene, proučit ćemo:

  • ANALIZA REGRESIJE KORIŠTENJEM EXCEL FUNKCIJA (PRIRUČNI NALAZ REGRESIJE)
  • REGRESIJSKA ANALIZA KORIŠĆENJEM EXCELOVE ANALIZNE ALATKE DODATAK
  • REGRESIJSKA LISTA U ODLIČNOM

Učinimo to…

Scenarij:

Pretpostavimo da prodajete bezalkoholna pića. Kako će biti super ako možete predvidjeti:

  • Koliko će se bezalkoholnih pića prodati sljedeće godine na temelju podataka iz prethodne godine?
  • Koja polja treba usredotočiti?
  • I kako možete povećati svoju prodaju promjenom strategije?

Bit će isplativo sjajno. Je li tako?… Znam. Pa krenimo.

Imate 11 evidencija prodanih prodavača i bezalkoholnih pića.

Sada na temelju ovih podataka želite predvidjeti broj prodavača potrebnih za 2000 prodaja bezalkoholnih pića.

Regresijska jednadžba je alat za takve bliske procjene. Da bismo to učinili, prvo moramo znati regresiju.

ANALIZA REGRESIJE KORIŠTENJEM EXCEL FUNKCIJA (PRIRUČNI NALAZ REGRESIJE)

Ovaj dio će vas bolje razumjeti nego samo reći excel regresijski postupak.

Uvod:

Jednostavna linearna regresija:

Proučavanje odnosa dviju varijabli naziva se jednostavna linearna regresija. Gdje jedna varijabla ovisi o drugoj neovisnoj varijabli. Ovisna varijabla često se naziva nazivima kao što su Driven, Response i Target varijabla. Nezavisna varijabla često se izgovara kao vožnja, prediktor ili jednostavno neovisna varijabla. Ova imena ih jasno opisuju.

Sada usporedimo ovo sa vašim scenarijem. Želite znati koliko je prodavača potrebno postići Prodaja 2000. Dakle, ovdje je ovisna varijabla broj prodavača, a neovisna varijabla prodaje se bezalkoholna pića.

Nezavisna varijabla uglavnom se označava kao x a ovisna varijabla kao y.

U našem slučaju se prodaju bezalkoholna pića x a broj prodavača je y.

Ako želimo znati koliko će se bezalkoholnih pića prodati ako to odredimo 200 prodavača, tada će scenarij biti obrnut.

Idemo dalje.

„Jednostavna“ matematika jednadžbe linearne regresije:

Pa nije jednostavno. No Excel je to učinio jednostavnim.

Moramo predvidjeti potreban broj prodavača za svih 11 slučajeva kako bismo dobili 12. najbliže predviđanje.

Recimo:

Bezalkoholno piće Prodano je x

Broj prodavača je y

Predviđeno y (broj prodavača) također nazvano Regresijska jednadžba, bilo bi

x*Nagib+presretanje (opusti se, sve je pokriveno)

Sada se sigurno pitate gdje je stat hoćete li dobiti kosinu i presresti. Ne brinite, Excel za njih ima funkcije. Ne morate naučiti kako pronaći nagib i ručno ga presresti.

Ako želite, za to ću pripremiti zaseban vodič. Javite mi u odjeljku za komentare. Ovo su neki važni alati za analizu podataka.

Idemo sada u naš izračun:

Korak 1: Pripremite ovaj mali stol

Korak 2: Pronađi nagib regresijske crte

Excel funkcija za padine je

= SLOPE (poznati_y, poznati_x)

Vaši poznati_y su u dometu B2: B12 i poznati_x su u rasponu C2: C12

U ćeliji B16, napišite donju formulu

= KOSA (B2: B12, C2: C12)

(Napomena: Nagib se u regresijskoj jednadžbi naziva i koeficijent x)

Dobit ćeš 0.058409. Zaokružite na 2 decimalne znamenke i dobit ćete 0.06.

Korak 3: Pronađite presjek regresijske crte

Excel funkcija za presretanje je

=INTERCEPT (poznati_y, poznati_x)

Znamo što je naše poznati x i y

U ćeliji B17, zapišite ovu formulu

= INTERCEPT (B2: B12, C2: C12)

Dobit ćete vrijednost -1.1118969. Zaokruži na 2 decimalne znamenke. Dobit ćeš -1.11.

Naša jednadžba linearne regresije je = x*0,06 + (-1,11). Sada možemo lako predvidjeti mogući y ovisno o cilju x.

Korak 4: U D2 napišite donju formulu

=C2*$ B $ 16+$ B $ 17(Jednadžba regresije)

Dobit ćete vrijednost od 13.55.

Odaberite D2 do D13 i pritisnite CTRL+D za popunjavanje formule u rasponu D2: D13

U ćeliji D13 imate potreban broj prodavača.

Dakle, za postizanje cilja 2000 Za prodaju bezalkoholnih pića potrebna vam je procjena od 115,71 prodavača ili recimo 116 jer je nezakonito rezati ljude na komade.

Sada pomoću ovoga možete jednostavno provesti What-If analizu u Excelu. Samo promijenite broj prodaje i to će vam pokazati koliko će prodavača biti potrebno da se postigne taj cilj prodaje.

Poigrajte se s tim i saznajte:

Koliko je radne snage potrebno za povećanje prodaje?

Koliko će se prodaja povećati ako povećate svoje prodavače?

Učinite svoju procjenu pouzdanijom:

Sada znate da vam je potrebno 116 prodavača da biste obavili 2000 prodaja.

U analitici se ništa ne govori i ne vjeruje. Na svojoj procjeni morate dati postotak pouzdanosti. To je poput davanja potvrde o svojoj jednadžbi.

Formula koeficijenta korelacije:

Sljedeće što će vas pitati je koliko su te dvije varijable povezane. Statički rečeno, morate reći koeficijent korelacije.

Excel funkcija za korelaciju je

= CORREL (niz1, niz2)

U vašem slučaju, known_x's i Know_y's su niz1 i niz2 neovisno.

U B18 unesite ovu formulu

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

Imat ćete 0.919090. Formirajte ćeliju B2 u postocima. Sada imajte 92% korelacije.

E sad, što je ovo 92% sredstva. Znači, tamo 92% šanse za povećanje prodaje povećate li broj prodavača i 92% smanjenje prodaje ako smanjite broj prodavača. To se zove Pozitivan koeficijent korelacije.

R štitonoša (R^2):

R Vrijednost štitonoše govori vam za koji postotak vaša regresijska jednadžba nije slučajnost. Koliko je točan prema navedenim podacima.

Excel funkcija za R squire je RSQ.

RSQ (poznati_y, poznati_x's)

U našem slučaju, dobit ćemo vrijednost R squire u ćeliji B19.

U B19 unesite ovu formulu

= RSQ (B2: B12, C2: C12)

Dakle, imamo 84% r kvadratne vrijednosti. Što je vrlo dobro objašnjenje naše regresije. Kaže da 84% naših podataka nije slučajno. Y (broj prodavača) uvelike ovisi o X (prodaja bezalkoholnih pića).

Postoje mnogi drugi testovi koje možemo učiniti na tim podacima kako bismo osigurali našu regresiju. No ručno će to biti složen i dugotrajan postupak. Zato Excel nudi paket alata za analizu. Pomoću ovog alata možemo napraviti ovu regresijsku analizu u nekoliko sekundi.

REGRESIJA U EXCELU KORIŠTENJEM EXCELOVE ANALIZNE ALATKE ADD-IN

Ako već znate što su regresijske jednadžbe i samo želite brzo rezultate, onda je ovaj dio za vas. No, ako želite lako razumjeti regresijske jednadžbe, pomaknite se gore do ANALIZA REGRESIJE KORIŠTENJEM ODLIČNIH FUNKCIJA (RUČNI REGRESIJSKI NALAZ).

Excel u svom paketu za analizu pruža čitav niz alata za analizu. Prema zadanim postavkama, nije dostupno na kartici Podaci. Morate ga dodati. Pa prvo dodajmo.

Dodavanje paketa alata za analizu u Excel 2016

Ako ne znate gdje je analiza podataka u Excelu, slijedite ove korake

Korak 1: Idite na Excel opcije: Datoteka? Opcije? Dodaci

Korak 2: Kliknite na Dodaci. Vidjet ćete popis dostupnih dodataka.

Odaberite Analysis ToolPak i pri dnu prozora pronađite upravljanje. U upravljanju odaberite Excel programske dodatke i kliknite GO.

Otvorit će se prozor dodataka. Ovdje odaberite Analysis ToolPak. Zatim kliknite gumb u redu.

Sada možete pristupiti svim funkcijama ToolPak za analizu podataka s kartice Podaci.

Korištenje Analysis ToolPak -a za regresiju

Korak 1: Idite na karticu Podaci, pronađite analizu podataka. Zatim kliknite na nju.

Pojavit će se okvir za dijalog.

Korak 2: Pronađite "Regresija" na popisu Alati za analizu i pritisnite gumb U redu.

Regresija pojavit će se prozor za unos. Vidjet ćete brojne dostupne opcije unosa. No, zasad ćemo se koncentrirati samo na Y raspon i X raspon, ostavljajući sve ostalo zadano.

Korak 4: Unesite unose:

Broj prodavača je Y

Prodaja bezalkoholnih pića je x

Stoga

  • Y raspon = B2: B11

I

  • X Raspon = C2: C11

Za raspon izlaza odabrao sam E4 na istom listu. Možete odabrati novi radni list da biste dobili rezultate na novom radnom listu u istoj radnoj knjizi ili u potpunoj novoj radnoj knjizi. Kada završite s unosom, pritisnite gumb OK.

Rezultati:

Bit će vam dostavljeni različiti podaci iz vaših podataka. Nemojte se preopteretiti. Ne morate konzumirati sva jela.

Bavit ćemo se samo onim rezultatima koji će nam pomoći u procjeni potrebnog broja prodavača

5. korak: Poznata nam je regresijska jednadžba za procjenu y, to je

x*Nagib+presretanje

Moramo samo locirati Nagib i Presresti u rezultatima.

I evo ih.

Koeficijent presretanja je jasno spomenut.

Nagib je napisan kao 'X varijabla 1’, Ponekad se spominje i kao koeficijent X. Zaokružite ih i dobit ćemo -1,11 kao presretanje i 0,06 kao nagib.

Korak 6: Iz rezultata možemo potaknuti regresijsku jednadžbu. I to bi bilo

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

Pripremite ovu tablicu u excelu.

Zasad, x je 2000, što je u ćeliji E2.

U ćeliju F2 unesite ovu formulu

= E2*F21+F20

Dobit ćete rezultat od 115.7052757.

Zaokruživanje će nam dati 116 potrebnih prodavača.

Tako smo naučili kako ručno oblikovati regresijsku jednadžbu i pomoću Analysis ToolPak -a. Kako možete koristiti ovu jednadžbu za procjenu budućih statistika?

Sada shvatimo izlaz regresije koji daje Analysis Toolpak.

Razumijevanje izlaza regresije:

Nema koristi ako radite regresijsku analizu pomoću paketa alata za analizu u Excelu i ne možete protumačiti njeno značenje.

Sažetak odjeljak:

Kao što naziv govori, to je sažetak podataka.

    1. Višestruko R: Govori koliko odgovara regresijska jednadžba podacima. Naziva se i koeficijent korelacije.

U našem slučaju je tako 0.919090619 ili 0.92 (okupite se). To znači da postoji 92% šanse za povećanje prodaje ako povećamo broj prodavača.

    1. R Square: Govori o pouzdanosti pronađene regresije. Govori nam koliko je opažanja dio naše regresijske linije. U našem slučaju to je 0,844727566 ili 0,85. To znači da je naša regresija sposobna za 85%.
    2. Prilagođeni kvadrat R: Prilagođeni kvadrat samo je svjedočenija verzija R kvadrata. Uglavnom korisno u analizi višestruke regresije.
    3. Standardna pogreška: Dok vam R. Squire govori koliko podatkovnih točaka pada u blizini regresijske crte, standardna pogreška govori vam koliko daleko podatkovna točka može ići od regresijske crte.

U našem slučaju je tako 6.74.

  1. Opažanje: Ovo je jednostavno broj opažanja, koji je u našem primjeru 11.

Anova odjeljak:

Ovaj odjeljak se rijetko koristi u linearnoj regresiji.

  1. df. To je stupanj slobode. Koristi se pri ručnom izračunavanju regresije.
  2. SS. Zbroj kvadrata. To je samo zbroj kvadrata varijance. Koristi se za pronalaženje vrijednosti R squire.
  3. MS. To znači vrijednost na kvadrat.
  4. I 5. F i značaj F. Ako je značaj F (p-vrijednost nagiba) manji od F testa, možete odbaciti nultu hipotezu i dokazati svoju hipotezu. Jednostavnim jezikom, možete zaključiti da postoji neki učinak x na y kada se promijeni.

U našem slučaju, F je 48,96264, a značajnost F je 0,000063. To znači da naša regresija odgovara podacima.

Odjeljak regresije:

U ovom odjeljku imamo dvije najvažnije vrijednosti za našu regresijsku jednadžbu.

  1. Presretanje: Ovdje imamo presretanje koje govori gdje x presreće na Y. Ovo je važan dio regresijske jednadžbe. U našem slučaju iznosi -1,11.
  2. X varijabla 1 (Nagib). Također se naziva koeficijent x. Definira tangentu regresijske crte.

REGRESIJSKA LISTA U ODLIČNOM

U Excelu je lako iscrtati regresijski grafikon. Samo slijedite ove korake. Za dodavanje regresijskog grafikona u Excel 2016, 2013 i 2010 slijedite ove jednostavne korake.

Korak 1. Neka vaši poznati x -i budu u prvom stupcu, a y u drugom.

U našem slučaju znamo da su Known_x's bezalkoholna pića koja se prodaju. A poznati_i su prodavači.

Korak 2. Odaberite svoj poznati x i y raspon.

3. korak: Idite na karticu Umetanje i kliknite raspršeni grafikon.

Imat ćete grafikon koji izgleda ovako.

Korak 4. Dodajte liniju trenda: Idite na izgled i u odjeljku za analizu pronađite opciju linije trenda.

Pod opcijom Trendline, kliknite Linear Trendline.

Vaš će grafikon izgledati ovako.

Ovo je vaš grafikon regresije.

Ako sada dodate donje podatke i proširite odabrane podatke. Vidjet ćete promjenu na grafikonu.

Za naš primjer, dodali smo 2000 u Bezalkoholno piće i prodavače ostavili prazno. A kad proširimo raspon grafikona, to ćemo imati.

To će dati potreban broj prodavača za 2000 prodaja bezalkoholnih pića u grafičkom obliku. Što je malo ispod 120 na grafikonu. Iz naše regresijske jednadžbe znamo da je 116.

U ovom sam članku pokušao obuhvatiti sve pod Excel regresijskom analizom. Objasnio sam regresiju u excelu 2016. Regresija u excelu 2010 i excelu 2013 ista je kao u excelu 2016.

Za sva dodatna pitanja o ovoj temi koristite odjeljak komentara. Postavite pitanje, dajte mišljenje ili samo spomenite moje gramatičke greške. Sve je dobrodošlo. Samo ne oklijevajte koristiti odjeljak komentara.

Kako izračunati funkciju MODE u Excelu

Kako izračunati srednju funkciju u Excelu

Kako stvoriti grafikon standardne devijacije

Opisna statistika u programu Microsoft Excel 2016

Kako koristiti Excel NORMDIST funkciju

Kako koristiti Paretov grafikon i analizu

Popularni članci:

50 Excel prečac za povećanje vaše produktivnosti

Kako koristiti funkciju VLOOKUP u Excelu

Kako koristiti funkciju COUNTIF u Excelu 2016

Kako koristiti funkciju SUMIF u Excelu