cómo crear macros de vb utilizando solver de excel

8/17/2019 Cómo Crear Macros de VB Utilizando Solver de Excel

http://slidepdf.com/reader/full/como-crear-macros-de-vb-utilizando-solver-de-excel 1/12

Id. de artículo : 843304Última revisión : miércoles, 11 de octubre de 2006Versión : 1.2

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 Advertencia: Artículo deTraducción Automática, vea la exención deresponsabilidad.En esta página

ResumenINTRODUCCIÓNMás informaciónDescripción del Microsoft Excel SolverCómo utilizar las funciones Microsoft Excel Solver en una macro

de VBACómo diseñar una macro de VBA que crea y que resuelve un modelo sencillo Microsoft Excel SolverLa función SolverOKLa función SolverSolveSolverFinish funciona

Cómo generar informa para solucionesCómo utilizar las funciones Microsoft ExcelSolver en una macro de bucleCómo trabajar con restriccionesCómo cambiar y eliminar restriccionesCómo cargar y guardar sus modelosCómo encontrar más información acerca de Microsoft Excel SolverCómo aprender más acerca del algoritmo y los métodos utilizados por Microsoft Excel Solver

ResumenEste 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 conrestricciones 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ÓNEste Este artículo contiene información acerca de Microsoft Excel Solver.

Más información

Descripción del Microsoft Excel SolverMicrosoft Excel Solver es ayudarle a determinar el valor óptimo de una fórmula de una celda objetiva determinada en unahoja de cálculo de Microsoft Excel. Microsoft Excel Solver ajusta los valores de otras celdas relacionadas con la celda objetivautilizando una ecuación. Después de que construye una ecuación y después de que define un conjunto de parámetros orestricciones para las variables en la ecuación, Microsoft Excel Solver prueba varias soluciones para llegar a una respuestaque 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, queestará 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 celdaobjetiva. Estas celdas son cambiadas por Microsoft Excel Solver para encontrar la solución óptima para la celdaobjetiva.

crear macros de Visual Basic utilizando Solver de Excel en Excel 97 http://support.microsoft.com/k

2 19/08/2008 0

• Restricciones restricciones son restricciones de celdas en el contenido. Por ejemplo, una celda en un modelo de hojade cálculo puede estar restringida a valores enteros mientras otra celda puede estar restringida al ser menor que unvalor dado.

Puede automatizar la creación y la manipulación de modelos Microsoft Excel Solver utilizando una macro de Microsoft VisualBasic para Aplicaciones ( VBA ). En este artículo se describe cómo utilizar el lenguaje de macros de VBA para utilizar lasfunciones Microsoft Excel Solver en Microsoft Excel 97. Este artículo presupone que conoce el lenguaje de VBA y el Editor deMicrosoft Visual Basic para Microsoft Excel 97. Los ejemplos que se utilizan en este artículo están disponibles para descargaen el sitio Web de Microsoft siguiente: http://download.microsoft.com/download/excel97win/solverex/1.0/WIN98Me /EN-US/SolverEx.exe (ht tp://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 VBAPara utilizar las funciones de complemento Microsoft Excel Solver en una macro de VBA, debe hacer referencia alcomplemento del proyecto de VBA del libro que contiene las macros. Si no hace referencia al complemento Microsoft ExcelSolver, recibirá el error siguiente de compilación cuando intenta ejecutar la macro: Error de compilación: Sub oFuncione de no estar definido. Para hacer referencia al complemento Microsoft Excel Solver para macros de su libro,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 acontinuació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álogoAgregar referencia, busque, seleccione el archivo Solver.xla y a continuación, haga clic en Abrir. El archivoSolver.xla se encuentra por lo general en la subcarpeta Office\Office\Library\Solver de Programa\Microsoft deC:\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 SolverAunque Microsoft Excel Solver ofrece muchas funciones, las tres funciones siguientes son fundamentales a crear y resolverun 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 primerafunción que utilizará para crear su modelo Microsoft Excel Solver. La función SolverOK es equivalente a hacer clic en Solveren 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 MaxMinValValueOf) 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 SolverOKSolverOK


2 19/08/2008 0

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 dela 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 comoTRUE. Para devolver los resultados y mostrar el cuadro de diálogo Resultados de Solver, establezca este argumentocomo FALSA

• ShowRef identifica a la macro que se llama cuando Microsoft Excel Solver devuelve una solución intermedia.

El argumento ShowRefShowRef se debería utilizar cuando TRUE sólo se pasa al argumentoStepThruStepThru 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 siSolverFinish 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 semantienen en las celdas cambiantes para reemplazar los valores. Si KeepFinal es 2, los valores finales de solución sedescartan y los valores anteriores se restauran.

• ReportArray especifica una matriz que indica el tipo de informe que creará Microsoft Excel cuando se alcanza lasolución. Si ReportArray se establece en 1, Microsoft Excel crea un Informe de Respuesta. Si el conjunto a 2 Excelde Microsoft crea un Informe de Sensibilidad y el conjunto a 3 Microsoft Excel crea un Informe de Limitar. "Para másinformación acerca de estos informes, vea" Cómo generar informa para soluciones"sección."

Figura 2. Microsoft Excel Solver resulta opciones asociadas a argumentos SolverFinishSolverFinish

En este artículo se describe cómo crear un modelo sencillo Microsoft Excel Solver de forma interactiva. El primer paso escrear su hoja de cálculo para el modelo. La hoja de cálculo contendrá algunas celdas de datos y al menos una celda que


2 19/08/2008 0

contiene una fórmula. Esta fórmula depende de las otras celdas de la hoja de cálculo. Después de configurar su hoja decálculo, haga clic en Solver en el menú Herramientas. En el cuadro de diálogo Parámetros de Solver, especifique la celdaobjetiva, el valor para el que está resolviendo, el intervalo de celdas que se modificarán y las restricciones. Haga clic enResolver para iniciar el proceso de solución. Después de haber encontrado una solución, los resultados aparecen en su hojade cálculo y el Microsoft Excel Solver muestra un cuadro de mensaje que se les pide si desean conservar los resultadosfinales 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 unvalor de 50. Es decir, busque la raíz cuadrada de 50. No hay restricciones en el modelo de Raíz cuadrada. La macro FindSquare 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ódigosiguiente 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 ' Finishand 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, lamacro Find Square Root2 le pide el valor que desea solucionarse a usted para la celda objetiva. Después de que introduzcaun valor, la macro Find Square Root2 establece este parámetro en el valor del argumento SolverOK valueofvalueof,soluciona el problema, guarda los resultados en la raíz cuadrada variable, descarta la solución y restaura a continuación elvalor de la hoja de cálculo a su estado original. La macro Find Square Root2 básicamente ilustra cómo puede guardar losresultados 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 squareroot. val = Application.InputBox( _ prompt:="Please enter the value for which you want " & _ "to find thesquare 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 ' theresults. sqroot = Range("a1") ' Finish and discard the results. SolverFinish KeepFinal:=2 ' Show theresult in a message box. MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00") End Sub

Cómo generar informa para solucionesMicrosoft Excel Solver ofrece que varios tipos de informes que describen cómo cambiaron los resultados y cómo cierran lasdelimitaciones llegaron a sus valores críticos. Cada informe se coloca de una hoja de cálculo independiente de su libro. Éstossiguientes son los tipos de informes que se ofrece el Microsoft Excel Solver:


2 19/08/2008 0

• Answer Report Las Respuesta Informe muestra la celda objetiva y las celdas cambiantes junto con sus valorescorrespondientes 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 acambios 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, loslímites inferiores y superiores y los valores de destino.

Para crear informes para sus modelos, especifique una matriz de valores para el argumentoReportArrayReportArray de la función SolverFinish. " " para más información acerca del argumento

