Engenharia industrial/Investigação operacional/Usando o Excel para resolver problemas de programação linear

Usando o Excel para resolver problemas de programação linear[1]

editar

O Excel tem a capacidade de resolver problemas de programação linear. Nesta secção vamos mostrar como se utiliza o Solver do Excel para encontar a solução ótima de um problema de dietas e de um problema de controle de inventário.

A chave para resolver problemas de programação linear numa folha de cálculo é preparar uma folha que contenha tudo de interesse no problema a solucionar (custos ou proveitos, utilização de recursos, etc.). De seguida identificam-se as celulas que podem ser variadas. Estas celulas chamam-se células variáveis. Depois de se definir as células variáveis, identificam-se as células que contêm a função objectivo. Estas celulas chamam-se células alvo. Finalmente identificam-se as restrições e pedimos ao Solver que rersolva o problema. Finalmente a solução ótima é colocada na folha de cálculo.

Apresentação do modelo

editar

Primeiro vamos formular um problema de dieta:

Existem quatro tipos de comida disponíveis para consumo: bolachas; gelado de chocolate; refregirante e bolo de ananás. Cada bolacha custa 0,50€, cada bola de gelado de chocolate custa 0,20€, cada lata de refrigerante custa 0,30€ e cada fatia de bolo de ananás custa 0,80€. Todos os dias é preciso ingerir pelo menos 500 calorias 6 oz de chocolate, 10 oz de açucar e 8 oz de gordura. O conteúdo nutricional de cada tipo de comida é mostrado na tabela 1.

Tabela 1 - Valores nutricionais por alimento
Tipo de

Alimento

Calorias Chocolate (oz) Açucar (oz) Gordura
Bolacha 400 3 2 2
Gelado 200 2 2 4
Refrigerante 150 0 4 1
Bolo 500 0 4 5

Começa-se por identificar as variáveis de decisão. Como a decisão final vai indicar quanto de cada tipo de comida vai ser consumido diáriamente, definem-se quatro variáveis de decisão:

  = número de bolachas comidas diáriamente

  = número de bolas de chocolate comidas diáriamente

  = número de latas de refrigerante bebidas diáriamente

  = fatias de bolo de chocolate comidas diáriamente

De seguida define-se a função objectivo que minimiza o custo total da dieta: (custo total da dieta) = (custo das bolachas) + (custo dos gelados) + (custo dos refrigerantes) + (custo das fatias de bolo)

Custo total da dieta = 0,50 +0,20 +0,30 +0,80 

As variáveis de decisão têm que satisfazer as seguintes restrições:

Restrição 1 - Ingestão de calorias tem que ser de pelo menos 500 calorias

Restrição 2 - Ingestão de chcolate tem que ser de pelo menos 6 oz

Restrição 3 - Ingestão de açucar tem que ser de pelo menos 10 oz

Restrição 4 - Ingestão de gordura tem que ser de pelo menos 8 oz

Então o modelo de programação matemática toma o seguinte aspecto:

min  

Sujeito a :  

Resolução do problema

editar

Começa-se por construir um quadro no Excel como o da figura 1

 
Figura 1 - Tabela do Excel para o Solver

O preenchimento da linha 4 deve ser feito com qualquer solução admissível. Na linha 5 introduz-se o custo de cada comida e na coluna F fica a soma dos produtos da linha 4 com a linha 5. Isto é B4xB5 + C4xC5 + D4xD5 + E4xE5. Mas é mais fácil introduzir na célula F5 a função SOMARPRODUTO($B$4:$E$4;B5:E5). O sinal $ na célula B4E4 fixa uma das linhas do sumatório de produto na linha4. Isto acontece para quando se arrastar a célula F5 para baixo um dos multiplicadores ser sempre a quantidade. A linha 4 contem as quantidades de cada comida que compõem a solução admissível. Na linha 6 coloca-se as calorias de cada comida, na linha 7 coloca-se o chocolate de cada comida, na linha 8 o açucar de cada comida e na linha 9 a gordura de cada comida. Finalmente arrasta-se a célula F5 para baixo para se obter os valores totais para as calorias, chocolate açucar e gordura. Nas células H6 a H9 colocam-se os respectivos valores máximos.

 
Figura 1 - Menú principal do Excel Solver

De seguida abre-se o solver no menú Dados, Análise, Solucionador. Começa-se por selecionar a célula F5 (custo total) para a célula de destino. Seleciona-se a opção Mínimo pois este é um problema de mínimização. No campo "Por alteração das células:" coloca-se a selecção das células B4 a E4.Neste caso alteram-se as células que definem a quantidade. De seguida clica-se em Adicionar para se adicionar as restrições. No campo referência de célula coloca-se o valor F6:F9 para se selecionar os valores totais das restrições. Muda-se a direcção da restrição para >=. E preenche-se o campo restrição com o valor H6:H9, que são os valores das restrições do problema. Nas opções deve selecionar-se a opção "Assumir modelo linear" e a opção "Assumir não negativos". Clica-se em solucionar e seleciona-se "Aceitar a solução do Solver". Clica-se em OK. A solução optima está na célula F5 com os valores ótimos para as variáveis de decisão nas células B4:E4.

Referências

  1. Operations Research - Wayne L. Winston