Realizar la regresión lineal con excel con una variable independiente es muy sencillo con excel 2007 ó 2003. Ya que se puede hacer mediante el uso de los gráficos que nos dan la recta y ecuación de ajuste lineal, aparte de otros tipos de ajuste como logarítmico, potencial, etc. Excel posee herramientas incorporadas para realizar análisis de regresión que puedes ver en este post, pero aquí se mostrará como hacer el ajuste lineal para dos variables dependientes usando fórmulas matemáticas a través de Excel.

Suponiendo que se busca una correlación lineal, del tipo y = f(x); y = mx + b. Los valores m y b, que indican la constante y la recta, se pueden determinar con el uso de la función ESTIMACION.LINEAL.

ESTIMACION.LINEAL tiene la siguiente sintaxis:

ESTIMACION.LINEAL(valores_de_y;[valores_de_x];[constante];[estadística]) entre corchetes se encuentran los parámetros que son opcionales, dejamos el tercer parámetro vacío y en estadística escribimos VERDADERO. Luego seleccionando la celda inferior a esta, presionamos F2 y luego CTRL+SHIFT+ENTER con ello tendremos los valores de m y b.

En el caso de tener una correlación con dos variables, y = a + b1*x1 + b2*x2. Excel no permite calcular esta ecuación directamente, los valores de a, b1 y b2 puede obtener mediante las siguientes ecuaciones:

Con lo cual podríamos formar el siguiente sistema de ecuaciones:

De donde es posible obtener los parámetros a, b1, b2. Para hacer esto se puede emplear las funciones de excel:

SUMAPRODUCTO(rango01;rango02) que devulve la suma entre los pares de rango01 y rango 02.
SUMA.CUADRADOS(rango) que devuelve la suma de cuadrados de rango

Con el empleo de estas ecuaciones se puede calcular los valores del sistema de ecuaciones planteado lineas arriba. El paso siguiente es invertir la matriz del miembro izquierda, esto se puede realizar con la función:

MINVERSA(matriz) que devuelve la matriz inversa. Para ello la matriz debe ser cuadrada y con determinante diferente de cero. Esta función se usa de la siguiente manera, en una celda se escribe MINVERSA(rango_de_la_matriz), luego desde esta celda seleccionar un rango de 3×3, luego presionar F2 finalmente CTRL+SHIFT+ENTER.

Luego hay que multiplicar la matriz inversa obtenida con matriz columna del miembro derecho, esto se realiza de manera similar al uso MINVERSA, se emplea para este caso MMULT.

Aquí dejo un archivo para que puedas ver el uso de estas funciones.

Regresión lineal con dos variables dependientes

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *