Regresija ir analīzes rīks, ko mēs izmantojam, lai analizētu lielu datu apjomu un prognozētu un prognozētu programmā Microsoft Excel.
Vai vēlaties paredzēt nākotni? Nē, mēs nemācīsimies astroloģiju. Mēs esam skaitļos, un šodien Excel iemācīsimies regresijas analīzi.
Lai prognozētu nākotnes aplēses, mēs pētīsim:
- REGRESIJAS ANALĪZE, IZMANTOJOT EXCEL FUNKCIJAS (MANUAL REGRESSION FINDING)
- REGRESIJAS ANALĪZE, IZMANTOJOT EXCEL ANALĪZES TOOLPAK ADD-IN
- REGRESIJAS TABULA EXCEL
Darīsim to…
Scenārijs:
Pieņemsim, ka jūs pārdodat bezalkoholiskos dzērienus. Cik forši būs, ja varēsit paredzēt:
- Cik bezalkoholisko dzērienu pārdos nākamgad, pamatojoties uz iepriekšējā gada datiem?
- Kuras jomas ir jākoncentrē?
- Un kā jūs varat palielināt pārdošanas apjomu, mainot savu stratēģiju?
Tas būs izdevīgi izdevīgi. Pareizi?… Es zinu. Tātad sāksim darbu.
Jums ir 11 pārdoto pārdevēju un bezalkoholisko dzērienu ieraksti.
Tagad, pamatojoties uz šiem datiem, jūs vēlaties paredzēt pārdevēju skaitu, kas nepieciešams, lai sasniegtu 2000 bezalkoholisko dzērienu pārdošanas apjomu.
Regresijas vienādojums ir instruments šādu tuvu aprēķinu veikšanai. Lai to izdarītu, mums vispirms jāzina regresija.
REGRESIJAS ANALĪZE, IZMANTOJOT EXCEL FUNKCIJAS (MANUAL REGRESSION FINDING)
Šī daļa ļaus jums labāk izprast regresiju, nevis tikai izstāstīt Excel regresijas procedūru.
Ievads:
Vienkārša lineāra regresija:
Saistību starp diviem mainīgajiem sauc par vienkāršu lineāru regresiju. Kur viens mainīgais ir atkarīgs no otra neatkarīgā mainīgā. Atkarīgo mainīgo bieži sauc ar tādiem nosaukumiem kā Driven, Response un Target. Un neatkarīgais mainīgais bieži tiek izrunāts kā braukšanas, prognozētājs vai vienkārši neatkarīgs mainīgais. Šie nosaukumi tos skaidri raksturo.
Tagad salīdzināsim to ar jūsu scenāriju. Jūs vēlaties zināt pārdevēju skaitu, kas nepieciešams, lai sasniegtu 2000 pārdošana. Tātad šeit atkarīgais mainīgais ir pārdevēju skaits, un neatkarīgais mainīgais tiek pārdots bezalkoholiskie dzērieni.
Neatkarīgo mainīgo lielākoties apzīmē kā x un atkarīgs mainīgais kā g.
Mūsu gadījumā tiek pārdoti bezalkoholiskie dzērieni x un pārdevēju skaits ir g.
Ja mēs vēlamies zināt, cik daudz bezalkoholisko dzērienu tiks pārdoti, ja mēs to iecelsim 200 pārdevēji, tad scenārijs būs otrādi.
Virzās tālāk.
Lineārās regresijas vienādojuma “vienkāršā” matemātika:
Nu, tas nav vienkārši. Bet Excel to padarīja vienkāršu.
Mums ir jāparedz nepieciešamais pārdevēju skaits visiem 11 gadījumiem, lai iegūtu 12. tuvāko prognozi.
Teiksim:
Pārdots bezalkoholiskais dzēriens ir x
Numurs no pārdevējiem ir g
Prognozētais g (pārdevēju skaits) arī zvanīja Regresijas vienādojums, būtu
| x*Slīpums+pārtveršana (atpūties, es to esmu pārklājis) |
Tagad jums noteikti rodas jautājums, kur stat vai jūs saņemsiet slīpumu un pārtversiet. Neuztraucieties, Excel viņiem ir funkcijas. Jums nav jāiemācās atrast nogāzi un manuāli to pārtvert.
Ja vēlaties, es tam sagatavošu atsevišķu pamācību. Paziņojiet man komentāru sadaļā. Šie ir daži svarīgi datu analīzes rīki.
Tagad pievērsīsimies mūsu aprēķinam:
1. darbība: Sagatavojiet šo mazo galdiņu
2. solis: Atrodiet regresijas līnijas slīpumu
Excel funkcija nogāzēm ir
| = SLOPE (zināms_g, zināms_x) |
Jūsu zināmie_ ir diapazonā B2: B12 un zināmie_x ir diapazonā C2: C12
Šūnā B16, uzrakstiet zemāk esošo formulu
| = SLOPE (B2: B12, C2: C12) |
(Piezīme: Regresijas vienādojumā slīpumu sauc arī par x koeficientu)
Tu saņemsi 0.058409. Noapaļojiet līdz 2 cipariem aiz komata, un jūs iegūsit 0.06.
3. solis: Atrodiet regresijas līnijas krustojumu
Excel funkcija pārtveršanai ir
| =INTERCEPT (zināms_g, zināms_x) |
Mēs zinām, kas ir mūsu zināmi x un y
Šūnā B17, pierakstiet šo formulu
| = INTERCEPT (B2: B12, C2: C12) |
Jūs iegūsit vērtību -1.1118969. Noapaļojiet līdz 2 cipariem aiz komata. Tu saņemsi -1.11.
Mūsu lineārās regresijas vienādojums ir = x*0,06 + (-1,11). Tagad mēs varam viegli paredzēt iespējamo y atkarībā no mērķa x.
4. solis: D2 ierakstiet zemāk esošo formulu
| =C2*$ B $ 16+$ B $ 17(Regresijas vienādojums) |
Jūs iegūsit vērtību 13.55.
Izvēlieties no D2 līdz D13 un nospiediet CTRL+D lai aizpildītu formulu diapazonā D2: D13
Šūnā D13 jums ir nepieciešamais pārdevēju skaits.
| Tādējādi, lai sasniegtu mērķi 2000 Bezalkoholisko dzērienu pārdošana, jums ir nepieciešami aptuveni 115,71 pārdevējs vai 116, jo cilvēku sagriešana gabalos ir nelikumīga. |
Tagad, izmantojot šo, jūs varat viegli veikt What-If analīzi programmā Excel. Vienkārši mainiet pārdošanas skaitu, un tas parādīs, ka daudziem pārdevējiem būs nepieciešams, lai sasniegtu šo pārdošanas mērķi.
Spēlējiet apkārt, lai uzzinātu:
Cik daudz darbaspēka jums nepieciešams, lai palielinātu pārdošanas apjomu?
Cik pārdošanas apjomu palielināsies, ja palielināsit savus pārdevējus?
Padariet savu aprēķinu ticamāku:
Tagad jūs zināt, ka jums ir nepieciešami 116 pārdevēji, lai veiktu 2000 pārdošanas darījumus.
Analītikā nekas netiek teikts un ticēts. Aprēķinā jums jānorāda ticamības procents. Tas ir tāpat kā sniegt vienādojuma sertifikātu.
Korelācijas koeficienta formula:
Nākamā lieta, kas jums tiks jautāts, ir tas, cik lielā mērā šie divi mainīgie ir saistīti. Statiskā izteiksmē jums jāpasaka korelācijas koeficients.
Excel funkcija korelācijai ir
| = CORREL (masīvs1, masīvs2) |
Jūsu gadījumā zināmie_x un Know_y ir neatkarīgi no masīva1 un masīva2.
B18 ievadiet šo formulu
| = CORREL ((B2: B12, C2: C12) |
Jums būs 0.919090. Formatējiet šūnu B2 procentos. Tagad ir 92% korelācijai.
Tagad, ko šis 92% nozīmē. Tas nozīmē, tur 92% pārdošanas iespējas palielinās, ja palielināsiet pārdevēju skaitu un 92% pārdošanas apjoms samazinās, ja samazināsiet pārdevēju skaitu. To sauc par Pozitīvs korelācijas koeficients.
R Squire (R^2):
R Squire vērtība norāda, cik procentos jūsu regresijas vienādojums nav nejaušs. Cik tas ir precīzi pēc sniegtajiem datiem.
Excel funkcija R squire ir RSQ.
| RSQ (zināms_g, zināms_x) |
Mūsu gadījumā šūnā B19 mēs iegūsim R squire vērtību.
B19 ievadiet šo formulu
| = RSQ (B2: B12, C2: C12) |
Tātad mums ir 84% no r laukuma vērtības. Tas ir ļoti labs mūsu regresijas skaidrojums. Tajā teikts, ka 84% mūsu datu nav vienkārši nejauši. Y (pārdevēju skaits) ir ļoti atkarīgs no X (bezalkoholisko dzērienu pārdošana).
Šiem datiem var veikt daudz citu testu, lai nodrošinātu mūsu regresiju. Bet manuāli tā būs sarežģīta un ilgstoša procedūra. Tāpēc Excel nodrošina analīzes rīku komplektu. Izmantojot šo rīku, mēs varam veikt šo regresijas analīzi dažu sekunžu laikā.
REGRESIJA EXCEL, IZMANTOJOT EXCEL ANALĪZES TOOLPAK ADD-IN
Ja jūs jau zināt, kas ir regresijas vienādojumi, un vēlaties tikai ātrus rezultātus, šī daļa ir paredzēta jums. Bet, ja vēlaties viegli saprast regresijas vienādojumus, ritiniet līdz REGRESIJAS ANALĪZEI, IZMANTOJOT EXCEL FUNKCIJAS (MANUĀLĀS REGRESIJAS ATRAŠANA).
Excel savā analīzes rīkkopā piedāvā virkni analīzes rīku. Pēc noklusējuma tas nav pieejams cilnē Dati. Jums tas jāpievieno. Tāpēc vispirms pievienosim to.
Analīzes rīku komplekta pievienošana programmai Excel 2016
Ja nezināt, kur programmā Excel ir datu analīze, veiciet šīs darbības
1. darbība: dodieties uz Excel opcijām: Fails? Iespējas? Papildinājumi
2. solis: noklikšķiniet uz Papildinājumi. Jūs redzēsit pieejamo papildinājumu sarakstu.
Atlasiet Analīzes rīku pakotne un loga apakšdaļā atrodiet pārvaldīt. Pārvaldībā atlasiet Excel pievienojumprogrammas un noklikšķiniet uz GO.
Tiks atvērts pievienojumprogrammu logs. Šeit izvēlieties Analysis ToolPak. Pēc tam noklikšķiniet uz pogas Labi.
Tagad cilnē Dati varat piekļūt visām datu analīzes rīka ToolPak funkcijām.
Izmantojot analīzes rīku komplektu regresijai
1. darbība: dodieties uz cilni Dati, atrodiet datu analīzi. Pēc tam noklikšķiniet uz tā.
Parādīsies dialoglodziņš.
2. darbība: analīzes rīku sarakstā atrodiet “Regresija” un nospiediet pogu Labi.
Regresija parādīsies ievades logs. Jūs redzēsit vairākas pieejamās ievades iespējas. Bet pagaidām mēs koncentrēsimies tikai uz Y diapazonu un X diapazonu, atstājot visu pārējo pēc noklusējuma.
4. darbība: ievadiet informāciju:
Pārdevēju skaits ir Y
Bezalkoholisko dzērienu tirdzniecība ir X
Līdz ar to
- Y diapazons = B2: B11
Un
- X diapazons = C2: C11
Izvades diapazonam esmu izvēlējies E4 tajā pašā lapā. Jūs varat izvēlēties jaunu darblapu, lai iegūtu rezultātus jaunā darblapā tajā pašā darbgrāmatā vai pilnīgi jaunā darbgrāmatā. Kad esat pabeidzis ievadi, nospiediet pogu Labi.
Rezultāti:
Jums tiks sniegta dažāda informācija no jūsu datiem. Nepārslogojiet. Jums nav nepieciešams patērēt visus ēdienus.
Mēs izskatīsim tikai tos rezultātus, kas mums palīdzēs novērtēt nepieciešamo pārdevēju skaitu
5. darbība. Mēs zinām regresijas vienādojumu, lai novērtētu y, tas ir
x*Slīpums+pārtveršana
Mums tikai jāatrod Slīpums un Pārtvert rezultātos.
Un šeit viņi ir.
Pārtveršanas koeficients ir skaidri minēts.
Slīpums ir uzrakstīts kā "X 1. mainīgais', Dažkārt minēts arī kā X koeficients. Noapaļojiet tos, un mēs iegūsim -1.11 kā pārtveršana un 0,06 kā slīpums.
6. darbība. No rezultātiem mēs varam vadīt regresijas vienādojumu. Un tā arī būtu
= x*(0,06) + (-1,11)
Sagatavojiet šo tabulu programmā Excel.
Tagad, x ir 2000, kas atrodas šūnā E2.
Šūnā F2 ievadiet šo formulu
= E2*F21+F20
Jūs iegūsit rezultātu no 115.7052757.
To noapaļojot, mēs to iegūsim 116 no nepieciešamajiem pārdevējiem.
Tātad mēs esam iemācījušies regresijas vienādojumu veidot manuāli un izmantojot Analysis ToolPak. Kā jūs varat izmantot šo vienādojumu, lai novērtētu statistiku nākotnē?
Tagad sapratīsim analīzes rīkkopa sniegto regresijas izvadi.
Izpratne par regresijas izvadi:
Nav nekāda labuma, ja veicat regresijas analīzi, izmantojot Excel analīzes rīku komplektu un nevarat interpretēt tā nozīmi.
Kopsavilkuma sadaļa:
Kā norāda nosaukums, tas ir datu kopsavilkums.
-
- Vairāki R: tas norāda, cik atbilstīgs datiem ir regresijas vienādojums. To sauc arī par korelācijas koeficientu.
Mūsu gadījumā tā ir 0.919090619 vai 0.92 (noapaļot uz augšu). Tas nozīmē, ka, palielinot mūsu pārdevēju skaitu, pastāv 92% iespēja palielināt pārdošanas apjomu.
-
- R laukums: tas norāda atrastās regresijas ticamību. Tas mums parāda, cik daudz novērojumu ir daļa no mūsu regresijas līnijas. Mūsu gadījumā tas ir 0,844727566 vai 0,85. Tas nozīmē, ka mūsu regresija ir piemērota par 85%.
- Pielāgots R kvadrāts: Pielāgotais laukums ir tikai vairāk pierādīta R kvadrāta versija. Galvenokārt noder vairāku regresijas analīzē.
- Standarta kļūda: Kamēr R. Skvīrs stāsta, cik datu punktu atrodas regresijas līnijas tuvumā, standarta kļūda norāda, cik tālu datu punkts var aiziet no regresijas līnijas.
Mūsu gadījumā tā ir 6.74.
- Novērojums: tas ir vienkārši novērojumu skaits, kas mūsu piemērā ir 11.
Anova sadaļa:
Šo sadaļu gandrīz neizmanto lineārā regresijā.
- df. Tā ir brīvības pakāpe. To izmanto, aprēķinot regresiju manuāli.
- SS. Kvadrātu summa. Tā ir tikai dispersiju kvadrātu summa. Izmanto, lai atrastu R squire vērtības.
- JAUNKUNDZE. Tas nozīmē vērtību kvadrātā.
- Un 5. F un F nozīme. Ja F nozīme (slīpuma p-vērtība) ir mazāka par F testu, jūs varat atmest nulles hipotēzi un pierādīt savu hipotēzi. Vienkāršā valodā jūs varat secināt, ka, mainot, x ietekmē y.
Mūsu gadījumā F ir 48,96264 un F nozīme ir 0,000063. Tas nozīmē, ka mūsu regresija atbilst datiem.
Regresijas sadaļa:
Šajā sadaļā mums ir divas vissvarīgākās mūsu regresijas vienādojuma vērtības.
- Pārtveršana: mums ir pārtverta vieta, kas norāda, kur x pārtver Y. Šī ir svarīga regresijas vienādojuma daļa. Mūsu gadījumā tas ir -1,11.
- X mainīgais 1 (Slīpums). To sauc arī par koeficientu x. Tas nosaka regresijas līnijas tangenci.
REGRESIJAS TABULA EXCEL
Programmā Excel ir viegli izveidot regresijas diagrammu. Vienkārši izpildiet šīs darbības. Lai pievienotu regresijas diagrammu programmā Excel 2016, 2013 un 2010, veiciet šīs vienkāršās darbības.
1. darbība. Pirmajā slejā ierakstiet savus zināmos x, bet otrajā - y.
Mūsu gadījumā mēs zinām, ka Known_ x ir pārdotie bezalkoholiskie dzērieni. Un zināmie ir pārdevēji.
2. solis. Atlasiet zināmos x un y diapazonus.
3. darbība: Dodieties uz cilni Ievietot un noklikšķiniet uz izkliedes diagrammas.
Jums būs diagramma, kas izskatās šādi.
4. solis. Pievienojiet tendences līniju: Dodieties uz izkārtojumu un analīzes sadaļā atrodiet tendenču līnijas opciju.
Zem opcijas Trendline noklikšķiniet uz Linear Trendline.
Jūsu grafiks izskatīsies šādi.
Šī ir jūsu regresijas diagramma.
Tagad, ja pievienojat tālāk norādītos datus un paplašināt atlasītos datus. Jūs redzēsit izmaiņas savā grafikā.
Piemēram, pārdotajam bezalkoholiskajam dzērienam mēs pievienojām 2000 un atstājām pārdevējus tukšus. Un, paplašinot diagrammas diapazonu, tas mums būs.
Tas nodrošinās nepieciešamo pārdevēju skaitu, lai veiktu 2000 bezalkoholisko dzērienu pārdošanu grafiskā veidā. Kas grafikā ir nedaudz zem 120. Un no mūsu regresijas vienādojuma mēs zinām, ka tas ir 116.
Šajā rakstā es mēģināju visu ietvert Excel regresijas analīzē. Es paskaidroju regresiju programmā Excel 2016. Regresija Excel 2010 un Excel 2013 ir tāda pati kā Excel 2016.
Lai iegūtu papildu jautājumus par šo tēmu, izmantojiet komentāru sadaļu. Uzdodiet jautājumu, sniedziet viedokli vai vienkārši pieminiet manas gramatiskās kļūdas. Viss ir apsveicams. Vienkārši nevilcinieties izmantot komentāru sadaļu.
Kā aprēķināt MODE funkciju programmā Excel
Kā aprēķināt vidējo funkciju programmā Excel
Kā izveidot standarta novirzes grafiku
Aprakstošā statistika programmā Microsoft Excel 2016
Kā lietot Excel NORMDIST funkciju
Kā izmantot Pareto diagrammu un analīzi
Populāri raksti:
50 Excel saīsne produktivitātes paaugstināšanai
Kā lietot funkciju VLOOKUP programmā Excel
Funkcijas COUNTIF izmantošana programmā Excel 2016
Kā lietot funkciju SUMIF programmā Excel