Como usar o solver no Excel 2019 ou Excel 2016
O Microsoft Excel evoluiu ao longo dos anos, inserindo ou aprimorando novas funções e fórmulas com o objetivo de tornar o gerenciamento de dados cada vez mais fácil de transportar. Isso se deve ao fato de que podemos enfrentar grandes quantidades de dados numéricos e de texto ou datas em que, se algum deles falhar ou estiver configurado incorretamente, uma série de erros é acionada que pode levar a uma dor de cabeça.
Mas a Microsoft O Excel vai muito além de funções e fórmulas. Para muitos usuários não é um fato consciente que o Excel integrará soluções práticas e verdadeiras através das quais será possível realizar uma hipótese baseada na função dos dados inseridos. Isso é algo realmente útil para tudo o que precisamos para ter certeza de quanto podemos gastar, vender ou administrar de uma forma específica.
Isso é conseguido graças à função Solver que podemos instalar no Microsoft Excel 2016 ou Excel 2019 e será de grande ajuda para este tipo de tarefas.
AdminGuides irá explicar em detalhes como usar o Solver no Excel 2019 e, assim, alcançar mais um ponto de administração e controle neste valioso aplicativo do pacote Office. Este processo é semelhante no Microsoft Excel 2016.
O objetivo principal do Solver é a simulação e otimização de vários modelos de negócios e de engenharia. O Solver trabalha com um grupo de células chamadas células de variável de decisão que são usadas para calcular fórmulas nas células alvo.
O Solver é responsável por ajustar os valores das células da variável de decisão para que eles atendam aos limites das células de restrição e, finalmente, gerar o resultado que esperamos na célula-alvo. Basicamente o Solver será um grande aliado para determinar o valor máximo ou mínimo de uma célula modificando o valor de outras células. O Solver é composto de três elementos que são:
- células variáveis
- célula restrita
- célula alvo
Para acompanhar, lembre-se de se inscrever em nosso canal no YouTube! SUBSCRIBE
1 . Como ativar o Solver no Excel 2016 ou 2019
A primeira etapa que devemos realizar será a ativação do add-in Solver no Microsoft Excel, para isso vamos ao menu Arquivo onde veremos o seguinte:
Aí clicamos em “Opções”? e na janela iremos para a categoria â € œAdd-onsâ € ?? e no painel central selecione â € œSolverâ € ??:
Na parte inferior clicaremos no botà £ o 'Ir' localizado no campo 'Gerenciar' e na janela pop-up ativamos a caixa 'Solver':
Clique em OK para aplicar as alterações. Agora, no â € œDataâ € ?? menu, â € œAnalysisâ € ?? grupo vamos encontrar o â € œSolverâ € ?? opção:
2. Usar o Solver no Excel 2016 ou 2019
Para usar o Solver no Excel 2019, temos o seguintes informações:
- Uma lista de sistemas ou aplicativos
- Uma coluna com o preço de cada um deles
- Uma lista de unidades de cada item
- Custo total
Agora, com esses dados, na coluna Custo total vamos multiplicar o preço unitário pelas unidades com as seguintes fórmulas :
= B7 * C7
Agora, adicionaremos uma nova linha chamada Orçamento Total, onde adicionaremos todo o intervalo da coluna Total com as seguintes fórmulas:
= SUM (D3: D8)
Agora vamos ao menu Dados, grupo Análise e lá clicamos em Solver e o seguinte assistente será exibido:
Lá indicaremos a célula-alvo, campo ' Definir destino 'e, neste caso, selecionamos a célula C11. Aí será possível ajustar o objetivo, “Para”? campo, seja o máximo, o mínimo ou um valor específico dependendo dos critérios dos resultados, para este exemplo vamos ativar o “Máximo”. caixa. O próximo passo é estabelecer as células variáveis, para isso vamos ao campo 'Mudando as células variáveis' e lá selecionamos a faixa desejada que será neste caso a coluna de preço unitário em USD:
Então, é hora de definir as restrições, para este exemplo assumimos que temos um limite de US $ 10.000 para compras, para definir essa restrição clicamos no botão “Adicionar”? botão na seção “Sujeito a restrições”? e na janela pop-up Definimos o seguinte:
- No campo 'Referência da célula', insira a célula Orçamento total.
- Atribuímos a restrição Menor ou igual to (u0026 lt; =).
- No campo Restrição, atribuímos o valor máximo a ser usado neste caso 10000.
Clique em 'Adicionar' para aplicar as alterações. Agora, a seguinte restrição será que tanto os sistemas quanto os aplicativos sejam vendidos completos, aí clicamos novamente em Adicionar e desta vez selecionamos o intervalo de Unidades e selecionamos o valor “int (inteiro)”:
Clique em Adicionar e por fim definiremos a quantidade mínima de cada sistema ou aplicativo para usar, para isso temos o seguinte:
- 3 Office 2019
- 2 Windows 10
- 1 macOS Mojave
- 1 Adobe Suite
- 2 Windows Server
- 2 Camtasia
Para fazer isso , clique em Adicionar e faça o seguinte:
- Na “Referência da célula”? inserimos a célula para cada sistema ou aplicativo na coluna Unidades, por exemplo, para Office 2019 será C3, para Windows 10 será C4 etc.
- Atribuímos o parâmetro menos que ou igual a (u0026 lt; =) e atribua o valor máximo na seção “Restrição”. campo.
Repetimos esse processo para cada elemento. Assim que esse processo for concluído, veremos algo semelhante a isto:
- u0026 lt; = (menor ou igual para
- =: mesmo que
- > =: maior ou igual a
- int: inteiro
- bin: binary
- dif: diferença
Assim que estiver definido, clique em “Resolver” € ?? botão para executar a análise e a seguinte janela será exibida:
Lá temos as seguintes opções:
- Se quisermos manter os valores â € ‹Â €‹ da soluçà £ o na planilha, clicaremos em â € œKeep Solver Solutionâ € ??.
- Se quisermos restaurar os valores originais â € ‹â €‹ antes de clicar no Resolve clicaremos em â € œRestaurar os valores originaisâ € ??.
- Para interromper o processo de resoluçà £ o, pressionamos a tecla Esc, o Excel atualiza a planilha com os últimos valores â Encontrado para as células da variável de decisão.
- A fim de criar uma solução-b Após o Solver encontrar a solução, selecionamos um tipo de relatório na caixa Relatórios e clicamos em OK. O relatório é criado em uma nova planilha do livro, caso o Solver não encontre uma solução, a opção de criar um relatório não estará disponível.
- Para salvar os valores â € ‹â € ‹da célula da variável de decisão como um cenário para usar mais tarde, devemos clicar em Salvar cenário na caixa de diálogo Resultados do Solver e, em seguida, inserir um nome para o cenário na caixa Nome do cenário.
Para este caso, selecionamos a opção “Keep Solver Solution” ?? e clique em OK para ver os resultados:
Como podemos ver, o Solver analisa automaticamente a quantidade máxima com base nos critérios selecionados. Vejamos outro exemplo de como o Solver é útil para todo o processo de análise. Neste caso, temos os seguintes dados:
Aí temos as seguintes informações:
- Preços de itens como CPUs, SSDs e RAM.
- O subtotal de cada item foi definido multiplicando a quantidade pelo preço unitário.
- Atribuímos o total vendas adicionando todos os subtotais.
- As restrições foram aplicadas por meio de vendas totais máximas, quantidade máxima de cada item e um máximo de dispositivos internos (discos e memória).
- Na parte final, adicionamos a soma de todos os elementos que o Solver irá calcular, bem como apenas o filtro dos elementos internos.
Como no ponto anterior, vamos ao menu Dados e no grupo Análise selecionamos Solver e aí vamos definir os seguintes parâmetros:
- No campo 'Definir destino', insira a célula desejada que é neste caso F5 (Vendas totais ).
- No campo ' Alterando as células variáveis 'inserimos cada célula atribuída aos subtotais.
- Nas restrições, adicionamos o seguinte.
No campo de restrições, usamos as seguintes opções para entender a operação:
- $ B $ 13 u0026 lt; = $ F $ 14: aí indicamos que a quantidade de memória a ser vendida deve ser menor ou igual a quantidade indicada na célula F14 (RAM máxima).
- $ B $ 9 u0026 lt; = $ F $ 13: aí indicamos que a quantidade de discos a serem vendidos deve ser menor ou igual a a quantidade indicada na célula F13 (máximo de discos SSD).
- $ F $ 18 u0026 lt; = $ F $ 11: aí indicamos que o total de itens a serem vendidos deve ser menor ou igual ao valor indicado na célula F11 (total máximo de itens).
- $ F $ 19 u0026 lt; = $ F $ 15: ali indicamos que a quantidade de itens internos vendidos deve ser menor que ou igual ao valor indicado na célula F15 (máximo de itens internos).
Clique em 'Resolver' e o Solver será encarregado da análise que se correta lançará a seguinte mensagem:
Lá podemos selecionar se queremos o tipo de relatório a usar. Clique em OK e veremos a análise realizada pelo Solver no Excel:
Se tivermos optado por usar a opção de relatório, ele estará disponível em uma planilha separada e seu formato será o seguinte :
Solver possui os seguintes métodos de resolução:
Usando qualquer um desses métodos, podemos ver que a frente disso é o ' Opções 'botão que permite configurar suas variáveis conforme julgarmos necessário:
Após executar a solução através do Solver será possível salvar o referido projeto ou carregar um já armazenado, para isso clique em “Carregar / Salvar” ?? botão:
A seguinte janela será exibida, onde definimos o intervalo com o modelo Solver para salvar. Clique em Salvar para aplicar as alterações.
Assim, vimos como o Solver é uma solução mais do que prática para a análise e projeção de dados que será muito necessária para futura gestão e administração.
