Atingir Meta e Solver

Leia também...

Detetive de Macros

Detetive de Macros Seis mulheres estão mortas por causa do Assassino Sem Sombra. Neste exato momento uma nova vítima está caída, sangrando, em uma rua

Ler »

Atingir Meta e Solver

Que o Excel é uma excelente ferramenta para controle e análise de dados todo mundo já sabe, mas o que nem todos sabem é que ele também te ajuda a resolver problemas matemáticos de forma automática com as ferramentas Atingir Meta Solver.


Vídeo explicativo de Atingir Meta e Solver


Para que servem estas ferramentas?

Ambas tem o mesmo propósito, resolver equações, porém uma é muito mais robusta que a outra, vamos a dois exemplos simples onde elas podem ser utilizadas:

Exemplo 1 – Preço do Bolo

Você é o dono de uma bolaria (vende bolos) e como um bom administrador de seu negócio, você sabe bem quanto que consome de cada ingrediente para fazer um bolo de cenoura e chocolate de acordo com o peso final do bolo. Um cliente lhe encomendou um bolo de 1,2Kg e seu intuito é lucrar exatos R$50,00 com a venda desse bolo, por quanto o bolo deve ser vendido?

Exemplo 2 – Quanto produzir de cada tipo para obter a maior receita?

Você é o dono de um fábrica de paletes, que produz 4 tipos de paletes de madeira diferentes com preços de venda diferentes. Todos têm os mesmos insumos em sua produção, porém não nas mesmas proporções, e estes insumos são limitados. Qual é a combinação perfeita de quantidade a ser produzida de cada tipo de palete para se obter a maior receita?

Ambos os exemplos podem ser resolvidos utilizando o Solver, porém o Atingir Metas só conseguiria resolver o exemplo do bolo. Então vamos resolver estes dois casos utilizando para o bolo, o Atingir Metas e, para os paletes, o Solver.


Atingir Metas

A imagem abaixo mostra um esquema para o caso do bolo.

atingir-meta-e-solver-0

C4 = Preço de venda do bolo ao cliente.

C12 = Preço de venda subtraído do custo total, é o lucro com a venda do bolo

C5 = Peso final do bolo

F6:F10 = Custo unitário de cada ingrediente

G6:G10 = Quanto cada ingrediente rende ou é utilizado em um bolo de 1kg

C6:C10 = Custo de cada ingrediente no bolo. A fórmula contida nestas células é:
(Peso do Bolo) x (Rendimento do ingrediente) x (Preço do ingrediente)

C11 = Custo total dos ingredientes

O que precisamos fazer é encontrar o valor exato que inserido na célula C4 fará com que o valor da célula C12 seja igual a R$50,00. Note que existem fórmulas na planilha de modo que o resultado obtido em C12 depende do valor contido em C4, caso contrário não funcionaria.

Então vamos abrir o Atingir Metas e informar isso lá. Siga os passos da imagem abaixo.

atingir-meta-e-solver-2

A seguinte janela será exibida, preencha os campos de acordo com a imagem.

atingir-meta-e-solver-3

A janela do Atingir Metas possui 3 campos apenas, e são bem auto explicativos, mas vamos a eles.

Definir célula: Aqui selecionamos a célula cujo valor queremos definir. Para o exemplo esta será a célula C12.

Para valor: É o valor que queremos que a célula definida em Definir célula assuma. Para o exemplo, utilizaremos o número 50.

Alterando célula: É a célula que deverá ter seu valor alterado a fim de que a célula definida em Defini célula resulte no valor definido em Para valor. Neste exemplo será a célula C4.

Obs: Não é possível selecionar mais de uma célula nos campos Definir célula e Alterando célula.

Obs. 2: Nas imagem de exemplo existe um ‘?‘ na célula C4, este deve ser substituído por um número qualquer antes de executar o Atingir Metas, caso contrário resultará em erro.

Resumindo tudo, informamos ao Atingir Metas que encontre o valor que quando inserido na célula C4 faça com que o resultado da fórmula contida em C12 resulte em 50.

Assim que terminado, basta apertar OK e o resultado será automaticamente calculado, confira na imagem.

atingir-meta-e-solver-4


Solver

O solver é um complemento para Excel, mas não se preocupe você não precisa baixar nada por fora, apenas precisa habilitá-lo. Para isso siga os passos abaixo.


Como habilitar o Solver no Excel

1 – Abra o menu de opções do Excel.

2 – No menu da esquerda selecione a opção Suplementos.

3 – Na parte inferior selecione Suplementos do Excel e clique em Ir, conforme a imagem.

atingir-meta-e-solver-5

4 – Na janela que se abrir, marque o Solver e aperte OK, conforme a imagem.

atingir-meta-e-solver-6

5 – Aguarde até que o processo de instalação seja concluído.


Como utilizar o Solver no Excel

Agora que tudo já está pronto, confira abaixo o esquema para o exemplo da fábrica de paletes.

atingir-meta-e-solver-7

C5:F5 = Quantidade a ser produzida de cada tipo de palete.

C6:F6 = Preço de venda, ou receita, pela venda de cada unidade de cada tipo de palete.

G6 = Receita total com a venda dos paletes. A fórmula contida nesta célula é:
=SOMARPRODUTO(C6:F6;C5:F5)

Ou seja, é o soma da multiplicação da quantidade vendida de cada palete pela receita obtida por cada palete.

C10:F13 = Quantidade utilizada de cada insumo para cada tipo de palete.

G10:G13 = Quantidade total utilizada de cada recurso na produção dos paletes. A fórmula contida nestas células é: =SOMARPRODUTO($C$5:$F$5;C10:F10)

A fórmula acima está inserida na célula G10 que quando arrastada para baixo sofre alterações até ficar igual a =SOMARPRODUTO($C$5:$F$5;C13:F13)

Esta fórmula é a soma da multiplicação da quantidade de cada palete produzida pelo quantidade de recurso necessário.

H10:H13 = Quantidade limite de utilização de cada insumo. Isto é, o resultado das fórmulas contidas nas células G10:G13 não poderão ser superiores aos valores contidos em H10:H13.

Neste caso, o que precisamos fazer é descobrir a combinação de valores que quando inseridos nas células C5:F5 façam com que o resultado da fórmula contida na célula G6 seja o maior possível, porém levando em consideração que o resultado da fórmula contida nas células G10:G13 seja menor ou igual aos valores contidos nas células H10:H13.

Então, vamos abrir o Solver e informar tudo isso para ele. Confira a imagem abaixo para abrir o Solver.

atingir-meta-e-solver-8

A seguinte janela será exibida, confira a imagem para como preencher os campos.

atingir-meta-e-solver-9

Definir Objetivo: Aqui selecionamos a célula cujo valor queremos definir, neste exemplo é a célula G6 que contém a receita total com a venda dos paletes.

Para: Aqui existem 3 opções, (1) fazer com que o valor da célula escolhida em Definir Objetivo seja o maior possível; (2) fazer com que o valor da célula escolhida em Definir Objetivo seja o menor possível; (3) fazer com que o valor da célula escolhida em Definir Objetivo seja igual a um valor qualquer a ser informado. Para o exemplo utilizaremos a primeira opção Máx..

Alterando células variáveis: Aqui escolhemos uma ou mais células que devem ser alteradas a fim que o valor da célula definida em Definir Objetivo satisfaça a condição escolhida em Para. Neste exemplo estas células são C5:F5.

Sujeito à restrição: Aqui definiremos algumas regras devem ser cumpridas enquanto o Solver estiver simulando os valores de C5:F5. Para inserir uma nova restrição, basta clicar no botão Adicionar à esquerda, a seguinte tela será exibida:

atingir-meta-e-solver-10

Nele informamos uma condição que deve ser respeitada por uma ou mais células. Neste exemplo iremos informar que o resultado das fórmulas contidas em G10:G13 deve ser menor ou igual aos valores contidos em H10:H13. Após clicar em OK esta restrição aparecerá na lista de restrições exibida na imagem anterior do Solver.

Tornar variáveis irrestritas não negativas: Este campo já vem marcado por padrão no Solver e, quando marcado, significa que os valores a serem inseridos nas células definidas em Alterando células variáveis, ou seja C5:F5 não podem ser negativos. Para este exemplo deixaremos este campo marcado, já que não existe produção negativa.

Selecionar um método de solução: Em praticamente todos os casos que utilizamos o Solver o método de solução padrão, GRG Não Linear, já resolve o problema. Caso isso não ocorra, você pode testar os outros dois métodos. Para este exemplo ficaremos com o padrão mesmo.

Pronto, agora basta pressionar o botão Resolver e deixar o Solver calcular o problema para nós. Assim que o cálculo é feito, outra janela é exibida informando se houve erro no cálculo e se o Solver conseguiu ou não um resultado que satisfaça todas as condições estabelecidas.

Confira o resultado encontrado pelo Solver.

atingir-meta-e-solver-11

Note que os valores em G10:G13 são todos inferiores ou iguais aos de H10:H13 e que o palete do tipo Aspen não é indicado para produção.


Acompanhe o Função Excel
facebook-logo youtube-logo googleplus-logo twitter-logo

E aí, o que achou?

Compartilhe

Destaques da Loja

Histórico de Cotações

Baixe cotações históricas de ações, índices, moedas, criptomoedas, commodities e fundos em mais de 50 bolsas de valores no mundo, direto no Excel. Selecione os parâmetros desejados e deixe o arquivo fazer o resto para você, de forma rápida, simples e organizada.

Saber mais »

Gerador de catálogos

Com o Gerador de catálogos do Função Excel você poderá criar catálogos personalizados de maneira muito fácil e rápida.

Os catálogos são gerados em formato PDF de forma automática, basta setar as configurações desejada e o arquivo fará tudo por você.

Gere seus catálogos e alavanque suas vendas.

Saber mais »