Crearea unui model de regresie liniară în Excel

Regresia liniară este o diagramă de date care grafică relația liniară dintre o variabilă independentă și dependentă. Este de obicei utilizat pentru a arăta vizual puterea relației și dispersia rezultatelor - toate în scopul explicării comportamentului variabilei dependente.

Spune că am vrut să testăm puterea relației dintre cantitatea de înghețată mâncată și obezitatea. Am lua variabila independentă, cantitatea de înghețată și am relaționa-o cu variabila dependentă, obezitatea, pentru a vedea dacă a existat o relație. Având în vedere că o regresie este o afișare grafică a acestei relații, cu cât variabilitatea este mai scăzută a datelor, cu atât este mai puternică relația și cu atât mai strânsă se va potrivi liniei de regresie.

Cheie de luat cu cheie

• Regresia liniară modelează relația dintre o variabilă (variabilă) dependentă și independentă.
• Analiza de regresie poate fi realizată dacă variabilele sunt independente, nu există heteroscedasticitate, iar termenii de eroare ale variabilelor nu sunt corelați.
• Modelarea regresiei liniare în Excel este mai ușoară cu Instrumentul de analiză a datelor.

Considerații importante

Există câteva ipoteze critice despre setul de date care trebuie să fie adevărat pentru a continua cu o analiză de regresie:

1. Variabilele trebuie să fie cu adevărat independente (folosind un test Chi-square).
2. Datele nu trebuie să aibă variații de eroare diferite (aceasta se numește heteroskedasticitate (de asemenea, heteroscedasticitate ortografică)).
3. Termenii de eroare ai fiecărei variabile trebuie să fie necorelați. Dacă nu, înseamnă că variabilele sunt corelate în serie.

Dacă aceste trei lucruri sună complicate, acestea sunt. Dar efectul uneia dintre aceste considerente nu este adevărat este o estimare părtinitoare. În esență, ați afirma greșit relația pe care o măsurați.

Afișarea unei regresii în Excel

Primul pas în derularea analizei de regresie în Excel este verificarea dublă a instalării pluginului Excel gratuit pentru Analiza datelor ToolPak. Acest plugin face foarte ușor calcularea unei game de statistici. Nu este obligatoriu să graficăm o linie de regresie liniară, dar creează tabele statistice mai simple. Pentru a verifica dacă este instalat, selectați „Date” din bara de instrumente. Dacă „Analiza datelor” este o opțiune, funcția este instalată și gata de utilizare. Dacă nu este instalată, puteți solicita această opțiune făcând clic pe butonul Office și selectând „Opțiuni Excel”.

Folosind Instrumentul de analiză a datelor, crearea unei ieșiri de regresie este de doar câteva clicuri.

Variabila independentă se încadrează în intervalul X.

Având în vedere randamentele S&P 500, să spunem că vrem să știm dacă putem estima puterea și relația randamentelor de acțiuni Visa (V). Stocurile Visa (V) returnează datele populează coloana 1 ca variabilă dependentă. S&P 500 returnează datele populează coloana 2 ca variabilă independentă.

1. Selectați „Date” din bara de instrumente. Se afișează meniul „Date”.
2. Selectați „Analiza datelor”. Se afișează caseta de dialog Analiza datelor - Instrumente de analiză.
3. Din meniu, selectați „Regresie” și faceți clic pe „OK”.
4. În caseta de dialog Regresie, faceți clic pe caseta „Intrare Y Range” și selectați datele variabile dependente (Visa (V)).
5. Faceți clic pe caseta „Intrare X Range” și selectați datele variabile independente (returnări S&P 500).
6. Faceți clic pe „OK” pentru a rula rezultatele.

[Notă: Dacă tabelul pare mic, faceți clic dreapta pe imagine și deschideți o nouă filă pentru o rezoluție mai mare.]

Interpretați rezultatele

Folosind aceste date (aceleași din articolul nostru R-pătrat), obținem următorul tabel:

Valoarea R ², cunoscută și sub denumirea de coeficient de determinare, măsoară proporția de variație a variabilei dependente explicată de variabila independentă sau cât de bine se încadrează modelul de regresie a datelor. Valoarea R ² variază de la 0 la 1, iar o valoare mai mare indică o potrivire mai bună. Valoarea p, sau valoarea probabilității, de asemenea, variază de la 0 la 1 și indică dacă testul este semnificativ. Spre deosebire de valoarea R ², o valoare mai mică este favorabilă, deoarece indică o corelație între variabilele dependente și independente.

Graficarea unei regresii în Excel

Putem grafica o regresie în Excel prin evidențierea datelor și graficarea lor ca un grafic de împrăștiere. Pentru a adăuga o linie de regresie, alegeți „Aspect” din meniul „Instrumente grafic”. În caseta de dialog, selectați „Trendline” și apoi „Line line Trendline”. Pentru a adăuga valoarea R ², selectați „Mai multe opțiuni de tendință” din meniul „Trendline. În sfârșit, selectați„ Afișați valoarea R-pătrat pe grafic ”. Rezultatul vizual însumează puterea relației, deși în detrimentul ne furnizării. la fel de multe detalii ca tabelul de mai sus.