ReportArrayReportArray, ve SolverFinish (KeepFinal, ReportArray) "sección." Por ejemplo, si desea generar unInforme de Limitar para el modelo que la macro Find Square Root2 crea y que resuelve, modifique la función SolverFinish enla 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 bucleEn 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 CreateSquare 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 unaiteració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 conel 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 thevalue 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 number10. For i = 1 To 10 ' Set the Solver parameters that indicate that Solver should ' solve the cell C2 forthe value of i (where i is the number ' of the iteration) by changing cell C1. SolverOkSetCell:=Range("C2"), ByChange:=Range("C1"), _ MaxMinVal:=3, ValueOf:=i ' Do not display the SolverResults 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 finalresults. 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 restriccionesUna restricción es una restricción de una o más celdas en el contenido. Un modelo puede tener una o varias restricciones. Elconjunto de restricción es un conjunto de desigualdades o un conjunto de igualdades que quitan combinacionesdeterminadas de valores de las variables de decisión de la solución. Por ejemplo, una restricción puede requerir que unacelda 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 larestricció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


2 19/08/2008 0

Para ejemplo si una empresa fabrica TV, estéreos y altavoces y si utiliza un inventario común de piezas de fuentes dealimentación, cónicos de altavoz y etc. Las partes están en suministro limitado. Su objetivo es determinar la combinaciónmás rentable de productos para generarlos. Su beneficio por unidad disminuye con volumen porque se requiere que losincentivos de precio adicional carguen el canal de distribución. El exponente diminishing returns es 0,9. Este exponente esutilizado 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 beneficiomáximo son el número de unidades que genera. El intervalo G9:G11 representa las celdas cambiantes en este modelo. Surestricción única es que el número de partes que utiliza no puede ser superior al número de partes que tiene a mano. ConMicrosoft Excel Solver, esta restricción aparece como E3:E7 < = B3:B7. Si se crearía interactivamente este modeloMicrosoft 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ónSolverAdd además de las funciones VBA de Microsoft Excel Solver que se describieron anteriormente. La función SolverAddagrega 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 ( < = ).

• El vaue 2 tiene (=).

• El valor 3 es mayor o igual ( > = ).

• El valor 4 es un entero.

• El valor 5 es el binary (un valor cero o uno).


2 19/08/2008 0

• FormulaText hace referencia a una o más celdas que forman el lado derecho del constraint.**

* Cuando especifica rango de celdas para el argumento FormulaTextFormulaText de la función SolverAdd, tengaen cuenta si la referencia es relativa o absoluta. Generalmente debe especificar una referencia absoluta para el argumentoFormulaTextFormulaText. Sin embargo, si especifica referencias relativas para el argumentoFormulaTextFormulaText, 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 elargumento FormulaTextFormulaText . En Microsoft Excel 97, utilice la notación de estilo A1 por contrario paraespecificar el argumento FormulaTextFormulaText .

Figura 7. Campos asociados a los argumentos SolverAddSolverAdd

La macro Maximum Profit que genera un modelo para el MIX de Producto con modelo Diminishing returns. Esta macroejecuta 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 ofprofits in cell G14 ' by changing the number of units to build in cells G9:I9. Solveroksetcell:=Range("G14"), maxminval:=1, _ bychange:=Range("G9:I9") ' Add the constraint for the model. Theonly constraint is that the ' number of parts used does not exceed the parts on hand-- ' E3:E7<=B3:B7SolverAdd CellRef:=Range("E3:E7"), Relation:=1, _ FormulaText:="$B$3:$B$7" ' Do not display the SolverResults dialog box. SolverSolve UserFinish:=True ' Finish and keep the final results. SolverFinishKeepFinal:=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 conel argumento FormulaTextFormulaText . En Microsoft Excel 97, utilice la notación de estilo A1 por contrario paraespecificar el argumento FormulaTextFormulaText .

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 restriccionesLas restricciones en su modelo se pueden cambiar o se pueden eliminar mediante programación. Las restricciones sonidentificadas por sus argumentos CellRefCellRef y RelationRelation.

Para cambiar una restricción existente mediante programación, utilice la función SolverChange. A continuación, se muestrala sintaxis para la función SolverChange: SolverChange (CellRef relación FormulaText) Tenga en cuenta que losargumentos 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 alnúmero de piezas es a mano ( E3:E7 < = B3:B7 ). Si desea cambiar esta delimitación de manera que el número de partesutilizadas es o igual al número de número de partes disponibles de piezas proyectado además número de partes ordenadasmenor. 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.


2 19/08/2008 0

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 CellRefCellRef y argumentos RelationRelation, sólopuede cambiar el argumento FormulaTextFormulaText para la restricción utilizando la función SolverChange. Si elCellRef 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 lasintaxis 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 lasfunciones SolverChange.

La macro siguiente muestra cómo eliminar y agregar una restricción. En este ejemplo, la macro Change Constraint andSolve2 quita la delimitación E3:E7 < = B3:B7 del MIX de Producto con modelo Diminishing returns y agrega una restricciónnueva. La restricción nueva sólo es una modificación de la restricción original dónde se invierten la izquierda y los ladosderechos 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 theconstraint 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. SolverSolveUserFinish:=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 conel argumento FormulaTextFormulaText. En Microsoft Excel 97, utilice la notación de estilo A1 por contrario paraespecificar el argumento FormulaTextFormulaText.

Cómo cargar y guardar sus modelosSi guarda su libro, los últimos parámetros que especificó en el cuadro de diálogo Parámetros de Solver se guardan con ellibro. 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 queintroduce en el Solver Parameter y en los cuadros de diálogo Opciones de Solver. Porque sólo el último problema se guardacon la hoja de cálculo, perderá todos los otros problemas a menos que los guarde explícitamente. Para guardarlos, haga clicen Guardar modelo en el cuadro de diálogo Opciones de Solver. Cuando desea restaurar los parámetros previamenteguardados, 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 lacelda objetiva. La segunda celda del rango contiene la fórmula que identifica las celdas cambiantes en el modelo. La últimacelda 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. Enesta tabla muestra cada tasa employeeÆs de pago, del número de horas programadas para ellos y de Número de unidadesproyectados que puede producir cada empleado en una hora por hora. Su objetivo es cumplir una cuota específica para elnúmero de unidades generado hasta que las unidades reducen el costo de trabajo.

Figura 8. Modo de empleado Programaciónl


2 19/08/2008 0

Dos factores adicionales (dos restricciones adicionales) se debe tener en cuenta si es el número minimum/maximum dehoras cuando puede trabajar cualquier un empleado y el número de unidades que desea que las unidades generen. Sidurante 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 $ : < c de 8 $ de $ = 45 c de 2 $ Las horas máximas por empleado son 45.

$ : > 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 formasemanal 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 sintaxissiguiente:

SolverSave (SaveArea)

SolverLoad (LoadArea)

El SolverSave y cada una de las funciones SolverLoad tienen respectivamente sólo argumento, unSaveAreaSaveArea y los argumentos LoadAreaLoadArea. Estos argumentos especifican un intervalo enuna 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 deusuario. Al usuario se le pide que proporcione la fecha del modelo, del número de unidades que las unidades generan y delnúmero de horas por empleado mínimo y máximo. Estos datos se utilizan a continuación para crear el modelo. Se resuelveel 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 themaximum and minimum number of hours per employee. ModelDate = Application.InputBox( _ Prompt:="Date ofModel:", 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 previousSolver 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 constraintthat number of hours worked <= MaxHrs. SolverAdd CellRef:=Range("C2:C8"), Relation:=1,FormulaText:=MaxHrs ' Add the constraint that number of hours worked >=MinHrs. SolverAddCellRef:=Range("C2:C8"), Relation:=3, FormulaText:=MinHrs ' Add the constraint that number of unitsproduced = Units. SolverAdd CellRef:=Range("G12"), Relation:=2, FormulaText:=Units ' Solve the model andkeep the final results. SolverSolve UserFinish:=True SolverFinish KeepFinal:=1 ' Save the input values


2 19/08/2008 0

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 themodel 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 conel argumento FormulaTextFormulaText . En Microsoft Excel 97, utilice la notación de estilo A1 por contrario paraespecificar el argumento FormulaTextFormulaText .

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 puedecargar uno de estos modelos guardados. La macro pide que cargue al usuario para el modelo la macro busca en columna yopara la fecha de modelo de base de datos. Si se encuentra la fecha de modelo de base de datos, la macro Load EmployeeSchedule 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 " & ModelDateElse ' If the model date is found, load the model into Solver, ' solve the model, and keep the finalresults. SolverLoad LoadArea:=DateRange.Offset(r - 1, 4).Resize(1, 6) SolverSolve UserFinish:=TrueSolverFinish KeepFinal:=1 End If End Sub

La macro New Employee Schedule incorpora la función SolverReset. La función SolverReset se puede utilizar para eliminartodas las selecciones de celdas y restricciones en el cuadro de diálogo Parámetros de Solver y restablecer todos los valoresen la función SolverReset no tiene ningún argumento.

Cómo encontrar más información acerca de Microsoft Excel SolverLos 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 siguientepara ver el artículo en el Microsoft Knowledge Base: 75714 (http://support.microsoft.com/kb/75714/) Límites quesuelve para restricciones

• 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 SolverMicrosoft Excel Solver utiliza el código de optimización no lineal Generalizar Reducir Degradado (GRG2) que fue desarrolladopor Leon Lasdon Universidad Leon Lasdon de Austin Texas en Austin y Allan Waren Universidad Allan Waren Cleveland.


12 19/08/2008 0

Ayuda y soporte

Para información adicional del algoritmo utilizado por Microsoft Excel Solver, haga clic en el número de artículo siguientepara ver el artículo en el Microsoft Knowledge Base: 82890 (http://support.microsoft.com/kb/82890/) Los usos que suelvegeneralizaron reducidos

Los problemas Lineal y entero utilizan el método simplex con límites en las variables y el método branch-and-boundimplementado por John Watson y Dan Fylstra de Frontline Systems , Inc. Para más información acerca del proceso desolución interna utilizado Solver, consulte:

Frontline Systems , Inc. : : [email protected] : : [email protected] P.O. Box 4288 Incline Village, NV 89450-4288 <FIXEDTEXT> 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 SistemasFrontline, 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 garantizamosque se puede utilizar en todas las situaciones. Si bien los profesionales de soporte técnico de Microsoft pueden ayudar con lainstalació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, elproducto está en su propio peligro.

La información de este artículo se refiere a:

• Microsoft Excel 97 Standard EditionPalabras 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 unsistema informático sin ayuda humana (Machine Translation). Microsoft ofrece estos artículos a los usuariosque no comprendan el inglés, exclusivamente, con el fin de que puedan entenderlos más fácilmente. Microsoftno se hace responsable de la calidad lingüística de las traducciones ni de la calidad técnica de los contenidosde los artículos así como tampoco de cualesquiera problemas, directos o indirectos, que pudieran surgir comoconsecuencia 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é

Totalmentede acuerdo

Totalmenteen

desacuerdo9 8 7 6 5 4 3 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.


12 19/08/2008 0

cómo crear macros de vb utilizando solver de excel

Documents