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

Preencher página Internet com VBA
Preencher formulário da web via código VBA Excel
14 de março de 2015
Checklist de viagem internacional Excel
Checklist de viagem internacional Excel
27 de março de 2015

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/sites/866/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.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

6 Comentários

  1. Clinaldo disse:

    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?

  2. yuri disse:

    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

  3. Thales disse:

    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.

  4. Fernando disse:

    Bom dia,
    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?

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.