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
CIV: 32.290 / SOITAVE: 260
REGRESION MULTIPLE LINEAL CON EXCEL  6.0 (o superior)
Instrucciones:            
a) Escoger en fx la función: ESTIMACION.LINEAL  
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  
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" la ventanilla donde aparece la fórmula de la regresión
h) Apretar simultáneamente las teclas: "CONTROL", "SHIFT" y "ENTER"  
   
(*) 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
2.- Click en fx: Mostrará una caja de diálogo con Dos Columnas
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á iluminar con el ratón conjuntamente las columnas de las Variable Dependientes
c) Escribir en la ventanilla "Constante" el argumento VERDADERO  
d) Escribir en la ventanilla "Estadística" el argumento VERDADERO  
e) Click en el boton ACEPTAR; esto lo devolverá a la Hoja de Cálculo    
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 Cinco (5)
  líneas de alto por "n" columnas de ancho, donde "n" es igual al número de
  Variables (Dependientes e Independientes) que tengan los datos que vamos
  a modelar. En el caso de este ejemplo, el rango de salida sera de Cinco (5)
  líneas de alto por Cinco (5) columnas de ancho.  
b) Iluminar con el ratón la "Barra de Fórmulas" (ventanilla donde Excel indica la fórmula de
la regresión)  
c) Apretar simultáneamente las teclas "CONTROL", "SHIFT" y "ENTER" (en los teclados
en español "CTRL", "Mayus" e "Intro")  
d) Esta acción, permite que en el Rango de Salida, que ya habíamos iluminado, se 
descomprima permitiendo ver los diferentes elementos de la regresión.    
Cuadro de texto: Cinco (5) filas de alto
5.- La Salida de la Subrutina ESTIMACION.LINEAL, es presentada
por EXCEL de la siguiente forma:
6.- La interpretación de esta Salida es la siguiente:
E D C B A
Se4 Se3 Se2 Se1 Se0
R^2 SeY      
F df      
SCR SCE      
Donde:
A: Coeficiente del término independiente      
B: Coeficiente que acompaña a X1 (Area)  
C: Coeficiente que acompaña a X2 (Nro. De Oficinas)  
D: Coeficiente que acompaña a X3 (Nro. De Baños)  
E: Coeficiente que acompaña a X4 (Edad del Galpón)  
Se0: Error estándar de la constante A  
Se1 al Se4: Error estándar de cada uno de los coeficientes de las Variables Independientes
SeY: Error estándar de la correlación  
R^2: Coeficiente de Determinación  
F: 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.56 + 13.23*X1 + 13134.58*X2 +3460.94*X3 - 425.99*X4
   
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 fx la función: ESTIMACION.LOGARITMICA  
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  
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" la caja donde aparece la fórmula de la regresión
h) Apretar simultáneamente las teclas: "CONTROL", "SHIFT" y "ENTER"  
   
(*) 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
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
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"
 
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 hoja la matriz de correlación: Diagonal principal = 1; Ignorar la 1ra. Columna
b) Utilizando la funcion (fx) "COEF.DE.CORREL", generar los 6 coeficientes de covarianza:
  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. X1 X2 X3 X4
CTE.          
X1          
X2          
X3          
X4          
3.- Crear la Diagonal Principal con unos (1) e ignorar la Primera Columna
  CTE. X1 X2 X3 X4
CTE. 1.00        
X1 -o- 1.00      
X2 -o-   1.00    
X3 -o-     1.00  
X4 -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."
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).
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. X1 X2 X3 X4
CTE. 1.00        
X1 -o- 1.00      
X2 -o- 0.22 1.00    
X3 -o-     1.00  
X4 -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.
Observese, que no hay problemas de Multicolinealida entre la Variables Independientes
Nota: Para aquellos usuarios de la versión "Professional" de Excel; tienen la oportunidad
de instalar el conjunto de macros : "Análisis de Datos", desde el CD de instalación y proceder
con los comandos:  
   
Herramientas
     Análisis de datos
