Cómo automatizar la búsqueda de objetivos en Excel con VBA Macro (2 formas)

En 2020, asistí a una reunión en línea donde Danielle Stein Fairhurst compartió una técnica para automatizar la búsqueda de objetivos en Excel con una macro de VBA. Nunca antes había pensado en esto; Tenía mi mente llena de ideas sobre cómo podría usarlo. Desde entonces, he implementado esta técnica en algunos proyectos y es un enfoque realmente útil para ahorrar tiempo. Entonces, quería compartir mi enfoque con ustedes.

Si bien es probablemente una de las mejores funciones de Excel, Goal Seek es bastante complicado de usar. Está escondido en el menú Datos; por lo que necesita saber que está ahí para encontrarlo. En esta publicación, automatizamos Goal Seek para que funcione con un solo clic directamente en la hoja de trabajo. Luego vamos un paso más allá eliminando el clic, haciéndolo totalmente automatizado.

Tabla de contenido

• El escenario
• Búsqueda manual de objetivos
• Búsqueda de objetivos con un clic
• grabar la macro
• Mirando el código grabado
• Asignar la macro a un botón
• Usando rangos con nombre
• Agregar una verificación de errores
• Automatizar la búsqueda de objetivos
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0022 Búsqueda automatizada de objetivos.zip

Tener acceso

El escenario

La función de búsqueda de objetivos determina los datos necesarios para lograr un resultado específico. El escenario que estamos utilizando es un caso de negocio simple. Con base en las unidades de ventas, el precio de venta, el precio de costo y los costos fijos, calculamos la ganancia.

Las celdas grises son las variables, si alguna de las variables cambia, la ganancia final cambia.

Búsqueda manual de objetivos

Primero, comprendamos qué hace Goal Seeks al realizar la tarea manualmente.

Usando los números anteriores, supongamos que queremos saber cuántas unidades necesitaríamos vender para alcanzar el punto de equilibrio (es decir, la ganancia es igual a cero).

1. Haga clic en Datos ¿Qué pasa si Análisis Búsqueda de objetivo... para abrir la herramienta Búsqueda de objetivo?
   
2. En el cuadro de diálogo Búsqueda de objetivo, establezca los siguientes parámetros:
   
   • Establecer celda: E12 (la celda de ganancias)
   • Para valorar: 0
   • Cambiando de celda: C4 (la celda de Unidades de Ventas)
3. Haga clic en Aceptar para ejecutar Goal Seek. Excel ahora intenta identificar una posible solución y actualizar la celda E12 en consecuencia.
   
4. Una vez que encuentre una solución, haga clic en Aceptar para cerrar el cuadro de diálogo Búsqueda de objetivo.

Si sigue el archivo de ejemplo, verá que necesitamos vender 571 unidades para alcanzar el punto de equilibrio.

Si una de las variables cambiara (por ejemplo, si el precio de venta aumentara a 110), entonces necesitaríamos volver a ejecutar el mismo proceso de búsqueda de objetivos. Consume bastante tiempo. Además, si estás diseñando la hoja de cálculo para otra persona, entonces no es una buena experiencia para el usuario.

Pasemos a buscar una solución más dinámica; cómo automatizar la búsqueda de objetivos en Excel con un solo clic.

Búsqueda de objetivos con un clic

En esta sección, creamos una solución de un clic usando una macro y adjunta a un botón. Cada vez que cambian las variables, solo se necesita un clic para volver a ejecutar la búsqueda de objetivos.

grabar la macro

En este escenario, Macro Recorder es un sólido punto de partida.

Si la cinta de desarrollador no está visible en su instancia de Excel, haga lo siguiente:

• Haga clic derecho en un espacio vacío en la cinta y seleccione Personalizar la cinta en el menú.
• Se abre el cuadro de diálogo Opciones de Excel. En el cuadro de la derecha, marque la opción Desarrollador.
• Haga clic en Aceptar.

Bien, ahora estamos listos para comenzar a grabar la macro.

1. Haga clic en Macro de registro de desarrollador
   
2. En el cuadro de diálogo Grabar macro, acepte la opción predeterminada haciendo clic en Aceptar .
   
3. Realice los mismos pasos que en la versión manual que utilizamos anteriormente.
4. Cuando termine, haga clic en Desarrollador Detener grabación
   

Mirando el código grabado

Echemos un vistazo breve al código grabado. Haga clic en Desarrollador Visual Basic para abrir el Editor de Visual Basic, luego mire el código en el Módulo 1.

El código grabado debería verse como el siguiente:

Sub Macro1()'' Macro1 Macro''    Range("E12").GoalSeek Goal:=0, ChangingCell:=Range("C4")End Sub 

La pieza que necesitamos es solo una línea de código. Podemos editarlo fácilmente para satisfacer nuestras necesidades. Volveremos al Editor de Visual Basic en unos momentos, pero por ahora, está bien cerrarlo.

Asignar la macro a un botón

