Preguntaba Camila Valdés en Calculo de regresión en Excel 2007 que como se podría obtener el ajuste de una función polinómica a unos datos en Excel sin tener que ajustar linea de tendencia. Antes de explicarlo agadezco a Camila su pregunta y a Agustín García Asuero por la explicación que me dio hace unos meses y que es la que me permite hoy explicar yo lo mismo en este blog.
Supongamos, para todos los ejemplos que propongo, que tenemos los datos en las columnas A y B, los valores de "x" en A1:A4 y los de "y" en B1:B4. Voy a explicarlo con funciones simples y preparadas, con lo que los ajustes serán bondadosos.
El primer ejemplo es una función del tipo y=ax^2+bx+c. Se selecciona la matriz D2:F4 y se hace click en el cuadro fx para poder escribir una función. la función es =ESTIMACION.LINEAL(B1:B4;A1:A4^{1;2};1;1).
Lo explicamos. B1:B4 son los valores "y", A1:A4 los de "x". El símbolo "^" es el que dice a la función que vamos a hacer un ajuste polinómico. Entre corchetes "{}" se dicen los grados que se van a ajustar. En este caso, el polinomio contiene coeficientes para el segundo grado, primer grado y término independiente. Como veis, se escriben en la fórmula de menor a mayor "1;2". Los valores 1 de después del corchete son necesarios para que no haga cero el término independiente (es lo que Excel llama [constante] y calcule los errores de cada coeficiente ([estadística], según Excel). Después se pulsa "ctrl + flecha de mayusculas + intro" todo a la vez. De este modo, en D2 aparecerá el coeficiente del término de segundo grado, en E2 el de primer grado y en F2 el independiente. En la fila 3, sus errores correspondientes, en la celda D4 el valor del coeficiente de determinación y en la E4 el error típico. He puesto además la gráfica de los datos con el ajuste de linea de tendencia de Excel.
NOTA IMPORTANTE: Si la configuración de idioma de tu PC es Español México o cualquier otra que use el punto como separador decimal, en la fórmula que propongo se debe cambiar los puntos y coma (;) por una coma (,)
Imaginemos que a los mismos datos queremos ajustar un polinomio del tipo y=ax^2+b. Entonces la fórmula empleada debe ser =ESTIMACION.LINEAL(B1:B4;A1:A4^{2};1;1). Ahora solo necesitamos seleccionar la matriz D2:E4, porque solo obtendremos dos coeficientes.
Y si queremos un polinomio de tercer grado =ESTIMACION.LINEAL(B1:B4;A1:A4^{1;2;3};1;1)
Si solo queremos solo el tercer y primer grado =ESTIMACION.LINEAL(B1:B4;A1:A4^{1;3};1;1)
Finalmente, si queremos que el término independiente valga cero, simplemente, en el valor de la fórmula que Excel identifica con el nombre [constante], se pone un 0 en vez de un 1. Así se escribe =ESTIMACION.LINEAL(B1:B4;A1:A4^{1;3};0;1)
Espero que os sirva.