Coeficiente de Correlación
   
una vez abierta la cája de diálogo, iluminar las Variables Independientes (X) con el ratón
(sub-caja: "Rango de Entrada") e indicarle en que celda de la hoja de cálculo se requiere
la salida de la matriz (Sub-caja: "Rango de Salida)  
   
Esta macro, es especialmente útil cuando se va a analizar modelos de regresión con  
muchas variables independientes.        
Ejemplo completo de Aplicación:
Sea la siguiente serie correspondientes a apartamentos en la Urbanización Campo Claro de la ciudad de Caracas:
REFER. P.UNIT AREA EDAD FECHA
    M2 AÑOS PROTOC.
  Y X1 X2 X3
A 1,245,000 75.00 1 02-Feb
B 987,500 100.00 7 08-Jul
C 1,195,000 80.00 3 02-Sep
D 1,500,000 100.00 8 12-Ene
E 975,000 100.00 13 21-Jun
F 705,000 125.00 16 27-Feb
G 872,500 110.00 12 29-May
H 1,007,500 90.00 19 16-Ago
I 1,450,000 60.00 1 01-Nov
J 1,332,500 85.00 8 04-Oct
K 1,082,500 90.00 7 14-Ago
L 750,000 65.00 3 12-Mar
LINEAL
562.1070084 -18742.6093 -638.8387787 -19320990
870.5196422 21043.95309 6982.044936 32162701
0.272036525 258304.8722 #N/A #N/A
0.996520783 8 #N/A #N/A
1.99468E+11 5.33771E+11 #N/A #N/A
LOGARITMICA
1.000659369 0.982757074 0.99948668 4.032E-05
0.000809692 0.019573493 0.00649417 29.915311
0.289675315 0.24025565 #N/A #N/A
1.087485095 8 #N/A #N/A
0.188317981 0.461782221 #N/A #N/A
El Modelo Logarítmico, explica mejor el fenómeno
  CTE. AREA EDAD FECHA
CTE. 1      
AREA -o- 1    
EDAD -o- 0.744898119 1  
FECHA -o- -0.643203716 -0.081577 1
No hay problemas de Multicolinialidad
REF Y observ. Y calc. Residuo
A 1,245,000 1,112,998 132,002
B 987,500 1,097,844 -110,344
C 1,195,000 1,233,805 -38,805
D 1,500,000 959,470 540,530 ATIPICO
E 975,000 978,028 -3,028
F 705,000 849,561 -144,561
G 872,500 975,194 -102,694
H 1,007,500 918,944 88,556
I 1,450,000 1,342,734 107,266
J 1,332,500 1,152,187 180,313 ATIPICO
K 1,082,500 1,130,739 -48,239
L 750,000 1,108,616 -358,616 ATIPICO
NUEVA SERIE
REFER. P.UNIT AREA EDAD FECHA
    M2 AÑOS PROTOC.
  Y X1 X2 X3
A 1,245,000 75.00 1 02-Feb
B 987,500 100.00 7 08-Jul
C 1,195,000 80.00 3 02-Sep
E 975,000 100.00 13 21-Jun
F 705,000 125.00 16 27-Feb
G 872,500 110.00 12 29-May
H 1,007,500 90.00 19 16-Ago
I 1,450,000 60.00 1 01-Nov
J 1,332,500 85.00 8 04-Oct
K 1,082,500 90.00 7 14-Ago
LOGARITMICA
1.000387297 0.994264192 0.991324183 1.6623932
0.000299204 0.005387852 0.00209271 11.081075
0.940520411 0.063741972 #N/A #N/A
31.62497994 6 #N/A #N/A
0.385480575 0.024378234 #N/A #N/A
R^2 = 94.05%
F = 31.6250
   
k = 3
n = 10
n - (k+1) = 6
   
Fo = 4.76
  F >> Fo
Modelo de Correlación:
Y = 1.6623932 * 0.991324183^X1*0.994264192^X2*1.000387297^X3
Rev.: Enero-2002