O que é o Excel Solver?

Neste artigo

O suplemento Excel Solver executa a otimização matemática. Isso normalmente é usado para ajustar modelos complexos a dados ou encontrar soluções iterativas para problemas. Por exemplo, você pode querer ajustar uma curva através de alguns pontos de dados, usando uma equação. O Solver pode encontrar as constantes na equação que fornecem o melhor ajuste aos dados. Outra aplicação é onde é difícil reorganizar um modelo para tornar a saída necessária o assunto de uma equação.

Onde está o Solver no Excel?

O suplemento Solver está incluído no Excel, mas nem sempre é carregado como parte de uma instalação padrão. Para verificar se ele está carregado, selecione o DADOS guia e procure o Solver ícone no Análise seção.

Se você não conseguir encontrar o Solver na guia DADOS, precisará carregar o suplemento:

Selecione os ARQUIVO guia e selecione Opções.
No Opções caixa de diálogo selecionar Suplementos nas guias do lado esquerdo.
Na parte inferior da janela, selecione Suplementos Excel de Gerir lista suspensa e selecione Vai…
Marque a caixa de seleção ao lado de Suplemento Solver e selecione OK.
O Solver comando agora deve aparecer no DADOS aba. Você está pronto para usar o Solver.

Usando o Solver no Excel

Vamos começar com um exemplo simples para entender o que o Solver faz. Imagine que queremos saber qual raio dará um círculo com uma área de 50 unidades quadradas. Nós conhecemos a equação para a área de um círculo (A = pi r²) Poderíamos, é claro, reorganizar essa equação para fornecer o raio necessário para uma determinada área, mas para fins de exemplo, vamos fingir que não sabemos como fazer isso. Crie uma planilha com o raio em B1 e calcular a área em B2 usando a equação = pi () * B1 ^ 2.

Poderíamos ajustar manualmente o valor em B1 até B2 mostra um valor próximo de 50. Dependendo de quão precisos precisamos ser, esta pode ser uma abordagem prática. No entanto, se precisarmos ser muito exatos, levará muito tempo para fazer os ajustes necessários. Na verdade, isso é essencialmente o que o Solver faz. Ele faz ajustes aos valores em certas células e verifica o valor em uma célula-alvo:

Selecione DADOS guia e Solver, para carregar o Parâmetros do Solver caixa de diálogo
Definir objetivo célula para ser a área, B2. Este é o valor que será verificado, ajustando as demais células até que esta atinja o valor correto.
Selecione o botão para Valor de: e defina um valor de 50. Este é o valor que B2 deve atingir.
Na caixa intitulada Alterando células variáveis: insira a célula que contém o raio, B1.
Deixe as outras opções como estão por padrão e selecione Resolver. A otimização é realizada, o valor de B1 é ajustado até que B2 seja 50 e o Resultados do Solver diálogo é exibido.
Selecione OK para manter a solução.

Este exemplo simples mostrou como funciona o solucionador. Nesse caso, poderíamos ter obtido mais facilmente a solução de outras maneiras. A seguir, veremos alguns exemplos em que o Solver fornece soluções que seriam difíceis de encontrar de outra maneira.

Ajustando um modelo complexo usando o complemento Excel Solver

O Excel possui uma função incorporada para realizar regressão linear, ajustando uma linha reta através de um conjunto de dados. Muitas funções não lineares comuns podem ser linearizadas, o que significa que a regressão linear pode ser usada para ajustar funções como exponenciais. Para funções mais complexas, o Solver pode ser usado para realizar uma ‘minimização de mínimos quadrados’. Neste exemplo, vamos considerar o ajuste de uma equação da forma ax ^ b + cx ^ d aos dados mostrados abaixo.

Isso envolve as seguintes etapas:

