Excel Solver: Otimizando Resultados, Adicionando Restrições e Salvando Soluções como Cenários

Você configura o modelo do Solver do Excel usando a caixa de diálogo Parâmetros do Solver. Use a caixa Definir objetivo para especificar a célula do objetivo e use o grupo Para para dizer ao Excel Solver o que você deseja da célula do objetivo: o valor máximo possível; o valor mínimo possível; ou um valor específico. Finalmente, você usa a caixa Alterando células variáveis ​​para especificar as células que o Solver pode usar para inserir valores e otimizar o resultado.

Otimizando os resultados do Excel Solver

Quando o Solver encontra uma solução, você pode escolher Keep Solver Solution ou Restore Original Values. Se você escolher Keep Solver Solution, o Excel altera permanentemente a planilha. Você não pode desfazer as alterações.

Com o seu modelo de planilha pronto para o Solver pronto para usar, aqui estão as etapas a seguir para encontrar um resultado ideal para o seu modelo usando o Solver:

Escolha Dados → Solver.
O Excel abre a caixa de diálogo Parâmetros do Solver.

Na caixa Definir objetivo, insira o endereço da célula objetivo do seu modelo.
Observe que se você clicar na célula para inseri-la, o Solver inserirá automaticamente um endereço de célula absoluto (por exemplo, $ B $ 14 em vez de B14). O Solver funciona bem de qualquer maneira.

No grupo Para, selecione uma opção:

• Máx: retorna o valor máximo possível.
• Min: Retorna o valor mínimo possível.
• Valor de: Insira um número para definir a célula objetiva para esse número.

  Para o modelo de exemplo, Value Of foi selecionado e 0 inserido na caixa de texto.

Na caixa Alterando células variáveis, insira os endereços das células que você deseja que o Solver altere enquanto procura uma solução.
No exemplo, as células mutáveis ​​são B4 e C4. A imagem a seguir mostra a caixa de diálogo Parâmetros do Solver concluída.

A caixa de diálogo Parâmetros do Solver concluída.

Clique em Resolver.
O Solver vai ao trabalho. Enquanto o Solver resolve o problema, você pode ver as caixas de diálogo Mostrar solução de teste aparecer uma ou mais vezes.

Em qualquer caixa de diálogo Mostrar Solução de Teste que aparecer, clique em Continuar para mover as coisas.
Quando a otimização é concluída, o Excel exibe a caixa de diálogo Resultados do Solver.

A caixa de diálogo Resultados do Solver e a solução para o problema do ponto de equilíbrio.

Selecione a opção Keep Solver Solution.
Se você não quiser aceitar o resultado, selecione a opção Restaurar valores originais.

Clique OK.

Você pode pedir ao Solver para exibir um ou mais relatórios que fornecem informações extras sobre os resultados. Na caixa de diálogo Resultados do Solver, use a lista Relatórios para selecionar cada relatório que deseja visualizar:

• Resposta: Exibe informações sobre a célula objetiva do modelo, células variáveis ​​e restrições. Para a célula objetiva e as células variáveis, o Solver mostra os valores original e final.
• Sensibilidade: Tenta mostrar o quão sensível é uma solução para mudanças nas fórmulas do modelo. O layout do relatório de sensibilidade depende do tipo de modelo que você está usando.
• Limites: exibe a célula objetiva e seu valor, bem como as células variáveis ​​e seus endereços, nomes e valores.

O Excel Solver pode usar um dos vários métodos de solução. Na caixa de diálogo Parâmetros do Solver, use a lista Selecionar um Método de Resolução para selecionar um dos seguintes:

• LP Simplex: Use se o modelo de planilha for linear. Nos termos mais simples possíveis, um modelo linear é aquele em que as variáveis ​​não são elevadas a nenhuma potência e nenhuma das chamadas funções transcendentes - como SIN e COS - são utilizadas.
• GRG não linear: use se o seu modelo de planilha for não linear e uniforme. Em termos gerais, um modelo suave é aquele em que um gráfico da equação usada não mostra bordas agudas ou quebras.
• Evolucionário: Use se o seu modelo de planilha for não linear e não suave.

Você precisa se preocupar com nada disso? Quase certamente não. O padrão do Excel Solver é usar GRG Nonlinear, e isso deve funcionar para quase tudo que você fizer com o Solver.

Adicionando restrições ao Excel Solver

O mundo real impõe restrições e condições às fórmulas. Uma fábrica pode ter uma capacidade máxima de 10.000 unidades por dia, o número de funcionários em uma empresa não pode ser um número negativo e seus custos de publicidade podem ser restritos a 10 por cento das despesas totais.

