La regressió a Excel és una manera d'automatitzar el procés estadístic de comparar diversos conjunts d'informació per veure com els canvis en les variables independents afecten els canvis en les variables dependents. Si alguna vegada has volgut trobar una correlació entre dues coses, utilitzar l'anàlisi de regressió a Excel és una de les millors maneres de fer-ho.
Les instruccions d'aquest article s'apliquen a Excel 2019, Excel 2016, Excel 2013, Excel 2010.
Quin és el significat de la regressió?
La regressió és un enfocament de modelatge estadístic que utilitzen els analistes per determinar les relacions entre diverses variables.
L'anàlisi de regressió comença amb una sola variable que intenteu analitzar i variables independents que esteu provant per veure si afecten aquesta variable única. L'anàlisi analitza els canvis en les variables independents i intenta correlacionar aquests canvis amb els canvis resultants en la variable única (depenent).
Això pot semblar una estadística avançada, però Excel posa aquesta anàlisi complexa a l'abast de tothom.
Regressió lineal a Excel
La forma més senzilla d'anàlisi de regressió és la regressió lineal. La regressió lineal simple analitza la relació entre només dues variables.
Per exemple, el full de càlcul següent mostra dades que contenen el nombre de calories que una persona va menjar cada dia i el seu pes en aquell dia.
Com que aquest full de càlcul conté dues columnes de dades i una variable podria tenir un efecte sobre l' altra, podeu fer una anàlisi de regressió d'aquestes dades amb Excel.
Activació del complement del paquet d'eines d'anàlisi
Abans de poder utilitzar la funció d'anàlisi de regressió d'Excel, heu d'activar el complement Analysis ToolPak a la pantalla Opcions d'Excel.
-
A Excel, seleccioneu el menú Fitxer i trieu Opcions.
Image -
Seleccioneu Complements al menú de navegació de l'esquerra. A continuació, assegureu-vos que Complements d'Excel estigui seleccionat al camp Gestiona.
Image -
Finalment, seleccioneu el botó Vés.
Image -
A la finestra emergent Complements. Activeu Analysis ToolPack fent clic a la casella que hi ha al davant per afegir una marca de verificació i seleccioneu D'acord.
Image
Ara que el paquet d'eines d'anàlisi està habilitat, ja esteu preparat per començar a fer anàlisis de regressió a Excel.
Com realitzar una regressió lineal simple a Excel
Usant el full de càlcul de pes i calories com a exemple, podeu realitzar una anàlisi de regressió lineal a Excel de la manera següent.
-
Seleccioneu el menú Dades. A continuació, al grup Anàlisi, seleccioneu Anàlisi de dades.
Image -
A la finestra Anàlisi de dades, seleccioneu Regressió de la llista i feu clic a D'acord.
Image -
El Interval Y d'entrada és l'interval de cel·les que conté la variable dependent. En aquest exemple, aquest és el pes. L'interval X d'entrada és l'interval de cel·les que conté la variable independent. En aquest exemple, aquesta és la columna de calories.
Image -
Seleccioneu Etiquetes per a les cel·les de la capçalera i, a continuació, seleccioneu Full de treball nou per enviar els resultats a un full de treball nou. Seleccioneu D'acord perquè Excel executi l'anàlisi i enviï els resultats a un full nou.
Image -
Examineu el nou full de treball. La sortida de l'anàlisi té una sèrie de valors que cal entendre per interpretar els resultats.
Image Cadascun d'aquests números té els significats següents:
- Múltiple R: el coeficient de correlació. 1 indica una forta correlació entre les dues variables, mentre que -1 significa que hi ha una forta relació negativa. 0 significa que no hi ha cap correlació.
- R Quadrat: el coeficient de determinació, que mostra quants punts entre les dues variables cauen a la recta de regressió. Estadísticament, aquesta és la suma de les desviacions al quadrat de la mitjana.
- Quadrat R ajustat: un valor estadístic anomenat R quadrat que s'ajusta pel nombre de variables independents que heu triat.
- Error estàndard: la precisió dels resultats de l'anàlisi de regressió. Si aquest error és petit, els resultats de la regressió són més precisos.
- Observacions: el nombre d'observacions del vostre model de regressió.
Els valors restants a la sortida de regressió us donen detalls sobre components més petits de l'anàlisi de regressió.
- df: valor estadístic conegut com a graus de llibertat relacionat amb les fonts de variància.
- SS: suma de quadrats. La proporció de la suma residual de quadrats en comparació amb el SS total hauria de ser més petita si la majoria de les vostres dades s'ajusten a la línia de regressió.
- MS: quadrat mitjà de les dades de regressió.
- F: l'estadística F (test F) per a la hipòtesi nul·la. Això proporciona la importància del model de regressió.
- Importància F: valor estadístic conegut com a valor P de F.
A menys que entenguis les estadístiques i els models de regressió de càlcul, els valors de la part inferior del resum no tindran gaire significat. Tanmateix, el quadrat R i R múltiple són els dos més importants.
Com podeu veure, en aquest exemple, les calories tenen una forta correlació amb el pes total.
Anàlisi de regressió lineal múltiple a Excel
Per realitzar la mateixa regressió lineal però amb diverses variables independents, seleccioneu tot l'interval (diverses columnes i files) per a l'interval X d'entrada.
Quan seleccioneu diverses variables independents, és menys probable que trobeu una correlació tan forta perquè hi ha tantes variables.
No obstant això, una anàlisi de regressió a Excel us pot ajudar a trobar correlacions amb una o més d'aquestes variables que potser no us adoneu que existeixen només revisant les dades manualment.
