Cómo utilizar Goal Seek en Microsoft Excel para escenarios hipotéticos

Aranzulla de Los Pobres

El análisis Y si … de Excel le permite ver cómo el cambio de una celda puede afectar el resultado de una fórmula. Puede utilizar las herramientas de Excel para este propósito para calcular el efecto de cambiar el valor de una celda en una fórmula.

Excel tiene tres tipos de herramientas de análisis Y si …: Administrador de escenarios, Búsqueda de objetivos y Tabla de datos. Con Goal Seek, puede determinar qué entrada necesita para convertir la fórmula hacia atrás a una determinada salida.

La función de búsqueda de objetivos en Excel es de prueba y error, por lo que si no produce lo que desea, Excel trabaja para mejorar ese valor hasta que lo haga.

¿Qué son las fórmulas de búsqueda de objetivos de Excel?

Goal Seek en Excel esencialmente se divide en tres componentes principales:

  1. Establecer celda : la celda que desea usar como su objetivo.
  2. Valorar : El valor que quieres como objetivo.
  3. Cambiando celda : la celda que le gustaría cambiar para alcanzar su valor objetivo.

Con estas tres configuraciones establecidas, Excel intentará mejorar el valor en la celda que estableció en Cambiando la celda hasta que la celda que estableció en Establecer celda alcance el valor que determinó en el valor A.

Si todo eso suena confuso, los ejemplos a continuación le darán una buena idea de cómo funciona Goal Seek.

Ejemplo de búsqueda de objetivo 1

Por ejemplo, digamos que tiene dos celdas (A y B) y una tercera celda que calcula el promedio de estas dos.

Ahora suponga que tiene un valor estático para A y desea aumentar el nivel promedio cambiando el valor de B. Al usar Goal Seek, puede calcular qué valor de B producirá el promedio que desea.

Relacionado: Consejos para ahorrar tiempo en Microsoft Excel

Cómo usar Goal Seek en Excel

  1. En Excel, haga clic en la celda C1 .
  2. En la barra de fórmulas, ingrese el siguiente código:
     =AVERAGE(A1:B1)

    Esta fórmula hará que Excel calcule el promedio de los valores en las celdas A1 y B1 y lo genere en la celda C1 . Puede asegurarse de que su fórmula funcione ingresando números en A1 y B1. C1 debería mostrar su promedio.

  3. Para este ejemplo, cambie el valor de la celda A1 a 16 .
  4. Seleccione la celda C1 , y luego desde la cinta, vaya a la pestaña Datos .
  5. En la pestaña Datos , seleccione Análisis hipotético y luego Buscar objetivo . Esto abrirá la ventana Buscar objetivo.
  6. En la ventana Buscar objetivo, establezca la celda Establecer en C1 . Esta será tu celda objetivo. Habiendo resaltado C1 antes de abrir la ventana Buscar objetivo, se configurará automáticamente en la celda Establecer .
  7. En Valor , inserte el valor del objetivo que desee. Para este ejemplo, queremos que nuestro promedio sea 26, por lo que el valor objetivo también será 26.
  8. Finalmente, en la celda Cambiando , seleccione la celda que desea cambiar para alcanzar la meta. En este ejemplo, será la celda B2 .
  9. Haga clic en Aceptar para que Goal Seek haga su magia.

Una vez que haga clic en Aceptar, aparecerá un diálogo que le informará que Goal Seek ha encontrado una solución.

El valor de la celda A1 cambiará a la solución, y esto sobrescribirá los datos anteriores. Es una buena idea ejecutar Goal Seek en una copia de su hoja de datos para evitar perder sus datos originales.

Relacionado: Fórmulas locas de Excel que hacen cosas asombrosas

Ejemplo de búsqueda de objetivo 2

Goal Seek puede ser útil cuando lo utilizas en escenarios de la vida real. Sin embargo, para utilizar Goal Seek en todo su potencial, debe tener una fórmula adecuada, para empezar.

Para este ejemplo, tomemos un pequeño caso bancario. Suponga que tiene una cuenta bancaria que le da un interés anual del 4% sobre el dinero que tiene en su cuenta.

