Cómo usar solver en Excel 2019 o Excel 2016

Microsoft Excel ha evolucionado a través de los años ingresando o mejorando nuevas funciones y fórmulas con el objetivo de que la gestión de ellos datos sea cada día más simple de llevar. Esto es debido a que podemos estar ante grandes cantidades de datos tanto numéricos como de texto o fechas donde si alguno de ellos fallase o está mal configurado se desencadena una serie de errores que pueden suponernos un dolor de cabeza.

 

Pero Microsoft Excel va mucho más allá de funciones y fórmulas. Para muchos usuarios no es un hecho consciente que Excel integrará soluciones prácticas y verídicas mediante las cuales será posible llevar a cabo una hipótesis en base a la función de los datos ingresados. Esto es algo realmente útil para todos aquello que necesitamos tener certeza de cuánto podemos gastar, vender o administrar de una forma específica.

 

Esto se logra gracias a la función Solver la cual podemos instalar en Microsoft Excel 2016 o Excel 2019 y será de gran ayuda para este tipo de tareas.

 

 

Solvetic explicará en detalle cómo usar Solver en Excel 2019 y así lograr un punto más de administración y control en esta valiosa aplicación de la suite de Office. Este proceso es similar en Microsoft Excel 2016.

 

 

 

 

1. Cómo activar Solver en Excel 2016 o 2019

 

El primer paso que debemos llevar a cabo será activar el complemento Solver en Microsoft Excel, para ello vamos al menú Archivo donde veremos lo siguiente:

 

 

Allí damos clic en la categoría “Opciones” y en la ventana desplegada iremos a la sección “Complementos” y en el panel central seleccionamos “Solver”:

 

 

En la parte inferior daremos clic sobre el botón “Ir” ubicado en el campo “Administrar”, y en la ventana emergente activamos la casilla “Solver”:

 

 

Pulsamos en Aceptar para aplicar los cambios. Ahora, en em menú “Datos”, grupo “Análisis” encontraremos la opción “Solver”:

 

 

 

2. Usar Solver en Excel 2016 o 2019

 

Para usar Solver en Excel 2019 hemos dispuesto de la siguiente información:

• Una lista de sistemas o apps

• Una columna con el precio de cada una de ellas

• Una lista de unidades de cada elemento

• Costo total

 

Ahora, con estos datos, en la columna Costo total multiplicaremos el precio unitario por las unidades con la siguiente fórmulas:

=B7*C7

 

 

 

Ahora, añadiremos una nueva fila llamada Total presupuesto donde sumaremos todo el rango de la columna Total con la siguientes fórmulas:

=SUMA(D3:D8)

 

Ahora vamos al menú Datos, grupo Análisis y allí damos clic sobre Solver y será desplegado el siguiente asistente:

 

 

Allí indicaremos la celda objetivo, campo “Establecer objetivo” y en este caso seleccionamos la celda C11. Luego será posible ajustar el objetivo, campo “Para” ya sea el máximo, al mínimo o a un valor concreto según sea el criterio de los resultados, para este ejemplo activaremos la casilla “Máximo”. El siguiente paso es establecer las celdas variables, para ello vamos al campo “Cambiando las celdas de variables” y allí seleccionamos el rango deseado el cual será en este caso la columna Precio unidad USD:

 

 

A continuación, es hora de definir las restricciones, para este ejemplo suponemos que tenemos un límite de USD 10.000 para las compras, para establecer esta restricción pulsamos en el botón “Agregar” en el campo “Sujeto a las restricciones” y en la ventana emergente definimos lo siguiente:

• En el campo “Referencia de celda” ingresamos la celda Total presupuesto.

• Asignamos la restricción Meno o igual que (<=).

• En el campo Restricción asignamos el valor máximo a usar en este caso 10000.

 

 

 

Pulsamos en “Agregar” para aplicar los cambios. Ahora, la siguiente restricción será que tanto los sistemas como las apps se vendan completas, allí pulsamos de nuevo en Agregar y esta vez seleccionamos el rango de Unidades y seleccionamos el valor “int (entero)”:

 

 

 

Pulsamos en Agregar y finalmente, vamos a definir la cantidad mínima de cada sistema o app a usar, para ello disponemos de lo siguiente:

• 3 Office 2019

• 2 Windows 10

• 1 macOS Mojave

• 1 Suite de Adobe

• 2 Windows Server

• 2 Camtasia

 

Para ello pulsamos en Agregar y realizamos lo siguiente:

• En el campo “Referencia de celda” ingresamos la celda para cada sistema o aplicación en la columna Unidades, por ejemplo, para Office 2019 será C3, para Windows 10 será C4 etc.

