Regresia este un instrument de analiză, pe care îl folosim pentru a analiza cantități mari de date și pentru a face prognoze și predicții în Microsoft Excel.
Vrei să prezici viitorul? Nu, nu vom învăța astrologia. Suntem în cifre și vom învăța astăzi analiza de regresie în Excel.
Pentru a prezice estimări viitoare, vom studia:
- ANALIZA REGRESIEI FOLOSIND EXCEL (GĂSIRE MANUALĂ A REGRESIEI)
- ANALIZA REGRESIEI FOLOSIND ADD-IN TOOLPAK DE ANALIZĂ EXCEL
- TABLA DE REGRESIE ÎN EXCEL
Hai să o facem…
Scenariu:
Să presupunem că vindeți băuturi răcoritoare. Cât de mișto va fi dacă puteți prezice:
- Câte băuturi răcoritoare vor fi vândute anul viitor pe baza datelor din anul precedent?
- Ce domenii trebuie concentrate?
- Și cum vă puteți crește vânzările schimbându-vă strategia?
Va fi extraordinar de profitabil. Nu? … Știu. Deci sa începem.
Aveți 11 înregistrări ale vânzătorilor și băuturilor răcoritoare vândute.
Acum, pe baza acestor date, doriți să preziceți numărul vânzătorilor necesari pentru realizarea a 2000 de vânzări de băuturi răcoritoare.
Ecuația de regresie este un instrument pentru a face estimări atât de apropiate. Pentru a face acest lucru, trebuie mai întâi să cunoaștem regresia.
ANALIZA REGRESIEI FOLOSIND EXCEL (GĂSIRE MANUALĂ A REGRESIEI)
Această parte vă va face să înțelegeți regresia mai bine decât să spuneți procedura de regresie excel.
Introducere:
Regresie liniară simplă:
Studiul relației dintre două variabile se numește Regresie liniară simplă. În cazul în care o variabilă depinde de cealaltă variabilă independentă. Variabila dependentă este deseori numită de nume precum variabila Driven, Response și Target. Și variabila independentă este adesea pronunțată ca o variabilă de conducere, predictor sau pur și simplu independentă. Aceste nume le descriu clar.
Acum să comparăm acest lucru cu scenariul dvs. Vrei să știi numărul vânzătorilor necesari pentru a realiza 2000 de vânzări. Deci, aici, variabila dependentă este numărul vânzătorilor, iar variabila independentă este vândută băuturi răcoritoare.
Variabila independentă este în mare parte notată ca X și variabilă dependentă ca y.
În cazul nostru, se vând băuturi răcoritoare X iar numărul vânzătorilor este y.
Dacă vrem să știm câte băuturi răcoritoare vor fi vândute dacă ne numim 200 de vânzători, atunci scenariul va fi invers.
Trecând peste.
Ecuația „simplă” a ecuației de regresie liniară:
Ei bine, nu este simplu. Dar Excel a făcut-o simplu de făcut.
Trebuie să prezicem numărul necesar de vânzători pentru toate cele 11 cazuri pentru a obține a 12-a cea mai apropiată predicție.
Sa spunem:
Băutura răcoritoare vândută este X
Numarul de Vânzători este y
Prevăzutul y (numărul vânzătorilor), de asemenea, sunați Ecuația de regresie, va fi
| x * Panta + Interceptare (relaxează-te, am acoperit) |
Acum trebuie să vă întrebați unde stat vei primi panta și intercepta. Nu vă faceți griji, Excel are funcții pentru ei. Nu trebuie să învățați cum să găsiți panta și să o interceptați manual.
Dacă doriți, voi pregăti un tutorial separat pentru asta. Anunță-mă în secțiunea de comentarii. Acestea sunt câteva instrumente importante de analiză a datelor.
Acum să trecem la calculul nostru:
Pasul 1: Pregătește această măsuță
Pasul 2: Găsiți panta liniei de regresie
Funcția Excel pentru pante este
| = SLOPE (cunoscut_y, cunoscut_x) |
Cunoscutele dvs. sunt în raza de acțiune B2: B12 și cunoscut_x sunt în raza de acțiune C2: C12
În celulă B16, scrie formula de mai jos
| = SLOPE (B2: B12, C2: C12) |
(Notă: Panta se mai numește coeficient de x în ecuația de regresie)
Vei primi 0.058409. Rotunjiți până la 2 cifre zecimale și veți obține 0.06.
Pasul 3: Găsiți linia de interceptare a regresiei
Funcția Excel pentru interceptare este
| =INTERCEPT (cunoscut_y, cunoscut_x) |
Știm ce este X și Y cunoscute
În celulă B17, notează această formulă
| = INTERCEPT (B2: B12, C2: C12) |
Veți obține o valoare de -1.1118969. Rotunjește la 2 cifre zecimale. Vei primi -1.11.
Ecuația noastră de regresie liniară este = x * 0,06 + (-1,11). Acum putem prezice cu ușurință posibilul y în funcție de ținta x.
Pasul 4: În D2 scrieți formula de mai jos
| =C2* $ B $ 16 $ + $ B $ 17(Ecuația de regresie) |
Veți obține o valoare de 13.55.
Selectați D2 până la D13 și apăsați CTRL + D pentru a completa formula din interval D2: D13
În celulă D13 aveți numărul necesar de vânzători.
| Prin urmare, pentru a atinge obiectivul de 2000 Vânzări de băuturi răcoritoare, aveți nevoie de o estimare de 115,71 vânzători sau de 116, deoarece este ilegal să tăiați oamenii în bucăți. |
Acum, utilizând acest lucru, puteți efectua cu ușurință analiza What-If în Excel. Trebuie doar să schimbați numărul de vânzări și vă va arăta mulți vânzători va fi nevoie pentru a atinge obiectivul respectiv de vânzări.
Joacă în jurul lui pentru a afla:
De câtă forță de muncă aveți nevoie pentru a crește vânzările?
Câte vânzări vor crește dacă vă creșteți vânzătorii?
Faceți estimarea dvs. mai fiabilă:
Acum știți că aveți nevoie de 116 vânzători pentru a realiza 2000 de vânzări.
În analiză, nimic nu este doar spus și crezut. Trebuie să dați un procent de fiabilitate în estimarea dvs. Este ca și cum ai da un certificat al ecuației tale.
Formula coeficientului de corelație:
Următorul lucru care vă va fi întrebat este legătura dintre aceste două variabile. În termeni statici, trebuie să spuneți coeficientul de corelație.
Funcția Excel pentru corelație este
| = CORREL (matrice1, matrice2) |
În cazul dvs., known_x’s și Know_y’s sunt array1 și array2 indiferent.
În B18 introduceți această formulă
| = CORREL ((B2: B12, C2: C12) |
Vei avea 0.919090. Formați celula B2 în procent. Acum ai 92% de corelație.
Acum, ce asta 92% mijloace. Înseamnă, acolo 92% de șanse de vânzări crește dacă creșteți numărul vânzătorilor și 92% a vânzărilor scade dacă scădeți numărul vânzătorilor. Se numeste Coeficient de corelație pozitivă.
R Squire (R 2):
R Valoarea Squire vă spune, cu ce procent ecuația de regresie nu este o întâmplare. Cât de exactă este data de datele furnizate.
Funcția Excel pentru R squire este RSQ.
| RSQ (cunoscut_y, cunoscut_x) |
În cazul nostru, vom obține valoarea R squire în celula B19.
În B19 introduceți această formulă
| = RSQ (B2: B12, C2: C12) |
Deci avem 84% din valoarea pătrată. Ceea ce este o explicație foarte bună a regresiei noastre. Se spune că 84% din datele noastre nu sunt întâmplătoare. Y (numărul vânzătorilor) depinde foarte mult de X (vânzările de băuturi răcoritoare).
Există multe alte teste pe care le putem face pe aceste date pentru a ne asigura regresia. Dar manual va fi o procedură complexă și lungă. De aceea Excel oferă Analysis Toolpak. Folosind acest instrument putem face această analiză de regresie în câteva secunde.
REGRESIUNE ÎN EXCEL FOLOSIND ADD-IN TOOLPAK DE ANALIZĂ EXCEL
Dacă știți deja ce sunt ecuațiile de regresie și doriți doar rezultatele dvs. rapid, această parte este pentru dvs. Dar dacă doriți să înțelegeți cu ușurință ecuațiile de regresie, derulați până la ANALIZA REGRESIEI FOLOSIȚI EXCEL (GĂSIRE MANUALĂ DE REGRESIE).
Excel oferă o grămadă de instrumente pentru analiză în Analiza sa Toolpak. În mod implicit, nu este disponibil în fila Date. Trebuie să-l adăugați. Deci, hai să o adăugăm mai întâi.
Adăugarea Analysis Toolpak la Excel 2016
Dacă nu știți unde este analiza datelor în Excel, urmați acești pași
Pasul 1: Mergeți la Opțiuni Excel: Fişier? Opțiuni? Adăugați ins
Pasul 2: Faceți clic pe Add-Ins. Veți vedea o listă de programe de completare disponibile.
Selectați Analysis ToolPak și în partea de jos a ferestrei, găsiți gestionarea. În gestionare, selectați programe de completare Excel și faceți clic pe GO.
Se va deschide fereastra de programe de completare. Aici, selectați Analysis ToolPak. Apoi faceți clic pe butonul OK.
Acum puteți accesa toate funcțiile de analiză a datelor ToolPak din fila Date.
Utilizarea Analizei ToolPak pentru regresie
Pasul 1: Accesați fila Date, Localizați analiza datelor. Apoi faceți clic pe el.
Va apărea o casetă de dialog.
Pasul 2: Găsiți „Regresie” în lista Instrumente de analiză și apăsați butonul OK.
Regresia fereastra de intrare va apărea. Veți vedea o serie de opțiuni de introducere disponibile. Însă, deocamdată, ne vom concentra doar pe gama Y și gama X, lăsând toate celelalte în mod implicit.
Pasul 4: Furnizați intrări:
Numărul vânzătorilor este Da
Vânzările de băuturi răcoritoare sunt X
Prin urmare
- Gama Y = B2: B11
Și
- Gama X = C2: C11
Pentru intervalul de ieșire, am selectat E4 pe aceeași foaie. Puteți selecta o foaie de lucru nouă pentru a obține rezultate pe o foaie de lucru nouă în același registru de lucru sau un registru de lucru complet nou. Când ați terminat cu intrările dvs., apăsați butonul OK.
Rezultate:
Veți primi o varietate de informații din datele dvs. Nu vă lăsați copleșiți. Nu trebuie să consumi toate felurile de mâncare.
Ne vom ocupa doar de acele rezultate care ne vor ajuta să estimăm numărul necesar de vânzători
Pasul 5: Cunoaștem ecuația de regresie pentru estimarea lui da, acesta este
x * Panta + Interceptare
Trebuie doar să localizăm Pantă și Intercepta în rezultate.
Și iată-le.
Coeficientul de interceptare este clar menționat.
Panta este scrisă ca „Variabila X 1', De câteva ori menționat și ca coeficientul lui X. Rotunjește-le și vom obține -1.11 ca interceptare și 0,06 ca Panta.
Pasul 6: Din rezultate, putem conduce ecuația de regresie. Și asta ar fi
= x * (0,06) + (-1,11)
Pregătește acest tabel în excel.
Deocamdata, X este 2000, care se află în celula E2.
În celula F2 introduceți această formulă
= E2 * F21 + F20
Veți obține un rezultat al 115.7052757.
Rotunjirea acestuia ne va da 116 din Vânzătorii necesari.
Deci, am învățat cum să formăm ecuația de regresie manual și folosind Analysis ToolPak. Cum puteți utiliza această ecuație pentru a estima statistici viitoare?
Acum să înțelegem rezultatul de regresie dat de Analysis Toolpak.
Înțelegerea rezultatului de regresie:
Nu există niciun beneficiu, dacă faceți analize de regresie utilizând pachetul de instrumente de analiză în Excel și nu puteți interpreta semnificația acestuia.
Secțiunea de rezumat:
După cum sugerează și numele, este un rezumat al datelor.
-
- Multiple R: Indică cât de potrivită este ecuația de regresie la date. Se mai numește coeficient de corelație.
În cazul nostru, este 0.919090619 sau 0.92 (a rotunji). Aceasta înseamnă că există șanse de 92% de creștere a vânzărilor dacă creștem numărul vânzătorilor noștri.
-
- Piața R: spune fiabilitatea regresiei constatate. Ne spune câte observații fac parte din linia noastră de regresie. În cazul nostru, este 0,844727566 sau 0,85. Înseamnă că regresia noastră se potrivește cu 85%.
- Pătrat R ajustat: Pătratul ajustat este doar o versiune mai mărturisită a pătratului R. Util în principal în analiza regresiei multiple.
- Eroare standard: În timp ce R. Squire vă spune câte puncte de date cad în apropierea liniei de regresie, eroarea standard vă spune cât de departe poate ajunge un punct de date de la linia de regresie.
În cazul nostru, este 6.74.
- Observație: acesta este pur și simplu numărul de observații, care este 11 în exemplul nostru.
Secțiunea Anova:
Această secțiune este greu utilizată în regresia liniară.
- df. Este un grad de libertate. Se utilizează la calcularea regresiei manual.
- SS. Suma patratelor. Este doar o sumă de pătrate de varianțe. Folosit pentru a găsi valorile lui R squire.
- DOMNIȘOARĂ. Aceasta înseamnă valoare pătrată.
- Și 5. F și semnificația lui F. Dacă semnificația lui F (valoarea p a pantei) este mai mică decât testul F, atunci puteți renunța la ipoteza nulă și vă puteți dovedi ipoteza. Într-un limbaj simplu, puteți concluziona că există un anumit efect al lui x asupra y când este schimbat.
În cazul nostru, F este 48,96264 și semnificația lui F este 0,000063. Înseamnă că regresia noastră se potrivește cu datele.
Secțiunea de regresie:
În această secțiune, avem cele mai importante două valori pentru ecuația noastră de regresie.
- Interceptare: Aici avem o interceptare care arată unde interceptează x pe Y. Aceasta este o parte importantă a ecuației de regresie. Este -1,11 în cazul nostru.
- X variabilă 1 (Pantă). Numit și coeficientul lui x. Definește tangenta liniei de regresie.
TABLA DE REGRESIE ÎN EXCEL
În Excel, este ușor de trasat o diagramă de regresie. Doar urmați acești pași. Pentru a adăuga o diagramă de regresie în Excel 2016, 2013 și 2010 urmați acești pași simpli.
Pasul 1. Faceți-vă cunoscutele x-uri în prima coloană și știți-le în a doua.
În cazul nostru, știm că Known_ x’s sunt băuturi răcoritoare vândute. Și cunoscutii sunt vânzători.
Pasul 2. Selectați gama X și Y cunoscute.
Pasul 3: Mergeți la fila Insert și faceți clic pe diagrama scatter.
Veți avea o diagramă care arată astfel.
Pasul 4. Adăugați linia de tendință: Mergeți la aspect și localizați opțiunea liniei de trend în secțiunea de analiză.
Sub opțiunea Trendline, faceți clic pe Linie de tendință liniară.
Veți avea graficul dvs. așa.
Acesta este graficul de regresie.
Acum, dacă adăugați datele de mai jos și extindeți datele selectate. Veți vedea o schimbare în graficul dvs.
Pentru exemplul nostru, am adăugat 2000 la băutura răcorită vândută și am lăsat vânzătorii goi. Și când extindem intervalul graficului, acesta este ceea ce vom avea.
Acesta va da numărul necesar de vânzători pentru realizarea a 2000 de vânzări de băuturi răcoritoare sub formă grafică. Care este puțin sub 120 în grafic. Și din ecuația noastră de regresie, știm că este 116.
În acest articol, am încercat să acopăr totul sub Analiza regresiei Excel. Am explicat regresia în Excel 2016. Regresia în Excel 2010 și Excel 2013 este aceeași ca în Excel 2016.
Pentru orice altă întrebare cu privire la acest subiect, utilizați secțiunea de comentarii. Puneți o întrebare, dați o părere sau menționați doar greșelile mele gramaticale. Totul este binevenit. Nu ezitați să folosiți secțiunea de comentarii.
Cum se calculează funcția MODE în Excel
Cum se calculează funcția medie în Excel
Cum se creează un grafic de deviație standard
Statistici descriptive în Microsoft Excel 2016
Cum se folosește funcția Excel NORMDIST
Cum se utilizează diagrama și analiza Pareto
Articole populare:
50 Comandă rapidă Excel pentru a vă crește productivitatea
Cum se utilizează funcția VLOOKUP în Excel
Cum se utilizează funcția COUNTIF în Excel 2016
Cum se utilizează funcția SUMIF în Excel