Ejemplo Regresión lineal múltiple (Interpretación de Ayuda de Excel) Suponga que un programador comercial está pensando en adquirir un grupo de pequeños edificios de oficinas en un distrito comercial conocido. El programador puede utilizar el análisis de regresión lineal múltiple para estimar el valor de un edificio de oficinas en un área determinada basándose en las variables siguientes. Variable y x1 x2 x3 x4
Indica Valor tasado del edificio oficinas Superficie en metros cuadrados Número de oficinas Número de entradas Antigüedad del edificio en años
Este ejemplo supone que existe una relación de línea recta entre c/variable independiente (x1, x2, x3, y x4) y la variable dependiente (y), el valor de los edificios de oficinas en esa área. El programador elige al azar una muestra de 11 edificios de oficinas de 1.500 edificios posibles y obtiene los datos siguientes. "Media entrada" significa una entrada sólo para entregas. superficie (x1)
Nº oficinas
Nº entradas
(x2)
2310 2333 2356 2379 2402 2425 2448 2471 2494 2517 2540
2 2 3 3 2 4 2 2 3 4 2
(x3)
antigüedad (x4)
valor (y)
2 2 1,5 2 3 2 1,5 2 3 4 3
20 12 33 43 53 23 99 34 23 55 22
142.000 144.000 151.000 150.000 139.000 169.000 126.000 142.900 163.000 169.000 149.000
Ecuación de Regresión Múltiple: Y = m1x1 + m2x2 + m3x3 + m4x4 + b
Calculamos sus valores usando la función Estimación Lineal, de excel, y metiendo los datos como una matriz, es decir: "conocido_Y" ….toda la columna (y) "conocido_X" ….las cuatro (4) columnas (x) "Constante"…….VERDADERO. Esto es para forzar el cálculo de "b" (que no sea "0") "Estadística"…...VERDADERO. Esto es para que devuelva estadísticas adicionales Una vez que tenemos la función con sus valores, señalamos, a partir de la celda que contiene la función, un área de 5 filas y 5 columnas (porque hay 5 variables), pulsamos F2 (para editar la función) y luego, Ctrl + Shift + Enter (para copiarse en todas
las
celdas
restantes. Lo que obtenemos tiene la siguiente interpretación: m4
m3
m2
m1
b
se4
se3
se2
se1
seb
r2
sey
F
df
ssreg
ssresid
m4 m3 m2 m1 b
<==
fila regresión (coeficientes y cte.)
se4 se3 se2 se1 seb
<==
standard error de cada coeficiente
r2 sey
coeficiente de determinación y standard error de la
<==
estimación Y.
F df
<==
Estadística F y grados de libertad.
ssreg ssresid
<==
Sumatoria de la regresión de los cuadrados y residual de los cuadrados.
Significado de cada elemento de la matriz: m1, m2, m3, m4 b
coeficientes de las variables ó pendiente de la recta constante ó pto. de intersección de la recta con el eje y.
se1, se2, se3, se4 standard error (error típico) de cada coeficiente. r2
El coeficiente de determinación. Compara los valores “y” estimados y reales, y los rangos con valor de 0 a 1. Si es 1, hay una correlación perfecta en la muestra, es decir, no hay diferencia entre el valor y estimado y el valor y real. En el otro extremo, si el coeficiente de determinación es 0, la ecuación de regresión no es útil para predecir un valor y.
sey
El error estándar para la estimación y.
F
La estadística F o valor F observado se usa para determinar si la relación observada entre las variables dependientes e independientes se produce por azar.
df
Grados de libertad del denominador (v2 en SNEDECOR). Se usan para encontrar valores F críticos. Se compara el valor que encuentra en la tabla con la estadística F devuelta por Estimación Lineal para determinar un nivel de confianza para el modelo.
ssreg
suma de regresión de los cuadrados.
ssresid
Excel calcula, para cada punto, el cuadrado de la diferencia entre el valor Y estimado para ese punto y su valor Y real. La suma de estas diferencias cuadradas se denomina suma de los cuadrados residual, ssresid.
Función Estimación Lineal (resultados) -234,2371645
2553,21066
12529,76817
27,64138737
52317,83051
13,26801148
530,6691519
400,0668382
5,429374042
12237,3616
0,996747993 459,7536742 1732393319
970,5784629 6 5652135,316
m4 -234,2371645
m3 2553,21066
#N/A #N/A #N/A
#N/A #N/A #N/A
#N/A #N/A #N/A
m2 12529,76817
m1 27,64138737
b 52317,83051
r2 = 0,996747993 indica una relación muy marcada entre las variables independientes y el precio de venta, pero debemos utilizar la estadística F para determinar si estos resultados, con un valor r2 tan alto, se produjeron por azar; los valores F y df en la salida de la ESTIMACION.LINEAL se pueden utilizar para determinar esto. La distribución F apropiada tiene los grados de libertad v1 y v2. Si n es el número de casos, 11 en este ejemplo, y const = VERDADERO o se omite, entonces v1(numerador) = n – df – 1 y v2(denominador) = df. Con un valor alfa de 0,05 (nivel de sifgnificancia 5%), v1 = 11 – 6 – 1 = 4 y v2 = 6, el valor crítico de F es 4,53. Como F = 459,753674 >>> 4,53; es improbable que un valor F tan elevado se produzca por azar; y la hipótesis de que no hay relación entre “conocido_y” y “conocido_x” hay que rechazarla y seguir considerando la marcada relación entre la variable dependiente (y) y las variables independientes deducidas del valor de r 2. Con DISTR.F de Excel se puede obtener la probabilidad de que un valor F tan elevado se produzca por azar. DISTR.F(459,753674. 4. 6) = 1,37E-7, una probabilidad extremadamente pequeña. Se puede concluir, bien buscando el nivel crítico de F en una tabla, o bien utilizando DISTR.F de Excel, que la ecuación de regresión es útil para predecir el valor tasado de los edificios de oficinas de esta área. Solo nos falta determinar si cada coeficiente de la pendiente es útil para estimar el valor tasado. Para probar si el coeficiente de antigüedad tiene significado estadístico, divido -234,24 (coeficiente de la pendiente de antigüedad) entre 13,268 (el error estándar estimado de el coeficiente de antigüedad ) El siguiente es el valor t observado: t = m4 / se4 = -234,24 / 13,268 = -17,7
Si el valor absoluto de t es suficientemente alto, puede deducirse que el coeficiente de la pendiente es útil para calcular el valor tasado del edificio de oficinas del ejemplo. La tabla muestra los valores absolutos de los 4 valores t observados. Variable Superficie Núm. oficinas Núm. entradas Antigüedad
t observado 5,1 31,3 4,8 17,7
Consultando la tabla de Distribución t de Student , observo que el valor t crítico, de dos colas, con 6 grados de libertad y alfa = 0,05 es 2,447. Este valor crítico puede encontrarse también utilizando la función DISTR.T.INV de Excel. DISTR.T.INV (0,05.6) = 2,447. Todos estos valores tienen un valor absoluto superior a 2,447; por tanto, todas las variables utilizadas en la ecuación de regresión son útiles para predecir el valor tasado de los edificios de oficinas de esta área. Ahora puede obtenerse la ecuación de regresión múltiple, y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b. superficie (x1) 2500
Nº oficinas (x2) 3
Nº entradas (x3) 2
antigüedad (x4) 25
valor (y) 158261,10
y = 27,64*2.500 + 12.530*3 + 2.553*2-234,24*25 + 52.318 = 158.26