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

O problema é o seguinte:

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.

[ads image="/uploads/banner1-19e2c598ae.webp" url="http://guiadoexcel.com.br/cursos-novo" alt="Cursos do Guia do Excel - Destaque-se no mercado de trabalho" side="left"]

O resultado final de nosso trabalho é: