Solver e 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.


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.


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.


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


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


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)


SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=val, _

ByChange:=Range("A1")

' Do not display the Solver Results dialog box.


' Save the value of cell A1 (the changing cell) before you discard

' the results.

sqroot = Range("a1")

' Finish and discard the results.


' Show the result in a message box.

MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00")

End Sub


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)


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



' 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.


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


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()


' 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"



' Finish and keep the final results.


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



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.


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





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.



' 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




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


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)



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.


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


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.


A informação contida neste artigo aplica-se a:

• Microsoft Excel 97 Standard Edition

Vo

Pala

Vo

T

IMPO

Tran

aplic

dos

cont

ocor

freqü

proc

pági

Cliqu

Vo

A

Este

"com

Vo

oltar para o in

avras-chave

oltar para o in

Tradução auto

ORTANTE: Es

nslation ou MT

cações (MT) e

artigos existe

er erros de v

rridos em dec

üentes ao sof

cesso de aper

na. Obrigado

ue aqui para

oltar para o in

Aviso de Isenç

artigo trata

mo está" e nã

oltar para o in

nício

e: kbmt kbh

nício

omática

ste artigo foi

T), não tendo

e artigos trad

entes na base

vocabulário, s

corrência da u

ftware de tra

rfeiçoamento

o.

ver a versão

nício

ção de Respo

de produtos

ão será mais

nício

owto kbmacr

traduzido po

o sido portan

duzidos por tr

e de dados d

sintaxe ou gr

utilização dos

adução autom

desta ferram

em Inglês d

onsabilidade s

para os quai

atualizado.

roexample kb

r um sistema

nto traduzido

radutores pro

e suporte. No

amática. A M

s artigos MT

mática (MT). C

menta, por fa

este artigo: 8

sobre Conteú

s a Microsoft

berrmsg kbad

a de tradução

ou revisto po

ofissionais, co

o entanto, a

Microsoft não

por parte dos

Caso venha a

vor preencha

843304

údo do KB Ap

t não mais ofe

ddin kbvba kb

o automática

or pessoas. A

om o objetivo

tradução aut

é responsáve

s nossos clien

a encontrar e

a o formulário

posentado

erece suporte

bprogrammin

(também de

A Microsoft po

o de oferecer

tomática não

el por incoerê

ntes. A Micro

erros neste ar

o existente n

e. Por esta ra

ng kbinfo KB8

esignado por

ossui artigos

r em portugu

é sempre pe

ências, erros

osoft realiza a

rtigo e queira

a parte infer

azão, este ar

843304 KbMt

Machine

traduzidos p

ês a totalidad

erfeita, poden

ou prejuízos

atualizações

a colaborar n

ior desta

tigo é oferec

tpt

por

de

ndo

s

o

ido

Documents

Solver e Macros