Creació d’un model de regressió lineal a Excel

La regressió lineal és una trama de dades que gràfica la relació lineal entre una variable independent i dependent. Normalment s’utilitza per mostrar visualment la força de la relació i la dispersió dels resultats, tot amb l’objectiu d’explicar el comportament de la variable dependent.

Diguem que volíem posar a prova la força de la relació entre la quantitat de gelats menjats i l’obesitat. Agafaríem la variable independent, la quantitat de gelat, i la relacionaríem amb la variable depenent, l'obesitat, per veure si hi havia alguna relació. Com que la regressió és una visualització gràfica d'aquesta relació, com més baixa és la variabilitat de les dades, més forta és la relació i més ajustat a la línia de regressió.

Compres per emportar

• La regressió lineal modelitza la relació entre una variable dependent o independent.
• L’anàlisi de regressió es pot aconseguir si les variables són independents, no hi ha heteroscedasticitat i els termes d’error de les variables no es correlacionen.
• El model de regressió lineal a Excel és més senzill amb el ToolPak Analysis Data.

Consideracions importants

Hi ha alguns supòsits crítics sobre el vostre conjunt de dades que han de ser certs per continuar amb una anàlisi de regressió:

1. Les variables han de ser veritablement independents (utilitzant un test Chi-square).
2. Les dades no han de tenir diferències d’error diferents (això s’anomena heteroskedasticitat (també heteroscedasticitat ortogràfica)).
3. Els termes d’error de cada variable han d’estar incorrelats. Si no, significa que les variables estan correlacionades en sèrie.

Si les tres coses semblen complicades, ho són. Però l'efecte d'una d'aquestes consideracions no és cert és una estimació esbiaixada. Essencialment, equivocaria la relació que mesura.

Sortida d’una regressió a Excel

El primer pas per executar l’anàlisi de regressió a Excel és comprovar que s’ha instal·lat el connector d’exploració gratuït Data Analysis ToolPak. Aquest complement facilita el càlcul d'una gamma d'estadístiques. No es requereix el gràfic d'una línia de regressió lineal, però és més senzill crear taules estadístiques. Per verificar si esteu instal·lats, seleccioneu "Dades" a la barra d'eines. Si "Anàlisi de dades" és una opció, la funció està instal·lada i llesta per utilitzar. Si no està instal·lat, podeu sol·licitar aquesta opció fent clic al botó Office i seleccionant "Opcions Excel".

Utilitzant el ToolPak d'anàlisi de dades, es pot crear una sortida de regressió amb només uns clics.

La variable independent va dins del rang X.

Tenint en compte els rendiments de S&P 500, diguem que volem saber si podem estimar la força i la relació dels rendiments de les accions Visa (V). Les dades de retorn de les accions Visa (V) rellenen la columna 1 com a variable dependent. S&P 500 retorna les dades que ocupa la columna 2 com a variable independent.

1. Seleccioneu "Dades" a la barra d'eines. Es mostra el menú "Dades".
2. Seleccioneu "Anàlisi de dades". Es mostra el quadre de diàleg Anàlisi de dades - Eines d’anàlisi.
3. Al menú, seleccioneu "Regressió" i feu clic a "D'acord".
4. Al quadre de diàleg Regressió, feu clic al quadre "Introducció Y Range" i seleccioneu les dades de devolució de la variable depenent (Visa (V)).
5. Feu clic al quadre "Entrada X Range" i seleccioneu les dades de la variable independent (devolucions S&P 500).
6. Feu clic a "D'acord" per executar els resultats.

[Nota: Si la taula sembla petita, feu clic amb el botó dret a la imatge i obriu-la a la pestanya nova per obtenir una resolució més alta.]

Interpretar els resultats

Amb aquestes dades (el mateix del nostre article quadrat R), obtenim la taula següent:

El valor R ², també conegut com a coeficient de determinació, mesura la proporció de variació de la variable depenent explicada per la variable independent o el bé que s’adapta al model de regressió de les dades. El valor R ² oscil·la entre 0 i 1, i un valor més alt indica una millor adaptació. El valor p, o valor de probabilitat, també oscil·la entre 0 i 1 i indica si la prova és significativa. En contrast amb el valor R ², un valor p més petit és favorable ja que indica una correlació entre les variables dependents i independents.

Diagrama d'una regressió a Excel

Podem traçar una regressió a Excel destacant les dades i cartografiant-les com a trama de dispersió. Per afegir una línia de regressió, trieu "Disseny" al menú "Eines del gràfic". Al quadre de diàleg, seleccioneu "Tendència" i, a continuació, "Línia de línia lineal". Per afegir el valor R ², seleccioneu "Més opcions de línia de tendència" al menú "Tendència. Finalment, seleccioneu" Mostra el valor quadrat R al gràfic ". El resultat visual resumeix la força de la relació, encara que a costa de no proporcionar-los tants detalls com la taula anterior.