Blog de Ninja ExcelFórmulas y funciones de ExcelSolver en Excel: Aprendiendo a entenderlo y aplicarlo

Solver en Excel: Aprendiendo a entenderlo y aplicarlo

Armando rubik

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:Excel excel Solver herramienta maximizar activar complemento ventanaPaso 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”:

Excel excel Solver herramienta maximizar activar complemento activado

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:

Excel excel Solver herramienta ejemplo autos parámetros

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:

Excel excel Solver herramienta ejemplo autos parámetros utilidad

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:

Excel excel Solver herramienta ejemplo autos parámetros utilidad variables decisión

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:

Excel excel Solver herramienta ejemplo autos parámetros utilidad función optimización

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:

Excel excel Solver herramienta ejemplo autos parámetros utilidad función optimización resultado 0

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:

Excel excel Solver herramienta ejemplo autos restricciones

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:

Excel excel Solver herramienta ejemplo autos restricciones presupuesto máximo

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

Excel excel Solver herramienta ejemplo autos restricciones producción máxima

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:

Excel excel Solver herramienta ejemplo autos ventana principal

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:

Excel excel Solver herramienta ejemplo autos ventana principal objetivo

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”:

Excel excel Solver herramienta ejemplo autos ventana principal opción para

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.

Excel excel Solver herramienta ejemplo autos ventana principal variables

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”:

Excel excel Solver herramienta ejemplo autos ventana principal agregar restricción

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:

Excel excel Solver herramienta ejemplo autos ventana principal agregar restricción secundaria

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:

Excel excel Solver herramienta ejemplo autos ventana principal agregar restricción secundaria presupuesto planilla

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:

Excel excel Solver herramienta ejemplo autos ventana principal agregar restricción secundaria presupuesto

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:

Excel excel Solver herramienta ejemplo autos ventana principal agregar restricción secundaria presupuesto introducida

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:

Excel excel Solver herramienta ejemplo autos resultado variables sin decimales planilla

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

¿Para qué sirve SOLVER?

Usa Solver para encontrar un valor óptimo (mínimo o máximo) para una fórmula en una celda, la celda objetivo, que está sujeta a restricciones o limitaciones en los valores de otras celdas de fórmula de una hoja de cálculo. Solver trabaja con un grupo de celdas llamadas celdas de variables de decisión o, simplemente, celdas de variables que se usan para calcular fórmulas en las celdas objetivo y de restricción.

¿Qué es la función SOLVER?

El complemento Solver es un programa de Microsoft Office Excel complemento que está disponible cuando instala Microsoft Office o Excel.

Logo ninja excel blanco

La plataforma líder de capacitación en Excel para empresas.

Compañía

Copyrigth © 2022 Ninja Excel.