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

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

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:

$x_{1}$ = número de bolachas comidas diáriamente

$x_{2}$ = número de bolas de chocolate comidas diáriamente

$x_{3}$ = número de latas de refrigerante bebidas diáriamente

$x_{4}$ = 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$x_{1}$+0,20$x_{2}$+0,30$x_{3}$+0,80$x_{4}$

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 $z=0,50x_{1}+0,20x_{2}+0,30x_{3}+0,80x_{4}$

Sujeito a : ${\begin{matrix}400x_{1}&+200x_{2}&+150x_{3}&+500x_{4}&\geq 500\\3x_{1}&+2x_{2}&&&\geq 6\\2x_{1}&+2x_{2}&+4x_{3}&+4x_{4}&\geq 10\\2x_{1}&+4x_{2}&+x_{3}&+5x_{4}&\geq 8\end{matrix}}$

Resolução do problema

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.