Solucionador de Excel: Optimiza decisiones complejas con múltiples variables

El análisis hipotético te permite probar diferentes escenarios para ver cómo influyen en tus resultados. Te ofrece un espacio seguro para experimentar con variables como los costes o las capacidades y descubrir la solución más eficaz. Este enfoque es fundamental para cualquiera que gestione recursos, planifique calendarios o tome decisiones empresariales estratégicas.

El método manual de prueba y error lleva horas y, a menudo, no da con la respuesta óptima. Solver garantiza la optimización matemática en cuestión de segundos. Esa es la diferencia entre adivinar la asignación de recursos y saber que estás tomando la mejor decisión posible.

Este tutorial te guía a través de Excel Solver, la función avanzada creada para estos problemas de optimización. Te mostraré cómo configurar tu hoja de cálculo, ejecutar el análisis e interpretar los resultados utilizando ejemplos del mundo real.

Comprender el Solucionador de Excel: Conceptos fundamentales

Antes de mostrarte cómo utilizar Solver, déjame explicarte los tres componentes básicos de todo problema de optimización. No son solo palabras de moda, son lo que hace que Solver funcione.

Célula objetivo, variables de decisión y restricciones

Piensa en el objetivo « » como tu meta. Es la fórmula que deseas maximizar o minimizar, como el beneficio total o el coste total. Tiene que ser una fórmula, no solo un número, porque Solver necesita ver cómo cambia cuando ajustas los valores.

Las variables de decisión son lo que estás tratando de averiguar. ¿Cuántas sillas debemos fabricar? ¿Cuánto debemos gastar en cada canal de marketing? Siempre empiezo con el valor cero, lo que facilita ver qué ha cambiado después de ejecutar el Solucionador.

Las restricciones son tus límites y reglas. No puedes gastar más de lo que tienes presupuestado. No puedes producir cantidades negativas. Algunos proyectos requieren niveles mínimos de inversión. Estas restricciones convierten tu problema matemático en algo realista que refleja las limitaciones reales del negocio.

Así es como funciona: El solucionador ajusta tus variables de decisión para optimizar tu objetivo, al tiempo que se asegura de que se cumplan todas las restricciones. Si has realizado investigaciones operativas, esto te resultará familiar. Si no es así, considéralo como un proceso inteligente de prueba y error con garantías.

Comparación con la búsqueda de objetivos

Me preguntan sobre esto. La búsqueda de objetivos es más sencilla. Cambia una celda para alcanzar un objetivo específico. Úsalo cuando sepas la respuesta que deseas y solo necesites encontrar la entrada que te lleve hasta ella.

Solver es diferente. Encuentra la mejor respuesta cuando tienes que tomar múltiples decisiones y cumplir con ciertas restricciones. ISolver se utilizasi necesitas maximizar o minimizar algo mientras manejas varias variables.

Mi regla general: si puedes decir exactamente qué número quieres, utiliza la función Buscar solución. Si te estás preguntando «¿cuál es la mejor manera de hacerlo?», utiliza Solver.

Carga y activación del complemento Solver

Solver viene con Excel, pero primero debes activarlo. No te preocupes, te llevará menos de un minuto.

Pasos de instalación para Windows y Mac

Para Windows:

1. Archivo > Opciones > Complementos
2. Selecciona Complementos de Excel en el menú desplegable Administrar y haz clic en Ir.
3. Comprobar complemento Solver, haz clic en Aceptar.
4. Ve a la pestaña Datos y busca Solucionador en la grupo .

Para Mac:

1. Herramientas > Excel Complementos
2. Comprobar complemento Solver, haz clic en Aceptar

Si utilizas Excel Online, tengo malas noticias. No es compatible con Solver. Necesitarás la versión de escritorio (2016 o posterior).

Configuración

Una vez activado, puedes acceder a Solver a través de la pestaña Datos > botón Solver. La configuración predeterminada funciona bien para la mayoría de los problemas, así que no le des demasiadas vueltas a esta parte.

