Uso do Solver do Excel em Programação Linear

Prévia do material em texto

1
O USO DA FERRAMENTA SOLVER DO EXCEL NA RESOLUÇÃO DE 
PROBLEMAS DE PROGRAMAÇÃO LINEAR 
 
João Batista de Jesus 1 
FATEC-JAHU 
Célio Favoni 2 
FATEC-JAHU 
 
 
1 jbatistaj@yahoo.com.br 
2 favoni@hotmail.com 
Resumo 
 
Este trabalho expõe de maneira sintetizada as 
funcionalidades da ferramenta Solver do Micro-
soft Excel, para resolução de problemas de 
programação linear. Tem como objetivo ser um 
instrumento de introdução à ferramenta Solver e 
despertar o interesse do leitor para o uso desta 
e de outras ferramentas de otimização. 
 
Palavras-chave: Solver, Programação Linear, 
Excel. 
 
Abstract 
 
This work summarizes the features of the tool 
“Solver” for Microsoft Excel, used to solve pro-
blems of Linear Programming. Its objective is to 
introduce the Solver tool and call the readers 
attention to this and other optimization tools. 
 
Keywords: Solver, Linear Programming, Excel. 
 
Programação Linear 
 
Segundo CAIXETA-FILHO (2004), programação 
linear é um aprimoramento da técnica de reso-
lução de sistema de equações lineares via in-
versões sucessivas de matrizes, com a vanta-
gem de incorporar uma equação linear adicional 
representativa relacionada com um comporta-
mento que deve ser otimizado. 
 
A programação linear é um das técnicas mais 
utilizadas na pesquisa operacional. Sua aplica-
ção é facilitada devido a simplicidade do modelo 
e a disponibilidade de uma técnica de solução 
programável em computador. As aplicações 
mais comuns são feitas em Produção, Finanças, 
Logística, Matemática aplicada entre outras 
SILVA et al. (1998). 
 
Elaboração do Modelo 
 
SILVA et al. (1998), apresenta o seguinte roteiro 
para a formulação do modelo matemático de 
programação linear: 
 
a) Variáveis de decisão 
Consiste em explicar as decisões que deverão 
ser tomadas. Por exemplo, se for um problema 
de programação de produção, as variáveis po-
derão representar as quantidades a produzir de 
cada produto em um determinado período. 
 
b) Função objetivo 
Deve ser definido o objetivo básico do proble-
ma, ou seja, é a otimização (maximizar ou mi-
nimizar algo) desejado. Geralmente aparecem 
na forma de maximização de lucros ou receitas; 
minimização de custos ou perdas etc. 
 
c) Restrições 
Cada restrição imposta na descrição do sistema 
deve ser expressa como uma relação linear 
(igualdade ou desigualdade), elaboradas com 
as variáveis de decisão. Por tanto, as variáveis 
de decisão poderão estar sujeitas a uma ou 
mais limitações. 
 
Normalmente as variáveis de decisão podem 
assumir apenas valores positivos, sendo assim 
faz-se necessário também expressar as restri-
ções de não negatividade. 
Para exemplificar o a montagem de um modelo, 
considere o seguinte problema extraído de SIL-
VA et al. (1998) pg. 19. 
 
“Um vendedor de frutas pode transportar 
800 caixas de frutas para sua região de 
vendas. Ele necessita transportar 200 cai-
xas de laranjas a 20 u.m. de lucro por cai-
xa, pelo menos 100 caixas de pêssegos a 
10 u.m. de lucro por caixa, e no máximo 
200 caixas de tangerinas a 30 u.m. de lu-
cro por caixa. De que forma ele deverá 
carregar o caminhão para obter lucro má-
ximo? Construa o modelo do problema”. 
 
Nota: u.m. corresponde a unidade monetária. 
 
Solução: 
 
a) Variáveis de decisão 
As variáveis de decisão representam os valores 
desconhecidos referente ao problema proposto. 
No caso desse problema, não sabemos a quan-
tidade de caixas de pêssegos e de tangerinas 
 2
que devemos transportar, portanto esse pro-
blema possui duas variáveis, podemos chamá-
las de x1 e x2 onde: 
 
x1 → representa a quantidade de caixas de pês-
sego a ser transportada. 
x2 → representa a quantidade de caixas de tan-
gerina a ser transportada. 
 
Note que a quantidade de caixas de laranja foi 
fixada em 200. 
 
b) Função Objetivo 
A função objetivo define a otimização desejada. 
No caso desse problema, deseja-se a maximi-
zação do lucro. O lucro atribuído a cada tipo de 
fruta deve ser multiplicado pela respectiva quan-
tidade a ser transportada, portanto a função 
objetivo é expressa pela seguinte equação: 
 
40003010 21 ++= xxL (1) 
 
Onde: 
 
L é o lucro máximo. 
10 lucro por cada caixa de pêssego transporta-
da. 
x1 quantidade de caixa de pêssego a ser trans-
portada. 
30 lucro por cada caixa de tangerina transporta-
da. 
x2 quantidade de caixa de tangerina a ser trans-
portada. 
4.000 lucro obtido pelo transporte de 200 caixas 
de laranja a 20 u.m. cada. 
 
c) Restrições 
 
As restrições definem as limitações a que o 
problema está sujeito. Neste caso, há três res-
trições: Sendo a quantidade total de caixas a 
serem transportadas, a quantidade de caixas de 
pêssego deve ser de pelo menos 100 e a de 
tangerina no máximo 200. Portanto teremos três 
inequações denominadas como restrições téc-
nicas. 
 
Total de caixas a serem transportadas. 
80020021 <=++ xx ou 60021 <=+ xx (2) 
 
Mínimo de caixas de pêssego. 
1001 >=x (3) 
 
Máximo de caixas de tangerina. 
2002 <=x (4) 
 
Devemos também expressar as restrições de 
não negatividade para que as variáveis de deci-
são não assumam valores negativos. Então, 
teremos mais duas inequações: 
 
01 >=x (5) 
02 >=x (6) 
 
A elaboração de um modelo é um passo muito 
importante para que se possa posteriormente 
chegar a uma resolução. 
 
Solver do Excel 
 
Conforme o manual do usuário do Office 2003, 
o Solver faz parte de um conjunto de progra-
mas, que geralmente são chamados de ferra-
mentas de análise hipotética, ou seja, uma fer-
ramenta que possibilita encontrar um valor ideal 
(otimizado) para uma determinada equação. 
Para resolver problemas lineares e de números 
inteiros, o Solver utiliza o algoritmo Simplex com 
limites sobre as variáveis e o método de desvio 
e limite. Esse método foi implementado por 
John Watson e Dan Fylstra, ambos da Frontline 
Systems, Inc. 
 
Definindo o Modelo de Otimização no Excel 
 
Winston (2004), propõe a montagem de um 
modelo de otimização no Excel em três partes: 
Célula de destino (fórmula da função objetivo), 
Células variáveis e as Restrições. Este método 
é bem semelhante ao modelo matemático ante-
riormente explicado, Entretanto facilitará a pas-
sagem dos dados do modelo matemático con-
vencional para uma planilha do Excel e posteri-
ormente a manipulação dos mesmos pelo Sol-
ver. 
 
Célula de destino 
É a célula que representa a meta ou o objetivo 
que se deseja atingir. Esta célula deverá conter 
uma fórmula que represente a função objetivo 
do modelo proposto. Por exemplo, combinar a 
quantidade de caixas de frutas a serem trans-
portadas conforme suas respectivas demandas 
com a finalidade de maximizar o lucro. 
 
Células variáveis 
Estas células poderão ser alteradas ou ajusta-
das a fim de atingir a otimização da célula de 
destino. A determinação destes valores está 
diretamente relacionada às restrições e/ou limi-
tações do modelo. 
 
Restrições 
As células das restrições representam os valo-
res a que o modelo está limitado. Estes valores 
estão relacionados à quantidade de recursos 
disponíveis, por exemplo, matéria-prima e mão 
 3
