Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

Resumen

En este artículo se describe cómo utilizar Microsoft Excel Solver en Microsoft Excel 97 para crear macros de Microsoft Visual Basic. Microsoft Excel Solver es un complemento de Microsoft Excel.

Además, este artículo contiene información acerca de cómo crear macros, cómo diseñar una macro y cómo trabajar con las restricciones de una macro. Este artículo también describe el algoritmo y métodos utilizados por Solver de Microsoft Excel. La lista siguiente proporciona todos los temas tratados en el artículo.

INTRODUCCIÓN

Este artículo contiene información acerca de Microsoft Excel Solver.

Más información

Descripción de Microsoft Excel Solver

Microsoft Excel Solver es un complemento de Microsoft Excel. Microsoft Excel Solver le ayuda a determinar el valor óptimo para una fórmula en una celda de destino determinado en una hoja de cálculo de Microsoft Excel. Microsoft Excel Solver ajusta los valores de otras celdas que estén relacionadas con la celda objetivo mediante el uso de una ecuación. Después de crear una ecuación y definir un conjunto de restricciones para las variables o parámetros en la ecuación, Microsoft Excel Solver intenta varias soluciones para llegar a una respuesta que satisfaga todas las restricciones. Microsoft Excel Solver utiliza los siguientes elementos para "resolver" una ecuación:
  • Celda objetivo : la celda objetivo es el objetivo. Es la celda en el modelo de hoja de cálculo que se minimizada, maximizada o establecido en un valor determinado.
  • Las celdas cambiantes - celdas cambiantes son las variables de decisión. Estas celdas afectan al valor de la celda objetivo. Estas celdas se cambian por Microsoft Excel Solver para encontrar la solución óptima para la celda objetivo.
  • Restricciones : restricciones son restricciones sobre el contenido de las celdas. Por ejemplo, una celda en un modelo de hoja de cálculo puede restringirse a valores enteros, mientras que otra celda podrán limitarse a es menor que un valor determinado.
Puede automatizar la creación y la manipulación de modelos de Solver de Microsoft Excel utilizando una de Microsoft Visual Basic para Aplicaciones macro (VBA). Este artículo describe cómo utilizar el lenguaje de macro VBA para utilizar las funciones de Microsoft Excel Solver en Microsoft Excel 97. En este artículo se supone que está familiarizado con el lenguaje VBA y el Editor de Microsoft Visual Basic para Microsoft Excel 97. Los ejemplos que se utilizan en este artículo están disponibles para su descarga en el siguiente sitio Web de Microsoft:Nota: También puede utilizar las macros y los ejemplos que se describen en este artículo en las versiones de Microsoft Excel 5.0 y 7.0.

Volver al principio

Cómo utilizar las funciones de Microsoft Excel Solver en una macro de VBA

Para utilizar las funciones de complemento de Microsoft Excel Solver en una macro de VBA, debe hacer referencia el complemento desde el proyecto VBA del libro que contiene las macros. Si no se hace referencia el complemento Solver de Microsoft Excel, recibirá el siguiente error de compilación cuando intenta ejecutar la macro:
Error de compilación: Sub o Function no definida.
Para hacer referencia el complemento de Microsoft Excel Solver para las macros en el libro, siga estos pasos:
  1. Abra el libro.
  2. En el menú Herramientas , elija
    Macroy, a continuación, haga clic en Editor de Visual Basic.
  3. En el menú Herramientas , haga clic en
    Referencias.
  4. En la lista Referencias disponibles , haga clic para activar la casilla de verificación Solver.xls y, a continuación, haga clic en Aceptar.

    Nota: Si no ve Solver.xls en las Referencias disponibles
    lista, haga clic en Examinar. En el cuadro de diálogo Agregar referencia , busque y seleccione el archivo Solver.xla y, a continuación, haga clic en Abrir. El archivo Solver.xla normalmente se encuentra en la subcarpeta C:\Program Files\Microsoft Office\Office\Library\Solver.
Ahora está preparado para utilizar las funciones de Microsoft Excel Solver en una macro de VBA.

Volver al principio

Cómo diseñar una macro VBA que crea y resuelve un modelo simple de Microsoft Excel Solver

