Análisis de regresión de Excel (Tabla de contenido)

  • Análisis de regresión en Excel
  • Explicación de la regresión matemáticamente
  • ¿Cómo realizar una regresión lineal en Excel?
    • # 1 - Herramienta de regresión usando Analysis ToolPak en Excel
    • # 2 - Análisis de regresión usando Scatterplot con Trendline en Excel

Análisis de regresión en Excel

La regresión lineal es una técnica estadística que examina la relación lineal entre una variable dependiente y una o más variables independientes.

  • Variable dependiente (también conocida como variable de respuesta / resultado): es la variable que le interesa y que desea predecir en función de la información disponible de las variables independientes.
  • Variable independiente (también conocida como variable explicativa / predictiva): es / son las variables de las que depende la variable de respuesta. Lo que significa que estas son las variables que utilizan qué variable de respuesta se puede predecir.

La relación lineal significa que el cambio en una variable o variables independientes provoca un cambio en la variable dependiente.

Básicamente, también hay dos tipos de relaciones lineales.

  1. Relación lineal positiva: cuando la variable independiente aumenta, la variable dependiente también aumenta.
  2. Relación lineal negativa: cuando la variable independiente aumenta, la variable dependiente disminuye.

Estos fueron algunos de los requisitos previos antes de que realmente proceda hacia el análisis de regresión en Excel.

Hay dos formas básicas de realizar una regresión lineal en Excel usando:

  • Herramienta de regresión a través de Analysis ToolPak
  • Gráfico de dispersión con línea de tendencia

En realidad, hay un método más que utiliza fórmulas manuales para calcular la regresión lineal. Pero, ¿por qué debería hacerlo cuando Excel hace los cálculos por usted?

Por lo tanto, vamos a hablar sobre los dos métodos discutidos anteriormente solamente.

Supongamos que tiene datos de altura y peso de 10 individuos. Si traza esta información a través de un gráfico, veamos qué da.

Como muestra la captura de pantalla anterior, la relación lineal se puede encontrar en Altura y Peso a través del gráfico. No se involucre mucho en el gráfico ahora, de todos modos vamos a profundizar en la segunda parte de este artículo.

Explicación de la regresión matemáticamente

Tenemos una expresión matemática para regresión lineal como se muestra a continuación:

Y = aX + b + ε

Dónde,

  • Y es una variable dependiente o variable de respuesta.
  • X es una variable independiente o predictor.
  • a es la pendiente de la línea de regresión. Lo que representa que cuando X cambia, hay un cambio en Y por "a" unidades.
  • b está interceptando. Es el valor que toma Y cuando el valor de X es cero.
  • ε es el término de error aleatorio. Ocurre porque el valor predicho de Y nunca será exactamente el mismo que el valor real para una X dada. Este término de error, no debemos preocuparnos. Como hay algunos software que hacen el cálculo de este término de error en el back-end por usted. Excel es uno de ese software.

En ese caso, la ecuación se convierte en,

Y = aX + b

Que se puede representar como:

Peso = a * Altura + b

Intentaremos encontrar los valores de estos a y b utilizando los métodos que hemos discutido anteriormente.

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

El artículo adicional explica los conceptos básicos del análisis de regresión en Excel y muestra algunas formas diferentes de hacer una regresión lineal en Excel.

Puede descargar esta plantilla de Excel de análisis de regresión aquí - Plantilla de Excel de análisis de regresión

# 1 - Herramienta de regresión usando Analysis ToolPak en Excel

