Get Adobe Flash player
Home Software Manejo de Datos El uso de la Herramienta Solver

El uso de la Herramienta Solver

altEn el proceso de producción científica uno de los elementos claves es el tratamiento y análisis de los datos. 
En muchos casos utilizamos herramientas de caracter general como son las hojas de cálculo. En la mayoría de los casos su uso queda relegado a simples elementos formadores de tablas, es decir, organizadores de datos y generadores de salidas gráficas.
En este texto vamos a dar un paso más en las posibilidades de las hojas de cálculo de uso frecuente (especificamente Excel de Microsft y Calc de OpenOffice).

La herramienta Solver permite el ajuste de fórmulas o modelos a partir de datos los datos reales. Este elemento toma un especial potencial cuando se realiza no sobre una ecuación específica (existen varios programas con mayor capacidad y ecuaciones predefinidas) sino sobre un conjunto de ecuaciones interelacionadas e interdependientes (modelo al fin y al cabo) donde el resultado de una afecta al resultado de otras. Otro elemento importante es que Solver viene incorporado en las herramientas que suelen estar en casi todas las computadoras, como son Excel (MSOffice) y Calc (OpenOffice, muy potente y gratuito).

Para mostrar el funcionamiento de Solver vamos a desarrollar un ejemplo simple con solo una fórmula. Supongamos que hemos realizado una curva característica de agua para un suelo problema, con lo que hemos obtenido pares de valores Presión-Contenido de agua. Como parte de un modelo superior queremos obtener la fórmula (modelo) de Van Genutchen (1980) que nos permitirá determinar el contenido de agua del suelo para cualquier valor de presión. La fórmula específica se muestra al comienzo del artículo, con q como contenido de agua (sat=saturación y res=residual); Y como presión; y a, n y m que junto con qres son los parámetros a ajustar.  

 

Inicialmente en Excel podríamos tener una hoja tal como se muestra a continuación:

altSe pueden observar los valores reales obtenidos en las celdas con fondo verde, el valor de ajuste en la columna adjunta (Van G) y la diferencia absoluta entre el valor real y el ajustado en la columna Dif (=Abs(B16-C16), en naranja). La fórmula de la columna Van G es la correspondiente a la fórmula de Van Genutchen mostrada anteriormente y que utiliza los parámetros de las columnas Param-Valor. Esta fórmula sería (para Excel):=(1/(1+($B$3*A16)^$B$4)^$B$6)*($B$7-$B$5)+$B$5 para la primera fila y variando A16..A24 para las restantes. Recordad que el símbolo $ delante de la fila y/o columna implican referencias absolutas. Estas fórmulas lo que indica es que se utilice los valores de a,n, Res, m y Sat de la columna B y filas 3 a 7, para el cálculo de la función de la columna C (Van G).
En la tabla también hemos puesto un par de gráficos de control, uno superior que muestra el ajuste lineal entre los datos reales y los calculados y otro inferior que muestra el comportamiento de estos datos frente a la presión.
Hasta aquí bien, pero para que esto nos se útil debemos conocer los valores de a,n,m y res. Como se puede observar, los valores iniciales, tomados casi al azar, no nos sirve.
Aquí es donde entra en juego la herramienta Solver. Solver es una opción que se encuentra en el menú Herramientas. En el caso de Excel, no suele venir por defecto, por lo que se activa pulsando sobre Herramientas->Complementos y pulsando la casilla de verificación de Solver. En ese momento aparece en el menú de Herramientas.
Una vez pulsada la opción de Solver aparece una ventana tal que así (en Excel, pero en Calc es muy similar y con los mismos conceptos generales):

 

 

 

 

 

 

 

 

 

 

 Los elementos de Solver son: Celda Objetivo: Es la celda que solver evaluará según el criterio hasta encontrar la altcombinación de parámetros que más se ajusten.Valor de la celda objetivo: Es el criterio al que debe acercarese la celda objetivo.Cambiando las celdas: Se refiere al conjunto de celdas que Excel puede modificar para encontrar el valor de la celda objetivo que más se acerque al valor de la celda objetivo, es decir, la combiación de celdas de los parámetros a buscar. En solve, para mejorar la eficiencia del ajuste, se pueden indicar ciertos criterios como que sean positivos (>=0), el rango de valores , etc.
La imagen mostrada correspondería a los conceptos de nuestro ejemplo, es decir, la celda objetivo es la A13 (con titulo: Ajuste a cero) cuya fórmula es =PROMEDIO(D16:D24), que muestra el promedio de la diferencia entre los valores reales y los estimados. El criterio de ajuste es que este valor sea 0, o lo más cercano a 0, es decir que los valores calculados sean lo más parecidos a los reales. Las celdas a cambiar son las que necesitamos ajustar, es decir, a, n, res y m (B3 a B6), y las restricciones que se han impuesto es que el valor se encuentre entre 0 y 3, n entre 1 y 5, y res se encuentre entre 0 y 0.2. Esto restringe el conjunto de combinaciones que podrían ajustarse bien e impide ajustes con valores imposibles.

 

 

 



Una vez decidido los criterios se presiona Resolver y la heramienta intentará conseguir aquellos valores que hagan 0 la media de las diferencias, en el caso de no encontrarlo (bastante común cuando se trabaja con valores reales), presenta los valores más ajustados que ha encontrado, y ahí su enorme potencial.


El resultado de este ejemplo concreto se muestra en la figura siguiente:
alt
 Como se observa el propio Solver ha modificado los valores de los parámetros y el resultado se puede observar en los gráficos.












 

 

 

 

 

 

 

 

 











Las posibilidades de esta herramienta son ilimitadas ya que podemos imaginar que esta fórmula se puede encontrar en un modelo más complejo (dentro de la misma hoja de cálculo, por ejemplo) y la celda objetivo posee una fórmula en la que participan varios resultados interelacionados (ej. minimización de diferencias de Van Genutchen y maximización de similitudes con conductividad hidráulica, cualquier otro ejemplo). En fin...

 

Ultima actualización (Jueves 14 de Octubre de 2010 13:03)

 
Necesitas haber iniciado sesión o debes registrarte para participar en esta discusión.
Comentarios
hace 2 años, 1 mes por anroldfiarn # 27
Hola NuAn, he tratado de replicar el ejemplo que mencionas en este artículo pero hay algunas variaciones en la columna Dif, además la herramienta solver me da el "La solución no fue encintrada. El modelo no es lineal". Estoy trabajando en Libre Office. Saludos.
hace 2 años, 1 mes por NuAn # 28
Hola anroldfiarn,
gracias, efectivamente las diferencias no coinciden con lo que debería. Es extraño, pero de todas formas el fallo no aporta error al ejemplo.

lo que si es interesante es lo que comentas que en LibreOffice el ejemplo no funciona. Lo he estado mirando y parece ser que en la opción "Optimizar resultados a", cuando se toma la opción "Valor de ..0", el programa intenta que ése sea el valor final, y si no lo consigue indica que no se ha encontrado la solución. Pero si observas si que va cambiando los coeficientes a medida que progresa las iteracciones y antes de aceptar la ventana donde se indica, sí que ha cambiado los coeficientes y muestra la solución más ajustada que ha encontrado.

Una solución, en este caso, es indicar "Optimizar resultados a ...mínimo" ya que se trata de minimizar el promedio del absoluto de las diferencias, es decir, el mínimo valor cercano a 0, dado en la celda A13.
En otros casos habrá que estar muy pendiente de adecuar correctamente la celda de "Objetivo de celda".

La respuesta :"La solución no fue encontrada. El modelo no es lineal", ocurre porque has tomado la opción de ajuste a modelo lineal (Solve Lineal LibreOffice). Vete a Opciones... y elije en Motor Solver otro modelo de ajuste.

Un saludo
Español(Spanish Formal International)