Problema de cálculo de limites em produção - Solver Excel
Problema de cálculo de limites em produção – Solver Excel
Objetivo: Resolver problema de cálculo de limites em produção – Solver Excel.
Neste artigo foi criado para auxiliar o colega do nosso fórum Rodrigo Dias.
Problema: Configurar um valor como 1 unidade inteira e combinar outros até o limite inteiro
Temos uma produção de barras de ferro cortadas sob medida.
As barras são cortadas a partir de maiores com o tamanho padrão de 6000 mm.
A produção é de 2 barras de 1000 mm, 2 barras de 2500 mm, 2 barras de 3000 mm, 1 barra de 4500 mm e 1 barra de 5100 mm.
O problema é como otimizar a produção de modo de combinar estes cortes em 4 barras de 6000.
Para isso foi necessário utilizar o Solver, que realiza cálculos de álgebra linear para identificar solução de problemas simples e complexos.
Neste artigo iremos tratar apenas da solução do problema. Para explicações sobre álgebra linear veja neste link Wikipedia – Álgebra linear, e para entender mais sobre o Solver veja este artigo criado á algum tempo Como Utilizar o Solver Excel – Álgebra linear no Excel.
O primeiro passo é esquematizar o problema, para isso criamos a seguinte tabela:
Na linha superior colocamos o tamanho das barras que serão produzidas, note que separamos todas as barras, então temos uma coluna para cada barra que será produzida.
Na coluna 1 temos a quantidade de barras, pensando que teremos que utilizar 4 barras de 6000 mm, o que nos dá um total de 24000 mm para criar os cortes que juntos somam 22600 mm.
Agora vamos criar as restrições:
Na coluna Limite temos o tamanho máximo de cada barra, no caso 6000 mm, e na linha Limite temos a soma da quantidade de cortes que serão feitos de cada barra, em seguida já ficará clara a sua utilização.
Agora vamos definir a meta:
Criamos então a coluna Soma, e nela utilizamos a função SOMARPRODUTO.
A função somar produto realiza a soma da multiplicação de duas matrizes, no caso temos O2:V2 que recebem as quantidades de barras produzidas e O$1$:V$1$ que é o tamanho de cada barra. Por exemplo, digamos que tenhamos uma produção de 1 barra de 2500 e uma barra de 3000, teríamos (1*2500)+(1*3000) , total seria 5500.
Esta fórmula tem por função identificar se a soma não está passando o limite da coluna anterior.
Por último temos a meta, que é a soma da coluna Soma, esta será a nossa meta, no caso como queremos o valor máximo utilizamos a função =SOMARPRODUTO(J2:J5;K2:K5), pois assim teremos que a maior utilização de cada barra com o menor desperdício possível.
Agora vamos á utilização do Solver:
O Solver é um suplemento do Excel que apesar de já estar instalado, estará desabilitado, para ativá-lo clique no ícone do Excel->Opções->Suplementos e no botão Ir.
Marque a opção Solver nesta lista e ele ficará disponível.
Agora clique na guia Dados e no botão Solver:
Agora vamos criar as configurações do nosso problema no Solver:
Em Set Objective selecione a soma do campo Meta e marque To: Max, ele irá definir que buscamos o valor máximo deste campo.
No campo By Changing Variable Cells selecione os campos em branco no meio da planilha, é a quantidade de barras criadas de cada tipo.
Em Subject to the Constraints: definiremos as restrições, clique em Add para inserir a restrição e selecione as seguintes:
A primeira restringe que podemos ter apenas um valor binário, ou seja 1 ou 0 para cada um dos campos de colunas.
O segundo define que a soma das colunas somente pode ter o valor de 1, ou seja, somente uma barra em cada coluna.
A terceira define que os recortes tem que ter no máximo 6000 mm.
O nosso problema fica da seguinte forma formatado:
Agora clique em Solve, como disse antes, o objetivo não é explicar como funciona o Solver, no começo do artigo já há um link para outro artigo que explica certinho como funciona, então vamos diretamente á solução:
Sendo assim temos a quantidade de barras que serão cortadas em cada uma das barras de 6000, veja que nenhuma ultrapassa a quantidade de 1 barra e nenhuma ultrapassa o tamanho total de 6000 mm que eram nossas restrições.
Podemos melhorar a exibição da planilha criando uma formatação condicional, veja no artigo Formatação Condicional Excel 2007 e 2010, este artigo demonstra como utilizá-la.
A formatação condicional foi definida assim:
Com esta formatação temos que todos os valores que forem >=1 terão o ícone verde, e os menores que 1 o ícone cinza, melhorando assim a exibição dos dados.
O resultado final de nosso trabalho é:
Para ver o trabalho completo baixe o exemplo clicando no botão download logo abaixo.
Clinaldo disse:
18 de março de 2015 às 14:36
Estou com uma planilha onde aplico a função INDIRETO, mas não esta funcionando perfeitamente, posso te enviar a planilha para vc dar uma olhada?
Por uma questão de organização e para auxiliar mais pessoas a resolver suas dúvidas, peço que publique sua questão em nosso fórum em http://www.guiadoexcel.com.br/forum.
boa noite, nao consigo me cadastrar no site, e tenho um problema… pode me ajudar? é parecido com esse mas na verdade preciso de uma formula que calcule qnts barras de 6000 usar de acordo com o numero de cortes
Claro, me envie em consultoria@guiadoexcel.com.br para eu verificar.
Como fazer para que o cálculo leve em conta a perda em cada corte devido a espessura da serra? Cada barra teria na verdade 6000-(5*n) onde n é a quantidade de cortes naquela barra. Em um caso real, você não conseguiria tirar dois pedaços de 3000mm de uma barra de 6000mm.
Montei a planilha direitinho, conforme o site explica, porém se eu aumentar a quantidade de cortes e barras, ele me dá um erro que me parece ser uma limitação do Solver em número de variáveis. Vocês podem me ajudar a resolver isso? tenho alguma alternativa?