Cómo utilizar la búsqueda de objetivos en Excel para encontrar un valor (Manual + VBA)

En la escuela secundaria, solía resolver ecuaciones matemáticas donde tengo el resultado de la ecuación pero no tengo el valor de entrada. Entonces, si estás pensando en resolver el mismo tipo de problemas en Excel, tienes una herramienta específica que puede ayudarte.

Buscar objetivo

Su idea básica es encontrar el valor de entrada que la fórmula necesita para obtener ese resultado. En palabras simples, si sabe qué valor de resultado real desea, puede utilizar la búsqueda de objetivos para encontrar el mejor valor de entrada para obtenerlo.

Por ejemplo: digamos que necesitas pedirle prestado algo de dinero a tu amigo. Sabes cuánto dinero necesitas, cuánto tiempo quieres tardar en liquidar el préstamo y cuánto puedes pagar cada mes.

Puede utilizar la búsqueda de objetivos para calcular la tasa de interés que necesitará para asegurar con su amigo. Hay N números de problemas complejos que puedes resolver con la búsqueda de objetivos.

Así que hoy, en esta publicación, aprenderá cómo utilizar la búsqueda de objetivos en Excel, cómo funciona y qué puntos importantes debe tener en cuenta. Empecemos.

Cómo abrir la búsqueda de objetivos

Para abrir una portería busque utilizar uno de estos dos métodos.

• Vaya a la pestaña Datos → Análisis What If → Búsqueda de objetivos.

• Utilice la tecla de método abreviado Alt + T + G.

Puede utilizar estos métodos para activar la búsqueda de objetivos en las versiones de Microsoft Excel 2007 a 2016.

Componentes de la función de búsqueda de objetivos

Cuando abras Goal Seek, aparecerá una pequeña ventana emergente para ingresar datos. Básicamente, tiene tres componentes requeridos.

1. Establecer celda: Celda en la que desea obtener el resultado deseado. Asegúrese de que la celda a la que se refiere aquí tenga una fórmula.
2. A valorar: Valor que desea como resultado.
3. Cambiando de celda: La celda en la que desea que la modificación obtenga el resultado es igual al “Valor de destino”. Asegúrese de que la celda a la que se refiere aquí se utilice en la fórmula de la celda a la que se hace referencia en “Establecer celda”.

Resolver un problema en Excel con búsqueda de objetivos

Imagine que está trabajando en una empresa que intenta conseguir una licitación cotizando el precio más bajo.

Para esto, necesita preparar una estructura de precios en la que su precio de aterrizaje (para el cliente) debe ser de $1000. Mientras que su precio final actual es de $ 1089,9.

Si quieres hacerlo manualmente tienes dos opciones.

1. Realice un cálculo atrasado en el que tendrá que deducir todos los impuestos y cargos del precio de aterrizaje ($1000) para llegar al precio base.
2. O bien, debe modificar su precio base para llegar al precio final deseado.

No es tan fácil como parece, pero la búsqueda de objetivos puede cambiar las reglas del juego aquí.

Descargue este archivo desde aquí y siga los pasos a continuación.

• Cuando abras la búsqueda de objetivos, aparecerá una ventana como esta.

• Ahora, debe ingresar los siguientes valores.
  1. Haga clic en “Establecer valor” y remítalo a la celda que tiene un precio de aterrizaje.
  2. Haga clic en “A valor” e ingrese el valor que desea como resultado, en la celda que tiene un precio de aterrizaje.
  3. Al final, haga clic en “Al cambiar de celda” y remítalo a la celda que tiene un precio base.

• Haga clic en Aceptar.
• La búsqueda de objetivos hará algo como esto con su hoja de cálculo.

Como funciona

Ahora, si marca la celda de precio base, su valor cambia a $925 y con esto, su precio de aterrizaje cambia a $1000. Tu base ha cambiado por objetivo buscado usando un método de golpe y prueba. Pero la búsqueda de objetivos es bastante rápida en esto y no puedes ver el proceso completo.

Puntos importantes sobre la búsqueda de objetivos

Estos son algunos puntos que deben tenerse en cuenta al utilizar la búsqueda de objetivos.

1. Una celda que se utiliza como celda de conjunto debe contener una fórmula que dependa de la celda “Al cambiar de celda”.
2. Goal Seek utiliza el método de iteración para alcanzar el valor objetivo que menciona en “Al valor”. Puede ajustar su iteración desde Archivo-> Opciones -> Fórmulas (puede aumentar su nivel de precisión).
3. A veces, Excel no puede brindarle el resultado deseado y muestra un mensaje que dice “es posible que no haya encontrado una solución”. En esa situación, puede ingresar un valor más cercano a su resultado e intentar nuevamente ($999,95 en lugar de $1000). Además, asegúrese de comprobar los dos consejos anteriores.
4. La búsqueda de objetivos solo puede funcionar con un valor de entrada de una sola celda a la vez.

Utilice la búsqueda de objetivos con código VBA

Puede disfrutar de su café mientras trabaja con el comando de búsqueda de objetivos de Excel. Espero que no te importe si puedes agregarle un poco de chocolate extra usando VBA. Sí, tienes razón, puedes utilizar la búsqueda de objetivos con VBA.

expresión.GoalSeek (objetivo, celda cambiante)

1. Expresión: Debe ser una celda en la que se desea el resultado deseado.
2. Objetivo: El resultado deseado que queremos obtener como resultado.
3. Celda cambiante: Celda en la que se requieren cambios para lograr el valor del resultado.

Aquí hay un código de macro que puede usar.

Sub GoalSeekVBA()
Rem: ////////// Code by ExcelChamps.com
Dim Target As Long
On Error GoTo Errorhandler
Target = InputBox("Enter the required value", "Enter Value")
Worksheets("Goal_Seek").Activate
With ActiveSheet.Range("C7")
.GoalSeek _
Goal:=Target, _
ChangingCell:=Range("C2")
End With
Exit Sub
Errorhandler: MsgBox ("Sorry, value is not valid.")
End Sub

archivo de muestra

Conclusión

Como dije, si intenta encontrar el valor de entrada aplicando un método manual, necesita dedicar mucho tiempo o crear una fórmula para realizar un cálculo inverso.

Pero al utilizar la búsqueda de objetivos, puedes hacerlo con un solo clic. Hará todos los cálculos complejos por usted en un segundo. La mejor parte es que también puedes usarlo con VBA. Y espero que esto te ayude a mejorar en Excel.

Nota rápida: Es uno de esos consejos de Excel que pueden ayudar a mejorar sus habilidades de Excel.