lunes, 29 de septiembre de 2008

Solver y Buscar Objetivo

Descargar el fichero: doblar.xlsx

En Excel disponemos de dos magníficas herramientas para resolver ecuaciones sin necesidad de despejar la variable que deseamos obtener. Se trata de Solver y de Buscar Objetivo, aunque son mucho más que eso. En especial, Solver es una estupenda herramienta de optimización (cálculo de máximos y mínimos).


Buscar Objetivo
Utilicemos la ley de la capitalización compuesta.
Vamos a contestar a la pregunta siguiente:
¿En cuanto tiempo se doblara un capital en compuesta trabajando al 5%?
Para ello podemos utilizar la función financiera de Excel:
=NPER(tasapagova; vf; tipo)
=NPER(5%;;-1000;2000)
Que arroja un resultado de 14,20669908 años.
Pero en este caso vamos a resolverlo con Buscar Objetivo.
 
Pasos:
  1. Poner unos datos arbitrarios en las celdas C6:C8
  2. Calculamos en C9 el capital final aplicando la ley de la compuesta
  3. En C8 se pone el tipo de interés del 5% que es un dato del enunciado
  4. Se lanza ‘Buscar Objetivo’ que esta en el menú Herramientas (en Excel 2007 esta en Datos, ‘Análisis Y si’)
  5. En ‘Definir la celda’ siempre se debe poner la celda que lleva la fórmula, en este caso la C9
  6. En ‘Con el valor’ se debe poner a mano (no deja pinchar una celda) el valor al que se quiere llegar
  7. En ‘Para cambiar la celda’ se debe poner la celda de la variable que queremos despejar (calcular), en nuestro caso C7.
Solver

Vamos ahora a resolver la siguiente cuestión:
Calcular a qué tipo se ha de trabajar en compuesta para doblar el capital en 10 años.
Se puede responder facilmente utilizando una función de Excel que calcula el tipo de interés de este tipo de operaciones. La función es:
=TASA(nper;pago;va;vf;tipo;estimar)
En nuestro caso:
=TASA(10;;-1000;2000)
que  da un resultado de: 7,17735% anual.
Podemos resolverlo con Solver al igual que lo haríamos con ‘Buscar Objetivo’. Aunque Solver es mucho mejor que ‘Buscar Objetivo’, no en vano es una potente herramienta de optimización.
Nosotros no la vamos a utilizar para calcular máximos y mínimos, sino para obtener valores a los que puede llegar una celda objetivo.
Solver sólo estará disponible si se activa el Complemento que permite su utilización. En Excel 2003 se activa en: Herramientas, Complementos, Solver. En Excel 2007 se activa pulsando el botón del Office (ese botón redonde que tienes arriba a la izquierda, le denominan The Ribbon), luego elige ‘Opciones de Excel’, y a la izquierda veras ‘Complementos’, selecciona Solver y activalo. Cuando actives Solver, de paso marca también ‘Herramientas para Análisis’ que permite disponer de muchas más funciones en Excel.
Una vez activado el complemento para usarlo en una hoja, en Excel 2003 lo tiene en Herramientas, y en Excel 2007 esta en Datos.




Pasos:
  1. Poner unos datos arbitrarios en las celdas C16:C18
  2. Calculamos en C19 el capital final aplicando la ley de la compuesta
  3. En C17 se ponen los años que según el enunciado son 10
  4. Se lanza ‘Solver’ que esta en el menú Herramientas (en Excel 2007 esta en Datos).
  5. En ‘Celda Objetivo’ siempre se debe poner la celda que lleva la fórmula, en este caso la C19
  6. Valor de la Celda Objetivo, marcar la casilla que pone ‘Valor de’ (no marcar ni Máximo, ni Mínimo) y poner el valor 2000
  7. En ‘Cambiando las celdas’ se debe poner la celda de la variable que queremos despejar (calcular), en nuestro caso C18.



La versión de Office 2010 Starter es una versión básica que ahora viene instalada en muchos ordenadores nuevos pero que no tiene muchas de las opciones de menú, ni herramientas que necesitamos.

