Como usar o Excel 2019 Solver

Embora os comandos Data Table e Goal Seek do Excel funcionem bem para problemas simples que requerem a determinação da relação direta entre as entradas e os resultados em uma fórmula, você precisa usar o suplemento Solver ao lidar com problemas mais complexos. Por exemplo, use o Solver para encontrar a melhor solução quando precisar alterar vários valores de entrada em seu modelo do Excel e precisar impor restrições a esses valores e / ou ao valor de saída.

O suplemento Solver funciona aplicando métodos iterativos para encontrar a “melhor” solução de acordo com as entradas, a solução desejada e as restrições que você impõe. A cada iteração, o programa aplica um método de tentativa e erro (baseado no uso de equações e desigualdades lineares ou não lineares) que tenta se aproximar da solução ótima.

Ao usar o suplemento Solver, lembre-se de que muitos problemas, especialmente os mais complicados, têm muitas soluções. Embora o Solver retorne a solução ideal, dados os valores iniciais, as variáveis ​​que podem mudar e as restrições que você define, essa solução geralmente não é a única possível e, na verdade, pode não ser a melhor solução para você. Para ter certeza de que está encontrando a melhor solução, você pode executar o Solver mais de uma vez, ajustando os valores iniciais a cada vez que resolver o problema.

Ao configurar o problema para o suplemento Solver em sua planilha do Excel, defina os seguintes itens:

• Célula objetivo: a célula- alvo em sua planilha cujo valor deve ser maximizado, minimizado ou feito para atingir um determinado valor. Observe que esta célula deve conter uma fórmula.
• Células variáveis: as células variáveis ​​em sua planilha, cujos valores devem ser ajustados até que a resposta seja encontrada.
• Células de restrição: as células que contêm os limites que você impõe aos valores variáveis ​​nas células variáveis ​​e / ou na célula alvo na célula objetivo.

Depois de terminar de definir o problema com esses parâmetros e ter o suplemento Solver resolvendo o problema, o programa retorna a solução ideal, modificando os valores em sua planilha. Neste ponto, você pode escolher reter as alterações na planilha ou restaurar os valores originais na planilha. Você também pode salvar a solução como um cenário para ver mais tarde, antes de restaurar os valores originais.

Você pode usar o suplemento Solver com o Scenario Manager para ajudar a definir um problema a ser resolvido ou para salvar uma solução para que possa visualizá-la posteriormente. As células variáveis ​​que você define para o Scenario Manager são automaticamente selecionadas e usadas pelo Solver quando você seleciona este comando e vice-versa. Além disso, você pode salvar a solução do Solver para um problema como um cenário (clicando no botão Salvar cenário na caixa de diálogo Solver) que pode ser visualizado com o Gerenciador de cenários.

Configurando e definindo o problema no Excel 2019

A primeira etapa na configuração de um problema para o Solver trabalhar é criar o modelo de planilha para o qual você definirá a célula objetivo, as células variáveis ​​e as células de restrição.

Lembre-se de que o Solver é um utilitário complementar. Isso significa que, antes de poder usá-lo, você precisa ter certeza de que o programa de suplemento Solver ainda está carregado, conforme indicado pela aparência do botão Solver no grupo Análise no final da guia Dados na Faixa de Opções. Se este botão estiver faltando, você pode carregar o Solver abrindo a guia Suplementos da caixa de diálogo Opções do Excel (Alt + FTAA) e clicando no botão Ir depois de verificar se Suplementos do Excel são exibidos no menu suspenso Gerenciar caixa de listagem imediatamente à esquerda. Em seguida, selecione a caixa de seleção Solver Add-in na caixa de diálogo Add-Ins para colocar uma marca de seleção antes de clicar em OK para fechar a caixa de diálogo e recarregar o add-in.

Para definir e resolver um problema com o suplemento Solver depois de carregar o suplemento e criar seu modelo de planilha, siga estas etapas:

Clique no botão de comando Solver no grupo Analisar no final da guia Dados da Faixa de Opções.

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

Especificando os parâmetros a serem aplicados ao modelo na caixa de diálogo Parâmetros do Solver.

Clique na célula de destino na planilha ou insira sua referência de célula ou nome de intervalo na caixa de texto Definir objetivo.

Em seguida, você precisa selecionar a configuração Para. Clique no botão de opção Máx quando desejar que o valor da célula de destino seja o maior possível. Clique no botão de opção Mínimo quando quiser que o valor da célula-alvo seja o menor possível. Clique no botão de opção Valor de e insira um valor na caixa de texto associada quando desejar que o valor da célula de destino alcance um determinado valor.

