Cómo utilizar Solver de Excel para Programación Lineal: Resolución de Problemas de Optimización
basHoy vamos a explicar una función avanzada de Microsoft Excel. He aquí una guía completa sobre Excel Solver Programación Lineal para resolver problemas de optimización. Solver le ayudará a resolver problemas lineales y no lineales.
Comencemos.
Tabla de contenido
1. ¿Qué es Excel Solver?
2. ¿Cómo añadir Solver a Excel?
3. ¿Cómo añadir Solver a Excel en mi Mac?
4. ¿Dónde está Solver en Excel?
5. ¿Cómo utilizar Solver de Excel para problemas de optimización?
6. ¿Qué es la Optimización y la Programación Lineal?
7. Ejemplo de la vida real
8. Palabras finales
1. ¿Qué es Excel Solver?
Excel Solver es una herramienta que puede añadir a Microsoft Excel. Cuando necesite encontrar la mejor respuesta para un problema con ciertas restricciones, ésta es una potente herramienta que puede utilizar.
Básicamente, le permite sacar el máximo o el mínimo de un determinado número (normalmente el coste o el beneficio) dentro de un conjunto de variables y límites. Mucha gente en áreas como las finanzas, la gestión de operaciones y la ingeniería utiliza Solver para realizar análisis de sensibilidad, programación lineal y resolución de problemas no lineales.
2. ¿Cómo añadir Solver a Excel?
En primer lugar, empecemos por añadir Solver a Excel. Solver es un complemento, por lo que probablemente no vea la pestaña Solver en su menú principal por defecto. Si es así, añada en primer lugar esta útil herramienta de optimización a su libro de Excel.
Para añadir el complemento Solver a Excel:
#Paso1: Abra el cuadro de diálogo Opciones
Para acceder al menú de opciones,vaya a Archivo > Opciones
#Paso2: Administrar complementos
En el cuadro de diálogo Opciones de Excel, vaya a Complementos > Administrar complementos de Excel y haga clic en Ir.
#Paso3: Añada el complemento Solver a su libro de Excel
Marque la casilla Complemento Solver en el cuadro Complementos disponibles y pulse Aceptar.
Excel lo instalará y lo encontrará en la pestaña «Datos», en el grupo «Análisis».
Ahora ya dispone de esta fantástica herramienta de optimización en su libro.
3. ¿Cómo añadir Solver a Excel en mi Mac?
Si es usted usuario de Mac, la instalación de Solver puede ser un poco diferente a la de Windows.
Para añadir Solver a Excel en Mac:
- Abra la aplicación Excel en su MacBook
- En Excel, vaya al menú «Herramientas».
- Haga clic en «Complementos para Excel».
- Marque la casilla junto a «Solver» en la lista de complementos que puede utilizar.
Cuando haga clic en «Aceptar», Excel activará el complemento Solver. Solver se encuentra ahora en la pestaña «Datos».
4. ¿Dónde está Solver en Excel?
Solver se encuentra en la pestaña» Datos» de Excel, que forma parte del grupo «Análisis». Una vez que haya añadido Solver a Excel, haga clic en «Solver» en la pestaña «Datos» para acceder a él.
Asegúrese de que está activado en los complementos de Excel si no puede verlo.
Ahora ya ha configurado su Excel Solver y sabe dónde está. ¡Veamos cómo utilizarlo!
5. ¿Cómo utilizar Excel Solver para problemas de optimización en programación lineal?
Así pues, antes de sumergirnos en un caso práctico, queremos dar una idea general sobre cómo utilizar Solver en Excel.
Así que primero conozcamos los conceptos de Solver.
¿Qué son los parámetros de Solver en Excel?
En Excel, los Parámetros Solver son:
- Establecer Objetivo: Elija la celda que desea mejorar.
- Objetivo: Elija si desea fijar la celda objetivo en un valor determinado, maximizar o minimizar.
- Cambiando Celdas Variables: Estas son las celdas que el solucionador cambiará para alcanzar el objetivo.
- Sujeto a las Restricciones: Son las reglas o restricciones que debe seguir la respuesta.
- Elija una Forma de Resolver: Puede elegir Simplex LP para problemas lineales, GRG No lineal para problemas no lineales, o Evolutivo para problemas cuyas respuestas no están claras o son aproximadas.
Así que mientras construye su modelo, debería definir al menos su Objetivo, las Celdas Variables y las Restricciones.
Construyamos ahora un modelo muy sencillo para ver cómo funciona el Solver.
#Paso1: Defina su problema
Asegúrese de aclarar su objetivo, variables y restricciones. Ahora vamos a empezar Excel Solver Programación Lineal simple caso de estudio.
Ejemplo de problema: Tenemos dos productos diferentes, con costes unitarios y precios unitarios distintos. Tenemos una capacidad de producción total de 2.000 unidades. Queremos encontrar los niveles óptimos de producción de cada producto para alcanzar unos ingresos netos de 6.000 $.
#Paso 2: Abra la ventana Solver
Ahora abriremos primero nuestra ventana Solver.
#Paso3: Introduzca sus objetivos y variables
Estableceremos objetivos y variables.
En este ejemplo nuestra celda objetivo es la celda de cálculo de Ingresos Netos. Entonces diremos que nuestra celda objetivo debe ser $6.000.
Y por último, seleccionará las celdas variables. En nuestro ejemplo seleccionamos como variables los niveles de producción de cada producto.
#Paso 4: Añada sus restricciones
Ahora añadiremos nuestras restricciones utilizando los parámetros de la ventana Añadir restricción.
En nuestro ejemplo, nuestra única restricción es la capacidad de producción. Aquí nuestra referencia de celda no debe ser superior a 2.000.
#Paso5: Elija su método de resolución
El Solver de Excel le ofrece tres opciones diferentes para resolver su problema.
No entraremos en detalles matemáticos aquí pero básicamente se utilizan para:
- Gradiente Reducido Generalizado (GRG): Para problemas que son no lineales suaves.
- LP Simplex: Para problemas que son lineales.
- Evolutiva: Para problemas que no son suaves.
Eso es todo.
Ahora Solver ha resuelto este problema y nos da los niveles óptimos de producción:
Ahora puede conservar su solución Solver o Restaurar valores originales y volver al cuadro de diálogo Solver.
6. ¿Qué es la optimización y la programación lineal?
Un método matemático llamado programación lineal se utiliza para encontrar la mejor solución en un modelo en el que los vínculos entre las partes son lineales.
Es el proceso de encontrar la mejor solución para un problema lineal que está limitado por un conjunto de límites lineales. En este caso, optimizar significa hacer más grande o más pequeña la función objetivo, como ganar la mayor cantidad de dinero o reducir los costes al máximo.
7. Ejemplo de la vida real para resolver un problema de optimización con Excel Solver
Ahora le daremos un ejemplo de la vida real para resolver problemas más complejos. Este es un problema más complejo que un ejemplo básico de programación lineal con Excel Solver.
En este caso práctico, uno de nuestros clientes nos pidió que preparáramos una simulación en Excel para probar su algoritmo antes de invertir en una solución de software más cara.
Volvamos a nuestro caso práctico.
El caso: Tenemos una aplicación de emparejamiento, que empareja a personas (mujeres con hombres) según sus aficiones.
Problema: Tenemos 50 mujeres y 50 hombres en nuestra cartera y conocemos sus valores y aficiones. Queremos hacer emparejamientos óptimos para conseguir la máxima felicidad para todos los usuarios.
A continuación presentamos visualmente nuestros datos de origen y la tabla de correspondencias resultante. Intentaremos que esta tabla tenga la máxima felicidad.
¿Cuál es la crítica sobre el modelado de optimización?
Es fácil encontrar una coincidencia para un solo usuario con el máximo de puntos en común. Pero esto no garantizará la máxima felicidad para toda nuestra base de datos de usuarios.
Así que si emparejamos a las personas con la puntuación más alta, ¡puede que queden prospectos de parejas muy poco complacientes!
Es decir, si una pareja tiene 9 emparejamientos pero la otra pareja sólo tiene 3 emparejamientos, ésa es la solución ideal. Preferiremos que ambas parejas tengan al menos 7 como puntuación de coincidencia.
Entonces tenemos dos restricciones:
- Deberíamos maximizar la puntuación media de emparejamiento en todo el grupo
- Deberíamos tener una puntuación mínima de emparejamiento de X
Entonces, ¿qué hemos hecho?
Primero hemos creado una matriz de emparejamiento, en la que podemos ver las puntuaciones de emparejamiento de todas las parejas posibles de forma dinámica.
Después hemos introducido un área de cálculo en el lado izquierdo con cálculos matemáticos muy sencillos:
Para no repetir esta tarea cada vez, también añadimos un código VBA para automatizar este proceso:
Ahora, intentaremos repasar los niveles óptimos para las dos restricciones explicadas anteriormente:
En resumen:
Esta herramienta básicamente:
- Coloca a todos los usuarios en la matriz
- Calcula las puntuaciones de coincidencia de forma dinámica.
- Ejecuta Solver para probar la optimización en busca de la mejor solución en los miles de combinaciones
- A continuación, encuentra la solución más adecuada para nosotros y las escribe en la tabla final.
Esta es la vista de la tabla de correspondencias:
Nuestro cliente tenía aquí un equipo de desarrolladores de muy alto nivel, y realizaron simulaciones basadas en nuestro modelo e intentaron encontrar el mejor algoritmo para su aplicación.
¿A que Excel es impresionante?
Si quiere mejorar sus conocimientos de Excel sobre funciones, debería probar nuestra herramienta interactiva Entrenador de fórmulas de Excel para practicar sus conocimientos y velocidad con las funciones:
– Este es el menú principal de la herramienta Excel Formulas Trainer de Someka con 30 ejercicios en tres niveles –
8. Palabras finales para Excel Solver Linear Programming
Como conclusión, la función Solver de Microsoft Excel es un dispositivo muy flexible y fuerte que puede manejar una gran cantidad de diferentes problemas de optimización.
Al estar integrada en Excel, Solver no sólo es potente sino también fácil de usar para las personas que ya están familiarizadas con el entorno Excel. Este artículo ofrece una guía paso a paso sobre cómo utilizar las funciones de Solver, desde la configuración del problema hasta la comprensión del significado de los resultados.
Así que esperamos que este artículo de programación lineal de Excel Solver con pasos prácticos y casos prácticos le ayude a diseñar modelos de optimización para sus problemas lineales y no lineales.
Lecturas recomendadas:
Guía profesional: Analista de datos
¿Cómo aleatorizar una lista en Excel?: Una guía completa
10 trucos de Excel imprescindibles que los expertos utilizan a diario
Puestos relacionados
