"En ninguna parte alguien concedería que la ciencia y la poesía puedan estar unidas. Se olvidaron que la ciencia surgió de la poesía, y no tuvieron en cuenta que una oscilación del péndulo podría reunirlas beneficiosamente a las dos, a un nivel superior y para ventaja mutua"-Wolfgang Goethe-

jueves, 5 de septiembre de 2013

Calculo de regresión en Excel: ajustando un polinomio

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.









59 comentarios:

  1. 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.

    ResponderEliminar
  2. Hola
    Muchas 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.

    ResponderEliminar
    Respuestas
    1. Gracias por comentar, es gratificante ver que es de utilidad

      Eliminar
  3. Amigo, a mi no me funciona el excel 2010 ¿Qué diferencia existe?

    ResponderEliminar
    Respuestas
    1. Hola, 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
  4. ¿Sabéis ya como arreglarlo en Excel 2010?
    muchas gracias

    ResponderEliminar
    Respuestas
    1. Lo siento, pero sorprendentemente en mi entorno nadie usa la versión 2010, y no tuve tiempo de comprobarlo.

      Eliminar
  5. PARA EL OFFICE 2010 ES =ESTIMACION.LINEAL(B1:B4;A1:A4^{1\2\3};1;1).

    ResponderEliminar
    Respuestas
    1. Muchas gracias, haré una micro-entrada con esta información con su permiso, para que se vea mejor.

      Eliminar
  6. Muchísimas gracias!! Me será de gran utilidad en Excel 2010.
    Gracias, de verdad. Saludos :)

    ResponderEliminar
  7. Hola, 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?

    Gracias

    ResponderEliminar
    Respuestas
    1. Si lee detenidamente la entrada ese es el ejemplo explicado. El error en D3. Un saludo

      Eliminar
  8. PARA 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!

    ResponderEliminar
    Respuestas
    1. Ciertamente es una lástima que Excel dependa tanto de la configuración del idioma. Es para volverse loco.

      Eliminar
  9. Muy buena la explicacion . HAs sacado un 10.

    ResponderEliminar
  10. Muchísimas gracias!!! Me ha resultado de gran ayuda! :)
    (Y gracias también al anónimo que ha dado en el clavo en la corrección para el Excel 2010!).

    ResponderEliminar
  11. Hola,
    Estoy 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

    ResponderEliminar
    Respuestas
    1. 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.

      Eliminar
  12. No lo he logrado en Office 365 =( con ninguna de las combinaciones.
    Agradezco si me pueden ayudar.
    Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Lo siento pero dezconozco Office 365. Este es el problema, que cambian de versión y uno no sabe como adaptarse.

      Eliminar
    2. Hola Carlos. yo uso office 365 y solo se tiene que modificar:...^{1;2} por...^{1;2}.
      He utilizado como referencia el primer ejemplo.
      Saludos.

      Eliminar
    3. Gracias al Anónimo de 23 de octubre por su aportación.

      Eliminar
  13. Muchas, muchas gracias.
    La verdad creo que has salvado un informe de laboratorio que tengo que entregar. El mío, y el de todos mis compañeros xD

    ResponderEliminar
    Respuestas
    1. Me alegra que os haya sido de utilidad. Gracias por el comentario.

      Eliminar
  14. Muchísimas gracias, solo quería agregar que si se desea sacar los datos en una celda se debe anteponer la fórmula Indice así:
    =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

    ResponderEliminar
  15. Hola, como podría despejar x del polinomio para interpolar un valor de y?

    ResponderEliminar
    Respuestas
    1. Lamentablemente Excel no tiene funciones que permitan hacerlo directamente. Tendrás que buscar algún tutorial de usuarios que tengan programada alguna función.

      Eliminar
    2. Mira este blog para ver funciones de interpolación, pero no lo he probado.

      Eliminar
  16. Hola 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?

    ResponderEliminar
    Respuestas
    1. Bueno, 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.

      Eliminar
  17. Hola 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..

    ResponderEliminar
  18. Buenas noches:

    Actualmente 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.

    ResponderEliminar
  19. Buenas noches:



    Gracias a comentarios anteriores, pude comprobar en Office 2016 que, p.e en *{9;1} cambia a *{9\1} (; por \). Excelente Blog.

    ResponderEliminar
    Respuestas
    1. 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

      Eliminar
  20. Sigue funcionando en exel 2016, buenísimo, gracias por ayudar a almas en desgracia.

    ResponderEliminar
    Respuestas
    1. Gracias por el comentario. Me alegra que así sea y siga funcionando en el 2016

      Eliminar
  21. Muchas gracias, al principio nada me daba pero al colocarle "\" donde va el ";" ya todo dio perfecto. Ojo, cambie el ; por \ solamente donde esta {}

    ResponderEliminar
    Respuestas
    1. Gracias por comentar. El problema como siempre son los paquetes de idiomas de Excel. Quieren llegar a todos, pero lo complican mucho.

      Eliminar
  22. Buenas tardes,
    dispongo 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?

    ResponderEliminar
    Respuestas
    1. 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.
      Esto 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.

      Eliminar
  23. Muy buen tutor y asi como los aportes que resuelven algunos problemas a la hora de procesarlos.
    Soy 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

    ResponderEliminar
    Respuestas
    1. 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.
      Saludos

      Eliminar
    2. 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.
      Esta 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

      Eliminar
  24. Gracias por tu respuesta tan pronta, saludos cordiales.
    César G.A.

    ResponderEliminar
  25. Muy bueno! Muy claro y conciso. Una gran ayuda, muy util. Da gusto encontrar cosas así por la red. Gracias!!!

    ResponderEliminar
  26. Muchas 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.

    ResponderEliminar
  27. Muchas gracias. Me fue extremadamente útil su presentación

    ResponderEliminar

Haz tu comentario... quedará pendiente de moderación