Regresión lineal con dos variables dependientes

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.

Posted in excel, excel tips Tagged with:

Facebook comments:

0 comments on “Regresión lineal con dos variables dependientes
1 Pings/Trackbacks for "Regresión lineal con dos variables dependientes"
  1. […] Excel posee herramientas incorporadas para realizar análisis de regresión múltiple, sin embargo estas características no se muestran en la configuración normal que tiene Excel, deben activarse de la misma forma que el Solver. En este post mostraré cómo usar tales herramientas  para realizar un análisis de regresión con dos variables dependientes. Usare los mismos datos usados en el post: Regresión lineal con dos variables dependientes. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Make a computation for validation *
Time limit is exhausted. Please reload CAPTCHA.

Traducir

Read previous post:
Resolver ecuaciones con buscar objetivo de Excel

Excel permite resolver ecuaciones del tipo y=f(x), es decir permite encontrar el valor "x" para un "y" dado. Por ejemplo...

Close