Clase 5 excel 2010

Vista previa del material en texto

Capitulo IX.
Uso de Solver y Macros
9.1 Uso de Solver
9.2 Macros
9.3 Extras
9.1 Uso de Solver
•Introducción
Se usa para resolver problemas de Optimización
-Encontrar máximos o mínimos de una función.
f (x1, x2,..., xn).
Métodos más conocidos para encontrar el óptimo de una función es a través del análisis 
de sus derivadas y mediante iteraciones (partiendo de una solución inicial, mediante 
algún algoritmo iterativo, se mejora sucesivamente la solución).
Podemos incluir restricciones a las variables, de modo que cumplan una o más 
condiciones, por lo general son función de las mismas variables.
gj (x1, x2, ..., xn) <=, = ó >= cj , donde cj es una constante
Condiciones típicas, no negatividad de las variables. 
9.1 Uso de Solver
• Los modelos mas típicos son de Programación Lineal, en que las restricciones y la 
función objetivo son funciones lineales.
•La opción Solver de EXCEL también 
sirve para resolver problemas de 
optimización no lineal
•Para acceder a Solver, seleccione 
la pestaña Datos y luego Solver. 
9.1 Uso de Solver
• Si no aparece, deberá instalar la herramienta Solver.
En Microsoft Office 2007 / 2010
1.Hacer clic en el botón de Office, (o ir a la pestaña Archivo) y luego seleccionar 
Opciones De Excel. 
2. Elija la opción Complementos, si Solver no aparece en la lista del cuadro de diálogo 
Complementos, en el Cuadro Administrar seleccionar Complementos de Excel.
3. Hacer clic en Ir
4. En el cuadro Complementos disponibles, active la casilla de verificación 
Complemento Solver y, a continuación, haga clic en Aceptar
5. Si Complemento Solver no aparece en la lista del cuadro Complementos disponibles, 
haga clic en Examinar para buscar el complemento (debería estar en la carpeta 
Archivos de Programas/Microsoft Office�subcarpeta Macros/Solver, o ejecute el 
programa de instalación si no puede 
localizar el archivo (se necesita el CD de Office 2007 / 2010).
6. Una vez cargado el complemento Solver, el comando Solver estará disponible en el 
grupo Análisis de la ficha Datos
9.1 Uso de Solver
• Cuadro de diálogo de los parámetros de Solver
•Ventana para describir el problema 
de optimización
•Celda Objetivo: Seleccionar celda 
donde esta definida función 
Objetivo.
•Valor de la celda Objetivo: si desea
Obtener máximo, mínimo o un valor 
Específico de la función objetivo.
•Cambiando las Celdas: Ubicación de las variables de decisión para el problema (x1, 
x2, ..., xn). 
9.1 Uso de Solver
•Sujetas a las siguientes Restricciones: Definición de las restricciones, gj(x1, x2, ..., 
xn), del problema.
Botón Agregar para definir nuevas restricciones (se pueden cambiar o eliminar)
•Agregar: aparece la ventana Agregar Restricción
•Restablecer Todo: borra el problema 
en curso y restablece todos los 
parámetros a sus valores por defecto
•Opciones: se accede a las opciones 
de Solver (se verán mas adelante)
9.1 Uso de Solver
•Referencia de la Celda: ubicación de una celda (por lo general, una celda con una 
fórmula) 
•Tipo de restricción: flecha del campo central desplegable (<=, >=, =, int se refiere a 
un número entero, o bin se refiere a binario). 
•Restricción: puede llevar una fórmula de celdas, una simple referencia a una celda o 
un valor numérico, corresponde al término cj, asociado a la restricción.
•Aceptar o Agregar para introducir
la restricción.
9.1 Uso de Solver
•Opciones de Solver:
•Tiempo máximo
Limita el tiempo que tarda el proceso de 
solución. Valor predeterminado 100 seg.
•Iteraciones
Limita el tiempo que tarda el proceso de 
solución, limitando el número de cálculos 
iterativos.
•Precisión
Controla la precisión de las soluciones utilizando el número que se introduce para 
averiguar si el valor de una restricción satisface un límite inferior o superior. Debe 
indicarse una fracción entre 0 y 1. Cuanto mayor sea la precisión, más tiempo se tardará 
en encontrar una solución
9.1 Uso de Solver
•Tolerancia
Porcentaje mediante el cual la celda objetivo de una solución satisface las 
restricciones externas puede diferir del valor óptimo verdadero y todavía considerarse 
aceptable. Esta opción sólo se aplica a los problemas que tengan restricciones enteras. 
Una tolerancia mayor tiende a acelerar el proceso de solución. 
•Convergencia
Si el valor del cambio relativo en la celda objetivo es menor que el número introducido 
en el cuadro Convergencia para las últimas cinco iteraciones, Solver se detendrá. Se 
aplica únicamente a los problemas no lineales y debe indicarse mediante una fracción 
entre 0 y 1. 
•Adoptar modelo lineal
Selecciónelo cuando todas las relaciones en el modelo sean lineales. El programa 
utiliza el algoritmo simplex.
9.1 Uso de Solver
•Mostrar resultado de iteraciones
Para que Solver muestre temporalmente los resultados de cada iteración. Esta opción es 
válida sólo en modelos no lineales.
•Usar escala automática
Para utilizar la escala automática cuando haya grandes diferencias de magnitud entre 
las entradas y los resultados; por ejemplo, maximizar el porcentaje de beneficios 
basándose en una inversión de millones de dolares.
•Adoptar no-negativo
Solver supone un límite inferior de 0 para todas las celdas ajustables en las que no se 
haya definido un límite inferior.
•Cargar modelo
Cuadro de diálogo, donde puede especificarse la 
referencia del modelo que desee cargar. Debe 
haber sido guardado previamente
9.1 Uso de Solver
•Guardar modelo
Cuadro de diálogo, donde puede especificar la ubicación en que desee guardar el 
modelo. Solo cuando se desea guardar más de un modelo con una hoja de cálculo; el 
primer modelo se guardará de forma automática.
•Opciones para Modelos No Lineales
•Estimación
Especifica el enfoque que se utiliza para obtener las estimaciones iniciales de las 
variables básicas en cada una de las búsquedas dimensionales.
•Lineal
Utiliza extrapolación lineal de un vector tangente.
•Cuadrática
Utiliza extrapolación cuadrática, puede mejorar en gran medida los resultados de 
problemas no lineales.
9.1 Uso de Solver
•Derivadas
Diferencia para estimar las derivadas parciales de la función objetivo y las funciones de 
las restricciónes.
•Progresivas
Cuando los valores de restricción cambien relativamente poco, para la mayoría de 
los problemas.
•Centrales
Para problemas en que las restricciones cambian rápidamente, especialmente cerca 
de los límites. 
•Hallar por
Algoritmo que se utiliza en cada iteración para determinar la dirección en que se hace 
la búsqueda.
•Newton
Utiliza un método cuasi Newton que necesita más memoria pero menos 
iteraciones.
•Gradiente Conjugado
Necesita más iteraciones para alcanzar un determinado nivel de precisión. 
9.1 Uso de Solver
•Derivadas
Diferencia para estimar las derivadas parciales de la función objetivo y las funciones de 
las restricciones.
•Progresivas
Cuando los valores de restricción cambien relativamente poco, para la mayoría de 
los problemas.
•Centrales
Para problemas en que las restricciones cambian rápidamente, especialmente cerca 
de los límites. 
•Hallar por
Algoritmo que se utiliza en cada iteración para determinar la dirección en que se hace 
la búsqueda.
•Newton
Utiliza un método cuasi Newton que necesita más memoria pero menos 
iteraciones.
•Gradiente Conjugado
Necesita más iteraciones para alcanzar un determinado nivel de precisión. 
9.1 Uso de Solver
•Ejemplo de Programación Lineal.
Una empresa ganadera puede comprar tres tipos de ingredientes alimenticios. El 
ganado de la empresa tiene ciertas necesidades alimenticias con respecto a las grasas, 
proteínas, calcio y hierro. Cada vaca requiere al menos 10 unidades de calcio, no más 
de 7,5 unidades de grasa, al menos 12 unidades de hierro y al menos 15 unidades de 
proteína al día. La tabla siguiente indica la cantidad de grasa, proteína, calcio y hierro 
por cada kilo de los tres ingredientes alimenticios. El alimento de grado 1 cuesta 0,25 
dólares; el de grado 2; 0,10 dólares; y el de grado 3; 0,08 dólares por kilo. El ganado se 
puedealimentar con una mezcla de los tres tipos de alimento sin procesar. La empresa 
está interesada en alimentar al ganado del modo más barato posible, pero obviamente 
satisfaciendo las necesidades alimenticias. 
9.1 Uso de Solver
•Ejemplo de Programación Lineal.
•Minimizar el costo total de los alimentos.
0,25*grado1 + 0,1*grado2 + 0,08*grado3 
•sujeto a las restricciones alimenticias
0,7*grado1 + 0,8*grado2 + 0*grado3 ≥10 (Calcio) 
0,9*grado1 + 0,8*grado2 + 0,8*grado3 ≥ 12 (Hierro) 
0,8*grado1 + 1,5*grado2 + 0,9*grado3 ≥ 15 (Proteínas) 
0,5*grado1 + 0,6*grado2 + 0,4*grado3 ≤ 7,5 (Grasa) 
grado1, grado2, grado3 ≥ 0 
9.1 Uso de Solver
•Informes de Resultados de Solver.
Solver genera tres informes para programas lineales: Informe de Respuestas, Informe 
de Sensibilidad e Informe de Límites. 
La opción Utilizar solución de Solver en la ventana de resultados, mantiene en la propia 
hoja los valores óptimos.
9.1 Uso de Solver
•Informe de Respuesta
Proporciona el valor inicial y final de la celda objetivo y de todas las celdas cambiantes 
así como un listado de cada restricción y su estado. 
Divergencia indica la diferencia con respecto a la restricción. 
9.1 Uso de Solver
•Informe de Sensibilidad 
Valor óptimo de cada celda Cambiante, su gradiente reducido, el coeficiente de función
objetivo y el aumento y la disminución de éste para el cual la solución en curso 
permanece óptima (el resto permanece fijo). 
Precio sombra (o coef de Lagrange) de cada restricción es el cambio en la función 
objetivo por unidad de aumento en el lado derecho de cada restricción, se da junto con 
el aumento y disminución del valor del lado derecho para el cual es válido el precio. 
9.1 Uso de Solver
•Informe de Límites 
Entrega los límites superior e inferior de cada celda cambiante manteniendo el resto de 
las celdas cambiantes en su valor actual y cumpliendo las restricciones. El informe que 
se detalla a continuación muestra que la solución del ejemplo de la Colorado Cattle 
Company es muy ajustada (con las tres celdas cambiantes fijas en sus valores de 
destino).
9.1 Uso de Solver
•Resolución de problemas no lineales y enteros 
•Modelos Lineales enteros, 
•Modelos No lineales
•Modelos enteros no lineales 
•Se logra mediante las mismas técnicas descritas anteriormente. 
•Restricción para un número entero desde la ventana Agregar Restricción, seleccionar 
en el campo Referencia de la Celda el tipo int (entero), para variables binarias elija bin. 
•Para modelos no lineales, lo único que tiene que hacer es NO seleccionar Adoptar 
Modelo Lineal en la ventana Opciones.
9.1 Uso de Solver
•Ejemplo de modelo No lineal 
Modelo típico que muestra las ventas en función de los gastos en publicidad y de un 
factor de temporada. Esta función es no lineal y se expresa:
Unid. vendidas = 35*factor de temporada*(publicidad+3000)^0.5
9.2 Macros
•Definición
Macro: serie de pasos que se almacenan y se pueden activar con alguna tecla de control y 
una letra (o desde la pestaña Vista � macro). 
Excel cuenta con un lenguaje de programación llamado Visual Basic, este permite hacer 
o resolver los problemas mas fácilmente, solo debemos aprender a programarlo.
Cuando grabamos una macro en la barra de estado aparecerá un botón para detener la 
grabación. 
9.2 Macros
•Para aprender partiremos creando una Macro sencilla que asigne un formato específico a 
una celda.
•El formato es el siguiente:
-Letra tamaño 12
-Comic Sanz
-Celda rellena color verde claro
-Texto en negrita
-Color del texto azul oscuro
Seguir los siguientes pasos:
Hacer clic en el botón Grabar Macro, del menú de Macros en la pestaña Vista. Windows 
activa el cuadro de dialogo Grabar Macro, que permitirá darle nombre a la macro y cual 
será el método abreviado para ejecutarla. 
9.2 Macros
- Asignar un nombre a la macro (no usar espacios)
-En Método Abreviado se indica que la macro se activara con la tecla Control(CTRL) + 
la letra que usted indica, en este caso usemos CTRL+a
-Hacer clic en el botón Aceptar. Windows empezara a grabar todos los pasos en la Macro, 
aparecerá un botón (cuadrito Azul) en la barra de estado para detener la grabación. 
-Cambiar el Tamaño (12) y Tipo de Letra (Comic Sanz)
-Asigne el color de relleno de la celda (verde claro)
-Cambie la letra a Negrita.
-Cambie el color de Fuente (Azul)
-Recuerda que todos estos pasos están siendo almacenados en la macro que estamos 
grabando. 
-Presionar el Botón Detener Grabación (cuadrito azul) en la barra de estado
-Nota. Cada vez que presiones Control + a Excel ejecutara la macro y efectuara los pasos 
en la celda que te encuentres. Puedes grabar todas las macros que desees.
9.2 Macros
-Insertar un botón de Activación.
Para evitar acceder al menú de macros cada vez 
que se desea ejecutar, podemos insertar un botón, 
que active la macro.
Los pasos son:
-Insertar una imagen prediseñada o 
autoforma.
-Hacer clic con el botón derecho del mouse
seleccionar la opción Asignar macro…
-Seleccionar la macro que desea asignar a 
la imagen prediseñada
-Pulsar Aceptar
Ahora cada vez que presione sobre el botón
creado se ejecutara la macros que acaba de 
asociar.
9.3 Extras
•Dividir paneles.
-En la parte superior de la barra de desplazamiento vertical o en el extremo derecho 
de la barra horizontal, sitúe el puntero sobre el cuadro de división.
-Cuando el puntero adopte la forma: desplácelo a la 
posición deseada.
-O seleccionar desde la Pestaña Vista � Dividir
-Para quitar división elegir nuevamente la opción Dividir desde la pestaña Vista o 
hacer doble clic sobre la barra de división que se desea eliminar.
9.3 Extras
•Inmovilizar paneles.
Permite seleccionar los datos que permanecen visibles al desplazarse en una hoja. 
Por ejemplo, permite mantener visibles los rótulos de las filas y las columnas 
mientras se desplaza sobre la hoja de cálculo.
Para inmovilizar un panel
Panel horizontal superior: Seleccione la fila situada 
debajo de donde desee que aparezca la división.
Panel vertical izquierdo: Seleccione la columna 
situada a la derecha de donde desee que aparezca la división.
Paneles superior e izquierdo: Haga clic en la celda situada debajo y a la derecha de 
donde desee que aparezcan las divisiones.
En la pestaña Vista, haga clic en Inmovilizar paneles.
Para movilizar paneleshaga clic en Movilizar paneles de la Pestaña Vista.