O que é o Excel Solver?

2024 Autor: Abigail Brown | [email protected]. Última modificação: 2024-01-07 19:05

O suplemento Excel Solver realiza 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 melhor se ajustam 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 complemento Solver está incluído no Excel, mas nem sempre é carregado como parte de uma instalação padrão. Para verificar se está carregado, selecione a aba DATA e procure o ícone Solver na seção Analysis.

Se você não encontrar o Solver na guia DATA, precisará carregar o add-in:

1. Selecione a aba FILE e então selecione Options.

   

2. Na caixa de diálogo Options selecione Add-Ins nas abas do lado esquerdo.

   

3. Na parte inferior da janela, selecione Excel Add-ins na lista suspensa Manage e selecione Go…

   

4. Marque a caixa de seleção ao lado de Solver Add-in e selecione OK.

   

5. O comando Solver deve agora aparecer na guia DATA. 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. 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, como exemplo, vamos fingir que não sabemos como fazer isso.

Crie uma planilha com o raio em B1 e calcule a área em B2 usando a equação =pi()B1^2.

Podemos ajustar manualmente o valor em B1 até que B2 mostre um valor próximo o suficiente de 50. Dependendo da precisão precisa 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 nos valores em determinadas células e verifica o valor em uma célula de destino:

1. Selecione a guia DATA e Solver, para carregar a caixa de diálogo Solver Parameters

2. Defina 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.

   

3. Selecione o botão para Valor de: e defina um valor de 50. Este é o valor que B2 deve alcançar.

   

4. Na caixa intitulada Ao alterar células de variáveis: insira a célula que contém o raio, B1.

   

5. Deixe as outras opções como estão por padrão e selecione Solve. A otimização é realizada, o valor de B1 é ajustado até que B2 seja 50 e o diálogo Resultados do Solver é exibido.

   

6. Selecione OK para manter a solução.

   

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

Ajustando um modelo complexo usando o complemento Excel Solver

Excel tem uma função interna para realizar a 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, consideraremos ajustar uma equação da forma ax^b+cx^d aos dados mostrados abaixo.

Isso envolve os seguintes passos:

1. Organize o conjunto de dados com os valores x na coluna A e os valores y na coluna B.
2. Crie os 4 valores de coeficiente (a, b, c e d) em algum lugar da planilha, eles podem receber valores iniciais arbitrários.

3. Crie uma coluna de valores Y ajustados, usando uma equação da forma ax^b+cx^d que referencia os 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 absolutas enquanto as referências aos valores x devem ser relativas.

   

4. 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, pois estes são valores discretos com ruído, e usar uma linha para a equação ajustada.

   

5. A seguir, precisamos de uma forma de 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 original dos dados para Y é subtraído do valor da equação ajustada e o resultado é elevado ao quadrado. Assim, em D2, o valor é dado por =(C2-B2)^2 A soma de todos esses valores ao quadrado é então calculada. Como os valores são elevados ao quadrado, eles só podem ser positivos.

   

6. Agora você está pronto para realizar a otimização usando o Solver. Há 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 solver, como acima, e defina os parâmetros do solver para referenciar esses valores, conforme mostrado abaixo.

   

7. Desmarque a opção para Tornar variáveis irrestritas não negativas, isso forçaria todos os coeficientes a assumir valores positivos.

   

8. Selecione Resolver e revise 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, tente 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.

   

9. Uma vez obtido um bom ajuste, você pode sair do solver.

Resolvendo um modelo iterativamente

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

1. Faça uma planilha simples com a velocidade, os coeficientes aeb e a potência calculada a partir deles.

   

2. Inicie o Solver e insira o poder, B5, como objetivo. Defina um valor objetivo de 740 e selecione a velocidade, B2, como as células variáveis a serem alteradas. Selecione solve para iniciar a solução.

   

3. O solver ajusta o valor da velocidade até que a potência fique bem próxima de 740, fornecendo a velocidade que precisamos.

   

4. Resolver modelos dessa maneira pode ser mais rápido e menos propenso a erros do que inverter modelos complexos.

Entender as diferentes opções disponíveis no solver pode ser bastante difícil. Se você está tendo dificuldade em obter uma solução sensata, geralmente é útil aplicar condições de limite às células mutáveis. Estes são valores limites 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. Esta seria uma velocidade que você tem certeza que o veículo não pode ir mais rápido do que. Se você conseguir 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 o multistart. Isso executará várias soluções diferentes, começando com valores iniciais diferentes para variáveis.

A escolha do método de resolução também pode ser difícil. O Simplex LP é adequado apenas 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. O 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 geralmente é o mais confiável, mas exige que todas as variáveis tenham limites superiores e inferiores, o que pode ser difícil de resolver com antecedência.

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.