Crear un modelo de regresión lineal en Excel

La regresión lineal es un gráfico de datos que representa gráficamente la relación lineal entre una variable independiente y una variable dependiente. Por lo general, se usa para mostrar visualmente la fuerza de la relación y la dispersión de los resultados, todo con el propósito de explicar el comportamiento de la variable dependiente.

Digamos que queríamos probar la fuerza de la relación entre la cantidad de helado consumido y la obesidad. Tomaríamos la variable independiente, la cantidad de helado, y la relacionaríamos con la variable dependiente, la obesidad, para ver si había una relación. Dada una regresión es una visualización gráfica de esta relación, cuanto menor es la variabilidad en los datos, más fuerte es la relación y más ajustado el ajuste a la línea de regresión.

Para llevar clave

• La regresión lineal modela la relación entre una variable o variables dependientes e independientes.
• El análisis de regresión se puede lograr si las variables son independientes, no hay heterocedasticidad y los términos de error de las variables no están correlacionados.
• Modelar la regresión lineal en Excel es más fácil con Data Analysis ToolPak.

Consideraciones importantes

Hay algunas suposiciones críticas sobre su conjunto de datos que deben ser ciertas para proceder con un análisis de regresión:

1. Las variables deben ser verdaderamente independientes (utilizando una prueba de Chi-cuadrado).
2. Los datos no deben tener diferentes variaciones de error (esto se llama heteroscedasticidad (también heteroscedasticidad)).
3. Los términos de error de cada variable no deben estar correlacionados. Si no, significa que las variables están correlacionadas en serie.

Si esas tres cosas suenan complicadas, lo son. Pero el efecto de que una de esas consideraciones no sea cierta es una estimación sesgada. Esencialmente, expresarías incorrectamente la relación que estás midiendo.

Salida de una regresión en Excel

El primer paso para ejecutar el análisis de regresión en Excel es verificar que el complemento gratuito de Excel Data Analysis ToolPak esté instalado. Este complemento hace que el cálculo de un rango de estadísticas sea muy fácil. No es necesario trazar una línea de regresión lineal, pero simplifica la creación de tablas estadísticas. Para verificar si está instalado, seleccione "Datos" de la barra de herramientas. Si "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 Office y seleccionando "Opciones de Excel".

Usando Data Analysis ToolPak, crear una salida de regresión es solo unos pocos clics.

La variable independiente va en el rango X.

Dados los retornos del S&P 500, digamos que queremos saber si podemos estimar la fortaleza y la relación de los retornos de las acciones de Visa (V). Los datos de devolución de acciones de Visa (V) llenan la columna 1 como la variable dependiente. S&P 500 devuelve datos y llena la columna 2 como la variable independiente.

1. Seleccione "Datos" de la barra de herramientas. Aparece el menú "Datos".
2. Seleccione "Análisis de datos". Aparece el cuadro de diálogo Análisis de datos - Herramientas de análisis.
3. Desde el menú, seleccione "Regresión" y haga clic en "Aceptar".
4. En el cuadro de diálogo Regresión, haga clic en el cuadro "Introducir rango Y" y seleccione los datos de la variable dependiente (devoluciones de acciones de Visa (V)).
5. Haga clic en el cuadro "Input X Range" y seleccione los datos de la variable independiente (devuelve S&P 500).
6. Haga clic en "Aceptar" para ejecutar los resultados.

[Nota: Si la tabla parece pequeña, haga clic con el botón derecho en la imagen y ábrala en una pestaña nueva para obtener una resolución más alta].

Interpreta los resultados

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 se ajusta el modelo de regresión 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 R ², un valor p más pequeño es favorable, ya que indica una correlación entre las variables dependientes e independientes.

Trazar una regresión en Excel

Podemos trazar una regresión en Excel resaltando los datos y trazándolos como un diagrama de dispersión. Para agregar una línea de regresión, elija "Diseño" en el menú "Herramientas de gráficos". En el cuadro de diálogo, seleccione "Línea de tendencia" y luego "Línea de tendencia lineal". Para agregar el valor R ², seleccione "Más opciones de línea de tendencia" en el menú "Línea de tendencia. Por último, seleccione" Mostrar valor de R cuadrado en el gráfico ". El resultado visual resume la fuerza de la relación, aunque a expensas de no proporcionar tantos detalles como la tabla de arriba.