Aunque Microsoft Excel Solver ofrece muchas funciones, las tres funciones siguientes son fundamentales para crear y resolver un modelo:
  • La función SolverOK
  • La función SolverSolve
  • La función SolverFinish

La función SolverOK

La función SolverOK define un modelo básico de Solver de Microsoft Excel. La función SolverOK suele ser la primera función que se va a utilizar para generar el modelo de Solver de Microsoft Excel. La función SolverOK es equivalente a hacer clic en Solver del menú Herramientas y, a continuación, especificando las opciones en el cuadro de diálogo Parámetros de Solver . La siguiente es la sintaxis de la función SolverOK :
SolverOK (argumentos SetCell, MaxMinVal, ValueOf, ByChange)
La siguiente información describe la sintaxis de la función SolverOK :
  • Argumentos SetCell especifica la celda objetivo.
  • MaxMinVal corresponde a si desea resolver la celda de destino para un valor máximo (1), un valor mínimo (2) o un valor específico (3).
  • ValueOf especifica el valor con el cual coincide la celda objetivo. Si MaxMinVal se establece a 3, debe especificar este argumento. Si MaxMinVal se establece en 1 o 2, puede omitir este argumento.
  • ByChange especifica la celda o rango de celdas que se cambiarán.
Figura 1 asocia los argumentos de la función SolverOK los parámetros en el cuadro de diálogo Parámetros de Solver .


La figura 1. Parámetros que están asociados con los argumentos SolverOK

 Figure 1. Parameters that are associated with the SolverOK arguments

La función SolverSolve

La función SolverSolve resolver el modelo utilizando los parámetros que especificó con la función SolverOK . Ejecución de la función SolverSolve es equivalente a hacer clic en resolver en los Parámetros de Solver
cuadro de diálogo. La siguiente es la sintaxis de la función SolverSolve :
SolverSolve (UserFinish, ShowRef)

La siguiente información describe la sintaxis de la función SolverSolve :
  • UserFinish indica si desea que el usuario termine de resolver el modelo.

    Para devolver los resultados sin mostrar el cuadro de diálogo Resultados de Solver , establezca este argumento en TRUE. Para devolver los resultados y mostrar el cuadro de diálogo Resultados de Solver , establezca este argumento en FALSE
  • ShowRef identifica la macro que se llama cuando Microsoft Excel Solver devuelva una solución intermedia.

    El argumento ShowRef debe utilizarse sólo cuando se pasa TRUE al argumento StepThru de la función SolverOptions .

La función SolverFinish

La función SolverFinish indica qué hacer con los resultados y qué clase de informe debe crear al finalizar el proceso de solución. La siguiente es la sintaxis de la función SolverFinish :
SolverFinish (KeepFinal, ReportArray)

La siguiente información describe la sintaxis de la función SolverFinish :
  • KeepFinal indica qué hacer con los resultados finales. Si KeepFinal es 1, los valores de la solución final se mantienen en las celdas cambiantes, reemplazando los valores. Si KeepFinal es 2, los valores de la solución final se descartarán y se restablecerán los valores anteriores.
  • ReportArray especifica una matriz que indica el tipo de informe que Microsoft Excel creará cuando se alcanza la solución. Si ReportArray se establece en 1, Microsoft Excel crea un informe de resultados. Si se establece en 2, Microsoft Excel crea un informe de sensibilidad y conjunto de 3 Microsoft Excel crea un informe de límites. Para obtener más información sobre estos informes, consulte la sección "cómo generar informes para soluciones".
La figura 2. Microsoft Excel Solver opciones de resultados que están asociados con argumentos SolverFinish

 Figure 2. Solver results options that are associated with SolverFinish arguments

Este artículo describe cómo crear un sencillo modelo de Microsoft Excel Solver de forma interactiva. El primer paso es crear la hoja de cálculo para el modelo. La hoja de cálculo contiene algunas celdas de datos y al menos una celda que contenga una fórmula. Esta fórmula depende de las otras celdas de la hoja de cálculo. Después de configurar la hoja de cálculo, haga clic en
Solver en el menú Herramientas . En el cuadro de diálogo Parámetros de Solver , especifique la celda de destino, el valor que va a resolver, el rango de celdas que se cambiarán y las restricciones. Haga clic en resolver para iniciar el proceso de solución. Después de que Microsoft Excel Solver ha encontrado una solución, los resultados aparecen en la hoja de cálculo y Microsoft Excel Solver muestra un cuadro de mensaje que pregunta si desea conservar los resultados finales o si desea deshacerse de ellos. Al hacer clic en una de estas opciones, Microsoft Excel Solver finalice.

