Como criar macros do Visual Basic usando o Excel Solver no Excel 97

Traduções deste artigo Traduções deste artigo
ID do artigo: 843304 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Neste artigo

Sumário

Este artigo descreve como usar o Microsoft Excel Solver no Microsoft Excel 97 para criar 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 lista a seguir relaciona todos os tópicos abordados no artigo.

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 adicionar 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 relacionadas à célula de destino usando uma equação. Depois de construir uma equação e definir 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á minimizado, maximizado ou definido para um determinado valor.
  • células variáveis - células variáveis 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 sendo menor 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 do 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 Microsoft Visual Basic Editor para o Microsoft Excel 97. Os exemplos 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 descritos neste artigo no Microsoft Excel versões 5.0 e 7.0.

Como usar as funções do Microsoft Excel Solver em uma macro VBA

Para usar as funções de suplemento do Microsoft Excel Solver em uma macro do VBA, você deve fazer referência 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 Function 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 marcar a caixa de seleção Solver.xls e, em seguida, clique em OK .

    Observação Se você não ver Solver.xls na lista 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 Solver.xla normalmente é encontrado na subpasta C:\Program Files\Microsoft Office\Office\Library\Solver.
Agora você 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 três funções a seguir são fundamentais para criar e para 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 SolverOK é geralmente a primeira função que você usará para criar o modelo do Microsoft Excel Solver. A função SolverOK é equivalente a clicar em Solver no Ferramentas menu e em seguida, especificando as opções em 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 da 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 será coincidida. 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 o intervalo de células que será alterado.
A Figura 1 associa os argumentos para a função SolverOK com os parâmetros na caixa de diálogo Parâmetros do Solver .

Figura 1. Parâmetros que estão associados com SolverOK argumentos

Recolher esta imagemExpandir esta imagem
 Figure 1. Parameters that are associated
		  with the SolverOK arguments


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 da função SolverSolve :
  • UserFinish indica se você deseja que o usuário para concluir a resolver o modelo.

    Para retornar os resultados sem exibir o Solver resultados diálogo caixa, defina este argumento como TRUE. Para retornar os resultados e exibir a caixa de diálogo Resultados do Solver , defina este argumento para FALSO
  • ShowRef identifica a macro é 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 da função SolverFinish :
  • KeepFinal indica o que fazer com os resultados finais. Se KeepFinal é 1, os valores de solução finais são mantidos nas células variáveis, substituindo os valores. Se KeepFinal é 2, os valores de solução finais são descartados e os valores anteriores são restaurados.
  • ReportArray Especifica uma matriz que indica o tipo de relatório, que o Microsoft Excel criará quando a solução é alcançada. Se ReportArray é definido como 1, Microsoft Excel cria um relatório de resposta. Se definido como 2, Microsoft Excel criará um relatório de sensibilidade, se definida como 3 e 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".
Figura 2. Microsoft Excel Solver resulta opções que estão associadas com argumentos SolverFinish

Recolher esta imagemExpandir esta imagem
 Figure 2. Solver results options
		  that are associated with SolverFinish arguments


Este artigo descreve como criar um modelo simples do Microsoft Excel Solver interativamente. A primeira etapa é criar a planilha para o modelo. A planilha irá 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 são resolver para, o intervalo de células que serão alteradas e as restrições. Clique em resolver para iniciar o processo de solução. Após o Microsoft Excel Solver encontrou uma solução, os resultados são exibidos na sua planilha e o Microsoft Excel Solver exibe uma caixa de mensagem que solicita se você quiser manter os resultados finais ou se você quiser descartá-las. Quando você clica em uma dessas opções, o Microsoft Excel Solver termina.

A Figura 3 ilustra um modelo simples que você pode criar usando essas etapas.

Figura 3. Um modelo simples: modelo a raiz quadrada

Recolher esta imagemExpandir esta imagem
 Figure 3. A
		  simple model: The Square Root model


