Cuando trabajas con hojas de cálculo complejas en Excel, llega un punto en el que no solo quieres calcular resultados, sino también averiguar qué valor necesitas cambiar para lograr una cifra concreta. Ahí es donde entra en juego Buscar Objetivo y el uso de escenarios, dos herramientas de análisis de hipótesis que te permiten responder justo a esa pregunta: “¿qué tengo que cambiar para llegar a este resultado?”.
Si alguna vez has pensado cosas como “¿qué tipo de interés necesito para pagar esta cuota?”, “¿cuántas unidades tengo que vender para ganar X euros?” o “¿qué pasa si mis ingresos suben o bajan?”, te interesa dominar estas funciones. A lo largo de este artículo veremos cómo usar Buscar Objetivo para resolver escenarios en Excel, cómo combinarlo con el Administrador de escenarios y en qué casos prácticos te van a ahorrar mucho tiempo y quebraderos de cabeza.
¿Qué es Buscar Objetivo en Excel y para qué sirve?
La herramienta Buscar Objetivo forma parte del análisis de hipótesis (What-If Analysis) de Excel. Dentro de este grupo de utilidades, Excel ofrece tres opciones principales: el Administrador de escenarios, las Tablas de datos y la propia Buscar Objetivo. Todas sirven para hacerse la típica pregunta “¿y si…?”, pero cada una lo hace de forma distinta.
En el caso concreto de Buscar Objetivo, la idea es muy sencilla: tú indicas un resultado final que quieres conseguir en una celda que ya contiene una fórmula, y Excel calcula automáticamente qué valor debe tomar otra celda (la celda variable) para que esa fórmula devuelva justo ese resultado objetivo.
Por ejemplo, puedes tener una fórmula que calcule la cuota de un préstamo, el beneficio de un producto, una nota media o el margen de una inversión. En lugar de probar números a ojo, dejas que Buscar Objetivo ajuste la celda que elijas hasta que el resultado de la fórmula coincida con la cifra que le has marcado.
Es importante entender que Buscar Objetivo trabaja siempre con una sola celda objetivo y una sola celda que cambia. Si necesitas variar varias celdas a la vez o resolver problemas con más restricciones, ahí ya entraríamos en terreno de Solver u otras técnicas más avanzadas.
Relación con otras herramientas de análisis de hipótesis
Además de Buscar Objetivo, Excel incluye otras dos herramientas de análisis de hipótesis muy útiles: el Administrador de escenarios y las Tablas de datos. Aunque este artículo se centra en Buscar Objetivo y escenarios, conviene tener claro cómo encajan entre sí.
El Administrador de escenarios permite guardar varios conjuntos de valores para unas mismas celdas cambiantes y alternar rápidamente entre diferentes situaciones (por ejemplo, peor caso, caso medio y mejor caso). De esta forma puedes ver cómo cambia el resultado de forma global al modificar varias variables al mismo tiempo.
Las Tablas de datos, por su parte, sirven para calcular de golpe muchas combinaciones posibles de una o dos variables y observar cómo varía el resultado de una fórmula en cada caso. Es ideal, por ejemplo, para ver cómo afecta un tipo de interés y un plazo diferentes al pago de un préstamo.
Buscar Objetivo se sitúa entre ambos: no genera una tabla de escenarios ni guarda varios juegos de datos, sino que se centra en resolver un problema muy concreto con una sola incógnita. Aun así, combinando Buscar Objetivo con el Administrador de escenarios puedes plantear análisis muy potentes, guardando resultados clave como escenarios predefinidos.
Ejemplo práctico: calcular la tasa de interés con Buscar Objetivo
Uno de los usos clásicos de Buscar Objetivo es averiguar qué tipo de interés necesitas para conseguir una cuota mensual determinada en un préstamo. Vamos a verlo con un ejemplo detallado, similar a los que propone la documentación oficial de Excel, pero explicado con otras palabras.
Preparar la hoja de cálculo para el préstamo
Primero necesitamos crear una hoja sencilla donde tengamos bien separados los datos conocidos, la incógnita y el resultado. Así será más fácil usar la herramienta Buscar Objetivo y entender lo que hace.
- Abre una hoja de cálculo en blanco y reserva la columna A para las etiquetas. La idea es que la hoja se lea de un vistazo.
- Escribe los rótulos en la primera columna:
– En la celda A1: Importe del préstamo
– En la celda A2: Plazo en meses
– En la celda A3: Tasa de interés
– En la celda A4: Pago mensual - A continuación introduce los valores que ya conoces en la columna B:
– En la celda B1 escribe 100000 (importe que quieres solicitar).
– En la celda B2 escribe 180 (número de meses en los que piensas devolver el préstamo). - La celda B3 se reservará para la tasa de interés, que es el valor desconocido. De momento, déjala en blanco.
- En la celda B4 introduces la fórmula del pago mensual utilizando la función PAGO (PMT en algunas versiones):
=PAGO(B3/12;B2;B1)
En esta fórmula, la tasa de interés de B3 se divide entre 12 porque estamos considerando pagos mensuales y un tipo anual. Como en B3 no hay nada todavía, Excel toma la tasa como 0 % y te mostrará un pago alrededor de 555,56 €. Ese valor de momento no nos interesa: simplemente necesitamos una fórmula en B4 para poder aplicar Buscar Objetivo.
Aplicar Buscar Objetivo para hallar la tasa de interés
Una vez tienes la hoja montada, lo que quieres es averiguar qué tipo de interés tendría que tener el préstamo para que la cuota mensual fuera, por ejemplo, de 900 €. Excel puede resolverlo automáticamente siguiendo esta lógica: “ajusta la celda B3 hasta que la fórmula de B4 dé el resultado que quiero”.
- Ve a la pestaña Datos de la cinta de opciones y localiza el grupo donde aparece “Análisis de hipótesis” o “What-If Analysis”. Dentro, haz clic en Buscar objetivo.
- Se abrirá el cuadro de diálogo de Buscar Objetivo, con tres campos clave:
– Establecer celda: aquí debes indicar la celda que contiene la fórmula cuyo resultado quieres forzar. En nuestro ejemplo, escribe B4.
– Valor a: es el resultado que quieres obtener en esa celda. Como la función PAGO devuelve un flujo de caja de salida, deberás escribir -900 (con signo negativo).
– Cambiando la celda: aquí indicas qué celda debe ajustar Excel. En este caso será B3, que es la tasa de interés. - Cuando todo esté configurado, pulsa Aceptar para que Excel lance el cálculo iterativo.
- En unos segundos, Excel mostrará un cuadro con la solución encontrada. Verás que ha ajustado el valor de B3 hasta llegar a una tasa de interés aproximada que hace que la fórmula de B4 devuelva -900. Esa será la tasa anual necesaria para que la cuota se sitúe en 900 € mensuales con ese capital y ese plazo.
- Para que la tasa se vea correctamente, aplica formato de porcentaje a la celda B3. Desde la pestaña Inicio, en el grupo Número, haz clic en Porcentaje y ajusta los decimales con Aumentar o Disminuir decimales hasta que tengas la precisión que quieras.
Con esto ya has usado Buscar Objetivo para resolver un escenario financiero muy típico: encontrar el tipo de interés compatible con un importe de préstamo, un plazo fijo y una cuota máxima que estás dispuesto a pagar.
Uso de escenarios en Excel para analizar diferentes casos
Mientras que Buscar Objetivo se centra en una sola incógnita, los escenarios de Excel permiten comparar varios conjuntos de valores para un mismo modelo. Es decir, no solo quieres saber qué valor concreto hace que se cumpla una condición, sino ver cómo cambian los resultados ante distintos supuestos de ingresos, costes o cantidades.
Imagina que estás montando un presupuesto y quieres jugar con diferentes hipótesis sobre tus ingresos y tus costes de bienes vendidos. En lugar de sobrescribir números todo el rato, puedes guardar varios escenarios y cambiar de uno a otro con un par de clics, viendo cómo varía el beneficio bruto.
Ejemplo: escenarios de presupuesto (peor caso y mejor caso)
Supongamos que creas una hoja de cálculo con ingresos brutos, costes de bienes vendidos (COGS) y beneficio bruto. La fórmula del beneficio es algo tan sencillo como Beneficio bruto = Ingresos – Costes, por ejemplo en la celda B4 con la fórmula =B2-B3.
Planteas dos escenarios: uno pesimista (peor caso) y otro optimista (mejor caso). Para el peor caso, decides que podrías tener unos ingresos de 50.000 € y unos costes de 13.200 €, dejando un beneficio bruto de 36.800 €. Estos valores los introduces en la hoja, con las celdas B2 y B3 como celdas cambiantes y B4 como celda resultado.
Para guardar este conjunto de datos como un escenario, haces lo siguiente:
- Ve a la pestaña Datos > Análisis de hipótesis > Administrador de escenarios.
- En el cuadro de diálogo del Administrador de escenarios, pulsa en Agregar.
- En el campo Nombre del escenario, escribe algo descriptivo, por ejemplo “Peor caso”.
- En el área de celdas cambiantes, indica que quieres que formen parte del escenario las celdas B2 y B3, que son las que van a variar entre un escenario y otro.
Si antes de pulsar Agregar habías seleccionado B2 y B3 en la hoja, el propio Administrador de escenarios rellenará esas celdas por ti. Si no, puedes escribirlas manualmente o usar el selector de rangos que aparece al lado del cuadro de texto.
Dentro del mismo cuadro de diálogo, tienes una sección de Protección que te permite controlar si se pueden cambiar los escenarios cuando la hoja esté protegida y si quieres ocultarlos. Puedes marcar Evitar cambios para que los escenarios no se editen cuando protejas la hoja y Oculto para que no se muestren a otros usuarios en esas condiciones.
Para el escenario optimista, planteas que los ingresos brutos suben a 150.000 € y los costes a 26.000 €, con lo que el beneficio bruto pasa a ser 124.000 €. Introduces estos valores en B2 y B3, dejas B4 con la misma fórmula (=B2-B3) y creas otro escenario en el Administrador de escenarios, llamándolo por ejemplo “Mejor caso”. Al guardar este segundo escenario, las celdas cambiantes vuelven a ser B2 y B3, y no necesitas tocar la celda de beneficio porque se recalcula automáticamente.
Una vez que has definido y guardado tus escenarios, podrás seleccionarlos desde el Administrador de escenarios para mostrarlos en cualquier momento. Cada vez que elijas uno, Excel actualizará las celdas B2 y B3 con los valores asociados a ese escenario, y la celda B4 mostrará el beneficio correspondiente. Es una manera muy rápida de ver el impacto de distintas combinaciones de ingresos y costes sobre el resultado final.
Cómo usar Buscar Objetivo paso a paso en ejemplos de negocio
La teoría está muy bien, pero donde de verdad se aprecia la potencia de esta herramienta es en problemas reales. A continuación se muestran varios escenarios en los que la búsqueda de objetivos en Excel se convierte en tu mejor aliada.
Ejemplo 1: cuántas unidades vender para lograr un beneficio
Imagina que quieres evaluar la rentabilidad de un producto. Sabes cuál es el coste unitario, el precio de venta por unidad y quieres investigar cuántas unidades tendrás que vender para alcanzar un beneficio determinado. El beneficio lo podrías calcular con una fórmula del tipo Beneficio = (Precio – Coste) × Unidades vendidas.
En la hoja puedes tener algo así:
– Coste unitario en una celda (por ejemplo B1).
– Precio unitario en otra celda (B2).
– Número de unidades vendidas en B3.
– Beneficio total en B4, con una fórmula del estilo =(B2-B1)*B3.
Si fijaras como objetivo un beneficio de 50.000 €, podrías usar Buscar Objetivo del siguiente modo: defines la celda B4 como Establecer celda, escribes 50000 en el campo Valor a y escoges B3 como celda que cambia. Tras ejecutar la herramienta, Excel calculará automáticamente cuántas unidades tendrías que vender con ese coste y ese precio para alcanzar el beneficio deseado.
Ejemplo 2: averiguar el precio necesario para un beneficio dado
También puedes invertir el planteamiento y utilizar Buscar Objetivo para saber a qué precio tendrías que vender si ya conoces tu capacidad de producción. Si tienes un tope de 500 unidades que puedes fabricar y quieres lograr esos mismos 50.000 € de beneficio, puedes usar el mismo modelo con una variación.
En este caso, dejas fijo el número de unidades vendidas (por ejemplo B3 = 500), mantienes el coste unitario en B1, y en B2 (precio) colocas un valor inicial cualquiera. La fórmula del beneficio seguirá en B4, como antes.
En el cuadro de diálogo de Buscar Objetivo, indicarás B4 como Establecer celda, 50000 como Valor a y B2 como celda que cambia. Al aceptar, Excel modificará el precio unitario de forma iterativa hasta encontrar el valor que hace que el beneficio sea exactamente 50.000 € con 500 unidades. Así obtendrás el precio mínimo de venta que te permite cumplir tu objetivo.
Ejemplo 3: cálculo del importe máximo de un préstamo
El caso contrario al de la tasa de interés es preguntarse cuánto puedes pedir prestado sabiendo la cuota máxima que puedes pagar, el tipo de interés y el plazo. Aquí la incógnita sería el importe del préstamo, mientras que la fórmula seguiría basada en la función PAGO.
Podrías estructurar la hoja de esta forma: tasa de interés en una celda (por ejemplo B1), número de meses en B2, importe del préstamo en B3 y pago mensual en B4 con la fórmula =PAGO(B1/12;B2;B3). Si tu presupuesto máximo para la cuota mensual es de 5.000 €, y conoces el tipo de interés (por ejemplo un 8 %) y 120 meses de plazo, usarías Buscar Objetivo estableciendo B4 como celda objetivo, -5000 como Valor a y B3 como celda que cambia.
Al ejecutar la herramienta, Excel ajustará el importe del préstamo de B3 hasta que la cuota mensual resultante encaje con tu límite. De este modo sabes cuánto puedes permitirte pedir con esas condiciones financieras.
Ejemplo 4: determinar la nota necesaria para mantener una beca
Buscar Objetivo no solo sirve para temas de dinero. También puedes aplicarlo a notas académicas, por ejemplo para mantener una beca que exige una media mínima. Imagina que ya tienes todas las calificaciones salvo una (por ejemplo, la nota de Matemáticas) y quieres saber qué puntuación necesitas sacar en ese examen para alcanzar una media de 70 puntos.
Si calculas la media ponderada mediante funciones como SUMAPRODUCTO y SUMA, tendrás en una celda una fórmula que devuelve el promedio general. Supón que esa celda es B10. La nota desconocida (Matemáticas) estará en otra celda, digamos B5.
Con Buscar Objetivo, seleccionas B10 como celda a establecer, introduces 70 como Valor a y especificas B5 como celda que cambia. Excel irá probando valores para la nota de Matemáticas hasta lograr una media de 70, indicándote la puntuación mínima que necesitas obtener para conservar la beca.
Otras aplicaciones habituales de Buscar Objetivo
Más allá de estos ejemplos, hay un montón de situaciones del día a día en las que Buscar Objetivo encaja como un guante. Siempre que tengas una fórmula cuyo resultado quieres fijar y una única entrada que quieras ajustar, es una buena candidata.
- Cálculo de ingresos: determinar el volumen de ventas necesario para llegar a un objetivo de facturación anual o mensual.
- Rentabilidad e inversiones: encontrar el rendimiento que necesitaría una inversión para generar un flujo de caja concreto o la aportación mensual requerida para alcanzar un capital futuro.
- Simulaciones deportivas: calcular cuántos puntos, goles o victorias necesita un equipo para terminar un torneo por encima de un rival o dentro de una posición clasificatoria determinada.
- Proyecciones de resultados electorales: estimar qué porcentaje de voto debería obtener un partido para lograr un número de escaños concreto en un sistema de reparto dado.
En realidad, siempre que tu modelo esté correctamente planteado en Excel y el resultado dependa de una sola variable principal, la herramienta te dará una respuesta rápida sin tener que hacer pruebas manuales.
Por qué a veces no funciona Buscar Objetivo y cómo solucionarlo
Puede ocurrir que intentes usar Buscar Objetivo y obtengas resultados raros, que no converja o que directamente no logre encontrar una solución válida. En esos casos conviene revisar varios aspectos tanto de la configuración de la herramienta como del propio modelo de hoja de cálculo.
Ajustes de iteración y precisión
Buscar Objetivo funciona por iteraciones: va probando valores en la celda variable hasta acercarse lo suficiente al resultado que quieres. Si los parámetros de iteración son demasiado restrictivos, puede que se quede corto y no llegue a una buena solución.
En las opciones avanzadas de Excel (Archivo > Opciones > Fórmulas) puedes modificar el número máximo de iteraciones y el cambio máximo permitido. Aumentar el número máximo de iteraciones permite que se prueben más valores antes de renunciar, y reducir el cambio máximo hace que la búsqueda sea más precisa, a costa de tardar algo más.
Revisar fórmulas y parámetros
Otro motivo muy habitual de fallo es que la fórmula de la celda objetivo no esté bien planteada o que los parámetros en el cuadro de diálogo de Buscar Objetivo no sean coherentes. Conviene repasar con calma que:
- La celda de “Establecer” contiene realmente una fórmula dependiente de la celda que cambia.
- Has introducido el valor objetivo con el signo correcto (por ejemplo, pagos negativos cuando se trata de salidas de caja).
- La celda que cambia no está limitada por otras restricciones que hagan imposible alcanzar el resultado (por ejemplo, fórmulas, validaciones demasiado rígidas, etc.).
También es clave comprobar que tu modelo no genere referencias circulares (una fórmula que termina dependiendo de sí misma). Si la celda objetivo depende, directa o indirectamente, de la celda variable de una forma circular, Buscar Objetivo no podrá trabajar correctamente y probablemente te mostrará avisos de error o resultados poco fiables.
Dominar Buscar Objetivo y el uso de escenarios en Excel te abre la puerta a un tipo de análisis mucho más potente y flexible, en el que dejas de limitarte a “ver qué pasa” y pasas a “averiguar qué necesito para que pase lo que quiero”; desde diseñar presupuestos con casos pesimistas y optimistas hasta calcular precios, cuotas, préstamos o notas mínimas, estas herramientas convierten tus hojas en laboratorios de simulación muy prácticos para tomar decisiones mejor informadas sin perderte en pruebas manuales interminables. Comparte este tutorial y ayuda a otros a usar la función de buscar objetivo en Excel.