Tratamiento de datos experimentales.

1. Instrucciones básicas de EXCEL.

2. Representaciones gráficas de datos experimentales.

3. Representaciones gráficas con EXCEL.

4. Ajustes por mínimos cuadrados.

5. Ajustes de valores experimentales a funciones utilizando Excel.

5.1. Ajustes directos por mínimos cuadrados a partir de las representaciones gráficas.

5.2. Instrucciones de diseño de un modelo para buscar valores con Solver.

1.     Instrucciones básicas de EXCEL.

Excel es un programa que permite de una forma sencilla y cómoda el tratamiento matemático de datos con hojas de cálculo, además de incorporar utilidades que permiten realizar su representación gráfica.

Este es el aspecto de la hoja de cálculo que te aparecerá en pantalla, una vez cargado el programa. Cada celda está situada en una columna y una fila, y se referencia indicando la columna y la fila en que se encuentra. Por ejemplo, en la figura está marcada la celda F17.

La barra de menús contiene los típicos menús desplegables de cualquier aplicación de entorno Windows y otras aplicaciones de OFFICE y deberían ser lo suficientemente conocidas ya: copiar, pegar, abrir, guardar, formato,...

Una vez configuradas las celdas, formato, fondos, recuadros..., se puede comenzar a introducir los datos. Excel, por defecto, entiende como número los valores numéricos que introducimos, y como texto los alfanuméricos. Si deseamos que un número aparezca como texto debemos anteponer una tilde (’12500 aparecerá como texto 12.500, a la izquierda de la celda. 12500 aparecerá como número, a la derecha de la celda).

Podemos introducir los datos de dos formas distintas: o bien introduciendo el valor escrito de forma directa situándonos encima de la celda en cuestión, o bien como operación matemática de los valores introducidos en otras celdas. Para realizar esta segunda operación, lo que introduciremos en la celda correspondiente será una formula matemática.

Para introducir la formula:

1.- Seleccionaremos la celda donde queremos introducirla.

2.- Colocaremos delante un signo "="

3.- Escribiremos la formula haciendo referencia a las celdas que intervienen de la forma que hemos señalado antes, o seleccionando con el ratón las casillas correspondientes a los valores a partir de los cuales queremos obtener el valor de la celda. Por ejemplo, si queremos multiplicar el valor de la celda A6 por el valor de la celda B6 deberemos escribir: =A6*B6. A continuación pulsaremos el botón aceptar para que se realice la operación. De esta forma en la celda seleccionada aparecerá el resultado de dicha operación.

4.- Para copiar la misma operación en un conjunto de celdas procederemos de la siguiente manera:

Una vez introducida la fórmula en una celda la seleccionamos y colocamos el ratón en el vértice inferior derecho de dicha celda. A continuación, manteniendo pulsado el botón izquierdo del ratón, arrastramos este por encima de las celdas en las que queramos introducir esta misma operación apareciendo de forma automática el valor correspondiente.

Otra forma sería marcar la celda con la fórmula y copiarla. A continuación marcamos las celdas sobre las que queramos copiarla y pegamos.

Los índices de filas y columnas de las operaciones se habrán modificado al copiar la fórmula manteniendo las posiciones relativas de fila y columna de la la celta donde estaba la operación copiada en las celdas donde copiamos. Si quisiéramos mantener fijo un índice de celda o columna de una fórmula deberemos introducir delante del mismo el signo "$", lo que indicara Excel, que no debe modificar dicho valor al arrastrar la formula a otras celdas. Por ejemplo la operación =A$6*B$6 al copiarse y pegarse en distintas celdas no modificará los índices de las filas, y sólo modificará los de las columnas.

2.     Representaciones gráficas de datos experimentales.

Tras un hecho experimental se obtienen una serie de valores medidos que nos relacionan dos o más magnitudes. Un primer paso para su interpretación consiste en relacionar las magnitudes a través de una o más gráficas de forma que nos permita traducir los resultados obtenidos a una forma más fácil de estudiar.

