Cómo automatizar Excel Solver

En Cómo sacar más provecho de Excel Solver, cubrí técnicas para mejorar su modelo y potenciar su análisis. Uno de los temas sobre los que recibí más preguntas es la automatización del solucionador para resolver múltiples valores.

Hubo una breve explicación en el artículo anterior, pero nada dedicado a cómo implementarlo de principio a fin. En este artículo profundizaremos de principio a fin cubriendo los conceptos y métodos paso a paso. Al final, podrá automatizar cualquiera de sus propios modelos personales y escalar su análisis.

Como lo haremos

Aprovecharemos la grabadora de macros en Excel para manejar la mayor parte del código real. La herramienta de registro de macros permitirá que Excel convierta nuestros pasos manuales en código VBA.

A continuación, identificaremos las piezas clave de código para cambiar. Luego crearemos un ciclo para seleccionar y reemplazar dinámicamente estos valores y ejecutar el solucionador nuevamente.

Esto será semi-técnico, pero a través de este estilo de desarrollo podemos enfocarnos en el proceso y los conceptos en lugar de enfocarnos en cómo escribir código.

Configuración de la grabadora de macros

La funcionalidad de registro de macros se encuentra en la pestaña de desarrollador en la cinta. Esto no está disponible de forma predeterminada, pero es fácil de implementar. Si navega a las opciones de la cinta, verá una casilla de verificación para la pestaña de desarrollador.

La siguiente captura de pantalla es para Mac, pero Excel basado en PC es similar.

Si no ha instalado el complemento del solucionador, puede instalarlo a través del menú Complementos. El sitio de soporte de Microsoft tiene instrucciones para todas las plataformas, Cargue el complemento Solver en Excel.

Grabando la macro

Ahora que la configuración está completa, ¡estamos listos para grabar! Configure su espacio de trabajo de manera adecuada con todas sus fórmulas y referencias. Querrá configurar todo hasta el punto antes de construir el modelo de solucionador.

Regrese a la pestaña de desarrollador en la cinta. Verá una hoja de papel con un punto rojo. Haga clic en ese botón para abrir la grabadora.

Dé un nombre a su macro y complete la descripción si lo desea.

Una vez que presione Aceptar, la grabadora comenzará.

Siga los pasos para configurar su solucionador y resuélvalo. Una vez que haya terminado, vuelva a la pestaña de desarrollador y deje de grabar. Será la misma ubicación que el botón que usó para iniciar la grabación.

Ahora que tenemos el código base, podemos limpiarlo y prepararlo para los siguientes pasos.

Limpiar el código

Ahora podemos editar el código, eliminar las partes innecesarias y asegurarnos de que solo estamos usando las partes esenciales de la grabación de macros.

Puede editar el código a través del editor VBA de Excel. Para acceder a su código grabado, haga clic en el botón Macros.

Navegue hasta su macro en el nuevo menú y haga clic en editar.

Ahora que tenemos nuestro editor de código abierto, podemos empezar a hacer ajustes.

Eliminar código innecesario

Si hizo clic en un montón de otras cosas mientras grababa, puede borrarlas ahora y quedarse con algo similar a lo que se muestra a continuación. Si hay mucho código adicional y le preocupa romperlo, siempre puede volver a grabarlo.

Configurar las referencias del solucionador

Un último paso antes de continuar, vaya a Herramientas> Referencias y seleccione Solver. Si no lo hace, el paquete no se cargará en la macro y obtendrá un error.

Ahora puede cambiar sus parámetros, abra el menú de macros, seleccione su macro, presione ejecutar y actualizará los resultados.

Alineemos el código con nuestro modelo de solucionador. Verás cómo se alinea. El idioma es diferente, pero puedes reconstruir todo lo que significa.

A continuación, aislaremos las partes que necesitamos cambiar y terminaremos nuestra automatización.

Enjuague y repita: construyendo el bucle

Ahora que tenemos un solucionador de trabajo. Necesitamos repetir eso varias veces. Pongamos un ejemplo y cimentemos el plan.