Neste exemplo, alteração de célula, que contém a fórmula = A1 ^ 2, para um valor que tornará a célula A2 igual a um valor de 50. Em outras palavras, localize a raiz quadrada de 50. Não há nenhum restrições no modelo de raiz quadrada. A macro Find_Square_Root realiza as seguintes tarefas:
  • Ele define 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 resolve-lo 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
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 valueof SolverOK , 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 seu estado original. Basicamente, a macro Find_Square_Root2 ilustra como você pode salvar os resultados em uma ou mais variáveis e, em seguida, restaure as células variáveis para seu valor original.

O código a seguir descreve a macro Find_Square_Root2 :
    Sub Find_Square_Root2()

    Dim val
    Dim sqroot

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

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

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

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

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

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

    End Sub

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 aos seus valores críticos. Cada relatório é colocado em uma planilha separada na sua pasta de trabalho. Esses a seguir são os tipos de relatórios que o Microsoft Excel Solver oferece:
  • Relatório de resposta - relatório de resposta a lista a célula de destino e as células variáveis com seus valores correspondentes de 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 da solução para pequenas alterações na fórmula da célula de destino.
  • Limites de relatório - O relatório de limites de lista a célula de destino e as células variáveis com seus respectivos valores, limites inferior e superior e os valores de destino.
Para criar relatórios para os modelos, especifica uma matriz de valores de argumento da função SolverFinish ReportArray . Para obter mais informações sobre o argumento ReportArray , consulte a seção "SolverFinish (KeepFinal, ReportArray) ". Por exemplo, se você desejar gerar um relatório de limites para o modelo que a macro Find_Square_Root2 cria e resolve, modifique a função SolverFinish a macro para que fique similar ao seguinte código de exemplo:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
para gerar vários relatórios, modificar função SolverFinish para que ele se parece com o seguinte código de exemplo:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)

Como usar as funções Microsoft ExcelSolver em uma macro de loop

Em muitas situações, é 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 uma das estruturas de loop disponíveis com o VBA.

A macro Create_Square_Root_Table demonstra como o Microsoft Excel Solver funciona em uma macro loop. Create_Square_Root_Table macro cria uma tabela em uma nova planilha. Ela 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 for para iterar em números de 1 a 10 e resolver a célula de destino no modelo de raiz quadrada para um valor que corresponde ao 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

a macro Create_Square_Root_Table gera a tabela ilustrada na Figura 4.

Figura 4. Saída gerada pela macro Create_Square_Root_Table

Recolher esta imagemExpandir esta imagem
Figure 4. Output that is generated by the
		  Create Square Root Table macro


Como trabalhar com restrições

Uma restrição é uma restrição no conteúdo de uma 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 a outra célula contém somente um valor inteiro.

O modelo de raiz quadrada que abordamos até esse ponto é um modelo simples que não contém quaisquer restrições. Figura 5 ilustra um modelo que usa restrições. O objetivo desse modelo é encontrar a melhor combinação de produtos para o lucro máximo.

Figura 5. Produto misturar com diminuindo margem de lucro

Recolher esta imagemExpandir esta imagem
Figure 5. Product mix with
		  diminishing profit margin


Por exemplo, se uma empresa fabrica TVs, aparelhos de som e alto-falantes e usa um inventário de partes comuns de alimentação, alto-falante cones e assim por diante. As partes estão na alimentação limitada. Seu objetivo é determinar a combinação de produtos para criar mais rentáveis. 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.

O 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 as células variáveis neste modelo. A ú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 seria semelhantes aos que estão na Figura 6.

Figura 6. Parâmetros do Microsoft Excel Solver para o produto misturam com margem de lucro diminuindo modelo

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


Para criar e resolver o mix de produtos com modelo diminuindo margem de lucro, você usará uma nova função, a função SolverAdd , juntamente com as funções do VBA do Microsoft Excel Solver 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 (CellRef Relation, FormulaText)
As informações a seguir descrevem a sintaxe da função SolverAdd :
  • CellRef faz referência a um 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 valor 1 é menor ou igual a (< =).
    • Vaue 2 é igual a (=).
    • O valor 3 é igual a ou maior que (> =).
    • O valor 4 é um número inteiro.
    • O valor 5 é o binário (um valor de zero ou mais).
  • FormulaText faz referência a uma ou mais células que formam o lado direito da constraint.* 1
