Solver en Excel: Aprendiendo a entenderlo y aplicarlo

Información clave

La herramienta SOLVER de Excel nos permite obtener la solución óptima para distintos problemas de decisión, tomando en cuenta una medida de desempeño (función objetivo), parámetros, variables de decisión y restricciones.

Lo básico

• Utilidad: Solver nos permite facilitar la toma de decisiones que podamos enfrentar. Un ejemplo es la compra del supermercado, en ella queremos gastar lo mínimo considerando que hay ciertas cosas que no se pueden dejar de lado. ¿Qué elementos escoger? ¿Cómo tomar esta decisión que incluye múltiples consideraciones? Solver te permite resolver este problema a partir de los datos que nosotros introduzcamos en Excel.
• Componentes
  • Función objetivo: Medida de desempeño de la decisión. Es lo que mide qué tan bien lo estamos haciendo dado lo que queremos lograr.
  • Parámetros: Números que no dependen de las decisiones que tomemos
  • Variables: Números que corresponden a las decisiones que tomemos, o que se verán afectadas por ellas.
  • Restricciones: Posibles limitaciones que presenta mi decisión.

Activando Solver en Excel

El primer paso al intentar usar la herramienta Solver en Excel es asegurarse de que la tengamos activada, ya que no está en nuestras herramientas por defecto. Para esto hacemos lo siguiente:

Paso 1: Hacemos click en la opción “Archivo” en la barra principal de Excel:

Paso 2: En la barra lateral izquierda hacemos click en “Opciones”.

Paso 3: Se abrirá una nueva venta, en la cual tenemos que hacer click en complementos:Paso 4: En la ventana de complementos hacemos click en la casilla “Ir”:

Paso 5: Se abrirá una nueva ventana, en la que debemos asegurarnos de que la opción “Solver” tenga un ticket en la casilla a su izquierda:

Paso 6: En la misma ventana (“Complementos disponibles”) hacemos click en aceptar:

Paso 7: Si es que realizamos todos estos pasos correctamente la herramienta SOLVER debería haber aparecido en la pestaña “Datos” de la barra principal de Excel, en la sección “Análisis”:

Definiendo el problema en Excel para optimizar con Solver

Habiendo activado la herramienta de Excel Solver, solo queda aprender cómo aplicarla. Lo primero que hay que definir es cuál es nuestra función para optimizar, la que correspondería al resultado de nuestras decisiones. En el siguiente ejemplo tenemos una empresa que vende autos:

El problema de decisión que enfrenta la empresa en este caso es obtener la mayor utilidad posible, por lo que además de tener la utilidad por cada tipo de auto es necesario ver cuánto vamos a producir.

Podemos ver en la imagen que la empresa ofrece tres tipos de autos: sedán, camioneta y deportivo. Cada uno de estos tipos tiene un costo de producción y un precio venta al mercado, con lo que podemos calcular la utilidad al vender cada uno:

Estos valores corresponden a los parámetros de nuestro problema en Excel, es decir, números que para propósitos de nuestros cálculos se mantendrán fijos, ya que en este escenario no dependen de las decisiones que tomemos. 

Asumamos que en este caso la única decisión que enfrenta nuestra empresa es cuánto producir de cada uno de los tipos de auto:

Para SOLVER estas serán nuestras únicas variables del problema, es decir, lo único que podrá cambiar para optimizar nuestra función.

Tip Ninja: Las casillas que contengan las variables pueden ir en blanco o contener un número, pero nunca contener una función de Excel, ya que en ese caso no podrán ser ajustadas.

Teniendo ya lo que ganamos por unidad y las unidades que vamos a producir, podemos crear una función que diga el total de utilidad que vamos a tener. Para simplificar este proceso usaremos la función SUMAPRODUCTO:

Por ahora el resultado que entregará Excel será 0, porque no estamos produciendo ninguna unidad, sin embargo, esto se ajustará cuando introduzcamos el problema en Solver:

Tip Ninja: Puedes asegurarte de que la función esté correctamente introducida poniendo números a las variables y viendo que los cambios en el resultado de la función a optimizar sean correctos.

Este resultado va a medir qué tan bien lo está haciendo nuestra empresa, por lo que nos gustaría optimizarla, lo que en este caso es hacer que la utilidad sea lo más grande posible. Esto significa que queremos maximizar la función.

Dado como está definido nuestro problema de decisión actualmente, nuestra función no tiene máximo, ya que, por ejemplo, podría poner un millón de camionetas y no hay nada que lo evite. Aquí es donde importan nuestras restricciones, ya que simulan las diferentes barreras que tenemos en nuestra producción.

Para el caso de nuestra empresa de autos, asumamos que existen dos restricciones: que tenemos un presupuesto de $5.000 para gastar en producir y que podemos guardar un máximo de 30 autos en nuestra bodega, por lo que somos incapaces de producir más:

