Regresión lineal en Excel (Tabla de contenido)

  • Introducción a la regresión lineal en Excel
  • Métodos para usar la regresión lineal en Excel

Introducción a la regresión lineal en Excel

La regresión lineal es una técnica / método estadístico utilizado para estudiar la relación entre dos variables cuantitativas continuas. En esta técnica, se usan variables independientes para predecir el valor de una variable dependiente. Si solo hay una variable independiente, entonces es una regresión lineal simple, y si varias variables independientes son más de una, entonces es una regresión lineal múltiple. Los modelos de regresión lineal tienen una relación entre variables dependientes e independientes ajustando una ecuación lineal a los datos observados. Lineal se refiere al hecho de que usamos una línea para ajustar nuestros datos. Las variables dependientes utilizadas en el análisis de regresión también se denominan variables de respuesta o predichas, y las variables independientes también se denominan variables explicativas o predictores.

Una línea de regresión lineal tiene una ecuación del tipo: Y = a + bX;

Dónde:

  • X es la variable explicativa,
  • Y es la variable dependiente,
  • b es la pendiente de la línea,
  • a es la intersección con el eje y (es decir, el valor de y cuando x = 0).

El método de mínimos cuadrados generalmente se usa en la regresión lineal que calcula la mejor línea de ajuste para los datos observados al minimizar la suma de los cuadrados de desviación de los puntos de datos de la línea.

Métodos para usar la regresión lineal en Excel

Este ejemplo le enseña los métodos para realizar el Análisis de regresión lineal en Excel. Veamos algunos métodos.

Puede descargar esta plantilla de Excel de regresión lineal aquí - Plantilla de Excel de regresión lineal

Método # 1 - Gráfico de dispersión con una línea de tendencia

Digamos que tenemos un conjunto de datos de algunas personas con su edad, índice de masa biológica (IMC) y la cantidad que gastaron en gastos médicos en un mes. Ahora, con una idea de las características de los individuos como la edad y el IMC, deseamos encontrar cómo estas variables afectan los gastos médicos y, por lo tanto, usarlos para llevar a cabo la regresión y estimar / predecir los gastos médicos promedio para algunos individuos específicos. Veamos primero cómo solo la edad afecta los gastos médicos. Veamos el conjunto de datos:

Cantidad de gastos médicos = b * edad + a

  • Seleccione las dos columnas del conjunto de datos (x e y), incluidos los encabezados.

  • Haga clic en 'Insertar' y expanda el menú desplegable de 'Gráfico de dispersión' y seleccione la miniatura 'Dispersión' (primera)

  • Ahora aparecerá un diagrama de dispersión y dibujaremos la línea de regresión sobre esto. Para hacer esto, haga clic derecho en cualquier punto de datos y seleccione 'Agregar línea de tendencia'

  • Ahora en el panel 'Formatear línea de tendencia' a la derecha, seleccione 'Línea de tendencia lineal' y 'Mostrar ecuación en gráfico'.

  • Seleccione 'Mostrar ecuación en gráfico'.

Podemos improvisar el gráfico según nuestros requisitos, como agregar títulos de ejes, cambiar la escala, el color y el tipo de línea.

Después de improvisar el gráfico, este es el resultado que obtenemos.

Nota: En este tipo de gráfico de regresión, la variable dependiente siempre debe estar en el eje y, e independiente del eje x. Si el gráfico se traza en orden inverso, cambie los ejes en un gráfico o cambie las columnas en el conjunto de datos.

Método n. ° 2: Método de complemento Analysis ToolPak

Analysis ToolPak a veces no está habilitado de forma predeterminada y debemos hacerlo manualmente. Para hacerlo:

  • Haga clic en el menú 'Archivo'.

Después de eso, haga clic en 'Opciones'.

  • Seleccione 'Complementos de Excel' en el cuadro 'Administrar' y haga clic en 'Ir'

  • Seleccione 'Analysis ToolPak' -> 'Aceptar'

