Regresija yra analizės įrankis, kurį naudojame analizuodami didelius duomenų kiekius ir prognozuodami bei prognozuodami „Microsoft Excel“.
Norite nuspėti ateitį? Ne, mes nesiruošiame mokytis astrologijos. Mes skaičiuojame ir šiandien išmoksime „Excel“ regresinės analizės.
Norėdami prognozuoti būsimus įvertinimus, mes ištirsime:
- REGRESIJOS ANALIZĖ NAUDOTI EXCEL FUNKCIJAS (MANUAL REGRESSION FINDING)
- REGRESIJOS ANALIZĖ NAUDOJANT „EXCEL“ ANALIZĖS ĮRANGINĮ PRIEDĄ
- REGRESIJOS LENTELĖ EXCEL
Padarykime tai…
Scenarijus:
Tarkime, kad parduodate gaiviuosius gėrimus. Kaip šaunu bus, jei galėsite nuspėti:
- Kiek gaiviųjų gėrimų bus parduodama kitais metais, remiantis praėjusių metų duomenimis?
- Kurias sritis reikia sutelkti?
- Ir kaip jūs galite padidinti savo pardavimus pakeisdami savo strategiją?
Tai bus pelningai nuostabu. Teisingai?… Žinau. Taigi pradėkime.
Turite 11 pardavėjų ir gaiviųjų gėrimų įrašų.
Dabar, remdamiesi šiais duomenimis, norite numatyti, kiek pardavėjų reikia norint parduoti 2000 gaiviųjų gėrimų.
Regresijos lygtis yra priemonė, padedanti atlikti tokius artimus įvertinimus. Norėdami tai padaryti, pirmiausia turime žinoti regresiją.
REGRESIJOS ANALIZĖ NAUDOTI EXCEL FUNKCIJAS (MANUAL REGRESSION FINDING)
Ši dalis padės geriau suprasti regresiją, o ne tik pasakyti „Excel“ regresijos procedūrą.
Įvadas:
Paprasta tiesinė regresija:
Santykių tarp dviejų kintamųjų tyrimas vadinamas paprasta linijine regresija. Kur vienas kintamasis priklauso nuo kito nepriklausomo kintamojo. Priklausomas kintamasis dažnai vadinamas tokiais pavadinimais kaip „Driven“, „Response“ ir „Target“. Nepriklausomas kintamasis dažnai tariamas kaip vairavimo, prognozavimo ar tiesiog nepriklausomas kintamasis. Šie pavadinimai juos aiškiai apibūdina.
Dabar palyginkime tai su jūsų scenarijumi. Norite žinoti, kiek pardavėjų reikia pasiekti 2000 pardavimai. Taigi čia priklausomas kintamasis yra pardavėjų skaičius, o nepriklausomas - gaivieji gėrimai.
Nepriklausomas kintamasis dažniausiai žymimas kaip x ir priklausomas kintamasis kaip y.
Mūsų atveju parduodami gaivieji gėrimai x o pardavėjų skaičius yra y.
Jei norime žinoti, kiek gaiviųjų gėrimų bus parduodama, jei paskirsime 200 pardavėjų, tada scenarijus bus atvirkščiai.
Toliau.
„Paprasta“ linijinės regresijos lygties matematika:
Na, tai nėra paprasta. Tačiau „Excel“ tai padaryti buvo paprasta.
Turime numatyti reikiamą pardavėjų skaičių visais 11 atvejų, kad gautume 12 -ą artimiausią prognozę.
Tarkim:
Parduodamas gaivusis gėrimas x
Skaičius pardavėjų yra y
Prognozuojama y (pardavėjų skaičius) taip pat skambino Regresijos lygtis, būtų
| x*Nuolydis+perėmimas (Atsipalaiduok, aš tai padengiau) |
Dabar turbūt įdomu, kur stat ar gausite nuolydį ir perimsite. Nesijaudinkite, „Excel“ turi jiems skirtų funkcijų. Jums nereikia išmokti rasti šlaito ir jį perimti rankiniu būdu.
Jei norite, paruošiu tam atskirą pamoką. Praneškite man komentarų skiltyje. Tai yra keletas svarbių duomenų analizės įrankių.
Dabar pereikime prie mūsų skaičiavimo:
1 žingsnis: Paruoškite šią mažą lentelę
2 žingsnis: Raskite regresijos linijos nuolydį
„Excel“ funkcija šlaitams yra
| = SLOPE (žinomi_y, žinomi_x) |
Jūsų žinomi_y yra diapazone B2: B12 ir žinomi_x yra diapazone C2: C12
Ląstelėje B16, parašykite žemiau esančią formulę
| = SLOPE (B2: B12, C2: C12) |
(Pastaba: nuolydis taip pat vadinamas x koeficientu regresijos lygtyje)
Tu gausi 0.058409. Apvalinkite iki 2 skaitmenų po kablelio ir gausite 0.06.
3 žingsnis: Raskite regresijos linijos perėmimą
Sulaikymo „Excel“ funkcija yra
| =INTERCEPT (žinomi_y, žinomi_x) |
Mes žinome, kas mūsų žinomi x ir y
Ląstelėje B17, užsirašykite šią formulę
| = INTERCEPT (B2: B12, C2: C12) |
Jūs gausite -1.1118969 vertę. Suapvalinkite iki 2 skaitmenų po kablelio. Tu gausi -1.11.
Mūsų tiesinės regresijos lygtis yra = x*0,06 + (-1,11). Dabar galime lengvai numatyti galimą y, priklausomai nuo tikslo x.
4 žingsnis: D2 parašykite žemiau esančią formulę
| =C2*16 USD+17 USD(Regresijos lygtis) |
Jūs gausite vertę 13.55.
Pasirinkite nuo D2 iki D13 ir paspauskite CTRL+D kad užpildytumėte diapazono formulę D2: D13
Ląstelėje D13 turite reikiamą pardavėjų skaičių.
| Vadinasi, pasiekti tikslą 2000 Gaiviųjų gėrimų išpardavimai, jums reikia maždaug 115,71 pardavėjo arba 116 pardavėjų, nes žmonių pjaustymas į gabalus yra neteisėtas. |
Dabar naudodami tai galite lengvai atlikti „Kas-jei“ analizę „Excel“. Tiesiog pakeiskite pardavimų skaičių ir parodys, kad daug pardavėjų prireiks, kad šis pardavimo tikslas būtų pasiektas.
Žaisk aplink, kad sužinotum:
Kiek darbo jėgos reikia norint padidinti pardavimus?
Kiek pardavimų padidės, jei padidinsite savo pardavėjus?
Padarykite savo įvertinimą patikimesnį:
Dabar jūs žinote, kad jums reikia 116 pardavėjų, kad galėtumėte parduoti 2000.
Analitikoje niekas nėra tik sakoma ir tikima. Savo sąmatoje turite nurodyti patikimumo procentą. Tai tarsi duoti savo lygties sertifikatą.
Koreliacijos koeficiento formulė:
Kitas dalykas, kurio jūsų paklaus, yra tai, kiek šie du kintamieji yra susiję. Statiškai kalbant, turite pasakyti koreliacijos koeficientą.
„Excel“ koreliacijos funkcija yra
| = CORREL (masyvas1, masyvas2) |
Jūsų atveju žinomi_x ir žinomi_y yra nepriklausomai nuo masyvo1 ir masyvo2.
Į B18 įveskite šią formulę
| = CORREL ((B2: B12, C2: C12) |
Tu turėsi 0.919090. Formatuokite langelį B2 į procentą. Dabar turi 92% koreliacijos.
Dabar, kas tai 92% reiškia. Reiškia, ten 92% pardavimo tikimybė padidės, jei padidinsite pardavėjų skaičių ir 92% pardavimų sumažės, jei sumažinsite pardavėjų skaičių. Tai vadinama Teigiamas koreliacijos koeficientas.
R Squire (R^2):
R Squire reikšmė nurodo, kiek procentų jūsų regresijos lygtis nėra klaidinga. Kiek jis tikslus pagal pateiktus duomenis.
„Excel“ funkcija „R squire“ yra RSQ.
| RSQ (žinomi_y, žinomi_x) |
Mūsų atveju ląstelėje B19 gausime R kvadrato reikšmę.
Į B19 įveskite šią formulę
| = RSQ (B2: B12, C2: C12) |
Taigi mes turime 84% r kvadratinės vertės. Tai yra labai geras mūsų regresijos paaiškinimas. Jame teigiama, kad 84% mūsų duomenų nėra atsitiktiniai. Y (pardavėjų skaičius) labai priklauso nuo X (gaiviųjų gėrimų pardavimo).
Yra daug kitų testų, kuriuos galime atlikti su šiais duomenimis, kad užtikrintume mūsų regresiją. Tačiau rankiniu būdu tai bus sudėtinga ir ilga procedūra. Štai kodėl „Excel“ teikia analizės įrankių paketą. Naudodami šį įrankį mes galime atlikti šią regresinę analizę per kelias sekundes.
REGRESIJA EXCEL NAUDOJANT EXCEL ANALIZĖS ĮRANGINĮ ĮRANKĮ
Jei jau žinote, kas yra regresijos lygtys, ir norite tik greitų rezultatų, ši dalis skirta jums. Bet jei norite lengvai suprasti regresijos lygtis, slinkite aukštyn iki REGRESIJOS ANALIZĖ, NAUDOTI EXCEL FUNKCIJAS (MANUAL REGRESSION FINDING).
„Excel“ savo analizės įrankių rinkinyje pateikia daugybę analizės įrankių. Pagal numatytuosius nustatymus jis nepasiekiamas skirtuke Duomenys. Jums reikia jį pridėti. Taigi pirmiausia pridėkime.
Analizės įrankių paketo pridėjimas prie „Excel 2016“
Jei nežinote, kur yra „Excel“ duomenų analizė, atlikite šiuos veiksmus
1 veiksmas: eikite į „Excel“ parinktis: Failas? Galimybės? Papildiniai
2 veiksmas: spustelėkite priedus. Pamatysite galimų priedų sąrašą.
Pasirinkite „Analysis ToolPak“ ir lango apačioje raskite „Manage“. Valdydami pasirinkite „Excel“ priedus ir spustelėkite Eiti.
Atsidarys priedų langas. Čia pasirinkite „Analysis ToolPak“. Tada spustelėkite mygtuką Gerai.
Dabar skirtuke „Duomenys“ galite pasiekti visas duomenų analizės „ToolPak“ funkcijas.
„Analysis ToolPak“ naudojimas regresijai
1 veiksmas: eikite į skirtuką Duomenys, raskite duomenų analizę. Tada spustelėkite jį.
Pasirodys dialogo langas.
2 veiksmas: analizės įrankių sąraše raskite „Regresija“ ir paspauskite mygtuką Gerai.
Regresija pasirodys įvesties langas. Pamatysite daugybę galimų įvesties parinkčių. Tačiau kol kas mes sutelksime dėmesį tik į Y diapazoną ir X diapazoną, o visa kita paliksime pagal numatytuosius nustatymus.
4 žingsnis: pateikite įvestis:
Pardavėjų skaičius yra Y
Gaiviųjų gėrimų pardavimas yra X
Vadinasi
- Y diapazonas = B2: B11
Ir
- X diapazonas = C2: C11
Išvesties diapazonui tame pačiame lape pasirinkau E4. Galite pasirinkti naują darbalapį, kad gautumėte rezultatus toje pačioje darbaknygėje arba visiškai naujoje darbaknygėje. Baigę įvestį, paspauskite mygtuką Gerai.
Rezultatai:
Jums bus pateikta įvairi informacija iš jūsų duomenų. Neapsigaukite. Jums nereikia valgyti visų patiekalų.
Mes nagrinėsime tik tuos rezultatus, kurie padės mums įvertinti reikiamą pardavėjų skaičių
5 žingsnis: Mes žinome regresijos lygtį y, tai yra
x*nuolydis+perėmimas
Mums tereikia rasti vietą Nuolydis ir Perimti rezultatuose.
Ir štai jie.
Sulaikymo koeficientas yra aiškiai paminėtas.
Šlaitas parašytas taip:X 1 kintamasis“, Kai kuriais atvejais taip pat minimas kaip X koeficientas. Apvalinkite juos ir gausime -1.11 kaip perimti ir 0,06 kaip nuolydis.
6 žingsnis: Remiantis rezultatais, galime valdyti regresijos lygtį. Ir tai būtų
= x*(0,06) + (-1,11)
Paruoškite šią lentelę „Excel“.
Dabar, x yra 2000, kuris yra E2 langelyje.
Lange F2 įveskite šią formulę
= E2*F21+F20
Jūs gausite rezultatą 115.7052757.
Apvalinimas mums duos 116 reikalingų pardavėjų.
Taigi mes išmokome suformuoti regresijos lygtį rankiniu būdu ir naudodami „Analysis ToolPak“. Kaip galite naudoti šią lygtį būsimai statistikai įvertinti?
Dabar suprasime „Analysis Toolpak“ pateiktą regresijos išvestį.
Regresijos išvesties supratimas:
Nėra jokios naudos, jei atliksite regresinę analizę naudodami „Excel“ analizės įrankių paketą ir negalėsite suprasti jo reikšmės.
Santraukų skyrius:
Kaip rodo pavadinimas, tai yra duomenų santrauka.
-
- Keli R: Jis parodo, kaip regresijos lygtis tinka duomenims. Jis taip pat vadinamas koreliacijos koeficientu.
Mūsų atveju tai yra 0.919090619 arba 0.92 (apvalinimas). Tai reiškia, kad padidinus pardavėjų skaičių yra 92% tikimybė padidinti pardavimus.
-
- R kvadratas: Tai rodo rastos regresijos patikimumą. Tai rodo, kiek stebėjimų yra mūsų regresijos linijos dalis. Mūsų atveju tai yra 0,844727566 arba 0,85. Tai reiškia, kad mūsų regresija atitinka 85%.
- Sureguliuotas R kvadratas: Sureguliuota aikštė yra tik labiau patvirtinta R kvadrato versija. Daugiausia naudinga atliekant daugybinės regresijos analizę.
- Standartinė klaida: Nors R. Squire'as nurodo, kiek duomenų taškų patenka šalia regresijos linijos, standartinė klaida nurodo, kiek duomenų taškas gali nukrypti nuo regresijos linijos.
Mūsų atveju tai yra 6.74.
- Stebėjimas: tai tiesiog stebėjimų skaičius, kuris mūsų pavyzdyje yra 11.
„Anova“ skyrius:
Šis skyrius beveik nenaudojamas tiesinei regresijai.
- df. Tai yra laisvės laipsnis. Jis naudojamas skaičiuojant regresiją rankiniu būdu.
- SS. Kvadratų suma. Tai tik dispersijų kvadratų suma. Naudojamas R kvadrato reikšmėms rasti.
- MS. Tai reiškia kvadratinę vertę.
- Ir 5. F ir reikšmingumas F. Jei F reikšmė (nuolydžio p reikšmė) yra mažesnė už F testą, galite atmesti nulinę hipotezę ir įrodyti savo hipotezę. Paprasta kalba galite daryti išvadą, kad pasikeitus x turi tam tikrą poveikį y.
Mūsų atveju F yra 48,96264, o F reikšmė yra 0,000063. Tai reiškia, kad mūsų regresija atitinka duomenis.
Regresijos skyrius:
Šiame skyriuje pateikiamos dvi svarbiausios mūsų regresijos lygties vertės.
- Sulaikymas: čia turime perėmimą, kuris nurodo, kur x perima Y. Tai yra svarbi regresijos lygties dalis. Mūsų atveju yra -1,11.
- X kintamasis 1 (Nuolydis). Taip pat vadinamas x koeficientu. Jis apibrėžia regresijos linijos liestinę.
REGRESIJOS LENTELĖ EXCEL
„Excel“ programoje lengva sudaryti regresijos diagramą. Tiesiog atlikite šiuos veiksmus. Norėdami pridėti regresijos diagramą programoje „Excel 2016“, 2013 ir 2010, atlikite šiuos paprastus veiksmus.
1 žingsnis. Pirmajame stulpelyje turite žinomus x, o antrame - y.
Mūsų atveju žinome, kad žinomi „x“ yra parduodami gaivieji gėrimai. Ir žinomi_y yra pardavėjai.
2 žingsnis. Pasirinkite žinomą x ir y diapazoną.
3 žingsnis: Eikite į skirtuką Įterpti ir spustelėkite sklaidos diagramą.
Turėsite diagramą, kuri atrodo taip.
4 veiksmas. Pridėkite tendencijų eilutę: Eikite į išdėstymą ir suraskite tendencijos linijos parinktį analizės skiltyje.
Pagal parinktį „Trendline“ spustelėkite „Linear Trendline“.
Jūsų grafikas atrodys taip.
Tai jūsų regresijos grafikas.
Dabar, jei pridėsite žemiau esančius duomenis ir išplėsite pasirinktus duomenis. Pamatysite diagramos pakeitimą.
Pavyzdžiui, prie parduotų gaiviųjų gėrimų pridėjome 2000, o pardavėjus palikome tuščius. Ir kai mes išplėsime grafiko diapazoną, tai turėsime.
Tai suteiks reikiamą pardavėjų skaičių, kad galėtų parduoti 2000 grafinių gaiviųjų gėrimų. Kuris grafike yra šiek tiek žemiau 120. Ir iš mūsų regresijos lygties žinome, kad tai yra 116.
Šiame straipsnyje aš bandžiau viską apimti pagal „Excel“ regresijos analizę. Aš paaiškinau regresiją „Excel 2016“. „Excel 2010“ ir „Excel 2013“ regresija yra tokia pati kaip „Excel 2016“.
Jei norite gauti papildomų klausimų šia tema, naudokite komentarų skiltį. Užduokite klausimą, pareikškite nuomonę arba tiesiog paminėkite mano gramatines klaidas. Viskas sveikintina. Tiesiog nedvejodami naudokite komentarų skiltį.
Kaip apskaičiuoti „MODE“ funkciją „Excel“
Kaip apskaičiuoti „Excel“ vidutinę funkciją
Kaip sukurti standartinio nuokrypio grafiką
Aprašomoji statistika „Microsoft Excel 2016“
Kaip naudotis „Excel“ funkcija NORMDIST
Kaip naudotis Pareto diagrama ir analize
Populiarūs straipsniai:
50 „Excel“ spartusis klavišas produktyvumui padidinti
Kaip naudotis „VLOOKUP“ funkcija „Excel“
Kaip naudoti funkciją „COUNTIF“ programoje „Excel 2016“
Kaip naudotis „SUMIF“ funkcija „Excel“