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.com
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
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á 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
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 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:
C:\Archivos de Scaner\Galpon\Cinco.jpg
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%
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
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 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
y la Primera Fila
CTE. X1 X2 X3 X4
CTE. 1,00
X1 1,00
X2 1,00
X3 1,00
X4 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. X1 X2 X3 X4
CTE. 1,00
X1 1,00
X2 0,22 1,00
X3 1,00
X4 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 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 una Urbanización del este de Caracas:
REFER. P.UNIT AREA EDAD % DE
Bs.F./M2 M2 AÑOS CONDOMINIO
Y X1 X2 X3
A 1.245 75 1 0,942%
B 987 100 7 0,515%
C 1.195 80 3 0,363%
D 1.500 100 8 1,000%
E 975 100 13 0,561%
F 705 125 16 0,874%
G 872 110 12 0,623%
H 1.007 90 19 0,409%
I 1.450 60 1 0,201%
J 1.332 85 8 0,277%
K 1.082 90 7 0,414%
L 750 51 3 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 #N/A
1,062461001 8 #N/A #N/A
208956,8058 524459,8609 #N/A #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 #N/A
1,169090969 8 #N/A #N/A
0,198194213 0,452075942 #N/A #N/A
El Modelo Logarítmico, explica mejor el fenómeno
MATRIZ DE CORRELACION:
AREA EDAD CONDOM
AREA 1
EDAD 0,725799624 1
CONDOM 0,23161363 0,039900051 1
No hay problemas de Multicolinialidad entre las variables independientes
CALCULO DE LOS VALORES ATIPICOS (Método Empírico)
REF Y observ. Y calc. Residuo
A 1.245 1.107 138
B 987 1.145 -158
C 1.195 1.259 -64
D 1.500 972 528 ATIPICO
E 975 976 -1
F 705 878 -173
G 872 1.005 -133
H 1.007 861 146
I 1.450 1.323 127
J 1.332 1.156 176 ATIPICO
K 1.082 1.152 -70
L 750 1.029 -279 ATIPICO
NUEVA SERIE
REFER. P.UNIT AREA EDAD % DE
M2 AÑOS CONDOMINIO
Y X1 X2 X3
A 1.245 75 1 0,942%
B 987 100 7 0,515%
C 1.195 80 3 0,363%
E 975 100 13 0,561%
F 705 125 16 0,874%
G 872 110 12 0,623%
H 1.007 90 19 0,409%
I 1.450 60 1 0,201%
J 1.332 85 8 0,277%
K 1.082 90 7 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 #N/A
31,66193639 6 #N/A #N/A
0,385669365 0,024361704 #N/A #N/A
R^2 = 94,06%
F = 31,6619
k = 3
n = 10
n - (k+1) = 6
Fo = 4,76
F >> Fo
Modelo de Correlación:
Y = 2673,544 * 0,99131359^X1*0,99426175^X2*0,00000069683^X3
Rev.: Febrero-2011