Organize o conjunto de dados com os valores x na coluna A e os valores y na coluna B.
Crie os 4 valores de coeficiente (a, b, c e d) em algum lugar da planilha, eles podem receber valores iniciais arbitrários.
Crie uma coluna de valores Y ajustados, usando uma equação da forma ax ^ b + cx ^ d que faz referência aos coeficientes criados na etapa 2 e os valores x na coluna A. Observe que, para copiar a fórmula para baixo na coluna, as referências aos coeficientes devem ser absolutos, enquanto as referências aos valores x devem ser relativas.
Embora não seja essencial, você pode obter uma indicação visual de quão bom é o ajuste da equação plotando ambas as colunas y contra os valores x em um único gráfico de dispersão XY. Faz sentido usar marcadores para os pontos de dados originais, uma vez que são valores discretos com ruído, e usar uma linha para a equação ajustada.
Em seguida, precisamos quantificar a diferença entre os dados e nossa equação ajustada. A maneira padrão de fazer isso é calcular a soma das diferenças quadradas. Em uma terceira coluna, para cada linha, o valor dos dados originais para Y é subtraído do valor da equação ajustada e o resultado é elevado ao quadrado. Então, em D2, o valor é dado por = (C2-B2) ^ 2. A soma de todos esses valores quadrados é então calculada. Como os valores são elevados, eles só podem ser positivos.
Agora você está pronto para realizar a otimização usando o Solver. Existem quatro coeficientes que precisam ser ajustados (a, b, c e d). Você também tem um único valor objetivo para minimizar, a soma das diferenças quadradas. Inicie o solucionador, como acima, e defina os parâmetros do solucionador para fazer referência a esses valores, conforme mostrado abaixo.
Desmarque a opção de Tornar as variáveis irrestritas não negativas, isso forçaria todos os coeficientes a assumirem valores positivos.
Selecione Resolver e analise os resultados. O gráfico será atualizado dando uma boa indicação da qualidade do ajuste. Se o solucionador não produzir um bom ajuste na primeira tentativa, você pode tentar executá-lo novamente. Se o ajuste melhorou, tente resolver a partir dos valores atuais. Caso contrário, você pode tentar melhorar manualmente o ajuste antes de resolver.
Depois de obter um bom ajuste, você pode sair do solucionador.

Resolvendo um modelo iterativamente

Às vezes, há uma equação relativamente simples que fornece uma saída em termos de alguma entrada. Porém, quando tentamos inverter o problema, não é possível encontrar uma solução simples. Por exemplo, a energia consumida por um veículo é aproximadamente fornecida por P = av + bv ^ 3 onde v é a velocidade, a é um coeficiente para a resistência ao rolamento eb é um coeficiente para o arrasto aerodinâmico. Embora esta seja uma equação bastante simples, não é fácil reorganizá-la para fornecer uma equação da velocidade que o veículo alcançará para uma dada entrada de potência. Podemos, no entanto, usar o Solver para encontrar iterativamente essa velocidade. Por exemplo, encontre a velocidade atingida com uma entrada de energia de 740 W.

Configure uma planilha simples com a velocidade, os coeficientes aeb e a potência calculada a partir deles.
Inicie o Solver e entre no poder, B5, como o objetivo. Defina um valor objetivo de 740 e selecione a velocidade, B2, como as células variáveis a serem alteradas. Selecione resolver para iniciar a solução.
O solucionador ajusta o valor da velocidade até que a potência esteja muito próxima de 740, fornecendo a velocidade de que precisamos.
Resolver modelos dessa maneira pode muitas vezes ser mais rápido e menos sujeito a erros do que inverter modelos complexos.

Compreender as diferentes opções disponíveis no solucionador pode ser bastante difícil. Se você está tendo dificuldade em obter uma solução sensata, geralmente é útil aplicar condições de contorno às células mutáveis. Esses são valores limitantes além dos quais não devem ser ajustados. Por exemplo, no exemplo anterior, a velocidade não deve ser menor que zero e também seria possível definir um limite superior. Essa seria uma velocidade que você tem certeza de que o veículo não pode ir mais rápido do que. Se você puder definir limites para as células variáveis variáveis, isso também fará com que outras opções mais avançadas funcionem melhor, como multistart. Isso executará várias soluções diferentes, começando com valores iniciais diferentes para as variáveis. Escolher o método de resolução também pode ser difícil. O Simplex LP só é adequado para modelos lineares, se o problema não for linear ele falhará com uma mensagem de que esta condição não foi atendida. Os outros dois métodos são adequados para métodos não lineares. GRG Nonlinear é o mais rápido, mas sua solução pode ser altamente dependente das condições iniciais de partida. Ele tem a flexibilidade de não exigir que as variáveis tenham limites definidos. O solucionador evolucionário costuma ser o mais confiável, mas requer que todas as variáveis tenham limites superior e inferior, o que pode ser difícil de calcular antecipadamente. O suplemento Excel Solver é uma ferramenta muito poderosa que pode ser aplicada a muitos problemas práticos. Para acessar totalmente o poder do Excel, tente combinar o Solver com macros do Excel.