Dicho esto, hay dos ajustes que son importantes si te encuentras con problemas. La precisión controla el grado de rigor con el que Solver cumple las restricciones. Aprieto esto para los modelos financieros en los que cada céntimo cuenta. La convergencia indica a Solver cuándo dejar de intentar mejorar la solución. Lo reduzco si Solver se cierra demasiado pronto con una respuesta que no es del todo óptima.

Ambos se encuentran en el cuadro de diálogo Parámetros del solucionador > botón Opciones, pero, en realidad, rara vez los utilizo.

Configuración del problema: Formulación de modelos en Excel

Una hoja de cálculo limpia facilita la configuración de Solver.

Permíteme explicarte con un ejemplo: un fabricante de muebles que produce sillas y tablas. Las sillas generan 60 $ de beneficio (se necesitan 4 horas de trabajo y 2 unidades de madera). Las tablas generan 75 $ de beneficio (se necesitan 6 horas de mano de obra y 2 unidades de madera). Tenemos 240 horas de trabajo y 100 unidades de madera disponibles. ¿Cuántos de cada uno debemos hacer?

Definición del problema y configuración de la hoja de cálculo

Organizo todos los modelos de Solver de la misma manera. Siempre cuatro secciones:

1. Datos de entrada (información sobre el producto, límites de recursos)
2. Variables de decisión (cantidades a producir)
3. Función objetivo (beneficio total)
4. Cálculos de restricciones (recursos utilizados frente a recursos disponibles)

Modelo completo de Solver con las cuatro secciones. Imagen del autor.

Este diseño hace que todo sea transparente. Cualquiera que vea tu modelo puede ver inmediatamente qué estás optimizando y con qué limitaciones estás trabajando.

Ahora, siempre creas lo que se conoce como rangos con nombre para las celdas importantes. En lugar de escribir =B8*B3+B9*B4, puedo escribir =SUMPRODUCT(QuantityToProduce,ProfitPerUnit). Mucho más fácil de entender seis meses después, cuando ya has olvidado lo que significan esas referencias de celda.

Los rangos con nombre facilitan la lectura de las fórmulas. Imagen del autor.

Para crearlos: selecciona las celdas, haz clic en el cuadro de nombre, escribe un nombre y pulsa Intro. Tarda dos segundos y te ahorra dolores de cabeza más adelante.

Variables de decisión y función objetivo

Empiezo las variables de decisión en cero. Esto ayuda a hacer comparaciones. A algunas personas les gusta empezar con valores estimados, lo cual está bien, pero a mí me funciona el cero.

La función objetivo tiene que ser una fórmula. Para nuestro ejemplo de muebles, utilizo:

=SUMPRODUCT(B8:B9,B3:B4)

Esto calcula: (Sillas × 60 $) + (Tablas × 75 $)

Multiplicación y suma simples. Eso es todo lo que necesitas para la mayoría de los problemas empresariales.

Restricciones

Las restricciones son el lugar donde se traducen los límites reales del negocio en matemáticas. Tu fabricante de muebles tiene dos limitaciones de recursos:

• Trabajo: =SUMPRODUCT(B8:B9,C3:C4) <= 240

• Madera: =SUMPRODUCT(B8:B9,D3:D4) <= 100

Y aquí hay algo que aprendí por las malas: siempre hay que añadir restricciones de no negatividad (B8:B9 >= 0). Sin ellos, Solver podría sugerir la creación de sillas negativas, lo que obviamente no funciona en el mundo real.

También añado una columna de estado con fórmulas de =IF(), como =IF(B16<=C16,"OK","EXCEEDED") . Esto me proporciona información visual instantánea incluso antes de ejecutar Solver. Las luces verdes significan que puedes continuar, las rojas significan que hay algún problema con tu configuración.

Paso a paso: Cómo utilizar el solucionador en Excel

