Problema de cálculo de limites em produção – Solver Excel

Objetivo: Resolver problema de cálculo de limites em produção – Solver Excel.

Problemas com limites Solver Excel 11

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:

Problemas com limites Solver Excel 1

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:

Problemas com limites Solver Excel 2

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:

Problemas com limites Solver Excel 3

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.

Solver5

Agora clique na guia Dados e no botão Solver:

Problemas com limites Solver Excel 4

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.
Problemas com limites Solver Excel 6

O nosso problema fica da seguinte forma formatado:

Problemas com limites Solver Excel 5

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:

Problemas com limites Solver Excel 8

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:

Problemas com limites Solver Excel 8
Problemas com limites Solver Excel 9

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.

[saiba_mais] [ads image=”https://www.guiadoexcel.com.br/wp-content/uploads/2012/08/banner1.png” 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 é:

Problemas com limites Solver Excel 10

Para ver o trabalho completo baixe o exemplo clicando no botão download logo abaixo.

Baixe a planilha

Abraço

Marcos Rieper


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel