Kako narediti regresijsko analizo v Excelu

Kazalo:

Anonim

Regresija je orodje za analizo, ki ga uporabljamo za analizo velikih količin podatkov ter izdelavo napovedi in napovedi v programu Microsoft Excel.

Želite napovedati prihodnost? Ne, astrologije se ne bomo učili. Ukvarjamo se s številkami in se bomo danes v Excelu naučili regresijske analize.

Za napovedovanje prihodnjih ocen bomo preučili:

  • REGRESIJSKA ANALIZA Z UPORABO EXCEL FUNKCIJ (ROČNO UGOTOVITEV REGRESIJE)
  • REGRESIJSKA ANALIZA Z UPORABO EXCEL'S ANALYSIS TOOLPAK ADD-IN
  • REGRESIJSKA LISTA V ODLIČNEM

Naredimo to…

Scenarij:

Predpostavimo, da prodajate brezalkoholne pijače. Kako kul bo, če lahko napoveste:

  • Koliko brezalkoholnih pijač bo prihodnje leto prodanih na podlagi podatkov iz prejšnjega leta?
  • Katera področja je treba osredotočiti?
  • In kako lahko s spremembo strategije povečate prodajo?

Donosno bo super. Kajne? … vem. Pa začnimo.

Imate 11 evidenc prodanih prodajalcev in brezalkoholnih pijač.

Na podlagi teh podatkov želite predvideti število prodajalcev, potrebnih za 2000 prodaj brezalkoholnih pijač.

Regresijska enačba je orodje za tako natančne ocene. Za to moramo najprej poznati regresijo.

REGRESIJSKA ANALIZA Z UPORABO EXCEL FUNKCIJ (ROČNI UGOTOVITEV REGRESIJE)

Ta del vam bo pomagal bolje razumeti regresijo, kot pa samo povedati regresijski postopek excela.

Uvod:

Enostavna linearna regresija:

Študija odnosa med dvema spremenljivkama se imenuje Enostavna linearna regresija. Kjer je ena spremenljivka odvisna od druge neodvisne spremenljivke. Odvisna spremenljivka se pogosto imenuje z imeni, kot so spremenljivka Driven, Response in Target. Neodvisna spremenljivka se pogosto izgovarja kot spremenljivka za vožnjo, napoved ali preprosto neodvisna. Ta imena jih jasno opisujejo.

Zdaj pa primerjajmo to z vašim scenarijem. Želite vedeti, koliko prodajalcev je potrebno doseči 2000 prodaj. Tu je torej odvisna spremenljivka število prodajalcev, neodvisna spremenljivka pa se prodajajo brezalkoholne pijače.

Neodvisno spremenljivko večinoma označujemo kot x in odvisna spremenljivka kot y.

V našem primeru se brezalkoholne pijače prodajajo x in število prodajalcev je y.

Če želimo vedeti, koliko brezalkoholnih pijač bo prodanih, če to imenujemo 200 prodajalcev, potem bo scenarij obratno.

Iti naprej.

"Enostavna" matematika enačbe linearne regresije:

No, ni preprosto. Toda Excel je to poenostavil.

Predvideti moramo potrebno število prodajalcev za vseh 11 primerov, da dobimo 12. najbližjo napoved.

Recimo:

Brezalkoholne pijače Prodano je x

Število prodajalcev je y

Napovedano y (število prodajalcev) tudi poklical Regresijska enačba, bi bilo

x*Nagib+prestrezanje (sprostite se, vse je pokrito)

Zdaj se gotovo sprašujete, kje stat boš dobil pobočje in prestregel. Ne skrbite, Excel ima svoje funkcije. Ni vam treba naučiti, kako najti pobočje in ga ročno prestreči.

Če želite, bom za to pripravil ločeno vadnico. Sporočite mi v oddelku za komentarje. To je nekaj pomembnih orodij za analizo podatkov.

Zdaj pa stopimo v naš izračun:

Korak 1: Pripravite to majhno mizo

2. korak: Poiščite naklon regresijske črte

Excel Funkcija za pobočja je

= SLOPE (znani_y, znani_x)

Vaši znani_y so v dosegu B2: B12 in znani_x so v dosegu C2: C12

