Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado
Logo Excel Avanzado

Buscar Objetivo (con macros)

| 16 comentarios

Veamos como funciona buscar objetivo, ...

Asumamos que tenemos como se muestra en la imagen en la celda A2 un valor, cuyo valor sumado a la celda B2 se muestra en la celda C2, … C2 entonces depende del valor de A2 y B2, ahora bien, deseamos que el valor de C2 sea igual a una cantidad  como la existente en D2

01_Condicion_inicial_excel

Con la opción buscar objetivo los usual es que tenemos que “definir la celda”,  indicar “con el valor” y “Para cambiar la celda”, … es decir que con estos valores la celda “definida” debe alcanzar el “valor” indicado en la segunda caja de texto,  Excel va a ir “cambiando”  la celda indicada en la tercera caja de texto hasta llegar al valor deseado.

02_Buscar_objetivo_usual

Finalizado el proceso, al obtener el resultado se mostrará la pantalla de confirmación.

03_Buscar_objetivo_resultado

Si bien esto luce sencillo, repetir esta operación para todas las filas sería algo muy tedioso, por lo que podríamos crear una macro como la siguiente para resolver el caso.

04_Buscar_objetivo_macro

Luego de la ejecución hemos  obtenido todos los valores deseados, naturalmente, podrán indicar que quizá se pudo usar algunas funciones para hallar lo mismo, lo cual es posible por que se tratan de sumas y es muy sencillo identificar las operaciones necesarias, sin embargo, si se tratasen de formulas financieras por ejemplo, entonces identificar las operaciones necesarias para cumplir el objetivo no sería tan sencillo, y esta si sería la solución mas apropiada (se han usado sumas a fin de simplificar el ejemplo).

 

05_Buscar_objetivo_final

La ejecución de la macro correspondiente a "buscar objetivo" incluyó el uso de los siguientes conceptos: 

Bucle For en VBA: Este comando es el que permitió ejecutar el "buscar objetivo" para cada fila.

Última Fila Excel VBA: Es el comando que permite identificar hasta que fila se debe trabajar en la ejecución de la macro.

 

Descargar ejemplo correspondiente a Buscar Objetivo:  Buscar Objetivo VBA 

 