Esto agregará herramientas de 'Análisis de datos' a la pestaña 'Datos'. Ahora ejecutamos el análisis de regresión:

  • Haga clic en 'Análisis de datos' en la pestaña 'Datos'

  • Seleccione 'Regresión' -> 'Aceptar'

  • Aparecerá un cuadro de diálogo de regresión. Seleccione el rango de entrada Y y el rango de entrada X (gastos médicos y edad, respectivamente). En caso de regresión lineal múltiple, podemos seleccionar más columnas de variables independientes (como si también deseáramos ver el impacto del IMC en los gastos médicos).
  • Marque la casilla 'Etiquetas' para incluir encabezados.
  • Elija la opción de 'salida' deseada.
  • Seleccione la casilla de verificación 'residuales' y haga clic en 'Aceptar'.

Ahora nuestro resultado del análisis de regresión se creará en una nueva hoja de trabajo, indicando las Estadísticas de regresión, ANOVA, los residuos y los coeficientes.

Interpretación de salida:

  • Las estadísticas de regresión indican qué tan bien se ajusta la ecuación de regresión a los datos:

  • R múltiple es el coeficiente de correlación que mide la fuerza de la relación lineal entre dos variables. Se encuentra entre -1 y 1, y su valor absoluto representa la fuerza de la relación con un valor grande que indica una relación más fuerte, un valor bajo que indica un valor negativo y un valor cero que indica que no hay relación.
  • R Square es el coeficiente de determinación utilizado como indicador de bondad de ajuste. Se encuentra entre 0 y 1, con un valor cercano a 1 que indica que el modelo se ajusta bien. En este caso, 0.57 = 57% de los valores de y se explican por los valores de x.
  • R Square ajustado es R Square ajustado por el número de predictores en caso de regresión lineal múltiple.
  • Error estándar representa la precisión del análisis de regresión.
  • Observaciones representa el número de observaciones del modelo.
  • Anova indica el nivel de variabilidad dentro del modelo de regresión.

Esto generalmente no se usa para la regresión lineal simple. Sin embargo, los 'valores de significancia F' indican cuán confiables son nuestros resultados, con un valor superior a 0.05 que sugiere elegir otro predictor.

  • Los coeficientes son la parte más importante utilizada para construir la ecuación de regresión.

Entonces, nuestra ecuación de regresión sería: y = 16.891 x - 355.32. Es lo mismo que el método 1 (gráfico de dispersión con una línea de tendencia).

Ahora, si deseamos predecir los gastos médicos promedio cuando la edad es 72:

Entonces y = 16.891 * 72 -355.32 = 860.832

De esta manera podemos predecir los valores de y para cualquier otro valor de x.

  • Los residuos indican la diferencia entre los valores reales y los pronosticados.

El último método para la regresión no se usa con tanta frecuencia y requiere funciones estadísticas como pendiente (), intercepción (), correl (), etc. para llevar a cabo el análisis de regresión.

Cosas para recordar sobre la regresión lineal en Excel

  • El análisis de regresión generalmente se usa para ver si existe una relación estadísticamente significativa entre dos conjuntos de variables.
  • Se utiliza para predecir el valor de la variable dependiente en función de los valores de una o más variables independientes.
  • Siempre que deseamos ajustar un modelo de regresión lineal a un grupo de datos, entonces el rango de datos debe observarse cuidadosamente como si usáramos una ecuación de regresión para predecir cualquier valor fuera de este rango (extrapolación), entonces puede dar lugar a resultados incorrectos.

Artículos recomendados

Esta es una guía para la regresión lineal en Excel. Aquí discutimos cómo hacer Regresión lineal en Excel junto con ejemplos prácticos y plantilla de Excel descargable. También puede consultar nuestros otros artículos sugeridos:

  1. ¿Cómo preparar la nómina en Excel?
  2. Uso de la fórmula MAX en Excel
  3. Tutoriales sobre referencias de celdas en Excel
  4. Crear análisis de regresión en Excel
  5. Programación lineal en Excel

Categoría:

Consejos de Excel