Muy bien, el modelo está construido. Ahora te mostraré cómo ejecutar Solver. Esta parte te llevará unos 30 segundos una vez que sepas lo que tienes que hacer.

Uso del cuadro de diálogo Parámetros del solucionador

Abre Solver haciendo clic en la pestaña Datos > botón Solver.

Cuadro de diálogo Solucionador antes de la configuración. Imagen del autor.

Esto es lo que debes rellenar:

Establece el objetivo: Haz clic en este campo y, a continuación, haz clic en la celda B12 (tu celda Total Profit).

To: Selecciona« » (Máximo) porque queremos maximizar los beneficios.

Al cambiar las celdas variables: Selecciona B8:B9 (las cantidades que estás decidiendo).

Sujeto a las restricciones: Haz clic en« » (Añadir restricción ). Añade lo siguientepara cada restricción:

• Trabajo: $B$16 <= $C$16

• Madera: $B$17 <= $C$17

• No negatividad: $B$8:$B$9 >= 0

Selecciona un método de resolución: Elige Simplex LP. Más adelante explicaré los otros métodos, pero para problemas lineales como este, lo que necesitas es Simplex LP.

Solucionador configurado y listo para resolver. Imagen del autor.

Resolución del modelo

Antes de hacer clic en Resolver, echa un vistazo rápido a tus valores actuales. Todo debería estar a cero.

Estado inicial con ceros en todas partes. Imagen del autor.

Ahora haz clic en «Resolver». Para problemas sencillos como el nuestro, esto lleva unos segundos.

¡Éxito! El solucionador ha encontrado la solución óptima. Imagen del autor.

Cuando aparezca el cuadro de diálogo Resultados del solucionador, debería indicar «El solucionador ha encontrado una solución». Marca las casillas« » (Respuesta de la aplicación) y «Sensitivity» (Sensibilidad) en «Reports» (Informes). Estos te proporcionan un análisis detallado que veremos más adelante. A continuación, haz clic en Aceptar.

La solución óptima: 30 sillas y 20 tablas. Imagen del autor.

Y ahí lo tienes. La solución muestra que deberíamos fabricar 30 sillas y 20 tablas para obtener un beneficio de 3300 $. Observa que ambas restricciones muestran «OK», lo que significa que estamos utilizando nuestros recursos de manera eficiente sin exceder los límites.

Resumen del flujo de trabajo

Este es el proceso que sigo siempre:

1. Construye el modelo (entradas, variables, objetivo, restricciones).
2. Configurar el solucionador (establecer qué optimizar y qué ajustar)
3. Haz clic en Resolver y espera.
4. Comprueba si la solución tiene sentido desde el punto de vista empresarial.
5. Revisa los informes para obtener información más detallada.

Ese último paso es importante. El hecho de que Solver haya encontrado una respuesta no significa que sea la respuesta adecuada para tu negocio. Comprueba siempre que los resultados sean correctos.

Métodos de resolución, parámetros y opciones avanzadas

Solver te ofrece tres algoritmos diferentes. Elegir el adecuado marca una gran diferencia en cuanto a velocidad y precisión.

Resumen de los métodos de resolución

Tres métodos de resolución para diferentes tipos de problemas. Imagen del autor.

Simplex LP

Simplex LP es lo que utilizo el 80 % de las veces. Es para problemas lineales en los que todo es suma y multiplicación. ¿Gama de productos? ¿Asignación presupuestaria? ¿Planificación de recursos? Simplex LP. Es rápido y te garantiza la mejor respuesta.

GRG No lineal

GRG Nonlinear se utiliza cuando hay curvas, cuadrados o exponenciales en las fórmulas. Piensa en modelos de precios en los que la demanda disminuye a medida que aumenta el precio, pero no de forma lineal. O optimización de portafolio con cálculos de varianza. Encuentra óptimos locales, que pueden no ser los mejores en términos absolutos, pero que suelen ser suficientemente buenos.

Evolutivo

