Como utilizar a Pesquisa de Objectivos e o Solver do Excel para resolver variáveis desconhecidas

O Excel é um instrumento excecionalmente potente quando todos os dados são fornecidos. No entanto, seria

Utilizar a funcionalidade do Goal Seek e o suplemento Solver permite a resolução de equações simples e complexas. Um tutorial completo sobre como usar essas ferramentas para determinar o valor de uma unidade de celuloide solitária é fornecido abaixo. Para cálculos mais complexos, o suplemento Solver pode ser utilizado para obter o resultado pretendido.

Como usar o Goal Seek no Excel

A funcionalidade acima mencionada do Goal Seek é inerentemente integrada ao aplicativo Microsoft Excel, sendo acessível por meio da guia Dados e do submenu Análise de variações hipotéticas.

Consideremos um cenário simples em que possuímos uma coleção modesta de dados numéricos, consistindo em três quartos dos nossos valores-alvo, juntamente com um objetivo anual. Utilizando as funcionalidades do Goal Seek, podemos determinar a quantidade necessária dos referidos números no último trimestre (Q4) para atingir o nosso objetivo pretendido.

Com base no total de vendas atual de 114.706 unidades, é necessário vender uma quantidade substancial de unidades durante o quarto trimestre para atingir o objetivo de vendas de 250.000 unidades até ao final do ano. A utilização da função Goal Seek do Microsoft Excel permitirá determinar com exatidão o número de unidades que é necessário vender durante o último trimestre.

Para utilizar a função de Procura de objectivos de uma forma eficiente, siga estes passos:

Ao clicar na guia “Dados”, selecionar “Análise de variações hipotéticas” e, em seguida, “Busca de metas”, aparecerá uma nova janela, denominada “Busca de metas”.

Introduza o valor que representa a soma dos valores de vendas na célula A como meta de otimização, no campo Definir célula. Esta referência de célula serve como ponto de convergência para o processo de otimização, sendo os seus valores calculados e ajustados com base nos critérios estabelecidos pela fórmula no teste IsNumeric na coluna B.

Introduza o valor de vendas pretendido na caixa “Valor para”. O nosso objetivo é atingir um total de 250.000 unidades vendidas, pelo que o valor “250.000” deve ser introduzido aqui.

⭐ Diga ao Excel qual a variável a resolver no campo de célulaBy changing. Queremos ver quais devem ser as nossas vendas no quarto trimestre. Por isso, vamos dizer ao Excel para resolver para a célula DIsso terá o seguinte aspeto quando estiver pronto a usar:

⭐ HitOKpara resolver o seu objetivo. Quando estiver tudo certo, basta clicar emOK. O Excel informará quando o Goal Seek tiver encontrado uma solução.

⭐ Clique novamente emOK e verá o valor que resolve a sua equação na célula que escolheu paraAo alterar a célula.

A nossa solução foi obtida através da subtração do total em execução do objetivo anual, o que produziu um resultado de 135 294 unidades. No entanto, a utilização do Goal Seek numa célula com dados pré-existentes pode revelar-se mais vantajosa, uma vez que permite uma maior flexibilidade e precisão na obtenção do resultado pretendido.

Certifique-se de que cria uma cópia de segurança dos dados originais antes de utilizar a função Procurar objetivo no Excel. Uma vez que esta função tem tendência para substituir qualquer informação introduzida anteriormente. Para evitar qualquer confusão ou erro, é aconselhável rotular os dados copiados como tendo sido gerados através da utilização do Goal Seek. Isto ajudará a distingui-los de quaisquer dados actuais e precisos.

A utilização da função Procura de Objetivo no Microsoft Excel é altamente benéfica e versátil, mas a sua aplicação está limitada a células individuais. Felizmente, o próprio Excel fornece uma solução através do suplemento Solver, que oferece capacidades robustas para lidar com problemas de otimização complexos.

O que é que o Solver do Excel faz?

O Solver pode ser considerado como uma ferramenta avançada e versátil que permite aos utilizadores procurar soluções através da otimização de múltiplas variáveis em simultâneo, à semelhança da funcionalidade oferecida pela função Goal Seek incorporada no Microsoft Excel. Com a sua capacidade de manipular vários factores, mantendo um valor objetivo pretendido, o Solver constitui uma alternativa poderosa ao ajuste manual dos valores de células individuais em busca de resultados específicos.

O Solver é capaz de determinar o valor mais elevado possível de um determinado parâmetro, o valor mais baixo possível ou um valor numérico exato. Além disso, funciona de acordo com restrições pré-determinadas, tendo em consideração quaisquer limitações nas variáveis alteráveis e especificando intervalos quando aplicável.

A utilização do Solver no Excel é uma excelente metodologia para a resolução de múltiplos parâmetros não identificados. No entanto, a aquisição e utilização desta ferramenta requer conhecimentos especializados, uma vez que não é simples. Neste artigo, vamos aprofundar o processo de integração do suplemento Solver na última iteração do Microsoft Excel 365, seguido de uma exploração das suas funcionalidades e técnicas de utilização.

