Cómo crear una recta de regresión en Excel Cómo utilizar la recta de regresión en Excel para predecir valores

El primer paso para ejecutar el análisis de regresión en Excel es verificar que se instale el análisis de datos de datos de complemento de Excel gratuito. Este complemento hace que calcular una variedad de estadísticas sea muy fácil. No es necesario trazar una línea de regresión lineal, pero hace que la creación de tablas de estadísticas sea más simple. Para verificar si está instalado, seleccione «Datos» en la barra de herramientas. Si el «análisis de datos» es una opción, la función está instalada y lista para usar. Si no está instalado, puede solicitar esta opción haciendo clic en el botón de la oficina y seleccionando «Opciones de Excel».

Utilizando el análisis de análisis de datos, crear una salida de regresión son solo unos pocos clics.

La variable independiente en Excel va en el rango X.

Dadas las devoluciones de S&P 500, digamos que queremos saber si podemos estimar la fuerza y ​​la relación de los stockreturns de Visa (V). El stock de Visa (V) devuelve los datos de la columna 1 como la variable dependiente. S&P 500 Devuelve los datos poblados por la columna 2 como la variable independiente.

[Nota: Si la tabla parece pequeña, haga clic con el botón derecho en la imagen y abra en una nueva pestaña para una mayor resolución.]

Usando esos datos (lo mismo de nuestro artículo R-cuadrado), obtenemos la siguiente tabla:

El valor R2, también conocido como coeficiente de determinación, mide la proporción de variación en la variable dependiente explicada por la variable independiente o qué tan bien el modelo de regresión se ajusta a los datos. El valor de R2 varía de 0 a 1, y un valor más alto indica un mejor ajuste. El valor p, o valor de probabilidad, también varía de 0 a 1 e indica si la prueba es significativa. A diferencia del valor R2, un valor p más pequeño es favorable, ya que indica una correlación entre las variables dependientes e independientes.

¿Cómo hallar la recta de regresión en Excel?

Supongamos que tenemos el siguiente conjunto de datos que contiene una variable predictor (x) y una variable de respuesta (y):

Podemos escribir la siguiente fórmula en la celda D1 para calcular la ecuación de regresión lineal simple para este conjunto de datos:

= Linest (A2: A15, B2: B15)

Una vez que presionemos Enter, se mostrarán los coeficientes para el modelo de regresión lineal simple:

  • El coeficiente para la intersección es 3.115589
  • El coeficiente para la pendiente es 0.479072

Usando estos valores, podemos escribir la ecuación para este modelo de regresión simple:

Nota: Para encontrar los valores P para los coeficientes, el valor R cuadrado del modelo y otras métricas, debe usar la función de regresión de la Terramentica de análisis de datos. Este tutorial explica cómo hacerlo.

Supongamos que tenemos el siguiente conjunto de datos que contiene dos variables predictoras (x1 y x2) y una variable de respuesta (y):

Podemos escribir la siguiente fórmula en la celda E1 para calcular la ecuación de regresión lineal múltiple para este conjunto de datos:

= Linest (A2: A15, B2: C15)