** Quando você especifica um intervalo de células para o argumento FormulaText da função SolverAdd , observe se a referência é relativo ou absoluto. Geralmente, 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á relativo para 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 L1C1 quando você especifica uma célula ou um intervalo de células com o argumento FormulaText . Por outro lado, no Microsoft Excel 97, use o estilo A1 notação para especificar o argumento FormulaText .

Figura 7. Campos que estão associados com os argumentos SolverAdd

Recolher esta imagemExpandir esta imagem
 Figure 7. Fields that are associated with
		  the SolverAdd arguments


A macro Maximum_Profit que gera um modelo para a combinação de produtos com modelo de diminuição retorna. Essa macro executa funções ou argumentos a seguir:

  • 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 encontra 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 L1C1 quando você especifica uma célula ou intervalo de células com o argumento FormulaText . Por outro lado, no Microsoft Excel 97, use o estilo A1 notação para especificar o argumento FormulaText .

Quando você executa a macro Maximum_Profit , Microsoft Excel Solver irá encontrar uma solução de criação de conjuntos de TV 160, 200 aparelhos de som e alto-80 falantes para um lucro máximo de $ 14,917 dólares.

Como alterar e excluir restrições

Restrições em seu modelo podem ser alteradas ou excluídas programaticamente. Restrições são identificadas por seus CellRef e argumentos de relação .

Para alterar programaticamente uma restrição existente, use a função SolverChange . A seguir está a sintaxe para SolverChange função:
SolverChange (CellRef Relation, FormulaText)
Observe que os argumentos para a função SolverChange são o mesmo para aqueles que você usar com a função SolverAdd .

Se você desejar alterar a restrição de mix de produtos com modelo de diminuição retorna, você usaria a função SolverChange . Por exemplo, no momento a restrição que é especificada é que o número de peças usadas é menor ou igual ao número de partes por lado (E3:E7 < = B3:B7). Se você desejar alterar essa restrição para que o número de peças usadas é menor ou igual ao número de partes projetado (número de partes em mão) mais número de partes ordenados. Essa restrição nova aparência E3:E7 < = D3:D7. A macro a seguir alteraria o E3:E7 restrição existente < = 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

como restrições são identificadas pelos argumentos CellRef e Relation , você somente pode alterar o argumento FormulaText para a restrição usando a função SolverChange . Se CellRef e os valores de relação não coincidirem com uma restrição existente, você deve excluir a restrição e 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 (CellRef Relation, FormulaText)

Observe que os argumentos para a função SolverDelete são o mesmo para que você usa com SolverAdd e as funções SolverChange .

A macro a seguir ilustra como excluir e adicionar uma restrição. Neste exemplo, a macro Change_Constraint_and_Solve2 remove a restrição E3:E7 < = B3:B7 de mix de produtos com modelo de diminuição retorna e adiciona uma nova restrição. A nova restrição é 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 L1C1 quando você especifica uma célula ou intervalo de células com o argumento FormulaText . Por outro lado, no Microsoft Excel 97, use o estilo A1 notação para especificar o argumento FormulaText .

Como carregar e salvar seus modelos

Quando você salva sua pasta de trabalho, os últimos parâmetros que você especificou 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 o mesmo 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ê insira nos Parâmetros do Solver e as caixas de diálogo Opções do Solver . Porque somente o último problema é salvo com a planilha, você perderá todos os outros problemas, a menos que você salvá-las explicitamente. Para salvá-los, clique em Salvar modelo no Solver opções caixa de diálogo. Da mesma forma, quando você deseja restaurar os parâmetros salvos anteriormente, clique em Carregar modelo na caixa de diálogo Opções do Solver .

