La Regresion Multiple con MS-Excel

COLEGIO DE INGENIEROS DE VENEZUELA

SOCIEDAD DE INGENIERIA DE TASACION DE VENEZUELA

Apéndice 1

Aplicación de la Hoja de Cálculo MS-Excel a problemas

de Regresión Lineal Múltiple

Ing. Roberto Piol Puppio

E-Mail: rpiol@yahoo.com

www.rpiol.c

REGRESION MULTIPLE LINEAL CON EXCEL 6.0 (o superior)

Instrucciones:

a) Escoger en

b) En la Caja

c) En la Caja

d) Indicar en l

e) Indicar en l

f) Marcar con

g) Iluminar co

h) Apretar sim

(*) el Rango de Salida tiene en tamaño de: 5 líneas X el # de Variables de columnas

Galpones Industriales en la Urbanización San Martín. Caracas

1.- Enterar en la Hoja de Cálculo los referenciales seleccionados

Nota: Cersiórese que todas las Variables Independientes estén

agrupadas en columnas adyacentes

$C:\Archivos de Scaner\Galpon\Uno.jpg$

2.- Click en fx: Mostrará una caja de diálogo con Dos Columnas

$C:\Archivos de Scaner\Galpon\Dos.jpg$

3.- Click en Estadísticas (Columna izquierda), e inmeditamente Click en

ESTIMACION.LINEAL (Columna derecha). La Subrutina lo enviará la caja

de diálogo donde:

a) Deberá iluminar con el ratón la columna de la Variable Independiente (Y)

b) Deberá ilum

c) Escribir en l

d) Escribir en l

e) Click en el boton ACEPTAR; esto lo devolverá a la Hoja de Cálculo

$C:\Archivos de Scaner\Galpon\Tres.jpg$

4.- Partiendo de la celda donde Excel indico la salida numérica de la Subrutina

ESTIMACION.LINEAL (0.99704807):

a) Iluminar con el ratón el RANGO DE SALIDA de los elementos de la Regresión

Nota: El rango de salida, tiene un formato específico que es el de Cinc

líneas de alto por "n" columnas de ancho, donde "n" es igual al númer

Variables (Dependientes e Independientes) que tengan los datos que v

a modelar. En el caso de este ejemplo, el rango de salida sera de Cinc

líneas de alto por Cinco (5) columnas de ancho.

b) Iluminar co

la regresión)

c) Apretar sim

en español "C

d) Esta acción

descomprima permitiendo ver los diferentes elementos de la regresión.

5.- La Salida de la Subrutina ESTIMACION.LINEAL, es presentada

por EXCEL de la siguiente forma:

$C:\Archivos de Scaner\Galpon\Cinco.jpg$

6.- La interpretación de esta Salida es la siguiente:

Se4

Se3

Se2

Se1

Se0

R^2

SeY

SCR

SCE

Donde:

Coeficiente del término independiente

Coeficiente que acompaña a X1 (Area)

Coeficiente que acompaña a X2 (Nro. De Oficinas)

Coeficiente que acompaña a X3 (Nro. De Baños)

Coeficiente que acompaña a X4 (Edad del Galpón)

Se0:

Error estándar de la constante A

Se1 al Se4:

Error estándar

SeY:

Error estándar de la correlación

R^2:

Coeficiente de Determinación

Estadístico F

df:

Grados de libertad [ n - (k+1) ]

SCR:

Suma del cuadrado de la regresión

SCE:

Suma del cuadrado del error

7.- El Resultado de la Regresión Lineal Múltiple será:

Modelo de Regresión Lineal: Y = A + B*X1 + C*X2 + D*X3 + E*X4

Y = 87540.5

R^2 = 94.68%

F= 26.71

Fo= 4.53

REGRESION MULTIPLE LOGARITMICA CON EXCEL 6.0 (o superior)

La Subrutina para el Cálculo de la Regresión Múltiple Logarítmica de Excel

