.
O Excel é uma ferramenta poderosa quando seus dados estão completos. Mas não seria bom se pudesse resolver para variáveis desconhecidas?
Com o Goal Seek e o suplemento Solver, pode. E vamos mostrar-lhe como. Continue lendo para obter um guia completo sobre como resolver uma única célula com o Goal Seek ou uma equação mais complicada com o Solver.
Como usar a busca de meta no Excel
O Goal Seek já está embutido no Excel. está sob o Dados guia, no Análise hipotética cardápio:
Para este exemplo, usaremos um conjunto muito simples de números. Temos três trimestres de números de vendas e uma meta anual. Podemos usar o Atingir meta para descobrir quais números precisam ser no quarto trimestre para fazer a meta.
Como você pode ver, o total de vendas atual é de 114.706 unidades. Se queremos vender 250.000 até o final do ano, quanto precisamos vender no quarto trimestre? O Goal Seek do Excel nos dirá.
Veja como usar o Goal Seek, passo a passo:
- Clique em Dados > Análise de variações hipotéticas > Atingir meta. A janela Atingir meta será exibida.
- Coloque a parte “igual” da sua equação no Definir célula campo. Este é o número que o Excel tentará otimizar. Em nosso caso, é o total acumulado de nossos números de vendas na célula A5.
- Digite o valor da meta no Dar valor campo. Estamos procurando um total de 250.000 unidades vendidas, então colocaremos “250.000” neste campo.
- Diga ao Excel qual variável resolver no Ao mudar de célula campo. Queremos ver o que nossas vendas no quarto trimestre precisam ser. Então, vamos dizer ao Excel para resolver a célula D2. Vai ficar assim quando estiver pronto para ir:
- Bater OK para resolver seu objetivo. Quando parece bom, basta clicar OK. O Excel informará quando o Atingir meta encontrar uma solução.
- Clique OK novamente, e você verá o valor que resolve sua equação na célula que você escolheu para Ao mudar de célula.
No nosso caso, a solução é de 135.294 unidades. É claro que poderíamos ter encontrado isso apenas subtraindo o total acumulado da meta anual. Mas o Goal Seek também pode ser usado em uma célula que já tem dados nele. E isso é mais útil.
Observe que o Excel substitui nossos dados anteriores. É uma boa ideia execute o Goal Seek em uma cópia de seus dados. Também é uma boa ideia anotar nos dados copiados que eles foram gerados usando o Goal Seek. Você não quer confundi-lo com dados atuais e precisos.
Portanto, o Goal Seek é uma das funções mais úteis do Excel, mas não é tão impressionante. Você só pode usá-lo em uma única célula por vez. Se você quiser usar o Goal Seek do Excel em várias células simultaneamente, precisará de uma ferramenta muito mais poderosa. Felizmente, uma dessas ferramentas vem com o Excel. Vamos dar uma olhada no suplemento Solver.
O que o Solver do Excel faz?
Resumindo, o Solver é como um versão multivariada do Goal Seek. Se você estava se perguntando como usar o Goal Seek no Excel para várias células ao mesmo tempo, é isso. Ele pega uma variável de objetivo e ajusta uma série de outras variáveis até obter a resposta que você deseja.
Ele pode resolver um valor máximo de um número, um valor mínimo de um número ou um número exato. E funciona dentro de restrições, portanto, se uma variável não puder ser alterada ou variar apenas dentro de um intervalo especificado, o Solver levará isso em consideração.
É uma ótima maneira de resolver várias variáveis desconhecidas no Excel. Mas encontrá-lo e usá-lo não é simples. Vamos dar uma olhada no carregamento do suplemento Solver e, em seguida, ver como usar o Solver na versão atual do Microsoft 365 do Excel.
Como carregar o suplemento Solver
O Excel não possui o Solver por padrão. É um suplemento, então você deve carregá-lo primeiro. Felizmente, ele já está no seu computador.
Dirigir a Arquivo > ( Mais… >) Opções > Suplementos. Em seguida, clique em Ir ao lado de Gerenciar: Suplementos do Excel. Se esta lista suspensa disser algo diferente de “Suplementos do Excel”, você precisará alterá-la:
Na janela resultante, você verá algumas opções. Certifique-se de que a caixa ao lado Suplemento Solver é verificado e atingido OK.
Agora você verá o Solucionador botão no Análise grupo do Dados aba:
Se você já estiver usando o pacote de ferramentas de análise de dados, verá o botão Análise de dados. Caso contrário, o Solver aparecerá sozinho. Agora que você carregou o suplemento, vamos ver como usá-lo.
Como usar o Solver no Excel
Existem três partes em qualquer ação do Solver: o objetivo, as células variáveis e as restrições. Vamos percorrer cada uma das etapas.
- Clique Dados > Solucionador. Você verá a janela Parâmetros do Solver abaixo. (Se você não vir o botão Solver, consulte a seção anterior sobre como carregar o suplemento Solver.)
- Defina o objetivo da sua célula e diga ao Excel o seu objetivo. O objetivo está na parte superior da janela do Solver e possui duas partes: a célula do objetivo e uma opção de maximizar, minimizar ou um valor específico.
Se você selecionar máx.o Excel ajustará suas variáveis para obter o maior número possível em sua célula de objetivo. mín. é o oposto: o Solver minimizará o número objetivo. Valor de permite especificar um número específico para o Solver procurar.
- Escolha as células variáveis que o Excel pode alterar. As células variáveis são definidas com o Mudando as Células Variáveis campo. Clique na seta ao lado do campo e, em seguida, clique e arraste para selecionar as células com as quais o Solver deve trabalhar. Note que estes são todas as células isso pode variar. Se você não deseja que uma célula seja alterada, não a selecione.
- Defina restrições em variáveis múltiplas ou individuais. Finalmente, chegamos às restrições. É aqui que o Solver é realmente poderoso. Em vez de alterar qualquer uma das células variáveis para qualquer número desejado, você pode especificar as restrições que devem ser atendidas. Para obter detalhes, consulte a seção sobre como definir restrições abaixo.
- Depois que todas essas informações estiverem no lugar, clique em Resolver para obter sua resposta. O Excel atualizará seus dados para incluir as novas variáveis (é por isso que recomendamos que você crie uma cópia de seus dados primeiro).
Você também pode gerar relatórios, que veremos brevemente em nosso exemplo de Solver abaixo.
Como definir restrições no Solver
Você pode dizer ao Excel que uma variável deve ser maior que 200. Ao tentar diferentes valores de variáveis, o Excel não ficará abaixo de 201 com aquela variável em particular.
Para adicionar uma restrição, clique no botão Adicionar botão ao lado da lista de restrições. Você terá uma nova janela. Selecione a célula (ou células) a serem restringidas no Referência de Célula campo e escolha um operador.
Aqui estão os operadores disponíveis:
- <= (menos que ou igual a)
- = (igual a)
- => (Melhor que ou igual a)
- int (deve ser um inteiro)
- lixeira (deve ser 1 ou 0)
- Todos Diferentes
Todos Diferentes é um pouco confuso. Ele especifica que cada célula no intervalo que você selecionar para Referência de Célula deve ser um número diferente. Mas também especifica que eles devem estar entre 1 e o número de células.
Portanto, se você tiver três células, terminará com os números 1, 2 e 3 (mas não necessariamente nessa ordem). Finalmente, adicione o valor para a restrição.
É importante lembrar que você pode selecione várias células para Referência de Célula. Se você quiser que seis variáveis tenham valores acima de 10, por exemplo, você pode selecionar todas elas e dizer ao Solver que elas devem ser maiores ou iguais a 11. Você não precisa adicionar uma restrição para cada célula.
Você também pode usar a caixa de seleção na janela principal do Solver para garantir que todos os valores para os quais você não especificou restrições sejam não negativos. Se você deseja que suas variáveis fiquem negativas, desmarque esta caixa.
Um exemplo de solucionador
Para ver como tudo isso funciona, usaremos o suplemento Solver para fazer um cálculo rápido. Aqui estão os dados com os quais estamos começando:
Nela, temos cinco empregos diferentes, cada um pagando uma taxa diferente. Também temos o número de horas que um trabalhador teórico trabalhou em cada um desses empregos em uma determinada semana.
Podemos usar o suplemento Solver para descobrir como maximizar o pagamento total, mantendo certas variáveis dentro de algumas restrições. Aqui estão as restrições que usaremos:
- Sem empregos pode cair abaixo de quatro horas.
- O trabalho 4 deve ser superior a 12 horas.
- O trabalho 5 deve ser menos de onze horas.
- O total de horas trabalhadas deve ser igual a 40.
Pode ser útil escrever suas restrições assim antes de usar o Solver. Veja como configuraríamos isso no Solver:
Primeiro, observe que Eu criei uma cópia da tabelapara não substituirmos o original, que contém nosso horário de trabalho atual.
Em segundo lugar, observe que os valores nas restrições de maior e menor que são um superior ou inferior do que mencionei acima. Isso ocorre porque não há opções de maior ou menor. Existem apenas maior que ou igual a e menor que ou igual a.
vamos bater Resolver e veja o que acontece.
Solver encontrou uma solução! Como você pode ver à esquerda da janela acima, nossos ganhos aumentaram em US$ 152. E todas as restrições foram atendidas.
Para manter os novos valores, certifique-se Keep Solver Solution é verificado e atingido OK. Porém, se você quiser mais informações, pode selecionar um relatório no lado direito da janela. Selecione todos os relatórios que deseja, diga ao Excel se deseja que eles sejam descritos (recomendo) e clique em OK.
Os relatórios são gerados em novas planilhas em sua pasta de trabalho e fornecem informações sobre o processo pelo qual o suplemento Solver passou para obter sua resposta.
No nosso caso, os relatórios não são muito empolgantes e não há muitas informações interessantes neles. Mas se você executar uma equação do Solver mais complicada, poderá encontrar algumas informações de relatório úteis nessas novas planilhas. Basta clicar no + botão ao lado de qualquer relatório para obter mais informações:
Opções Avançadas do Solver
Se você não sabe muito sobre estatísticas, pode ignorar as opções avançadas do Solver e apenas executá-lo como está. Mas se você estiver executando cálculos grandes e complexos, talvez queira analisá-los.
O mais óbvio é o método de resolução:
Você pode escolher entre GRG Nonlinear, Simplex LP e Evolutionary. O Excel fornece uma explicação simples sobre quando você deve usar cada um. Uma explicação melhor requer algum conhecimento de estatística e regressão do Excel.
Para ajustar configurações adicionais, basta clicar no botão Opções botão. Você pode informar ao Excel sobre otimização de número inteiro, definir restrições de tempo de cálculo (útil para conjuntos de dados massivos) e ajustar como os métodos de solução GRG e Evolucionário fazem seus cálculos.
Novamente, se você não sabe o que isso significa, não se preocupe. Se você quiser saber mais sobre qual método de solução usar, o Engineer Excel tem um bom artigo que explica isso para você. Se você deseja precisão máxima, o Evolutionary é provavelmente um bom caminho a percorrer. Apenas esteja ciente de que isso levará muito tempo.
Atingir Meta e Solucionar: Levando o Excel para o Próximo Nível
Agora que você está familiarizado com os fundamentos da resolução de variáveis desconhecidas no Excel, um mundo inteiramente novo de cálculo de planilhas está aberto para você. O Goal Seek pode ajudá-lo a economizar tempo fazendo alguns cálculos mais rapidamente, e o Solver adiciona uma enorme quantidade de poder às habilidades de cálculo do Excel.
É apenas uma questão de se sentir confortável com eles. Quanto mais você os usar, mais úteis eles se tornarão.
.
