Buscar objetivo y análisis de hipótesis: ¿cuánto tengo que vender para llegar a la meta?
Buscar objetivo calcula una fórmula al revés: tú dices qué resultado quieres y Excel busca el valor de entrada que lo consigue. Ve a Datos › Previsión › Análisis de hipótesis › Buscar objetivo..., indica en «Definir la celda» la celda con la fórmula, en «Con el valor» la cifra que quieres y en «Para cambiar la celda» el dato que Excel puede mover. Pulsa Aceptar.
Tienes una hoja que calcula el beneficio a partir de las unidades vendidas. La pregunta del jefe, sin embargo, va al revés: «¿cuántas unidades hay que vender para ganar 2.000 €?». Lo normal es probar números a mano: 400, 450, 520… hasta acertar. Funciona, pero es lento y nunca sabes si te has pasado.
Excel trae una herramienta para esto: Buscar objetivo. Piensa en ella como un ayudante que prueba números por ti a toda velocidad. Le dices qué celda quieres que llegue a qué valor y qué dato puede cambiar, y en un segundo te da la respuesta. Forma parte del Análisis de hipótesis, el grupo de herramientas para preguntar «¿y si…?», junto con el Administrador de escenarios.
En esta guía calculas un objetivo de ventas, el importe máximo de un préstamo según la cuota que puedes pagar y comparas tres escenarios sin duplicar la hoja.
Qué vas a aprender
- Cómo preparar la hoja para que Buscar objetivo funcione a la primera
- Cómo calcular cuánto vender para llegar a un beneficio concreto
- Cómo saber cuánto puedes pedir prestado según la cuota que puedes pagar
- Cómo guardar y comparar escenarios con el Administrador de escenarios
Paso a paso
Monta el modelo: datos de entrada y una fórmula de resultado
Buscar objetivo necesita dos cosas: una celda de entrada con un número escrito a mano (aquí, las unidades vendidas en B2) y una celda de resultado con una fórmula que dependa de ella (el beneficio en B6).
Con 300 unidades, a 25 € de precio y 15 € de coste por unidad, y 3.000 € de costes fijos, el beneficio es exactamente 0 €: es tu punto de equilibrio, el punto en el que no ganas ni pierdes.
Unidades por margen unitario, menos los costes fijos.
Escribe la fórmula y comprueba el resultado con una calculadora antes de seguir: Buscar objetivo solo es tan fiable como tu modelo.
Abre Datos › Previsión › Análisis de hipótesis
Ve a la pestaña Datos. En el grupo Previsión, haz clic en Análisis de hipótesis. Se despliega un menú con tres herramientas.
Elige «Buscar objetivo...»
En el menú, elige Buscar objetivo.... Las otras dos opciones también responden a preguntas «¿y si…?»: el Administrador de escenarios guarda juegos de valores para compararlos (lo usas en el paso 7) y la Tabla de datos calcula un resultado para muchos valores de entrada a la vez.
Rellena las tres casillas: qué celda, qué valor, qué cambiar
Se abre la ventana Buscar objetivo. Rellénala así:
- Definir la celda:
B6, la celda con la fórmula del beneficio. - Con el valor:
2000, el beneficio que quieres. Escríbelo como número, sin el símbolo €. - Para cambiar la celda:
B2, las unidades vendidas.
Pulsa Aceptar.
«Con el valor» solo admite un número escrito; no acepta una referencia a otra celda. Y «Para cambiar la celda» debe contener un número, no una fórmula.
Revisa la solución y decide si la aceptas
Excel prueba valores y muestra una ventana de estado con el valor objetivo y el valor actual. En la hoja ya ves la respuesta: 500 unidades. Compruébalo: 500 × (25 − 15) − 3.000 = 2.000 €.
Si pulsas Aceptar, el 500 se queda en B2. Si pulsas Cancelar, vuelve el valor original (300). Si crees que vas a querer volver atrás, anota antes el valor original.
Si el resultado sale con decimales largos (por ejemplo 499,9999), es normal: Excel se detiene cuando está lo bastante cerca. Redondea al presentarlo.
Úsalo con un préstamo: ¿cuánto puedo pedir si pago 300 € al mes?
La misma técnica sirve para finanzas personales o de empresa. En B1 pon el importe del préstamo (10.000 €), en B2 el interés anual (5 %) y en B3 los meses (60). En B4, calcula la cuota con PAGO: el interés se divide entre 12 porque pagas cada mes, y el importe va en negativo para que la cuota salga en positivo. Da 188,71 €.
Ahora abre Buscar objetivo con Definir la celda B4, Con el valor 300 y Para cambiar la celda B1. Excel responde: puedes pedir unos 15.897,21 €.
Cuota mensual de un préstamo de B1 euros al interés anual de B2 durante B3 meses.
Compara escenarios con el Administrador de escenarios
Buscar objetivo da una respuesta. Para comparar varias hipótesis, usa Datos › Previsión › Análisis de hipótesis › Administrador de escenarios.... Pulsa Agregar..., ponle nombre (Pesimista), indica las celdas cambiantes (B2) y escribe su valor (250). Repite con Realista (400) y Optimista (600).
Selecciona un escenario y pulsa Mostrar para ver la hoja con esos valores: beneficio de −500 €, 1.000 € y 3.000 €. Con Resumen..., Excel crea una hoja nueva con todos los escenarios lado a lado, lista para enseñar en una reunión.
Trucos de experto
- Separa entradas y fórmulas. Pon los datos que se pueden tocar en un bloque con relleno amarillo y los resultados en otro. Así sabes siempre qué celda poner en «Para cambiar la celda».
- Pon nombre a las celdas clave. Si llamas Beneficio a
B6y Unidades aB2, puedes escribir esos nombres en la ventana de Buscar objetivo y no te equivocas de celda. Te lo explicamos en Nombres definidos y LET. - Para varias incógnitas, usa Solver. Buscar objetivo solo cambia una celda. Si necesitas mover varias a la vez o poner límites («como mucho 800 unidades»), activa el complemento Solver en Archivo › Opciones › Complementos; después aparece en la pestaña Datos.
- Pídele el modelo a la IA, calcula tú el objetivo. ChatGPT puede proponerte la fórmula del beneficio o de la cuota, pero compruébala con un caso que puedas calcular a mano antes de lanzar Buscar objetivo. Más ideas en pedir fórmulas a la IA y comprobarlas.
Errores comunes y cómo arreglarlos
Excel dice que no puede encontrar una solución o da un número absurdo.
Comprueba que la celda de «Definir la celda» tiene una fórmula que depende, directa o indirectamente, de la celda que cambias. Prueba también a poner un valor inicial más cercano a la respuesta y repite.
La ventana no deja elegir la celda que quieres cambiar.
«Para cambiar la celda» tiene que contener un valor escrito, no una fórmula. Si allí hay una fórmula, cambia la celda de entrada de la que depende.
Aceptas el resultado y pierdes el dato original.
Buscar objetivo sobrescribe la celda de entrada. Antes de lanzarlo, copia el valor original al lado o guarda la situación actual como escenario en el Administrador de escenarios.
En Excel para Mac el camino es el mismo: pestaña Datos › Análisis de hipótesis › Buscar objetivo... La ventana tiene las mismas tres casillas.
Preguntas frecuentes
¿Qué diferencia hay entre Buscar objetivo y una fórmula normal?
Una fórmula va de los datos al resultado. Buscar objetivo hace el camino inverso: parte del resultado que quieres y encuentra el dato de entrada. No crea ninguna fórmula nueva; solo cambia el valor de una celda.
¿Se actualiza solo si cambio el precio?
No. Buscar objetivo calcula una vez. Si cambias el precio o los costes, tienes que lanzarlo de nuevo. Si quieres un resultado que se recalcule solo, despeja la incógnita con una fórmula: por ejemplo, =(2000+B5)/(B3-B4) da las unidades necesarias para ganar 2.000 €.
¿Puedo usarlo con una tabla de presupuesto real?
Sí, siempre que el resultado sea una fórmula que dependa de la celda que cambias. Es muy útil para fijar un tope de gasto en un presupuesto mensual.
Mi Excel da error al copiar la fórmula con «;». ¿Qué hago?
En algunos países (por ejemplo, México) Excel usa la coma como separador. Cambia ; por , o usa el botón de esta página que lo hace por ti.
Se guarda solo en este navegador.
Sigue aprendiendo
Haz un presupuesto mensual que se calcula solo
Crea en Excel un presupuesto mensual con ingresos, gastos por categoría, balance y gráfico que se actualiza solo al anotar cada gasto. Paso a paso para empezar.
Nombres definidos y LET: fórmulas que se entienden al leerlas
Pon nombre a celdas y rangos y usa LET para que tus fórmulas se lean como una frase, sin $ ni cálculos repetidos. Paso a paso con un ejemplo de factura.
Tus primeras fórmulas: SUMA, PROMEDIO, MAX, MIN y Autosuma
Escribe tus primeras fórmulas en Excel: suma un rango con SUMA, calcula la media con PROMEDIO, encuentra el máximo y el mínimo y usa Autosuma, paso a paso.
Pedir fórmulas a ChatGPT (o a cualquier IA) y comprobar que están bien
Aprende a pedir fórmulas de Excel a ChatGPT o a otra IA, a adaptarlas a Excel en español y a comprobar que están bien. Paso a paso, con ejemplos reales.
Referencias relativas y absolutas ($A$1): por qué tu fórmula se rompe al arrastrarla
Fija una celda con $ para que tu fórmula no se descuadre al arrastrarla. Referencias relativas, absolutas y mixtas, con la tecla F4, paso a paso.