Como carregar o suplemento Solver

O Excel não incorpora o Solver em seu pacote padrão.No entanto, é uma ferramenta suplementar que precisa de ser instalada como um complemento, carregando-a primeiro. A boa notícia é que já vem pré-instalada no seu dispositivo.

Siga estes passos para gerir suplementos no Microsoft Excel:1. Clique no menu “Ficheiro” e seleccione “Opções”.2. No submenu que aparece, seleccione “Suplementos”. 3. Abre-se uma nova janela que mostra todos os suplementos instalados. Localize o que deseja ativar ou desativar.4. Marque ou desmarque a caixa junto ao suplemento para o ativar ou desativar.5. Depois de efetuar as alterações, clique em “Ir” junto a “Gerir: Suplementos do Excel” para salvar suas configurações.

Confirme que a caixa de verificação junto a “Suplemento Solver” na janela de pop-up foi selecionada e, em seguida, clique em “OK”.

O botão Solver será apresentado na secção Analysis (Análise) do separador Data (Dados),

A presença despretensiosa do Data Analysis Toolpak (Conjunto de ferramentas de análise de dados) pode levar a que não se tenha em conta a sua utilidade. No entanto, após a ativação, o utilizador tem acesso ao botão Análise de Dados, que serve como ponto de entrada para explorar as potencialidades deste poderoso conjunto de ferramentas. No caso de o suplemento Solver ainda não ter sido ativado, o seu aparecimento anunciará a sua disponibilidade para integração no seu fluxo de trabalho. Assim que o suplemento Solver tiver sido carregado, podemos continuar a explorar os vários métodos através dos quais pode ser empregue para resolver desafios analíticos.

Como utilizar o Solver no Excel

o objetivo, as células variáveis e as restrições. Iremos delinear cada componente em pormenor.

⭐ ClickData > Solver. Verá a janela Parâmetros do Solver abaixo. (Se não vir o botão do Solver, consulte a secção anterior sobre como carregar o suplemento Solver).

⭐ Defina o objetivo da célula e informe ao Excel sua meta. O objetivo está na parte superior da janela do Solver e tem duas partes: a célula do objetivo e uma opção de maximizar, minimizar ou um valor específico. Se você selecionarMax, o Excel ajustará suas variáveis para obter o maior número possível na célula do objetivo: O Solver minimizará o número do objetivo.Valor Permite-lhe 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 campoAo alterar as células variáveis. 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 estas são todas as células que podem variar. Se não quiser que uma célula seja alterada, não a seleccione.

A função de restrição do Solver permite a especificação de condições que devem ser cumpridas por uma ou mais variáveis. Ao contrário de alterar livremente os valores das variáveis, as restrições permitem ditar requisitos específicos que devem ser respeitados durante o processo de otimização. A metodologia de imposição de restrições é apresentada na secção seguinte.

Após o fornecimento de todas as informações relevantes, utilize a função “Solve” do Microsoft Excel para obter a solução. Esta função irá incorporar quaisquer variáveis recentemente introduzidas no conjunto de dados existente. É aconselhável efetuar uma cópia de segurança dos seus dados antes de implementar quaisquer alterações.

Para além de fornecer soluções para os problemas, o Excel também permite aos utilizadores criar relatórios, que serão examinados brevemente no exemplo seguinte de utilização da ferramenta Solver.

Como definir restrições no Solver

É possível instruir o Excel que uma determinada variável deve ser maior que um valor especificado quando se tenta variar os valores dessa variável. No entanto, se for introduzido um valor inferior a 201 para esta variável específica, o Excel não o permitirá.

Seleccione a célula ou células às quais pretende impor uma restrição e especifique os parâmetros operacionais para a restrição, utilizando os operadores fornecidos nos campos designados.

Eis os operadores disponíveis:

⭐ <= (menor ou igual a)

⭐ = (igual a)

⭐ => (maior ou igual a)

⭐ int (deve ser um número inteiro)

O sistema binário representa um código digital composto por uns e zeros, onde cada dígito tem apenas dois valores possíveis, 1 ou 0.

⭐ TodosDiferentes

O requisito especificado por “TodosDiferentes” é algo desconcertante. Obriga a que cada célula constituinte dentro do intervalo designado para a Referência de célula seja um valor numérico único, ao mesmo tempo que assegura que estes valores se enquadram no espetro de 1 até à contagem total de células.

Dado um conjunto de três células, a soma dos seus valores será invariavelmente um do trio numérico constituído por 1, 2 ou 3 (embora não necessariamente por essa ordem específica), após o que o resultado da restrição deve ser anexado ao cálculo final.

Tenha em atenção que é possível especificar várias células para referência numa seleção de Referência de Célula.Isto significa que se necessitar que várias variáveis tenham um valor maior ou igual a 10, pode selecionar todas essas variáveis e informar o Solver desse facto, em vez de adicionar restrições individuais para cada célula.