La opción evolutiva es la opción de la fuerza bruta. Úsalo cuando tengas restricciones de números enteros (solo puedes crear unidades completas), decisiones binarias (sí o no) o instrucciones « =IF() » en tus fórmulas. Es más lento porque básicamente prueba un montón de soluciones aleatorias y se queda con las buenas. , funciona cuando los otros métodos fallan.

Tu regla de decisión es sencilla: empieza con Simplex LP. Si tus fórmulas contienen exponentes o productos de variables, cambia a GRG No lineal. Si necesitas variables enteras o binarias, opta por Evolutionary.

Opciones y configuración del solucionador

Haz clic en Opciones ar en el cuadro de diálogo Solver para acceder a la configuración avanzada. Rara vez me meto en estos temas, pero esto es lo que importa:

Limita el tiempo de ejecución de Solver con Max Time. El valor predeterminado es 100 segundos, lo cual es suficiente para la mayoría de los problemas. Lo aumento para modelos realmente grandes que necesitan más tiempo para pensar.

Limita el número de intentos que realiza el solucionador con iteraciones. Si se alcanza este límite sin resolverlo, súbelo.

Controla el nivel de rigor del solucionador con respecto a las restricciones mediante la precisión. Lo aprieto para los modelos financieros en los que cada céntimo cuenta, pero por lo demás lo dejo como está.

Generación de informes y análisis de sensibilidad

¿Recuerdas cuando te dije que revisaras esas casillas del informe? Esto es lo que obtienes.

El informe de respuesta documenta la solución. Imagen del autor.

El informe de respuestas es tu rastro documental. Muestra lo que cambió: las variables de decisión pasaron de 0 a 30 sillas y 20 tablas, y los beneficios pasaron de 0 a 3300 dólares. También te indica qué restricciones son vinculantes (se utilizan por completo) y cuáles no lo son (tienen holgura). En nuestro caso, tanto la mano de obra como la madera son vinculantes, lo que significa que estamos utilizando toda la capacidad disponible.

¿Cuánto pueden cambiar los insumos antes de que cambie la solución? Imagen del autor.

El informe de sensibilidad es donde la cosa se pone interesante. Las columnas« » (Aumento/disminución admisible) y « » (Aumento/disminución admisible) muestran cuánto pueden variar tus entradas antes de que necesites una solución diferente. Los rangos amplios significan que tu respuesta es sólida. Los rangos estrechos significan que pequeños cambios pueden cambiarlo todo.

Los precios sombra muestran el valor de los recursos. Imagen del autor.

Pero el verdadero oro es el precio en la sombra. Esto te indica cuánto vale una unidad más de un recurso. Si la mano de obra tiene un precio sombra de 7,5 dólares, cada hora de trabajo adicional añade 7,5 dólares a los beneficios. Así que, si puedes contratar mano de obra temporal a 5 dólares la hora, hazlo. A 10 dólares la hora, paso.

Importancia del análisis de sensibilidad

Utilizo estos informes para responder a tres preguntas. ¿En qué recursos debo invertir? ¿Tu solución es frágil? ¿Qué pasa si tus previsiones son erróneas? Los informes dan respuestas claras a las tres preguntas.

En primer lugar, analizo los precios sombra para decidir en qué recursos debo invertir. Un precio alto significa un valor alto.

A continuación, compruebo los rangos permitidos para ver si mi solución es frágil. Si las ganancias solo pueden variar en 1 dólar antes de que cambie la solución, estás en terreno peligroso. Si puede cambiar 50 dólares, está bien.

Por último, te pregunto ¿qué pasa si mis previsiones son erróneas? Los rangos permitidos indican cuánto te puedes desviar antes de tener que volver a calcular.

Mi consejo: comprueba siempre los precios sombra antes de aprobar un presupuesto. Si un recurso muestra un precio sombra de cero, significa que tienes exceso de capacidad. Invertir más ahí es un desperdicio. Invierte tu dinero donde los precios en la sombra sean más altos.