16 comentarios

  1. Sería interesante desarrollar una generalización del solver para optimizar todo un vector de variables en un bucle.

  2. Supongamos que has hecho un presupuesto en una hoja Excel

    Hiciste la suma de todos los precios, obtuviste un Total sin IGV (por ejemplo en E8), hiciste un reintegro (en la celda E9), aplicaste el IGV (en E10) y obtuviste el Total Incluido Impuestos (en E11)
    Ahora tu cliente te pide que redondees el monto total: Total Incluido Impuestos…(en E11)

    Pero:
    No deseas modificar el Total sin IGV (E8)
    No deseas modificar el IGV (E10)
    No puedes modificar el reintegro (E9) para ajustar (variable de ajuste).

    Herramientas / Buscar objetivo / Definir la celda…haces clic en E11 (el Total Incluido Impuestos) / Con el valor…indicas el valor redondeado que deseas presentar / Para cambiar la celda…haces clic en E9 (Reintegro)…Aceptar

    Excel vuelve a calcular el reintegro (E9) para alcanzar de manera precisa el valor final (E11) deseado, y también reajusta el valor del IGV (E10), sin modificar la tasa.

  3. La herramienta de Buscar Objetivo ayuda a descubrir el valor que se necesita para obtener un resultado específico.Se debe tener en cuenta que la funcionalidad de Buscar Objetivo funciona únicamente con un solo valor de entrada variable

    • O sea si la columna D la formulo como la suma de la columna E y F (ya que quiero que mi objetivo sea siempre esa suma) entonces: ¿la herramienta buscar objetivo no sirve?. Bueno, quizas haya que agregar a la macro que la suma de E y F sea considerada como valor y no fórmula. Probaré a ver que sale.

  4. Adapté esta macro, me funciona muy bien con excepción de que NO BUSCA EN LA HOJA1 desde donde abro la búsqueda. Recorre todos menos la 1

    Sub Macro1()
    'Busca en todas hojas menos la 1ª, seleciona la celda y abre la hoja del dato encontrado
    Dim buscar
    Dim texto As String, titulo As String
    texto = "Digite cualquier dato sobre lo que desea buscar, menos cualquier ID"
    titulo = "Busqueda en libro"
    buscar = InputBox(texto, titulo)
    If buscar = "" Then Exit Sub
    For Each hoja In Sheets
    If hoja.Name "Hoja1" Then
    With hoja.Range("A2:AA65500")
    Set esta = .Find(buscar)
    If Not esta Is Nothing Then
    primeracelda = esta.Address
    'opcional: mostrar el dato, la celda y el nombre de la hoja según consulta original.
    MsgBox esta & " se encuentra en la celda " & esta.Address & " " & hoja.Name
    hoja.Activate
    esta.Select
    Exit Sub
    End If
    End With
    End If
    Next hoja
    End Sub

    Quisiera que alguien me diera una manito para que recorra también la hoja1

  5. Profesor
    En esta línea del Procedimiento:
    Cells(i, 3).GoalSeek Cell(i, 4).Value, Cells(i, 2), tengo un par de consultas, después de GoalSeek hay un espacio antes de Cell(i,4), y luego de Value hay una coma y un espacio antes de Cells(i,2). Corri el Procedimiento y me arrojó un mensaje de error, entiendo que por esos dos temas.

    Podía por favor indicarnos cuál sería la instrucción correcta?

  6. Profesor
    En el Procedimiento, en la línea
    Cells(i,3).GoalSeek Cells(i,4).Value, Cells(i,2), luego de GoalSeek hay un espacio en blanco antes de Cells(i,4), y luego de Value una coma y luego un espacio antes de Cells(i,2). Al correr dicho procedimiento me arroja un mensaje de error, entiendo que por esos dos temas, podría por favor indicarnos cuál es la instrucción correcta?

    Gracias

  7. Buenos días,

    Una Pregunta, si invierto el ejercicio cual sería el código? soy neófito en el tema y desearía aprender... Gracias!!!

  8. Tiene aplicación similar al solver pero a las celdas que tu quieras .

  9. Me gusta su aplicación en macros, ya que te da la posibilidad de aplicar "buscar objetivo" en varios casos; por ejemplo planeo utilizarlo para los presupuestos de las agencias del trabajo ya que es una manera facil de saber cuanto saldo va quedando sobre el monto maximo que se puede otorgar al mes, de tal modo que se pueden ajustar los demas gastos en sus respectivas celdas.

  10. Hola, tengo un problema para encontrar valores vacios (huecos o ceros) entre columnas.

    EJEMPLO:

    COLUMNA1 COLUMNA2 COLUMNA3 COLUMNA4 COLUMNA5 HUECOS
    100 100 100 100 1
    100 100 100 2
    100 100 100 100 100 0
    100 100 1

    saludos cordiales, gracias!

  11. Específicamente para que se usa el goalseek? Es decir, que acción realiza el goalseek

    • No se si pueda explicarme bien, pero el goalseek te permite llegar a un resultado mediante el cambio de una de las variables de una formula. El ejemplo dado para la macro de este artículo es una simple operación aritmética pero se usa para operaciones un poco más complejas donde te tomaría más tiempo "adivinar" que valor debes tener en X celda para que tu resultado final sea Y.

  12. Fernando Santos;

    Muchisimas gracias por la Macro, busque por miles de paginas para dar una solución a mi problema y nada me resulto...

    Me ahorraste muchas horas de trabajo...

  13. Hola, la verdad este articulo es muy interesante, es justo lo que ocupo, yo no sé de Macros e intenté aplicar la señalada aqui pero obviamente no es el mismo caso, adapte las celdas pero me sale el error de ejecucion 1004. la verdad no se como arreglarlo, me podrian ayudar para saber que esta mal? lo unico que cambie es el numero de las celdas.

  14. Hola, gracias por tus conocimientos. ¿Si quisiese hacer esto en Google Sheets cómo se haría?

    Saludos

Deja una respuesta

Los campos requeridos estan marcados con *.