A caixa de verificação localizada na janela primária do Solver permite ao utilizador verificar se todos os valores não sujeitos a restrições são não-negativos. Por outro lado, se o utilizador desejar que as variáveis assumam valores negativos, deve desmarcar esta opção.

Um exemplo de Solver

O add-in Solver será utilizado para efetuar um cálculo rápido, sendo o início do processo iniciado pela disponibilidade dos dados necessários. O conjunto de dados inicial é o seguinte:

O documento inclui cinco profissões distintas, com remunerações variáveis para cada uma delas. Para além disso, há uma atribuição de horas de trabalho hipotéticas que um trabalhador poderá realizar em cada uma das respectivas funções num determinado período de tempo.

A utilização do suplemento Solver permite-nos determinar a afetação óptima dos recursos, de modo a maximizar a remuneração global, respeitando limitações específicas. As restrições que vamos aplicar são as seguintes:

É improvável que qualquer trabalho tenha uma duração inferior a quatro horas.

A tarefa atribuída ao trabalho 4 deve ser concluída num prazo superior a doze horas.

O requisito da tarefa indica que a duração do trabalho não deve exceder onze horas.

A totalidade das horas de trabalho deve ser equiparada a quarenta (40) horas.

Antes de utilizar o Solver, é útil articular quaisquer limitações ou restrições que se possam aplicar ao seu funcionamento. O seguinte procedimento passo-a-passo demonstra como tais restrições podem ser estabelecidas no software Solver:

Por favor, faça uma cópia da tabela para evitar qualquer modificação acidental da original, que compreende as nossas horas de trabalho actuais.

Para garantir que os valores dentro das restrições “maior que” e “menor que” são representados corretamente, é importante notar que estas restrições devem ter um valor superior ou inferior aos valores acima mencionados. Isso se deve ao fato de que não há opções de maior ou menor que disponíveis; em vez disso, há apenas restrições de maior que ou igual a e menor que ou igual a.

Vamos clicar em Solve e ver o que acontece.

O solucionador descobriu com sucesso uma solução que cumpre todos os requisitos e aumentou os nossos ganhos, como mostra o gráfico à esquerda da janela.

Para conservar os novos valores gerados, certifique-se de que a opção “Manter solução do solucionador” está selecionada e clique em “OK”. Em alternativa, se necessitar de mais detalhes, pode optar por gerar um relatório, seleccionando os relatórios pretendidos na janela da direita e especificando se pretende que sejam delineados. Recomenda-se que o faça. Por fim, clique em

Os relatórios, que são criados na pasta de trabalho, fornecem uma descrição detalhada do procedimento efectuado pelo suplemento Solver para chegar à sua solução.

Os relatórios actuais não suscitam muito entusiasmo devido à escassez de dados intrigantes. No entanto, ao utilizar uma equação do Solver mais avançada, é possível descobrir detalhes valiosos do relatório nas folhas recentemente introduzidas. Para aceder a informações adicionais, basta clicar no botão “+” situado ao lado de qualquer relatório.

Opções avançadas do Solver

Se uma pessoa não for muito versada em análise estatística, pode optar por utilizar as funcionalidades avançadas do Solver sem se aprofundar nas suas complexidades. No entanto, para aqueles que efectuam cálculos extensos e complexos, a familiaridade com estas funcionalidades seria benéfica.

O mais óbvio é o método de resolução:

As opções acima mencionadas, nomeadamente GRG Não-linear, Simplex LP e Evolutivo, estão disponíveis no Microsoft Excel para resolver problemas de programação não-linear. Embora o Excel ofereça uma explicação elementar relativa à adequação destes métodos, uma compreensão mais abrangente requer familiaridade com a análise estatística e as técnicas de regressão no software.

Active as opções avançadas clicando no botão “Opções” para ajustar as suas preferências. Pode especificar a otimização de números inteiros, impor restrições de tempo aos cálculos (aplicável a grandes conjuntos de dados) e personalizar os processos de tomada de decisão das técnicas Greedy Randomized Generation e Evolutionary Solving.

Mais uma vez, se não souber o que isto significa, não se preocupe. Se quiser saber mais sobre qual o método de resolução a utilizar, o Engineer Excel tem um bom artigo que o explica. Se pretende obter a máxima precisão, o método evolutivo é provavelmente uma boa opção. Mas tenha em atenção que demorará muito tempo.

Goal Seek and Solver: Levar o Excel para o próximo nível

Com proficiência na resolução de parâmetros não identificados no Microsoft Excel, é possível mergulhar num vasto domínio de cálculos em folhas de cálculo. A utilização do Goal Seek e do Solver permite uma execução mais rápida de determinados cálculos, ao mesmo tempo que proporciona um aumento significativo das capacidades analíticas do programa.

Habituar-se a utilizar estas ferramentas é crucial para perceber o seu valor prático. Com a utilização repetida, a sua eficácia tornar-se-á cada vez mais evidente.