Así, por ejemplo, supongamos que hemos estirado una fibra de plástico aplicando diferentes fuerzas y hemos medido la longitud de la fibra en cada caso, obteniéndose los valores siguientes

        A partir de estos datos es difícil sacar conclusiones sobre el comportamiento del material. Un primer paso es ordenar los valores de las medidas,

        Se puede observar una ligera mejoría en la interpretación de los resultados. Ahora sabemos que al incrementar la fuerza aplicada se incrementa la longitud de la fibra, pero no podemos decir en que proporción aumenta, ni dar con certeza un valor intermedio entre dos puntos medidos.

        Si trazamos la gráfica:

observamos que en un principio la longitud aumenta linealmente con la fuerza aplicada. A partir de un cierto valor de fuerza la pendiente disminuye progresivamente. Una representación gráfica nos ayuda en la interpretación de los resultados y nos permite averiguar rápidamente algunos valores relacionados con la experiencia efectuada: pendiente inicial, puntos intermedios, punto de inicio de pérdida de linealidad, etc.

Consideraciones a la hora de trazar una gráfica:

• Las gráficas se representan en papel milimetrado, y se asigna el eje horizontal (abscisa) a la variable independiente y el eje vertical (ordenada) a la variable dependiente. Se indica sobre cada eje la magnitud que se está representando y la unidad en que se trabaja.

• Para facilitar la lectura de puntos intermedios se toma cada cm del papel milimetrado como un 1, 2, o 5 veces alguna potencia de 10. Por ejemplo, 10, 20, 50, 0'1, 0'2, 0'5, etc. Sobre los ejes sólo se indican estas divisiones enteras, no los valores de las medidas realizadas.

• El origen de ambas escalas no tiene necesariamente que coincidir con el punto de intersección de los dos ejes. Conviene desplazar los orígenes para que la gráfica ocupe la mayor parte del rectángulo definido por ambos ejes.

• Al realizar las medidas conviene, normalmente, que éstas se encuentren uniformemente espaciadas sobre el intervalo de valores que se está midiendo.

• La escala elegida en los dos ejes no tiene porque ser la misma, de nuevo hay que procurar ocupar la mayor superficie dentro del rectángulo definido por los ejes.

• Las líneas, rectas o curvas, que mejor se ajustan al conjunto de puntos experimentales, han de ser finas y continuas, nunca quebradas, ya que, generalmente, las magnitudes físicas y sus derivadas varían de forma continua. El trazado de la curva que mejor se ajusta a la distribución se realiza de forma que pase por el mayor número posible de puntos experimentales, y deje a la derecha aproximadamente el mismo número de puntos que a la izquierda. La curva debe ser coherente con la experiencia que se está realizando.

• Si las magnitudes que se representan están afectadas por un error no despreciable, en lugar de representar el dato experimental por un punto lo haremos por un rectángulo, centrado en el punto, donde sabemos que sí se encuentra el valor real de ese dato. Este “rectángulo de error” tendrá de dimensiones dos veces el error cometido en la medida de cada una de las dos magnitudes representadas (2.Δx y 2·Δy , siendo Δx y Δy los errores correspondientes). La curva que represente el fenómeno físico estudiado deberá pasar por todos los rectángulos.

3.     Representaciones gráficas con EXCEL.

Una vez introducidos los datos en la hoja de cálculo de EXCEL, si queremos representar los resultados experimentales que tenemos en dos columnas la primera operación será seleccionarlas. Lo haremos haciendo uso del ratón presionando el botón izquierdo y arrastrando sobre las celdas correspondientes a una de las columnas. Para seleccionar otra columna no contigua sin perder la selección anterior haremos uso de la tecla "ctrl" que, manteniéndola pulsada, nos permitirá proceder de la misma forma que para la primera selección realizada.

Una vez seleccionadas la celdas a representar iremos al menú insertar y seleccionaremos el botón gráfico.

Seleccionada la opción "Gráfico", tendremos la posibilidad de elegir entre los tipos de gráficos disponibles, eligiendo el que consideramos mas conveniente para nuestras necesidades. En general nos interesará una representación en un sistema de coordenadas XY (dispersión XY) en la que se representen solo los puntos experimentales, sin escoger la opción de unirlos mediante rectas o curvas.

Tras la selección del gráfico irán apareciendo las distintas pantallas, mediante las cuales podremos definir las opciones de configuración de nuestro gráfico.

De todas las opciones a configurar, se pueden destacar, el tipo de gráfico, el rango de los datos (seleccionado de forma automática según la tabla de datos), las leyendas de Gráfica, Ejes etc. Así como la situación del gráfico en nuestra hoja de Excel.

Una vez configurado el gráfico ya lo tendremos en nuestra hoja. Mediante el uso del ratón, y seleccionando sobre los laterales o esquinas del gráfico, podremos modificar su tamaño.

También podemos desplazar la gráfica mediante el ratón: se selecciona con el botón izquierdo del ratón y se desplaza como si fuera una imagen normal de Word, etc.

Una vez colocada en el sitio mas adecuado según nuestras necesidades, mediante el ratón podremos seleccionar cualquiera de sus elementos: desde una leyenda, hasta uno de los ejes o la serie de puntos representada. Con el botón derecho del ratón, o haciendo doble clic, se desplegará un menú con todas las opciones para modificarlo. De esta forma ajustaremos el gráfico al formato que consideremos mas adecuado.

Rectángulos de error:

Puede ser conveniente la representación de los rectángulos de error en la gráfica, para lo cual procederemos de la siguiente forma:

• A partir de la gráfica representada, y posicionando el ratón sobre los puntos, pulsaremos el botón izquierdo haciendo "doble clik", aparecerá el menú correspondiente.

 

• Sobre dicho menú seleccionamos la pestaña correspondiente a "Barras de error eje X”, donde podremos configurar el tipo de dato de error que vamos a representar, así como la ubicación de los datos que nos dan dicho error.

• Introducidos estos datos y pulsando "Aceptar", volvemos a ver la hoja principal con la representación de lo que se denomina "Rectángulos de error".

Deberemos realizar esta operación del mismo modo para los "ejes X e Y".

Una vez realizados todos estos pasos tendremos la gráfica dispuesta para ser analizada e interpretada.

4.     Ajustes por mínimos cuadrados.

En numerosas ocasiones es preciso trabajar analíticamente con funciones obtenidas a partir de resultados experimentales. Para ello deberemos ajustar los datos experimentales a una curva cuyos parámetros sean tales que el error introducido con el ajuste sea mínimo.

El caso más sencillo de ajuste para la obtención de una función que relacione dos variables estudiadas experimentalmente es aquel en que la relación sea lineal. La curva a ajustar será una recta .

El problema que tendremos que resolver será calcular la ordenada en el origen y la pendiente de la recta, m y n, de tal forma que el sumatorio de las distancias entre cada punto experimental y la recta elevadas al cuadrado, S, sea mínimo:

expresión en la que el subíndice i corresponde a los datos de cada punto experimental, y variará entre 1 para el primer punto y N para el último.

La recta así calculada es la que mejor se ajusta a los valores experimentales y se denomina recta de regresión.

Para hacer mínimo a S variando m y n se debe cumplir la doble condición de mínimo:

De las dos derivadas obtenemos dos ecuaciones con dos incógnitas, m y n.

Definiendo: , , , ,

Se puede demostrar que los valores de los parámetros deben ser:

,

Donde N es el número de puntos experimentales.

Una vez obtenidos los parámetros de la recta, a fin de evaluar la bondad del ajuste obtenido se determina el coeficiente de correlación, R, cuyo módulo toma valores entre 0 y 1, siendo mejor el ajuste cuanto más próximo sea su valor a 1:

Definiendo  la expresión del coeficiente de correlación es:

Al aplicar las expresiones de la recta de regresión se debe tener en cuenta que podemos calcular los parámetros para cualquier conjunto de puntos experimentales, se ajusten o no a una recta. El coeficiente de correlación será el que nos de idea de la bondad del ajuste, aunque también se debe saber valorar dicho coeficiente.

La proximidad del coeficiente de correlación al valor 1 debe ser grande para que el ajuste se pueda considerar como bueno. Para hacernos una idea de esto, en los ejemplos de las siguientes figuras se puede ver como varía el coeficiente de correlación para diferentes casos de resultados experimentales, y el valor del coeficiente en cada caso:

caso 1: R² = 0,536                            caso 2:     R² = 0,8542

caso 3: R² = 0,914                            caso 4: R² = 0,9969

En el primer caso, resultados completamente aleatorios, el coeficiente es 0,53, y en el tercero 0,91 a pesar de que los resultados experiementales no parecen ajustarse del todo a una recta.

5.     Ajustes de valores experimentales a funciones utilizando Excel.

Mediante la hoja de cálculo Excel podemos realizar los ajustes de datos experimentales a curvas siguiendo diferentes procedimientos, de los que los más sencillos son a partir de la propia representación gráfica de los resultados y utilizando la función Solver.

5.1.   Ajustes directos por mínimos cuadrados a partir de las representaciones gráficas.

Si representamos gráficamente los resultados de una experiencia en Excel, una de las opciones de la hoja de cálculo es la de agregar líneas de tendencia. Por ejemplo: en la hoja representamos la temperatura en un depósito, eje y en la columna B, en función del tiempo transcurrido, eje x en la columna A.

• Si marcamos con el puntero sobre los puntos representados, y apretamos el botón derecho del ratón se despliega el menú que permite Agregar línea de tendencia.

Aquí podremos seleccionar entre algunas funciones. En función de los resultados de nuestra experiencia nosotros deberemos establecer una hipótesis de ley física que la rija. En nuestro caso, observando la representación, podría ser lineal, o una polinomial de segundo grado, o incluso una exponencial. En las dos representaciones gráficas inferiores hemos seleccionado las dos primeras posibilidades.

• Para que en la representación gráfica aparezcan los parámetros de nuestra ley deberemos pasar al menú Opciones:

Al seleccionar que aparezca la ecuación en el gráfico, y el valor del coeficiente de regresión R, el resultado es el que se observa en las dos gráficas inferiores. En el caso representado la ley polinomial de segundo grado tiene un mejor valor de R, por lo que puede ser una mejor ley física que representa la relación entre la temperatura y el tiempo del fenómeno estudiado.

5.2.   Instrucciones de diseño de un modelo para buscar valores con Solver.

Funcionamiento de Solver:

Con la función Solver de Excel puede buscarse el valor óptimo para una celda, denominada celda objetivo, en una hoja de cálculo. Solver funciona en un grupo de celdas que estén relacionadas, directa o indirectamente, con la fórmula de la celda objetivo. Solver ajusta los valores en las celdas cambiantes que se especifiquen, denominadas celdas ajustables, para generar el resultado especificado en la fórmula de la celda objetivo. Pueden aplicarse restricciones para restringir los valores que puede utilizar Solver en el modelo y las restricciones pueden hacer referencia a otras celdas a las que afecte la fórmula de la celda objetivo.

Ejemplo sencillo de una evaluación de Solver:

Estudiamos un fenómeno en el que medimos dos magnitudes físicas: longitud (y) y tiempo (x).

• Los valores medidos los anotamos en la hoja de cálculo: los valores de x entre B5 y B11, y los valores de y experimental entre C5 y C11.

• Los resultados representados en la figura de la derecha de la hoja de cálculo muestran una posible relación lineal entre los valores de x e y, que será la hipótesis que utilizaremos.

• En las celdas E2 y E3 anoto una hipótesis inicial de ordenada en el origen y pendiente, que utilizo para calcular los valores de y que daría la ley lineal, y los anoto en las celdas D5, para el valor de x de B5, a D11 para el valor de x de B11. Las funciones utilizadas serían del tipo: para D5 (=+$E$2+B5*$E$3), para D11 (=+$E$2+B11*$E$3).

• Ahora calculo el error existente entre el valor de y medido y el valor de y calculado, elevado al cuadrado para dar igual peso a los errores por exceso y por defecto, y los anoto entre las celdas E5, para el valor de x de B5, a E11 para el valor de x de B11. Las funciones utilizadas serían del tipo: para E5 (=+(D5-C5)^2), para E11 (=+(D11-C11)^2).

• Sumo los errores cuadráticos en la celda E12 con la función (=+SUMA(E5:E11)). Esta última celda es en la que aplicaremos la función SOLVER, de forma que su valor se minimice haciendo variar las celdas E2 y E3, que son las celdas de la ordenada en el origen y pendiente.

• En el menú Herramientas seleccionamos Solver. En el menú que aparece anotamos:

en Objetivo $E$12

en Valor de la celda objetivo: Mínimo o Valor de 0

en Cambiando las celdas $E$2:$E$3 y

apretamos botón Resolver.

Solver buscará la solución óptima, que hemos representado en la gráfica inferior de la hoja: la recta que mejor se ajusta a los datos experimentales.

Otros dos ejemplos de planteamiento semejante con funciones diferentes son los de las hojas siguientes: