Solver versus Macros

Transcrição

Solver versus Macros
Este artigo descreve como usar o Microsoft Excel Solver no Microsoft Excel 97 para criar as macros do Microsoft Visual
Basic. Microsoft Excel Solver é um suplemento do Microsoft Excel.
Além disso, este artigo contém informações sobre como criar macros, como criar uma macro e como trabalhar com
restrições de uma macro. Este artigo também descreve o algoritmo e métodos que são usados pelo Microsoft Excel
Solver. A seguinte lista apresenta todos os tópicos abordados no artigo.
•
Description of the Microsoft Excel Solver
•
How to use the Microsoft Excel Solver functions in a VBA macro
•
How to design a VBA macro that creates and solves a simple Microsoft Excel Solver model
•
How to generate reports for solutions
•
How to use the Microsoft Excel Solver functions in a looping macro
•
How to work with constraints
•
How to change and delete constraints
•
How to load and save your models
•
How to find more information about Microsoft Excel Solver
•
How to learn more about the algorithm and methods that are used by Microsoft Excel Solver
Voltar para o início
INTRODUÇÃO
Este artigo contém informações sobre o Microsoft Excel Solver.
Voltar para o início
Mais Informações
Descrição do Microsoft Excel Solver
Microsoft Excel Solver é que um Microsoft Excel add-in. Microsoft Excel Solver ajuda você a determinar o valor ideal
para uma fórmula em uma célula de destino específico em uma planilha do Microsoft Excel. Microsoft Excel Solver
ajusta os valores de outras células que estão relacionados à célula de destino usando uma equação. Depois de criar
uma equação e definem um conjunto de parâmetros ou restrições para as variáveis na equação, o Microsoft Excel
Solver tenta várias soluções para chegar a uma resposta que satisfaça todas as restrições. Microsoft Excel Solver usa os
seguintes elementos para "resolver" uma equação:
•
célula de destino - A célula de destino é o objetivo. Ele é a célula no modelo de planilha que serão
minimizada, maximizada ou definido com um valor.
•
células variáveis - alterar células são as variáveis de decisão. Essas células afetam o valor da célula de
destino. Essas células são alteradas pelo Microsoft Excel Solver para encontrar a solução ideal para a célula
de destino.
•
restrições - restrições são restrições sobre o conteúdo de células. Por exemplo, uma célula em um modelo
de planilha pode ser restrita aos valores inteiros, enquanto outra célula pode ser restrita a ser menor do
que um determinado valor.
Você pode automatizar a criação e a manipulação de modelos do Microsoft Excel Solver usando um Microsoft Visual
Basic para Applications (VBA) macro. Este artigo descreve como usar a linguagem de macro VBA para usar as funções
do Microsoft Excel Solver no Microsoft Excel 97. Este artigo presume que você esteja familiarizado com a linguagem
VBA e o Editor do Microsoft Visual Basic para o Microsoft Excel 97. Os exemplos que são usados neste artigo estão
disponíveis para download no site da Microsoft:
http://download.microsoft.com/download/excel97win/solverex/1.0/WIN98Me/EN-US/SolverEx.exe
Observação Você também pode usar as macros e os exemplos que são descritos neste artigo nas versões do Microsoft
Excel 5.0 e 7.0.
Voltar para o início
Como usar as funções do Microsoft Excel Solver em uma macro do VBA
Para usar as funções de suplemento do Microsoft Excel Solver em uma macro do VBA, você deve referenciar o add-in do
projeto VBA da pasta de trabalho que contém as macros. Se você não referenciar o suplemento do Microsoft Excel
Solver, você receberá o seguinte erro de compilação quando você tenta executar a macro:
Erro de compilação: Sub ou função não definida.
Para fazer referência o suplemento do Microsoft Excel Solver para macros em sua pasta de trabalho, use as seguintes
etapas:
1.
Abra sua pasta de trabalho.
2.
No menu Ferramentas , aponte para macro e, em seguida, clique em Editor do Visual Basic .
3.
No menu Ferramentas , clique em referências .
4.
Na lista Referências disponíveis , clique para selecionar a caixa de seleção Solver.xls e, em seguida,
clique em OK .
Observação Se você não vir Solver.xls na lista de Referências disponíveis , clique em Procurar . Na
caixa de diálogo Add Reference , localize e selecione o arquivo Solver.xla e, em seguida, clique em Abrir .
O arquivo de Solver.xla normalmente for encontrado na subpasta C:\Program Files\Microsoft
Office\Office\Library\Solver.
Você agora está pronto para usar as funções do Microsoft Excel Solver em uma macro VBA.
Voltar para o início
Como criar uma macro VBA que cria e resolve um modelo simples do Microsoft Excel Solver
Embora o Microsoft Excel Solver oferece muitas funções, as funções de três seguintes são fundamentais para criar e
solucionar um modelo:
•
A função SolverOK
•
A função SolverSolve
•
A função SolverFinish
A função SolverOK
A função SolverOK define um modelo básico do Microsoft Excel Solver. A função de SolverOK é geralmente a primeira
função que você usará para criar o modelo do Microsoft Excel Solver. A função SolverOK é equivalente a clicar Solver
em ferramentas do menu e, em seguida, especificar as opções que estão nos Parâmetros do Solver caixa de
diálogo. A sintaxe para a função SolverOK é o seguinte:
SolverOK(SetCell, MaxMinVal, ValueOf, ByChange)
As informações a seguir descrevem a sintaxe para a função SolverOK :
•
SetCell especifica a célula de destino.
•
MaxMinVal corresponde ao se você deseja resolver a célula de destino para um valor máximo (1), um
valor mínimo (2) ou um valor específico (3).
•
ValueOf Especifica o valor para o qual a célula de destino é correspondente. Se você definir MaxMinVal
para 3, você deve especificar esse argumento. Se você definir MaxMinVal como 1 ou 2, você pode omitir
este argumento.
•
ByChange especifica a célula ou intervalo de células que serão alterados.
A Figura 1 associa os argumentos da função SolverOK parâmetros na caixa de diálogo Parâmetros do Solver .
A Figura 1. Parâmetros que são associados com o SolverOK argumentos
Expandir esta imagem
A função SolverSolve
A função SolverSolve resolve o modelo usando os parâmetros que você especificou com a função SolverOK . Executar
a função SolverSolve é equivalente a clicar em resolução na caixa de diálogo Parâmetros do Solver . A sintaxe para
a função SolverSolve é o seguinte:
SolverSolve(UserFinish, ShowRef)
As informações a seguir descrevem a sintaxe para a função SolverSolve :
•
UserFinish indica se você deseja que o usuário terminar de solucionar o modelo.
Para retornar os resultados sem exibir o Solver resultados diálogo caixa, definir este argumento como
TRUE. Para retornar os resultados e exibir a caixa de diálogo Resultados do Solver , defina este
argumento como falso
•
ShowRef identifica a macro que é chamada quando o Microsoft Excel Solver retorna uma solução
intermediária.
O argumento ShowRef deve ser usado somente quando verdadeiro é passado para o argumento StepThru
da função SolverOptions .
A função SolverFinish
A função SolverFinish indica o que fazer com os resultados e o tipo de relatório para criar após o processo de solução
é concluído. A sintaxe para a função SolverFinish é o seguinte:
SolverFinish (KeepFinal, ReportArray)
As informações a seguir descrevem a sintaxe para a função SolverFinish :
•
KeepFinal indica o que fazer com os resultados finais. Se KeepFinal for 1, os valores da solução final são
mantidos nas células variáveis, substituindo os valores. Se KeepFinal for 2, os valores da solução final são
descartados e os valores anteriores são restaurados.
•
ReportArray Especifica uma matriz que indica o tipo de relatório, o Microsoft Excel criará quando a solução
for atingida. Se ReportArray for definido como 1, Microsoft Excel cria um relatório de respostas. Se
definido como 2, Microsoft Excel cria um relatório de sensibilidade e, se definida como 3 Microsoft Excel cria
um relatório de limites. Para obter mais informações sobre esses relatórios, consulte a seção " How to
generate reports for solutions ".
A Figura 2. Microsoft Excel Solver resultados opções que estão associadas com argumentos SolverFinish
Expandir esta imagem
Este artigo descreve como criar um modelo simples do Microsoft Excel Solver interativamente. A primeira etapa é criar
a sua planilha para o modelo. A planilha conterá algumas células de dados e pelo menos uma célula que contém uma
fórmula. Essa fórmula depende de outras células na planilha. Depois de configurar sua planilha, clique em Solver no
menu Ferramentas . Na caixa de diálogo Parâmetros do Solver , especifique a célula de destino, o valor que você
está solucionando para o intervalo de células que serão alteradas e as restrições. Clicar em resolução para iniciar o
processo de solução. Após o Microsoft Excel Solver tiver encontrado uma solução, os resultados aparecem na sua
planilha, e o Microsoft Excel Solver exibe uma caixa de mensagem que solicita que você se você quiser manter os
resultados finais ou se você quiser descartá-las. Quando você clica em uma das seguintes opções, o Microsoft Excel
Solver termina.
A Figura 3 ilustra um modelo simples que você pode criar usando essas etapas.
A Figura 3. Um modelo simples: modelo A raiz quadrada
Expandir esta imagem
Neste exemplo, alterar célula A1, que contém a fórmula = A1 ^ 2, como um valor que fará a célula A2 igual a um valor
de 50. Em outras palavras, localizar a raiz quadrada de 50. Não há nenhuma restrição no modelo de raiz quadrada. A
macro Find_Square_Root realiza as seguintes tarefas:
•
Configura um modelo que resolverá o valor da célula A2 para um valor de 50 alterando o valor da célula A1.
•
Ele resolve o modelo.
•
Ele salva os resultados finais para a planilha sem exibir a caixa de diálogo Resultados do Solver .
Essa macro simples cria um modelo do Microsoft Excel Solver e soluciona-sem qualquer intervenção do usuário. O
código a seguir descreve a 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 SolverFinish KeepFinal: = 1 end sub A macro Find_Square_Root2 , é uma versão modificada da macro
Find_Square_Root . Se você usar a função InputBox , a macro Find_Square_Root2 solicitará que você para o
valor que você deseja resolver para a célula de destino. Após você digitar um valor, a macro Find_Square_Root2
define esse parâmetro como o valor do argumento SolverOK valueof , resolve o problema, salva os resultados na raiz
do quadrado variável e, em seguida, descarta a solução e restaura o valor na planilha para seu estado original.
Basicamente, a macro Find_Square_Root2 ilustra como você pode salvar os resultados em um ou mais variáveis e,
em seguida, restaurar as células variáveis para seu valor original.
O código a seguir descreve a macro Find_Square_Root2 : sqroot Dim
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 Voltar para o início
Como gerar relatórios para soluções
Microsoft Excel Solver oferece vários tipos de relatórios que descrevem como os resultados alterado e como fechar as
restrições fornecida com seus valores críticos. Cada relatório é colocado em uma planilha separada na sua pasta de
trabalho. Esses seguintes são os tipos de relatórios que o Microsoft Excel Solver oferece:
•
relatório de respostas - relatório de respostas A lista a célula de destino e as células variáveis com seus
valores correspondentes originais e finais, restrições e informações sobre as restrições.
•
relatório de sensibilidade - O relatório de sensibilidade fornece informações sobre como confidenciais a
solução é pequenas alterações na fórmula para a célula de destino.
•
relatório de limites - O relatório de limites de lista a célula de destino e as células variáveis com seus
respectivos valores, os limites inferiores e superiores e os valores de destino.
Para criar relatórios para seus modelos, especifique uma matriz de valores para o argumento ReportArray da função
SolverFinish . Para obter mais informações sobre o argumento ReportArray , consulte a seção SolverFinish
(KeepFinal, ReportArray) . Por exemplo, se você deseja gerar um relatório de limites para o modelo que a macro
Find_Square_Root2 cria e resolve, modifique a função SolverFinish na macro para que fique semelhante ao seguinte
código de exemplo:
SolverFinish KeepFinal:=2, ReportArray:= Array(3) para gerar vários relatórios, modifique a função SolverFinish para que fique semelhante ao seguinte código de
exemplo:
SolverFinish KeepFinal:=2, ReportArray:= Array(1,2) Voltar para o início
Como usar as funções do Microsoft ExcelSolver em uma macro de loop
Em muitas situações, ele é uma boa idéia para que o Microsoft Excel Solver resolver a célula de destino para vários
valores. Geralmente, você pode fazer isso usando um das estruturas de loop que estão disponíveis com o VBA.
A macro Create_Square_Root_Table demonstra como o Microsoft Excel Solver funciona em uma macro de loop. O
Create_Square_Root_Table macro cria uma tabela em uma nova planilha. Ele insere os números de um por meio de
dez e a raiz quadrada correspondente de cada número. A macro Create_Square_Root_Table cria a tabela usando um
loop para iterar os números de 1 a 10 e resolver a célula de destino no modelo raiz quadrada de um valor que coincida
com o número da iteração. O código a seguir descreve a 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 SolverFinish KeepFinal: = 2 Avançar ' limpar o intervalo C1:C2 w.Range("C1:C2").Clear end sub A macro
Create_Square_Root_Table gera a tabela ilustrada na Figura 4.
A Figura 4. Saída gerada pela macro Create_Square_Root_Table
Voltar para o início
Como trabalhar com restrições
Uma restrição é uma restrição no conteúdo de um ou mais células. Um modelo pode ter uma ou várias restrições. O
conjunto de restrição é um conjunto de inequações ou um conjunto de equalities remover certas combinações de
valores para as variáveis de decisão da solução. Por exemplo, uma restrição pode exigir que uma célula ser maior que
zero e outra célula conter apenas um valor inteiro.
O modelo raiz quadrada que abordamos até a esse ponto é um modelo simples que não contenha quaisquer restrições.
A Figura 5 ilustra um modelo que usa restrições. O objetivo desse modelo é encontrar a melhor combinação de
produtos para lucro máximo.
A Figura 5. Produto misturar com para diminuir a margem de lucro
Por exemplo, se uma empresa fabrica TVs, aparelhos e alto-falantes e usa um inventário de partes comuns de fontes de
alimentação, alto-falante cones e assim por diante. As partes são no fornecimento limitado. Seu objetivo é determinar a
combinação mais rentáveis de produtos para criar. O lucro por unidade diminui com volume pois incentivos preço
adicionais são necessárias para carregar o canal de distribuição. O expoente diminuição retorna é 0,9. Este expoente é
usado para calcular o lucro por produto no intervalo G11:I11.
Seu objetivo é localizar o lucro máximo (célula G14). Os valores que você irá alterar para localizar o lucro máximo são o
número de unidades que você criar. O intervalo G9:G11 representa células variáveis nesse modelo. Sua única restrição
é que o número de partes que você usar não pode exceder o número de partes que você tenha na mão. Com o
Microsoft Excel Solver, essa restrição aparece como E3:E7 < = B3:B7. Se você fosse criar esse modelo do Microsoft
Excel Solver interativamente, os parâmetros do Microsoft Excel Solver devem ser semelhantes aos que estão na Figura
6.
A Figura 6. Parâmetros do Microsoft Excel Solver para o produto misturar com modelo para diminuir a margem de lucro
Para criar e solucionar a combinação de produtos com modelo para diminuir a margem de lucro, você usará uma nova
função, a função SolverAdd , com as funções do Microsoft Excel Solver VBA que foram descritos anteriormente. A
função SolverAdd adiciona a restrição ao modelo. Executar a função SolverAdd é equivalente a clicar no botão
Adicionar a caixa de diálogo Parâmetros do Solver . A função SolverAdd possui a seguinte sintaxe:
SolverAdd (ref. célula Relation, FormulaText)
As informações a seguir descrevem a sintaxe para a função SolverAdd :
•
ref. célula faz referência a uma ou mais células que formam o lado esquerdo da restrição.
•
relação é a relação aritmética entre o lados esquerdo e os direito de uma restrição.
•
relação pode ser um valor entre 1 e 5 como no exemplo a seguir:
•
o
O valor 1 é menor ou igual a (< =).
o
O vaue 2 é igual (=).
o
O valor 3 é maior que ou igual a (> =).
o
O valor 4 é um inteiro.
o
O valor 5 é o binário (um valor de zero ou um).
FormulaText faz referência a uma ou mais células que formam o lado direito da constraint.* *
**When você especifica um intervalo de células para o argumento FormulaText da função SolverAdd , observe se a
referência é relativo ou absoluto. Em geral, você deve especificar uma referência absoluta para o argumento
FormulaText . No entanto, se for especificado referências relativas para o argumento FormulaText , percebe que a
referência será relativas a célula de destino e não a célula ativa.
Observação No Microsoft Excel, versões 5.0 e 7.0, use a notação de L1C1 quando você especifica uma célula ou um
intervalo de células com o argumento FormulaText . Em contraste, no Microsoft Excel 97, usar o estilo A1 notação
para especificar o argumento FormulaText .
A Figura 7. Os campos que estão associados com os argumentos SolverAdd
A macro Maximum_Profit que gera um modelo para a combinação de produtos com modelo de diminuição retorna.
Essa macro executa as seguintes funções ou argumentos:
•
A função SolverOK configura a célula de destino para um valor máximo e especifica as células para alterar.
•
A função SolverAdd adiciona a restrição ao modelo.
•
A função SolverSolve localiza uma solução sem exibir a caixa de diálogo Resultados do Solver .
•
A função SolverFinish retorna os resultados finais para a planilha.
O código a seguir descreve a 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 Observação no Microsoft Excel, versões 5.0 e 7.0, use a notação de L1C1 quando você especifica uma célula ou
intervalo de células com o argumento FormulaText . Em contraste, no Microsoft Excel 97, usar o estilo A1 notação
para especificar o argumento FormulaText .
Quando você executa a macro Maximum_Profit , o Microsoft Excel Solver encontrará uma solução de criação de
conjuntos de TV 160, aparelhos 200 e alto-falantes 80 para um máximo lucro de r$ 14,917 dólares.
Voltar para o início
Como alterar e excluir restrições
Restrições em seu modelo podem ser através de programação alteradas ou excluídas. Restrições são identificadas por
seus ref. célula e argumentos de relação .
Para alterar programaticamente uma restrição existente, use a função SolverChange . A seguir está a sintaxe para o
SolverChange função:
SolverChange (ref. célula Relation, FormulaText)
Observe que os argumentos da função SolverChange são iguais aos que você use com a função SolverAdd .
Se você quiser alterar a restrição na combinação de produtos com modelo de diminuição retorna, você deve usar a
função SolverChange . Por exemplo, no momento da restrição que é especificada é que o número de partes usadas é
menor ou igual ao número de partes disponível (E3:E7 < = B3:B7). Se você quiser alterar essa restrição para que o
número de partes usada é menor ou igual ao número do partes projetado (número de partes em mão) mais o número
de partes solicitada. Essa nova restrição aparência E3:E7 < = D3:D7. A seguinte macro alteraria o existente E3:E7
restrição < = B3:B7 para E3:E7 < = D3:D7 e resolve para uma solução.
O código a seguir descreve a 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 SolverSolve UserFinish: = False end sub como restrições são identificadas pelo ref. célula e argumentos de relação ,
você só poderá alterar o argumento FormulaText para a restrição usando a função SolverChange . Se o ref. célula e
os valores de relação não corresponderem uma restrição existente, você deve excluir a restrição e, em seguida,
adicionar a restrição modificada. Para excluir uma restrição, use a função SolverDelete . A sintaxe para a função
SolverDelete é o seguinte:
SolverDelete (ref. célula Relation, FormulaText)
Observe que os argumentos da função SolverDelete são iguais às que você usar com o SolverAdd e as funções de
SolverChange .
A macro a seguir ilustra como excluir e adicionar uma restrição. Neste exemplo, a macro
Change_Constraint_and_Solve2 removerá a restrição E3:E7 < = B3:B7 da combinação de produtos com modelo de
diminuição retorna e adiciona uma nova restrição. A restrição nova é apenas uma modificação da restrição original,
onde os lados esquerdo e direito da restrição serão revertidos.
O código a seguir descreve a 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 Observação no Microsoft Excel, versões 5.0 e 7.0, use a notação de L1C1 quando você especifica uma célula ou
intervalo de células com o argumento FormulaText . Em contraste, no Microsoft Excel 97, usar o estilo A1 notação
para especificar o argumento FormulaText .
Voltar para o início
Como carregar e salvar seus modelos
Quando você salva sua pasta de trabalho, os últimos parâmetros que você especificado na caixa de diálogo
Parâmetros do Solver são salvas com a pasta de trabalho. Portanto, quando você abre a pasta de trabalho, os
parâmetros são as mesmas quando você salvou a pasta de trabalho pela última vez.
Você pode definir mais de um problema para uma planilha. Cada problema é composto de células e restrições que você
inserir nos Parâmetros do Solver e as caixas de diálogo Opções do Solver . Como o último problema é salvo com a
planilha, você perderá todos os outros problemas, a menos que você salvá-las explicitamente. Para salvá-las, clique em
Salvar modelo no Solver opções caixa de diálogo. Da mesma forma, quando você deseja restaurar os parâmetros
salvas anteriormente, clique em modelo de carga na caixa de diálogo Opções do Solver .
Modelos do Solver são armazenados em um intervalo de células em uma planilha. A primeira célula do intervalo contém
a fórmula para a célula de destino. A segunda célula no intervalo contém a fórmula que identifica as células variáveis no
modelo. A última célula no intervalo contém uma matriz que representa as opções que definiu na caixa de diálogo
Opções do Solver . As células entre a célula a segunda e a última célula contêm as fórmulas que representam as
restrições no modelo.
A Figura 8 ilustra um modelo para agendamento de funcionário. Suponha que você trabalha para um fabricante de
pequena empresa. Esta tabela mostra taxa por hora cada funcionário ’s de pagamento, o número de horas que eles são
agendados e um número projetado de unidades de que cada funcionário pode produzir em uma hora. Seu objetivo é
atender uma cota específica para o número de unidades produzido ao mesmo tempo em que o custo de mão-de-obra.
A Figura 8. Funcionário modo de agendamento
l
Dois fatores adicionais (ou restrições) que você deve considerar o número de mínimo/máximo de horas que qualquer
um funcionário pode trabalhar e o número de unidades que você deseja produzir. Se para uma semana especificada,
você precisa produzir 3975 unidades e desejar que cada funcionário trabalhar entre 30 e 45 horas, os parâmetros do
Microsoft Excel Solver devem ser semelhantes aos descrito na tabela a seguir:
parâmetro
intervalo de célula descrição
Célula de destino $ D $12
Custo de mão-de-obra.
Células variáveis
$C$2: $C$8
Horas trabalhadas por funcionário.
Restrições
$C$2: $C$8 < = 45
Número máximo de horas por funcionário é 45.
$C$2: $C$8 > = 30
Mínimas de horas por funcionário é 35.
G $ $ 12 = 3975
Número de unidades é 3975.
Seus objetivos são resolver para custos de mão-de-obra ideal em uma base semanal, para salvar cada modelo
semanalmente e ser capaz de carregar qualquer modelo semanal quando necessário.
Em uma macro, os parâmetros do Microsoft Excel Solver para um modelo podem ser salvo e carregados pelo usando o
SolverSave e as funções de SolverLoad respectivamente. O SolverSave e as funções de SolverLoad têm a seguinte
sintaxe:
SolverSave (SaveArea)
solverLoad (LoadArea)
O SolverSave e as funções de SolverLoad têm apenas um argumento, SaveArea e os argumentos LoadArea
respectivamente. Esses argumentos especificar um intervalo em uma planilha em que as informações de modelo são
armazenadas.
A seguinte macro New_Employee_Schedule demonstra como para criar, para resolver e para salvar um modelo
baseado na entrada do usuário. O usuário é solicitado a fornecer a data do modelo, o número de unidades para produzir
e o número mínimo e máximo de horas por funcionário. Esses dados, em seguida, é usado para criar o modelo. O
modelo é resolvido e, em seguida, salva com a entrada do usuário.
O código a seguir descreve a 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 Observação no Microsoft Excel, versões 5.0 e 7.0, use a notação de L1C1 quando você especifica uma célula ou
intervalo de células com o argumento FormulaText . Em contraste, no Microsoft Excel 97, usar o estilo A1 notação
para especificar o argumento FormulaText .
A Figura 9 ilustra como as informações de modelo salvo aparecem na planilha.
A Figura 9. Informações de modelo que são salvas, a macro New_Employee_Schedule
Expandir esta imagem
A macro New_Employee_Schedule salva cada novo modelo de planilha. A macro Load_Employee_Schedule pode
carregar um desses modelos salvos. A macro solicita ao usuário para o modelo para carregar e, em seguida, procura a
coluna que para a data de modelo. Se a data do modelo for encontrada, a macro Load_Employee_Schedule carrega
o modelo correspondente, resolve-lo e, em seguida, mantém os resultados finais.
O código a seguir descreve a 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 a macro New_Employee_Schedule apresenta a função SolverReset . O SolverReset função pode ser usada para
excluir todas as seleções de célula e restrições na caixa de diálogo Parâmetros do Solver e para redefinir todas as
configurações em A função SolverReset possui sem argumentos.
Voltar para o início
Como encontrar mais informações sobre o Microsoft Excel Solver
Os seguintes recursos fornecem informações sobre como usar o Microsoft Excel Solver add-in.
•
Para obter ajuda com mensagens solver específicas, consulte Frontline Systems.
•
Para obter dicas sobre a criação de legível, gerenciáveis modelos, consulte Frontline Systems.
•
Para obter informações adicionais sobre o recurso Solver limites para restrições e, clique no número abaixo
para ler o artigo na Base de Dados de Conhecimento da Microsoft:
75714 Limites do Solver para restrições
•
Para vários exemplos que usar o suplemento do Microsoft Excel Solver no Microsoft Excel, consulte o
arquivo de exemplo Exemsolv.xls.
•
A seguir é o local do padrão do arquivo de exemplo que acompanha o Microsoft Excel 97:
\Program Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
•
A seguir é o local do padrão do arquivo de exemplo que acompanha o Microsoft Excel 7.0:
\MSOffice\Excel\Examples\Solver\SolvSamp.xls
•
A seguir é o local do padrão do arquivo de exemplo que acompanha o Microsoft Excel 5.0:
\Excel\Examples\Solver\SolvSamp.xls
Voltar para o início
Como saber mais sobre o algoritmo e métodos que são usados pelo Microsoft Excel Solver
Microsoft Excel Solver usa o gradiente generalizada reduzida código de otimização não linear (GRG2) que foi
desenvolvido por Leon Lasdon, Universidade do Texas em Austin e Allan Waren, Universidade do Estado de Cleveland.
Para obter informações adicionais sobre o algoritmo usado pelo Microsoft Excel Solver, clique o número abaixo para ler
o artigo na Base de Dados de Conhecimento da Microsoft:
82890 O Solver usa generalizada reduzida
Problemas de linear e inteiro usar o método simplex com limites nas variáveis e o método de ramificação e ligado,
implementado por John Watson e Dan Fylstra, Frontline Systems, Inc. Para obter mais informações sobre o processo de
solução interno usado pelo Solver, contate:
Frontline Systems, Inc. P.O. Box 4288 Incline Village, NV 89450‐4288 (702) 831‐0300 Web site: http://www.frontsys.com Electronic mail: [email protected] P.O. Box 4288 vila inclinada, NV 89450-4288 (702) 831-0300 site: http://www.frontsys.com email eletrônica:
[email protected]
Seleções de código de programa são o Microsoft Excel Solver copyright 1990, 1991, 1992 e 1995 Frontline Systems,
Inc. partes são copyright 1989, optimal métodos, Inc.
Observação O suplemento do Microsoft Excel Solver descrita neste artigo é fornecido "como estão" e nós não garante
que ele pode ser usado em todas as situações. Embora os profissionais de suporte da Microsoft possam ajudar com a
instalação e a funcionalidade existente desse suplemento, eles não modificarão o add-in para fornecer nova
funcionalidade.
nenhuma garantia . O software é fornecido " como-é, " sem garantia de qualquer tipo e qualquer uso deste software
produto está em seu próprio risco.
Voltar para o início
A informação contida neste artigo aplica-se a:
•
Microsoft Excel 97 Standard Edition
Vo
oltar para o in
nício
Pala
avras-chave
e: kbmt kbhowto kbmacrroexample kb
berrmsg kbad
ddin kbvba kb
bprogrammin
ng kbinfo KB8
843304 KbMttpt
Vo
oltar para o in
nício
T
Tradução
auto
omática
IMPO
ORTANTE: Es
ste artigo foi traduzido por um sistema
a de tradução
o automática (também de
esignado por Machine
Tran
nslation ou MT
T), não tendo
o sido portan
nto traduzido ou revisto po
or pessoas. A Microsoft po
ossui artigos traduzidos por
p
aplic
cações (MT) e artigos trad
duzidos por trradutores pro
ofissionais, co
om o objetivo
o de oferecerr em português a totalidad
de
dos artigos existe
entes na base
e de dados de suporte. No
o entanto, a tradução auttomática não é sempre pe
erfeita, poden
ndo
v
s
sintaxe
ou gramática. A Microsoft
M
não é responsáve
el por incoerê
ências, erros ou prejuízos
s
conter erros de vocabulário,
corrência da utilização
u
dos
s artigos MT por parte dos
s nossos clien
ntes. A Micro
osoft realiza atualizações
a
ocorrridos em dec
freqü
üentes ao sofftware de tra
adução autom
mática (MT). Caso
C
venha a encontrar erros
e
neste arrtigo e queira
a colaborar no
proc
cesso de aperrfeiçoamento desta ferram
menta, por favor preencha
a o formulário
o existente na parte inferior desta
página. Obrigado
o.
ue aqui para ver a versão em Inglês deste artigo: 843304
8
Cliqu
Vo
oltar para o in
nício
A
Aviso
de Isenç
ção de Respo
onsabilidade sobre
s
Conteú
údo do KB Ap
posentado
Este artigo trata de produtos para os quais a Microsoftt não mais ofe
erece suporte
e. Por esta ra
azão, este artigo é oferecido
"com
mo está" e nã
ão será mais atualizado.
Vo
oltar para o in
nício

Documentos relacionados