Mostrando entradas con la etiqueta Solver en Excel. Mostrar todas las entradas
Mostrando entradas con la etiqueta Solver en Excel. Mostrar todas las entradas

jueves, 23 de enero de 2014

Creando una Macro para Excel que ejecute Solver u OpenSolver



En esta entrada, crearemos una Macro en Microsoft Excel 2013® que nos permita resolver un Problema de Programación Lineal (o PPL), facilitando tanto la definición como el ingreso de los parámetros y variables que utilizaremos para, posteriormente, ejecutar complementos como Solver u OpenSolver para buscar una solución.

 

1.- Pasos Previos:

Comenzaremos por preparar nuestro Excel, corroborando que tengamos todo lo necesario para llevar a cabo nuestra tarea. En particular:

  • Primero, debemos abrir un libro de Excel y habilitar el complemento "Solver" dirigiéndonos a:
    Archivo → Opciones → Complementos → Click en el botón "Ir" → Marcar "Solver" y click en "Aceptar".


  • Segundo, descargamos OpenSolver y descomprimimos los archivos. Posteriormente nos dirigimos a la siguiente ruta:
    Archivo → Opciones → Complementos → Click en el botón "Ir" → Click en "Examinar" → Y arrastramos los archivos comprimidos a esa ventana y, una vez realizado, seleccionamos el archivo "OpenSolver" y ponemos "Aceptar". Luego volvemos a la ventana anterior, activamos OpenSolver y clickeamos en "Aceptar".


     
  • Tercero, mostraremos la pestaña Desarrollador moviéndonos a:
    Archivo → Opciones → Personalizar cinta de opciones → Marcar "Desarrollador" y click en "Aceptar"
  • Cuarto, guardaremos nuestro documento como un "Libro de Excel habilitado para macros" en:
    Archivo → Guardar Como → Escogemos el nombre, directorio y en Tipo seleccionaremos "Libro de Excel habilitado para macros" y damos click en "Aceptar"

2.- Problema de Programación Lineal:

Realizado lo anterior, debemos contar con un PPL. A modo de ejemplo, utilizaremos el problema:

"Usted quiere abastecerse de cervezas para el verano y cuenta con un presupuesto de $75.000. Para lo anterior, ha cotizado en una distribuidora mayorista los siguientes tipos de cervezas por botella: Cerveza Negra $800 c/u, Cerveza Rubia $700 c/u y Cerveza Artesanal $1200 c/u. Cabe recalcar que cada tipo de cerveza se vende por botellas ( o unidades enteras).

Asimismo, el vendedor le informa que, para acceder a los precios anteriores, usted debe comprar al menos 12 unidades de Cerveza Negra, 24 de Cerveza Rubia y 6 de Cerveza Artesanal."
Un ejemplo para el problema planteado puede ser el siguiente:


En donde:
  • Las Celdas C3 a C5 contienen las celdas variables de nuestro problema (destacadas en color azul).
  • La Celda A13 contiene la función objetivo (destacada en amarillo), que contiene la sumatoria de las multiplicaciones de cada precio unitario por las unidades respectivas. 
  • Las Celdas C9 a C11 contienen las restricciones de unidades mínimas a consumir.
  • Las Celdas B15 a B17 nos recuerdan que debemos imponer que las celdas variables sean enteros.

 

3.- Trabajando con Nuestra Macro:

Una vez definido nuestro PPL a resolver, podemos comenzar a programar nuestra Macro. En primera instancia, supondremos que deseamos que el usuario final no tenga la posibilidad de editar los parámetros y variables de nuestro problema, por lo que el usuario utilizará la Macro para obtener solamente el resultado. 

Comenzaremos por crear nuestra Macro clickeando en el botón "Macro" dentro de la pestaña Desarrollador. En la ventana emergente, indicamos que llamaremos a nuestra Macro "ResolverOculto" (sin comillas) y apretamos el botón "Crear" para, posteriormente, clickear en "Modificar". Luego, se nos abrirá la ventana de "Microsoft Visual Basic para Aplicaciones", en donde podremos programar nuestra Macro.

En ésta última ventana, deberemos activar las referencias a Solver y OpenSolver (si es que usaremos los dos) dirigiéndonos hacia:
Herramientas → Referencias → Marcamos "Solver" y "OpenSolver" y damos click en "Aceptar".


 
A continuación, debemos programar la macro con las instrucciones necesarias para que se ejecute el Solver o el OpenSolver correctamente. Se muestra una imagen a continuación que posee comentarios con la explicación de los comandos e instrucciones utilizadas para construir nuestra Macro:




Nota 1: Para correr el OpenSolver se debe quitar el apostrofe ( ' ) que se encuentra antes de la expresión "RunOpenSolver=True".

Nota 2: Cabe mencionar que para controlar Errores se incluyeron la expresiones: "On Error GoTo Tratar_Errores" (al inicio de la Macro), "Exit Sub", "Tratar_Errores:" y un Mensaje que señala que ha ocurrido un error y el programa ha finalizado (al final de la Macro).


Otro situación a la que nos podemos enfrentar consiste en que queramos que el usuario ingrese los parámetros y restricciones para nuestro modelo. Para lograr lo anterior, creamos una nueva Macro llamada ResolverInteractivo (siguiendo el mismo procedimiento empleado para crear ResolverOculto). A continuación, explicaremos por partes el código programado:

En la imagen anterior se aprecia lo siguiente:
  1. Primero, se genera la expresión para el control de errores. 
  2. Segundo, se definen las variables "temp" que utilizaremos. 
  3. Tercero, se restablecen los parámetros del Solver mediante el comando SolverReset. 
  4. Cuarto, se solicita que el individuo indique si quiere Maximizar (1), Minimizar (2) o Alcanzar un Valor Objetivo (3) y se controlan errores de ingreso mediante un comando "Do While". 
  5. Quinto, se solicita que el usuario seleccione la celda objetivo.
  6. Sexto, se requiere que la persona ingrese las celdas variables.
  7. Séptimo, se generan distintos escenarios según el problema que va a resolver el individuo. Lo anterior se hace con el fin de definir el modelo de manera correcta. Cabe señalar que se utiliza la función SolverAceptar señalando la Función Objetivo (definirCelda), el tipo de problema a resolver (valorMáxMín), el valor deseado (valorDe, que es "0" para los problemas 1 y 2) y las celdas variables (celdasCambiantes).  



En la segunda imagen, notamos que:
  1. Primero, se pregunta el número de restricciones a ingresar. 
  2. Segundo, se definen las variables "aux" que utilizaremos. 
  3. Tercero, se crea un ciclo para ingresar el número de restricciones, pidiendo que ingrese el lado izquierdo restricción. Luego, se solicita que señale la relación ingresando un 1 para menor o igual, 2 para igual, 3 para mayor o igual, 4 para enteros y 5 para binarios. 
  4. Cuarto, se generan distintos escenarios según el tipo de relación, separando los casos 4 y 5 del resto. La idea es utilizar la función SolverAgregar que requiere el lado izquierdo de la restricción (referenciaCelda), la relación (relación) y el lado derecho de la restricción (Formula, que no corre para los casos 4 y 5).
  5. Quinto, se restablecen las variables "aux" y se va a la siguiente iteración.




En la tercera imagen, se aprecia que:
  1. Primero, se vuelven a ingresar los parámetros con el SolverAceptar. Lo anterior es requerido por el Solver para poder operar.
  2. Segundo, se definen las opciones adicionales del Solver (las dejamos fijas para el caso analizado).
  3. Tercero, se ejecuta el Solver sin mostrar el cuadro de diálogo al finalizar. Además, se señala que mantendremos el resultado final encontrado por la aplicación. 
  4. Finalmente, aparecen las líneas relacionadas con el tratamiento de errores.
Con todo lo anterior, cualquiera de las dos macros mostradas en la presente entrada debiese arrojar un resultado como el siguiente:
Finalmente, es importante volver a recalcar que debemos tener habilitados los complementos (Solver y OpenSolver). Además, dichos complementos deben ser referenciados en nuestra macro tal y como se mencionó anteriormente.
En el siguiente Link, podrán descargar el Excel utilizado en la siguiente entrada (con sus respectivas Macros incorporadas). Adicionalmente, se entregan los archivos de texto de las Macros programadas:
ResuelveOculto (Texto)
ResuelveInteractivo (Texto)


Referencias:
  • Microsoft (Solver en Macros): http://support.microsoft.com/kb/843304/es
  • Microsoft (Funciones VBA de Solver): http://msdn.microsoft.com/en-us/library/office/ff196600.aspx
  • Microsoft: (Función SolverReset): http://msdn.microsoft.com/en-us/library/office/ff821349.aspx
  • Excel-Easy: http://www.excel-easy.com/vba/range-object.html
  • Microsoft (Inputbox): http://msdn.microsoft.com/en-us/library/office/ff839468.aspx
  • ExcelTip (Referenciar): http://www.exceltip.com/custom-functions/how-to-use-your-excel-add-in-functions-in-vba.html
  • OpenSolver: http://opensolver.org/installing-opensolver/

Entrada publicada el 22 de enero de 2013 por Pierre Mariani R.
Leer más...

domingo, 17 de junio de 2012

Macros recursivas con uso de Solver

Macros recursivas con uso de Solver

Solver es parte de una serie de comandos a veces denominados herramientas de análisis. Con Solver, podemos buscar el valor óptimo para una fórmula determinada, la cual puede ser secuencia de valores, referencias de celda,  funciones u operadores de una celda que producen juntos un valor nuevo.
Una formula comienza siempre con el signo igual (=), denominada celda objetivo, en una hoja de cálculo. Solver funciona en un grupo de celdas que estén relacionadas, directa o indirectamente, con la fórmula de la celda objetivo.
Solver ajusta los valores en las celdas cambiantes que se especifiquen, denominadas celdas ajustables, para generar el resultado especificado en la fórmula de la celda objetivo. También pueden aplicarse restricciones, o sea,  limitaciones al problema, se puede aplicar restricciones a celdas ajustables, la celda de destino u otras celdas que estén directa o indirectamente relacionadas con la celda de destino.
Con Solver, podemos  dar solución a  variados problemas de optimización, entre ellos por ejemplo la  maximización de la utilidad.

Ahora un ejemplo para utilizar Solver y encontrar el Optimo de Pareto

¿Qué es un Óptimo de Pareto? Profundizando los análisis de las Curvas de Indiferencia y de la Caja de Edgeworth, el economista, político y sociólogo italianoVilfredo Pareto (1848-1923) desarrolló el concepto de Óptimo para aquella situación en la cual se cumple que no es posible beneficiar a una persona sin perjudicar a otra. En la búsqueda de un equilibrio con mejor bienestar, ambos agentes aceptan el intercambio hasta el punto en que este deja de generar beneficios.

Una forma de encontrar el óptimo de Pareto, es utilizando Microsoft Excel, utilizando la herramienta SOLVER con la ayuda del VBA, específicamente con el uso de Macros Recursivas.

Macros Recursivas
Recursividad, es un concepto fundamental en computación, es una alternativa diferente para implementar estructuras de repetición (ciclos). La recursividad se puede utilizar en toda situación en la cual la solución puede ser expresada como una secuencia de movimientos o pasos con reglas no ambiguas.
Se dice que una función (Function) es recursiva o que un procedimiento Sub es recursivo si se llama a sí mismos. Un ejemplo sencillo de cómo calcular el factorial de un número es el siguiente:

Function Factorial (N As Integer) As Long
                If N = 0 Then
                               Factorial = 1  ‘esto debido  a que 0!=1
                Else
                               Factorial = N * Factorial(N-1) ‘se llama así misma, pero en un número menor
                End if
End Function

Encontrando el optimo de Pareto:
Caso: Tenemos dos agentes en la economía, ambos tienen la misma función de utilidad que depende de solo dos bienes U(x1,x2) = x1*x2.  Inicialmente los consumos corresponden
Agente A (X1,X2)=(1,2) y el Agente B (X1,X2)=(2,1). Se sabe además que los precios relativos son igual a 1.

Utilizando Solver podemos encontrar el óptimo de consumo de estos agentes, o sea, el óptimo de Pareto. Para encontrar el óptimo se debe cumplir la siguiente condición, que es parte de la celda objetivo de Solver.


La macro utilizada para calcular lo anterior es la siguiente:
Sub MacroSolverr()

    SolverAceptar definirCelda:="$B$12", valorMáxMín:=3, valorDe:="0", _
        celdasCambiantes:="$B$4,$B$5,$B$8,$B$9"
    SolverAgregar referenciaCelda:="$F$5", relación:=2, Formula:="3"
    SolverAgregar referenciaCelda:="$F$6", relación:=2, Formula:="3"
    SolverAgregar referenciaCelda:="$E$9", relación:=2, Formula:="$E$8"
    SolverAgregar referenciaCelda:="$E$10", relación:=2, Formula:="$E$8"
    SolverAceptar definirCelda:="$B$12", valorMáxMín:=3, valorDe:="0", _
        celdasCambiantes:="$B$4,$B$5,$B$8,$B$9"
    SolverResolver
End Sub

Para utilizar esta macro, y otras macros que utilizan la herramienta Solver es necesario tener activado las funciones de Solver, para ello deben ir al Visual Basic en parte superior donde dice Herramientas->Referencias y activar la casilla SOLVER.


 Para graficar el óptimo de Pareto, y muchos otros tipos de gráficos, existe el complemento QIMacros para Excel, donde fácilmente puedes obtener el grafico que necesitas. Este complemento también debe ser activado para que pueda ser utilizado una vez se ha descargado. Para activar sus funciones se realiza el mismo procedimiento que para Solver:
 Esta herramienta utilizando macros permite graficar con solo seleccionar los datos correspondientes.
 Volviendo al ejemplo, si seleccionamos los datos estimados y utilizamos QImacros, específicamente, donde dice “Pareto” obtenemos el grafico siguiente, que muestra el optimo de nuestra función optimizada con Solver.
Al utilizar las Macros en Excel podemos apreciar muchas de sus ventajas, algunas de ellas son:
Realizar programas a medida
Rápido y fácil manejo 
Realización de tareas y cálculos complejos,
Aumento de eficacia y eficiencia 


Leer más...

miércoles, 25 de abril de 2012

Cómo Utilizar Solver en Excel


¿Cómo Utilizar Solver en Excel?


Solver es una “herramienta de análisis de hipótesis que busca el valor óptimo de una celda objetivo cambiando los valores de las celdas usadas para calcular la celda objetivo”. (Microsoft Excel 2010). En otras palabras, Solver es útil para resolver problemas matemáticos, sujetos a restricciones, los cuales pueden ser expresados en Excel.

 

¿Cómo habilitar Solver en Excel 2010?

Ir a “Archivo” y seleccionar “Opciones de Excel” (en algunos casos aparece sólo “Opciones”), donde se desplegará una ventana, en la cual deben hacer click en Complementos y luego ir a Complementos de Excel como se muestra en la figura. 

Luego, aparecerá una nueva ventana donde deberán seleccionar Solver.
Para poder utilizar la herramienta, deben ir a datos y al final de la fila en “Análisis” encontrarán Solver.

¿Cómo usar Solver?

Antes de poder utilizar la herramienta, se debe tener claro cuál es el problema que se quiere resolver, cuáles son las restricciones y cómo se debe expresar en Excel. Además, de poder distinguir entre los parámetros del problema (valores fijos) y las variables (que cambian según los parámetros, y sus valores influyen en el valor final de la función objetivo).
Por lo tanto, para poder simplificar la explicación de cómo usar Solver, utilizaremos un ejemplo de una empresa que vende dos productos y que enfrenta el siguiente problema de Maximización de Utilidad:

Donde X1 y X2 son la cantidad de cada productos.
El cual se puede expresar de la siguiente manera en Excel:

Dado que la incógnita que debe resolver la empresa es cuál es la cantidad necesaria que se debe producir por cada producto para maximizar la utilidad, estas celdas deben permanecer vacías, dado que Solver “trabajará” en ellas para poder encontrar una solución óptima.
Luego de escribir el problema en nuestra Hoja de Cálculo, vamos a la herramienta de Solver para “decirle a Excel” qué rol cumple cada una de nuestras celdas.


Para poder agregar una nueva restricción, se debe hacer click en “Agregar” y aparecerá una nueva ventana, en donde se debe señalar la celda a la cual se hace referencia, la relación (menor, mayor, igual, etc…) y cuál es el valor de referencia que debe cumplir, el cual puede ser numérico o una celda, como muestra la figura. Sin embargo, es recomendable hacer la relación a una celda que posea el valor numérico para hacer análisis de sensibilidad con mayor facilidad.


Luego de agregar todas las restricciones, se debe seleccionar qué Método de Resolución utilizaremos, lo cual estará definido según el problema que queramos resolver:
  • ·         Para problemas Solver No Lineales Suavizados, se debe utilizar la opción GRG Nonlinear
  • ·         Para problemas Solver Lineales, se debe utilizar la opción Simplex LP. (Nuestro caso)
  • ·         Para problemas Solver No Suavizados, se debe utilizar la opción Evolutionary.
Es importante destacar que Solver no asume la No Negatividad de las Variables, por lo que es recomendable agregarlo como restricción o seleccionar la opción Convertir las Variables Sin Restricción en No Negativas.

Y finalmente, ates de resolver el problema, se debe ir a Opciones, donde se desplegará la siguiente ventana:

Donde le puedes pedir a Solver cómo funcionar, es decir, número de iteraciones a realizar (intentos de solución), precisión de las restricciones, tiempo máximo que se puede demorar en resolver el problema, etc…
Luego de haber especificado todo lo necesario, hacemos click en Resolver, y nos aparecerá una venta como la figura.


Para poder conocer el valor de esta solución, seleccionamos Conservar Solución de Solver. Además, podemos guardar el escenario, es decir, la solución que encontró Solver en este primer intento. Y finalmente, Solver da la opción de generar informes, para lo cual se debe seleccionar la opción de Informes de Esquema.
Finalmente, luego de seleccionar lo que se necesite, se debe hacer click en Aceptar, y Solver mostrará la solución en nuestra Hoja de Cálculo, tal como lo muestra la figura.


Limitantes de Solver

Solver sólo puede resolver problemas de hasta 200 variables de decisión (celdas vacías), 400 restricciones de cota (inferior, superior, igualdad, etc…) y 100 restricciones explícitas, lo cual debe tomarse en cuenta a la hora de querer resolver un problema.

Función Objetivo v/s Solver

Antes de continuar, cabe destacar que la función Buscar Objetivo, cumple una función similar, sin embargo, cuando se trabaja con más de una variable es  recomendable trabajar con Solver.


Links Recomendados

Les recomiendo estos dos videos (http://www.youtube.com/watch?v=400NVJF80b4  y http://www.youtube.com/watch?v=j_nS6YReiN0&feature=related ), donde el segundo muestra las diferencias entre Solver y la Función Objetivo, los cuales son herramientas útiles para resolver problemas, sin embargo, dependiendo de la situación una es mejor que la otra. Los dos videos explica muy bien el procedimiento que se debe seguir para resolver un problema con Solver.
Además, les dejo un link de Bit Uchile (http://bituchile.com/2012/04/nuevo-apunte-de-solver-bit/) donde se explica cómo utilizar Solver con un ejemplo más largo y más detallado.




Leer más...

martes, 24 de abril de 2012

Como se puede usar Solver para determinación de cartera financiera

Solver para determinación de cartera financiera


¿Qué es solver?
Solver es una herramienta que ayuda a resolver y optimizar ecuaciones mediante el uso de métodos matemáticos, basandose en la programacion lineal

Primero que nada es necesario agregar o activar el complemento SOLVER, ya que en la mayoría de los excel no viene activado, por lo que se debe hacer lo siguiente:

Hacer click en el botón de Microsoft Office y, a continuación, hacer click en Opciones de Excel.


Hacer click en Complementos y, en el cuadro Administrar, seleccionar Complementos de Excel.

Hacer click en Ir.

En el cuadro Complementos disponibles, activar la casilla de verificación Complemento Solver y, a continuación, hacer click en Aceptar.

  • Sugerencia, si Complemento Solver no aparece en la lista del cuadro Complementos disponibles, hacer click en Examinar para buscar el complemento.
  • Si se indica que el complemento Solver no está instalado actualmente en el equipo, hacer click en Sí para instalarlo.
  • Una vez cargado el complemento Solver, el comando Solver estará disponible en el grupo Análisis de la ficha Datos.
Proceso de construcción de modelos:
1- Definir variables de decisión
2- Definir la
función de objetivos
3- Definir las restricciones
Utilidad o perdida = PX - CX - F
MAX Z = PX - CX - F
S.A
Donde:
P= Precio
C= Costo
X= Utilidades vendidas
F= Costo fijo

X<= U
X<= D
X<= O

Ejemplo para ver cómo usar "SOLVER"

Pepito es presidente de una empresa de inversiones que se dedica a administrar las carteras de acciones de varios clientes Un nuevo clientes ha solicitado que la compañía se haga cargo de administrar para él una cartera de 100.000. A ese cliente le agradaría restringir la cartera a una mezcla de tres tipos de acciones únicamente, como podemos apreciar en la siguiente tabla

Ahora veremos cómo crear un modelo de programacion lineal para mostrar cuántas acciones de cada empresa tendría que comprar Pepito con el fin de maximizar el rendimiento anual total estimado de esa cartera, es decir el optimo.


  • Para solucionar este problema debemos seguir los pasos para la construcción de modelos de programación lineal:
    1.- Definir la variable de decisión.
    2.- Definir la función objetivo
    3.- Definir las restricciones.
  •  Luego construimos el modelo:
    MAX Z = 7X1 + 3X2 + 3X3
    S.A.:
    60X1 +25X2 + 20X3 <= 100.000
    60X1 <= 60.000
    25X2 <= 25.000
    20X3 <= 30.000
    Xi >= 0

  • En la fila 2 se coloca la variable de decisión la cual es el número de acciones y sus valores desde la B2 hasta la D2.
  • En la fila 3 el rendimiento anual y sus valores desde B3 hasta D3.
  • En la celda E3 colocaremos una formula la cual nos va indicar el rendimiento anual total, =sumaproducto($B$2:$D$2;B3:D3).
  • Desde la fila B5 hasta la D8 ponermos los coeficientes que acompañan a las variables de decisión que componen las restricciones.
  • Desde la E5 hasta la E8 se encuentra la función de restricción y no es mas que utilizar la siguiente formula =sumaproducto($B$2:$D$2;B5:D5) la cual se alojaría en la celda E5, luego copiamos hasta la E8.
  • Desde la F5 hasta F8 se encuentran los valores de las restricciones.
  • Desde la G5 hasta G8 se encuentra la holgura o excedente.





Qué especificar dentro del cuadro de Solver:
  • La celda que va a optimizar
  • Las celdas cambiantes
  • Las restricciones 
Así tendremos la siguiente pantalla:





 
Como se puede observar en la celda objetivo se coloca la celda que se quiere optimizar, en las celdas cambiantes las variables de decisión y por último se debe de complementar con las restricciones. Una vez realizado estos pasos se debe apretar en el icono de "Opciones" y hacer clic en "Asumir modelo lineal" y enseguida el botón de "Aceptar". Luego hacer clic en el botón de "Resolver" para realizar la optimización. Hay que leer mensaje de Solver y ahí observar si se encontró una solución o hay que modificar el modelo, en caso de haber encontrado una solución óptima se podrá aceptar o no dicha solución, luego se podrá analizar un informe de análisis de sensibilidad para  tomar la mejor decisión de la cartera de inversiones que estamos evaluando.




Finalmente vemos que en el optimo, Pepito debría comprar 750 acciones de Navesa, 1000 acciones de Telectricidad, y 1500 acciones de Rampa. generando una utilidad de 12750

Como conclución podemos ver que excel es una rápida y eficaz herramienta para poder analizar la compra de activos de renta variable para poder optimizar nuestra cartera de inversion, a través de la cual podemos obtener que cantidad de acciones o que porcentaje de nuestro capital invertir en cada una de ellas.

Leer más...