Podemos ver que la columna derecha de la tabla “Restricciones” muestra el máximo de recursos de cada tipo que tenemos, dados por los parámetros $5.000 y 30. Ahora faltaría ver los recursos que estamos usando actualmente.

Para la restricción de “Presupuesto” usamos la función SUMAPRODUCTO para ver cuánto estamos gastando actualmente:

Para la restricción de bodegaje utilizamos la función SUMA para obtener el total de unidades producidas:

Ahora ya estamos listos para pasar nuestro problema a la herramienta Solver.

Traspasando nuestro problema de decisión a Solver

 Paso 1: Para pasar nuestro problema de Excel a Solver primero debemos abrir la herramienta. Como ya mencionamos, esta se encuentra en la pestaña “Datos” de la barra principal de Excel, en la sección “Análisis”. Al hacer click en el botón se abrirá la siguiente ventana:

Paso 2: Lo siguiente es indicar nuestro objetivo, que corresponde a la función que queremos optimizar. En este caso corresponde a la utilidad total, por lo que apretamos botón con la flecha e indicamos a Solver la casilla C9, que es donde se ubica nuestra función:

Paso 3: Ahora debemos asegurarnos de que la opción “Para” tenga seleccionado lo que estamos buscando. Aquí podemos indicarle a Solver que maximice la función, la minimice o haga que tome un valor determinado. Dado que estamos buscando maximizar la utilidad, nos aseguramos de que la opción indique “Máx”:

Paso 4: El siguiente paso es indicarle a Solver las variables de decisión. En este caso mis variables son cuánto produzco de cada tipo de auto, por lo que indico a Solver el rango G4:G6:

Tip Ninja: Al seleccionar las variables puedo mantener apretado la tecla “Ctrl” para seleccionar rangos de variables que estén separados. También puedo escribir los rangos usando un “;” para indicar que están separados.

Paso 5: Corresponde ahora introducir las restricciones de nuestro problema a Solver. Para esto apretamos el botón que dice “Agregar”, que se encuentra a la derecha del cuadro “Sujeto a las restricciones”:

Paso 6: Apretar el botón “Agregar” abrirá una nueva ventana llamada “Agregar restricción”. En esta ventana tenemos tres elementos: una referencia a la celda que contiene los recursos que estamos usando actualmente, la relación que tiene esa celda con la restricción y una referencia a la celda que contiene el parámetro de nuestra restricción:

Paso 7: La primera restricción que introduciremos en Solver es la de nuestro presupuesto limitado. En este caso la celda C12 irá a la izquierda, ya que corresponde a la celda que contiene a función SUMAPRODUCTO que describe los recursos que estamos usando:

Luego a la derecha colocamos la celda E12, que es la que contiene el parámetro de $5.000, nuestro presupuesto máximo. Finalmente nos aseguramos de que la relación entre las variables sea la correcta, en este caso es menor o igual:

Paso 8: Teniendo la restricción lista, apretamos el botón “Agregar” para que sea introducida en Solver, lo que hará que se vacíen las casillas para permitirnos ingresar otra restricción:

Paso 9: Ahora hacemos lo mismo con la segunda restricción, indicando en la casilla de la derecha   la celda C13, que contiene el total de unidades producidas, y en la de la izquierda a E13, que contiene el máximo que puedo almacenar:

Paso 10: No quedan más restricciones que ingresar así que hacemos click en aceptar, lo que nos lleva a la ventana principal de Solver:

Si las restricciones fueron ingresadas correctamente en Solver deberían aparecernos en el cuadrado blanco debajo de “Sujeto a las restricciones”:

Paso 11: Dado que nuestras variables no pueden ser negativas, debemos asegurarnos de que la opción en Solver esté chequeada:

Paso 12: Ahora ya podemos correr Solver, por lo que apretamos el botón “Resolver”:

Volviendo ahora al libro de Excel, vemos que Solver asignó números a nuestras variables, lo que nos entrega un resultado óptimo. Sin embargo, tenemos un problema porque Solver no sabe que las variables son autos, por lo que nos entrega números en decimales:

Para resolver esto volvemos a la ventana de Solver y agregamos una nueva restricción, indicando el rango donde se encuentren nuestras variables y la relación “int” (la palabra entero aparecerá automáticamente):

Esta restringe que las variables, que se encuentran en el rango G4:G6, sean números enteros.

Nuestro óptimo ahora está dado por números enteros:

Tip Ninja: La herramienta Solver no se actualiza automáticamente como una función tradicional de Excel, por lo que cada vez que hagamos cambios en restricciones o parámetros debemos volver a correrla para obtener un nuevo óptimo. ​

Preguntas Frecuentes