La figura 3 ilustra un modelo sencillo que puede crear mediante estos pasos.

La figura 3. Un modelo simple: modelo de la raíz cuadrada

 Figure 3. A simple model: The Square Root model

En este ejemplo, el cambio de celda A1, que contiene la fórmula = A1 ^ 2, un valor que hará que la celda A2 es igual a un valor de 50. En otras palabras, buscar la raíz cuadrada de 50. No hay ninguna restricción en el modelo de raíz cuadrada. La macro Find_Square_Root lleva a cabo las siguientes tareas:
  • Configura un modelo que se va a resolver el valor de la celda A2 para un valor de 50 cambiando el valor de la celda A1.
  • Soluciona el modelo.
  • Guarda los resultados finales en la hoja de cálculo sin mostrar el cuadro de diálogo Resultados de Solver .
Esta macro simple crea un modelo de Solver de Microsoft Excel y lo resuelve sin intervención del usuario. El código siguiente describe la macro Find_Square_Root :
    Sub Find_Square_Root()
' Set up the parameters for the model.
' Set the target cell A2 to a value of 50 by changing cell A1.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=50, _
ByChange:=Range("A1")

' Solve the model but do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub

La macro Find_Square_Root2 , es una versión modificada de la macro Find_Square_Root . Si utiliza la función InputBox , la macro Find_Square_Root2 le pide el valor de la celda de destino que desea resolver. Después de introducir un valor, la macro Find_Square_Root2 establece este parámetro como el valor del argumentovalueof SolverOK, soluciona el problema, guarda los resultados en la variable raíz cuadrada y, a continuación, descarta la solución y restablece el valor en la hoja de cálculo a su estado original. Básicamente, la macro Find_Square_Root2 muestra cómo puede guardar los resultados en una o más variables y, a continuación, restaurar las celdas cambiantes a su valor original.


El código siguiente describe la macro Find_Square_Root2 :
    Sub Find_Square_Root2()
Dim val
Dim sqroot

' Request the value for which you want to obtain the square root.
val = Application.InputBox( _
prompt:="Please enter the value for which you want " & _
"to find the square root:", Type:=1)

' Set up the parameters for the model.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=val, _
ByChange:=Range("A1")

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Save the value of cell A1 (the changing cell) before you discard
' the results.
sqroot = Range("a1")

' Finish and discard the results.
SolverFinish KeepFinal:=2

' Show the result in a message box.
MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00")

End Sub

Volver al principio

Cómo generar informes para soluciones

Microsoft Excel Solver ofrece varios tipos de informes que describen cómo los resultados cambiado y cómo cerrar las restricciones llegaron a sus valores críticos. Cada informe se coloca en una hoja independiente del libro. Los tipos de informes que ofrece Microsoft Excel Solver son los siguientes:
  • Informe de resultados : el informe de respuesta enumera la celda objetivo y las celdas cambiantes con sus valores originales y finales correspondientes, restricciones y obtener información acerca de las restricciones.
  • Informe de sensibilidad - el informe de sensibilidad proporciona información acerca de la sensibilidad de la solución consiste en pequeños cambios en la fórmula para la celda objetivo.
  • Informe de límites : el informe de límites muestra la celda objetivo y las celdas cambiantes con sus valores correspondientes, los límites inferiores y superiores y los valores de destino.
Para crear informes para los modelos, especificar una matriz de valores para el argumento ReportArray de la función SolverFinish . Para obtener más información sobre el argumento ReportArray , consulte la sección "SolverFinish (KeepFinal, ReportArray)". Por ejemplo, si desea generar un informe de límites para el modelo que se crea y se resuelve la macro Find_Square_Root2 , modifique la función SolverFinish en la macro para que es similar al ejemplo de código siguiente:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
Para generar varios informes, modifique la función SolverFinish , por lo que es similar al siguiente código de ejemplo:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)
Volver al principio

Cómo utilizar las funciones de Microsoft ExcelSolver en una macro bucle

