Cómo hacer una recta de regresión lineal en Excel

La regresión lineal es una línea que se ajusta a un conjunto de datos para observar la relación entre dos variables. Se usa para el análisis y la predicción de datos en un contexto lineal.

En este artículo, te enseñaremos a cómo hacer una recta de regresión lineal en Excel de forma práctica y rápida. De esta manera, lograrás aplicar los gráficos en los momentos necesarios.

Diferencias entre una recta de regresión y una recta de regresión lineal en Excel

En Excel, no hay una diferencia significativa entre una “recta de regresión” y una “recta de regresión lineal”. Ambos términos se utilizan indistintamente para referirse al mismo concepto: una línea que se ajusta a la relación lineal entre una variable dependiente y una variable independiente utilizando el método de regresión lineal.

Aspectos a tener en cuenta para hacer la recta de regresión en Excel

Estos son algunos términos con los que tienes que estar familiarizado para poder realizar una recta de regresión en Excel:

• Variable dependiente: es aquella sobre la cual se quiere medir o hacer la estimación del efecto.
• Variable(s) independiente (s): son aquellas variables que intentan explicar a la variable dependiente.
• Observaciones: representa el conjunto de datos de donde se va a agregar la regresión lineal. Cada fila es una observación.
• Forma funcional: una regresión lineal se puede expresar matemáticamente como:

¿Cómo realizar una recta de regresión lineal en Excel?

Para hacer una recta de regresión en Excel solo tienes que ejecutar los siguientes pasos:

1. Ingresa los datos necesarios para la gráfica.
2. Selecciona los valores y ve a la pestaña “Insertar”.
3. Busca y haz clic sobre el icono de dispersión. Elige el primer modelo.
4. Pulsa clic derecho sobre los puntos de dispersión de la gráfica. Escoge “Agregar línea de tendencia…”.
5. Presiona sobre “Lineal”.
6. Desliza y oprime sobre el recuadro de “Presentar ecuación en el gráfico” y “Presentar el valor R cuadrado en el gráfico”.

¿Cómo hacer una recta de regresión en Excel con la herramienta Análisis de datos?

Supongamos que tienes una lista de datos que contiene el peso y la altura de cada estudiante en un salón de clases. Para hacer una estadística de la regresión lineal es necesario activar la opción “Análisis de datos” en Excel. Para esto, debes seguir la ruta: “Archivo > Opciones”.

Luego, debes pulsar el botón de “Complementos”. En la opción de “Complementos de Excel” pulsa sobre “Ir…”. Aparecerá una ventana informativa en la que deberás marcar la casilla de “Herramientas para análisis”. Una vez que hayas hecho esto, presiona “Aceptar”.

Ahora, tienes que ir a la pestaña “Datos” para presionar la nueva opción de “Análisis de datos” que se ha habilitado con los anteriores pasos. Al presionar esta herramienta, se abrirá otra ventana en la que deberás buscar la palabra “Regresión” y presionar “Aceptar”.

Verás una nueva ventana para ingresar datos. Para este ejemplo, en “Rango Y de entrada” vamos a seleccionar toda la columna de “Peso” que corresponderá a las variables dependientes. Después, en “Rango X de entrada”, vamos a agregar todos los datos de “Altura” puesto que son las variables independientes.

Marca la casilla “Rótulos” y escoge el rango de salida para que la información aparezca en la misma hoja o marca “En una hoja nueva”. Hecho esto, pulsa “Aceptar” para ver los resultados.

En una hoja nueva o en rango que hayas seleccionado aparecerá unos nuevos rótulos con diferentes tipos de información. Lo más importante que debemos resaltar es:

• Las estadísticas de la regresión.
• El análisis de varianza (F y valor crítico de F).
• Análisis de la intercepción (Coeficientes, Error típico, Estadístico t, Probabilidad, etc.).

Interpretación de los resultados de la estadística de regresión en Excel

Una vez que se ha hecho la operación para calcular la regresión lineal en Excel, solo queda por analizar los diferentes datos que arroje la operación. Empezaremos con las “Estadísticas de la regresión” cuyo recuadro contiene los siguientes argumentos:

Coeficiente de correlación múltiple

El coeficiente de correlación es una medida que varía entre -1 y 1 e indica la proximidad de movimiento entre las variables de una regresión. Si el valor es cercano a 1 significa que las variables se mueven parecidamente, mientras que un valor cercano a -1 indica que están en direcciones opuestas. Si el valor es cero, significa que no hay relación entre las variables.Para el ejemplo anterior, el valor de la correlación es de 0.943, lo cual representa una correlación positiva fuerte entre la altura y el peso, considerando también la constante.

Coeficiente de determinación R^2

Este coeficiente es una medida que indica el porcentaje de variabilidad en la variable dependiente (peso) que puede ser explicada por las variables independientes (altura y constante). Su valor oscila entre 0 y 1, y mientras más cercano a 1, las variables independientes tienen una gran influencia en la variación de la variable dependiente.

En este caso, el coeficiente de determinación R^2 es 0.8905, lo que significa que aproximadamente el 89.05% de la variabilidad en el peso puede ser explicada por la altura y la constante.

R^2 ajustado

