Excel Avanzado

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

Excel Avanzado
Logo Excel Avanzado

Buscar Objetivo (con macros)

| 13 Comments

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

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.

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

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.

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) .

13 Comments

  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

  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…

Deja un comentario

Required fields are marked *.


Excel Avanzado located at , Lima, Perú . Reviewed by usuarios rated: 4.7 / 5