Solución de problemas y resolución de errores

Los mensajes de error del solucionador pueden parecer alarmantes, pero normalmente son fáciles de solucionar. Estos son los que veo con más frecuencia.

Errores y mensajes comunes

El solucionador no ha podido encontrar una solución viable.

Esto significa que tus restricciones entran en conflicto entre sí.

 Ninguna solución satisface todas las restricciones. Imagen del autor.

Esto suele ocurrir cuando se pide algo imposible, como intentar alcanzar un objetivo de beneficios que los recursos simplemente no pueden respaldar. La solución consiste en eliminar las restricciones una por una hasta que Solver funcione; este proceso identifica exactamente qué restricción está causando el conflicto.

Los valores de las celdas no convergen.

Esto significa que tu objetivo puede crecer indefinidamente. Te falta un límite superior en alguna parte. Los negocios reales siempre tienen límites, añádelos.

El solucionador no puede mejorar la solución actual.

Esto suele ocurrir con problemas no lineales. Prueba con diferentes valores iniciales o cambia al método evolutivo. A veces, un óptimo local es suficiente.

Errores en la formulación del modelo

Referencias circulares

Esto ocurre cuando la celda A depende de la celda B, que a su vez depende de la celda A. Excel te avisará de ello. Utiliza la pestaña Fórmulas > Rastrear precedentes para encontrar el bucle y romperlo.

Restricciones enteras

Añadir restricciones enteras para números enteros. Imagen del autor.

Limitaciones del solucionador y alternativas para problemas a gran escala

Excel Solver es fantástico, pero tiene algunas limitaciones que debes conocer.

Comprender las limitaciones de Solver

El solucionador estándar tiene un máximo de 200 variables de decisión y 100 restricciones. Parece mucho hasta que intentas programar a 200 trabajadores en 50 franjas horarias. Son 10 000 variables, mucho más de lo que Solver puede manejar.

Utilizo Solver para los planes de producción mensuales, los presupuestos trimestrales y la selección de proyectos. Es perfecto para tomar decisiones tácticas. Pero no está diseñado para problemas a escala empresarial, como redes de cadenas de suministro con miles de ubicaciones.

Alternativas para problemas grandes

OpenSolver

OpenSolver es gratuito y elimina esos límites. Maneja tantas variables como permita la memoria de tu ordenador, utiliza la misma interfaz que Excel Solver y se puede descargar desde OpenSolver.org. Lo recomiendo cuando superes las capacidades del Solver estándar.

Frontline Solver Premium

Frontline Solver cuesta dinero (entre 300 y 1500 dólares al año), pero admite miles de variables con mejores algoritmos. Es lo que usaría si me dedicara a esto profesionalmente a tiempo completo.

Software independiente

Herramientas como CPLEX o Gurobi manejan millones de variables, pero requieren conocimientos de programación y una inversión real. Eso es cosa de empresas.

En mi opinión, para la mayoría de los analistas de negocios, Excel Solver cubre el 95 % de lo que necesitas. Si llegas a tus límites, en realidad es una buena señal, ya que estás realizando un trabajo lo suficientemente sofisticado como para justificar el uso de mejores herramientas.

Conclusión

Hemos cubierto muchos temas, desde la configuración básica hasta la interpretación de los precios sombra. 

Ahora, en lugar de debatir opiniones en una sala de reuniones, puedes mostrar los números en la pantalla. «Este es el plan matemáticamente óptimo». Eso cambia la conversación. Deja de adivinar y empieza a demostrar.

Anímate y pruébalo con tu próximo problema complicado, ese conflicto de horarios o esa restricción presupuestaria que has estado evitando. Quizás descubras que la respuesta perfecta estaba escondida en tu hoja de cálculo todo este tiempo.

Y si quieres aprender más técnicas para impresionar a tu jefe, realiza nuestro curso de  curso de Análisis de datos en Excel para seguir mejorando.