V celici B16, spodaj napiši formulo

= NOGO (B2: B12, C2: C12)

(Opomba: Nagib se v regresijski enačbi imenuje tudi koeficient x)

Dobil boš 0.058409. Zaokrožite na 2 decimalni mesti in dobili boste 0.06.

3. korak: Poiščite prestrezanje regresijske črte

Excel funkcija za prestrezanje je

=INTERCEPT (known_y's, known_x's)

Vemo, kaj je naše znani x in y

V celici B17, zapišite to formulo

= PREKREZI (B2: B12, C2: C12)

Dobili boste vrednost -1.1118969. Zaokroži na 2 decimalni mesti. Dobil boš -1.11.

Naša linearna regresijska enačba je = x*0,06 + (-1,11). Zdaj lahko z lahkoto predvidimo možen y, odvisno od cilja x.

4. korak: V D2 napišite spodnjo formulo

=C2*16 USD+17 B $(Regresijska enačba)

Dobili boste vrednost 13.55.

Izberite D2 do D13 in pritisnite CTRL+D za izpolnitev formule v obsegu D2: D13

V celici D13 imate potrebno število prodajalcev.

Zato je za dosego cilja 2000 Za prodajo brezalkoholnih pijač potrebujete oceno 115,71 prodajalcev ali recimo 116, saj je nezakonito razsekati ljudi na koščke.

Zdaj z uporabo tega lahko preprosto izvedete analizo Kaj-Če v Excelu. Samo spremenite število prodaj in pokazalo se vam bo, da bo veliko prodajalcev potrebno za dosego prodajnega cilja.

Poigrajte se, če želite izvedeti:

Koliko delovne sile potrebujete za povečanje prodaje?

Koliko prodaje se bo povečalo, če povečate svoje prodajalce?

Naredite svojo oceno bolj zanesljivo:

Zdaj veste, da za 2000 prodaj potrebujete 116 prodajalcev.

V analitiki se nič ne govori in ne verjame. V svoji oceni morate navesti odstotek zanesljivosti. To je tako, kot če bi dali potrdilo o svoji enačbi.

Formula korelacijskega koeficienta:

Naslednja stvar, ki vas bo vprašala, je, koliko sta ti dve spremenljivki povezani. V statičnem smislu morate povedati koeficient korelacije.

Excel funkcija za korelacijo je

= CORREL (matrika1, matrika2)

V vašem primeru sta znana_x -a in Know_y -ja matrika1 in matrika2 ne glede na to.

V B18 vnesite to formulo

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

Boste imeli 0.919090. Celico B2 formatirajte v odstotkih. Zdaj imajo 92% korelacije.

No, kaj je to 92% pomeni. Pomeni, tam 92% možnosti za povečanje prodaje, če povečate število prodajalcev in 92% zmanjšanje prodaje, če zmanjšate število prodajalcev. Se imenuje Pozitiven korelacijski koeficient.

R Squire (R^2):

R Vrednost Squire vam pove, za koliko odstotkov vaša regresijska enačba ni naključna. Koliko je natančno po predloženih podatkih.

Excel funkcija za R squire je RSQ.

RSQ (znani_y, znani_x)

V našem primeru bomo dobili vrednost R squire v celici B19.

V B19 vnesite to formulo

= RSQ (B2: B12, C2: C12)

Tako imamo 84% kvadratne vrednosti r. Kar je zelo dobra razlaga naše regresije. Pravi, da 84% naših podatkov ni naključje. Y (število prodajalcev) je zelo odvisno od X (prodaja brezalkoholnih pijač).

Za zagotovitev naše regresije lahko opravimo še veliko drugih testov. Toda ročno bo to zapleten in dolgotrajen postopek. Zato Excel ponuja paket orodja za analizo. S tem orodjem lahko naredimo to regresijsko analizo v nekaj sekundah.

REGRESIJA V EXCEL Z UPORABO EXCELOVE ANALIZE TOOLPAK ADD-IN

Če že veste, kaj so regresijske enačbe, in želite le hitre rezultate, potem je ta del za vas. Če pa želite zlahka razumeti regresijske enačbe, se pomaknite navzgor do REGRESIJSKA ANALIZA Z UPORABO ODLIČNIH FUNKCIJ (ROČNI UGOTOVITVE REGRESIJE).