Esta es una imagen de la versión Starter donde puedes ver los recortados menús de que disfruta.




Activar las Macros para que funcione Solver

Solver es un complemento de Excel, que está programado en lenguaje de macros. Las macros las usaremos más adelante para hacer algunas formulas personalizadas. Eso ya os lo contaré más adelante, cuando veamos rentas. De momento tienes que saber que una Macro es un programa y que como tal hay gente que en su día programaron virus en lenguaje de macros. Por este motivo Microsoft estableció varios niveles de seguridad,
para indicarle a Excel si quieres que al abrir un fichero que lleve macros las habilite o no.

Si tienes Excel 2003 o versiones anteriores para habilitar las macros debes hacer lo siguiente:
Herramientas, Macros, Seguridad, Nivel bajo o Nivel Medio.

Lo recomendable suele ser poner nivel medio de seguridad, de esa forma cuando abras un fichero de Excel que lleva macros te preguntará que si quieres habilitarlas. Si te fías de la fuente o has pasado un antivirus le dices que SI quieres habilitar las macros, y si no te fías no las habilitas, pero entonces no son operativas.

Como todos tenemos antivirus más o menos actualizados y efectivos, puedes poner el nivel bajo de seguridad, así no te estarán preguntando cada vez que abras un fichero que lleve macros.

Para Excel 2007, la cosa se complica. Primero debemos obtener la pestaña PROGRAMADOR que es una pestaña que de entrada no aparece. Las que aparecen con la instalación son: Inicio, Insertar, Diseño de página, Fórmulas, Datos, Revisar, Vista.

Para obtener la pestaña PROGRAMADOR (en Excel 2007) debes ir al botón redondo de la esquina superior izquierda (se llama 'el botón de Office' porque es común a todos los programas de Microsoft Office: Word, PowePoint,..). Pulsando en ese botón redondo, verás abajo que pone 'Opciones de Excel'. En la ventana 'Mas frecuentes' verás una opción que esta desmarcada y pone: "Mostrar ficha Programador en la cinta de opciones". Marca esa opción y pulsa Aceptar. Así conseguirás tener la pestañita (ficha) PROGRAMADOR.

Se supone que ya tenemos la ficha PROGRAMADOR. Pulsa en ella, y luego en 'Seguridad de Macros', y elige 'Habilitar todas las macros'.

De esta forma tendremos habilitadas las macros. Para que esto sea efectivo debemos salirnos del fichero y volver a entrar.

Y para todos, si es importante aprender a conseguir la ficha PROGRAMADOR (si tienes Excel 2007), y conseguir poner un nivel bajo de seguridad o nivel medio.



Buscar Objetivo

Puedes usar la función TASA que esta disponible en todos los ordenadores con Excel,
sean de la versión que sean. O puedes usar 'Buscar Objetivo' que aunque menos preciso
que Solver, para este ejemplo es más que suficiente. Y también esta disponible en todos
los ordenadores. No es necesario instalar nada adicional.





Para Excel 2003 y anteriores


¿Dónde esta Buscar Objetivo?


Esta en el menú Herramientas.

¿Dónde esta SOLVER?


Al ser Solver un complemento, primero se ha de instalar. Para instalarlo haz: Herramientas, Complemento, y marca Solver, y ya que estamos marca 'Herramientas para Análisis'. Al aceptar veras que el ordenador carga el complemento. Si en su día realizaste una instalación completa, tarda en cargar los complementos un segundo. Si en su día no realizaste una instalación completa de Excel te pedirá que introduzcas el disco con el programa Office que instalaste en su día.

Comprobemos ahora que ya tengo Solver disponible. Lo encontrarás en el menú Herramientas, Solver. Pruebalo con un ejemplo. Verás que es mucho mejor que Buscar Objetivo. Es importante conseguirlo ya que en la práctica 2 y sucesivas lo usaremos de forma intensa.

'Herramientas para Análisis' lo que hace es aumentar muchísimo las funciones disponibles. Esto nos resultará necesario para las próximas prácticas, ya que utilizaremos algunas de estas funciones complementarias.

Vídeo: Solver y Buscar objetivo



Vídeo: Buscar objetivo y Solver



Caso práctico: Equilibrar una operación financiera

Veamos un caso práctico de tipo financiero. Vamos a calcular la cuantía necesaria para cuadrar una operación financiera. La calcularemos por tres métodos siendo uno de ellos Solver y otro Buscar objetivo.




Caso práctico: Efectivo de una Letra del Tesoro

28 comentarios:

  1. Hola Sara.

    La versión Starter de Excel 2010 es una versión gratuita que viene ahora con los nuevos ordenadores que se compran. Es poco más que un visualizador y está muy "capada".

    Si usas Excel de forma profesional no te servirá.

    Un saludo.

    ResponderEliminar
  2. Una duda, el solver por lo que leo, solo puede tener una celda objetivo...y no puede tener más de una celda objetivo?? estaba leyendo la ayuda de Excel y menciona que se puede guardar el escenario, pero no se si se podría explicar un poco más sobre ello, de antemano, muchas gracias.

    ResponderEliminar
  3. Efectivamente, Solver puede afrontar varios objetivos de forma simultánea.

    De echo, pone "Cambiando las celdas de variables:" (en plural), en la versión Excel 2010.

    Puedes ver un ejemplo, con vídeo del uso de Solver para resolver varias celdas de forma simultánea:

    Valoración de Bonos con Solver

    ResponderEliminar
  4. Hola,

    Mi problema es el siguiente. Necesito una función que busque el valor mínimo de una celda X cambiando el valor de una celda Y. Los valores de la celda Y son finitos (digamos unos 30 enteros por ejemplo 3100 o 6003). Al variar los valores de la celda Y, la mayoría devolverán el mismo valor a la celda X aunque solo uno devolverá el mínimo. ¿Cómo lo puedo hacer?

    ResponderEliminar
  5. Hola Xexi.

    Si no son muchos valores, por ejemplo unos 30 como comentas, lo mejor es usar Tabla de Datos.

    En la versión de Excel 2007 y siguientes está en Datos, Análisis y si, Tabla de datos.

    Esto te permite hacer un análisis de sensibilidad mostrando una tabla con todos los resultados de la variable que desea analizar. Finalmente solo te queda buscar el mínimo valor que deseas localizar.

    Un cordial saludo.

    ResponderEliminar
  6. hola tengo una pregunta con respecto al manejo de las restricciones
    tengo una restricción del tipo
    "X diferente de Cero" y no se de que manera agregarla
    Espero que me puedan ayudar gracias

    ResponderEliminar
  7. Hola Juan Manuel.

    Mira a ver si te sirve marcar justo debajo de las restricciones donde pone:
    "Convertir variables sin restricciones en no negativas".
    Eso lo puedes ver en Excel versión 2010.

    ResponderEliminar
  8. Hola,

    Me gustaría solucionar un problema con Solver pero se me escapa algo.
    Seleccionar los efectos a descontar para conseguir el efectivo buscado al mínimo coste.
    Efecto Nominal Vencimiento Días Intereses Variable Comisiones Correo Total gastos EFECTIVO
    Totales 22.700,00 194,97 6,00 68,10 1,20 270,27 22.429,73
    1 3.000,00 25/02/2013 24 12,00 1,00 9,00 0,20 22,20 2.977,80
    2 5.000,00 05/03/2013 32 26,67 1,00 15,00 0,20 42,87 4.957,13
    3 3.800,00 10/03/2013 37 23,43 1,00 11,40 0,20 36,03 3.763,97
    4 4.600,00 25/03/2013 52 39,87 1,00 13,80 0,20 54,87 4.545,13
    5 4.000,00 24/04/2013 82 54,67 1,00 12,00 0,20 67,87 3.932,13
    6 2.300,00 12/05/2013 100 38,33 1,00 6,90 0,20 46,43 2.253,57

    Si me pudieran ayudar,
    Muchas gracias.

    ResponderEliminar
  9. Muy buenas, estoy trabajando en un problema de programación multiobjetivo con multitud de variables (aproximadamente unas 50) en las que me aparecen funciones no lineales.

    Me desearía saber algo más sobre el uso de la función de Solver mediante el método de resolución GRG2.

    Le estaría muy agradecido si pudiera facilitarme algún enlace con información sobre la potencialidad de la aplicación en cuestión y sobre su uso.

    Muchísimas gracias por su atención.

    Francesc Jurado

    ResponderEliminar
  10. Muy buenas de nuevo, tengo ahora una duda específica en torno a la aplicación Solver para cálculos con ecuaciones no lineales.

    ¿Se pueden incluir restricciones no lineales en la programación con Solver?

    Otra duda que me surge de forma general en torno a Solver es si tanto los valores de las variables como los de las restricciones deben ser valores fijos o pueden componerse de funciones

    Muchas gracias

    Francesc

    ResponderEliminar
  11. Hola, cual es la diferencia entre usar SOLVER o una Macro de Excel para resolver un problema?

    ResponderEliminar
  12. Hola, te hago una consulta. Intento crear una Macro para asignarla a un boton y que cada ves que toque el mismo corra el Solver (Son siempre las mismas celdas pero cambian los datos).
    El problema es que cuando corro la macro me da el siguiente error: "Error de compilación: No se ha definido Sub o Function" Y me marca la parte de la programación que dice SolverAceptar. Te copio la programación de la Macro a ver si me podes ayudar:

    Sub SolverMacro()
    '
    ' Solver Macro
    '

    '
    SolverAceptar definirCelda:="$J$14", valorMáxMín:=2, valorDe:="0", _
    celdasCambiantes:="$J$16"
    SolverEliminar referenciaCelda:="$J$14", relación:=2, Formula:="$J$15"
    SolverAceptar definirCelda:="$J$14", valorMáxMín:=2, valorDe:="0", _
    celdasCambiantes:="$J$16"
    SolverEliminar referenciaCelda:="$J$16", relación:=3, Formula:="0,5"
    SolverAceptar definirCelda:="$J$14", valorMáxMín:=2, valorDe:="0", _
    celdasCambiantes:="$J$16"
    SolverAgregar referenciaCelda:="$J$14", relación:=2, Formula:="$J$15"
    SolverAceptar definirCelda:="$J$14", valorMáxMín:=2, valorDe:="0", _
    celdasCambiantes:="$J$16"
    SolverAgregar referenciaCelda:="$J$16", relación:=3, Formula:="0,5"
    SolverAceptar definirCelda:="$J$14", valorMáxMín:=2, valorDe:="0", _
    celdasCambiantes:="$J$16"
    SolverResolver
    End Sub

    ResponderEliminar
  13. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  14. Hola Adolfo,

    Estoy intentando hacer una optimización de valores utilizando el solver. Le estoy metiendo restricciones como que los valores tienen que ser >0 y <1 que todos tienen que sumar 1 y mi objetivo es maximizar la rentabilidad con una volatilidad maxima. mi pregunta es si hay otra utilidad a parte del solver para hacerlo o si a través de VBA el solver es mas exacto. Lo digo porque con todas las restricciones que tengo le meto, por ejemplo, 15 valores, y yo lo que quiero es que me de una cartera de máximo 5 valores, pero con todas las restricciones que le pongo no me deja bajar de 9 o 10.

    Gracias.

    ResponderEliminar
  15. Hola Alejandro.

    Solver en ocasiones no llega a la solución óptima. Yo he probado a meterle problemas de libro algo complejos y no ha llegado a la solución óptima.

    Existe una versión profesional de Solver, que no se si será mejor en este aspecto.

    Si estas usando temas "delicados" e importantes, prueba a usar algoritmos de optimización que tienes en diferentes lenguajes de programación, por ejemplo, en MatLab.

    Un saludo.

    ResponderEliminar
  16. Hola Adolfo, consulta, que función de Matlab me puede ayudar a optimizar un sistema de ecuaciones "multivariables" para optimizar parámetros especificando restricciones?
    Necesito dar con los parámetros para realizar mezclas de distintos gráneles con distintas calidades químicas y dar con una determinada especificación.

    Es algo similar a lo consultado por Alejandro

    Gracias, Miguel

    ResponderEliminar
    Respuestas
    1. Hola Miguel.
      No conozco con tanta profundida Matlab. Quizás lo mejor es localizar un foro específico de MatLab y optimización.
      Solver está muy bien pero no siempre optimiza correctamente, especialmente si le metemos muchas restricciones 'complejas'. Por ejemplo, con programación entera se hace bastante lio y no obtiene el optimo, o el que te da no es el correcto.
      Como digo, lo mejor es mirar en un foro matemático donde preguntar temas de optimización.
      Ánimo con tu proyecto que parece muy interesante.

      Eliminar
  17. Hola Adolfo Aparicio, consulta como puedo hacer un ciclo (A,B,C), cunado coloque en una celda ejemplo A1 "A" en A2 retorne ·"B" y cuando coloque "B" en A2 retorne ·"C" y cunado coloque "C" en A2 retorne ·"A"

    ResponderEliminar
  18. Hola Adolfo Aparicio, consulta como puedo hacer un ciclo (A,B,C), cunado coloque en una celda ejemplo A1 "A" en A2 retorne ·"B" y cuando coloque "B" en A2 retorne ·"C" y cunado coloque "C" en A2 retorne ·"A"

    ResponderEliminar
  19. buenas noches !!!
    Estoy interesado en saber como funcionar buscar objetivo pero desde el punto de vista matemático, o a través de lenguaje de programación.
    Espero me pueda ayudar, muchas gracias por su tiempo y amabilidad .

    ResponderEliminar


  20. Hola a usted, ¿necesita un préstamo urgente? CONTACTO ELITEFINANCE2000@GMAIL.COM

    ResponderEliminar
  21. Hola,

    Si yo tengo una ganancia que quiero obtener, cambiando el precio y cambiando las unidades vendidas quiero llegar a la misma ganancia. Es decir si vendo 10 unidades a 1 dolar y gano 5 dolares, si yo le bajo el precio a 0.90 cuantas unidades debo vender para ganar los mismos 5 dolares

    ResponderEliminar
  22. Respondiendo a Leo que tenía un problema con una referencia circular.
    Hola Leo.
    Puedes usar la herramienta de Excel "Buscar objetivo".
    Está en: Datos / Análisis de hipótesis / Buscar Objetivo.
    Te indico los parámetros que se han de usar.
    * Definir la celda: $A$12
    * Con el valor: 0
    * Cambiando la celda: A3
    ​Si luego quieres automatizar el proceso puedes hacer una pequeña macro con grabadora que lo haría mucho más rápido.

    Un cordial saludo.

    ResponderEliminar
    Respuestas
    1. Hola, gracias por la prontitud de su respuesta y la efectividad...Su propuesta permite eliminar completamente la referencia circular.

      Eliminar
  23. Hola Leo.
    Si lo que necesitas es automatizar el proceso lo que procede es crear una macro con grabadora que haga lo mismo que tu harías a mano con Buscar Objetivo.
    Para que aprendas a crear macros con grabadora te recomiendo revises el siguiente enlace.
    http://www.excelavanzado.com/2014/09/modulo-10-creacion-de-macros.html
    Un cordial saludo.

    ResponderEliminar
  24. hola buenas tardes

    estoy utilizando solver pero manejo mas de 5000 celdas quisiera saber si hay alguna opción en el que me puede sumar muchas celdas y me arroje el resultado deseado ya que el mensaje que me aparece es de (demasiadas celdas variables), gracias slaudos

    ResponderEliminar
    Respuestas
    1. Hola.

      Puedes intentar a manejar Solver desde una macro, es posible que esto te de más opciones para manejar los datos con mayor flexibilidad. Te dejo un caso, aunque aborda otro tema:

      Sensibilidad de un Forward con Solver

      Un cordial saludo.

      Eliminar