¿Por qué el complemento Solver se atasca cuando intento agregar restricciones? Desarrollador de registros, desarrollo de aplicaciones

No creo que pueda usar Solver en modo de cálculo manual. Si bien puede parecer que hace algo, su hoja de cálculo debe volver a calcular después de cada nueva suposición que Solver haga, y eso no está sucediendo en el modo de cálculo manual.

Solver no tiene una visión física de su modelo de Excel, por lo que invariablemente cambia las variables independientes de manera ineficiente. Está bien si su modelo se recalcula rápidamente, pero el suyo necesita 3 minutos cada vez. Por lo tanto, su primer paso debería ser descubrir por qué su modelo se recalcula tan lentamente, y cambiar su enfoque para que se recupere en unos segundos.

He trabajado en modelos convergentes lentos varias veces durante una carrera de cuarenta años como ingeniero. En todos los casos, pude aumentar la velocidad de convergencia al menos en un orden de magnitud. Hay varios enfoques generales:

Muchos modelos se pueden construir con bucles de convergencia anidados. Si es así, asegúrese de que los bucles más rápidos estén en el interior y los más lentos en el exterior. Si sus bucles ya están codificados en VBA, use un temporizador para averiguar cuáles son rápidos y cuáles son lentos. Si sus bucles están actualmente solo en el modelo Solver, descubra qué variables independientes tienen el mayor impacto en los resultados después de un pequeño cambio porcentual de una sola variable, ese es su bucle “lento”. Una vez que haya identificado los bucles “rápidos” y “lentos”, puede usar el código VBA para colocar los bucles más rápidos en el lado interno y los más lentos en el exterior. Para un conjunto fijo de entradas “lentas”, cambie las entradas “rápidas” hasta que converjan (optimicen). Después de que las entradas “rápidas” hayan convergido, realice un solo cambio en las entradas “lentas”. Deje que las entradas “rápidas” converjan una vez más. Repita este proceso en un bucle hasta que las entradas “lentas” también hayan convergido.
Excel tiene varias funciones llamadas “volátiles”. Estos vuelven a calcular cada vez que un valor cambia en cualquier parte de un libro abierto. Como se puede imaginar, miles de celdas con fórmulas que utilizan funciones volátiles harán que el recálculo del libro se arrastre. Obviamente, debes trabajar para evitar su uso. Para más discusión, vea la página de Charles Williams Funciones volátiles de Excel. Las funciones volátiles incluyen INDIRECTO, OFFSET, RAND, NOW, HOY, CELL e INFO. La buena noticia es que puede encontrar soluciones alternativas no volátiles para la mayoría de ellos. Yo uso el código VBA para construir fórmulas en tiempo de ejecución en lugar de usar INDIRECTO. Puede usar INDEX en lugar de OFFSET. Si está utilizando AHORA para marcar los cambios de fecha y hora en un modelo (o para capturar altibajos), utilice en su lugar un sub evento Evento de cambio de hoja de cálculo.
Otro cuello de botella común en los libros de Excel es el uso de fórmulas de matriz en grandes columnas de entradas. Las fórmulas de matriz a menudo se usan para analizar datos sujetos a uno o más criterios, y cada fórmula de matriz repite las pruebas de criterios en todo el rango que se está probando. Puede usar una fórmula regular y evitar esta costosa recalculación de los criterios agregando columnas auxiliares que hacen la prueba de criterios en cada fila, y se evalúa solo, en lugar de miles de millones de veces por fórmula de matriz.
Cuando intente hacer que un modelo converja, sea inteligente acerca de cómo generar la próxima suposición. Una fórmula empírica (cálculo de la regla general) generalmente le da una suposición mucho mejor que una extrapolación lineal como la que usaría Solver. Y en lugar de comenzar la convergencia probando los límites superior e inferior, considere comenzar con el mismo valor al que convergió con el problema anterior.
Solver fue desarrollado para Excel por FrontlineSolvers. Se especializan en el uso de Solver para grandes optimizaciones de programación lineal, y han desarrollado un conjunto de complementos de resolución más rápidos y potentes que venden a personas como usted con modelos grandes. Nuestro software de optimización, simulación y minería de datos La versión gratuita de Solver que viene con Excel fue intencionalmente limitada en sus capacidades y se actualizó solo una vez desde que se agregó por primera vez a Excel a principios de los años noventa. Los otros complementos de FrontlineSolver, por el contrario, se han actualizado continuamente desde su primer lanzamiento. Si necesita la velocidad, gaste el dinero para obtener una versión más poderosa de solucionador.
Cuando haya minimizado su tiempo de recalcificación e incluso una versión más potente de Solver todavía no obtenga su respuesta en un tiempo razonable, considere trasladar su trabajo a un clúster de cómputo como el servicio Azure de Microsoft. Usted alquila tiempo en el grupo de cómputo, por lo que el costo es sorprendentemente razonable. Venden el servicio para muchas aplicaciones comerciales y científicas; Aquí hay un enlace para uno de estos: Simulación de ingeniería en la nube con Microsoft Azure

