Con Goal Seek y el complemento Solver, se puede. Y te mostraremos cómo. Sigue leyendo para obtener una guía completa sobre cómo resolver una sola celda con Goal Seek o una ecuación más complicada con Solver.
Cómo utilizar la Búsqueda de Objetivos en Excel
La búsqueda de objetivos ya está integrada en Excel. Está bajo la opción Datos en la pestaña Análisis Y si.. menú:
Para este ejemplo, utilizaremos un conjunto de números muy sencillo. Tenemos los números de las ventas de tres trimestres y un objetivo anual. Podemos utilizar Goal Seek para averiguar cuáles son los números que hay que obtener en el cuarto trimestre para alcanzar el objetivo.
Como puedes ver, el total de ventas actual es de 114.706 unidades. Si queremos vender 250.000 a final de año, ¿cuántas tenemos que vender en el 4º trimestre? El Buscador de Objetivos de Excel nos lo dirá.
Aquí tienes cómo utilizar Goal Seek, paso a paso:
- Haz clic en Datos > Análisis Y si… > Búsqueda de objetivos. Aparecerá la ventana Búsqueda de objetivos
- Coloca la parte «igual» de tu ecuación en la casilla Celda de ajuste campo. Este es el número que Excel intentará optimizar. En nuestro caso, es el total de nuestros números de ventas en la celda A5
- Escribe el valor de tu objetivo en la casilla Al valor campo. Buscamos un total de 250.000 unidades vendidas, así que pondremos «250.000» en este campo
- Indica a Excel qué variable debe resolver en el campo Cambiando la celda campo. Queremos ver cuáles deben ser nuestras ventas en el cuarto trimestre. Así que le diremos a Excel que resuelva la celda D2. Tendrá este aspecto cuando esté listo para funcionar
- Pulsa OK para resolver tu objetivo. Cuando se vea bien, pulsa OK. Excel te avisará cuando Goal Seek haya encontrado una solución
- Haz clic en OK de nuevo, y verás el valor que resuelve tu ecuación en la celda que elegiste para Al cambiar la celda
En nuestro caso, la solución es 135.294 unidades. Por supuesto, podríamos haberlo encontrado simplemente restando el total en curso del objetivo anual. Pero la búsqueda de objetivos también puede utilizarse en una celda que ya tiene datos. Y eso es más útil.
Observa que Excel sobrescribe nuestros datos anteriores. Es una buena idea ejecutar Goal Seek en una copia de tus datos. También es una buena idea anotar en tus datos copiados que se generaron utilizando Goal Seek. No querrás confundirlos con datos actuales y precisos.
Así pues, Goal Seek es una función útil de Excel, pero no es tan impresionante. Sólo puedes utilizarla en una sola celda a la vez. Si quieres utilizar la Búsqueda de Objetivos de Excel en varias celdas simultáneamente, necesitarás una herramienta mucho más potente. Afortunadamente, una de esas herramientas viene con Excel. Echemos un vistazo al complemento Solver.
¿Qué hace el Solver de Excel?
En resumen, Solver es como un versión multivariante de Goal Seek. Si te preguntabas cómo utilizar Goal Seek en Excel para varias celdas a la vez, esto es. Toma una variable objetivo y ajusta otras variables hasta obtener la respuesta que deseas.
Puede resolver un valor máximo de un número, un valor mínimo de un número o un número exacto. Y trabaja con restricciones, de modo que si una variable no puede cambiarse, o sólo puede variar dentro de un rango especificado, Solver lo tendrá en cuenta.
Es una forma estupenda de resolver múltiples variables desconocidas en Excel. Pero encontrarlo y utilizarlo no es sencillo. Echemos un vistazo a la carga del complemento Solver, y luego pasemos a cómo utilizar Solver en la versión actual de Microsoft 365 de Excel.
Cómo cargar el complemento Solver
Excel no tiene Solver por defecto. Es un complemento, así que tienes que cargarlo primero. Afortunadamente, ya está en tu ordenador.
Dirígete a Archivo > ( Más… >) Opciones > Complementos. Luego haz clic en Ir a al lado de Gestionar: Complementos de Excel.
Si este desplegable dice algo distinto a «Complementos de Excel», tendrás que cambiarlo:
En la ventana resultante, verás unas cuantas opciones. Asegúrate de que la casilla junto a Complemento Solver está marcado, y pulsa OK.
Ahora verás el Solucionador en el botón Análisis grupo de la Datos ficha:
Si ya has utilizado el paquete de herramientas de análisis de datos, verás el botón de análisis de datos. Si no es así, aparecerá Solver por sí mismo.
Ahora que has cargado el complemento, veamos cómo utilizarlo.
Cómo utilizar el solucionador en Excel
Hay tres partes en cualquier acción de Solver: el objetivo, las celdas variables y las restricciones. Recorreremos cada uno de los pasos.
- Haz clic en Datos > Solucionador. Verás la ventana de Parámetros del Solver que aparece a continuación. (Si no ves el botón del solucionador, consulta la sección anterior sobre cómo cargar el complemento Solver)
- Establece el objetivo de tu celda e indícale a Excel tu meta. El objetivo se encuentra en la parte superior de la ventana del Solver, y tiene dos partes: la celda objetivo y la opción de maximizar, minimizar o un valor específico. Si seleccionas Max, Excel ajustará tus variables para obtener el mayor número posible en tu celda objetivo Min es lo contrario: El solucionador minimizará el número objetivo Valor de te permite especificar un número concreto para que Solver lo busque
- Elige las celdas variables que Excel puede modificar. Las celdas variables se establecen con el botón Cambiando las celdas variables campo. Haz clic en la flecha situada junto al campo, y luego haz clic y arrastra para seleccionar las celdas con las que debe trabajar Solver. Ten en cuenta que éstas son todas las celdas que pueden variar. Si no quieres que una celda cambie, no la selecciones
- Establece restricciones en variables múltiples o individuales. Por último, llegamos a las restricciones. Aquí es donde Solver es realmente potente. En lugar de cambiar cualquiera de las celdas de las variables por el número que quiera, puedes especificar las restricciones que deben cumplirse. Para más detalles, consulta la sección sobre cómo establecer restricciones más adelante
- Una vez que tengas toda esta información, pulsa Resuelve para obtener tu respuesta. Excel actualizará tus datos para incluir las nuevas variables (por eso te recomendamos que crees primero una copia de tus datos)
También puedes generar informes, que veremos brevemente en nuestro ejemplo de Solver más adelante.
Cómo establecer restricciones en el Solver
Puede que le digas a Excel que una variable tiene que ser mayor que 200. Al probar diferentes valores de la variable, Excel no baja de 201 con esa variable en particular.
Para añadir una restricción, haz clic en el botón Añadir junto a la lista de restricciones. Aparecerá una nueva ventana. Selecciona la celda (o celdas) a restringir en la ventana Referencia de celda y elige un operador.
Estos son los operadores disponibles:
- <= (menor o igual que)
- = (igual a)
- => (mayor o igual que)
- int (debe ser un número entero)
- bin (debe ser 1 o 0)
- TodoDiferente
TodoDiferente es un poco confuso. Especifica que cada celda del rango que selecciones para Referencia de celda debe ser un número diferente. Pero también especifica que deben estar entre 1 y el número de celdas. Así que si tienes tres celdas, acabarás con los números 1, 2 y 3 (pero no necesariamente en ese orden)
Por último, añade el valor de la restricción.
Es importante recordar que puedes seleccionar varias celdas para la referencia de la celda. Si quieres que seis variables tengan valores superiores a 10, por ejemplo, puedes seleccionarlas todas y decirle a Solver que deben ser mayores o iguales a 11. No tienes que añadir una restricción para cada celda.
También puedes utilizar la casilla de verificación de la ventana principal de Solver para asegurarte de que todos los valores para los que no has especificado restricciones son no negativos. Si quieres que tus variables sean negativas, desmarca esta casilla.
Un ejemplo de solucionador
Para ver cómo funciona todo esto, utilizaremos el complemento Solver para hacer un cálculo rápido. Estos son los datos con los que empezamos:
En él, tenemos cinco trabajos diferentes, cada uno de los cuales paga una tarifa distinta. También tenemos el número de horas que un trabajador teórico ha trabajado en cada uno de esos trabajos en una semana determinada. Podemos utilizar el complemento Solver para averiguar cómo maximizar el salario total manteniendo ciertas variables dentro de algunas restricciones.
Estas son las restricciones que utilizaremos:
- No hay trabajos puede ser inferior a cuatro horas
- El trabajo 4 debe ser superior a 12 horas
- El trabajo 5 debe ser menos de once horas
- El total de horas trabajadas debe ser igual a 40
Puede ser útil escribir tus restricciones de esta manera antes de utilizar el Solver.
Así es como lo configuraríamos en Solver:
En primer lugar, observa que He creado una copia de la tablapara no sobrescribir la original, que contiene nuestras horas de trabajo actuales.
Y en segundo lugar, comprueba que los valores de las restricciones mayor que y menor que son uno mayor o uno menor que lo que he mencionado anteriormente. Eso es porque no hay opciones mayores que o menores que. Sólo hay mayor que o igual a y menor que o igual a.
Vamos a darle a Resuelve y mira lo que ocurre.
¡El solucionador ha encontrado una solución! Como puedes ver a la izquierda de la ventana de arriba, nuestras ganancias han aumentado en 130$. Y se han cumplido todas las restricciones.
Para mantener los nuevos valores, asegúrate de que Mantener la solución del solucionador está marcada y golpeada OK.
Pero si quieres más información, puedes seleccionar un informe en la parte derecha de la ventana. Selecciona todos los informes que quieras, dile a Excel si los quieres esquematizados (lo recomiendo), y pulsa OK.
Los informes se generan en nuevas hojas de tu libro de trabajo y te dan información sobre el proceso que ha seguido el complemento Solver para obtener tu respuesta.
En nuestro caso, los informes no son muy emocionantes y no hay mucha información interesante. Pero si ejecutas una ecuación de Solver más complicada, puede que encuentres información útil en estas nuevas hojas de trabajo. Sólo tienes que hacer clic en el botón + en el lateral de cualquier informe para obtener más información:
Opciones avanzadas del solucionador
Si no sabes mucho de estadística, puedes ignorar las opciones avanzadas de Solver y ejecutarlo tal cual. Pero si realizas cálculos grandes y complejos, puede que te interese conocerlas.
La más obvia es el método de resolución:
Puedes elegir entre GRG no lineal, LP simple y evolutivo. Excel proporciona una explicación sencilla sobre cuándo debes utilizar cada uno. Una explicación mejor requiere algunos conocimientos de estadística y regresión de Excel.
Para ajustar los parámetros adicionales, sólo tienes que pulsar el botón Opciones botón. Puedes informar a Excel sobre la optimalidad de los enteros, establecer limitaciones de tiempo de cálculo (útil para conjuntos de datos masivos) y ajustar cómo realizan sus cálculos los métodos de resolución GRG y Evolución.
De nuevo, si no sabes lo que significa nada de esto, no te preocupes. Si quieres saber más sobre qué método de resolución utilizar, Ingeniero Excel tiene un buen artículo que te lo explica. Si quieres la máxima precisión, la Evolución es probablemente una buena manera de hacerlo. Pero ten en cuenta que te llevará mucho tiempo.
Buscador y Solucionador de Metas: Llevando a Excel al siguiente nivel
Ahora que te sientes cómodo con los fundamentos de la resolución de variables desconocidas en Excel, se abre ante ti un mundo completamente nuevo de cálculo en la hoja de cálculo. La búsqueda de objetivos puede ayudarte a ahorrar tiempo haciendo algunos cálculos más rápidos, y Solver añade una gran cantidad de potencia a las capacidades de cálculo de Excel.
Sólo es cuestión de que te sientas cómodo con ellas. Cuanto más las utilices, más útiles te resultarán.