Da mesma forma, suponha que você esteja executando uma análise de ponto de equilíbrio em dois produtos. Se você executar a otimização sem quaisquer restrições, o Solver pode atingir um lucro total de 0 definindo um produto com uma pequena perda e o outro com um pequeno lucro, onde a perda e o lucro se cancelam mutuamente. Na verdade, se você der uma olhada na imagem anterior, é exatamente isso que o Solver fez. Para obter uma solução de equilíbrio real, você pode preferir ver os valores de lucro do produto como 0.

Essas restrições e condições são exemplos do que o Solver chama de restrições. Adicionar restrições informa ao Solver para encontrar uma solução para que essas condições não sejam violadas.

Veja como executar o Solver com restrições adicionadas à otimização:

Escolha Dados → Solver.
O Excel abre a caixa de diálogo Parâmetros do Solver.

Use a caixa Definir objetivo, o grupo Para e a caixa Ao alterar células variáveis ​​para configurar o Solver conforme descrito acima.

Clique em Adicionar.
O Excel exibe a caixa de diálogo Adicionar restrição.

Na caixa Referência de célula, insira o endereço da célula que deseja restringir.
Você pode digitar o endereço ou selecionar a célula na planilha.

Na lista suspensa, selecione a operadora que deseja usar.
Na maioria das vezes, você usa um operador de comparação, como igual a (=) ou maior que (>). Use o operador int (inteiro) quando precisar que uma restrição, como o total de funcionários, seja um valor inteiro em vez de um número real (ou seja, um número com um componente decimal; você não pode ter 10,5 funcionários!). Use o operador bin (binário) quando tiver uma restrição que deve ser TRUE ou FALSE (ou 1 ou 0).

If you chose a comparison operator in Step 5, in the Constraint box, enter the value by which you want to restrict the cell.
This image shows an example of a completed Add Constraint dialog box. In the example model, this constraint tells Solver to find a solution such that the product profit of the Inflatable Dartboard (cell B12) is equal to 0.

The completed Add Constraint dialog box.

To specify more constraints, click Add and repeat Steps 4 through 6, as needed.

For the example, you add a constraint that asks for the Dog Polisher product profit (cell C12) to be 0.

Click OK.

Excel returns to the Solver Parameters dialog box and displays your constraints in the Subject to the Constraints list box.

Click Solve.

Em qualquer caixa de diálogo Mostrar Solução de Teste que aparecer, clique em Continuar para mover as coisas.
A imagem abaixo mostra o exemplo de solução de ponto de equilíbrio com as restrições adicionadas. Observe que não apenas a célula Lucro total (B14) está definida como 0, mas também as duas células Lucro do produto (B12 e C12).

A caixa de diálogo Resultados do Solver e a solução final para o problema do ponto de equilíbrio.

Selecione a opção Keep Solver Solution.
Se você não quiser aceitar o resultado, selecione a opção Restaurar valores originais.

Clique OK.

Você pode adicionar no máximo 100 restrições. Além disso, se você precisar fazer uma alteração em uma restrição antes de começar a resolvê-la, selecione a restrição na caixa de listagem Assunto para as restrições, clique em Alterar e faça seus ajustes na caixa de diálogo Alterar restrição que aparece. Se você deseja excluir uma restrição de que não precisa mais, selecione a restrição e clique em Excluir.

Salve uma solução Excel Solver como um cenário

Sempre que você tem um modelo de planilha que usa um conjunto coerente de valores de entrada - conhecido como células variáveis - você tem o que o Excel chama de cenário. Com o Solver, essas células variáveis ​​são suas células variáveis, portanto, uma solução Solver equivale a uma espécie de cenário no Excel . No entanto, o Solver não oferece uma maneira fácil de salvar e executar novamente uma solução específica. Para contornar esse problema, você pode salvar uma solução como um cenário que pode ser recuperado posteriormente usando o recurso Gerenciador de cenários do Excel.

Siga estas etapas para salvar uma solução Solver como um cenário:

Escolha Dados → Solver.
O Excel abre a caixa de diálogo Parâmetros do Solver.

Use a caixa Definir objetivo, o grupo Para, a caixa Ao alterar células variáveis ​​e a lista Assunto para as restrições para configurar o Solver conforme descrito acima.

Clique em Resolver.

Sempre que a caixa de diálogo Mostrar solução de teste for exibida, escolha Continuar.
Quando a otimização é concluída, o Excel exibe a caixa de diálogo Resultados do Solver.

Clique em Salvar cenário.
O Excel exibe a caixa de diálogo Salvar cenário.

Na caixa de diálogo Nome do cenário, digite um nome para o cenário e clique em OK.
O Excel retorna à caixa de diálogo Resultados do Solver.

Selecione a opção Keep Solver Solution.
Se você não quiser aceitar o resultado, selecione a opção Restaurar valores originais.

Clique OK.