de obra, sendo que não podemos produzir mais 
do que é demandado, portanto a demanda tam-
bém pode ser um fator limitante do modelo. 
Geralmente cada restrição poderá ser constituí-
da de três células com os respectivos conteú-
dos: 
 
1ª célula (Termo da esquerda): contém fórmula 
com constantes e variáveis do modelo. Ex: 
3*B5+2*B6. 
2ª célula: Operador relacional. Ex: >=, <=, =. 
Nesta etapa, serve apenas como ilustração. 
3ª célula (Termo da direita): contém o Valor 
limitante da restrição. 
 
Instalando o Solver 
 
O Solver é distribuído juntamente com o pacote 
Office da Microsoft como suplemento que podeser incorporado ao Excel. Por padrão o Solver 
não estará instalado após a instalação típica do 
pacote Office, portanto será necessário instalá-
lo através do recurso de instalação de suple-
mentos descrito a seguir. 
 
Para instalar o Solver siga os seguintes pas-
sos: 
 
1. Com o Excel aberto, clique no menu Ferra-
mentas e depois em Suplementos. 
2. Na caixa de diálogo Suplementos, procure o 
Solver na lista de suplementos disponíveis e 
selecione-o conforme a figura a seguir. 
3. Clique em OK para confirmar a instalação. 
 
 
Figura 1: Caixa de diálogo Suplementos com o suplemento 
Solver selecionado. 
Resolvendo Problema Linear no Solver 
 
Utilizaremos o método proposto por Winston 
(2004) para montagem no Excel do modelo do 
transporte de caixas de frutas que foi abordado 
anteriormente. Para isso, execute o Excel e em 
uma nova planilha, siga os seguintes passos: 
 
1. Prepare uma planilha com os dados do mo-
delo conforme a figura 2 a seguir. 
 
 
Figura 2: Planilha com os dados do modelo proposto. 
Nota: As células B2 e B3 representam as célu-
las ajustáveis (variáveis). 
 
2. Acrescente nesta planilha as seguintes fór-
mulas: 
Na célula B6: =10*B2+30*B3+4000 
Na célula A10: =B2+B3+200 
Na célula A11: =B2 
Na célula A12: =B3 
 
3. Selecione a célula de destino (B6) e execute 
a ferramenta Solver. Para isso, clique na célula 
B6 e depois clique no menu Ferramentas e em 
seguida clique em Solver.(figura 3) 
 
 
Figura 3: Caixa de diálogo dos Parâmetros da Ferramenta 
Solver. 
4. Defina a célula de destino, clicando sobre ela 
ou digitando a referência da mesma. Se a célula 
de destino já estiver selecionada antes de abrir 
o Solver, a célula de destino já estará definida. 
 
Nota: O cifrão é utilizado para definir uma refe-
rência absoluta. Isto significa que se precisar-
mos mover o valor da célula referenciada, as 
definições especificadas no Solver serão atuali-
zadas automaticamente. Para maiores esclare-
cimentos sobre tipos de referências, consulte a 
ajuda do Excel. 
 
5. Defina o tipo otimização, Max para maximizar 
a função objetivo, Min para minimizar a função 
 4
objetivo e Valor para especificar um valor para 
função objetivo. No caso do exemplo proposto, 
será a opção Max, para maximizar o Lucro. 
 
6. Especifique as células ajustáveis. Quando 
essas células forem adjacentes, clique na caixa 
Células ajustáveis, clique na primeira célula e 
arraste até a última célula ajustável. Caso con-
trário utilize ponto e vírgula entre cada referên-
cia. No caso do exemplo proposto, serão as 
células B2 e B3, que podem ser referenciadas 
como B2:B3 por serem adjacentes. 
 
