Como usar a solução de busca e solução de metas do Excel para resolver variáveis desconhecidas

O Excel é muito capaz quando você tem todos os dados de que precisa para seus cálculos.

Mas não seria bom se pudesse resolver para variáveis desconhecidas ?

Com o Goal Seek e o suplemento Solver, isso pode acontecer. E nós mostraremos 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 meta de busca no Excel

Goal Seek já está embutido no Excel. Está sob o Dados guia, no Análise de variações hipotéticas cardápio:

Para este exemplo, usaremos um conjunto muito simples de números. Temos o valor de três quartos de números de vendas e uma meta anual. Podemos usar o Goal Seek para descobrir quais números precisam estar no quarto trimestre para atingir a meta.

Como você pode ver, o total de vendas atual é de 114.706 unidades. Se quisermos vender 250.000 até o final do ano, quantos precisamos vender no quarto trimestre? O Goal Seek do Excel nos dirá.

Veja como usar o Goal Seek, passo a passo:

Clique Dados> Análise de variações hipotéticas> Atingir meta . Você verá esta janela:
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 B5.
Digite o valor do seu objetivo 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 Mudando de célula campo. Queremos ver quais devem ser nossas vendas no quarto trimestre. Então, vamos dizer ao Excel para resolver para a célula D2. Ele ficará assim quando estiver pronto para uso:
Bater OK para resolver seu objetivo. Quando estiver bom, basta clicar OK . O Excel avisará você quando o Goal Seek encontrar uma solução.
Clique OK novamente e você verá o valor que resolve sua equação na célula que você escolheu para Mudando de célula .

Em nosso caso, a solução é 135.294 unidades. Claro, poderíamos apenas ter descoberto isso subtraindo o total corrente da meta anual. Mas 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.

como você obtém carma no reddit

Portanto, a busca de metas é um recurso útil do Excel , mas não é tão impressionante. Vamos dar uma olhada em uma ferramenta que é muito mais interessante: o suplemento Solver.

O que o Solver do Excel faz?

Resumindo, o Solver é como um versão multivariada de Goal Seek . Ele pega uma variável de objetivo e ajusta várias outras variáveis até obter a resposta desejada.

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 com restrições, portanto, se uma variável não puder ser alterada, ou apenas puder variar 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 em como carregar o suplemento Solver e, em seguida, ver como usar o Solver no Excel 2016.

Como carregar o suplemento Solver

O Excel não tem Solver por padrão. É um suplemento, portanto, como outros recursos poderosos do Excel, você precisa carregá-lo primeiro. Felizmente, já está no seu computador.

Dirigir a Arquivo> Opções> Suplementos . Então clique em Ir ao lado de Gerenciar: Suplementos do Excel .

Se esta lista suspensa disser algo diferente de 'Suplementos do Excel', você precisará alterá-lo:

Na janela resultante, você verá algumas opções. Certifique-se de que a caixa ao lado de Suplemento Solver é verificado e bateu OK .

Agora você verá o Solver botão no Análise grupo do Dados aba:

Se você já usa o Data Analysis Toolpak , você verá o botão Análise de dados. Caso contrário, o Solver aparecerá sozinho.

Agora que você carregou o suplemento, vamos dar uma olhada em 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. Percorreremos cada uma das etapas.

Clique Dados> Solver . 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 informe o seu objetivo ao Excel. O objetivo está na parte superior da janela do Solver e tem duas partes: a célula 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 objetivo. Min é o oposto: o Solver minimizará o número do objetivo. Valor de permite que você especifique 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 Alterando 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. Observe que estes são todas as células isso pode variar. Se você não deseja que uma célula mude, 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 que desejar, 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, pressione 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 do Solver abaixo.

Como definir restrições no Solver

Você pode dizer ao Excel que uma variável deve ser maior que 200. Ao tentar valores de variáveis diferentes, o Excel não ficará abaixo de 201 com essa variável em particular.

Para adicionar uma restrição, clique no Adicionar botão próximo à lista de restrições. Você verá uma nova janela. Selecione a célula (ou células) a ser restringida no Referência de Célula campo e, em seguida, 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 número inteiro)
sou (deve ser 1 ou 0)
AllDifferent

AllDifferent é um pouco confuso. Ele especifica que cada célula no intervalo que você seleciona 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, acabará com os números 1, 2 e 3 (mas não necessariamente nessa ordem)

Finalmente, adicione o valor da 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 certificar-se de que todos os valores para os quais você não especificou restrições não são negativos. Se você quiser que suas variáveis sejam negativas, desmarque esta caixa.

Um exemplo de Solver

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:

Nele, temos cinco empregos diferentes, cada um deles com 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 para menos de quatro horas.
Trabalho 2 deve ser mais de oito horas .
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 tabela portanto, não substituímos o original, que contém nosso horário de trabalho atual.

E, em segundo lugar, veja que os valores nas restrições maior que e menor que são um superior ou inferior do que o que mencionei acima. Isso porque não há opções maior ou menor. Existem apenas maior ou igual a e menor ou igual a.

Vamos bater Resolver e veja o que acontece.

O Solver encontrou uma solução! Como você pode ver à esquerda da janela acima, nossos ganhos aumentaram em $ 130. E todas as restrições foram atendidas.

computador não carrega quando conectado

Para manter os novos valores, certifique-se de Solução Keep Solver é verificado e bateu OK .

Se você quiser mais informações, pode selecionar um relatório do lado direito da janela. Selecione todos os relatórios que deseja, diga ao Excel se deseja que eles sejam delineados (eu recomendo) e clique 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. 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 na lateral 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, pode querer dar uma olhada neles.

O mais óbvio é o método de soluçã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.

Para ajustar as configurações adicionais, basta clicar no Opções botão. Você pode informar ao Excel sobre a otimização de inteiros, 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 Evolutionary 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 Engenheiro Excel tem um bom artigo que descreve tudo para você . Se você deseja precisão máxima, o Evolutionary é provavelmente um bom caminho a seguir. Esteja ciente de que isso levará muito tempo.

Atingir e resolver metas: levando o Excel ao próximo nível

Agora que você está confortável com os fundamentos da solução de variáveis desconhecidas no Excel, um mundo inteiramente novo de cálculos em planilhas está aberto para você.

O Goal Seek pode ajudá-lo a economizar tempo ao fazer alguns cálculos mais rapidamente, e o Solver adiciona uma grande quantidade de energia para Habilidades de cálculo do Excel .

É apenas uma questão de se sentir confortável com eles. Quanto mais você os usa, mais úteis eles se tornam.

Você usa Goal Seek ou Solver em suas planilhas? Que outras dicas você pode dar para obter as melhores respostas deles? Compartilhe sua opinião nos comentários abaixo!

Compartilhado Compartilhado Tweet O email 5 dicas para turbinar suas máquinas Linux VirtualBox

Cansado do fraco desempenho oferecido pelas máquinas virtuais? Aqui está o que você deve fazer para aumentar o desempenho do VirtualBox.

Leia a seguir Tópicos relacionados

Produtividade
Planilha
Microsoft Excel
Dicas do Microsoft Office

Sobre o autor Então Albright(506 artigos publicados)

Dann é um consultor de marketing e estratégia de conteúdo que ajuda as empresas a gerar demanda e leads. Ele também bloga sobre estratégia e marketing de conteúdo em dannalbright.com.

Mais de Dann Albright

Assine a nossa newsletter

Junte-se ao nosso boletim informativo para dicas de tecnologia, análises, e-books grátis e ofertas exclusivas!

Clique aqui para se inscrever