Excel v svojem kompletu analiznih orodij ponuja cel kup orodij za analizo. Privzeto ni na voljo na zavihku Podatki. Morate ga dodati. Zato ga najprej dodajmo.

Dodajanje orodja za analizo v Excel 2016

Če ne veste, kje je analiza podatkov v Excelu, sledite tem korakom

1. korak: Pojdite na Excelove možnosti: Mapa? Opcije? Dodatki

2. korak: Kliknite na dodatke. Videli boste seznam razpoložljivih dodatkov.

Izberite Analysis ToolPak in na dnu okna poiščite upravljanje. V upravljanju izberite Excelove dodatke in kliknite GO.

Odpre se okno z dodatki. Tukaj izberite Analysis ToolPak. Nato kliknite gumb V redu.

Zdaj lahko dostopate do vseh funkcij orodja za analizo podatkov z zavihka Podatki.

Uporaba orodja Analysis ToolPak za regresijo

1. korak: Pojdite na zavihek Podatki in poiščite analizo podatkov. Nato kliknite nanjo.

Odpre se pogovorno okno.

2. korak: Na seznamu orodja za analizo poiščite »Regresija« in pritisnite gumb V redu.

Regresija se prikaže okno za vnos. Videli boste številne razpoložljive možnosti vnosa. Toda za zdaj se bomo osredotočili le na Y Range in X Range, vse ostalo pa pustimo privzetim.

4. korak: Vnesite vnose:

Št. Prodajalcev je Y

Prodaja brezalkoholnih pijač je X

Zato

  • Y Razpon = B2: B11

In

  • Razpon X = C2: C11

Za izhodno območje sem na istem listu izbral E4. Izberete lahko nov delovni list, da dobite rezultate na novem delovnem listu v istem delovnem zvezku ali celotnem novem delovnem zvezku. Ko končate s svojimi vnosi, pritisnite gumb V redu.

Rezultati:

Na voljo vam bodo različni podatki iz vaših podatkov. Ne obremenjujte se. Ni vam treba porabiti vseh jedi.

Ukvarjali se bomo le s tistimi rezultati, ki nam bodo pomagali oceniti potrebno število prodajalcev

5. korak: Poznamo regresijsko enačbo za oceno y, to je

x*Nagib+prestrezanje

Samo poiskati moramo Naklon in Prestreči v rezultatih.

In tukaj so.

Koeficient prestrezanja je jasno omenjen.

Pobočje je zapisano kot 'X spremenljivka 1’, Nekajkrat omenjen tudi kot koeficient X. Zaokrožite jih in dobili bomo -1,11 kot prestrezanje in 0,06 kot naklon.

6. korak: Iz rezultatov lahko poganjamo regresijsko enačbo. In to bi bilo

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

Pripravite to tabelo v Excelu.

Za zdaj, x je 2000, kar je v celici E2.

V celico F2 vnesite to formulo

= E2*F21+F20

Dobili boste rezultat 115.7052757.

Zaokroževanje nam bo dalo 116 potrebnih prodajalcev.

Tako smo se naučili, kako regresijsko enačbo oblikovati ročno in z orodjem Analysis ToolPak. Kako lahko uporabite to enačbo za oceno prihodnjih statističnih podatkov?

Zdaj pa razumejmo regresijski rezultat, ki ga podaja Tool Toolpak.

Razumevanje izida regresije:

Ne boste imeli koristi, če opravite regresijsko analizo z uporabo orodja za analizo v Excelu in ne morete razlagati njegovega pomena.

Povzetek:

Kot že ime pove, gre za povzetek podatkov.

    1. Več R: Pove, kako ustreza regresijska enačba podatkom. Imenuje se tudi korelacijski koeficient.

V našem primeru je tako 0.919090619 ali 0.92 (zaokroži navzgor). To pomeni, da obstaja 92 -odstotna možnost povečanja prodaje, če povečamo število prodajalcev.

    1. R Square: Pove zanesljivost ugotovljene regresije. Pove nam, koliko opazovanj je del naše regresijske črte. V našem primeru je 0,844727566 ali 0,85. To pomeni, da je naša regresija primerna za 85%.
    2. Prilagojen kvadrat R: Prilagojen kvadrat je le bolj potrjena različica R -kvadrata. Predvsem uporabno pri večkratni regresijski analizi.
    3. Standardna napaka: Medtem ko R. Squire pove, koliko podatkovnih točk pade blizu regresijske črte, standardna napaka pove, kako daleč lahko podatkovna točka seže od regresijske črte.

