Ya he hablado sobre ajustes lineales y polinómicos empleando la función "=ESTIMACION.LINEAL()" de Excel. Me preguntaba una lectora del blog ¿como hace Excel para obtener la ecuación de la curva de mejor ajuste, cuando agrega la línea de tendencia sobre los datos en una gráfica, en el caso de tener una función exponencial? La respuesta es simple, aplicando el método de mínimos cuadrados, es decir, calculando los parámetros de la función de manera que minimicen la suma de cuadrados de los residuales. ¿Y se puede obtener esos parámetros en una hoja Excel? Si, incluso cuando el método gráfico de Excel no deja ajustar una exponencial, lo que ocurre si los datos tienen tendencia negativa. A veces gana el procedimiento gráfico, con un ajuste mejor, pero otras (la mayoría), el ajuste en la hoja de cálculo es más eficaz, es decir, lleva a un mayor coeficiente de determinación.
¿Cómo se hace? Es muy simple. Pondré un ejemplo que me facilite a explicación.
Caso 1. Ajuste a una exponencial: Y=a*EXP(b*X) con "a>0"
Imaginemos que tenemos una serie de valores X e Y entre los que pensamos que existe una relación del tipo Y=a*EXP(b*X), donde EXP() se refiere al numero "e" elevado a la expresión que viene entre paréntesis y "a" y "b" son parámetros a determinar. Supongamos los siguientes datos:
En las celdas B14 y B15 introducimos valores iniciales de a y b, por ejemplo 1 y 1, respectivamente.
En la celda C2 escribimos =B$14*EXP(B$15*A2), esto se corresponde para el valor de Y estimado cuando se aplica la función de ajuste con los valores de a y b de las celdas B14 y B15. El símbolo $ se coloca delante de los números 14 y 15 para poder arrastrar esta celda desde C2 a C6, de manera que la misma fórmula se escriba en cada celda de la columna pero variando el valor de X utilizado en cada fila.
En la celda D2 se escribe =B2-C2, es decir el residual (valor verdadero menos valor estimado) y se arrastra hasta D6.
Si todo va bien debe quedar:
Se elige utilizar la solución de Solver y debe quedar así:
Caso 2. Ajuste a función del tipo Y=a*(1-EXP(b*X))
Colocando los datos en A2:B6, se hace igual que antes pero en C2 se escribe =B$14*(1-EXP(B$15*A2)).
Quedaría como sigue:
Como se ve, aquí el ajuste gráfico de Excel no es una opción adecuada.
Caso 3. Ajuste a la función tipo Y=a*EXP(b*X), con a negativo
En este caso Excel no deja agregar línea de tendencia. Se resuelve como en el caso 1, pero los valores de partida deben ser a y b deben ser, por ejemplo -1 y 1.
En resumidas cuentas:
1. La opción agregar línea de tendencia puede dar valores truncados no correctos.
2. Para funciones complejas es mejor usar SOLVER, pero debe cuidarse los valores de partida de lo parámetros.
3. Las exponenciales de tendencia negativa solo las soluciona SOLVER, no la herramienta gráfica
Espero os sirva
Imaginemos que tenemos una serie de valores X e Y entre los que pensamos que existe una relación del tipo Y=a*EXP(b*X), donde EXP() se refiere al numero "e" elevado a la expresión que viene entre paréntesis y "a" y "b" son parámetros a determinar. Supongamos los siguientes datos:
En las celdas B14 y B15 introducimos valores iniciales de a y b, por ejemplo 1 y 1, respectivamente.
En la celda C2 escribimos =B$14*EXP(B$15*A2), esto se corresponde para el valor de Y estimado cuando se aplica la función de ajuste con los valores de a y b de las celdas B14 y B15. El símbolo $ se coloca delante de los números 14 y 15 para poder arrastrar esta celda desde C2 a C6, de manera que la misma fórmula se escriba en cada celda de la columna pero variando el valor de X utilizado en cada fila.
En la celda D2 se escribe =B2-C2, es decir el residual (valor verdadero menos valor estimado) y se arrastra hasta D6.
Si todo va bien debe quedar:
Ahora en B9 escribimos =SUMA.CUADRADOS(D2:D6) y llamamos la herramienta SOLVER en Datos/Análisis/Solver. Si no estuviese activada se activa en Botón de Office/Opciones de Excel/ Complementos
Aquí se elige como celda objetivo la B9, donde estaba la suma de cuadrados de residuales, se elige que su valor sea mínimo cambiando las celdas B14 y B15 (a y b). Se pulsa resolver y SOLVER realiza un cálculo iterativo de a y b para minimizar la suma de residuales.
Aquí ademas he añadido el ajuste de linea de tendencia de Excel (línea de ajuste negra) y nuestro ajuste (linea roja), la varianza de residuales en B10 (es la suma de cuadrados dividido ente grados de libertad, n-2) y la varianza de los valores reales de Y en B11. Con estos valores se calcula el coeficiente de determinación en B17 como R^2=1-(Varianza de regresión/Varianza de Y), es decir =1-B10/B11.
Como puede verse, el cálculo sobre el gráfico de Excel no da buen resultado, porque da un valor de b de 0.999 cuando realmente es 0.9999
Colocando los datos en A2:B6, se hace igual que antes pero en C2 se escribe =B$14*(1-EXP(B$15*A2)).
Quedaría como sigue:
Como se ve, aquí el ajuste gráfico de Excel no es una opción adecuada.
Caso 3. Ajuste a la función tipo Y=a*EXP(b*X), con a negativo
En este caso Excel no deja agregar línea de tendencia. Se resuelve como en el caso 1, pero los valores de partida deben ser a y b deben ser, por ejemplo -1 y 1.
En resumidas cuentas:
1. La opción agregar línea de tendencia puede dar valores truncados no correctos.
2. Para funciones complejas es mejor usar SOLVER, pero debe cuidarse los valores de partida de lo parámetros.
3. Las exponenciales de tendencia negativa solo las soluciona SOLVER, no la herramienta gráfica
Espero os sirva
