Goal Seek es una de las herramientas de análisis hipotético de Excel que le ayuda a encontrar el valor de entrada correcto de una fórmula para obtener el resultado deseado. Muestra cómo un valor de una fórmula afecta a otro. En otras palabras, si tiene un valor objetivo que desea alcanzar, puede utilizar la búsqueda de objetivos para encontrar el valor de entrada correcto para obtenerlo.
Por ejemplo, supongamos que obtuvo un total de 75 puntos en una materia y necesita al menos 90 para obtener una calificación de S en esa materia. Afortunadamente, tiene una última prueba que podría ayudarlo a elevar su puntaje promedio. Puede usar Goal Seek para averiguar cuánto puntaje necesita en esa prueba final para obtener una calificación de S.
Goal Seek utiliza un método de prueba y error para retroceder el problema ingresando conjeturas hasta que se llega al resultado correcto. Goal Seek puede encontrar el mejor valor de entrada para la fórmula en cuestión de segundos que habría llevado mucho tiempo averiguarlo manualmente. En este artículo, le mostraremos cómo utilizar la herramienta Buscar objetivos en Excel con algunos ejemplos.
Componentes de la función de búsqueda de objetivos
La función de búsqueda de objetivos consta de tres parámetros, a saber:
- Establecer celda - Esta es la celda en la que se ingresa la fórmula. Especifica la celda en la que desea la salida deseada.
- Valorar - Este es el valor objetivo / deseado que desea como resultado de la operación de búsqueda de objetivo.
- Cambiando de celda - Esto especifica la celda cuyo valor debe ajustarse para obtener la salida deseada.
Cómo usar Goal Seek en Excel: Ejemplo 1
Para demostrar cómo funciona con un ejemplo simple, imagina que tienes un puesto de frutas. En la siguiente captura de pantalla, la celda B11 (abajo) muestra cuánto le costó comprar frutas para su puesto y la celda B12 muestra sus ingresos totales por la venta de esas frutas. Y la celda B13 muestra el porcentaje de su beneficio (20%).
Si desea aumentar sus ganancias al "30%", pero no puede aumentar su inversión, debe aumentar sus ingresos para obtener ganancias. ¿Pero a cuánto? Goal seek te ayudará a encontrarlo.
Utiliza la siguiente fórmula en la celda B13 para calcular el porcentaje de beneficio:
= (B12-B11) / B12Ahora, desea calcular el margen de beneficio para que su porcentaje de beneficio sea del 30%. Puede hacer esto con Goal Seek en Excel.
Lo primero que debe hacer es seleccionar la celda cuyo valor desea ajustar. Cada vez que use Goal Seek, debe seleccionar una celda que contenga una fórmula o función. En nuestro caso, seleccionaremos la celda B13 porque contiene la fórmula para calcular el porcentaje.
A continuación, vaya a la pestaña "Datos", haga clic en el botón "Análisis hipotético" en el grupo Previsión y seleccione "Búsqueda de objetivos".
Aparecerá el cuadro de diálogo Buscar objetivo con 3 campos:
- Establecer celda - Ingrese la referencia de celda que contiene la fórmula (B13). Esta es la celda que tendrá la salida deseada.
- Valorar - Ingrese el resultado deseado que está tratando de lograr (30%).
- Cambiando de celda - Inserte la referencia de celda para el valor de entrada que le gustaría cambiar (B12) para llegar al resultado deseado. Simplemente haga clic en la celda o ingrese manualmente la referencia de la celda. Cuando seleccione la celda, Excel agregará el signo "$" antes de la letra de la columna y el número de fila para convertirla en una celda absoluta.
Cuando haya terminado, haga clic en "Aceptar" para probarlo.
Luego, aparecerá un cuadro de diálogo "Estado de búsqueda de objetivos" que le informará si encontró alguna solución, como se muestra a continuación. Si se ha encontrado una solución, el valor de entrada en la "celda cambiante (B12)" se ajustará a un nuevo valor. Esto es lo que queremos. Entonces, en este ejemplo, el análisis determinó que, para que pueda alcanzar su objetivo de ganancias del 30%, debe lograr un ingreso de $ 1635.5 (B12).
Haga clic en "Aceptar" y Excel cambiará los valores de las celdas o haga clic en "Cancelar" para descartar la solución y restaurar el valor original.
El valor de entrada (1635.5) en la celda B12 es lo que descubrimos usando Goal Seek para lograr nuestro objetivo (30%).
Cosas para recordar al usar Goal Seek
- La celda de conjunto siempre debe contener una fórmula que dependa de la celda "Cambiando celda".
- Solo puede usar Goal Seek en un solo valor de entrada de celda a la vez
- La celda "Al cambiar" debe contener un valor y no una fórmula.
- Si Goal Seek no puede encontrar la solución correcta, muestra el valor más cercano que puede producir y le indica que es posible que el mensaje "Buscando metas no haya encontrado una solución".
Qué hacer cuando la búsqueda de objetivos de Excel no funciona
Sin embargo, si está seguro de que existe una solución, hay algunas cosas que puede intentar para solucionar este problema.
Compruebe los parámetros y valores de Goal Seek
Hay dos cosas que debe verificar: primero, verifique si el parámetro "Establecer celda" se refiere a la celda de fórmula. En segundo lugar, asegúrese de que la celda de fórmula (Establecer celda) dependa, directa o indirectamente, de la celda cambiante.
Ajustar la configuración de iteración
En la configuración de Excel, puede cambiar la cantidad de intentos posibles que Excel puede hacer para encontrar la solución adecuada, así como su precisión.
Para cambiar la configuración del cálculo de la iteración, haga clic en "Archivo" en la lista de pestañas. Luego, haga clic en "Opciones" en la parte inferior.
En la ventana Opciones de Excel, haga clic en "Fórmulas" en el panel de la izquierda.
En la sección "Opciones de cálculo", cambie esta configuración:
- Iteraciones máximas: indica el número de posibles soluciones que Excel calculará; cuanto mayor sea el número, más iteraciones puede realizar. Por ejemplo, si establece "Iteraciones máximas" en 150, Excel probará 150 posibles soluciones.
- Cambio máximo: indica la precisión de los resultados; el número más pequeño proporciona una mayor precisión. Por ejemplo, si el valor de la celda de entrada es igual a "0" pero Goal Seek deja de calcular en "0,001", cambiarlo a "0,0001" debería resolver el problema.
Aumente el valor "Máximo de iteraciones" si desea que Excel pruebe más soluciones posibles y disminuya el valor "Cambio máximo" si desea un resultado más preciso.
La siguiente captura de pantalla muestra el valor predeterminado:
Sin referencias circulares
Cuando una fórmula hace referencia a su propia celda, se denomina referencia circular. Si desea que Excel Goal Seek funcione correctamente, las fórmulas no deben usar referencias circulares.
Ejemplo 2 de búsqueda de objetivos de Excel
Supongamos que está pidiendo prestado dinero de "$ 25000" a alguien. Te prestan el dinero a una tasa de interés del 7% mensual por un período de 20 meses, lo que hace el reembolso de "$ 1327" por mes. Pero solo puede pagar "$ 1,000" por mes durante 20 meses con un interés del 7%. Goal Seek puede ayudarlo a encontrar el monto del préstamo que produce un pago mensual (EMI) de "$ 1000".
Ingrese las tres variables de entrada que necesitará para calcular su cálculo de PMT, es decir, tasa de interés del 7%, plazo de 20 meses y monto principal de $ 25,000.
Ingrese la siguiente fórmula de PMT en la celda B5 que le dará una cantidad de EMI de $ 1,327.97.
La sintaxis de la función PMT:
= PMT (tasa de interés / 12, plazo, principal)La formula:
= PAGO (B3 / 12, B4, -B2)
Seleccione la celda B5 (celda de fórmula) y vaya a Datos -> Análisis hipotético -> Búsqueda de objetivos.
Utilice estos parámetros en la ventana "Búsqueda de objetivos":
- Establecer celda - B5 (la celda que contiene la fórmula que calcula EMI)
- Valorar - 1000 (el resultado / objetivo de la fórmula que está buscando)
- Cambiando de celda - B2 (este es el monto del préstamo que desea cambiar para lograr el valor objetivo)
Luego, presione "Aceptar" para conservar la solución encontrada o "Cancelar" para descartarla.
El análisis le dice que la cantidad máxima de dinero que puede pedir prestada es de $ 18825 si desea exceder su presupuesto.
Así es como se usa Goal Seek en Excel.