V našem primeru je tako 6.74.

  1. Opazovanje: To je preprosto število opazovanj, ki je v našem primeru 11.

Odsek Anova:

Ta odsek se pri linearni regresiji skoraj ne uporablja.

  1. df. To je stopnja svobode. Uporablja se pri ročnem izračunu regresije.
  2. SS. Vsota kvadratov. To je le vsota kvadratov variacij. Uporablja se za iskanje vrednosti R squire.
  3. GOSPA. To pomeni kvadratno vrednost.
  4. In 5. F in pomen F. Če je pomembnost F (p-vrednost naklona) manjša od testa F, lahko zavržete ničelno hipotezo in svojo hipotezo dokažete. V preprostem jeziku lahko sklepate, da ob spremembi obstaja določen učinek x na y.

V našem primeru je F 48,96264, pomembnost F pa 0,000063. To pomeni, da naša regresija ustreza podatkom.

Regresijski odsek:

V tem razdelku imamo dve najpomembnejši vrednosti za našo regresijsko enačbo.

  1. Prestrezanje: Tukaj imamo prestrezanje, ki pove, kje x-prestreže na Y. To je pomemben del regresijske enačbe. V našem primeru je -1,11.
  2. X spremenljivka 1 (Naklon). Imenuje se tudi koeficient x. Določa tangenco regresijske črte.

REGRESIJSKA LISTA V ODLIČNEM

V Excelu je enostavno narisati regresijski grafikon. Samo sledite tem korakom. Če želite dodati regresijski grafikon v Excelih 2016, 2013 in 2010, sledite tem preprostim korakom.

Korak 1. Naj bodo znani x -ji v prvem stolpcu, v drugem pa y.

V našem primeru vemo, da so znani_x -ji brezalkoholne pijače. In znani_y so prodajalci.

2. korak. Izberite znani obseg x in y.

3. korak: Pojdite na zavihek Vstavljanje in kliknite razpršeni grafikon.

Imeli boste grafikon, ki izgleda tako.

Korak 4. Dodajte trendno črto: Pojdite na postavitev in v razdelku za analizo poiščite možnost linije trenda.

Pod možnostjo Trendline kliknite Linear Trendline.

Vaš grafikon bo videti tako.

To je vaš regresijski graf.

Zdaj, če dodate spodnje podatke in razširite izbrane podatke. V grafikonu boste videli spremembo.

V našem primeru smo prodani brezalkoholni pijači dodali 2000 in prodajalce pustili prazne. In ko razširimo obseg grafa, bomo imeli to.

To bo dalo potrebno število prodajalcev za 2000 prodaj brezalkoholnih pijač v grafični obliki. Kar je na grafu nekoliko pod 120. In iz naše regresijske enačbe vemo, da je 116.

V tem članku sem poskušal zajeti vse v Excelovi regresijski analizi. Razložil sem regresijo v Excelu 2016. Regresija v Excelu 2010 in Excelu 2013 je enaka kot v Excelu 2016.

Za dodatna vprašanja o tej temi uporabite razdelek s komentarji. Postavite vprašanje, podajte mnenje ali samo omenite moje slovnične napake. Vse je dobrodošlo. Ne oklevajte in uporabite razdelek za komentarje.

Kako izračunati način MODE v Excelu

Kako izračunati povprečno funkcijo v Excelu

Kako ustvariti graf standardnega odklona

Opisna statistika v programu Microsoft Excel 2016

Kako uporabljati funkcijo Excel NORMDIST

Kako uporabljati Paretov diagram in analizo

Priljubljeni članki:

50 Bližnjica v Excelu za večjo produktivnost

Kako uporabljati funkcijo VLOOKUP v Excelu

Kako uporabljati funkcijo COUNTIF v Excelu 2016

Kako uporabljati funkcijo SUMIF v Excelu