Analiza regresiei Excel (Cuprins)
- Analiza regresiei în Excel
- Explicația regresiei Matematic
- Cum se efectuează regresia liniară în Excel?
- # 1 - Instrument de regresie folosind instrumentul de analizăPak în Excel
- # 2 - Analiza regresiei folosind Scatterplot cu Trendline în Excel
Analiza regresiei în Excel
Regresia liniară este o tehnică statistică care examinează relația liniară dintre o variabilă dependentă și una sau mai multe variabile independente.
- Variabilă dependentă (aka variabilă de răspuns / rezultat): este variabila de interes și pe care ați dorit să o prezice pe baza informațiilor disponibile ale variabilei independente.
- Variabilă independentă (aka variabilă explicativă / predictoare): Este / sunt variabila (variabilele) de care depinde variabila de răspuns. Ceea ce înseamnă că acestea sunt variabilele care utilizează ce variabilă de răspuns poate fi prevăzută.
Relație liniară înseamnă că schimbarea unei variabile independente provoacă o modificare a variabilei dependente.
Există practic două tipuri de relații liniare.
- Relație liniară pozitivă: când variabila independentă crește, variabila dependentă crește și ea.
- Relația liniară negativă: când variabila independentă crește, variabila dependentă scade.
Acestea au fost câteva dintre condițiile anterioare înainte de a continua efectiv spre analiza regresiei în excel.
Există două moduri de bază pentru a efectua regresia liniară în excel folosind:
- Instrument de regresie prin Instrumentul de analiză
- Diagrama de scatter cu trendline
Există de fapt încă o metodă care folosește formula manuală pentru a calcula regresia liniară. Dar de ce ar trebui să mergi pentru asta când Excel face calcule pentru tine?
Prin urmare, vom vorbi doar despre cele două metode discutate mai sus.
Să presupunem că aveți o înălțime și o greutate de 10 persoane. Dacă complotați aceste informații printr-un grafic, să vedem ce oferă.
După cum arată ecranul de mai sus, relația liniară poate fi găsită în înălțime și greutate prin grafic. Nu vă implicați prea mult în grafic acum, oricum o vom săpa în adâncime în partea a doua a acestui articol.
Explicația regresiei Matematic
Avem o expresie matematică pentru regresie liniară, ca mai jos:
Y = aX + b + ε
Unde,
- Y este o variabilă dependentă sau o variabilă de răspuns.
- X este o variabilă sau un predictor independent.
- a este panta liniei de regresie. Ceea ce reprezintă faptul că atunci când X se schimbă, există o schimbare în Y de unitățile „a”.
- b este interceptant. Este valoarea Y luată atunci când valoarea lui X este zero.
- ε este termenul de eroare aleatorie. Se produce deoarece valoarea prevăzută de Y nu va fi niciodată exact aceeași cu valoarea reală pentru X. Acest termen de eroare, nu trebuie să ne preocupăm. Deoarece există un software care face calculul acestui termen de eroare în backend pentru dvs. Excel este unul dintre acel software.
În acest caz, ecuația devine,
Y = aX + b
Care poate fi reprezentat ca:
Greutatea = a * Înălțimea + b
Vom încerca să aflăm valorile acestor a și b folosind metodele despre care am discutat mai sus.
Cum se efectuează regresia liniară în Excel?
Articolul suplimentar explică elementele de bază ale analizei de regresie în excel și arată câteva moduri diferite de a face regresia liniară în Excel.
Puteți descărca acest șablon Excel de analiză de regresie aici - șablon Excel Analysis Analysis# 1 - Instrument de regresie folosind instrumentul de analizăPak în Excel
Pentru exemplul nostru, vom încerca să se potrivească regresiei valorilor de greutate (care este variabilă dependentă) cu ajutorul valorilor de înălțime (care este o variabilă independentă).
- În foaia de calcul Excel, faceți clic pe Analiza datelor (prezentă în Grupul de analiză ) sub Date.
- Căutați regresul . Selectați-l și apăsați ok.
- Utilizați următoarele intrări sub panoul Regresie care se deschide.
- Interval de intrare Y : Selectați celulele care conțin variabila dvs. dependentă (în acest exemplu B1: B11)
- Interval de intrare X : Selectați celulele care conțin variabila dvs. independentă (în acest exemplu A1: A11).
- Bifează caseta denumită Etichete dacă datele tale au nume de coloană (în acest exemplu avem nume de coloană).
- Nivelul de încredere este setat la 95% implicit, care poate fi modificat conform cerințelor utilizatorilor.
- În Opțiuni de ieșire, puteți personaliza unde doriți să vedeți ieșirea analizei de regresie în Excel. În acest caz, dorim să vedem ieșirea din aceeași foaie. Prin urmare, având în vedere intervalul în consecință.
- În opțiunea Reziduuri, aveți intrări opționale, cum ar fi: reziduuri, parcele reziduale, reziduale standardizate, parcele de montare pe linie, pe care le puteți selecta în funcție de nevoia dvs. În acest caz, bifează caseta de selectare Reziduuri, astfel încât să putem vedea dispersia dintre valorile prezise și cele reale.
- În opțiunea Probabilitate normală, puteți selecta Planuri normale de probabilitate care vă pot ajuta să verificați normalitatea predictorilor. Faceți clic pe OK .
- Excel va calcula analiza de regresie pentru dvs. într-o fracțiune de secunde.
Până aici, a fost ușor și nu atât de logic. Cu toate acestea, interpretarea acestei ieșiri și realizarea unor informații valoroase din aceasta este o sarcină complicată.
O parte importantă a întregii ieșiri este R Square / R Square Square ajustat sub SUMMARY OUTPUT. Care oferă informații, cât de bun este modelul nostru. În acest caz, valoarea R Square este 0, 9547. Ceea ce interpretează că modelul are o precizie de 95, 47% (potrivire bună). Sau într-o altă limbă, informațiile despre variabila Y sunt explicate 95, 47% prin variabila X.
Cealaltă parte importantă a întregii producții este un tabel de coeficienți. Acesta oferă valori ale coeficienților care pot fi folosiți pentru a construi modelul pentru previziuni viitoare.
Ecuația noastră de regresie pentru predicție devine:
Greutate = 0, 6746 * Înălțime - 38, 45508 (Valoarea pantei pentru Înălțime este 0, 6746 … iar Interceptul este -38, 45508 …)
Ai primit ceea ce ai definit? Ați definit o funcție în care acum trebuie doar să puneți valoarea Înălțime și veți obține valoarea Greutate.
# 2 - Analiza regresiei folosind Scatterplot cu Trendline în Excel
Acum, vom vedea cum, în excel, putem încadra o ecuație de regresie pe o diagramă de dispersie.
- Selectați toate cele două date pe coloană (inclusiv anteturile).
- Faceți clic pe Insert și selectați Scatter Plot în secțiunea grafică, așa cum se arată în imaginea de mai jos.
- Consultați graficul de ieșire.
- Acum, trebuie să avem o linie de regresie cel puțin pătrată pe acest grafic. Pentru a adăuga această linie, faceți clic dreapta pe oricare dintre punctele de date din grafic și selectați opțiunea Adăugare trend .
- Vă va permite să aveți o linie de tendință de cel puțin pătrat de regresie ca mai jos.
- Sub opțiunea Format Trendline, bifează caseta pentru Ecuația afișării pe grafic.
- Vă permite să vedeți ecuația liniei de regresie cel puțin pătrată.
Aceasta este ecuația folosind care putem prezice valorile de greutate pentru orice set de valori de înălțime.
Lucruri de reținut despre analiza regresiei în Excel
- Puteți schimba aspectul liniei de trend sub opțiunea Format Trendline în graficul de împrăștiere.
- Este întotdeauna recomandat să aruncați o privire asupra loturilor reziduale în timp ce efectuați o analiză de regresie folosind Instrumentul de analiză a datelor în Excel. Vă oferă o mai bună înțelegere a răspândirii valorilor Y reale și a valorilor X estimate.
- Regresia liniară simplă în excel nu are nevoie de ANOVA și Pătrat R ajustat pentru a verifica. Aceste caracteristici pot fi luate în considerare pentru regresie liniară multiplă. Ceea ce este în afara domeniului de aplicare al acestui articol.
Articole recomandate
Acesta a fost un ghid pentru analiza regresiei în Excel. Aici vom discuta despre cum se face analiza de regresie în Excel, împreună cu exemple excel și șablon excel descărcabil. De asemenea, puteți parcurge și alte articole sugerate -
- Instrument Excel pentru analiza datelor
- Calculați ANOVA în Excel
- Cum să găsiți medii în mișcare Excel
- Z TEST Exemple în Excel