Comenzamos con un presupuesto de $ 1,000 en el primer modelo, pero queremos hacer lo mismo con incrementos de $ 100 hasta $ 2,500.

¡Vamos a sumergirnos! Tenemos una lista de valores en la columna B y nuestra salida en la columna C. Si escribiéramos el proceso, se vería así,

1. Establezca el gasto total en el valor en B10.
2. Resuelve el modelo.
3. Coloque la salida en C10.
4. Elija el siguiente valor B.
5. Establezca la salida en la fila C.
6. Repita los pasos 2-5 para todos los valores en B.

Comenzando el ciclo

Los bucles son construcciones de codificación que repiten una acción hasta llegar a un punto final lógico. Un ciclo será útil aquí para repetir el mismo proceso, ejecutando el solucionador, en cada valor hasta que lleguemos al final del valor.

Para este ejemplo, definiremos el punto final por el número de filas. B10: B25 tiene 16 valores.

Comenzaremos nuestro ciclo y lo envolveremos alrededor de nuestro código de resolución. Configuraremos una variable para contener el número de ciclo (i) y revisaremos cada valor, resolviendo el ciclo cada vez.

Restablecer el solucionador

Agregaremos otra condición más al comienzo de nuestro código. Como queremos volver a ejecutar el solucionador en cada iteración, lo restableceremos en cada ciclo. Esto borrará la configuración y comenzará un modelo nuevo.

Podemos hacer esto a través de SolverReset .

Actualizar referencias de celda

Ahora resolvemos dieciséis veces pero necesitamos actualizar nuestras referencias de celda. Aunque originalmente hicimos referencia a B10, necesitamos bajar una celda en cada ejecución para actualizar los límites. Podemos hacer esto a través de la función de compensación. Podemos actualizar "$ B $ 10" para hacer referencia a la celda y luego compensar por i filas (0 en la primera ejecución, 1 fila en la segunda, y así sucesivamente). Cada vez que se ejecuta el bucle, i aumenta en uno.

Recolectando la salida

La salida cambiará cada vez, por lo que querremos guardarla. Podemos seguir el mismo proceso anterior pero compensar desde C10 y establecerlo igual al valor del modelo resuelto. Entonces el ciclo comienza de nuevo.

Llamaremos C10 compensarlo por el número apropiado de filas y luego pegaremos el valor de nuestra salida.

Eludir las notificaciones del solucionador

Ahorremos un dolor de cabeza y no aprendamos con el ejemplo ni por un momento. ¿Recuerda cuando ejecutó el solucionador y apareció ese menú preguntando si desea aceptar la solución? Aparecerá cada vez que no lo deshabilite. Imagínese si configura un bucle con cientos de valores, ¡tendría que hacer clic en aceptar cada vez!

Continúe y agregue las siguientes líneas después de su código de solucionador y agregue (TRUE) a SolverSolve. Esto imitará su aceptación de los cambios.

¡Ahora tenemos el código terminado!

En funcionamiento

Ahora podemos ejecutar el código seleccionándolo de la lista de macros y ejecutándolo. Si desea utilizar su libro de trabajo como plantilla, puede asignar la macro a un botón para facilitar su uso. Puede encontrarlos en la pestaña de desarrollador.

También puede agregar gráficos de comparación. En este caso, queremos comparar la tasa de crecimiento del gasto con el crecimiento del volumen de conversiones. Este ejemplo no es interesante, pero los datos reales pueden ayudar a revelar puntos críticos de eficiencia.

Conclusión

¡Cubrimos bastante! Felicitaciones por poner en funcionamiento su solucionador automático. El ejemplo que cubrimos fue demasiado simplista, pero puede tomar los mismos conceptos y aplicarlos a cualquier modelo de solucionador.

Esto puede ahorrar una enorme cantidad de tiempo para los modelos que necesita resolver varias veces y alentar a los usuarios a experimentar con diferentes escenarios.

Fecha de publicación original: 30/07/2019