Cargando

Ir a contenido

X

 


Cómo usar solver en Excel 2019 o Excel 2016

Tutorial con vídeo para poder configurar y usar la herramienta solver dentro de Excel 2019 o Excel 2016 .


Escrito por ene 21 2019 11:51 excel



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.

 

Te explicamos cómo crear en Excel 2016 listas desplegables con validación de datos paso a paso.

 

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.

 

Qué es Solver
Solver es un complemento desarrollado por Microsoft como un complemento de Excel mediante el cual será posible ejecutar un análisis y si (what-if). Cuando implementamos y usamos Solver, será posible detectar un valor óptimo, ya sea mínimo o máximo, destinada a una fórmula en una celda. Esta celda (denominada celda objetivo), está sujeta a limitaciones en los valores de otras celdas de fórmula de una hoja de cálculo.

 

El propósito principal de Solver es la simulación y optimización de diversos modelos de negocios e ingeniería. Solver trabaja con un grupo de celdas denominadas celdas de variables de decisión las cuales se usan para calcular fórmulas en las celdas objetivo.

 

Solver se encarga de ajustar los valores de las celdas de variables de decisión con el objetivo que estas cumplan con los límites de las celdas de restricción y finalmente generen el resultado que esperamos en la celda objetivo. Básicamente Solver será un gran aliado para determinar el valor máximo o mínimo de una celda modificando el valor de otras celdas. Solver se compone de tres elementos que son:

  • Celdas variables.
  • Celda restringida
  • Celda objetivo

 

 

Para estar al día, recuerda suscribirte a nuestro canal de YouTube!   SUSCRIBETE

 

 


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

 

Paso 1

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:

 

 

Paso 2

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

 

 

Paso 3

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

 

3-Cómo-activar-Solver-en-Excel-2016-o-2019.png

 

Paso 4

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

 

Paso 1

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

 

Paso 2

Ahora, con estos datos, en la columna Costo total multiplicaremos el precio unitario por las unidades con la siguiente fórmulas:
=B7*C7
Nota
Podemos arrastrar esta fórmula a todas las celdas inferiores para copiar las fórmulas.

 

 

 

Paso 3

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)

 

Paso 4

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

 

7-Usar-Solver-en-Excel-2016-o-2019.png

 

Paso 5

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:

 

8-Cambiando-las-celdas-de-variables”.png

 

Paso 6

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.

 

 

9-Cambiando-las-celdas-de-variables”.png

 

Paso 7

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

 

10-Cambiando-las-celdas-de-variables”.png

 

Nota
Este último parámetro es opcional.

 

Paso 8

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

 

Paso 9

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

 

 

11-Referencia-de-celda”.png

 

Paso 7

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

 

12-Referencia-de-celda”.png

 

 

Nota
as opciones disponibles de restricción son:
  • <= (menor o igual que
  • =: igual que
  • >=: mayor o igual que
  • int: entero
  • bin: binario
  • dif: diferencia

 

Paso 8

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

 

13-Referencia-de-celda-excel-solvber.png

 

Paso 9

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.

 

Paso 10

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

 

 

 

Paso 11

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:

 

 

Paso 12

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.

 

Paso 13

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.

 

 

 

16-Conservar-solución-de-Solver.png

 

 

Paso 14

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).

 

Paso 15

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

 

17-análisis-realizado-por-Solver-en-Excel.png

 

 

Paso 16

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

 

 

 

Paso 17

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

 

 

Paso 18

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

 

GRG Nonlinear
Este tipo de método se usa para problemas no lineales, es decir, en los que al menos una de las restricciones es una función no lineal uniforme de las variables de decisión.

 

LP Simplex
Está basado en el algoritmo Simplex desarrollado por el matemático estadounidense George Dantzig, este método se usa para resolver problemas de programación lineal, allí, los modelos matemáticos se caracterizan por relaciones lineales, es decir, consisten en un solo objetivo representado por una ecuación lineal la cual debe maximizarse o minimizarse.

 

Evolutionary
Es usado para el tipo de problemas de optimización más complejos de resolver ya que algunas de las funciones pueden ser discontinuas, y de este modo será más complejo determinar la dirección en la que una función está aumentando o disminuyendo.

 

Paso 19

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:

 

20-informe-solver-excel-2019.png

 

Paso 20

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

 

21-análisis-realizado-por-Solver-en-Excel-guardar.png

 

Paso 21

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

 

 

22-análisis-realizado-por-Solver-en-Excel-219-2016.png

 

 

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.

 

Tutorial que explica las diferentes formas de cortar, copiar o pegar en Excel 2019 de Microsof Office paso a paso



AYUDA A MEJORAR ESTE TUTORIAL!

¿Crees que puedes corregir o mejorar este tutorial? Puedes enviar tu Edición con los cambios que consideres útiles.

0 usuarios han editado este tutorial. ¡Edita y conviértete en un reconocido experto!


Tutoriales Relacionados



Sin comentarios, sé el primero!

No esperes más y entra en Solvetic
Deja tus comentarios y aprovecha las ventajas de la cuenta de usuario ¡Únete!
10
VOTA
5
100%
4
0%
3
0%
2
0%
1
0%

  Información

  •   Publicado ene 21 2019 11:51
  •   Actualizado ene 21 2019 12:46
  •   Visitas 2.3K
  •   Nivel
    Avanzado