En muchas situaciones, es una buena idea hacer que Microsoft Excel Solver resolver la celda de destino para varios valores. Generalmente se puede realizar mediante una de las estructuras de bucle que están disponibles con VBA.


La macro Create_Square_Root_Table muestra cómo funciona Microsoft Excel Solver en una macro bucle. La macro Create_Square_Root_Table crea una tabla en una nueva hoja de cálculo. Inserta los números uno a través de diez y la raíz cuadrada correspondiente de cada número. La macro Create_Square_Root_Table crea la tabla utilizando un bucle For para recorrer en iteración los números del 1 al 10 y resolver la celda de destino en el modelo de la raíz cuadrada de un valor que coincide con el número de la iteración. El código siguiente describe la macro Create_Square_Root_Table :
    Sub Create_Square_Root_Table()
' Add a new worksheet to the workbook.
Set w = Worksheets.Add

' Put the value 2 in cell C1 and the formula =C1^2 in cell C2.
w.Range("C1").Value = 2
w.Range("C2").Formula = "=C1^2"

' A loop that will make 10 iterations, starting with the number 1,
' and finishing at the number 10.
For i = 1 To 10

' Set the Solver parameters that indicate that Solver should
' solve the cell C2 for the value of i (where i is the number
' of the iteration) by changing cell C1.
SolverOk SetCell:=Range("C2"), ByChange:=Range("C1"), _
MaxMinVal:=3, ValueOf:=i

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Save the value of i in column A and the results of the
' changing cell in column B.
w.Cells(i, 1) = i
w.Cells(i, 2) = Range("C1")

' Finish and discard the final results.
SolverFinish KeepFinal:=2

Next

' Clear the range C1:C2
w.Range("C1:C2").Clear

End Sub


La macro Create_Square_Root_Table genera la tabla que se muestra en la figura 4.


La figura 4. Resultado generado por la macro Create_Square_Root_Table


Figure 4. Output that is generated by the Create_Square_Root_Table macro

Volver al principio

Cómo trabajar con restricciones

Una restricción es una restricción en el contenido de una o más celdas. Un modelo puede tener una o varias restricciones. El conjunto de restricciones es un conjunto de desigualdades o un conjunto de igualdades que quitar ciertas combinaciones de valores para las variables de la decisión de la solución. Por ejemplo, una restricción puede requerir que una celda sea mayor que cero y otra celda contiene sólo un valor entero.

El modelo de raíz cuadrada que hemos descrito hasta este punto es un modelo sencillo que no contiene ninguna restricción. La figura 5 muestra un modelo que utiliza restricciones. El propósito de este modelo es encontrar la combinación óptima de productos para el máximo beneficio.

La figura 5. Mezcla de productos con reducir el margen de beneficio

Figure 5. Product mix with diminishing profit margin

Por ejemplo, si una empresa fabrica televisores, equipos estéreo, altavoces y se utiliza un inventario de piezas comunes de fuentes de alimentación, conos de altavoz y así sucesivamente. Las partes están en oferta limitada. El objetivo es determinar la combinación más rentable de productos para generar. El beneficio por unidad disminuye con el volumen porque los incentivos de precios adicionales son necesarias para cargar el canal de distribución. El exponente menguando es 0,9. Este exponente se utiliza para calcular las ganancias por producto en el intervalo G11:I11.

Su objetivo es encontrar el máximo beneficio (celda G14). Los valores que va a cambiar para obtener el máximo beneficio son el número de unidades que genere. El intervalo G9:G11 representa las celdas cambiantes de este modelo. La única restricción es que el número de partes que se utiliza no puede superar el número de elementos que tiene a mano. Con Microsoft Excel Solver, esta restricción aparece como E3:E7 < = B3: B7. Si fuera a construir este modelo de Microsoft Excel Solver de forma interactiva, los parámetros de Solver de Microsoft Excel tendría un aspecto similares a los que están en la figura 6.

La figura 6. Parámetros de Solver de Microsoft Excel para la mezcla de productos con el modelo de reducir el margen de beneficio

Figure 6. Microsoft Excel Solver parameters for the product mix with Diminishing Profit Margin model

