Transcripción del video
El comando Buscar objetivo nos ayuda a encontrar el resultado deseado ajustando los valores de entrada.
Dicho de otra manera, si conocemos el resultado que queremos obtener de una fórmula, pero no estamos seguros del valor a indicar en alguno de sus argumentos, podemos usar el comando Buscar objetivo para obtener una respuesta.
Este comando lo encontramos en la pestaña Datos, dentro del grupo Previsión, y dentro del botón de menú Análisis de hipótesis.
Pero hagamos un primer ejemplo para dejar en claro el funcionamiento de este comando.
En esta hoja observamos una lista de asignaturas con sus respectivas calificaciones excepto la última de ellas que es Química y que en este momento está en blanco, porque aún no hemos realizado el examen para dicha asignatura.
Sin embargo, yo soy un estudiante que tiene una beca en el colegio y por lo tanto estoy obligado a obtener un promedio de 83 para mantener esa ayuda económica o de lo contrario podría perder la beca.
La pregunta a este problema es, ¿qué calificación debo obtener en el examen de Química para que mi promedio final sea 83?
Comencemos por crear la fórmula que me ayudará a resolver esta incógnita.
En la celda B9 ingresaré el símbolo igual y la función PROMEDIO y como argumento seleccionaré el rango B2:B7.
Observa que en la selección he incluido la celda que está en blanco porque a final de cuentas quiero que esa calificación sea considerada en el promedio final.
Cierro el paréntesis de la función PROMEDIO y al pulsar Enter obtengo el promedio de 82.
Voy a ingresar manualmente la calificación 80 para química y puedes notar que el promedio final es inferior a 83 por lo que necesito una calificación superior en química.
Ingreso la calificación 85 y en este caso ya solo me faltan 5 décimas para alcanzar el promedio de 83.
Con seguridad, si ingreso la calificación 90, obtendré un promedio superior a 83.
Como puedes ver, he tenido que ingresar 3 valores diferentes en la celda B7 para tener una idea de la calificación que debo obtener en la asignatura de química para alcanzar un promedio superior a 83 pero aún no he encontrado el valor exacto que me dará el promedio de 83.
Este tipo de problema es ideal para el comando Buscar objetivo ya que conozco con antelación el resultado a obtener por la función PROMEDIO y tengo una variable cuyo valor es la incógnita.
Voy a ir a la pestaña Datos y en el grupo Previsión, haré clic en el botón Análisis de hipótesis y elegiré el comando Buscar objetivo.
Se abrirá el cuadro de diálogo Buscar objetivo el cual tiene solo 3 parámetros que tendremos que indicar.
El primero de ellos es Definir celda y se refiere a la celda que contine el resultado que queremos fijar, que en este caso es la fórmula que se encuentra en la celda B9.
Nosotros queremos que dicha fórmula nos devuelva exactamente el promedio 83 y por lo tanto ese será el valor que ingresaremos en el cuadro de texto inferior.
De esta manera estamos diciendo a Excel que queremos definir la fórmula de la celda B9 con el valor 83.
El último argumento que debemos ingresar es la celda que contiene la variable desconocida y que en este ejemplo es la celda B7 que tiene la calificación de química.
Excel cambiará automáticamente el valor de la celda B7 y hará múltiples pruebas por nosotros hasta encontrar el valor exacto que nos devuelva el promedio 83 para la fórmula de la celda B9.
Haré clic en el botón Aceptar y por unas fracciones de segundo observaremos a Excel haciendo las pruebas de cálculo y finalmente el mensaje que nos indica que se ha encontrado una solución.
Puedes ver que la celda B7 tiene la calificación 88 que ocasiona que la fórmula de la celda B9 devuelva el promedio 83.
Así es como puedo conocer fácilmente la calificación que estoy obligado a obtener en el examen de química y que me dará un promedio de 83 para mantener mi beca en el colegio.
Si quisiera revertir el cálculo hecho por el comando Buscar objetivo, debería hacer clic en el botón Cancelar y eso regresaría el último valor que tenían las celdas antes de ejecutar el comando, pero yo haré clic en Aceptar para hacer efectivo el cambio en el valor de la celda B7.
Este ha sido un ejemplo muy sencillo de lo que podemos lograr con el comando Buscar objetivo, pero hagamos un segundo ejemplo para dejar en claro el funcionamiento de este comando.
Activaré la Hoja2 donde puedes ver una lista de productos con sus respectivos precios y la cantidad de unidades que hemos vendido este mes en la empresa.
La columna Ventas, hace la multiplicación de los valores de la columna B con los valores de la columna C para obtener el monto de las ventas de cada producto y en la parte inferior, la celda D12 muestra el total de ventas en el mes.
Ahora vamos a ponernos el objetivo de aumentar nuestras ventas para el próximo mes para llegar a 80 mil pesos y quiero saber cuál es el porcentaje por el cual debo incrementar las ventas.
Antes de usar el comando Buscar objetivo, vamos a crear las fórmulas de la columna E que serán el cálculo de los nuevos montos que debemos vender y que nos llevarán a los 80 mil pesos que tenemos como objetivo.
La fórmula es la siguiente: en la celda E1 ingresaré el símbolo igual seguido de la referencia a la celda D2 y a este valor le vamos a agregar el porcentaje indicado en la celda H1.
Para hacer este cálculo ingresaré el asterisco y en seguida abriré un paréntesis donde sumaremos el 100% más el porcentaje indicado en la celda H1.
Solo debemos recordar que esta referencia debe ser absoluta y por lo tanto pulsaré el atajo F4 para convertir la referencia.
Al multiplicar el valor original de la celda D2 por un porcentaje mayor al 100% estaremos sumando el porcentaje indicado en la celda H1.
Cerraré el paréntesis y pulsaré la tecla Enter y obtendremos un incremento del 5% que es lo que está indicado en este momento en la celda H1.
Copiaré la fórmula hacia abajo y en seguida iré a la pestaña Inicio y pulsaré el comando Autosuma para obtener el total en la celda inferior.
En este momento, el 5% de incremento no nos da los 80 mil pesos que nos hemos puesto como meta para el próximo mes así que usaremos el comando Buscar objetivo para encontrar ese valor.
Iré a la pestaña Datos y en el grupo Previsión haré clic en Análisis de hipótesis y elegiré el comando Buscar objetivo.
En el cuadro de texto Definir la celda, ingresaré la referencia a la celda E12 que tiene el total de ventas y en el cuadro de texto inferior voy a colocar el objetivo que es 80 mil.
La celda que vamos a cambiar es la celda H1 ya que queremos que Excel haga pruebas con diferentes valores de porcentaje hasta llegar al valor exacto que nos dará una ganancia de 80 mil.
Pulso el botón Aceptar y después de hacer los cálculos, Excel nos muestra un porcentaje de 13.57% en la celda H1.
Voy a hacer clic en el botón Cancelar para hacer un segundo ejemplo.
De nuevo abriré el cuadro de diálogo del comando Buscar objetivo porque quiero incrementar el monto de la venta que pondremos como objetivo para el próximo mes.
Haré clic en la celda E12 que es la fórmula que contiene el objetivo, pero ahora ingresaré el valor 85000.
Y finalmente haré clic en la celda H1 para incluir la referencia al porcentaje que es variable.
Pulsaré el botón Aceptar y en esta ocasión hemos obtenido un porcentaje del 20.67% lo cual nos indica que, si queremos incrementar nuestras ventas para llegar a 85 mil pesos, podemos subir el precio un 20.67% o también podemos incrementar la cantidad de unidades vendidas por ese mismo porcentaje.
El comando Buscar objetivo es de gran ayuda para esas ocasiones en las que tenemos una fórmula donde ya conocemos con antelación el resultado que queremos obtener y vamos a indicarle una variable para la cual Excel encontrará el valor exacto que nos arrojará el resultado requerido.
La única desventaja del comando Buscar objetivo es que solo podemos tener una variable.
Si estuviéramos en una situación en la que queremos evaluar 2 o más variables, entonces necesitaremos de una herramienta más robusta como lo es el comando Solver el cual revisaremos en una ocasión posterior.