Excel es muy capaz cuando tiene todos los datos que necesita para sus cálculos.
Pero, ¿no sería bueno si pudiera resolver las variables desconocidas ?
Con Goal Seek y el complemento Solver, puede hacerlo. Y te mostraremos cómo. Siga leyendo para obtener una guía completa sobre cómo resolver una sola celda con la búsqueda de objetivos o una ecuación más complicada con Solver.
Cómo usar la búsqueda de objetivos en Excel
Goal Seek ya está integrado en Excel. Está en la pestaña Datos, en el menú Análisis de Qué-Si :
Para este ejemplo, utilizaremos un conjunto muy simple de números. Tenemos un valor de tres cuartas partes de las ventas y un objetivo anual. Podemos usar la búsqueda de objetivos para averiguar qué números deben estar en el cuarto trimestre para alcanzar el objetivo.
Como puede ver, el total de ventas actual es de 114, 706 unidades. Si queremos vender 250, 000 a fin de año, ¿cuántos tenemos que vender en el cuarto trimestre? La búsqueda de objetivos de Excel nos lo dirá.
A continuación, le mostramos cómo usar la Búsqueda de objetivos, paso a paso:
- Haga clic en Datos> Análisis de Qué sucede> Buscar objetivo . Verás esta ventana:
- Ponga la parte "igual" de su ecuación en el campo Establecer celda . Este es el número que Excel tratará de optimizar. En nuestro caso, es el total acumulado de nuestros números de ventas en la celda B5.
- Escriba su valor de objetivo en el campo de valor A. Estamos buscando un total de 250, 000 unidades vendidas, así que pondremos "250, 000" en este campo.
- Indique a Excel qué variable resolver en el campo Cambiar celda . 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:
- Haga clic en Aceptar para resolver su objetivo. Cuando se ve bien, solo presiona OK . Excel le informará cuando Goal Seek haya encontrado una solución.
- Haga clic en Aceptar de nuevo y verá el valor que resuelve su ecuación en la celda que eligió Al cambiar de celda .
En nuestro caso, la solución es 135, 294 unidades. Por supuesto, podríamos haberlo encontrado al restar el total acumulado del objetivo 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 ejecutar Goal Seek en una copia de sus datos . También es una buena idea anotar en sus datos copiados que se generó utilizando Goal Seek. No quiere confundirlo con datos actuales y precisos.
Así que Goal Seek es una función útil de Excel. 16 Fórmulas de Excel que te ayudarán a resolver problemas de la vida real. 16 Fórmulas de Excel que te ayudarán a resolver problemas de la vida real. La herramienta correcta 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 demostramos cómo usarlas. Lea 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 una versión multivariada de Goal Seek . Toma una variable de objetivo y ajusta una cantidad de otras variables hasta que obtiene la respuesta que desea.
Puede resolver por 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 forma excelente 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 pasemos al modo de usar Solver en Excel 2016.
Cómo cargar el complemento Solver
Excel no tiene solución por defecto. Es un complemento así que, al igual que otras poderosas características de Excel Power Up Excel con 10 complementos para procesar, analizar y visualizar datos como un Power Up de Excel con 10 complementos para procesar, analizar y visualizar datos como un Excel Pro Vanilla es increíble, 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 Excel para ello. Aquí hay una selección. Leer más, tienes que cargarlo primero. Afortunadamente, ya está en tu computadora.
Dirígete a Archivo> Opciones> Complementos . Luego haga clic en Ir al lado de Administrar: Complementos de Excel .
Si este menú desplegable dice algo diferente a "Complementos de Excel", deberá cambiarlo:
En la ventana resultante, verá algunas opciones. Asegúrese de que la casilla al lado de Solver Add-In esté marcada y presione OK .
Ahora verá el botón Solver en el grupo Análisis de la pestaña Datos :
Si ya ha estado usando la herramienta 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á diseñado 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 estadísticas de Excel. Lea más, verá el botón Análisis de datos. Si no, Solver aparecerá por sí mismo.
Ahora que ha cargado el complemento, echemos un vistazo a cómo usarlo.
Cómo usar Solver en Excel
Hay tres partes para cualquier acción de Solver: el objetivo, las celdas variables y las restricciones. Pasaremos por cada uno de los pasos.
- Haga clic en Datos> Solucionador . Verá la ventana Solver Parameters a continuación. (Si no ve el botón de solución, consulte la sección anterior sobre cómo cargar el complemento Solver).
- Establezca el objetivo de su celda y diga a Excel su 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 Máx., Excel ajustará sus variables para obtener el mayor número posible en su celda objetivo. Min es todo lo contrario: Solver minimizará el número objetivo. El valor de le permite especificar un número específico para que Solver lo busque. - Elija las celdas variables que Excel puede cambiar. Las celdas variables se establecen con el campo Al cambiar celdas variables . 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 estas son todas las celdas que pueden variar. Si no desea que una celda cambie, no la seleccione.
- Establezca 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 las restricciones que deben cumplirse. Para más detalles, consulte la sección sobre cómo establecer restricciones a continuación.
- Una vez que toda esta información esté en su lugar, presione Solver para obtener su respuesta. Excel actualizará sus datos para incluir las nuevas variables (esta es la razón por la que le recomendamos que cree primero una copia de sus datos).
También puede generar informes, que veremos brevemente en nuestro ejemplo 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 pasará a 201 con esa variable en particular.
Para agregar una restricción, haga clic en el botón Agregar al lado de la lista de restricciones. Obtendrás una nueva ventana. Seleccione la celda (o celdas) a restringir en el campo Referencia de celda, luego elija un operador.
Aquí están los operadores disponibles:
- <= (menor que o igual a)
- = (igual a)
- => (mayor que o igual a)
- int (debe ser un número entero)
- bin (debe ser 1 o 0)
- Todo diferente
AllDifferent es un poco confuso. Especifica que cada celda del 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 puede seleccionar varias celdas para Referencia de celda. 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 que 11. No es necesario agregar una restricción para cada celda.
También puede utilizar 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 quieres que tus variables sean negativas, desmarca esta casilla.
Un ejemplo de solucionador
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 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 la paga total mientras se mantienen ciertas variables dentro de algunas restricciones.
Estas son las restricciones que usaremos:
- Ningún trabajo puede caer menos de cuatro horas.
- El trabajo 2 debe ser mayor a 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 configuramos en Solver:
Primero, tenga en cuenta que he creado una copia de la tabla para que no sobrescribamos la original, que contiene nuestras horas de trabajo actuales.
Y segundo, vea que los valores en las restricciones mayor que y menor que son uno más alto o más bajo que lo que mencioné anteriormente. Eso es porque no hay opciones más grandes que o menos. Solo hay mayor que o igual a y menor que o igual a.
Golpeemos Solve y veamos qué pasa.
¡Solver encontró una solución! Como puede ver a la izquierda de la ventana de arriba, nuestras ganancias aumentaron en $ 130. Y todas las restricciones se han cumplido.
Para mantener los nuevos valores, asegúrese de que esté activada la opción Mantener Solver Solution y presione OK .
Sin embargo, si desea obtener más información, puede seleccionar un informe en el lado derecho de la ventana. Seleccione todos los informes que desee, indique a Excel si desea que se los describa (lo recomiendo) y presione OK .
Los informes se generan en hojas nuevas en su libro de trabajo y le brindan información sobre el proceso por el que pasó 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. Simplemente haga clic en el 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 ejecutarlas tal como están. Pero si está ejecutando cálculos grandes y complejos, es posible que desee examinarlos.
El más obvio es el método de solució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 poco de conocimiento de las estadísticas. Aprenda estadísticas de forma gratuita con estos 6 recursos. Aprenda estadísticas de forma gratuita con estos 6 recursos. Las estadísticas tienen la reputación de ser un tema difícil de entender. Pero aprender del recurso correcto lo ayudará a comprender los resultados de la encuesta, los informes electorales y las asignaciones de las clases de estadísticas en muy poco tiempo. Leer más y regresión.
Para ajustar la configuración adicional, solo presiona el botón Opciones . Puede decirle a Excel sobre la optimalidad de enteros, establecer restricciones de tiempo de cálculo (útiles para conjuntos de datos masivos) y ajustar cómo los métodos de resolución de GRG y Evolutivo hacen sus cálculos.
Nuevamente, si no sabe qué significa esto, no se preocupe. Si desea saber más sobre qué método de solución utilizar, Engineer Excel tiene un buen artículo que lo establece para usted. Si quieres la máxima precisión, Evolutionary es probablemente una buena forma de hacerlo. Solo ten 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 aspectos básicos de la resolución de variables desconocidas en Excel, un mundo completamente nuevo de cálculo de hoja de cálculo está abierto para usted.
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 capacidades 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 los porcentajes, los promedios, la desviación estándar, el error estándar y las pruebas T del alumno. Lee mas .
Es solo 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!