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.
Lo he comprobado y en español mexicano los ";" se sustituyen por ",", salvo los números del corchete, los de las potencias, que siguen separándose mediante puntos y coma. Por lo menos en mi versión de Excel 2007. Será fallo de programación.
ResponderEliminarMuchas gracias, me sirvió mucho
ResponderEliminarGracias a usted por dejar comentario
EliminarHola
ResponderEliminarMuchas gracias. Me ha sido muy útil porque quería encontrar un modo de poner directamente en celdas los parámetros del ajuste que sale en la gráfica.
Gracias por comentar, es gratificante ver que es de utilidad
EliminarAmigo, a mi no me funciona el excel 2010 ¿Qué diferencia existe?
ResponderEliminarHola, debería ir bien, pero ya se sabe que Microsoft... Creo que debe ser algún problema con la traduccion del programa (lo de ponerlo en distintos idiomas lo chafa todo) y ña separación de los datos. Mira mi primer comentario al respecto. A lo mejor en 2010 han cambiado la separación entre exponentes. Prueba a ver y yo intentaré verlo en un equipo con 2010
Eliminar¿Sabéis ya como arreglarlo en Excel 2010?
ResponderEliminarmuchas gracias
Lo siento, pero sorprendentemente en mi entorno nadie usa la versión 2010, y no tuve tiempo de comprobarlo.
EliminarPARA EL OFFICE 2010 ES =ESTIMACION.LINEAL(B1:B4;A1:A4^{1\2\3};1;1).
ResponderEliminarMuchas gracias, haré una micro-entrada con esta información con su permiso, para que se vea mejor.
EliminarMuchísimas gracias!! Me será de gran utilidad en Excel 2010.
ResponderEliminarGracias, de verdad. Saludos :)
Gracias por comentar
EliminarHola, Tengo una duda. Tengo una serie de datos, cuya gráfica se ajusta con una línea de tendencia polinómica de grado 2, es decir, tengo los recta: y=Ax^2 + Bx+ C. Y deseo calcular el error cometido para el parámetro A. ¿Cómo sería la estimación lineal? ¿O cual sería el valor que corresponde a dicho error?
ResponderEliminarGracias
Si lee detenidamente la entrada ese es el ejemplo explicado. El error en D3. Un saludo
EliminarPARA EL OFFICE 2013 también es =ESTIMACION.LINEAL(B1:B4;A1:A4^{1\2\3};1;1). ¡Me he vuelto loca antes no he dado con ello!
ResponderEliminarCiertamente es una lástima que Excel dependa tanto de la configuración del idioma. Es para volverse loco.
EliminarY gracias por comentar
EliminarMuy buena la explicacion . HAs sacado un 10.
ResponderEliminarJajajaja. Gracias hombre.
EliminarMuchísimas gracias!!! Me ha resultado de gran ayuda! :)
ResponderEliminar(Y gracias también al anónimo que ha dado en el clavo en la corrección para el Excel 2010!).
Gracias por comentar Un saludo
EliminarHola,
ResponderEliminarEstoy buscando un modo de poner directamente en celdas los parámetros del ajuste que sale en la gráfica. La cuestión es que estoy trabajando con tendencias logaritmicas y exponenciales. Y los ejemplos que veo solo son de tendencia lineal y polinomica. Por eso, me gustaría saber si hay alguna manera de obtener los parametros de las ecuaciones logaritmicas y exponenciales, en excel, sin necesidad de graficar. Gracias
Buenas noches y gracias por comentar... y por ponerme en un aprieto. Vamos a ver, para las funciones exponenciales que Excel propone en la gráfica como a*e^(bx), la función a usar es =ESTIMACION.LOGARITMICA(). Pero esta función ofrece resultados el tipo a*b^x. Por ejemplo, si generas la función y=e^x, ESTIMACION.LOGARITMICA() devuelve 2,718*1^x. Si generas y=10^x, la función te devuelve a=10 y b=1, es decir, lo que pusiste, pero la grafica y el ajuste de la linea de tendencia da y=e^(2.302*x). Esto es digno de estudio, y lo mismo me da para un post. En cuanto a la función logarítmica...me temo que esto no sale de forma automática. La única solución que te propongo es usar Solver y construirte la ecuación a tu gusto. Excel presenta algoritmos lineales y no lineales, con lo que se puede solucionar casi todo. El empleo de Solver lo introduje en un post precisamente para funciones exponenciales. Espero que le sirva.
EliminarNo lo he logrado en Office 365 =( con ninguna de las combinaciones.
ResponderEliminarAgradezco si me pueden ayudar.
Muchas gracias!
Lo siento pero dezconozco Office 365. Este es el problema, que cambian de versión y uno no sabe como adaptarse.
EliminarHola Carlos. yo uso office 365 y solo se tiene que modificar:...^{1;2} por...^{1;2}.
EliminarHe utilizado como referencia el primer ejemplo.
Saludos.
Gracias al Anónimo de 23 de octubre por su aportación.
EliminarMuchas, muchas gracias.
ResponderEliminarLa verdad creo que has salvado un informe de laboratorio que tengo que entregar. El mío, y el de todos mis compañeros xD
Me alegra que os haya sido de utilidad. Gracias por el comentario.
EliminarMuchísimas gracias, solo quería agregar que si se desea sacar los datos en una celda se debe anteponer la fórmula Indice así:
ResponderEliminar=INDICE(ESTIMACION.LINEAL(B1:B4;A1:A4^{1,2,3};1;1);1;1) en este caso el resultado me trae el dato de la primera fila y la primera columna para el ejemplo dado seria el 22, ahora si se desea encontrar la constante estaría en la primera fila cuarta columna (;1;4) y para el caso nos daría el -12
Muchas gracias por la aportación. Un saludo
EliminarEXCELENTE
ResponderEliminarGracias
EliminarHola, como podría despejar x del polinomio para interpolar un valor de y?
ResponderEliminarLamentablemente Excel no tiene funciones que permitan hacerlo directamente. Tendrás que buscar algún tutorial de usuarios que tengan programada alguna función.
EliminarMira este blog para ver funciones de interpolación, pero no lo he probado.
EliminarHola tengo una duda bastante profunda. Tengo datos no muestran una relación "aparente", pero quiero descartar que exista...se entiende? en 14 años de registro de precipitación quiero ver como vario la concentración de Uranio en el agua de una minera, en función a las fluctuaciones en la precipitación. Que podria usar?
ResponderEliminarBueno, si tiene el valor de r^2, puede obtener el coeficiente de correlación r y ver si es significativamente distinto de cero (esta sería la hipótesis nula). Para ello se calcula una t de student como t=r/SDr. SDr es la desviación estándar de r calculada como raiz((1-r^2)/(n-2)), siendo n el número de puntos. Si la t calculada es mayor que la t tabulada para una probabilidad dada(generalmente 0.05) y n-2 grados de libertad se rechaza la hipótesis nula y se dice que r no es igual a cero (existiría correlación). Si t calculado es menor que el tabulado, se puede afirmar que no existe correlación.
EliminarHola que podria utilizar para descartar unha relación entre concentración de uranio y precipitaciones en el tiempo. Son 14 años de mediciones y muy fluctuante, los r cuadrados me daN MUY BAJOS..
ResponderEliminarLa respuesta arriba
EliminarBuenas noches:
ResponderEliminarActualmente uso Office 2016 y sólamente para el polinomio del tipo y=ax^2+b me funciona; para los demás, en cada celda de la matriz seleccionada me aparece #¡VALOR!.
Estaré atento a sus comentarios.
Buenas noches:
ResponderEliminarGracias a comentarios anteriores, pude comprobar en Office 2016 que, p.e en *{9;1} cambia a *{9\1} (; por \). Excelente Blog.
Gracias por los comentarios. Actualmente no uso Excel 2016, porque he preferido quedarme en 2010. No obstante, si necesita ayuda creo que es mejor que indique aquí cual es la función que quiere ajustar y cual es la fórmula que introduce en Excel, así podremos ver si hay algún error o simplemente es inexplicable (no creo). Un saludo
EliminarSigue funcionando en exel 2016, buenísimo, gracias por ayudar a almas en desgracia.
ResponderEliminarGracias por el comentario. Me alegra que así sea y siga funcionando en el 2016
EliminarMuchas gracias, al principio nada me daba pero al colocarle "\" donde va el ";" ya todo dio perfecto. Ojo, cambie el ; por \ solamente donde esta {}
ResponderEliminarGracias por comentar. El problema como siempre son los paquetes de idiomas de Excel. Quieren llegar a todos, pero lo complican mucho.
EliminarBuenas tardes,
ResponderEliminardispongo de una columna X y una columna Y, que són de forma exponencial (0;0 - 50;30 - 100;100). El mismo excel me hace un gràfico del mismo.
Quisiera que DEL MISMO GRAFICO (no de la lineal ni logaritmica,...) indicando en una celda el valor de X, me de automaticamente el valor de Y. Como lo puedo tramitar en excel?
Buenas noches. Lamento no entender bien su problema. Tal vez hable del gráfico de Excel que une puntos con líneas suavizadas. Si se dispone de un número de puntos adecuado se puede interpolar linealmente entre los dos puntos más próximos. Para ello es necesario indizar el valor a interpolar en la matriz de datos de x y escribir una fórmula que permita hacer la interpolación.
EliminarEsto aparece resuelto en este enlace. Como las funciones están en inglés, pruebe a ver la traducción en este otro enlace.
Decir que cuando me dejan un comentario en anónimo nunca puedo saber si mi respuesta sirve de algo. Pero gracias de todos modos.
Muy buen tutor y asi como los aportes que resuelven algunos problemas a la hora de procesarlos.
ResponderEliminarSoy principiante en el manejo de excel sobre todo en estas funciones, y si pueden ayudarme explicando de donde salen los valores de las celdas D2;F4, gracias anteladas
Gracias por el comentario. Pues aparecen de aplicar el método de mínimos cuadrados a los datos a ajustar. Supongo que las fórmulas analíticas las puedes encontrar en los libros. Yo puedo indicarte otra forma de hacerlo con Excel en este enlace . Ahí viene explicado lo que implica el método de mínimos cuadrados.
EliminarSaludos
Gracias por tu pronta respuesta, Justamente para linealizar una ecuación exponencial se debe recurrir a logaritmos para luego con regresión lineal, hallar el término independiente, la pendiente y el factor de correlación.
EliminarEsta ultima parte es la que me interesa tener en forma tangible los parámetros A, B y r. para con ellos hallar una ecuacion que me permita graficar la curva linealizada. Visitare el enlace que me pasas.
Gracias mil por tu ayuda.
César G.A. Cochabamba Bolivia
Gracias por tu respuesta tan pronta, saludos cordiales.
ResponderEliminarCésar G.A.
Muy bueno! Muy claro y conciso. Una gran ayuda, muy util. Da gusto encontrar cosas así por la red. Gracias!!!
ResponderEliminarGracias por comentar. Un saludo
EliminarMuchas gracias, esta es una de las explicaciones más detalladas... en muchas páginas estaba la sintaxis, pero hacía falta presionar ctrl+shift+enter. y acabe de entrar al mundo de las matrices. Muchas gracias.
ResponderEliminarNo hay de qué, gracias a vosotros por comentar
EliminarMuchas gracias. Me fue extremadamente útil su presentación
ResponderEliminar