Uso del Solver en Excel

For the english version of this post, press here.

Ayer hablamos sobre como usar Buscar Objetivo (Goal Seek) de Excel para resolver problemas dependientes de una sola variable. La funcionalidad Buscar Objetivo nos permite responder a la pregunta cuál debe ser el valor de una variable para obtener un determinado valor en una función (o fórmula).
Cuando queremos resolver problemas que dependen de más de una variable, la herramienta a usar es el Solver.
El Solver es un complemento de Excel (Add – In). Si no aparece en el menú Herramientas (inmediatamente debajo de Buscar Objetivo) hay que instalarlo.
Para esto hay que activar el menú Herramientas--->





Y señalar el Solver

Supongamos el siguiente problema (para descargar el cuaderno del ejemplo apretar aquí):

Dados estos datos



Queremos armar la cartera de inversiones que maximice las ganancias, sujeto a la restricción de no invertir en cada tipo de inversión más de lo permitido.
Para resolver este problema con el Solver debemos primero construir un modelo:



Las fórmulas en el rango E7:E9 son =$C$3*D7;

en el rango F7:F9 son =E7*C7;
las celdas en el rango D7:D9 las dejamos vacías para recibir en ellas la solución del problema.

El siguiente paso es determinar los parámetros del solver:




Interpretamos el diálogo que se abre de la siguiente manera:

Set Target Cell to: la celda para la cual queremos encontrar la solución. En nuestro caso F10.

Equal to: en nuestro caso elegimos "Max", es decir, maximizar el resultado en F10 (que contiene la fórmula =SUMA(F7:F9))

Subject to Constraints: las condiciones del caso. Para introducir estas condiciones apretamos el botón Add.

En nuestro caso:

D10 = 1, ya que el total de las partes de la cartera no pueden superar el 100% de la cartera.

D7<=B7, para cumplir la condición que la inversión en este tipo de bien no supere lo establecido en la celda B7 D7<=0, para evitar que Solver nos de un resultado negativo, lo que sería cierto matemáticamente, pero carente de sentido en la realidad. De la misma manera procedemos con el resto de la condiciones.
Finalmente pulsamos "Solve". Se abre un diálogo donde Excel nos muestra la solución encontrada.



Para retener los resultados apretamos "Keep Solver Solution".



Categorías: Funciones&Formulas_, Varios_




Technorati Tags: SOLVER

Uso de Buscar Objetivo (Goal Seek) en Excel.

For the english version of this post, press here.

Esta semana he estado ocupando desarrollando un modelo de optimización para el departamento de productos para la industria avícola de nuestra empresa.
El modelo que construí, y cuyo objetivo no será mencionado por razones obvias, está basado en el uso de la funcionalidad Solver de Excel. En el pasado ya he escrito sobre el uso del Solver para resolver sistemas de ecuaciones. En esta oportunidad hablaremos sobre Buscar Objetivo (Goal Seek en la versión inglesa). Al uso del Solver le dedicaré la próxima entrada.

El cuaderno con los ejemplos de esta entrada se puede descargar aquí.

El uso de Buscar Objetivo es sencillo. Supongamos la siguiente pregunta: sabiendo la tasa de interés, el plazo en el que deberemos pagar el préstamo y el monto del pago mensual que estamos dispuestos a afrontar, cuál es monto del préstamo que podemos pedir.

Para encontrar la solución montamos un modelo (sencillo en este caso) en Excel

La fórmula en la celda C5 es =PAGO(C3/12,C4,-C6), dónde dividimos la tasa de interés anual (C3) por 12 para obtener la tasa mensual, C4 el plazo del préstamo en meses, C5 el pago mensual que estamos dispuestos a afrontar y C6 la celda donde recibiremos la solución (la celda está en blanco antes de usar el Buscar Objetivo).

Supongamos que el monto mensual que estamos dispuestos a pagar es de 1,500. Pulsamos el menú Herramientas--->Buscar Objetivo. En el diálogo que se abre completamos los siguientes datos



Apretamos "Aceptar" y vemos el resultado que Excel encontró (no siempre se puede encontrar una solución)



Volvemos a apretar "aceptar" y los datos de la solución pasan a la hoja de Excel.

Buscar Objetivo funciona cambiando una de las variables de la formula. En casos en que queremos buscar un resultado evaluando más de una variable y sujetando el resultado a condiciones, debemos usar el Solver. Esto será expuesto en la próxima entrada.

