Cómo usar la búsqueda y solución de objetivos 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. Está bajo el Datos pestaña, en el Y si el análisis menú:

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

Como puede ver, el total de ventas actual 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 dirá.

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

1. Hacer clic Datos> Análisis hipotético> Búsqueda de objetivos. Verás esta ventana:
   
2. Pon el “es igual” 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 el valor de su 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 en el Al cambiar de celda campo. Queremos ver cuáles deben ser nuestras ventas en el cuarto trimestre. Entonces le diremos a Excel que resuelva la celda D2. Se verá así cuando esté listo:
   
5. Golpear Okay para resolver tu objetivo. Cuando se ve bien, solo presiona Okay. Excel le avisará cuando Goal Seek haya encontrado una solución.
   
6. Hacer clic Okay nuevamente y verá el valor que resuelve su ecuación en la celda que eligió para Al cambiar de celda.
   

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

Tenga en cuenta que Excel sobrescribe nuestros datos anteriores. Es una buena idea ejecute Goal Seek en una copia de sus datos. También es una buena idea tomar nota de los datos copiados que se generaron con Goal Seek. No desea confundirlo con los datos actuales y precisos..

Por lo tanto, Goal Seek es una característica útil 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 fórmulas clave de Excel y le mostramos cómo usarlas. , 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 multivariada de Goal Seek. Toma una variable objetivo y ajusta una serie de otras variables hasta que obtenga 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, luego veamos cómo usar Solver en Excel 2016.

Cómo cargar el complemento Solver

Excel no tiene Solver por defecto. Es un complemento, así como otras potentes funciones de Excel Power Excel con 10 complementos para procesar, analizar y visualizar datos como un profesional Power Excel con 10 complementos para procesar, analizar y visualizar datos como un profesional Vanilla Excel es sorprendente, pero puedes hacerlo aún más poderoso con complementos. Independientemente de los datos que necesite procesar, es probable que alguien haya creado una aplicación de Excel para ello. Aquí hay una selección. , primero debes cargarlo. Afortunadamente, ya está en tu computadora.

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

Si este menú desplegable dice algo diferente a “Complementos de Excel,” necesitarás cambiarlo:

En la ventana resultante, verá algunas opciones. Asegúrate de que la casilla al lado de Complemento Solver está marcado y presiona Okay.

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

Si ya ha estado utilizando el Paquete de herramientas de análisis de datos Cómo hacer análisis de datos básicos en Excel Cómo hacer análisis de datos básicos en Excel Excel no está destinado al análisis de datos, pero aún puede manejar estadísticas. Le mostraremos cómo usar el complemento Data Analysis Toolpak para ejecutar estadísticas de Excel. , verá el botón Análisis de datos. Si no, Solver aparecerá solo.

Ahora que ha 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> Solucionador. Verá la ventana 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 Solver).
   
2. Establezca el objetivo de su celda y dígale a Excel su objetivo. El objetivo está en la parte superior de la ventana 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á 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 lo busque.
3. Elija las celdas variables que Excel puede cambiar. Las celdas variables se establecen con el Al cambiar celdas 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 desea que cambie una celda, no la seleccione.
   
4. Establecer restricciones en variables múltiples o individuales. Finalmente, llegamos a las limitaciones. 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 detalles, consulte la sección sobre cómo establecer restricciones a continuación.
5. Una vez que toda esta información esté en su lugar, presione Resolver para obtener tu respuesta Excel actualizará sus datos para incluir las nuevas variables (es por eso que le recomendamos que cree primero 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

Puede decirle a Excel que una variable tiene que ser mayor de 200. Al probar diferentes valores de variable, Excel no irá por debajo de 201 con esa variable en particular.

Para agregar una restricción, haga clic en el Añadir botón al lado de la lista de restricciones. Tendrás una nueva ventana. Seleccione la celda (o celdas) a restringir en Referencia de celda campo, luego elija 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 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. Entonces, si tiene tres celdas, terminará 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 seleccione múltiples celdas para referencia de celda. Si desea 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ó restricciones no sean negativos. Si desea que sus variables se vuelvan negativas, desactive esta casilla.

Un ejemplo de solucionador

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

En él, tenemos cinco trabajos diferentes, cada uno de los cuales paga una tarifa diferente. También tenemos la cantidad 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 manteniendo ciertas variables dentro de algunas restricciones.

Aquí están las restricciones que usaremos:

• No trabajos puede caer por debajo de cuatro horas.
• El trabajo 2 debe ser mayor de ocho horas.
• El 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 configuraríamos en Solver:

Primero, tenga en cuenta que He creado una copia de la tabla así que no sobrescribimos el original, que contiene nuestras horas de trabajo actuales.

Y segundo, observe que los valores en las restricciones mayor que menor son uno más alto o más bajo de lo que mencioné anteriormente. Eso es porque no hay opciones de mayor o menor 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 de arriba, nuestras ganancias han aumentado en $ 130. Y todas las limitaciones se han cumplido.

Para mantener los nuevos valores, asegúrese de Mantenga la solución de solucionador está marcado y golpeado Okay.

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

Los informes se generan en nuevas hojas en su libro de trabajo y le brindan información sobre el proceso que realizó el complemento Solver 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 de informes útil en estas nuevas hojas de trabajo. Solo haz clic en + botón al costado de cualquier informe para obtener más información:

Opciones avanzadas de Solver

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

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

Puede elegir entre GRG no lineal, LP simple y evolutivo. Excel proporciona una explicación simple sobre cuándo debe usar cada uno. Una mejor explicación requiere un poco de 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 electorales y sus asignaciones de clase de estadísticas en muy poco tiempo. y regresión.

Para ajustar configuraciones adicionales, simplemente presione el 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 los métodos de resolución GRG y Evolutiva hacen sus cálculos.

Una vez más, si no sabe lo que esto significa, no se preocupe. Si desea saber más acerca de qué método de resolución usar, Engineer Excel tiene un buen artículo que lo presenta por usted. Si desea la máxima precisión, Evolutionary es probablemente un buen camino a seguir. Solo tenga en cuenta que tomará mucho tiempo.

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

Ahora que se siente cómodo con los conceptos básicos para resolver variables desconocidas en Excel, tiene abierto 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. .

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

¿Utiliza Goal Seek o Solver en sus 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!