Parece que está trabajando con un modelo importante, ya sea para investigación académica o para decisiones comerciales. Probablemente has estado luchando con ese modelo durante bastante tiempo. Así que no me sorprendería si su primera reacción a mis sugerencias es decir que no son relevantes. Sin embargo, debe reservar su juicio.

Caso 1: Determinación del rendimiento de un evaporador de efectos múltiples (3 a 12 variables independientes). Este problema se resolvió usando VBA para los calcs y una hoja de cálculo para entradas y salidas. Después de una semana de trabajo, mejoré la velocidad de recálculo de 30 segundos / recálculo a una fracción de segundo. Esta mejora resultó del uso de la transferencia de matriz de entradas y resultados, utilizando una fórmula semiempírica para la siguiente aproximación, y anidando los bucles convergentes rápidos dentro del convergente lento.

Caso 2: dimensionamiento de un sistema de refrigeración de almacenamiento térmico de hielo (3 variables independientes). Este problema también se resolvió mediante el uso de VBA para los calcs y una hoja de cálculo para entradas y salidas. Después de un mes y medio de trabajo, mejoré la velocidad de recalc de 5 minutos / recalc a menos de 10 segundos. La mayor mejora vino de mantener los resultados provisionales dentro de VBA en lugar de devolverlos a la hoja de cálculo. También vi mejoras al usar la transferencia de matriz de entradas y resultados, y anidar bucles rápidos dentro de los lentos.

Caso 3: Simulación de desgaste en una carretera debido a la mezcla de tráfico. Este problema tenía una combinación de fórmulas de hoja de trabajo y código VBA, y se ejecutó tan lentamente que la computadora se apagó (sobrecalentando) antes de poder completar la tarea. Entré como consultor cuando el proyecto se enfrentaba a una fecha difícil de entrega y no tenía resultados debido al sobrecalentamiento de la computadora. Después de unas pocas horas de trabajo observó que el código VBA calculaba repetidamente algunos datos que realmente no cambiaron. Saqué ese código del bucle de convergencia de VBA, utilicé una fórmula empírica para generar la siguiente suposición y reduje el tiempo de recalcificación en un factor de 6. Esta mejora fue suficiente para que mi cliente hiciera todas sus simulaciones durante el fin de semana del 4 de julio. sin experimentar nunca el apagado de una computadora.

Caso 4: Determinación de las cargas permitidas en una grúa de pluma. Este problema requería analizar el esfuerzo en una grúa de brazo y determinar si algún componente estaba sobrecargado o desviado más allá de los límites permitidos. Mi cliente necesitaba producir gráficos de carga para una nueva familia de productos, y cada valor tardaba 20 minutos. Entré como consultor y pasé 10 horas trabajando en el problema. La velocidad general se mejoró en un factor de 1300. El compromiso comenzó estudiando el rendimiento del código VBA existente para identificar qué bucles eran lentos y cuáles eran rápidos. Luego usé fórmulas empíricas para generar la siguiente aproximación a la velocidad de convergencia, y reduje el número de puntos que se calculan de 500 intervalos igualmente espaciados a los 10 puntos de carga que se aplicaron a ese modelo de grúa.

Caso 5: Estudiar economía de la generación de energía eólica. El problema era generar una familia de curvas de rendimiento para diferentes conjuntos de condiciones. Mi cliente estaba usando Solver para hacer esto, pero solo podía estudiar un punto a la vez. Como los supuestos económicos no se habían finalizado, el resultado fue mucho trabajo ocupado con Solver. Reorganicé los cálculos utilizando la función Tabla de datos de Excel y utilicé un criterio de mínimos cuadrados para producir todas las curvas de rendimiento con una sola ejecución de Solver.