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.

The All Results Journals

Bromeaba yo durante mi periodo como doctorando sobre lo bueno que sería poder publicar lo que no sale bien para que nadie perdiese su tiempo intentándolo. Parece que no fui el único en pensarlo y, desde la Sociedad para el Avance Científico, Society for the Improvement of Science (SACSIS) se lanza una iniciativa que me parece estupenda: crear una revista en la que se puedan publicar esos datos negativos. Tal y como reza en la web de la revista un gran porcentaje de trabajos no se publican porque los resultados son negativos. Y a veces el no perder tiempo en realizar experimentos que están abocados al fracaso o el hecho de analizar las causas de dicho fracaso es tan importante como presentar un buen resultado. 

He leído además una entrevista al fundador de SACSIS y promotor de la idea, David Alcántara,  y la idea ha terminado de convencerme. No se trata de publicar experimentos mal planteados (o trabajos malos como solemos llamarlos) sino experimentos bien desarrollados que no llegan a buen puerto y, es más, da la oportunidad de discutir el porqué de que ocurra esto. Además la revista es "Total Open Access", es decir, que no paga ni el autor ni el lector. Simplemente una muy buena iniciativa que creo merece ser divulgada. 

Tecnología Química

Revista cubana, editada por la Universidad de Oriente (Santiago de Cuba), acogida al OJS del Public Knowledge Project. Admite trabajos en español o en inglés sobre Ingeniería Química o Ingeniería de Alimentos, según reza en su ficha de PKP. Los trabajos publicados son revisados por expertos, con lo cual, se pretende superar unos estándares de calidad. Es otro claro ejemplo de publicaciones científicas en español y la apuesta de divulgación de universidades hispanoparlantes. Salvo algunas excepciones, no suelen tener indice de impacto. En el caso de Tecnología Química es así, pero está indexada en Latindex y Publindex. Ahí lo dejo para vuestra consideración.

  

Bubok, una alternativa en español

Ayer puse el enlace a bookboon.com como ejemplo de una editorial de autoedición que ofrece los libros gratis (en alemán, ingles, finés,... menos en español). En español podemos encontrar Bubok, que ofrece libros gratis o a un precio establecido por el autor.
Todas estas alternativas me gustan, pero hay que tener cuidado, pues al ser autoediciones la calidad final no está garantizada salvo por las credenciales del autor. En editoriales tradicionales  los libros técnicos se revisan por un equipo editorial y en muchas ocasiones (que debería ser siempre) por una comisión de expertos en la materia. Estas nuevas editoriales ofrecen la posibilidad de vender tu producto (o regalarlo), pero dudo que se revise de algún modo el material.

Por lo tanto, aunque la idea es buena y la aplaudo, cuidado con lo que se lee, puesto que cualquier "presunto experto" puede intentar vender un libro para expertos. Debiéramos quizá  buscar las credenciales del autor antes de usar esas fuentes. Por lo menos en el caso de la enseñanza de la ciencia, la no conformidad siempre ha de ser la premisa y todo debe ser puesto en duda hasta no ser demostrado.

Libros técnicos grátis en bookboon.com

Presento aquí una biblioteca de libros electrónicos gratuitos. En cuanto a los contenidos, estoy mirando algunos y tienen buena pinta. Los autores son profesores o expertos (según parece) en los temas sobre los que publican. Parece una buena alternativa para publicar (autoeditar) material docente. Y es que la idea es ponerlos gratis para los alumnos.Son gratis porque contienen publicidad.

Hay libros en siete idiomas... ¿ adivináis en cual no?

Electronic Statistics Textbook de StatSoft

Aquí presento otro de los grandes libros on-line sobre estadística. Contiene entradas sobre la estadística más básica y alguna algo más compleja. Por ejemplo, da una breve explicación sobre técnicas de reconocimiento de patrones. 

Toda fuente gratuita es interesante, si bien el programa que venden (explican las técnicas que podemos encontrar en el mismo) no lo es. 

Para mi gusto a veces es demasiado escueto, otras se me antoja oscuro, pero si se tiene algunos conocimientos previos resulta de gran utilidad.

NIST/SEMATECH e-Handbook

Dejo un enlace interesante a un e-book gratuito que resume muy bien los métodos estadísticos más habituales en el trabajo científico, desde el análisis exploratorio de datos, cálculos de incertidumbre, diseño experimental, gráficos de control, comparaciones estadísticas...

Como contenido científico on-line creo que es bastante bueno. Espero sea útil. 

Per un pugno di euro

No, no voy a hablar de Clint Eastwood en su papel en la película de Sergio Leone. Ni voy a hablar del Movimento Cinque Estelle. Ni mucho menos del hip hop italiano.

Solo quería llamar de nuevo la atención de aquellos que me siguen o simplemente me encuentran por casualidad. En estos tiempos de falta de medios económicos la investigación se presenta costosa, sobre todo la que no da unos resultados de aplicación inmediata. Por eso necesitamos ser creativos y si es posible, low-cost. Mis temas de trabajo versan sobre la aplicación de técnicas de análisis multivariante a datos químicos para la caracterización de alimentos. Este tipo de estudios puede llegar a tener interés económico dependiendo del producto que se pretenda diferenciar del resto. Claro ejemplo, el jamón ibérico de bellota, del que un día pondré algún otro ejemplo. Pero hoy voy a hablar de otra cosa que puede no parecer tan importante, pero que me permite entrenarme en la aplicación de técnicas que pueden usarse en otros muchos ámbitos. Además, si echamos el lápiz, no le ha costado a la Universidad más de 100 euros en reactivos y electricidad. Las muestras las pago de mi bolsillo.

Se trataba de proponer procedimientos de análisis no específicos, que aunque no dan información sobre composición, sirvan para diferenciar productos. El primer producto que he trabajado es el té y la técnica la espectroscopia de absorción molecular ultravioleta-visible (UV-Vis). La espectroscopia UV-Vis es una técnica sencilla y económica. El único tratamiento de la muestra es una extracción con una mezcla metanol-agua, una dilución y la medida del espectro.

Cuando se obtienen espectros de té verde, negro y rojo se observan algunas diferencias pero, a simple vista es complicado distinguir un tipo de té de otro. El problema es evidente cuando uno se plantea la diferenciación entre algunos tés negros y rojos, que visualmente se parecen y sus espectros se superponen en algunas zonas de longitud de onda. Si se aplica PCA se puede obtener a partir de los espectros variables no correlacionadas que sirven de alimentación de modelos basados en redes neuronales (si bien se han estudiados otros diversos modelos) que permite la diferenciación de estas variedades de té.

Podéis encontrar más información en: A. Palacios-Morillo, A. Alcázar, F. de Pablos, J. M. Jurado, Differentiation of tea varieties using UV–Vis spectra and pattern recognition techniques, Spectrochimica Acta Part A: Molecular and Biomolecular Spectroscopy, 103 (2013) 79-83