Para crear y resolver la mezcla del producto con el modelo de margen de beneficio disminuyendo, utilizará una nueva función, la función SolverAdd , además de las funciones de Microsoft Excel Solver VBA descritos anteriormente. La función SolverAdd agrega la restricción al modelo. Ejecutar la función SolverAdd es equivalente a hacer clic en el complemento
botón en el cuadro de diálogo Parámetros de Solver . La
Función SolverAdd tiene la siguiente sintaxis:
SolverAdd (CellRef, relación FormulaText)

La siguiente información describe la sintaxis de la función SolverAdd :
  • CellRef hace referencia a una o más celdas que forman el lado izquierdo de la restricción.
  • Relación es la relación aritmética entre lo lados izquierdo y el derecho de una restricción.
  • Relación puede ser un valor entre 1 y 5 como en el ejemplo siguiente:
    • El valor 1 es menor o igual que (< =).
    • El vaue 2 es igual a (=).
    • El valor 3 es mayor o igual que (> =).
    • El valor 4 es un entero.
    • El valor 5 es el archivo binario (un valor de cero o uno).
  • FormulaText hace referencia a una o más celdas que forman el lado derecho de la constraint.* *
** Cuando se especifica un rango de celdas para el argumento FormulaText de la función SolverAdd , tenga en cuenta si la referencia es absoluta o relativa. En general, debe especificar una referencia absoluta para el argumento FormulaText . Sin embargo, si especifica las referencias relativas para el argumento FormulaText , cuenta que será la referencia relativa a la celda de destino y no en la celda activa.

Nota: En Microsoft Excel, versiones 5.0 y 7.0, utilice la notación F1C1 cuando se especifica una celda o un rango de celdas con el argumento FormulaText . Por el contrario, en Microsoft Excel 97, utilice la notación de estilo A1 para especificar el argumento FormulaText .

La figura 7. Los campos que están asociados con el
Argumentos SolverAdd

 Figure 7. Fields that are associated with the SolverAdd arguments

La macro Maximum_Profit que genera un modelo para la mezcla de productos con el modelo decrecientes. Esta macro ejecuta las siguientes funciones o argumentos:

  • La función SolverOK establece la celda de destino para un valor máximo y especifica las celdas para cambiar.
  • La función SolverAdd agrega la restricción al modelo.
  • La función SolverSolve encuentre una solución sin mostrar la
    Cuadro de diálogo Resultados de Solver .
  • La función SolverFinish devuelve los resultados finales a la hoja de cálculo.
El código siguiente describe la macro Maximum_Profit :
    Sub Maximum_Profit()
' Set up the parameters for the model.
' Determine the maximum value for the sum of profits in cell G14
' by changing the number of units to build in cells G9:I9.
Solverok setcell:=Range("G14"), maxminval:=1, _
bychange:=Range("G9:I9")

' Add the constraint for the model. The only constraint is that the
' number of parts used does not exceed the parts on hand--
' E3:E7<=B3:B7
SolverAdd CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$B$3:$B$7"

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub


Nota: En Microsoft Excel, versiones 5.0 y 7.0, utilice la notación F1C1 cuando se especifica una celda o rango de celdas con el argumento FormulaText . Por el contrario, en Microsoft Excel 97, utilice la notación de estilo A1 para especificar el argumento FormulaText .

Cuando se ejecuta la macro Maximum_Profit , Microsoft Excel Solver encontrará una solución de creación de 160 televisores, 200 estéreos y 80 altavoces para un beneficio máximo de 14,917 dólares.


Volver al principio

Cómo cambiar y eliminar restricciones

Las restricciones de su modelo pueden modificarse o borrarse mediante programación. Las restricciones se identifican por sus argumentos CellRef y Relation .

Para cambiar mediante programación una restricción ya existente, utilice la función SolverChange . La sintaxis de la función SolverChange es el siguiente:
SolverChange (CellRef, relación FormulaText)
Tenga en cuenta que los argumentos de la función SolverChange son los mismos para las que utilizan con la función SolverAdd .

Si desea cambiar la delimitación en la mezcla del producto con el modelo menguando, utilizaría la función SolverChange . Por ejemplo, actualmente la restricción especificada es que el número de elementos utilizados es menor o igual que el número de partes en mano (E3:E7 < = B3: B7). Si desea cambiar esta restricción para que el número de elementos utilizados es menor o igual al número de piezas proyectadas (número de elementos en mano) más el número de pedido de piezas. Esta nueva restricción sería E3:E7 < = D3:D7. La siguiente macro cambiaría la restricción existente E3:E7 < = B3: B7 a E3:E7 < = D3:D7 y resolver para una solución.

