Cómo Usar Solver Excel 2016 O 2019: Tutorial Paso A Paso!

A continuación en este tutorial vas a aprender cómo configurar y usar solver Excel 2016 o 2019. Microsoft Excel ha crecido a lo largo de los años, introduciendo o mejorando nuevas funciones y fórmulas para mejorar la gestión diaria de los datos.

Esto se debe a que puedes enfrentarte a grandes cantidades de datos, ya sean numéricos, de texto o de fechas, en los que si alguno de ellos falla o se desconfigura, se desencadenan una serie de errores que pueden darte un serio dolor de cabeza.

Índice

    Mejoras de Excel

    Solver de Excel
    Solver de Excel

    Microsoft Excel está avanzando y va mucho más allá de las funciones y las fórmulas. Muchos son los usuarios que no se dan cuenta del hecho de que Excel integrará soluciones prácticas y verdaderas con las que realizar una hipótesis basada en la función de los datos introducidos. Esto es realmente útil para cualquiera que necesite estar seguro de cuánto puede gastar, vender o gestionar de una manera determinada.

    Esto se consigue gracias a la función Solver que puedes instalar en Microsoft Excel 2016 o Excel 2019, y que te será de gran ayuda para este tipo de tareas.

    Es este post te explicaremos con detalle cómo usar solver Excel 2016 o 2019 y así conseguir un punto más de administración y control en esta valiosa herramienta de la suite Office. El proceso es parecido al de Microsoft Excel 2016.

    ¿Qué es Solver Excel?

    Solver es un complemento creado por Microsoft como complemento de Excel que se usaría  para realizar análisis "what if". Cuando se implementa y se usa Solver, va a ser posible determinar el valor óptimo, mínimo o máximo, previsto para una fórmula en una celda. Esta celda (llamada celda de destino) está sujeta a las restricciones de los valores de otras celdas de fórmula en la hoja de trabajo.

    El objetivo principal de Solver es modelar y optimizar varios modelos de negocio y modelos de ingeniería. Además Solver trabaja con un grupo de celdas, llamadas celdas de variables de decisión, que se utilizan para calcular las fórmulas en las celdas de destino.

    El solucionador “Solver” se encarga de ajustar los valores de las celdas de las variables de decisión para que coincidan con las restricciones de las celdas de las restricciones y, últimamente, generar el resultado que se espera en la celda objetivo. De hecho, Solver sería un excelente aliado para establecer el valor máximo o mínimo de una celda cambiando los valores de otras celdas. Consta además de tres elementos:

    • Celdas variables.
    • Celdas restringidas
    • Celda objetivo

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

    En este primer punto re vamos a explicar cómo activas y luego cómo usar solver Excel 2016 o 2019. Los pasos son los siguientes:

    1. Primero tienes que activar el complemento Solver en el propio Microsoft Excel, para ello debes dirigirte al menú Archivo donde verás lo siguiente.
    Haz clic en la sección Opciones
    Haz clic en la sección Opciones
    1. Estando allí debes hacer clic en la sección Opciones y en la ventana que se abre, verás la sección Complementos y en el panel central elige Solver.
    Debes elegir Solver
    Debes elegir Solver
    1. En la zona inferior, haz clic en el botón "Ir" situado en la sección "Administrar" y activa el campo "Resolver" en la ventana emergente.
    Complementos y luego Solver
    Complementos y luego Solver
    1. Haz clic en Aceptar para aplicar los cambios. Ahora estando en el menú "Datos", grupo "Análisis" encontrarás la opción "Solver".
    Ahora encontrarás la opción "Solver"
    Ahora encontrarás la opción "Solver"

    Puedes leer: ¿Cómo Insertar PDF En Un Documento De Word?

    2. Cómo usar solver Excel 2016 o 2019

    A continuación vas a aprender cómo usar solver Excel 2016 o 2019:

    Paso 1

    Para utilizar Solver en Excel 2019, tienes esta información:

    • Una lista de sistemas o aplicaciones
    • Una columna con el precio de cada
    • Una lista de unidades para cada uno de los elementos artículo
    • Costo total

    Paso 2

    Ahora, utilizando tales datos, en la columna de Costo Total, multiplica el precio por unidad por las unidades utilizando la siguiente fórmula:

    =B7*C7

    Puedes arrastrar y soltar la fórmula en todas las celdas de abajo para el copiado las fórmulas.

    Arrastra y suelta la fórmula
    Arrastra y suelta la fórmula

    Paso 3

    Ahora debes añadir una nueva fila llamada "Presupuesto total" donde se va a añadir todo el rango de la columna "Total" con la siguiente fórmula:

    =SUMA(D3:D8)

    Pon la siguiente fórmula
    Pon la siguiente fórmula

    Paso 4

    Ahora dirígete al menú "Datos", grupo "Análisis", y haz clic en "Solver" y se mostrará el siguiente asistente.

    Asistente de Solver
    Asistente de Solver

    Paso 5

    Allí especificaras la celda de destino, el campo "Establecer objetivo" y en este caso selecciona la celda C11. A continuación vas a poder establecer el objetivo, el campo "Para" ya sea un máximo, un mínimo o un valor específico según los criterios de los resultados, para este ejemplo activaremos el campo "Máximo". El próximo paso es establecer las celdas variables, para ello debes ir al campo "Combinando las celdas variables" y elige allí el rango que deseas, que en este caso va a ser la columna Precio por Unidad USD.

    Combinando las celdas variables
    Combinando las celdas variables

    Paso 6

    Bien, entonces es el momento de definir las restricciones. En este ejemplo, suponemos que hay un límite de compras de 10.000 dólares. Para poner este límite, haz clic en el botón "Agregar" en el campo "Sujeto a las restricciones" y en la ventana emergente debes definir lo siguiente:

    1. En el campo "Referencia de celda", ingresa la celda "Presupuesto total".
    2. Asigna una restricción menor o igual a (<=).
    3. En el campo Restricción asigna un valor máximo, usando en este caso 10000.
    Llena el campo "Referencia de celda"
    Llena el campo "Referencia de celda"

    Paso 7

    Haz clic en "Añadir" o Agregar para aplicar los cambios. Ahora la próxima restricción será que tanto el sistema como las aplicaciones se vendan como un conjunto, vuelve a presionar el botón "Añadir" y esta vez elige el rango de unidades y escoge el valor "int (entero)". Este último parámetro es opcional.

    Agregar restricción
    Agregar restricción

    Paso 8

    Haz clic en Agregar, y seguidamente debes definir la cantidad mínima de cada sistema o aplicación a utilizar, para ello tienes lo siguiente.

    • 3 Office 2019
    • 2 Windows 10
    • 1 macOS Mojave
    • 1 Suite de Adobe
    • 2 Windows Server
    • 2 Camtasia

    Paso 9

    Haz clic en "Agregar" y haz lo siguiente:

    1. En el campo que dice "Referencia de celda", introduces la celda de cada sistema o app en la columna "Unidades", por ejemplo, para Office 2019 sería C3, para Windows 10 sería C4, entre otros.
    2. Asigna un parámetro menor o igual a (<=) y especifica la cantidad máxima en el campo "Restricción".
    Pon la cantidad máxima en el campo "Restricción"
    Pon la cantidad máxima en el campo "Restricción"

    Paso 7

    Repite este proceso para cada uno de los elementos. Luego de que hayas completado este proceso, verás algo parecido a esto.

    Repite el proceso para ver esto
    Repite el proceso para ver esto

    Nota: Las opciones de restricción disponibles son las siguientes:

    • <=: menor o igual que.
    • =: lo mismo que y
    • >=: mayor o igual que
    • int: número entero
    • bin: binario
    • diff: diferencia

    Paso 8

    Una vez determinado esto, haz clic en el la opción conservar solución de "Solver" para iniciar el análisis y luego te aparecerá la siguiente ventana:

    Conservar solución de "Solver"
    Conservar solución de "Solver"

    Paso 9

    Aquí tenemos las siguientes opciones:

    • Si queremos guardar los valores de la solución en una hoja de cálculo, haz clic en "Guardar solución para la solución".
    • Si quieres restablecer los valores originales antes de hacer clic en "Solver", haz clic en "Restaurar valores originales".
    • Para interrumpir el proceso de resolución, es necesario pulsar Esc, debes saber que Excel actualizará la hoja de cálculo con los últimos valores hallados para las celdas de la variable de decisión.
    • Para hacer un informe basado en la solución luego de que Solver encuentre la solución, elige el tipo de informe en el campo "Informes" y haz clic en "Aceptar". El informe se crea en una nueva hoja del libro, si acaso Solver no localiza la solución, la opción de creación del informe no se encontrará disponible.
    • Para almacenar los valores de las celdas de las variables de la solución como un evento para su uso futuro, es necesario hacer clic en "Guardar escenario" en el cuadro de diálogo Resultados de Solver, y seguidamente debes introducir el nombre del escenario en el campo "Nombre del escenario".

    Paso 10

    En este caso, elige la opción "Guardar solución de Solver" y haz clic en Aceptar para ver los resultados.

    Haz clic en Aceptar para ver los resultados
    Haz clic en Aceptar para ver los resultados

    Paso 11

    Como puedes observar, Solver se encarga de forma automática del análisis de la cantidad máxima en función de los criterios elegidos. Observa otro ejemplo de la utilidad de Solver en todo el proceso de análisis. En este caso, tienes los siguientes datos.

    Tienes los siguientes datos
    Tienes los siguientes datos

    Paso 12

    Allí tienes la siguiente información:

    • Precios de elementos como procesadores, discos SSD y memoria RAM.
    • El subtotal de cada elemento se determinó multiplicando la cantidad por el precio por unidad.
    • Asigna un total de ventas sumando cada uno de los subtotales.
    • Se aplicaron restricciones al total máximo de ventas, al número máximo de cada uno de los elementos y al número máximo de unidades internas (discos y memoria).
    • En la parte final, se ha colocado la suma de todos los elementos que Solver va a calcular, así como solamente el filtro de elementos internos.

    Paso 13

    Como en el paso anterior, observamos al menú Datos y en el grupo Análisis elige Solver y allí debes definir los siguientes parámetros:

    • En la sección "Establecer objetivo", debes añadir la celda deseada, en este caso F5 (Ventas totales).
    • En el campo "Cambiar celdas variables", añade cada celda asignada a los totales intermedios.
    • En las restricciones añade lo siguiente.
    Debes definir los siguientes parámetros
    Debes definir los siguientes parámetros

    Paso 14

    En el área de restricciones, usa los siguientes parámetros para entender cómo funciona:

    • $ B $ 13 <= $ F $ 14: aquí especificamos que la cantidad de memoria a vender debe ser menor o igual a la cantidad especificada en la celda F14 (memoria RAM máxima).
    • $ B $ 9 <= $ F $ 13: aquí especificamos que el número de discos a vender debe ser menor o igual al número especificado en la celda F13 (número máximo de discos SSD).
    • $ F $ 18 <= $ F $ 11: Aquí especificamos que el número total de elementos vendidos debe ser menor o igual a la cantidad especificada en la celda F11 (número máximo de artículos).
    • $ F $ 19 <= $ F $ 15: Aquí se especifica que el número de ítems vendidos debe ser menor o igual a la cantidad especificada en la celda F15 (el número máximo de ítems internos).

    Paso 15

    Debes hacer clic en "Resolver" y el solucionador hará el análisis, que, si es verdadero, va a generar el siguiente mensaje.

    Haz clic en Aceptar y verás el análisis realizado
    Haz clic en Aceptar y verás el análisis realizado

    Paso 16

    Aquí puedes elegir si queremos utilizar el tipo de informe. Haz clic en Aceptar y verás el análisis realizado por el Solver en Excel.

    Análisis realizado por el Solver en Excel
    Análisis realizado por el Solver en Excel

    Paso 17

    Si eliges la opción de informe, estará disponible en una hoja separada y tiene el siguiente formato.

    Opción de informe
    Opción de informe

    Paso 18

    El solucionador tiene los siguientes métodos de resolución.

    GRG Nolinear

    Este tipo de método se utiliza para problemas no lineales, o sea, cuando al menos una de las restricciones es una función no lineal homogénea de las variables de solución.

    LP Simplex

    Se basa en el algoritmo Simplex desarrollado por el matemático estadounidense George Danzig, este método se utiliza para resolver inconvenientes de programación lineal, donde los modelos matemáticos se caracterizan por dependencias lineales, o sea, constan de un único objetivo representado por una ecuación lineal la que hay que maximizar o minimizar.

    Evolutionary

    Se utiliza para problemas de optimización que son más difíciles de solucionar porque algunas funciones pueden ser discontinuas y, por tanto, será más difícil determinar la dirección en la que la función aumenta o disminuye.

    Paso 19

    Al utilizar cualquiera de estos métodos, puedes notar que hay un botón de "Opciones" delante que te permite ajustar tus variables como creas necesario.

    Haz clic en el botón de "Opciones"
    Haz clic en el botón de "Opciones"

    Paso 20

    Luego de ejecutar la solución por medio de Solver, puedes guardar el proyecto especificado o cargar uno ya guardado, para ello haz clic en el botón "Load/Save":

    Haz clic en el botón "Cargar/Guardar"
    Haz clic en el botón "Cargar/Guardar"

    Paso 21

    Define el rango con el modelo de solver
    Define el rango con el modelo de solver

    Se abre la siguiente ventana en la que debes definir el rango con el modelo de solver a guardar. Haz clic en "Guardar" para aplicar los cambios.

    TE PUEDE INTERESAR

    Deja un comentario

    Tu dirección de correo electrónico no será publicada.

    Go up