Clique no botão de opção apropriado na seção Para da caixa de diálogo. Se você selecionar o botão de opção Valor de, insira o valor correspondente na caixa de texto associada.

Em seguida, designe as células variáveis ​​- ou seja, aquelas que o Solver pode alterar para atingir sua meta Igual a.

Clique na caixa de texto Alterando células variáveis ​​e selecione as células a serem alteradas na planilha ou insira suas referências de célula ou nome de intervalo na caixa de texto.

Lembre-se de que, para selecionar células não adjacentes na planilha, você precisa manter pressionada a tecla Ctrl ao clicar em cada célula na seleção. Para que o Excel escolha as células de alteração para você com base na célula de destino que você selecionou, clique no botão Adivinhar à direita desta caixa de texto.

Antes de o Solver ajustar seu modelo, você pode adicionar restrições para a célula-alvo ou qualquer uma das células em mudança que determinam seus limites ao ajustar os valores.

(Opcional) Clique no botão Adicionar à direita da caixa de listagem Assunto para as restrições na caixa de diálogo Parâmetros do Solver.

Esta ação abre a caixa de diálogo Adicionar restrição. Ao definir uma restrição, escolha a célula cujo valor deseja restringir ou selecione a célula na planilha ou insira sua referência de célula na caixa de texto Referência de célula. Em seguida, selecione a relação (=, <=,> = ou int para inteiro ou bin para binário) na caixa de lista suspensa à direita e (a menos que você escolha int ou bin ) insira o valor apropriado ou referência de célula no Caixa de texto de restrição.

Para continuar adicionando restrições para outras células usadas pelo Solver, clique no botão Adicionar para adicionar a restrição e desmarque as caixas de texto na caixa de diálogo Adicionar restrição. Em seguida, repita a Etapa 5 para adicionar uma nova restrição. Depois de terminar de definir as restrições para a célula de destino e alterar os valores no modelo, clique em OK para fechar a caixa de diálogo Adicionar Restrição e retornar à caixa de diálogo Parâmetros do Solver (que agora lista suas restrições na caixa de listagem Assunto para as Restrições).

(Opcional) Desmarque a caixa de seleção Tornar variáveis ​​irrestritas não negativas se quiser permitir valores negativos quando as células variáveis ​​não estiverem sujeitas a restrições.
Por padrão, o Solver Add-in emprega o método GRG (Generalized Reduced Gradient) não linear para resolver o modelo cujos parâmetros você está definindo, conhecido como uma maneira muito eficiente de resolver problemas não lineares suaves. Para usar o método LP Simplex (para Programação Linear seguindo o algoritmo Simplex) ou mecanismo Evolucionário para resolver problemas não suaves, você precisa seguir a Etapa 7.

(Opcional) Selecione LP Simplex ou Evolutionary na lista suspensa Select a Solving Method para usar qualquer um desses métodos para resolver problemas não suaves.

Clique no botão Solve para que o Solver resolva o problema conforme você o definiu na caixa de diálogo Parâmetros do Solver.

Solving the problem with Excel’s Solver

When you click the Solve button, the Solver Parameters dialog box disappears, and the status bar indicates that the Solver is setting up the problem and then keeps you informed of the progress in solving the problem by showing the number of the intermediate (or trial) solutions as they are tried. To interrupt the solution process at any time before Excel calculates the last iteration, press the Esc key. Excel then displays the Show Trial Solution dialog box, informing you that the solution process has been paused. To continue the solution process, click the Continue button. To abort the solution process, click the Stop button.

When Excel finishes the solution process, the Solver Results dialog box appears. This dialog box informs you whether the Solver was able to find a solution, given the target cell, changing cells, and constraints defined for the problem. To retain the changes that the Solver makes in your worksheet model, leave the Keep Solver Solution option button selected and click OK to close the Solver Results dialog box. To return the original values to the worksheet, click the Restore Original Values option button instead. To save the changes as a scenario before you restore the original values, click the Save Scenario button and assign a name to the current scenario before you click the Restore Original Values option and OK button.

The Solver Results dialog box showing that Solver found a solution to the problem.

Unlike when using the Goal Seek command, after clicking the Keep Solver Solution option button in the Solver Results dialog box, you can’t use the Undo command button on the Quick Access toolbar to restore the original values to your worksheet. If you want to be able to switch between the “before” and “after” views of your worksheet, you must save the changes with the Save Scenario button and then select the Restore Original Values option button. That way, you can retain the “before” view in the original worksheet and use the Scenario Manager to display the “after” view created by the Solver.

Changing Excel’s Solver options