El código siguiente describe la macro Change_Constraint_and_Solve :
    Sub Change_Constraint_and_Solve()
' Change the constraint.
SolverChange CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$D$3:$D$7"

' Return the results and display the Solver Results dialog box.
SolverSolve UserFinish:=False

End Sub


Dado que las restricciones se identifican por el CellRef y Relation argumentos, sólo puede cambiar el argumento FormulaText para la restricción mediante la función SolverChange . Si el CellRef y los valores de la relación no coinciden con una restricción ya existente, debe eliminar la restricción y, a continuación, agregar la restricción modificada. Para borrar una restricción, utilice la función SolverDelete . La sintaxis de la función SolverDelete es el siguiente:


SolverDelete (CellRef, relación FormulaText)



Tenga en cuenta que los argumentos de la función SolverDelete son los mismos que aquellos que se utilizan con la SolverAdd y funciones SolverChange .


La macro siguiente muestra cómo eliminar y agregar una restricción. En este ejemplo, la macro Change_Constraint_and_Solve2 quita la restricción E3:E7 < = B3: B7 de la mezcla de productos con el modelo menguando y agrega una nueva restricción. La nueva restricción es simplemente una modificación de la restricción original, donde se invierten los lados izquierdo y derecho de la restricción.

El código siguiente describe la macro Change_Constraint_and_Solve2 :
    Sub Change_Constraint_and_Solve2()
' Reverse the left and right sides of the constraint...
' Delete the constraint E3:E7<=B3:B7 and add the
' constraint B3:B7>=E3:E7.
SolverDelete CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$B$3:$B$7"
SolverAdd CellRef:=Range("B3:B7"), Relation:=3, _
FormulaText:="$E$3:$E$7"

' Return the results and display the Solver Results dialog box.
SolverSolve UserFinish:=False

End Sub


Nota: En Microsoft Excel, versiones 5.0 y 7.0, utilice la notación F1C1 cuando se especifica una celda o rango de celdas con el argumento FormulaText . Por el contrario, en Microsoft Excel 97, utilice la notación de estilo A1 para especificar el argumento FormulaText .

Volver al principio

Cómo cargar y guardar los modelos

Cuando se guarda el libro, los últimos parámetros que especificó en el cuadro de diálogo Parámetros de Solver se guardan con el libro. Por lo tanto, al abrir el libro, los parámetros son el mismo que cuando guardó por última vez el libro.

Puede definir más de un problema en una hoja de cálculo. Cada problema se compone de las celdas y restricciones que se especifiquen en el Parámetro de Solver y los cuadros de diálogo Opciones de Solver . Porque sólo el último problema se guarda con la hoja de cálculo, a menos que guarde explícitamente, perderá todos los demás problemas. Para guardarlos, haga clic en Guardar modelo en el cuadro de diálogo Opciones de Solver . De forma similar, cuando desee restaurar los parámetros previamente guardados, haga clic en Cargar modelo en el cuadro de diálogo Opciones de Solver .

Modelos de Solver se almacenan en un rango de celdas en una hoja de cálculo. La primera celda del rango contiene la fórmula para la celda objetivo. La segunda celda del rango contiene la fórmula que identifica las celdas cambiantes en el modelo. La última celda del rango contiene una matriz que representa las opciones establecidas en el cuadro de diálogo Opciones de Solver . Las celdas entre la celda de la segunda y la última celda contienen las fórmulas que representan las restricciones en el modelo.

La figura 8 ilustra un modelo de programación de empleados. Suponga que trabaja para un pequeño fabricante. Esta tabla muestra la tarifa de cada empleado de pago, el número de horas que están programados y un número previsto de unidades de que cada empleado puede producir en una hora. Su objetivo es satisfacer una cuota específica para el número de unidades producidas mientras minimiza el costo de mano de obra.

La figura 8. Modo de programación de empleado
l
 Figure 8. Employee Scheduling model

