ANÁLISIS DE REGRESIÓN LINEAL, GEOMÉTRICA, LOGARÍTMICA Y CUADRÁTICA EN MICROSOFT EXCEL

ANÁLISIS DE REGRESIÓN LINEAL, GEOMÉTRICA, LOGARÍTMICA Y CUADRÁTICA EN MICROSOFT EXCEL
Ing. Luis Manfredo Reyes

El análisis de regresión es una técnica estadística que trata de encontrar un modelo matemático que explique los cambios en una variable (dependiente) en función de otra u otras (independiente o independientes).

Aunque el modelo más enseñado y más utilizado es el lineal (y=a+bx), hay muchas situaciones donde éste modelo no se adapta bien a los datos, surgiendo la necesidad de probar otros modelos.

Afortunadamente, la tecnología disponible actualmente permite el ajuste de muchos modelos con los mismos datos. En ésta ocasión se discute el uso de Microsft Excel  para el ajuste.

En Microsoft Excel existe un complemento llamado ANÁLISIS DE DATOS, el que debe estar instalado para que funcione el análisis de regresión.

Estudio de caso:

Se tienen los registros de población de una comunidad a partir de su fundación (tiempo cero):

AÑO	POBLACION
0	200
25	425
50	950
70	2200
100	5000

1.       Creación del archivo de datos

Para el modelo lineal se ingresan los datos tal y como están originalmente

La variable independiente es el año (x) y la dependiente la población (y)

Para el modelo geométrico se calcula una nueva columna con los datos del logaritmo de la población (OJO, ÉSTE DATO NO PUEDE DAR CERO, PORQUE EL LOGARITMO DE CERO NO EXISTE, EN SU LUGAR SE USA UN VALOR MUY PEQUEÑO POR EJEMPLO  0.000001)

Para el modelo geométrico se calcula una nueva columna con los datos del logaritmo del año (OJO, ÉSTE DATO NO PUEDE DAR CERO, PORQUE EL LOGARITMO DE CERO NO EXISTE, EN SU LUGAR SE USA UN VALOR MUY PEQUEÑO POR EJEMPLO  0.000001)

Para el modelo cuadrático se calcula una nueva columna con los datos del año al cuadrado. Los datos deben quedar junto a los datos de año.

Se puede trabajar con logaritmos naturales o base 10. En éste ejemplo se usarán logaritmos naturales

Los datos ya ingresados a Excel quedan así:

Los datros pueden ingresarse en cualquier parte de la hoja, pero debe conocerse el lugar, para especificar después los datos en el análisis

2.       Diagrama de dispersión de los datos

Permite visualizar si los datos tienen tendencia lineal o de otro tipo. Dentro de la galería de Gráficas de Excel se debe especificar: diagrama de dispersión, los datos de x van en el eje horizontal y los datos de y en el eje vertical.

Es evidente que los datos no siguen una tendencia lineal, más bien geométrica o cuadrática.

3.       Ajuste del modelo lineal

Se ingresa a Datos, Análisis de datos y luego Regresión.  El modelo se especifica así:

Y el resultado es el siguiente:

La información más importante es: el coeficiente de determinación (0.849) y la significancia del modelo (0.0259) que por ser menor a 0.05 indica que el modelo sí es significativo al 5% de significancia. Estos datos se deben comparar con los de los otros modelos para escoger el mejor.

4.       Ajuste del modelo Geométrico

Se ingresa a Estadísticas y Regresión Lineal. Se ingresa como variable independiente x y como dependiente el logaritmo de x

Y el resultado es:

El grado de ajuste es de 0.99 y el modelo es significativo al 1%

Ojo: el modelo queda así: Ln y  =5.273+0.033 x

Para convertirlo a su forma exponencial, se operan los antilogaritmos de 2.21 y 0.04, el modelo sería:

Y= 194.4159*1.0304^X

             Ajuste del modelo logarítmico

La variable independiente es: logaritmo de x y la dependiente: logaritmo de y

El resultado que produce Excel  es el siguiente:

El modelo queda así: Ln y=6.664+0.162*Ln x

Puede verse que el coeficiente de determinación es muy bajo y el modelo no es significativo (porque el p es mayor que 0.05), por lo que debe ser desechado

  
   Ajuste del modelo cuadrático

La variable dependiente es y, las dos independientes x y x cuadrado

Siendo los resultados producidos por el programa los siguientes:

El grado de ajuste es alto y el modelo es significativo al 1%

   

DISCUSIÓN:

Debido a que existen dos modelos que tienen 0.99 de ajuste, la regla que se sigue es que se usa el modelo que matemáticamente sea más simple, en éste caso el geométrico.

Es posible probar muchos modelos más dependiendo del interés del investigador