Categorías: Funciones&Formulas_, Varios_

Technorati Tags: Buscar Objetivo, Goal Seek

Manejo de pequeñas bases de datos en Excel – Acceso a base de datos externos con MS Query

Esta es otra nota de la serie sobre manejo de pequeñas bases de datos en Excel. Las entradas anteriores fueron

1 - Manejo de pequeñas bases de datos en Excel con tablas dinámicas (pivot tables), donde nos ocupamos de cómo construir una lista o tabla que sirva de base para generar una tabla dinámica (pivot table).

2 - Manejo de pequeñas bases de datos en Excel con tablas dinámicas (pivot tables) - Actualización de Datos, donde vimos como actualizar la tabla dinámica cada vez que los datos de la base de datos cambian.

3- Manejo de pequeñas bases de datos en Excel – Referencias dinámicas con nombres (NAMES), donde expliqué como generar una referencia al rango de la base de datos en la tabla dinámica, de manera que no haga falta actualizarlo manualmente con cada cambio.

En todas estas entradas la base de datos de la tabla dinámica era una lista que se encontraba en una hoja de Excel. A esta tabla le agregamos datos (o borramos datos de ella) manualmente.
En esta cuarta entrada nos ocuparemos de cómo acceder bases de datos externas con Excel. Para esta tarea Excel cuenta con un programa auxiliar, el MS Query.
Para los ejemplos de esta entrada he utilizado la base de datos Northwind, que forma parte del paquete Office. Estos archivos se encuentran (Office XP) en la carpeta C:\Program Files\Microsoft Office\Office10\Samples .

Para importar los datos externos a una hoja Excel usamos los comandos Datos--->Obtener datos externos--->Nueva consulta de base de datos.

Si se fijan en la barra inferior de la pantalla, verán que Excel a abierto un nuevo programa, el MS Query




En el diálogo que se abre, debemos señalar cual es nuestra fuente de datos



En nuestro ejemplo basta con señalar el tipo de base datos, MS Access, que ya figura en la lista de MS Query. A veces el tipo de base de datos no aparece en al lista y el acceso debe ser creado. En este ejemplo nos limitaremos al primer caso.
Luego de elegir la fuente, elegimos la tabla y/o los campos de la tabla que estamos interesados en importar



Luego de elegir la fuente, veremos la lista tablas disponibles. Para no complicar nuestro ejemplo elegiremos sólo una tabla. Al lado del nombre de cada tabla hay un símbolo "+". Al pulsarlo veremos la lista de campos de la tabla- Esto nos permite elegir cuales estamos interesados en importar. Cada campo ocupará una columna en la hoja de Excel.

En nuestro ejemplo elegimos la tabla "Alphabetical List of Products", y de ella los campos que aparecen en la imagen (eso se hace señalando los campos en la ventanilla izquierda y pulsando la flecha en dirección a la ventanilla derecha)



Luego podemos establecer criterios para la importación, por ejemplo productos cuyos precios sean mayores de 15



En el próximo diálogo podemos ordenar los datos de acuerdo a los campos



Finalmente podemos elegir si importar los datos directamente a la hoja de Excel, si ver los datos en el MS Query (luego se pueden importar desde allí) o crear un cubo OLAP (tema sobre el cual escribiré en el futuro)



Si elegimos importar los datos a una hoja Excel, debemos indicar a partir de qué celda comenzar (por lo general será A1)



Como pueden ver, Excel nos propone en este mismo diálogo crear una tabla dinámica.

Apretamos "Aceptar" y Excel importará los datos a la hoja



A partir de este momento podemos trabajar como con toda lista de datos en Excel, ordenar los datos, generar subtotales, usar filtros y generar tablas dinámicas.

Si en lugar de importar los datos a una hoja, elegimos la opción "Crear un informe de tabla dinámica", Excel abrira una plantilla de tabla dinámica vacía. En lugar de importar los datos a una hoja, Excel establece un vínculo con los datos en la tbla de la base de datos.

En la próxima entrada daré una breve explicación de cómo proceder cuando la fuente de datos no aparece en la lista de MS Query.

Categorías: Manejo de Datos_

Technorati Tags: MS Query, Importar Datos, Bases de Datos

Si te gustó esta entrada anotala en del.icio.us