Como su nombre lo indica, este argumento representa el valor corregido de R^2 y se ajusta por la cantidad de variables que posea la regresión. El R^2 se acercará cada vez más a 1 en la medida de que se agreguen suficientes cantidades de variables al ejercicio, así esas variables sean parcial o totalmente irrelevantes.

Para este ejemplo, el R^2 ajustado es de 0.8795. Disminuyó un poco porque el ejercicio solo posee dos variables dependientes que son el peso y la constante.

Error típico

También es conocido como error estándar y corresponde a qué tantas desviaciones tienen las variables de la predicción. En otras palabras, se usa para evaluar qué tan cerca o lejanos están los puntos de dispersión al momento de hacer la recta de la regresión en la gráfica. En este caso, el error estándar es de 3.58, por ende, no hay mucha dispersión.

Observaciones

Las observaciones solo representan la cantidad de filas que se han ocupado para la elaboración del ejercicio.

Interpretación del análisis de varianza (Tabla ANOVA)

El análisis de varianza (ANOVA) evalúa la significancia global del modelo de regresión. El valor de “F” calculada es 81.334 y comparando este valor con el “Valor crítico de F” que es 4.062, podemos concluir que el modelo de regresión es estadísticamente significativo, lo que indica que la altura y la constante tienen un efecto conjunto significativo sobre el peso.

Coeficientes de la regresión lineal en Excel

En el siguiente recuadro, observarás los siguientes argumentos:

• Coeficientes.
• Error típico.
• Estadístico t.
• Probabilidad
• Inferior 95%.
• Superior 95%.

Explicaremos cada punto para comprender los resultados y analizar qué quiere decir cada cifra del recuadro.

Coeficientes

Los resultados del análisis de regresión lineal en Excel muestran los siguientes coeficientes:

• Intercepción: este valor indica cuál sería la estimación de la variable independiente si todas las otras variables son iguales a cero, por ende, para este ejemplo, indica el valor de la variable dependiente (peso) cuando la variable independiente (altura) es igual a cero. El valor de la intercepción es -73.766086. En este caso, no tiene un significado práctico, ya que la altura no puede ser igual a cero en la realidad.
• Altura (m): el coeficiente para la variable de altura es 83.646. Esto significa que, por cada unidad de aumento en la altura, se espera un aumento de 83.646 en el peso, manteniendo constante el resto de las variables en el modelo.

Error típico

El error estándar representa la variabilidad en los datos con respecto a la estimación del coeficiente en la regresión. En este caso, el error típico para la “Intercepción” es de 15.33843 y para la variable de “Altura” es de 9.274866.

Estadístico t

El estadístico t es una medida de la significancia estadística de los coeficientes que se calcula dividiendo el coeficiente entre el error típico. Los valores más grandes en valor absoluto indican una mayor significancia. En este caso, el estadístico t para la intercepción es -4.809232117 y para la variable de altura es 9.018579414, por ende, se puede decir que la altura es significativa.

Probabilidad

Este valor también es conocido como valor-p y representa la probabilidad de que el coeficiente sea igual a cero. La variable "Intercepción" representa el valor estimado del peso cuando la “Altura” es igual a cero. En este caso, el valor estimado es de 0.00071366, pero en el contexto de la altura y el peso de los estudiantes, un valor de altura igual a cero no tiene un significado práctico.

Por su parte, la variable “Altura (m)” representa la pendiente de la regresión, es decir, el cambio estimado en el peso por cada unidad de cambio en la altura. El resultado fue de 4.06201E-06, lo que indica un cambio muy pequeño en el peso por cada unidad de cambio en la altura.

Inferior 95% y Superior 95%

Estas columnas representan los intervalos de confianza de los coeficientes. Por ende, si se tiene el error típico y la estimación puntual (porcentaje), el coeficiente debe moverse entre 2 valores.

El intervalo de confianza al 95% para la “Intercepción” se encuentra entre -107.9422 y -39.58992. Esto significa que, es probable que la verdadera intercepción de la regresión esté dentro de este rango.

Por su parte, el intervalo de confianza al 95% para la “Altura” se encuentra entre 62.9804241 y 104.3118. Esto quiere decir que es probable que la verdadera relación entre la altura y el peso se encuentre en este rango.

¿Cómo analizar residuos de una regresión lineal en Excel?

Los residuos son las diferencias entre los valores reales de la variable dependiente y los valores predichos por el modelo de regresión lineal.

Es posible analizar los residuos de una regresión lineal en Excel. Para ello, solo tienes que usar la herramienta “Análisis de datos” y realizar el procedimiento que se ha explicado al inicio del artículo. En el recuadro de “Regresión”, marca la casilla de “Residuos”. Al hacer esto, tendrás un nuevo renglón en el folio de resultados cuyo nombre será “Análisis de los residuales”.

De acuerdo con estos resultados, se puede concluir que existe una relación positiva fuerte entre la altura y el peso. A medida que la altura aumenta, se espera que el peso también aumente.

Sin embargo, es importante tener en cuenta que la relación lineal no explica la totalidad de la variabilidad en el peso, ya que existen otros factores que también pueden influir en el peso de una persona como estilo de vida, alimentación, metabolismo, actividad física, entre otros.

En resumen, cuentas con dos métodos para realizar una recta de regresión lineal en Excel.