es similar al la Lineal, ya estudiada. El comando para activarla es :

ESTIMACION.LOGARITMICA.

1.- Se expondrá brevemente las Instrucciones:

Instrucciones:

a) Escoger en

b) En la Caja

c) En la Caja

d) Indicar en l

e) Indicar en l

f) Marcar con

g) Iluminar co

h) Apretar sim

(*) el Rango de Salida tiene en tamaño de: 5 líneas X el # de Variables de columnas

a) Escoger en fx la función: ESTIMACION.LOGARITMICA

$C:\Archivos de Scaner\Galpon\Nueve.jpg$

b) En la Caja de diálogo marcar la Columna de la Variable Dependiente (y) con el ratón

c) En la Caja de diálogo marcas las Columnas de las Variables Independientes (x) con el ratón

d) Indicar en la ventanilla "CONSTANTE" el argumento: VERDADERO

e) Indicar en la ventanilla "ESTADISTICA" el argumento: VERDADERO

$C:\Archivos de Scaner\Galpon\Diez.jpg$

f) Marcar con el Ratón el Rango de Salida de los elementos de la Regresión

g) Iluminar con el ratón la "Barra de Fórmulas", ventanilla donde aparece la fórmula de la regresión

h) Apretar simultáneamente las teclas: "CONTROL", "SHIFT" y "ENTER"

$C:\Archivos de Scaner\Galpon\Once.jpg$

Nota: el Rango de Salida tiene en tamaño de: 5 líneas por el # de Variables de columnas

3.- El Resultado de la Regresión Logarítmica Múltiple sera:

4.- ¿Que Modelo utilizar Lineal o Logarítmico?

El modelo que mejor explica el Fenómeno: "Precios Unitarios de Galpones Industriales

en la Urbanización Industrial San Martín. Caracas"; sera aque cuyo Coeficiente de

Determinación R^2 sea Mayor.

CONCLUSION: En este caso, el Modelo LINEAL explica mejor que el EXPONENCIAL

CALCULO DE LA MATRIZ DE CORRELACION CON EXCEL 6.0 (o superior)

Para calcular la Matriz de Correlación, utilizando las Funciones (fx) Estadísticas

de la hoja de cálculo Excel; hay que proceder de manera manual. La Matriz de

Correlación debe ser calculada factor por factor (procedimiento algo engorroso)

1.- Se expondrán brevemente las instrucciones:

Instrucciones:

a) Crear en la

b) Utilizando l

X1 - X2

X1 - X3

X1 - X4

X2 - X3

X2 - X4

X3 - X4

c) Complatar la Matriz

2.- Crear en la hoja de cálculo, en forma manual, el esqueleto de la matriz

de correlación:

CTE.

3.- Crear la

CTE.

1,00

-o-

1,00

-o-

1,00

-o-

1,00

-o-

1,00

4.- Click en fx: Mostrará una caja de diálogo con Dos Columnas

seleccionar en la columna de la izquierda: "Estadísticas" y en la

columna de la derecha: "COEF.DE.CORREL."

$C:\Archivos de Scaner\Galpon\Seis.jpg$

5.- Dentro del "esqueleto" de la Matriz, ubicarse el la intersección de

la Variable Independientes X1-X2. Click en fx, se abrirá la caja de diálogo

con dos ventanillas (Matriz 1 y Matriz 2).

$C:\Archivos de Scaner\Galpon\Siete.jpg$

6). Ilumine con el ratón la variable idependiente X1 para una de las

ventanillas y a la variable independiente X2 para la otra.

Click en Aceptar. Esta operación generó el Coeficiente de Correlación

entre las Variables independientes X1 y X2

CTE.

1,00

-o-

1,00

-o-

0,22

1,00

-o-

1,00

-o-

1,00

7.- Utilizando la funcion (fx) / "COEF.DE.CORREL", generar los

Cinco (5) Coeficientes de correlación faltantes para los pares:

X1 - X3

X1 - X4

X2 - X3

X2 - X4

X3 - X4

y completar la Matriz de Correlación.

$C:\Archivos de Scaner\Galpon\Ocho.jpg$

Observese, que no hay problemas de Multicolinealida entre la Variables Independientes

DISTRIBUCION NORMAL ESTANDAR INVERSA (IDF)

Para calcular el coeficiente "k", que genera la Distibución Normal Estandar Inversa:

1.- Escoger en fx la función: DISTR.NORM.ESTAND.INV (IDF en Inglés) de las funciones

estadísticas de la Hoja de Cálculo Excel.

2.- Ingresar en la ventanilla de la caja de diálogo presenteda por Excel, la

Probabilidad "p", bien como número, fracción o indicando la celda donde ya se

haya calculado

3.- Click en "Aceptar", generará la salida de esta subrutina que será el valor del

coeficiente "k"

NOTA IMPORTANTE:

Nota: Para aquellos usuarios de la versión "Professional" de Excel; tienen la oportunidad

de instalar el c

con los coman

Herramientas

Análisis de datos

Coeficiente de Correlación

una vez abiert

(sub-caja: "Ra

la salida de la

Esta macro, e

muchas variables independientes.

Ejemplo completo de Aplicación:

Sea la siguiente serie correspondientes a apartamentos en una Urbanización del suroeste de Caracas:

REFER.

P.UNIT

AREA

EDAD

% DE

Bs.F./M2

AÑOS

CONDOMINIO

1.245

0,942%

987

100

0,515%

1.195

0,363%

1.500

100

1,000%

975

100

0,561%

705

125

0,874%

872

110

0,623%

1.007

0,409%

1.450

0,201%

1.332

0,277%

1.082

0,414%

750

0,836%

LINEAL

-25305,4062

-25,9970171

2,264762828

1250,7204

29889,83393

19,56592643

5,694049533

395,98012

0,284908723

256,0419548

#N/A

1,062461001

#N/A

208956,8058

524459,8609

#N/A

LOGARITMICA

3,49749E-13

0,975890266

1,002286345

1252,68

27,7506444

0,01816561

0,005286531

0,3676402

0,304787497

0,237717254

#N/A

1,169090969

#N/A

0,198194213

0,452075942

#N/A

El Modelo Logarítmico, explica mejor el fenómeno

MATRIZ DE CORRELACION:

AREA

EDAD

CONDOM

AREA

EDAD

0,725799624

CONDOM

0,23161363

0,039900051

No hay probl

CALCULO DE LOS VALORES ATIPICOS (Método Empírico)

REF

Y observ.

Y calc.

Residuo

1.245

1.107

138

987

1.145

-158

1.195

1.259

-64

1.500

972

528

ATIPICO

975

976

-1

705

878

-173

872

1.005

-133

1.007

861

146

1.450

1.323

127

1.332

1.156

176

ATIPICO

1.082

1.152

-70

750

1.029

-279

ATIPICO

NUEVA SERIE

REFER.

P.UNIT

AREA

EDAD

% DE

AÑOS

CONDOMINIO

1.245

0,942%

987

100

0,515%

1.195

0,363%

975

100

0,561%

705

125

0,874%

872

110

0,623%

1.007

0,409%

1.450

0,201%

1.332

0,277%

1.082

0,414%

LOGARITMICA

6,96826E-07

0,994261752

0,991313595

2673,5435

11,00904861

0,005386193

0,002090944

0,1338812

0,940585712

0,063720358

#N/A

31,66193639

#N/A

0,385669365

0,024361704

#N/A

R^2 =

94,06%

F =

31,6619

k =

n =

n - (k+1) =

Fo =

4,76

F >> Fo

Modelo de Correlación:

Y = 2673,544 * 0,99131359^X1*0,99426175^X2*0,00000069683^X3

Rev.: Febrero-2011