Cómo utilizar la Búsqueda de objetivos y el Solucionador de Excel para resolver variables desconocidas

Excel es muy capaz cuando tiene todos los datos que necesita para sus cálculos..

Pero ¿no sería bueno si pudiera resolver variables desconocidas?

Con Goal Seek y el complemento Solver, puede. Y te mostraremos cómo. Siga 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 usar Goal Seek en Excel

Goal Seek ya está integrado en Excel. Esta bajo el Datos pestaña, en la Y si el análisis menú:

Para este ejemplo, usaremos un conjunto muy simple de números. Tenemos tres cuartos de ventas y un objetivo anual. Podemos usar Goal Seek para averiguar cuáles deben ser los números en el cuarto trimestre para lograr el objetivo..

Como puede ver, el total de ventas actuales es de 114,706 unidades. Si queremos vender 250,000 para fin de año, ¿cuántos necesitamos vender en el cuarto trimestre? La búsqueda de objetivos de Excel nos lo dirá.

Aquí es cómo usar Goal Seek, paso a paso:

1. Hacer clic Datos> Análisis de qué hacer si> Buscar objetivos. Verás esta ventana:
   
2. Pon el “es igual a” parte de tu ecuación en el Establecer celda campo. Este es el número que Excel intentará optimizar. En nuestro caso, es el total acumulado de nuestros números de ventas en la celda B5.
3. Escriba su valor objetivo en el Valorar campo. Estamos buscando un total de 250,000 unidades vendidas, así que pondremos “250,000” en este campo.
4. Dígale a Excel qué variable resolver para en el Cambiando de celda campo. Queremos ver cuáles deben ser nuestras ventas en el cuarto trimestre. Entonces le diremos a Excel que resuelva para la celda D2. Se verá así cuando esté listo para irse:
   
5. Golpear DE ACUERDO Para resolver tu objetivo. Cuando se ve bien, solo pulsa DE ACUERDO. Excel te avisará cuando Goal Seek haya encontrado una solución.
   
6. Hacer clic DE ACUERDO de nuevo y verá el valor que resuelve su ecuación en la celda que eligió para Cambiando de celda.
   

En nuestro caso, la solución es de 135.294 unidades. Por supuesto, podríamos haberlo descubierto al restar el total acumulado de la meta anual. Pero Goal Seek también se puede utilizar en una celda que ya tiene datos en ella. Y eso es más útil..

Tenga en cuenta que Excel sobrescribe nuestros datos anteriores. Es una buena idea ejecuta Goal Seek en una copia de tus datos. También es una buena idea hacer una nota sobre los datos copiados que se generó utilizando Goal Seek. No desea confundirlo con datos actuales y precisos.

Por lo tanto, Goal Seek es una útil función de Excel. 16 Fórmulas de Excel que lo ayudarán a resolver problemas de la vida real. 16 Fórmulas de Excel que lo ayudarán a resolver problemas de la vida real. La herramienta adecuada es la mitad del trabajo. Excel puede resolver cálculos y procesar datos más rápido de lo que puede encontrar su calculadora. Le mostramos las fórmulas clave de Excel y le mostramos cómo usarlas. Lee más, pero no es tan impresionante. Echemos un vistazo a una herramienta que es mucho más interesante: el complemento Solver.

¿Qué hace el solucionador de Excel??

En resumen, Solver es como un Versión multivariable de Goal Seek.. Toma una variable de objetivo y ajusta una serie de otras variables hasta que obtiene la respuesta que desea.

Puede resolver un valor máximo de un número, un valor mínimo de un número o un número exacto.

Y funciona dentro de las restricciones, por lo que si una variable no se puede cambiar, o solo puede variar dentro de un rango específico, Solver lo tendrá en cuenta.

Es una excelente manera de resolver múltiples variables desconocidas en Excel. Pero encontrarlo y usarlo no es sencillo..

Echemos un vistazo a la carga del complemento Solver y luego comencemos a utilizar Solver en Excel 2016..

Cómo cargar el complemento Solver

Excel no tiene Solver por defecto. Es un complemento así que, al igual que otras poderosas funciones de Excel, Power Up Excel con 10 complementos para procesar, analizar y visualizar datos como un Pro Power Up Excel con 10 complementos para procesar, analizar y visualizar datos como un Pro Vanilla Excel Es increíble, pero puedes hacerlo aún más poderoso con los complementos. Cualquiera que sea la información que necesite procesar, es probable que alguien haya creado una aplicación de Excel para ella. Aquí hay una selección. Leer más, tienes que cargarlo primero. Afortunadamente, ya está en tu computadora.