Con el análisis Y si … y la búsqueda de metas, puede calcular cuánto dinero necesita tener en su cuenta para obtener una cierta cantidad de pago de intereses mensual.

Para este ejemplo, digamos que queremos obtener $ 350 cada mes de los pagos de intereses. Así es como se puede calcular eso:

  1. En la celda A1, escriba Balance .
  2. En la celda A2, escriba Tasa anual .
  3. En la celda A4, escriba Beneficio mensual .
  4. En la celda B2 escriba 4% .
  5. Seleccione la celda B4 y en la barra de fórmulas, ingrese la fórmula a continuación:
     =B1*B2/12

    La ganancia mensual será igual al saldo de la cuenta multiplicado por la tasa anual y luego dividido por la cantidad de meses en un año, 12 meses.

  6. Vaya a la pestaña Datos , haga clic en Análisis Y si … y luego seleccione Buscar objetivo .
  7. En la ventana Buscar objetivo , escriba B4 en la celda Establecer .
  8. Escriba 350 en la celda Para valor . (Esta es la ganancia mensual que desea obtener)
  9. Escriba B1 en Cambiando de celda . (Esto cambiará el saldo a un valor que producirá $ 350 mensuales)
  10. Haga clic en Aceptar . Aparecerá el cuadro de diálogo Estado de búsqueda de objetivos .

En el cuadro de diálogo Estado de búsqueda de objetivos, verá que Búsqueda de objetivos ha encontrado una solución en la celda B4. En la celda B1, puede ver la solución, que debería ser 105.000.

Requisitos de búsqueda de objetivos

Con Goal Seek, puede ver que solo puede cambiar una celda para alcanzar el valor del objetivo. Esto significa que Goal Seek no puede resolver y llegar a una solución si tiene más de una variable.

En Excel, aún puede resolver más de una variable, pero deberá usar otra herramienta llamada Solver. Puede leer más sobre Excel's Solver leyendo nuestro artículo sobre Cómo usar Excel's Solver .

Limitaciones de búsqueda de objetivos

Una fórmula de búsqueda de objetivos utiliza el proceso de prueba y mejora para alcanzar el valor óptimo, lo que puede plantear algunos problemas cuando la fórmula produce un valor indefinido. Así es como puede probarlo usted mismo:

  1. En la celda A1 , escriba 3 .
  2. Seleccione la celda C1 , luego en la barra de fórmulas ingrese la fórmula a continuación:
     =1/(A1-5)

    Esta fórmula dividirá uno por A1-5, y si A1-5 resulta ser 0, el resultado será un valor indefinido.

  3. Vaya a la pestaña Datos , haga clic en Análisis hipotético y luego seleccione Buscar objetivo .
  4. En Establecer celda , escriba C1 .
  5. En Para valor , escriba 1 .
  6. Finalmente, en Cambiando el tipo de celda A1 . (Esto cambiará el valor de A1 para llegar a 1 en C1)
  7. Haga clic en Aceptar .

El cuadro de diálogo Estado de búsqueda de objetivo le dirá que es posible que no haya encontrado una solución, y puede verificar que el valor de A1 esté muy lejos.

Ahora, la solución a esta búsqueda de objetivos sería simplemente tener 6 en A1. Esto dará 1/1, que es igual a 1. Pero en un momento durante el proceso de prueba y mejora, Excel prueba 5 en A1, lo que da 1/0, que no está definido, matando el proceso.

Una forma de solucionar este problema de búsqueda de objetivos es tener un valor inicial diferente, uno que pueda evadir lo indefinido en el proceso de prueba y mejora.

Por ejemplo, si cambia A1 a cualquier número mayor que 5 y luego repite los mismos pasos para Buscar objetivo para 1, debería obtener el resultado correcto.

Haga los cálculos más eficientes con Goal Seek

El análisis hipotético y la búsqueda de objetivos pueden acelerar sus cálculos con sus fórmulas en gran medida. Aprender las fórmulas correctas es otro paso importante para convertirse en un maestro de Excel.