Para nuestro ejemplo, intentaremos ajustar la regresión para los valores de Peso (que es una variable dependiente) con la ayuda de los valores de Altura (que es una variable independiente).

  • En la hoja de cálculo de Excel, haga clic en Análisis de datos (presente en Grupo de análisis ) en Datos.

  • Busca la regresión . Selecciónelo y presione ok.

  • Utilice las siguientes entradas en el panel Regresión que se abre.

  • Rango Y de entrada : seleccione las celdas que contienen su variable dependiente (en este ejemplo B1: B11)

  • Rango de entrada X : seleccione las celdas que contienen su variable independiente (en este ejemplo A1: A11).

  • Marque la casilla llamada Etiquetas si sus datos tienen nombres de columna (en este ejemplo tenemos nombres de columna).

  • El nivel de confianza se establece en el 95% de forma predeterminada, que se puede cambiar según los requisitos de los usuarios.

  • En Opciones de salida, puede personalizar dónde desea ver la salida del análisis de regresión en Excel. En este caso, queremos ver el resultado en la misma hoja. Por lo tanto, dado el rango correspondiente.

  • En la opción Residuos, tiene entradas opcionales como Residuos, Gráficos de residuos, Residuos estandarizados, Gráficos de ajuste de línea que puede seleccionar según sus necesidades. En este caso, marque la casilla de verificación Residuos para que podamos ver la dispersión entre los valores predichos y los reales.

  • En la opción Probabilidad normal, puede seleccionar Gráficos de probabilidad normal que pueden ayudarlo a verificar la normalidad de los predictores. Haga clic en OK

  • Excel calculará el análisis de regresión por usted en una fracción de segundos.

Hasta aquí, fue fácil y no tan lógico. Sin embargo, interpretar este resultado y obtener información valiosa a partir de él es una tarea difícil.

Una parte importante de toda esta salida es R cuadrado / R cuadrado ajustado en la tabla RESUMEN DE SALIDA. Que proporciona información, qué tan bueno es nuestro modelo. En este caso, el valor R cuadrado es 0.9547. Lo que interpreta que el modelo tiene una precisión del 95, 47% (buen ajuste). O en otro idioma, la información sobre la variable Y se explica 95.47% por la variable X.

La otra parte importante de todo el resultado es una tabla de coeficientes. Da valores de coeficientes que pueden usarse para construir el modelo para predicciones futuras.

Ahora nuestra ecuación de regresión para predicción se convierte en:

Peso = 0.6746 * Altura - 38.45508 (El valor de la pendiente para Altura es 0.6746 … y la Intercepción es -38.45508 …)

¿Obtuviste lo que has definido? Ha definido una función en la que ahora solo tiene que poner el valor de Altura y obtendrá el valor de Peso.

# 2 - Análisis de regresión usando Scatterplot con Trendline en Excel

Ahora, veremos cómo en Excel podemos ajustar una ecuación de regresión en un diagrama de dispersión.

  • Seleccione sus datos completos de dos columnas (incluidos los encabezados).
  • Haga clic en Insertar y seleccione Diagrama de dispersión en la sección de gráficos como se muestra en la imagen a continuación.

  • Ver el gráfico de salida.

  • Ahora, necesitamos tener una línea de regresión de mínimos cuadrados en este gráfico. Para agregar esta línea, haga clic derecho en cualquiera de los puntos de datos en el gráfico y seleccione la opción Agregar línea de tendencia .

  • Le permitirá tener una línea de tendencia de mínimo cuadrado de regresión como se muestra a continuación.

  • En la opción Formato de línea de tendencia, marque la casilla para Mostrar ecuación en el gráfico.

  • Le permite ver la ecuación de la línea de regresión de mínimos cuadrados en el gráfico.

Esta es la ecuación mediante la cual podemos predecir los valores de peso para cualquier conjunto dado de valores de Altura.

Cosas para recordar sobre el análisis de regresión en Excel

  • Puede cambiar el diseño de la línea de tendencia en la opción Formatear línea de tendencia en el diagrama de dispersión.
  • Siempre se recomienda echar un vistazo a los gráficos residuales mientras realiza el análisis de regresión utilizando Data Analysis ToolPak en Excel. Le da una mejor comprensión de la propagación de los valores reales de Y y los valores estimados de X.
  • La regresión lineal simple en Excel no necesita ANOVA y el cuadrado R ajustado para verificar. Estas características se pueden tener en cuenta para la regresión lineal múltiple. Que está más allá del alcance de este artículo.

Artículos recomendados

Esta ha sido una guía para el análisis de regresión en Excel. Aquí discutimos cómo hacer el Análisis de regresión en Excel junto con ejemplos de Excel y una plantilla de Excel descargable. También puede consultar nuestros otros artículos sugeridos:

  1. Herramienta Excel para análisis de datos
  2. Calcular ANOVA en Excel
  3. Cómo encontrar los promedios móviles de Excel
  4. Ejemplos de prueba Z en Excel

Categoría: