Cuando se usa la herramienta Solver de Microsoft Excel, una de las cosas que se puede hechar de menos es entender que cálculos ha realizado la herramienta.
Si Solver para Microsoft Excel encuentra una solución, o si se para el proceso mediante la tecla ESC, es posible seleccionar unos informes.
Se pueden seleccionar, dependiendo del método de resolución, dos informes.
Reponder o Población.
El de población tiene el siguiente aspecto.
Mientras que el de respuestas tiene el que se muestra a continuación.
El informe de respuestas está disponible para todos los métodos de cálculo.
Un caso que me han pedido resolver hace tiempo con Microsoft Excel, es a partir de un listado de productos conseguir generar un lote por un importe exacto.
En el ejemplo, tenemos que generar un lote de 500,00 € en el que es obligatorio que se incluya al menos un ejemplar de cada uno de los diez primeros artículos.
La cantidad máxima de ejemplares por título es de 2 ejemplares.
En el video podéis ver el proceso completo.
En la columna C hemos indicado el número mínimo de ejemplares exigido.
En la columna E, será donde solver introduzca datos de partida.
Y en la columna D, sumaremos las dos columnas anteriores, obteniendo el total de ejemplares a incluir en el listado.
La columna F calculará el importe por línea, que se acumulará en la celda I1.
Con los datos de partida, el listado asciente a 169,54 €, ahora necesitamos seleccionar artículos por el resto del importe.
La configuración de Solver es la que se muestra, indicando el valor exacto para la celda I1, cambiando las celdas de la columna E, a la que también aplicaremos como restricción que los valores sean enteros.
La columna D tendrá dos restricciones, mayor o igual que cero y menor o igual que el número de artículos máximo permitido de la misma referencia.
Para este ejemplo el mejor método de resolución es Simplex LP.
Encontrar un valor aproximado, es relativamente sencillo hacerlo a mano, pero encontrar el valor exacto es prácticamente imposible sin usar Solver.
En este caso calcularemos un depósito prismático, con lados rectangurales.
Los criterios son tres:
1 - Una capacidad mínima de 1000 litros.
2 - El uso de la mínima cantidad de material
3 - Para facilitar las mediciones trabajaremos en centímetros
En el vídeo de Youtube podéis ver el proceso completo.
En este caso, partiendo de las dimensiones de Alto, Ancho y Fondo, calculamos, tanto el volumen del depósito, como la superficie, lo que nos dará la cantidad de material necesario.
En este caso vamos a intentar crear el depósito que menos material precise para su construcción.
La fórmulas usadas son la siguientes:
En solver, indicamos que deseamos que la casilla B9, tenga el valor mínimo, esta casilla muestra el total de material necesario.
Y en el campo "cambiando las celadas de variables", seleccionaremos las casillas correspondiente a las medida de Alto, Ancho y Fondo.
En cuanto a las restricciones, indicaremos que el volumen sea mayor o igual a 1000.
En esta ocasión no indicaremos que deseamos el menor posible, porque al usar la mínima cantidad de material, también obtendremos el mínimo volumen.
La otra restricción hará que las medidas Alto, Ancho y Fondo sean en valores enteros.
La configuración de solver queda como se muestra.
En un primer intento vamos a lanzar solver con el método "Siplex LP"
Vemos que en esta ocasión, solver nos indica que no es posible encontrar la solución con este método porque no se cumplen las condiciones de linealidad.
Cambiamos el método de resolución a "GRG Nonlinear" y ejecutamos.
En este caso si que obtenemos un resultado.
El resultado óptimo para este caso es la fabricación de un depósito cubico con todos los lados iguales.
Este ejemplo es solo una aproximación, pudiendo mejorare con muchos más criterios, como puede ser, usar los precios en lugar de la cantidad de material, incluyendo cortes, mano de obra de soldadura y desperdicios, así como intentar usar chapas enteras para minimizar los desperdicios de material.
La herramienta de Microsoft Excel, Solver dispone de tres métodos de resolución de problemas.
Es posible cambiar entre ellos en la sección "Método de resolución" seleccionando el que mejor se adapte mediante el cuadro desplegable.
Las opciones son:
- GRG Nonlinear
- Simplex LP
- Evolutionary
En la misma pantalla se explica cuando usar cada uno de ellos.
A pesar de que el método "GRG Nonlinear" es el método por defecto, para la mayoría de los problemas es suficiente y más rápido usar el "Simplex LP"
A la derecha del desplegable encontramos un botón que nos lleva a una pantalla de configuración de los métodos de resolución.
La primera pestaña es común a todos lo métodos.
La segunda es para el método "GRG Nonlinear"
Y la tercera para el método "Evolutionary"
El mejor enfoque me suele resultar comenzar por el método "Simlex LP", y si con este no se encuentra el resultado deseado, pasar a uno de los otros dos.
Una de las ventajas de sol ver es que permiten único paso seleccionar múltiples celdas para aplicar un criterio común a todas.
En el ejemplo vemos cómo aplicar a un rango de celdas la restricción igual a un valor determinado.
En este ejemplo tenemos en la columna c la suma de la columna a y b y lo repetimos para 16 filas.
Vamos a ver como en un único paso podemos aplicar una restricción a las 16 resultado para que sean iguales a un valor determinado.
En solver podemos indicar una única casilla con un valor cómo resultado del cálculo.
Sin embargo podemos aplicar restricciones a múltiples celdas.
En este caso aprovecharemos las restricciones para indicar que todas las celdas de la columna C tengan el valor 10.
Eso lo podemos conseguir seleccionando todo el rango e igualando lo al valor 10.
La restricción se aplicará para cada una de las celdas.
Cómo estáe conjunto de criterios vamos a conseguir que solver introduzca en las columnas A y C datos que provocarán el resultado en la columna C igual a 10 para todas las filas.
Resolvemos y comprobamos que se cumple nuestro objetivo.
Podríamos tener mayor variedad de resultados si hubiésemos indicado un resultado mayor de 10.
En ocasiones, como la del problema de los depósitos con valores de entrada enteros, es imposible obtener el resultado deseado.
En esos casos lo que deberemos intentar es obtener el valor más cercano.
En el vídeo muestro cómo conseguirlo con el mínimo y una restricción.
Partiendo del último ejercicio, veíamos que el resultado no se podía conseguir.
Esta era la configuración.
Para este conjunto de valores la respuesta de solver era que no podía encontrar una solución.
A estas alturas la única forma de conseguir el resultado era usar valores decimales.
Hoy vamos a realizar otra aproximación al problema.
En lugar de buscar exactamente un valor de 1000, intentaremos encontrar el valor más pequeño que cumpla la condición de que se usen valores enteros de entrada, y a la vez sea mayor de 1000.
Para ello aplicaremos en primer lugar una restricción.
La celda del resultado debe ser mayor o igual a 1000.
Y por otra parte, el valor de la celda resultado debe ser el menor posible.
Estos son los cambios aplicados.
En este caso comprobamos que sí es posible encontrar una solución.
Con la adecuada combinación de restricciones solver permite obtener el resultado deseado.