Dos factores adicionales (o restricciones) que debe tener en cuenta son el número mínimo y máximo de horas que un empleado puede trabajar y el número de unidades que se desea generar. Si para una semana especificada, es necesario producir 3975 unidades y desea que cada empleado trabajar entre 30 y 45 horas, los parámetros de Microsoft Excel Solver sería similares a los descritos en la tabla siguiente:

ParámetroRango de celdasDescripción
Celda objetivo$D$12Costo de mano de obra.
Celdas cambiantes$C$2:$C$8Horas trabajadas por empleado.
Restricciones$C$ 2: $C$ 8 < = 45Número máximo de horas por empleado es 45.
$C$ 2: $C$ 8 > = 30Número mínimo de horas por empleado es 35.
$G 12 = 3975Número de unidades es 3975.


Sus objetivos son solucionar para costo de mano de obra óptima semanalmente, para guardar cada modelo semanalmente y podrá cargar cualquier modelo semanal cuando lo necesite.

En una macro, los parámetros de Solver de Microsoft Excel para un modelo pueden guardarse y cargarse mediante el SolverSave y las funciones de SolverLoad respectivamente. El SolverSave y las funciones de SolverLoad tienen la siguiente sintaxis:

SolverSave (SaveArea)

SolverLoad (LoadArea)


El SolverSave y las funciones de SolverLoad tienen un solo argumento, SaveArea y los argumentos de LoadArea respectivamente. Estos argumentos especifican un rango en una hoja de cálculo donde se almacena la información del modelo.

La siguiente macro New_Employee_Schedule , se muestra cómo crear, resolver y guardar un modelo basado en la entrada del usuario. Se pregunta al usuario para proporcionar la fecha del modelo, el número de unidades a producir y el número mínimo y máximo de horas por empleado. A continuación, se utiliza estos datos para crear el modelo. El modelo es resuelto y, a continuación, se guarda con la entrada del usuario.

El código siguiente describe la macro New_Employee_Schedule :
    Sub New_Employee_Schedule()    
' Prompt the user for the date of the model, the units to produce,
' and the maximum and minimum number of hours per employee.
ModelDate = Application.InputBox( _
Prompt:="Date of Model:", Type:=2)
Units = Application.InputBox( _
Prompt:="Projected Number of Units:", Type:=1)
MaxHrs = Application.InputBox( _
Prompt:="Maximum Number of Hours Per Employee:", Type:=1)
MinHrs = Application.InputBox( _
Prompt:="Minimum Number of Hours Per Employee:", Type:=1)

' Clear any previous Solver settings.
SolverReset

' Set the target cell, D12, to a minimum value by changing
' the range, C2:C8.
SolverOk SetCell:=Range("$D$12"), MaxMinVal:=2, _
ByChange:=Range("C2:C8")

' Add the constraint that number of hours worked <= MaxHrs.
SolverAdd CellRef:=Range("C2:C8"), Relation:=1, FormulaText:=MaxHrs

' Add the constraint that number of hours worked >=MinHrs.
SolverAdd CellRef:=Range("C2:C8"), Relation:=3, FormulaText:=MinHrs

' Add the constraint that number of units produced = Units.
SolverAdd CellRef:=Range("G12"), Relation:=2, FormulaText:=Units

' Solve the model and keep the final results.
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

' Save the input values for ModelDate, MaxHrs, MinHrs, and Units
' in columns I:L.
Set ModelRange = Range("I2:R2").CurrentRegion.Offset( _
Range("I2:R2").CurrentRegion.Rows.Count).Resize(1, 1)
ModelRange.Resize(1, 4) = Array("'" & Format(ModelDate, "m/d/yy"), _
Units, MaxHrs, MinHrs)

' Save the model parameters to the range M:R in the worksheet.
SolverSave SaveArea:=ModelRange.Offset(, 4).Resize(1, 6)

End Sub

Nota: En Microsoft Excel, versiones 5.0 y 7.0, utilice la notación F1C1 cuando se especifica una celda o rango de celdas con el argumento FormulaText . Por el contrario, en Microsoft Excel 97, utilice la notación de estilo A1 para especificar el argumento FormulaText .

La figura 9 muestra cómo aparece la información de modelo guardado en la hoja de cálculo.

La figura 9. Información sobre el modelo que se guarda la macro New_Employee_Schedule

Figure 9. Model information that is saved by the New_Employee_Schedule macro

