Excel 2019 (Parte 28): Análisis Y Si (Análisis Si-Entonces)

Excel incluye potentes herramientas para realizar cálculos matemáticos complejos, incluido el análisis What-if. Esta función puede ayudarle a experimentar y responder preguntas con sus datos, incluso si están incompletos. En este artículo, aprenderá a utilizar una herramienta de análisis llamada Goal Seek.

Buscar objetivo

Siempre que creas una fórmula o función en Excel, debes combinar diferentes partes para calcular el resultado. Goal Seek funciona de manera opuesta: le permite comenzar con un resultado deseado y el motor calculará el valor de entrada que le brinda ese resultado. El artículo utilizará algunos ejemplos para mostrar cómo utilizar la búsqueda de objetivos.

Ejemplo 1 de cómo utilizar la búsqueda de objetivos

Digamos que estás inscrito en una clase. Actualmente tienes 65 puntos y necesitas obtener al menos 70 puntos para aprobar esta asignatura. Afortunadamente, tienes una última tarea que puede ayudarte a aumentar tu GPA. Puedes utilizar Goal Seek para saber qué puntuación necesitas en el ejercicio final para aprobar la asignatura.

En la imagen a continuación, puedes ver que las puntuaciones de los primeros 4 ejercicios son 58, 70, 72 y 60. Aunque se desconoce cuál será la puntuación del quinto ejercicio, puedes escribir una fórmula -o función- para calcular la puntuación final necesaria. En este caso, cada tarea tiene el mismo peso, por lo que todo lo que tenemos que hacer es promediar las cinco puntuaciones ingresando =PROMEDIO(B2:B6). Al utilizar Goal Seek, la celda B6 mostrará la puntuación mínima requerida para esa tarea final.

1. Seleccione la celda cuyo valor desea cambiar. Siempre que utilice la búsqueda de objetivos, deberá seleccionar una celda que ya contenga una fórmula o función. Por ejemplo, seleccionaremos la celda B7 porque contiene la fórmula =PROMEDIO(B2:B6).

2. En la pestaña Datos , haga clic en el comando Análisis What-If y luego seleccione Búsqueda de objetivo en el menú desplegable.

3. Aparecerá un cuadro de diálogo con tres campos. El primer campo, Establecer celda: contendrá el resultado deseado. En el ejemplo, se ha seleccionado la celda B7.

El segundo campo, Valorar: , es el resultado deseado. Por ejemplo, ingresaríamos 70 porque necesitamos obtener al menos ese número para aprobar la clase.

El tercer campo, Cambiando de celda: , es la celda donde Goal Seek colocará su respuesta. Por ejemplo, elegiremos la celda B6 porque queremos determinar la puntuación que debemos conseguir en el ejercicio final.

4. Cuando haya terminado, haga clic en Aceptar.

5. El cuadro de diálogo le indicará si Goal Seek pudo encontrar la respuesta o no. Haga clic en Aceptar.

6. Los resultados aparecerán en la celda especificada. En el ejemplo, Goal Seek calculó que para aprobar se necesitaría una puntuación de al menos 90 en la tarea final.

Ejemplo 2 de uso de la búsqueda de objetivos

Digamos que estás planeando un evento y quieres invitar a tantas personas como sea posible sin exceder tu presupuesto de $500. Puedes utilizar Goal Seek para saber cuántas personas invitar. En el siguiente ejemplo, la celda B5 contiene la fórmula =B2+B3*B4 para calcular el coste total de la reserva más el coste por persona.

1. Seleccione la celda cuyo valor desea cambiar. Por ejemplo, seleccionaremos la celda B5.

2. En la pestaña Datos , haga clic en el comando Análisis What-If y luego seleccione Búsqueda de objetivo en el menú desplegable.

3. Aparecerá un cuadro de diálogo con tres campos. El primer campo, Establecer celda: contendrá el resultado deseado. En el ejemplo, se ha seleccionado la celda B5.

El segundo campo, Valorar: , es el resultado deseado. Por ejemplo, ingresaría 500 porque solo quiero gastar $500.

El tercer campo, Cambiando de celda: , es la celda donde Goal Seek colocará su respuesta. Por ejemplo, elegiría la celda B4 porque quiero saber cuántos invitados puedo invitar sin tener que gastar más de $500.

4. Cuando haya terminado, haga clic en Aceptar.

5. El cuadro de diálogo le indicará si Goal Seek pudo encontrar la respuesta o no. Haga clic en Aceptar.

6. Los resultados aparecerán en la celda especificada. En el ejemplo, Goal Seek calculó que la respuesta era aproximadamente 18,62. En este caso, la respuesta final debe ser un número entero, por lo que es necesario redondear la respuesta hacia arriba o hacia abajo. Dado que el redondeo excedería el presupuesto, el ejemplo sería redondear hacia abajo a 18 invitados.

Como puede ver en el ejemplo anterior, algunas situaciones requerirán una respuesta que sea un número entero. Si Goal Seek te da un número decimal, tendrás que redondearlo hacia arriba o hacia abajo, según la situación.

Otros tipos de análisis hipotético

Para proyectos más avanzados, es posible que desee considerar otros tipos de análisis hipotéticos: escenarios y tablas de datos. En lugar de comenzar desde el resultado deseado y trabajar hacia atrás, como con Goal Seek, estas opciones le permiten probar múltiples valores y ver cómo cambian los resultados.

Los scripts le permiten reemplazar valores de varias celdas (hasta 32) a la vez. Puedes crear tantos escenarios como quieras y luego compararlos sin cambiar los valores manualmente. El siguiente ejemplo utiliza escenarios para comparar diferentes ubicaciones para un próximo evento.

Las tablas de datos le permiten tomar una o dos variables en una fórmula y reemplazarlas con tantos valores diferentes como desee, luego ver los resultados en una tabla. Esta opción es especialmente poderosa porque muestra múltiples resultados a la vez, a diferencia de los escenarios o la búsqueda de objetivos. En el siguiente ejemplo, se pueden ver 24 resultados posibles para un préstamo de automóvil.