1. Internet

Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
1 de 12
http://support.microsoft.com/kb/843304
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
Ver los productos a los que se aplica este artículo
responsabilidad.
Advertencia: Artículo deTraducción Automática, vea la exención de
En esta página
Id. de artículo : 843304
Resumen
Última revisión : miércoles, 11 de octubre de 2006
INTRODUCCIÓN
Más información
Versión
: 1.2
Descripción del Microsoft Excel Solver
Cómo utilizar las funciones Microsoft Excel Solver en una macro
de VBA
Cómo diseñar una macro de VBA que crea y que resuelve un modelo sencillo Microsoft Excel Solver
La función SolverOK
La función SolverSolve
SolverFinish funciona
Cómo generar informa para soluciones
Cómo utilizar las funciones Microsoft ExcelSolver en una macro de bucle
Cómo trabajar con restricciones
Cómo cambiar y eliminar restricciones
Cómo cargar y guardar sus modelos
Cómo encontrar más información acerca de Microsoft Excel Solver
Cómo aprender más acerca del algoritmo y los métodos utilizados por Microsoft Excel Solver
Resumen
Este artículo presente 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.
Este Este artículo además contiene información sobre cómo crear macros, cómo diseñar una macro y cómo trabajar con
restricciones de una macro. También en este artículo se describen el algoritmo y los métodos utilizados por Microsoft
Excel Solver. En la lista siguiente se enumeran todos los temas que se tratan en el artículo.
• Descripción del Microsoft Excel Solver
• Cómo utilizar las funciones Microsoft Excel Solver en una macro de VBA
• Cómo diseñar una macro de VBA que crea y que resuelve un modelo sencillo Microsoft Excel Solver
• Cómo generar informa para soluciones
• Cómo utilizar las funciones Microsoft Excel Solver en una macro de bucle
• Cómo trabajar con restricciones
• Cómo cambiar y eliminar restricciones
• Cómo cargar y guardar sus modelos
• Cómo encontrar más información acerca de Microsoft Excel Solver
• Cómo aprender más acerca del algoritmo y los métodos utilizados por Microsoft Excel Solver
INTRODUCCIÓN
Este Este artículo contiene información acerca de Microsoft Excel Solver.
Más información
Descripción del Microsoft Excel Solver
Microsoft Excel Solver es ayudarle a determinar el valor óptimo de una fórmula de una celda objetiva determinada en una
hoja de cálculo de Microsoft Excel. Microsoft Excel Solver ajusta los valores de otras celdas relacionadas con la celda objetiva
utilizando una ecuación. Después de que construye una ecuación y después de que define un conjunto de parámetros o
restricciones para las variables en la ecuación, Microsoft Excel Solver prueba varias soluciones para llegar a una respuesta
que cumple todas las restricciones. Microsoft Excel Solver utiliza los elementos siguientes para "solucionar" una ecuación:
• Target cell, la celda objetiva, es el objetivo. Es la celda en el modelo de hoja de cálculo que estará minimizado, que
estará maximizado o que se establecerá en un valor determinado.
• Celdas cambiantes Celdas cambiantes son las variables de decisión. Estas celdas afectan al valor de la celda
objetiva. Estas celdas son cambiadas por Microsoft Excel Solver para encontrar la solución óptima para la celda
objetiva.
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
2 de 12
http://support.microsoft.com/kb/843304
• Restricciones restricciones son restricciones de celdas en el contenido. Por ejemplo, una celda en un modelo de hoja
de cálculo puede estar restringida a valores enteros mientras otra celda puede estar restringida al ser menor que un
valor dado.
Puede automatizar la creación y la manipulación de modelos Microsoft Excel Solver utilizando una macro de Microsoft Visual
Basic para Aplicaciones ( VBA ). En este artículo se describe cómo utilizar el lenguaje de macros de VBA para utilizar las
funciones Microsoft Excel Solver en Microsoft Excel 97. Este artículo presupone que conoce el lenguaje de 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 descarga
en el sitio Web de Microsoft siguiente:
http://download.microsoft.com/download/excel97win/solverex/1.0/WIN98Me
/EN-US/SolverEx.exe (http://download.microsoft.com/download/excel97win/solverex/1.0/win98me/en-us/solverex.exe)
Nota También
puede utilizar las macros y los ejemplos que se describen en este artículo en versiones de Microsoft Excel 5.0 y 7.0.
Cómo utilizar las funciones Microsoft Excel Solver en una macro de VBA
Para utilizar las funciones de complemento Microsoft Excel Solver en una macro de VBA, debe hacer referencia al
complemento del proyecto de VBA del libro que contiene las macros. Si no hace referencia al complemento Microsoft Excel
Solver, recibirá el error siguiente de compilación cuando intenta ejecutar la macro:
Error de compilación: Sub o
Para hacer referencia al complemento Microsoft Excel Solver para macros de su libro,
Funcione de no estar definido.
utilice los pasos siguientes:
1. Abra su libro.
2. En el menú Herramientas, elija Macro y 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 seleccionar la casilla de verificación Activar Solver.xls y a
continuación, haga clic en Aceptar.
Nota Si no ve Solver.xls en la lista Referencias disponibles, haga clic en Examinar. En el cuadro de diálogo
Agregar referencia, busque, seleccione el archivo Solver.xla y a continuación, haga clic en Abrir. El archivo
Solver.xla se encuentra por lo general en la subcarpeta Office\Office\Library\Solver de Programa\Microsoft de
C:\Program.
Está ahora preparado por usted para utilizar las funciones Microsoft Excel Solver en una macro de VBA.
Cómo diseñar una macro de VBA que crea y que resuelve un modelo sencillo Microsoft Excel Solver
Aunque Microsoft Excel Solver ofrece muchas funciones, las tres funciones siguientes son fundamentales a crear y resolver
un modelo:
• La función SolverOK
• La función SolverSolve
• SolverFinish funciona
La función SolverOK
La función SolverOK define un modelo básico Microsoft Excel Solver. La función SolverOK generalmente es la primera
función que utilizará para crear su modelo Microsoft Excel Solver. La función SolverOK es equivalente a hacer clic en Solver
en el menú Herramientas y a continuación, especificar las opciones que están en el cuadro de diálogo Parámetros de Solver.
A continuación, se muestra la sintaxis para la función SolverOK:
SolverOK (SetCell ByChange MaxMinVal
ValueOf)
La información siguiente describe la sintaxis de la función SolverOK:
• SetCell especifica la celda objetiva.
• MaxMinVal corresponde para que lo pase si se desea que se solucione la celda objetiva para un valor máximo (1), un
valor mínimo (2) o un valor específico (3) a usted.
• ValueOf especifica el valor al que se coincide con la celda objetiva. Si establece MaxMinVal a 3, debe especificar
este argumento. Si establece MaxMinVal a 1 o 2, puede omitir este argumento.
• ByChange especifica la celda o rango de las celdas que se cambiarán.
Figura 1 asocia los argumentos de la función SolverOK a los parámetros en el cuadro de diálogo Parámetros de Solver.
Figura 1. Parámetros asociados a los argumentos <B>SolverOK</B>SolverOK
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
3 de 12
http://support.microsoft.com/kb/843304
La función SolverSolve
La función SolverSolve resuelve el modelo que utiliza los parámetros que especificó con la función SolverOK. La ejecución de
la función SolverSolve es equivalente a hacer clic en Resolver en el cuadro de diálogo Parámetros de Solver. A continuación,
se muestra la sintaxis para la función SolverSolve:
SolverSolve (UserFinish, ShowRef)
La información siguiente
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 como
TRUE. Para devolver los resultados y mostrar el cuadro de diálogo Resultados de Solver, establezca este argumento
como FALSA
• ShowRef identifica a la macro que se llama cuando Microsoft Excel Solver devuelve una solución intermedia.
El argumento <B>ShowRef</B>ShowRef se debería utilizar cuando TRUE sólo se pasa al argumento
<B>StepThru</B>StepThru de la función SolverOptions.
SolverFinish funciona
Indica qué hacer con los resultados y tipo de informe de crear después de que se completa el proceso de solución si
SolverFinish funciona. A continuación, se muestra la sintaxis para la función SolverFinish:
SolverFinish (KeepFinal,
ReportArray)
La información siguiente describe la sintaxis de la función SolverFinish:
• KeepFinal indica lo que hacer con los resultados finales. Si KeepFinal es 1, los valores finales de solución se
mantienen en las celdas cambiantes para reemplazar los valores. Si KeepFinal es 2, los valores finales de solución se
descartan y los valores anteriores se restauran.
• ReportArray especifica una matriz que indica el tipo de informe que creará Microsoft Excel cuando se alcanza la
solución. Si ReportArray se establece en 1, Microsoft Excel crea un Informe de Respuesta. Si el conjunto a 2 Excel
de Microsoft crea un Informe de Sensibilidad y el conjunto a 3 Microsoft Excel crea un Informe de Limitar. "Para más
información acerca de estos informes, vea" Cómo generar informa para soluciones"sección."
Figura 2. Microsoft Excel Solver resulta opciones asociadas a argumentos <B>SolverFinish</B>SolverFinish
En este artículo se describe cómo crear un modelo sencillo Microsoft Excel Solver de forma interactiva. El primer paso es
crear su hoja de cálculo para el modelo. La hoja de cálculo contendrá algunas celdas de datos y al menos una celda que
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
4 de 12
http://support.microsoft.com/kb/843304
contiene una fórmula. Esta fórmula depende de las otras celdas de la hoja de cálculo. Después de configurar su 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
objetiva, el valor para el que está resolviendo, el intervalo de celdas que se modificarán y las restricciones. Haga clic en
Resolver para iniciar el proceso de solución. Después de haber encontrado una solución, los resultados aparecen en su hoja
de cálculo y el Microsoft Excel Solver muestra un cuadro de mensaje que se les pide si desean conservar los resultados
finales o si desean descartarlos. Cuando hace clic en una de estas opciones, Microsoft Excel Solver finaliza.
Figura 3 muestra un modelo sencillo que puede crear utilizando estos pasos.
Figura 3. Un modelo sencillo: Modelo Las Square root
En este ejemplo, celda de cambio A1, que contiene la fórmula, = A1 ^ 2 a un valor que realizará celda A2 son iguales a un
valor de 50. Es decir, busque la raíz cuadrada de 50. No hay restricciones en el modelo de Raíz cuadrada. La macro Find
Square Root realiza las tareas siguientes:
• Configura un modelo que solucionará el valor de celda A2 para un valor de 50 cambiando el valor de celda A1.
• Resuelve el modelo.
• Guarda los resultados finales a la hoja de cálculo sin mostrar el cuadro de diálogo Resultados de Solver.
Esta macro sencilla crea un modelo Microsoft Excel Solver y lo resuelve sin intervención de ningún 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 que desea solucionarse a usted para la celda objetiva. Después de que introduzca
un valor, la macro Find Square Root2 establece este parámetro en el valor del argumento SolverOK <I>valueof</I>valueof,
soluciona el problema, guarda los resultados en la raíz cuadrada variable, descarta la solución y restaura a continuación el
valor de la hoja de cálculo a su estado original. La macro Find Square Root2 básicamente ilustra cómo puede guardar los
resultados en una o más variables y 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
Cómo generar informa para soluciones
Microsoft Excel Solver ofrece que varios tipos de informes que describen cómo cambiaron los resultados y cómo cierran las
delimitaciones llegaron a sus valores críticos. Cada informe se coloca de una hoja de cálculo independiente de su libro. Éstos
siguientes son los tipos de informes que se ofrece el Microsoft Excel Solver:
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
5 de 12
http://support.microsoft.com/kb/843304
• Answer Report Las Respuesta Informe muestra la celda objetiva y las celdas cambiantes junto con sus valores
correspondientes originales y finales, sus restricciones y su información acerca de las restricciones.
• Sensitivity Report Las Sensibilidad Informe proporciona información acerca de cuán confidencial es la solución a
cambios pequeños en la fórmula de la celda objetiva.
• Limits Report Las Limitar Informe muestra la celda objetiva y las celdas cambiantes con sus valores respectivos, los
límites inferiores y superiores y los valores de destino.
Para crear informes para sus modelos, especifique una matriz de valores para el argumento
<B>ReportArray</B>ReportArray de la función SolverFinish. " " para más información acerca del argumento
<B>ReportArray</B>ReportArray, ve SolverFinish (KeepFinal, ReportArray) "sección." Por ejemplo, si desea generar un
Informe de Limitar para el modelo que la macro Find Square Root2 crea y que resuelve, modifique la función SolverFinish en
la macro para que sea similar al código siguiente de ejemplo:
SolverFinish KeepFinal:=2, ReportArray:= Array(3)
Para generar varios informes, modifique la función SolverFinish para que sea similar al código de ejemplo siguiente:
SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)
Cómo utilizar las funciones Microsoft ExcelSolver en una macro de bucle
En muchas situaciones, es conveniente tener que se soluciona la celda objetiva para varios valores a Microsoft Excel Solver.
Generalmente puede realizar esto utilizando 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 de bucle. La macro Create
Square Root Table crea una tabla en una hoja de cálculo nueva. Inserta los números uno entre diez y la raíz cuadrada
correspondiente de cada número. La macro Create Square Root Table crea la tabla que usa un bucle For para establecer una
iteración en los número 1 a 10 y solucionar la celda objetiva en el modelo de Raíz cuadrada para 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 ilustrada en figura 4.
Figura 4. Resultado generado por la macro Create Square Root Table
Cómo trabajar con restricciones
Una restricción es una restricción de una o más celdas en el contenido. Un modelo puede tener una o varias restricciones. El
conjunto de restricción es un conjunto de desigualdades o un conjunto de igualdades que quitan combinaciones
determinadas de valores de las variables de decisión de la solución. Por ejemplo, una restricción puede requerir que una
celda es mayor que cero y que aquella otra celda contiene sólo un valor entero.
El modelo de Raíz cuadrada que hemos descrito hasta este momento es un modelo sencillo que no contiene toda la
restricción. Figura 5 muestra un modelo que utiliza restricciones. El propósito de este modelo es encontrar la combinación
óptima de productos para beneficio máximo.
Figura 5. Producto se mezcla con reducir margen de beneficio
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
6 de 12
http://support.microsoft.com/kb/843304
Para ejemplo si una empresa fabrica TV, estéreos y altavoces y si utiliza un inventario común de piezas de fuentes de
alimentación, cónicos de altavoz y etc. Las partes están en suministro limitado. Su objetivo es determinar la combinación
más rentable de productos para generarlos. Su beneficio por unidad disminuye con volumen porque se requiere que los
incentivos de precio adicional carguen el canal de distribución. El exponente diminishing returns es 0,9. Este exponente es
utilizado por producto del rango G11:I11 para calcular el beneficio.
Su objetivo es buscar el beneficio máximo (celda G14). Los valores de cambiar para buscar a los valores el beneficio
máximo son el número de unidades que genera. El intervalo G9:G11 representa las celdas cambiantes en este modelo. Su
restricción única es que el número de partes que utiliza no puede ser superior al número de partes que tiene a mano. Con
Microsoft Excel Solver, esta restricción aparece como E3:E7 &lt; = B3:B7. Si se crearía interactivamente este modelo
Microsoft Excel Solver, los parámetros Microsoft Excel Solver serán similares a aquéllos que se encuentran en figura 6.
Figura 6. Los parámetros Microsoft Excel Solver del producto se mezclan con modelo Reducir Margen de beneficio
Para crear y resolver el MIX de Producto con modelo Reducir Margen de beneficio, utilizará una función nueva, la función
SolverAdd además de las funciones VBA de Microsoft Excel Solver que se describieron anteriormente. La función SolverAdd
agrega la restricción al modelo. La ejecución de la función SolverAdd es equivalente a hacer clic en el botón Agregar del
cuadro de diálogo Parámetros de Solver. La función SolverAdd tiene la sintaxis siguiente:
SolverAdd (CellRef
relación FormulaText)
La información siguiente 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.
• Relation es la relación aritmética entre la izquierda y los lados derechos de una restricción.
• Relation puede ser un valor entre 1 y 5 como el ejemplo siguiente:
• El valor 1 es o igual para menor ( &lt; = ).
• El vaue 2 tiene (=).
• El valor 3 es mayor o igual ( &gt; = ).
• El valor 4 es un entero.
• El valor 5 es el binary (un valor cero o uno).
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
7 de 12
http://support.microsoft.com/kb/843304
• FormulaText hace referencia a una o más celdas que forman el lado derecho del constraint.**
* Cuando especifica rango de celdas para el argumento <B>FormulaText</B>FormulaText de la función SolverAdd, tenga
en cuenta si la referencia es relativa o absoluta. Generalmente debe especificar una referencia absoluta para el argumento
<B>FormulaText</B>FormulaText. Sin embargo, si especifica referencias relativas para el argumento
<B>FormulaText</B>FormulaText, realice que la referencia estará respecto a la celda objetiva y no la celda activa.
Nota Microsoft Excel En versiones 5.0 y 7.0 de utilizar la notación R1C1 al especificar una celda o rango de celdas con el
argumento <B>FormulaText</B>FormulaText. En Microsoft Excel 97, utilice la notación de estilo A1 por contrario para
especificar el argumento <B>FormulaText</B>FormulaText.
Figura 7. Campos asociados a los argumentos <B>SolverAdd</B>SolverAdd
La macro Maximum Profit que genera un modelo para el MIX de Producto con modelo Diminishing returns. Esta macro
ejecuta las funciones o argumentos siguientes:
• Establece la función SolverOK la celda objetiva para un valor máximo y especifica las celdas que cambia.
• La función SolverAdd agrega la restricción al modelo.
• La función SolverSolve encuentra una solución sin mostrar el cuadro de diálogo Resultados de Solver.
• La función SolverFinish devuelve los resultados finales a la hoja de cálculo.
Se describe el código siguiente para 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 Microsoft Excel En versiones 5.0 y 7.0 de utilizar la notación R1C1 al especificar una celda o unos rango de celdas con
el argumento <B>FormulaText</B>FormulaText. En Microsoft Excel 97, utilice la notación de estilo A1 por contrario para
especificar el argumento <B>FormulaText</B>FormulaText.
Cuando ejecuta la macro Maximum Profit, Microsoft Excel Solver encontrará una solución de generar 160 conjuntos de TV,
200 estéreos y 80 altavoces de un beneficio máximo de 14.917 dólares de $.
Cómo cambiar y eliminar restricciones
Las restricciones en su modelo se pueden cambiar o se pueden eliminar mediante programación. Las restricciones son
identificadas por sus argumentos <B>CellRef</B>CellRef y <B>Relation</B>Relation.
Para cambiar una restricción existente mediante programación, utilice la función SolverChange. A continuación, se muestra
la sintaxis para la función SolverChange:
SolverChange (CellRef relación FormulaText)
Tenga en cuenta que los
argumentos de la función SolverChange son los mismos a aquéllos que utiliza con la función SolverAdd.
Si desea cambiar la delimitación en el MIX de Producto con modelo Diminishing returns, utilizará la función SolverChange.
Por ejemplo, la restricción que se especifica es aquél actualmente de que de partes utilizadas, numérico, menos o igual al
número de piezas es a mano ( E3:E7 < = B3:B7 ). Si desea cambiar esta delimitación de manera que el número de partes
utilizadas es o igual al número de número de partes disponibles de piezas proyectado además número de partes ordenadas
menor. Esta restricción nueva será similar a E3:E7 < = D3:D7. La macro siguiente cambiará la restricción existente E3:E7 <
= B3:B7 a E3:E7 < = D3:D7 y resuelve para una solución.
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
8 de 12
http://support.microsoft.com/kb/843304
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
Puesto que las restricciones son identificadas por el <B>CellRef</B>CellRef y argumentos <B>Relation</B>Relation, sólo
puede cambiar el argumento <B>FormulaText</B>FormulaText para la restricción utilizando la función SolverChange. Si el
CellRef y los valores Relation no coinciden con una restricción existente, debe eliminar la restricción y a continuación,
agregar la restricción modificada. Para eliminar una restricción, utilice la función SolverDelete. A continuación, se muestra la
sintaxis para la función SolverDelete:
SolverDelete (CellRef, Relation, FormulaText)
Tenga en cuenta que los argumentos de la función SolverDelete son los mismos a aquéllos que utiliza con el SolverAdd y las
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 delimitación E3:E7 < = B3:B7 del MIX de Producto con modelo Diminishing returns y agrega una restricción
nueva. La restricción nueva sólo es una modificación de la restricción original dónde se invierten la izquierda y los lados
derechos 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 Microsoft Excel En versiones 5.0 y 7.0 de utilizar la notación R1C1 al especificar una celda o unos rango de celdas con
el argumento <I>FormulaText</I>FormulaText. En Microsoft Excel 97, utilice la notación de estilo A1 por contrario para
especificar el argumento <I>FormulaText</I>FormulaText.
Cómo cargar y guardar sus modelos
Si guarda su libro, los últimos parámetros que especificó en el cuadro de diálogo Parámetros de Solver se guardan con el
libro. Por tanto, al abrir el libro, los parámetros son los mismos al cuándo guardar el libro por última vez.
Puede definir más de un problema de una hoja de cálculo. Cada problema se compone de celdas y restricciones que
introduce en el Solver Parameter y en los cuadros de diálogo Opciones de Solver. Porque sólo el último problema se guarda
con la hoja de cálculo, perderá todos los otros problemas a menos que los guarde explícitamente. Para guardarlos, haga clic
en Guardar modelo en el cuadro de diálogo Opciones de Solver. Cuando desea restaurar los parámetros previamente
guardados, haga clic de forma similar en Cargar modelo en el cuadro de diálogo Opciones de Solver.
Los modelos que suelve se almacenan en rango de celdas de una hoja. La primera celda del rango contiene la fórmula de la
celda objetiva. 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 configuradas en el cuadro de diálogo Opciones de Solver.
Las celdas entre la segunda celda y la última celda contienen las fórmulas que representan las restricciones en el modelo.
Figura 8 muestra un modelo para empleado de programación. Presuponga que se trabaja para un fabricante pequeño. En
esta tabla muestra cada tasa employeeÆs de pago, del número de horas programadas para ellos y de Número de unidades
proyectados que puede producir cada empleado en una hora por hora. Su objetivo es cumplir una cuota específica para el
número de unidades generado hasta que las unidades reducen el costo de trabajo.
Figura 8. Modo de empleado Programación
l
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
9 de 12
http://support.microsoft.com/kb/843304
Dos factores adicionales (dos restricciones adicionales) se debe tener en cuenta si es el número minimum/maximum de
horas cuando puede trabajar cualquier un empleado y el número de unidades que desea que las unidades generen. Si
durante una semana especificada, debe producir 3975 unidades y si desea que cada empleado trabaje entre 30 y 45 horas,
los parámetros Microsoft Excel Solver serán similares a aquéllos enumerados en la tabla siguiente:
Parámetro
Nombre o rango de celdas
Descripció
Celda objetiva
D DE $ 12 DE $
Costo de trabajo.
Celdas cambiantes $ c de 8 $ de $ c de 2 $
Funcionaron horas por empleado.
Restricciones
Las horas máximas por empleado son 45.
$ : &lt; c de 8 $ de $ = 45 c de 2 $
$ : &gt; c de 8 $ de $ = 30 c de 2 $ Las horas mínimas por empleado son 35.
SOL DE $ 12 = 3975 DE $
Número de unidades son 3975.
Sus objetivos es resolver para guarda cada modelo semanalmente y para que el costo de trabajo óptimo de una forma
semanal sea capaz en cargarse cualquier modelo semanal cuando lo necesita para que sea.
En una macro, se puede guardar los parámetros Microsoft Excel Solver para un modelo y se pueden cargar utilizando
respectivamente el SolverSave y las funciones SolverLoad. El SolverSave y las funciones SolverLoad tienen la sintaxis
siguiente:
SolverSave (SaveArea)
SolverLoad (LoadArea)
El SolverSave y cada una de las funciones SolverLoad tienen respectivamente sólo argumento, un
<B>SaveArea</B>SaveArea y los argumentos <B>LoadArea</B>LoadArea. Estos argumentos especifican un intervalo en
una hoja de cálculo donde se almacena la información de modelo.
La macro siguiente New Employee Schedule muestra cómo crear, solucionar y guardar un modelo basado en entrada de
usuario. Al usuario se le pide que proporcione la fecha del modelo, del número de unidades que las unidades generan y del
número de horas por empleado mínimo y máximo. Estos datos se utilizan a continuación para crear el modelo. Se resuelve
el modelo y se guarda con los datos proporcionados por 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
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
10 de 12
http://support.microsoft.com/kb/843304
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 Microsoft Excel En versiones 5.0 y 7.0 de utilizar la notación R1C1 al especificar una celda o unos rango de celdas con
el argumento <B>FormulaText</B>FormulaText. En Microsoft Excel 97, utilice la notación de estilo A1 por contrario para
especificar el argumento <B>FormulaText</B>FormulaText.
Figura 9 muestra cómo aparece la información de modelo guardado en la hoja de cálculo.
Figura 9. Información de modelo guardada por la macro New_Employee_Schedule
La macro New Employee Schedule guarda cada modelo nuevo a la hoja de cálculo. La macro Load Employee Schedule puede
cargar uno de estos modelos guardados. La macro pide que cargue al usuario para el modelo la macro busca en columna yo
para la fecha de modelo de base de datos. Si se encuentra la fecha de modelo de base de datos, la macro Load Employee
Schedule carga el modelo correspondiente, lo resuelve y a continuación, mantiene 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 incorpora la función SolverReset. La función SolverReset se puede utilizar para eliminar
todas las selecciones de celdas y restricciones en el cuadro de diálogo Parámetros de Solver y restablecer todos los valores
en la función SolverReset no tiene ningún argumento.
Cómo encontrar más información acerca de Microsoft Excel Solver
Los recursos siguientes contienen información acerca de cómo utilizar el complemento Microsoft Excel Solver.
• Para ayuda con mensajes que suelve específico, vea Frontline Systems (http://www.frontsys.com/solvmsgs.htm).
• Para sugerencias en generar legible, los modelos manejables ven Frontline Systems (http://www.frontsys.com
/solvmsgs.htm).
• Para información adicional acerca de Solver, limita para restricciones y hace clic en el número de artículo siguiente
para ver el artículo en el Microsoft Knowledge Base:
suelve para restricciones
75714 (http://support.microsoft.com/kb/75714/) Límites que
• Para varios ejemplos que utilizan el complemento Microsoft Excel Solver en Microsoft Excel, vea el archivo de ejemplo
Solvsamp.xls.
• La siguiente es la ubicación predeterminada del archivo de ejemplo incluido en Microsoft Excel 97:
\Program
Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
• La siguiente es la ubicación predeterminada del archivo de ejemplo incluido en Microsoft Excel 7.0:
\MSOffice
\Excel\Examples\Solver\SolvSamp.xls
• La siguiente es la ubicación predeterminada del archivo de ejemplo incluido en Microsoft Excel 5.0:
\Excel
\Examples\Solver\SolvSamp.xls
Cómo aprender más acerca del algoritmo y los métodos utilizados por Microsoft Excel Solver
Microsoft Excel Solver utiliza el código de optimización no lineal Generalizar Reducir Degradado (GRG2) que fue desarrollado
por Leon Lasdon Universidad Leon Lasdon de Austin Texas en Austin y Allan Waren Universidad Allan Waren Cleveland.
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
11 de 12
http://support.microsoft.com/kb/843304
Para información adicional del algoritmo utilizado por Microsoft Excel Solver, haga clic en el número de artículo siguiente
para ver el artículo en el Microsoft Knowledge Base:
82890 (http://support.microsoft.com/kb/82890/) Los usos que suelve
generalizaron reducidos
Los problemas Lineal y entero utilizan el método simplex con límites en las variables y el método branch-and-bound
implementado por John Watson y Dan Fylstra de Frontline Systems , Inc. Para más información acerca del proceso de
solución interna utilizado Solver, consulte:
Frontline Systems , Inc. : : [email protected] : : [email protected] P.O. Box 4288 Incline Village, NV 89450-4288 <FIXED
TEXT> sitio Web (702) 831-0300 http://www.frontsys.com Correo electrónico Sistemas Frontline </FIXED TEXT> Web de
Microsoft (702) 831-0300 P.O. Box 4288 Incline Village, NV 89450-4288 inc. http://www.frontsys.com Correo electrónico
Las selecciones del código de programa Microsoft Excel Solver son 1990, 1991, 1992 y 1995 de copyright por Sistemas
Frontline, Partes de Inc son 1989 de copyright por Optimal Methods , Inc.
Nota se lo proporciona el complemento Las Microsoft Excel Solver que se trata en este artículo tal cual y no garantizamos
que se puede utilizar en todas las situaciones. Si bien 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
NO WARRANTY. Se proporciona el software " como - es " sin garantía de ningún tipo ni ningún uso de este software, el
producto está en su propio peligro.
La información de este artículo se refiere a:
• Microsoft Excel 97 Standard Edition
Palabras clave: kberrmsg kbhowto kbinfo kbmacroexample kbaddin kbprogramming kbvba KB843304 KbMtes kbmt
AVISO: Gracias por utilizar el servicio de Traducción Automática. Este artículo ha sido traducido por un
sistema informático sin ayuda humana (Machine Translation). Microsoft ofrece estos artículos a los usuarios
que no comprendan el inglés, exclusivamente, con el fin de que puedan entenderlos más fácilmente. Microsoft
no se hace responsable de la calidad lingüística de las traducciones ni de la calidad técnica de los contenidos
de los artículos así como tampoco de cualesquiera problemas, directos o indirectos, que pudieran surgir como
consecuencia de su utilización por los lectores.Haga clic aquí para ver el artículo original (en inglés): 843304
(http://support.microsoft.com/kb/843304/en-us/)
Proporcione comentarios sobre este artículo
¿Le ayudó este artículo a solucionar su problema?
Sí
No
Parcialmente
Aún no lo sé
Totalmente
de acuerdo
9
8
7
6
5
4
3
Totalmente
en
desacuerdo
2
1
El artículo es fácil de entender
El artículo es exacto
Comentarios adicionales:
Para
proteger su privacidad, no incluya información de contacto en los comentarios.
Ayuda y soporte
19/08/2008 05:19 p.m.
Cómo crear macros de Visual Basic utilizando Solver de Excel en Excel 97
12 de 12
http://support.microsoft.com/kb/843304
©2008 Microsoft
19/08/2008 05:19 p.m.