O Solver modelos são armazenados em um intervalo de células em uma planilha. A primeira célula no 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 definidas na caixa de diálogo Opções do Solver . As células entre a segunda célula e a última célula contêm as fórmulas que representam as restrições no modelo.

Figura 8 ilustra um modelo para agendamento de funcionário. Suponha que você trabalha para um fabricante pequeno. Esta tabela mostra taxa por hora ?s cada funcionário 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 minimizar o custo de mão-de-obra.

Figura 8. Funcionário Modo de agendamento
l
Recolher esta imagemExpandir esta imagem
 Figure 8. Employee Scheduling
		  model


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ê pretende produzir. Se por uma semana especificada, você precisa produzir 3975 unidades e você deseja cada funcionário trabalhar entre 30 e 45 horas, os parâmetros do Microsoft Excel Solver pareceria semelhantes aos descritos na tabela a seguir:

Recolher esta tabelaExpandir esta tabela
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 Máxima de horas por funcionário é 45.
$ C $ 2: $ C $ 8 > = 30 Horas mínimas 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 precisar.

Em uma macro, os parâmetros do Microsoft Excel Solver para um modelo podem ser salvos e carregados usando SolverSave e as funções SolverLoad respectivamente. SolverSave e as funções SolverLoad têm a seguinte sintaxe:

SolverSave (SaveArea)

solverLoad (LoadArea)

SolverSave e as funções SolverLoad têm apenas um argumento, SaveArea e os argumentos LoadArea respectivamente. Esses argumentos especificam um intervalo em uma planilha em que as informações de modelo são armazenadas.

A macro a seguir New_Employee_Schedule , demonstra como criar, resolver e salvar um modelo com base 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 L1C1 quando você especifica uma célula ou intervalo de células com o argumento FormulaText . Por outro lado, no Microsoft Excel 97, use o estilo A1 notação para especificar o argumento FormulaText .

Figura 9 ilustra como as informações de modelo salvo aparecem na planilha.

Figura 9. Informações de modelo que são salvas pela macro New_Employee_Schedule

Recolher esta imagemExpandir esta imagem
Figure 9. Model information that is saved
		  by the New Employee Schedule macro


A macro New_Employee_Schedule salva cada novo modelo para a 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, 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
macro New_Employee_Schedule apresenta a função SolverReset . 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 redefinir todas as configurações a função SolverReset não possui argumentos.

Como encontrar mais informações sobre o Microsoft Excel Solver

Os recursos a seguintes 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:
    75714O Solver limites de 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 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 padrão do arquivo de exemplo que acompanha o Microsoft Excel 7.0:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • A seguir é o local 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, da Universidade do Texas em Austin e Allan Waren, Cleveland State University.

Para obter informações adicionais sobre o algoritmo usado pelo Microsoft Excel Solver, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
82890O Solver usa generalizada reduzido


Problemas linear e inteiro usam 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, entre em contato com:

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


Seleções de código de programa são o Microsoft Excel Solver copyright 1990, 1991, 1992 e 1995 pela Frontline Systems, Inc. partes são registradas em 1989 pela Optimal Methods, Inc.

Observação O suplemento do Microsoft Excel Solver descrita neste artigo é fornecido "como estão" e não garantimos que pode ser usado em todas as situações. Embora profissionais de Atendimento Microsoft pode 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.

Propriedades

ID do artigo: 843304 - Última revisão: quarta-feira, 11 de outubro de 2006 - Revisão: 1.2
A informação contida neste artigo aplica-se a:
  • Microsoft Excel 97 Standard Edition
Palavras-chave: 
kbmt kbhowto kbmacroexample kberrmsg kbaddin kbvba kbprogramming kbinfo KB843304 KbMtpt
Tradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine Translation ou MT), não tendo sido portanto traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 843304
Aviso de Isenção de Responsabilidade sobre Conteúdo do KB Aposentado
Este artigo trata de produtos para os quais a Microsoft não mais oferece suporte. Por esta razão, este artigo é oferecido "como está" e não será mais atualizado.

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com