• Asignamos el parámetro menor o igual que (<=) y asignamos la cantidad máxima en el campo “Restricción”.

 

 

 

Este proceso lo repetimos para cada elemento. Una vez realizado este proceso veremos algo similar a esto:

 

 

 

 

Definido esto, pulsamos en el botón “Resolver” para ejecutar el análisis y será desplegada la siguiente ventana:

 

 

Allí disponemos de las siguientes opciones:

• Si deseamos mantener los valores de la solución en la hoja de cálculo, daremos clic en “Conservar solución de Solver”.

• Si deseamos restaurar los valores originales antes de hacer clic en Resolver, daremos clic en “Restaurar valores originales”.

• Con el fin de interrumpir el proceso de resolución, presionamos la tecla Esc, Excel actualizará la hoja de cálculo con los últimos valores encontrados para las celdas de variables de decisión.

• Con el fin de crear un informe basado en la solución después de que Solver encuentre la solución, seleccionamos un tipo de informe en el cuadro Informes y daremos clic en Aceptar. El informe se crea en una nueva hoja de cálculo del libro, en caso de que Solver no encuentre una solución, la opción de crear un informe no estará disponible.

• Para guardar los valores de la celda de variable de decisión como un escenario para usarlo más adelante, debemos dar clic en Guardar escenario en el cuadro de diálogo Resultados de Solver y luego ingresar un nombre para el escenario en el cuadro Nombre del escenario.

 

Para este caso seleccionamos la opción “Conservar solución de Solver” y pulsamos en Aceptar para ver los resultados:

 

 

 

Como vemos, automáticamente Solver se encarga de analizar la cantidad máxima en base a los criterios seleccionadas. Vamos a ver otro ejemplo de cómo Solver es de utilidad para todo el proceso de análisis. En este caso contamos con los siguientes datos:

 

 

Allí disponemos de la siguiente información:

• Precios de elementos como CPUs, discos SSD y memorias RAM.

• Se ha definido el subtotal de cada elemento multiplicando la cantidad por el precio unitario.

• Hemos asignado las ventas totales sumando todos los subtotales.

• Se han aplicado restricciones a través de un máximo total de ventas, máxima cantidad de cada elemento y un máximo de dispositivos internos (discos y memoria).

• En la parte final hemos añadido la suma de todos los elementos que calculara Solver, así como solo el filtro de los elementos internos.

 

Al igual que el punto anterior, vamos al menú Datos y en el grupo Análisis seleccionamos Solver y allí definiremos los siguientes parámetros:

• En el campo “Establecer objetivo” ingresamos la celda deseada la cual es en este caso F5 (Ventas totales).

• En el campo “Cambiando las celdas de variables” ingresamos cada celda asignada a los subtotales.

• En las restricciones añadimos lo siguiente.

 

 

 

 

 

En el campo de restricciones usamos las siguientes opciones para comprender el funcionamiento:

• $B$13 <= $F$14: allí indicamos que la cantidad de memoria a vender debe ser menor o igual a la cantidad indicada en la celda F14 (máximo memorias RAM).

• $B$9 <= $F$13: allí indicamos que la cantidad de discos a vender debe ser menor o igual a la cantidad indicada en la celda F13 (máximo discos SSD).

• $F$18 <= $F$11: allí indicamos que el total de elementos a vender debe ser menor o igual a la cantidad indicada en la celda F11 (máximo total de ítems).

• $F$19 <= $F$15: allí indicamos que la cantidad de ítems internos vendidos debe ser menor o igual a la cantidad indicada en la celda F15 (máximo ítems internos).

 

Pulsamos en “Resolver” y Solver se encargará del análisis el cual si es correcto lanzará el siguiente mensaje:

 

 

 

Allí podemos seleccionar si deseamos el tipo de informe a usar. Pulsamos en Aceptar y veremos el análisis realizado por Solver en Excel:

 

 

 

Si hemos optado por usar la opción de informe esta estará disponible en una hoja independiente y su formato será el siguiente:

 

 

Solver dispone de los siguientes métodos de resolución:

 

 

 

 

Al usar alguno de estos métodos, podemos ver que el frente de éste está el botón “Opciones” el cual nos permite configurar sus variables según consideremos necesario:

 

 

Después de ejecutar la solución a través de Solver será posible guardar dicho proyecto o bien cargar alguno ya almacenado, para esto pulsamos en el botón “Cargar / Guardar”:

 

 

Se desplegará la siguiente ventana donde definimos el rango con el modelo de Solver a guardar. Pulsamos en Guardar para aplicar los cambios.

 

 

 

 

Así, hemos visto como Solver es una solución más que práctica para el análisis y proyección de datos lo cual será muy necesario para la gestión y administración a futuro.