Nota: O Solver suporta até 200 células ajustá-
veis (variáveis). 
7. No quadro Submeter às restrições, siga os 
seguintes passos para inserir as restrições: 
7.1. Clique no botão Adicionar. Surgirá uma 
janela conforme a figura 4. 
7.2. Clique na caixa Referência de célula, em 
seguida na célula ou digite a referência que 
conterá o valor que será comparado com o limi-
te da restrição que está sendo especificada. 
7.3. Escolha o operador conforme o tipo de res-
trição. 
7.4. Clique na caixa Restrição, em seguida na 
célula ou digite a referência que contém o limite 
para a restrição que está sendo especificada e 
depois em OK para adicionar a restrição. 
 
 
Figura 4: Janela Adicionar restrição com a 1ª restrição do 
modelo proposto definida. 
Repita o passo 7 e todos os seus sub-passos 
para cada restrição do modelo. 
 
8. Para atender as restrições de não negativida-
de, clique no botão opções e selecione a caixa 
de seleção “Presumir não negativos”. Como 
estamos trabalhando com problemas lineares, 
selecione também a caixa de seleção “Presumir 
modelo linear”, conforme demonstrado na figura 
5. 
 
 
Figura 5: Caixa de diálogo Opções do Solver. 
 
A figura 6 demonstra como modelo proposto 
como exemplo ficará definido no Solver. Antes 
de clicar no botão Resolver certifique-se de que 
todas as definições estejam corretas. 
 
 
Figura 6: Janela do Solver com as definições do modelo 
proposto. 
9. Clique no botão Resolver. Surgirá uma janela 
perguntando se desejamos Manter ou Restaurar 
os valores, como mostra a figura 7. Também é 
possível selecionarmos os relatórios sobre o 
processo de solução. Selecione as opções que 
desejar e clique em OK. 
 
 
Figura 7: Janela de Resultados do Solver. 
 5
Confira na figura 8, o resultado da função objeti-
vo na célula de destino (B6), e os valores das 
células ajustáveis B2 e B3 que propiciaram a 
otimização do modelo proposto através da ma-
ximização do Lucro. 
 
 
Figura 8: Resultado do modelo proposto. 
 
Nota: Mesmo depois que o problema foi resolvi-
do, é possível realizar alterações na planilha ou 
nas definições do Solver, portanto, caso isso 
ocorra, abra novamente o Solver, e excute a 
resolução novamente através do botão Resol-
ver. 
 
Substituindo as variáveis 1x e 2x da função 
objetivo pelos valores que foram obtidos através 
do solver nas células ajustáveis B2 e B3, obte-
mos a seguinte expressão: 
 
40002003040010 +×+×=L 
 
Resolvendo a expressão acima, chegaremos ao 
mesmo resultado obtido através do Solver: 
14000 u.m. Esse é o valor ótimo para o modelo 
proposto, neste caso ótimo significa maximizar o 
Lucro levando em consideração as restrições 
impostas. 
 
Outras Ferramentas de Otimização 
 
Veja a seguir alguns softwares da área de pes-
quisa operacional disponíveis para download 
(baixar) da Internet gratuitamente para fins aca-
dêmicos. Para utilização comercial é necessário 
licenciar o produto. 
 
LINDO (Lindo Systems Inc) 
http://www.lindo.com 
 
Prolin (João Matos e Ricardo Matos (Escola 
Superior de Tecnologia de Setúbal) 
http://prolin.no.sapo.pt 
 
Multisimplex (Gabritech Solutions) 
http://www.multisimplex.com 
 
 
Bibliografia 
 
CAIXETA-FILHO, José Vicente. PESQUISA 
OPERACIONAL: Técnicas de Otimização Apli-
cadas a Sistemas Agroindustriais. 2. ed. São 
Paulo, Atlas, 2004. cap. 2. p. 18-32. 
 
SILVA, Ermes Medeiros; et al. PESQUISA O-
PERACIONAL: Programação linear. São Paulo, 
Atlas, 1998. cap. 2. p. 14-22. 
 
WINSTON, Wayne L. Introdução à otimização 
com a ferramenta Solver do Ms-Excel. Disponí-
vel em: <http://office.microsoft.com/pt-
br/assistance/ha011245951046.aspx> Acessado 
em: 17 mar. 2006. 
 
Microsoft Office On-Line - Sobre o Solver. Dis-
ponível em: <http://office.microsoft.com/pt-
br/assistance/hp051983681046.aspx> Acessado 
em: 20 mar. 2006.