Una vez que presionemos Enter, se mostrarán los coeficientes para el modelo de regresión lineal múltiple:

  • El coeficiente para la intersección es 3.115589
  • El coeficiente para la pendiente es 0.479072
  • El coeficiente para la intersección es 1.471205
  • El coeficiente para X1 es 0.047243
  • El coeficiente para x2 es 0.406344
  • Usando estos valores, podemos escribir la ecuación para este modelo de regresión múltiple:

    Nota: Para encontrar los valores P para los coeficientes, el valor R cuadrado del modelo y otras métricas para un modelo de regresión lineal múltiple en Excel, debe usar la función de regresión de la herramienta de análisis de datos. Este tutorial explica cómo hacerlo.

    ¿Cómo se halla la recta de regresión en Excel?

    Las regresiones lineales modelan una relación entre las variables de datos estadísticos dependientes e independientes. En términos más simples, destacan una tendencia entre dos columnas de tabla en una hoja de cálculo. Por ejemplo, si configura una tabla de hoja de cálculo de Excel con una columna de mes x y registra un conjunto de datos para cada uno de los meses en la columna Y adyacente, la regresión lineal resaltará la tendencia entre las variables X e Y agregando líneas de tendencia a la tabla gráficos. Así es como puede agregar regresión lineal a los gráficos de Excel.

    • Primero, abra una hoja de cálculo de Excel en blanco, seleccione Cell D3 e ingrese «Mes» como encabezado de columna, que será la variable X.
    • Luego haga clic en Cell E3 e ingrese el «valor Y» como encabezado de columna de variable y. Esta es básicamente una tabla con una serie registrada de valores de datos para los meses enero-mayo.
    • Ingrese los meses en las celdas D4 a D8 y los valores de datos para ellos en las celdas E4 a E8 como se muestra en la instantánea directamente a continuación.
    • Seleccione todas las celdas de la tabla con el cursor.
    • Haga clic en la pestaña Insertar y seleccione dispersión> dispersión con solo marcadores para agregar el gráfico a la hoja de cálculo como se muestra a continuación. Alternativamente, puede presionar la tecla de acceso rápida Alt + F1 para insertar un gráfico de barras.
    • Luego, debe hacer clic con el botón derecho en el gráfico y seleccionar Cambiar Tipo de gráfico> X Y (dispersión)> Dispertar con solo marcadores.

    A continuación, puede agregar la línea de tendencia a la trama de dispersión

    • Primero, abra una hoja de cálculo de Excel en blanco, seleccione Cell D3 e ingrese «Mes» como encabezado de columna, que será la variable X.
    • Luego haga clic en Cell E3 e ingrese el «valor Y» como encabezado de columna de variable y. Esta es básicamente una tabla con una serie registrada de valores de datos para los meses enero-mayo.
    • Ingrese los meses en las celdas D4 a D8 y los valores de datos para ellos en las celdas E4 a E8 como se muestra en la instantánea directamente a continuación.
    • Seleccione todas las celdas de la tabla con el cursor.
    • Haga clic en la pestaña Insertar y seleccione dispersión> dispersión con solo marcadores para agregar el gráfico a la hoja de cálculo como se muestra a continuación. Alternativamente, puede presionar la tecla de acceso rápida Alt + F1 para insertar un gráfico de barras.
    • Luego, debe hacer clic con el botón derecho en el gráfico y seleccionar Cambiar Tipo de gráfico> X Y (dispersión)> Dispertar con solo marcadores.
  • Seleccione uno de los puntos de datos en la gráfica de dispersión y haga clic con el botón derecho para abrir el menú contextual, que incluye una opción ADD TENDLINE.
  • Seleccione Agregar línea de tendencia para abrir la ventana que se muestra en la instantánea directamente a continuación. Esa ventana tiene cinco pestañas que incluyen varias opciones de formato para líneas de tendencia de regresión lineal.
  • 4. Seleccione Lineal y haga clic en Cerrar para agregar esa línea de tendencia al gráfico como se muestra directamente a continuación.

    ¿Cómo se calcula la recta de regresión?

    Calcular la pendiente de una línea de regresión ayuda a determinar qué tan rápido cambia sus datos. Las líneas de regresión pasan a través de conjuntos lineales de puntos de datos para modelar su patrón matemático. La pendiente de la línea representa el cambio de los datos trazados en el eje Y al cambio de los datos trazados en el eje X. Una pendiente más alta corresponde a una línea con mayor inclinación, mientras que la línea de una pendiente más pequeña es más plana. Una pendiente positiva indica que la línea de regresión aumenta a medida que aumentan los valores del eje y, mientras que una pendiente negativa implica que la línea disminuye a medida que aumentan los valores del eje Y.

    Elija dos puntos que caen en la línea de regresión. Los puntos de datos en el gráfico se escriben como pares ordenados (x, y), donde «x» representa un valor en el eje horizontal y «y» representa un valor en el eje vertical.

    Resta el valor «X» del primer punto del valor «X» del segundo punto para obtener el cambio en «X». Por ejemplo, suponga que los dos puntos (3,6) y (9,15) están en la línea de regresión. Usando este ejemplo, 9 – 3 = 6, que es el cambio calculado en el valor «X».

    Resta el valor «y» del primer punto del valor «y» del segundo punto para calcular el cambio en «y». Continuando con el ejemplo anterior, (3,6) y (9,15) en la línea de regresión, el cambio calculado en el valor «y» es 15 – 6 = 9.

    Divida el cambio en «y» por el cambio en «x» para obtener la pendiente de la línea de regresión. Usando el ejemplo anterior produce 9/6 = 1.5. Tenga en cuenta que la pendiente es positiva, lo que significa que la línea aumenta a medida que aumentan los valores del eje y.

    ¿Qué son las rectas de regresión?

    Una línea de regresión indica una relación lineal entre las variables dependientes en el eje Y y las variables independientes en el eje X. La correlación se establece analizando el patrón de datos formado por las variables.

    La línea de regresión se traza más cerca de los puntos de datos en un gráfico de regresión. Esta herramienta estadística ayuda a analizar el comportamiento de una variable dependiente Y cuando hay un cambio en la variable independiente X, al sustituir diferentes valores de x en la ecuación de regresión.

    • La línea de regresión establece una relación lineal entre dos conjuntos de variables. El cambio en una variable depende de los cambios a la otra (variable independiente).
    • La línea de regresión de mínimos cuadrados (LSRL) se traza más cerca de los puntos de datos (x, y) en un gráfico de regresión.
    • La regresión se usa ampliamente en modelos financieros como CAPM y medidas de inversión como Beta para determinar la viabilidad de un proyecto. También se utiliza para crear proyecciones de inversiones y rendimientos financieros.
    • Si y es la variable dependiente y x es la variable independiente, la ecuación de la línea de regresión y en x se representa de la siguiente manera: «y = a + bx + ɛ».

    Una línea de regresión es una herramienta estadística que representa la correlación entre dos variables. Específicamente, se usa cuando la variación en una (variable dependiente) depende del cambio en el valor del otro (variable independiente).

    • La línea de regresión establece una relación lineal entre dos conjuntos de variables. El cambio en una variable depende de los cambios a la otra (variable independiente).
    • La línea de regresión de mínimos cuadrados (LSRL) se traza más cerca de los puntos de datos (x, y) en un gráfico de regresión.
    • La regresión se usa ampliamente en modelos financieros como CAPM y medidas de inversión como Beta para determinar la viabilidad de un proyecto. También se utiliza para crear proyecciones de inversiones y rendimientos financieros.
    • Si y es la variable dependiente y x es la variable independiente, la ecuación de la línea de regresión y en x se representa de la siguiente manera: «y = a + bx + ɛ».
  • La ecuación es y en x, donde el valor de y cambia con una variación en el valor de X.
  • ¿Qué es la regresión en Excel?

    Excel puede realizar varios análisis estadísticos, incluido el análisis de regresión. Es una gran opción porque casi todos pueden acceder a Excel. Esta publicación es una excelente introducción al análisis de regresión de rendimiento e interpretación, incluso si Excel no es su paquete de software estadístico principal.

    En esta publicación, proporciono instrucciones paso a paso para usar Excel para realizar un análisis de regresión múltiple. Es importante destacar que también le muestro cómo especificar el modelo, elegir las opciones correctas, evaluar el modelo, verificar los supuestos e interpretar los resultados.

    Incluyo enlaces a recursos adicionales que he escrito, que presentan explicaciones claras de los conceptos de análisis de regresión relevantes que no encontrará en la documentación de Excel. ¡Y uso un conjunto de datos de ejemplo para que trabajemos e interpretemos juntos!

    Antes de continuar, asegúrese de que se instale la herramienta de análisis de datos de Excel. En la pestaña de datos, busque análisis de datos, como se muestra a continuación.

    El análisis de regresión describe las relaciones entre un conjunto de variables independientes y la variable dependiente. Produce una ecuación en la que los coeficientes representan la relación entre cada variable independiente y la variable dependiente. También puede usar la ecuación para hacer predicciones. Excel realiza una regresión de mínimos cuadrados ordinarios.

    Para realizar el análisis de regresión en Excel, organice sus datos para que cada variable esté en una columna, como se muestra a continuación. Las variables independientes deben estar una al lado de la otra.

    ¿Cómo se hace un análisis de regresión?

    El análisis de regresión a menudo se usa para modelar o analizar datos. La mayoría de los analistas de encuestas lo usan para comprender la relación entre las variables, que se pueden utilizar aún más para predecir el resultado preciso.

    Por ejemplo, suponga que una empresa de refrescos quiere expandir su unidad de fabricación a una ubicación más nueva. Antes de avanzar; La compañía quiere analizar su modelo de generación de ingresos y los diversos factores que podrían afectarlo. Por lo tanto, la compañía realiza una encuesta en línea con un cuestionario específico.

    Después de usar el análisis de regresión, se vuelve más fácil para la compañía analizar los resultados de la encuesta y comprender la relación entre diferentes variables como la electricidad y los ingresos; aquí los ingresos son la variable dependiente. Además de eso, comprender la relación entre diferentes variables independientes como los precios, el número de trabajadores y la logística con los ingresos ayuda a la compañía a estimar un impacto de los factores variados en las ventas y las ganancias de la empresa.

    Los investigadores de encuestas a menudo usan esta técnica para examinar y encontrar una correlación entre diferentes variables de interés. Proporciona la oportunidad de medir la influencia de diferentes variables independientes en una variable dependiente. En general, el análisis de regresión es una técnica que ahorra esfuerzos adicionales de los investigadores de la encuesta en la organización de numerosas variables independientes en las tablas y pruebas o calculando su efecto en una variable dependiente. Los diferentes tipos de métodos analíticos se utilizan ampliamente para evaluar nuevas ideas de negocios y tomar decisiones informadas.

    ¿Cómo utilizar la regresión lineal simple en Excel?

    ¿Alguna vez te has preguntado cómo funciona la regresión lineal. ¿Siempre has usado la función RERR.Lin, pero nunca has tenido la oportunidad de ir al fondo de cómo funciona? Excelsapeviche le dice una de las funciones estadísticas más famosas y utilizadas de Excel y le muestra los detalles.

    Primero especificamos que la regresión lineal (en dos dimensiones, como en el caso de que lidiaremos con ella, se llama así la línea recta que pasa «lo más cerca posible» en todos los puntos (x, y), que es para todas las observaciones. Y la línea recta se describe en dos dimensiones por la función lineal y = βx+α.

    Comencemos por ver cómo se compone y qué función nativa para la regresión de regresión lineal se usa Rem.lin of Excel. Supongamos que tenemos la edad variable independiente y la variable promedio dependiente del salario, es decir, la que queremos estimar el valor

    Mostramos a continuación la distribución de las parejas ordenadas (edad, salario promedio) en los gráficos cartesianos

    La función estadística para la regresión lineal regresa estima a través del método de cuadrados mínimos de los parámetros α y β que sirven para definir de manera única la famosa tarifa de regresión, también llamada el cuadrado mínimo. En las estadísticas, α se llama intercepción y de alguna manera indica la altura de la línea recta, mientras que β representa su pendiente. Los temas de la función Rem.lin son los siguientes:

    • Y_nota: es obligatorio y es el conjunto de valores observados de la variable dependiente, en nuestro caso los valores salariales promedio

    ¿Cómo utilizar la regresion lineal simple en Excel?

    La regresión lineal simple es un método que podemos usar para comprender la relación entre una variable explicativa, X y una variable de respuesta, y.

    Este tutorial explica cómo realizar una regresión lineal simple en Excel.

    Supongamos que estamos interesados ​​en comprender la relación entre la cantidad de horas de estudios de estudiantes para un examen y el puntaje del examen que reciben.

    Para explorar esta relación, podemos realizar una regresión lineal simple utilizando horas estudiadas como una variable explicativa y una puntuación del examen como una variable de respuesta.

    Realice los siguientes pasos en Excel para realizar una regresión lineal simple.

    Ingrese los siguientes datos para el número de horas estudiadas y el puntaje del examen recibido para 20 estudiantes:

    Antes de realizar una regresión lineal simple, es útil crear un diagrama de dispersión de los datos para asegurarse de que realmente exista una relación lineal entre las horas estudiadas y la puntuación del examen.

    Resalte los datos en las columnas A y B. A lo largo de la cinta superior en Excel, vaya a la pestaña Insertar. Dentro del grupo de gráficos, haga clic en Insertar dispersión (x, y) y haga clic en la primera opción titulada Scatter. Esto producirá automáticamente el siguiente plan de dispersión:

    El número de horas estudiadas se muestra en el eje X y los puntajes del examen se muestran en el eje y. Podemos ver que existe una relación lineal entre las dos variables: más horas estudiadas se asocian con puntajes de exámenes más altos.

    Para cuantificar la relación entre estas dos variables, podemos realizar una regresión lineal simple.

    ¿Cómo poner la regresión lineal en Excel?

    Supongamos que aún no has aprendido todo sobre Excel. El primer paso es crear una trama de dispersión. Entonces podemos crear la línea de tendencia. Luego podemos hacer algunas cosas ordenadas con la línea de tendencia y ver lo que significa.

    • Cree 2 columnas de datos en Excel. Nuestro ejemplo tendrá tiempo en años y valor de acciones en dólares. Puede copiar y pegar los datos en Excel para que pueda jugar. Luego, en Excel, seleccione ambas columnas de datos seleccionando y manteniendo presionado el número superior izquierdo y arrastrándose hacia abajo al número más inferior en la columna derecha.
    • Seleccione Insertar en la barra de herramientas principal.
    • Busque el icono de un gráfico con solo puntos. Seleccione la flecha hacia abajo al lado.
    • Seleccione el primer gráfico de dispersión con solo puntos y sin líneas.
    • Una vez que se construye el gráfico y ha personalizado el gráfico de Excel para que se vea como lo desea, haga clic con el botón derecho en un solo punto de datos. Esto puede ser un poco complicado, así que sigue intentando si no lo consigue en el primer intento. Cuando lo hagas, se abrirá un submenú.
    • Seleccione Agregar línea de tendencia.
    • El menú de línea de tendencia de formato se abrirá a la derecha. La opción de línea de tendencia lineal ya se seleccionará. Deja eso como es. Siéntase libre de trabajar con el formato de Excel para que la línea se vea bien. Ahora tiene una línea de tendencia de regresión lineal que le muestra el crecimiento general del valor de las acciones durante 20 años.
    • ¿Quieres saber qué valdrá en 2030? Puede tener una idea aproximada agregando 10 períodos, o años, a la línea de tendencia en el campo de pronóstico. Mostrará que el punto en el círculo verde lo valorará en algún lugar alrededor de $ 2500.
    • Si desea que su gráfico haga que se vea inteligente, desplácese hacia abajo en el menú de la línea de tendencia de formato y verifique la ecuación de visualización en el gráfico y visite el valor R cuadrado en la tabla. Verás algo como lo que hay en la caja roja a continuación aparece en tu gráfico.

    Estos son útiles para tener. El valor R-cuadrado le dice cuán bueno es el ajuste de la línea de tendencia. Aunque un valor R cuadrado por encima de 0.8 es ideal, 0.69 no es malo. Piense en ello, ya que tiene un 69% seguro de que esta línea le dará una buena idea de cómo este stock tiende a funcionar.

    La ecuación le facilita hacer cálculos rápidos para determinar cuál es el valor de la línea de tendencia en cualquier momento. Incluso antes de que comience la línea y después de que termine. ¿Qué podría valer la acción en 2030? Vamos a conectarlo a la ecuación.

    Sí, si la tendencia se mantiene, el valor de la acción tiene una posibilidad decente de valer $ 3214.04 en 2030.

    Le hemos mostrado un ejemplo de cómo una línea de tendencia de regresión lineal en Excel podría ayudarlo a tomar una decisión financiera. Pero, ¿qué otras formas podrías usarlo? ¿Se te ocurren ideas? Haznos saber.

    ¿Cómo hacer un modelo de regresión lineal multiple en Excel?

    Definición 1: Utilizamos la misma terminología que en la definición 3 de análisis de regresión, excepto que los grados de libertad dfres y dfregare modificados para tener en cuenta el número K de variables independientes.

    Los valores predichos de y, es decir, el hat y, se pueden calcular usando la fórmula de matriz

    El error estándar de cada uno de los coeficientes en B se puede calcular de la siguiente manera. Primero calcule la matriz de términos de error E (rango O4: O14) usando la fórmula de matriz I4: I14 – M4: M14. Entonces, al igual que en el caso de regresión simple ssres = devsq (o4: o14) = 277.36, dfres = n – k – 1 = 11 – 2 – 1 = 8 y msres = ssres/dfres = 34.67 (ver análisis de regresión múltiple para obtener más detalles ).

    Según la observación siguiente, la propiedad 4 se deduce que MSRES (XTX) -1 es la matriz de covarianza para los coeficientes, por lo que la raíz cuadrada de los términos diagonales es el error estándar de los coeficientes. En particular, el error estándar de la intersección B0 (en la celda K9) se expresa mediante la fórmula = SQRT (i17), el error estándar del coeficiente de color B1 (en la celda K10) se expresa mediante la fórmula = SQRT (J18), y el error estándar del coeficiente de calidad B2 (en la celda K11) se expresa mediante la fórmula = SQRT (K19).

    Funciones de Excel: la pendiente de las funciones, la intercepción, el Steyx y el pronóstico no funcionan para la regresión múltiple, pero las funciones tenden tendencia y Linest admiten regresión múltiple al igual que la herramienta de análisis de datos de regresión.

    La tendencia funciona exactamente como se describe en el método de mínimos cuadrados, excepto que el segundo parámetro R2 ahora contendrá datos para todas las variables independientes.

    Artículos Relacionados:

    Más posts relacionados:

    Deja una respuesta

    Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *