Búsqueda de objetivos con SOLVER

Solver es un poderoso complemento de Excel que nos ayuda buscar objetivos, aplicando condiciones a celdas y rangos cambiantes, utilizando distintos métodos de resolución de problemas. Para poder utilizarlo es necesario activar este complemento en Excel 2010.

12-Ago-2016

Activar el complemento Solver

Solver está incluido dentro de Excel pero se encuentra desactivado de manera predeterminada. Para poder habilitarlo tenemos dos opciones: desde las Opciones de Excel y desde la ficha Programador.

Activar desde las Opciones de Excel

Ir a la ficha Archivo y elegir Opciones y se mostrará el cuadro de diálogo Opciones de Excel donde deberemos seleccionar la opción Complementos.

Ficha Archivo Opciones
Opciones Excel Complementos

En el panel derecho encontraremos el complemento llamado Solver. Para activarlo tenemos hacer clic en el botón Ir de la sección Administrar.

Activar complementos de Excel

Se mostrará el cuadro de diálogo Complementos y deberemos marcar la casilla de verificación de Solver y aceptar los cambios.

Activar complemento Solver

Activar desde la ficha Programador

La otra opción para activar el complemento, consiste en utilizar la opción Complementos de la ficha Programador.

Opción Complementos de la ficha Programador

Se mostrará el cuadro de diálogo Complementos donde deberemos marcar la casilla de verificación de Solver y aceptar los cambios.

Utilizando Solver

Si tenemos la necesidad de realizar un pronóstico que involucra más de una variable, podemos utilizar Solver en Excel. Este complemento nos ayudará a analizar escenarios de negocio multivariable y de optimización.

Abrir Solver

Una vez que hemos activado el complemento, para utilizarlo debemos de ir a la ficha Datos y Excel habrá creado un nuevo grupo llamado Análisis el cual contendrá el comando Solver.

Opción Solver de la ficha Datos

Al hacer clic sobre ese comando se mostrará el cuadro de diálogo Parámetros de Solver el cual nos permitirá configurar y trabajar con el complemento recién instalado.

Parámetros de Solver

Cómo mínimo tendremos que indicar:

  • Una celda objetivo (Establecer objetivo) que debe de contener una fórmula, donde deseamos obtener un valor máximo, mínimo o exacto (Para).
  • Rango de celdas que puede ir modificando Solver (celdas variables) para obtener el objetivo que necesariamente estén vinculadas directa o indirectamente en el resultado de la celda objetivo.

Opcionalmente indicaremos una lista de restricciones (sujeto a las restricciones) que se tienen que ir aplicando a celdas de la hoja para obtener el objetivo final. Cada restricción indicada estará formada por una referencia (celda o rango) un operador y un valor de restricción. Estas celdas de alguna manera tienen que estar también vinculadas directa o indirectamente con las celdas de variables.

Ejemplo Solver

Un granjero especializado en la explotación de ganado, debe decidir qué piensos ha de comprar para la alimentación de sus animales. El catálogo de piensos disponibles es corto, ya que únicamente puede seleccionar 4 tipos de piensos, A, B, C, D.

Cada uno de los piensos que puede adquirir tiene dos nutrientes M y N.

Las conversaciones con el veterinario de la granja le indican que las necesidades de alimentación, medidas en kg de nutrientes que deben ingerir la suma de sus animales al día, es de 5500 kg de nutriente M y 8700 kg de nutriente N.

Las necesidades de alimentación deben ser cubiertas a través de las compras diarias de piensos que deben realizarse. Para ello el granjero se basa en datos del catálogo de cada pienso.

Para la toma de decisiones el granjero ha diseño un modelo donde se indica la cantidad de toneladas de cada pienso que tiene que comprar y le muestre las aportaciones totales de cada nutriente, así como el coste total del pedido.

Ejemplo Solver

¿Cuántas toneladas de cada pienso debe comprar el granjero al día de tal manera que se satisfagan las necesidades de alimentación, y el coste sea mínimo?

Las compras de pienso están sujetas a las siguientes restricciones:

  • No se pueden realizar compras fraccionadas de tonelada de pienso.
  • Está obligado a comprar de todos los piensos, con  un pedido mínimo de 1 tm de pienso.
  • Las compras han de satisfacer las necesidades diarias de cada tipo de nutriente.

El objetivo (E14) es obtener un total de costes mínimo, satisfaciendo las necesidades diarias de nutrientes M (C15) Y N (D15) comprando de todos los tipos de pienso (A10:A13) como mínimo de 1 tonelada sin realizar compras fraccionarias.

La tabla ‘PEDIDO DIARIO PIENSOS’, tiene los cálculos necesarios para:

  • Obtener los kg de cada nutriente a partir de las tm de pienso que se pide multiplicado por la cantidad de kg de nutriente que contiene 1 tonelada de pienso (C10:D13).
  • El coste de cada tipo de pienso pedido (E10:E13).
  • El total de necesidades de cada nutriente que se consigue en función de los kilos que obtenemos por cada pedido (C14:D14).
  • El coste total de la compra que se realiza entre todos los pedidos (E14).
Fórmulas Ejemplo Solver

Los datos ya están listos para utilizar Solver, así que vamos a la ficha Datos y hacemos clic en el comando Solver donde se mostrará el cuadro de diálogo Parámetros de Solver.

Parámetros Solver Ejemplo

En nuestro ejemplo lo que queremos minimizar es el gasto total por lo que en el cuadro de texto Establecer objetivo está especificada la celda $E$14 y se ha seleccionado la opción Mín. El otro parámetro importante son las celdas de variables que en nuestro caso serán las toneladas de cada tipo de pienso que debemos de comprar $A$10:$A$13.

En la lista de las restricciones se han ido indicando cada una de las condiciones que se tienen que cumplir tanto para las toneladas de cada tipo de pienso que hay que pedir, como las necesidades de cada nutriente que se tienen que conseguir.

Para cada pedido de pienso que se tiene que comprar hemos establecido las siguientes restricciones:

  • La cantidad que se compra tiene que ser un número entero, ya que no podemos comprar valores fraccionarios.
Restricción para numeros enteros
    • Pulsamos el botón ‘Agregar’
    • Seleccionamos la celda donde se indica la cantidad de pienso que se pide
    • En la lista de operadores seleccionamos el valor int
  • La cantidad que se compra tiene que ser como mínimo de 1 tm.
Restricción para pedidos superiores a 1
    • Pulsamos el botón ‘Agregar’
    • Seleccionamos la celda donde se indica la cantidad de pienso que se pide
    • En la lista de operadores seleccionamos >=
    • En el cuadro Restricción escribimos el valor 1

Para cumplir las necesidades de cada nutriente hemos establecido las siguientes restricciones:

  • El total de nutrientes M y N obtenidos con los pedidos tiene que ser igual a las necesidades diarias del nutriente.
Restricción para necesidades diarias de nutrientes
    • Pulsamos el botón ‘Agregar’
    • Seleccionamos la celda donde se calcula el total de nutriente obtenido sumando todos los kg de nutriente
    • En la lista de operadores seleccionamos =
    • En el cuadro Restricción seleccionamos la celda donde se indica la necesidad diaria del nutriente

Todo está listo para continuar. Solamente debemos hacer clic en el botón Resolver y Excel comenzará a calcular diferentes valores para las celdas variables hasta encontrar el valor mínimo para el importe total de la compra. Al término del cálculo se mostrará el cuadro de diálogo Resultados de Solver.

Solución Solver

Si Solver consigue encontrar una solución a nuestra necesidad aplicando las restricciones establecidas, pulsaremos el botón Aceptar para mostrar los resultados obtenidos. Si no es correcta la solución obtenida, marcaremos la casilla Restaurar valores originales y pulsaremos el botón Aceptar para cancelar el proceso de resolución.

Resultado Solver

Excel ha hecho los cálculos para saber que, con las restricciones establecidas, tendremos un valor mínimo de gasto total  de 2.496,00 €.

Observar cómo se realizan pedidos de todos los tipos de pienso, que son números enteros y que son superiores a 1. También podemos comprobar que con los pedidos realizados conseguimos cubrir las necesidades diarias de cada tipo de nutriente.