Dirigirse a Archivo> Opciones> Complementos. Luego haga clic en Ir cerca de Administrar: Excel Add-Ins.

Si este desplegable dice algo más que “Complementos de Excel,” necesitarás cambiarlo:

En la ventana resultante, verás algunas opciones. Asegúrese de que la casilla junto a Complemento de Solver se comprueba, y pulsa DE ACUERDO.

Ahora verás el Solucionador botón en el Análisis grupo de los Datos lengüeta:

Si ya ha estado utilizando el Data Analysis Toolpak Cómo hacer un análisis de datos básico en Excel Cómo hacer un análisis de datos básico en Excel Excel no está destinado para el análisis de datos, pero aún puede manejar estadísticas. Le mostraremos cómo usar el complemento Data Analysis Toolpak para ejecutar las estadísticas de Excel. Lea más, verá el botón Análisis de datos. Si no, Solver aparecerá por sí mismo..

Ahora que has cargado el complemento, echemos un vistazo a cómo usarlo.

Cómo usar Solver en Excel

Hay tres partes en cualquier acción de Solver: el objetivo, las celdas variables y las restricciones. Caminaremos por cada uno de los pasos..

1. Hacer clic Datos> Solver. Verás la ventana de Parámetros de Solver a continuación. (Si no ve el botón Solver, consulte la sección anterior sobre cómo cargar el complemento de Solver).
   
2. Establece el objetivo de tu celda y dile a Excel tu objetivo. El objetivo se encuentra en la parte superior de la ventana de Solver y tiene dos partes: la celda objetivo y la opción de maximizar, minimizar o un valor específico.
   
   Si selecciona Max, Excel ajustará sus variables para obtener el mayor número posible en su celda objetivo. Min es lo contrario: Solver minimizará el número objetivo. Valor de le permite especificar un número específico para que Solver busque.
3. Elija las celdas variables que Excel puede cambiar. Las celdas variables se establecen con el Cambiando las células variables campo. Haga clic en la flecha al lado del campo, luego haga clic y arrastre para seleccionar las celdas con las que Solver debería trabajar. Tenga en cuenta que estos son todas las celdas Eso puede variar. Si no quieres que cambie una celda, no la selecciones.
   
4. Establecer restricciones en variables múltiples o individuales. Finalmente, llegamos a las restricciones. Aquí es donde Solver es realmente poderoso. En lugar de cambiar cualquiera de las celdas variables a cualquier número que desee, puede especificar restricciones que deben cumplirse. Para obtener más información, consulte la sección sobre cómo establecer restricciones a continuación..
5. Una vez que toda esta información esté en su lugar, pulsa Resolver para obtener su respuesta. Excel actualizará sus datos para incluir las nuevas variables (por eso le recomendamos que primero cree una copia de sus datos).

También puede generar informes, que veremos brevemente en nuestro ejemplo de Solver a continuación..

Cómo establecer restricciones en Solver

Podría decirle a Excel que una variable debe ser mayor a 200. Al probar diferentes valores de variable, Excel no pasará de 201 con esa variable en particular.

Para agregar una restricción, haga clic en Añadir botón junto a la lista de restricciones. Obtendrás una nueva ventana. Seleccione la celda (o celdas) a restringir en la Referencia de celda campo, luego elegir un operador.

Aquí están los operadores disponibles:

• <= (Menos que o igual a)
• = (igual a)
• => (Mayor qué o igual a)
• En t (debe ser un entero)
• compartimiento (debe ser 1 o 0)
• Todo diferente

Todo diferente Es un poco confuso. Especifica que cada celda en el rango que seleccione Referencia de celda Debe ser un número diferente. Pero también especifica que deben estar entre 1 y el número de celdas. Entonces, si tienes tres celdas, terminarás con los números 1, 2 y 3 (pero no necesariamente en ese orden)

Finalmente, agregue el valor para la restricción.

Es importante recordar que puedes seleccionar celdas múltiples para la referencia de la célula. Si quiere que seis variables tengan valores superiores a 10, por ejemplo, puede seleccionarlas todas y decirle a Solver que deben ser mayores o iguales a 11. No tiene que agregar una restricción para cada celda.

También puede usar la casilla de verificación en la ventana principal de Solver para asegurarse de que todos los valores para los que no especificó las restricciones no son negativos. Si quieres que tus variables se vuelvan negativas, desmarca esta casilla.

Un ejemplo de resolución

Para ver cómo funciona todo esto, usaremos el complemento Solver para hacer un cálculo rápido. Aquí están los datos con los que estamos empezando:

En él, tenemos cinco trabajos diferentes, cada uno de los cuales paga una tarifa diferente. 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 usar el complemento Solver para descubrir cómo maximizar el pago total y mantener ciertas variables dentro de algunas restricciones.

Aquí están las restricciones que usaremos:

• No trabajos puede caer por debajo de cuatro horas.
• Trabajo 2 debe ser más de ocho horas.
• Trabajo 5 debe ser menos de once horas.
• El total de horas trabajadas debe ser igual a 40.

Puede ser útil escribir sus restricciones de esta manera antes de usar Solver.

Así es como lo configuramos en Solver:

En primer lugar, tenga en cuenta que He creado una copia de la tabla. por lo que no sobrescribimos el original, que contiene nuestras horas de trabajo actuales.

Y segundo, ver que los valores en las restricciones mayor que y menor que son uno más alto o más bajo de lo que he mencionado anteriormente. Eso es porque no hay opciones mayores o menores que. Solo hay mayor que o igual a y menor que o igual a.

Vamos a golpear Resolver y mira lo que pasa.

¡Solver encontró una solución! Como puede ver a la izquierda de la ventana anterior, nuestras ganancias se han incrementado en $ 130. Y todas las restricciones se han cumplido.

Para mantener los nuevos valores, asegúrese Solución Solvente Solver es revisado y golpeado DE ACUERDO.

Sin embargo, si desea obtener más información, puede seleccionar un informe desde el lado derecho de la ventana. Seleccione todos los informes que desee, informe a Excel si desea que se describan (lo recomiendo) y pulse DE ACUERDO.

Los informes se generan en hojas nuevas en su libro de trabajo y le brindan información sobre el proceso que el complemento de Solver realizó para obtener su respuesta..

En nuestro caso, los informes no son muy interesantes y no hay mucha información interesante allí. Pero si ejecuta una ecuación de Solver más complicada, puede encontrar información útil sobre informes en estas nuevas hojas de trabajo. Solo haga clic en el + botón en el lado de cualquier informe para obtener más información:

Opciones avanzadas de Solver

Si no sabe mucho acerca de las estadísticas, puede ignorar las opciones avanzadas de Solver y simplemente ejecutarlo como está. Pero si está ejecutando cálculos grandes y complejos, es posible que desee examinarlos.

El más obvio es el método de resolución:

Puede elegir entre GRG Nonlinear, Simplex LP y Evolutionary. Excel proporciona una explicación simple sobre cuándo debe usar cada uno. Una mejor explicación requiere un cierto conocimiento de las estadísticas. Aprenda estadísticas gratis con estos 6 recursos. Aprenda estadísticas gratis con estos 6 recursos. Las estadísticas tienen una reputación de un tema que es difícil de entender. Pero aprender del recurso correcto lo ayudará a comprender los resultados de la encuesta, los informes de elecciones y las asignaciones de su clase de estadísticas en ningún momento. Leer más y regresión.

Para ajustar configuraciones adicionales, simplemente pulse la tecla Opciones botón. Puede decirle a Excel sobre la optimización de enteros, establecer restricciones de tiempo de cálculo (útil para conjuntos de datos masivos) y ajustar cómo GRG y los métodos de resolución evolutiva se basan en hacer sus cálculos..

Nuevamente, si no sabes qué significa esto, no te preocupes por eso. Si desea saber más sobre qué método de resolución usar, Engineer Excel tiene un buen artículo que lo expone para usted. Si quieres la máxima precisión, Evolutionary es probablemente una buena manera de hacerlo. Solo ten en cuenta que tomará mucho tiempo.

Búsqueda de objetivos y solucionador: llevar a Excel al siguiente nivel

Ahora que está cómodo con los conceptos básicos de la resolución de variables desconocidas en Excel, está abierto para usted un mundo completamente nuevo de cálculo de hojas de cálculo..

Goal Seek puede ayudarlo a ahorrar tiempo al hacer algunos cálculos más rápido, y Solver agrega una gran cantidad de poder a las habilidades de cálculo de Excel Cómo calcular estadísticas básicas en Excel: una guía para principiantes Cómo calcular estadísticas básicas en Excel: una guía para principiantes Microsoft Excel puede hacer estadísticas! Puede calcular porcentajes, promedios, desviación estándar, error estándar y pruebas T de Student. Lee mas .

Es solo cuestión de sentirse cómodo con ellos. Cuanto más los uses, más útiles serán..

¿Utilizas Goal Seek o Solver en tus hojas de cálculo? ¿Qué otros consejos puede proporcionar para obtener las mejores respuestas de ellos? Comparte tus pensamientos en los comentarios a continuación!

Explore más acerca de: Microsoft Excel, consejos de Microsoft Office, hoja de cálculo.