La macro New_Employee_Schedule guarda cada nuevo modelo en la hoja de cálculo. La macro Load_Employee_Schedule puede cargar uno de estos modelos guardados. La macro solicita al usuario el modelo para cargar y, a continuación, busca la columna I para el modelo de fecha. Si se encuentra la fecha de modelo, la macro Load_Employee_Schedule carga el modelo correspondiente, lo resuelve y, a continuación, guarda los resultados finales.

El código siguiente describe la macro New_Employee_Schedule:
    Sub Load_Employee_Schedule()    
' Prompt for the date of the model.
ModelDate = Application.InputBox( _
Prompt:="Date of Model to Load:", Type:=2)

' Locate the date in column I.
Set DateRange = Range("I2").CurrentRegion.Resize(, 1)
r = Application.Match(ModelDate, DateRange, 0)

If IsError(r) Then
' Display a message if the model date is not found
MsgBox "Cannot find a model with the date " & ModelDate
Else
' If the model date is found, load the model into Solver,
' solve the model, and keep the final results.
SolverLoad LoadArea:=DateRange.Offset(r - 1, 4).Resize(1, 6)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End If

End Sub

La macro New_Employee_Schedule presenta la función SolverReset . La función SolverReset puede utilizarse para eliminar todas las selecciones de celda y las restricciones en el
Cuadro de diálogo Parámetros de Solver y para restablecer todas las configuraciones en el SolverReset función no tiene argumentos.

Volver al principio

Cómo encontrar más información acerca de Microsoft Excel Solver

Los recursos siguientes proporcionan información acerca de cómo utilizar el complemento Solver de Microsoft Excel.

  • Para obtener ayuda con mensajes específicos de solver, vea
    Frontline Systems.
  • Para obtener indicaciones sobre cómo generar legible, fácil de administrar modelos, vea
    Frontline Systems.
  • Para obtener información adicional acerca de Solver límites para las restricciones y, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

    75714 límites de solver para restricciones

  • Para varios ejemplos que utilizan el complemento Solver de Microsoft Excel en Microsoft Excel, consulte el archivo de ejemplo Solvsamp.xls.
  • Ésta es la ubicación predeterminada del archivo de ejemplo que se incluye con Microsoft Excel 97:
    \Program Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
  • Ésta es la ubicación predeterminada del archivo de ejemplo que se incluye con Microsoft Excel 7.0:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • Ésta es la ubicación predeterminada del archivo de ejemplo que se incluye con Microsoft Excel 5.0:
    \Excel\Examples\Solver\SolvSamp.xls
Volver al principio

Cómo obtener más información sobre el algoritmo y métodos utilizados por Solver de Microsoft Excel

Microsoft Excel Solver utiliza el código de optimización no lineal generalizado reducido degradado (GRG2) desarrollado por Leon Lasdon, University of Texas at Austin y Allan Waren, Cleveland State University.

Para obtener información adicional acerca del algoritmo que utiliza Microsoft Excel Solver, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

82890 utiliza solver generalizado reducida



Los problemas lineales y enteros utilizan el método más simple con límites en las variables y el método de ramificación y límite, implantado por John Watson y Dan Fylstra de Frontline Systems, Inc. Para obtener más información sobre el proceso de solución interno que utiliza Solver, póngase en contacto con:

Frontline Systems, Inc.P.O. Box 4288
Incline Village, NV 89450-4288
(702) 831-0300
Web site: http://www.frontsys.com
Electronic mail: info@frontsys.com



Selecciones del código de programa Microsoft Excel Solver son copyright 1990, 1991, 1992 y 1995 por Frontline Systems, Inc. partes son copyright 1989 de Optimal Methods, Inc.

Nota: El complemento de Microsoft Excel Solver que se describe en este artículo se proporciona "tal cual" y no garantizamos que puede utilizarse en todas las situaciones. Aunque los profesionales de soporte técnico de Microsoft pueden ayudar con la instalación y la funcionalidad de este complemento, no modificarán el complemento para ofrecer otra funcionalidad.


Ningún tipo de garantía. El software se proporciona "como-es," sin garantía de ningún tipo y cualquier uso de este software producto es bajo su propio riesgo.


Volver al principio
Propiedades

Id. de artículo: 843304 - Última revisión: 17 ene. 2017 - Revisión: 1

Comentarios