El último paso es crear un botón para ejecutar la macro.

1. Haga clic en Insertar formas Rectángulo Esquinas redondeadas (o cualquier otra forma que desee).
   
2. Haga clic en la hoja de trabajo para crear la forma.
3. Haga clic derecho en la forma y seleccione Asignar macro… en el menú.
   
4. En el cuadro de diálogo Asignar macro, seleccione el nombre de la macro grabada anteriormente y luego haga clic en Aceptar .
   

Al hacer clic en la forma, se ejecutará la macro de búsqueda de objetivo. Formatee la forma como desee.

Con suerte, verá que esta es una experiencia de usuario mucho mejor que usar la herramienta predeterminada.

Usando rangos con nombre

Puede resultar peligroso utilizar simplemente referencias de celda en una macro. Si un usuario inserta una fila o una columna, la referencia apunta a la celda incorrecta. Para hacer esto más sólido, deberíamos crear algunos rangos con nombre.

Seleccione cada celda gris (es decir, las que contienen variables), luego escriba un nombre en el cuadro de nombre y presione Intro.

He nombrado las celdas de la siguiente manera:

• C4: Unidades de Venta
• D4: Precio de venta
• D6: Precio de costo variable
• E10: Costo Fijo
• E12: Beneficio
• I6: Valor objetivo

Actualicemos nuestra macro para los rangos nombrados (también eliminé las líneas innecesarias).

Sub Macro1()Range("Profit").GoalSeek Goal:=Range("TargetValue"), _    ChangingCell:=Range("SalesPrice")End Sub

Al agregar el valor objetivo, ahora podemos buscar objetivos a cualquier valor que queramos. Por ejemplo:

• Para calcular el punto de equilibrio, ingresamos cero como valor objetivo y luego hacemos clic en el botón para ejecutar la macro.
• Para calcular las unidades necesarias para obtener una ganancia de $50 000, ingrese 50 000 como valor objetivo y luego ejecute la macro.

Bastante bien, ¿eh?

Agregar una verificación de errores

Normalmente, con una búsqueda de objetivo, recibimos un mensaje cuando no se puede encontrar un valor.

Esto no sucede con la macro, por lo que necesitamos crear nuestras propias comprobaciones de errores. Hay muchas cosas que podemos hacer aquí, pero para simplificar, acabo de agregar lo siguiente en la celda F12:

=IF(ProfitTargetValue," Automated Goal Seek - no result found","")

Muestra cuando la ganancia no es igual al valor objetivo, lo que significa que la búsqueda de objetivo no encontró un valor coincidente.

Dependiendo de la precisión que necesites, es posible que tengas que redondear los números.

Automatizar la búsqueda de objetivos

Ahora, para las cosas del siguiente nivel.

Agregue dos rangos con nombre más:

• I4: Establecer celda
• I8: Cambiar celda

Ingrese los siguientes valores en esas celdas; pronto verás por qué.

• I4 = Beneficio
• I8 = Unidades de Venta

A continuación, agregue una nueva macro al módulo de hoja de trabajo, utilizando el evento de cambio de hoja de trabajo.

Los comentarios proporcionan información sobre cada línea de código. La macro es:

Private Sub Worksheet_Change(ByVal Target As Range)Dim inputCells As Range'List all the input cells.Set inputCells = Range("SalesUnits, SalesPrice, VariableCostPrice, "  _    "FixedCost, TargetValue, SetCell, ChangeCell")'Run the macro if an input cell changesIf Not Application.Intersect(Range(Target.Address), _    inputCells) Is Nothing Then    'Run the Goal Seek using the values in the SetCell, TargetValue     'and ChangeCell named ranges    Range(Range("SetCell").Value).GoalSeek _        Goal:=Range("TargetValue").Value, _        ChangingCell:=Range(Range("ChangeCell").Value)End IfEnd Sub

Ahora, cada vez que cambiamos una celda de entrada, la búsqueda de objetivo se activa automáticamente.

Tanto Establecer celda como Cambiar celda deben ser los nombres de los rangos con nombre que creamos anteriormente.

Si queremos que la búsqueda de objetivo cambie una variable diferente para calcular la ganancia, ajustamos la celda de cambio (celda I8) al rango nombrado para la variable.

Muy bien, ¿eh? Ahora ni siquiera necesitamos hacer clic en un botón

Conclusión

En esta publicación, aprendimos cómo automatizar la búsqueda de objetivos en Excel con una macro de VBA. Usando la grabadora de macros podemos crear una solución simple con un solo clic, o podemos ir aún más lejos y automatizar cada vez que cambia el objetivo.

Hay muchas funciones de Excel que se pueden operar con unas pocas líneas de código. ¿Por qué no explorar y ver qué más puedes encontrar?

Artículos Relacionados:

• Códigos VBA útiles para Excel (30 macros de ejemplo + libro electrónico gratuito)
• Rangos con nombre: métodos de propiedades de VBA
• Propiedades y métodos de VBA de celda y rango

---