Regresión Lineal Con Excel
Regresión Lineal Con Excel
Regresión Lineal Con Excel
universitarios.
a) Elabore un diagrama de dispersión.
b) Calcule la ecuación de la recta de regresión para pronosticar las ventas trimestrales a partir de la población
c) Estime las ventas semanales si los costos de publicidad son de $35.
d) Grafique los residuales en comparación con los costos de publicidad. Comente sus resultados.
1 2 58
2 6 105
3 8 88
4 8 118
5 12 117
6 16 137
7 20 157
8 20 169
9 22 149
10 26 202
Entrada:
En el Rango Y de entrada, se coloca los datos de la variable Y con el título (rótulo); se coloca el cursor en la
columna D y fila 4 y se arrastra hasta la columna D y fila 14.
En el Rango X de entrada, se coloca los datos de la variable X con el título (rótulo); se coloca el cursor en la
columna C y fila 4 y se arrastra hasta la columna C y fila 14.
Se marca rótulo.
Opciones de salida:
Residuales:
Probabilidad normal:
Resumen
Estadísticas de la regresión
Coeficiente de correlación múltiple 0,95012296
Coeficiente de determinación R^2 0,90273363
R^2 ajustado 0,89057533
Error típico 13,8293167
Observaciones 10
Probabilidad
Coeficientes Error típico Estadístico t (p-valor) Inferior 95% Superior 95%
Intercepción 60 9,2260348 6,5033355 0,0001874 38,7247256 81,2752744
(X) Población
estudiantes
*1000 5 0,5802652 8,6167492 0,0000255 3,6619060 6,3380940
ANÁLISIS DE VARIANZA
Grados de Suma de Promedio de Valor crítico
libertad cuadrados los cuadrados F de F
Regresión 1 14200 14200 74,248366 2,5489E-05
Residuos 8 1530 191,25
Total 9 15730
Interpretación de los datos de la tabla Estadística de la regresión
Resumen
Estadísticas de la regresión
Coeficiente de correlación múltiple 0,95012296
Coeficiente de determinación R^2 0,90273363
R^2 ajustado 0,89057533
Error típico 13,8293167
Observaciones 10
Coeficiente de regresión → 𝑹 = 0,95012296, indica que existe muy alta asociación entre las ventas trimestrales
y la población estudiantil.
Coeficiente de determinación 𝑹𝟐 mide la bondad del ajuste de la recta de regresión a la nube de puntos, el rango
de valores es de 0 a 1. Valores pequeños de 𝑹𝟐 indican que el modelo no se ajusta bien a los datos. El valor de
𝑹𝟐 = 0,90273363, indica que el 90,27% de la variabilidad en las ventas trimestrales se explica por la relación
lineal que existe con el tamaño de la población estudiantil.
𝑛
1 𝑆𝐶𝐸 1533
𝜎̂ = 𝑆𝑒 = √ ∑(𝑦𝑖 − 𝑦̂𝑖 )2 = √ =√ = √191,25 = 13,8293
𝑛−2 𝑛−2 10 − 2
𝑖
ANÁLISIS DE VARIANZA
Grados de Suma de Promedio de Valor crítico
libertad cuadrados los cuadrados F de F
Regresión 1 14200 14200 74,248366 2,5489E-05
Residuos 8 1530 191,25
Total 9 15730
En la Tabla ANOVA, se muestra la descomposición de la Variabilidad Total (SCT = 15730) en la Variabilidad debida
a la Regresión (SCR = 14200) y la Variabilidad Residual (SCE = 1530) es decir en Variabilidad explicada por el modelo
de regresión y la Variabilidad no explicada. SCT = SCR + SCE. La Tabla de Análisis de la Varianza (Tabla ANOVA) se
construye a partir de esta descomposición y proporciona el valor del estadístico F que permite contrastar la
hipótesis nula de que la pendiente de la recta de regresión es igual a cero contra la alternativa de que la pendiente
es distinta de cero, es decir:
𝐻0 : 𝛽1 = 0
{
𝐻1 : 𝛽1 ≠ 0
donde 𝐻0 se conoce, en general, como hipótesis de no linealidad entre la población estudiantil (X) y las ventas
trimestrales (Y).
La Tabla ANOVA muestra el valor del estadístico de contraste, F = 74,24, que se define como el cociente entre el
Cuadrado medio debido a la regresión (CMR = 14200) y el Cuadrado medio residual (CME = 191.25), por tanto,
cuanto mayor sea su valor, mejor será la predicción mediante el modelo lineal. El p-valor asociado a F, en la
columna valor crítico de F, es menor que 0.001, menor que el nivel de significación α = 0.05, lo que conduce a
rechazar la hipótesis nula, es decir existe una relación lineal significativa entre las ventas trimestrales y la población
estudiantil. Esto indica que es válido el modelo de regresión considerado, en este caso el modelo lineal simple.
DISTR.F.CD(valor Fisher; grado de libertad de la regresión ; grado de libertad del error) =DISTR.F.CD(74,248;1;8)
grado de libertad de la regresión = 1 grado de libertad del error = n - 2 = 10 - 2 = 8 valor Fisher =74,248
𝛽0 = 60, indica que las ventas trimestrales son de 60000 $ cuando la población estudiantil es cero.
𝛽1 = 5, indica que las ventas trimestrales aumentan 5000 cuando la población estudiantil aumenta en 1000
estudiantes. Esto significa que hay una relación positiva en ambas variables, a mayor población, mayores ventas
trimestrales.
La tabla anterior presenta los resultados de los dos contrastes individuales de la significación de los parámetros
de 𝛽0 y 𝛽1
𝐻 : 𝛽 =0 𝐻0 : 𝛽1 = 0
{ 0 0 {
𝐻1 : 𝛽0 ≠ 0 𝐻1 : 𝛽1 ≠ 0
El primero de estos contrastes carece de interés en la mayoría de los casos ya que raramente el punto de corte de
la recta de regresión con el eje de ordenadas (ordenada en el origen) será el punto (0,0). Además, dicho punto de
corte carece de significado casi siempre. En nuestro caso, la interpretación de 𝛽0 indica el valor de Y que
correspondería a un valor de X igual a 0.
(buscando en la tabla t-Student). Es decir, con 0,05⁄2 = 0,025 y 8 grados de libertad se va a la tabla de
la distribución t-Student y su valor es 2,306. Si se desea buscar este valor con Excel, se realiza lo siguiente:
DISTR.T.2CD(nivel de significación; grado de libertad) =DISTR.T.2CD(0,05;8) = 2,30600414 este es el valor
que da con Excel.
Probabilidad
Coeficientes Error típico Estadístico t (p-valor) Inferior 95% Superior 95%
Intercepción 60 9,2260348 6,5033355 0,0001874 38,7247256 81,2752744
(X) Población
estudiantes
*1000 5 0,5802652 8,6167492 0,0000255 3,6619060 6,3380940
El intervalo para 𝛽0 es (38,7247; 81,2753), puesto que el cero no pertenece al intervalo, hay evidencia
empírica para concluir que 𝛽0 ≠ 0, es decir, la ecuación de regresión no pasa por el origen (punto (0, 0)).
38,724 ≤ 𝛽0 ≤ 81,275
3,661 ≤ 𝛽1 ≤ 6,338
Intervalo de confianza para pendiente 𝜷𝟏 y la ordenada al origen 𝜷𝟎
Bajo la hipótesis de que las observaciones están distribuidas de manera normal e independiente, el intervalo de
confianza para 𝛽1 de 100(1- α) por ciento en una regresión lineal simples es
𝜎̂ 2 𝜎̂ 2
𝛽̂1 − 𝑡(𝛼⁄ √ < 𝛽1 < 𝛽̂1 + 𝑡(𝛼⁄ √
2,𝑛−2) 𝑆𝑥𝑥 2,𝑛−2) 𝑆𝑥𝑥
Con 𝛼⁄2 = 0,025 y 8 grados de libertad se va a la tabla de la distribución t-Student y su valor es 2,306.
1 𝑥̅ 2 1 𝑥̅ 2
𝛽̂0 − 𝑡(𝛼⁄ √𝜎̂ 2 [ + ] < 𝛽0 < 𝛽̂0 + 𝑡(𝛼⁄ √𝜎̂ 2 [ + ]
2,𝑛−2) 𝑛 𝑆𝑥𝑦 2,𝑛−2) 𝑛 𝑆𝑥𝑦
Diagrama de dispersión
200
150
100
50
0
0 5 10 15 20 25 30
Población estudiantil *1000
Diagrama de dispersión
200
150
100
y = 5x + 60
50 R² = 0,9027
0
0 5 10 15 20 25 30
Población estudiantil *1000
200
150
100
50
0
0 5 10 15 20 25 30
Población estudiantil * 1000
250
Ventas trimestrales *1000
200
150
y = 5x + 60
100 R² = 0,9027
50
0
0 5 10 15 20 25 30
Población estudiantil * 1000