For most of the problems, the default options used by the Solver are adequate. In some situations, however, you may want to change some of the Solver options before you begin the solution process. To change the solution options, click the Options button in the Solver Parameters dialog box. Excel then opens the Options dialog box with the All Methods tab selected where you can make all necessary changes.

Modifying the solution options in the Options dialog box.

Excel 2019 Solver Option Settings

After changing the options, click OK to return to the Solver Parameters dialog box; from here, you can then click the Solve button to begin the solution process with the new solution settings that you just changed.

When you use the default GRG (Generalized Reduced Gradient) Nonlinear or Evolutionary method, you can set additional Solver settings using the options on the GRG Nonlinear and Evolutionary tabs of the Options dialog box. These options include changing the Converge, Population Size, and Random Seed settings for either of these particular methods.

Saving and loading a model problem in Excel 2019

The objective cell, variable cells, constraint cells, and Solver options that you most recently used are saved as part of the Excel worksheet when you click the Save button on the Quick Access toolbar (Ctrl+S). When you define other problems for the same worksheet that you want to save, you must click the Save Model button in the Solver Options dialog box and indicate the cell reference or name of the range in the active worksheet where you want the problem’s parameters to be inserted.

Quando você clica no botão Carregar / Salvar, o Excel abre a caixa de diálogo Carregar / Salvar modelo, contendo uma caixa de texto Selecionar área do modelo. Esta caixa de texto contém as referências de células para um intervalo grande o suficiente para conter todos os parâmetros do problema, começando com a célula ativa. Para salvar os parâmetros do problema neste intervalo, clique em OK. Se este intervalo incluir células com dados existentes, você precisará modificar a referência de célula nesta caixa de texto antes de clicar em OK para evitar que o Excel substitua os dados existentes.

Depois de clicar em OK, o Excel copia os parâmetros do problema no intervalo especificado. Esses valores são salvos como parte da planilha na próxima vez que você salvar a pasta de trabalho. Para reutilizar esses parâmetros de problema ao resolver um problema, você simplesmente precisa abrir a caixa de diálogo Opções do Solver, clicar no botão Carregar / Salvar para abrir a caixa de diálogo Carregar / Salvar modelo, clicar no botão Carregar e selecionar o intervalo que contém os dados salvos parâmetros de problema. Quando você clica em OK na caixa de diálogo Carregar modelo, o Excel carrega os parâmetros desse intervalo de células nas caixas de texto apropriadas na caixa de diálogo Parâmetros do Solver. Você pode então fechar a caixa de diálogo Opções do Solver clicando em OK e pode resolver o problema usando esses parâmetros clicando no botão de comando Solve.

Lembre-se de que você pode usar o botão Redefinir tudo sempre que desejar limpar todos os parâmetros definidos para o problema anterior e retornar as opções do Solver aos seus padrões.

Criação de relatórios do Solver no Excel 2019

Você pode criar três tipos diferentes de relatórios com o Solver:

• Relatório de resposta: Lista a célula-alvo e as células em mudança com seus valores originais e finais, junto com as restrições usadas na solução do problema.
• Relatório de sensibilidade: indica o quão sensível é uma solução ideal para mudanças nas fórmulas que calculam a célula-alvo e restrições. O relatório mostra as células em mudança com seus valores finais e o gradiente reduzido para cada célula. (O gradiente reduzido mede o objetivo por aumento de unidade na célula em mudança.) Se você definiu restrições, o relatório de Sensibilidade as lista com seus valores finais e o multiplicador de Lagrange para cada restrição. (O multiplicador de Lagrange mede o objetivo por aumento de unidade que aparece no lado direito da equação de restrição.)
• Relatório de limites: mostra a célula-alvo e as células em mudança com seus valores, limites inferior e superior e resultados-alvo. O limite inferior representa o valor mais baixo que uma célula em mudança pode ter enquanto fixa os valores de todas as outras células e ainda satisfaz as restrições. O limite superior representa o valor mais alto que fará isso.

O Excel coloca cada relatório gerado para um problema do Solver em uma planilha separada na pasta de trabalho. Para gerar um (ou todos) desses relatórios, selecione o tipo de relatório (Resposta, Sensibilidade ou Limites) na caixa de listagem Relatórios da caixa de diálogo Resultados do Solver. Para selecionar mais de um relatório, basta clicar no nome do relatório.

Quando você clica em OK para fechar a caixa de diálogo Resultados do Solver (após escolher entre as opções Manter Solução do Solver e Restaurar Valores Originais), o Excel gera o relatório (ou relatórios) que você selecionou em uma nova planilha que adiciona ao início da pasta de trabalho . (As guias da folha de relatório são nomeadas por tipo de relatório, como no Relatório de resposta 1, Relatório de sensibilidade 1 e Relatório de limites 1. )