SOLVER(EXCEL):
MANUAL DE REFERÊNCIA
Aloísio de Castro Gomes Júnior
Marcone Jamilson Freitas Souza
Projeto patrocinado pelo programa PRÓ-ATIVA da UFOP
DEPARTAMENTO DE COMPUTAÇÃO
UNIVERSIDADE FEDERAL DE OURO PRETO
JANEIRO DE 2004
Prepare-se para as provas
Estude fácil! Tem muito documento disponível na Docsity
Ganhe pontos para baixar
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Guias e Dicas
Prepare-se para as provas
Estude fácil! Tem muito documento disponível na Docsity
Encontrar documentos
Prepare-se para as provas com trabalhos de outros alunos como você, aqui na Docsity
Encontra documentos específicos para os exames da tua universidade
Encontra documentos específicos para os exames da tua universidade
Videoaulas
Prepare-se com as videoaulas e exercícios resolvidos criados a partir da grade da sua Universidade
Quiz
Responda perguntas de provas passadas e avalie sua preparação.
Ganhe pontos para baixar
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Manual - solver, Manuais, Projetos, Pesquisas de Engenharia de Produção
Manual sobre o programa Solver do Excel, utilizado na Pesquisa Operacional com problemas de Programação Linear
Tipologia: Manuais, Projetos, Pesquisas
Antes de 2010
1 / 22
Esta página não é visível na pré-visualização
Não perca as partes importantes!
Documentos relacionados
Pré-visualização parcial do texto
Baixe Manual - solver e outras Manuais, Projetos, Pesquisas em PDF para Engenharia de Produção, somente na Docsity!
SOLVER(EXCEL):
MANUAL DE REFERÊNCIA
Aloísio de Castro Gomes Júnior
Marcone Jamilson Freitas Souza
Projeto patrocinado pelo programa PRÓ-ATIVA da UFOP
DEPARTAMENTO DE COMPUTAÇÃO UNIVERSIDADE FEDERAL DE OURO PRETO JANEIRO DE 2004
Conteúdo
Capítulo 1
SOLVER (EXCEL)
1.1 O que é o SOLVER?
O Solver faz parte de um conjunto de programas algumas vezes chamado de ferramentas de análise hipotética. Com o Solver você pode localizar um valor ideal para uma fórmula em uma célula − chamada de célula de destino − em uma planilha. O Solver trabalha com um grupo de células relacionadas direta ou indiretamente com a fórmula na célula de destino. O Solver ajusta os valores nas células variáveis que você especi car − chamadas de células ajustáveis − para produzir o resultado especi cado por você na fórmula da célula de destino. Você pode aplicar restrições para restringir os valores que o Solver poderá usar no modelo e as restrições podem se referir a outras células que afetem a fórmula da célula de destino. Poderemos visualizar isto melhor através de exemplos. No nosso curso, usaremos o SOLVER para resolver Problemas de Programação Linear.
1.2 Exemplos de como Modelar usando o SOLVER do Excel
Para familiarizarmos com o uso do SOLVER utilizaremos uma série de exemplos para a xação de seus principais comandos.
1.2.1 Problema da Fábrica de Automóveis
Alfa Inc. deve produzir 1000 automóveis Alfa. A empresa tem quatro fábricas. Devido a diferenças na mão-de-obra e avanços tecnológicos, as plantas diferem no custo de produção unitário de cada carro. Elas também utilizam diferentes quantidades de matéria-prima e mão-de-obra. O custo de operação, o tempo necessário de mão-de-obra e o custo de matéria-prima para produzir uma unidade de cada carro em cada uma das fábricas estão evidenciados na tabela abaixo.
Fábrica Custo Unitário Mão-de-Obra Matéria-Prima (em R$1.000,00) (horas de fabricação) (unidades de material) 1 15 2 3 2 10 3 4 3 9 4 5 4 7 5 6 Um acordo trabalhista assinado requer que pelo menos 250 carros sejam produzidas na fábrica
- Existem 3200 horas de mão-de-obra e 4000 unidades de material que podem ser alocados às quatro fábricas. O modelo de decisão do problema é dado abaixo, onde xj representa a quantidade de automóveis a serem fabricadas na fábrica j = 1, 2 , 3 , 4. min 15 x 1 + 10 x 2 + 9 x 3 + 7 x 4 s.a 2 x 1 + 3 x 2 + 4 x 3 + 5 x 4 ≤ 3200 3 x 1 + 4 x 2 + 5 x 3 + 6 x 4 ≤ 4000 x 1 + x 2 + x 3 + x 4 = 1000 x 3 ≥ 250 x 1 , x 2 , x 3 , x 4 ≥ 0 Para resolvermos este PPL utilizando o Excel, devemos primeiramente designar uma célula para representar cada uma das seguintes entidades:
- Função Objetivo (FO) (Expressão a ser minimizada ou maximizada);
- Varáveis de Decisão (variáveis que o modelador pode alterar seu valor);
- Para cada restrição temos uma célula representando o lado esquerdo da restrição (LHS) e outra representando o lado direito darestrição (RHS).
Figura 1.1: Modelagem do Exemplo da seção 1.2.1 no Excel
A gura 1.1 apresenta uma das possíveis maneiras de representar o PPL em uma planilha do Excel. Nesta planilha as células a seguir designarão cada uma das entidades citadas anteriormente.
a planilha de dados, nesse ponto devemos clicar sobre a célula que representa a FO e pressionar a tecla ENTER para voltar a janela do SOLVER. A segunda é digitar o nome da cálula (B7 no nosso exemplo) no campo. Realizando uma das duas maneiras, a janela resultante é apresentada na gura 1.3. Na linha seguinte são apresentadas as opções de maximizar, minimizar e atingir valor. Depen- dendo do problema devemos clicar sobre uma das três, no nosso exemplo devemos clicar sobre Min, pois nosso exemplo é de minimização. A opção "Valor de" pode ser utilizada em análise do tipo ponto de equilíbrio, onde desejamos que a função Lucro (por exemplo) atinja o valor de 0. Nos casos de Programação Linear esta opção não será utilizada. Na próxima linha há um campo denominado "Células Variáveis". Neste campo serão inseridas as células que representarão as variáveis de decisão. Os valores podem ser inseridos como o caso da FO, isto é, clicando sobre o ícone à direita do campo e marcando as células escolhidas ou simplesmente digitando seus nomes utilizando as regras do Excel para tal. Utilizando uma das maneiras, a janela terá o formato da gura 1.4. O próximo passo é designar as restrições do problema. Devemos inserir uma restrição ou um grupo de restrições (desde que as restrições tenham o mesmo sinal de restrição e estejam adjacentes) de cada vez. Para inserir a 1 a^ restrição devemos clicar no botão "Adicionar" para aparecer uma janela de entrada de restrições. A janela de entrada de restrições tem três campos, que representam o LHS - "Referência de célula:" (à esquerda), o sinal da restrição (ao centro), e o RHS - "Restrição" (à direita). Como já mencionado anteriormente, o LHS representa a equação do lado esquerdo da restrição (o lado esquerdo do dicionário modi cado). O RHS representa o lado direito da restrição (a constante do dicionário). A gura 1.5 representa a entrada da 1 a^ e 2 a^ restrições. Para entrar com os valores nos campos, deve-se proceder como nos casos anteriores, usando o ícone à direita ou digitando o nome da célula. O passo seguinte será o de clicar no botão "OK", no caso de não haver nenhuma outra restrição, ou no botão "Adicionar" para con rmar esta restrição e abrir espaço para uma nova entrada. No nosso exemplo, devemos clicar em "Adicionar" e inserir as outra restrições. Ao nal de todas as entradas a janela do SOLVER terá a forma da gura 1.6. Devemos agora inserir as restrições de não-negatividade e de nir que o modelo é de Programação Linear, para isto, devemos clicar no botão "Opções" e marcar as opções "Presumir modelo linear" e "Presumir não negativos" como é mostrada na gura 1.7 e depois clique no botão "OK" para
Figura 1.2: Janela da ferramenta SOLVER
Figura 1.3: Escolha da Célula de Destino
Figura 1.4: Janela do Solver após a designação das células variáveis
Figura 1.5: Formato da entrada da 1 a^ e 2 a^ restrições
Figura 1.6: Janela de entrada dos parâmetros do SOLVER para o Exemplo da seção 1.2.
Figura 1.7: Janela de Opções do SOLVER
Figura 1.8: Opções de Resultado da ferramenta SOLVER
Própria, 4=Agrícola e 5=Comercial). max 0 , 126 x 1 + 0 , 121 x 2 + 0 , 116 x 3 + 0 , 119 x 4 + 0 , 098 x 5 s.a x 1 + x 2 + x 3 + x 4 + x 5 ≤ 12 x 4 + x 5 ≥ 4, − 0 , 05 x 1 − 0 , 05 x 2 + x 3 ≥ 0 0 , 06 x 1 + 0 , 03 x 2 − 0 , 01 x 3 + 0 , 01 x 4 − 0 , 02 x 5 ≤ 0 x 1 , x 2 , x 3 , x 4 , x 5 ≥ 0 Para resolvermos este PPL, devemos proceder da mesma forma apresentada no exemplo da seção 1.2.1, só que o modelo deve ser parecido com o da gura 1.10. A gura 1.10 apresenta uma das possíveis maneiras de representar o PPL em uma planilha do Excel. Nesta planilha as células a seguir designarão cada uma das entidades:
- B7 irá representar o valor da FO a ser maximizada;
- B6 a F6 representarão os valores que as variáveis de decisão assumirão na solução;
- G11 a G14 irão representar os LHS das 4 restrições;
- H11 a H14 irão representar os RHS das 4 restrições. As fórmulas utilizadas são apresentadas na tabela a seguir.
Fórmulas utilizadas nas células da modelagem do Exemplo 2 B7 =B6B5+C6C5+D6D5+E6E5+F6F G11 =B11$B$6+C11$C$6+D11$D$6+E11$E$6+F11$F$ G12 =B12$B$6+C12$C$6+D12$D$6+E12$E$6+F12$F$ G13 =B13$B$6+C13$C$6+D13$D$6+E13$E$6+F13$F$ G14 =B14$B$6+C14$C$6+D14$D$6+E14$E$6+F14*$F$ A janela com os parâmetros do SOLVER é apresentado na gura 1.11 e a planilha com os resultados é mostrada na gura 1.12.
1.2.3 Problema da Fábrica de Motores
A LCL Motores Ltda., uma fábrica de motores especiais, recebeu recentemente R$90.000,00 em pedidos de seus três tipos de motores. Cada motor necessita de um determinado número de horas de trabalho no setor de montagem e de acabamento. A LCL pode terceirizar parte da sua produção. A tabela a seguir resume estes dados. Modelo 1 2 3 TOTAL Demanda 3000 unid. 2500 unid. 500 unid. 6000 unid. Montagem 1 h/unid. 2 h/unid. 0,5 h/unid. 6000 h Acabamento 2,5 h/unid. 1 h/unid. 4 h/unid. 10000 h Custo Produção R$50 R$90 R$ Terceirizado R$65 R$92 R$
A LCL Motores deseja determinar quantos motores devem ser produzidos em sua fábrica e quantos devem ser produzidos de forma terceirizada para atender à demanda de pedidos. Seja Fi o número de motores fabricados pela LCL do modelo i (i=1,2,3) e Ti o número de motores terceirizados pela LCL do modelo i(i=1,2,3). O modelo de decisão do problema é dado a seguir. min 50 F 1 + 90 F 2 + 120 F 3 + 65 T 1 + 92 T 2 + 140 T 3 s.a F 1 + 2 F 2 + 0 , 5 F 3 ≤ 6000 2 , 5 F 1 + F 2 + 4 F 3 ≤ 10. F 1 + T 1 = 3. F 2 + T 2 = 2. F 3 + T 3 = 500 Fi, Ti ≥ 0, ∀i=1,2, Para resolvermos este PPL, devemos proceder da mesma forma apresentada no exemplo da seção 1.2.1, só que o modelo deve ser parecido com o da gura 1.13. A gura 1.13 apresenta uma das possíveis maneiras de representar o PPL em uma planilha do Excel. Nesta planilha as células a seguir designarão cada uma das entidades:
- B7 irá representar o valor da FO a ser minimizada;
- B6 a G6 representarão os valores que as variáveis de decisão assumirão na solução;
- H11 a H15 irão representar os LHS das 5 restrições;
- I11 a I15 irão representar os RHS das 5 restrições. As fórmulas utilizadas são apresentadas na tabela a seguir. Fórmulas utilizadas nas células da modelagem do Exemplo 3 B7 =B6B5+C6C5+D6D5+E6E5+F6F5+G6G H11 =B11$B$6+C11$C$6+D11$D$6+E11$E$6+F11$F$6+G11$G$ H12 =B12$B$6+C12$C$6+D12$D$6+E12$E$6+F12$F$6+G12$G$ H13 =B13$B$6+C13$C$6+D13$D$6+E13$E$6+F13$F$6+G13$G$ H14 =B14$B$6+C14$C$6+D14$D$6+E14$E$6+F14$F$6+G14$G$ H15 =B15$B$6+C15$C$6+D15$D$6+E15$E$6+F15$F$6+G15$G$ A janela com os parâmetros do SOLVER é apresentado na gura 1.14 e a planilha com os resultados é mostrada na gura 1.15.
1.2.4 Problema da Escolha de Carteira de Investimentos
A LCL Investimentos S.A. gerencia recursos de terceiros através da escolha de carteiras de investi- mentos para diversos clientes, baseados em bonds de diversas empresas. Um de seus clientes exige que:
Figura 1.12: Resultados inseridos na planilha para o exemplo da seção 1.2.
Figura 1.13: Modelagem do Exemplo da seção 1.2.3 no Excel
Figura 1.14: Janela de entrada dos parâmetros do SOLVER
Figura 1.15: Resultados inseridos na planilha para o exemplo da seção 1.2.
Figura 1.16: Modelagem do Exemplo da seção 1.2.4 no Excel
A janela com os parâmetros do SOLVER é apresentado na gura 1.17 e a planilha com os
resultados é mostrada na gura 1.18.
- 1 SOLVER (EXCEL)
- 1.1 O que é o SOLVER?
- 1.2 Exemplos de como Modelar usando o SOLVER do Excel
- 1.2.1 Problema da Fábrica de Automóveis
- 1.2.2 Problema do Empréstimo do Banco
- 1.2.3 Problema da Fábrica de Motores
- 1.2.4 Problema da Escolha de Carteira de Investimentos
- 1.2.5 Problema da Mistura de Petróleo
- Bibliogra a
- 1.1 Modelagem do Exemplo da seção 1.2.1 no Excel Lista de Figuras
- 1.2 Janela da ferramenta SOLVER
- 1.3 Escolha da Célula de Destino
- 1.4 Janela do Solver após a designação das células variáveis
- 1.5 Formato da entrada da 1 a e 2 a restrições
- 1.6 Janela de entrada dos parâmetros do SOLVER para o Exemplo da seção 1.2.1
- 1.7 Janela de Opções do SOLVER
- 1.8 Opções de Resultado da ferramenta SOLVER
- 1.9 Resultados inseridos na planilha
- 1.10 Modelagem do Exemplo da seção 1.2.2 no Excel
- 1.11 Janela de entrada dos parâmetros do SOLVER
- 1.12 Resultados inseridos na planilha para o exemplo da seção 1.2.2
- 1.13 Modelagem do Exemplo da seção 1.2.3 no Excel
- 1.14 Janela de entrada dos parâmetros do SOLVER
- 1.15 Resultados inseridos na planilha para o exemplo da seção 1.2.3
- 1.16 Modelagem do Exemplo da seção 1.2.4 no Excel
- 1.17 Janela de entrada dos parâmetros do SOLVER
- 1.18 Resultados inseridos na planilha para o exemplo da seção 1.2.4
- 1.19 Modelagem do Exemplo da seção 1.2.5 no Excel
- 1.20 Janela de entrada dos parâmetros do SOLVER
- 1.21 Resultados inseridos na planilha para o exemplo da seção 1.2.5 - Fórmulas utilizadas nas células da modelagem do Exemplo
- B7 =B6B5+C6C5+D6D5+E6E5+F6F5+G6G
- H11 =B11$B$6+C11$C$6+D11$D$6+E11$E$6+F11$F$6+G11$G$
- H12 =B12$B$6+C12$C$6+D12$D$6+E12$E$6+F12$F$6+G12$G$
- H13 =B13$B$6+C13$C$6+D13$D$6+E13$E$6+F13$F$6+G13$G$
- H14 =B14$B$6+C14$C$6+D14$D$6+E14$E$6+F14$F$6+G14$G$
- H15 =B15$B$6+C15$C$6+D15$D$6+E15$E$6+F15$F$6+G15$G$
- H16 =B16$B$6+C16$C$6+D16$D$6+E16$E$6+F16$F$6+G16$G$
- H17 =B17$B$6+C17$C$6+D17$D$6+E17$E$6+F17$F$6+G17$G$
- H18 =B18$B$6+C18$C$6+D18$D$6+E18$E$6+F18$F$6+G18$G$
- H19 =B19$B$6+C19$C$6+D19$D$6+E19$E$6+F19$F$6+G19$G$
- Figura 1.18: Resultados inseridos na planilha para o exemplo da seção 1.2. Figura 1.17: Janela de entrada dos parâmetros do SOLVER
xA 4 + xZ 4 + xS 4 ≤ 1. 800
0 , 7 xS 1 − 0 , 3 xS 2 − 0 , 3 xS 3 − 0 , 3 xS 4 ≤ 0
− 0 , 4 xS 1 + 0, 6 xS 2 − 0 , 4 xS 3 − 0 , 4 xS 4 ≥ 0
− 0 , 5 xS 1 − 0 , 5 xS 2 + 0, 5 xS 3 − 0 , 5 xS 4 ≤ 0
0 , 7 xZ 1 − 0 , 3 xZ 2 − 0 , 3 xZ 3 − 0 , 3 xZ 4 ≤ 0
0 , 9 xZ 1 − 0 , 1 xZ 2 − 0 , 1 xZ 3 − 0 , 1 xZ 4 ≥ 0
0 , 3 xA 1 − 0 , 7 xA 2 − 0 , 7 xA 3 − 0 , 7 xA 4 ≤ 0
xA 1 , xA 2 , xA 3 , xA 4 , xZ 1 , xZ 2 , xZ 3 , xZ 4 , xS 1 , xS 2 , xS 3 , xS 4 ≥ 0
Já de nido o problema vamos agora modelá-lo no Excel. Para resolvermos este PPL, devemos proceder da mesma forma apresentada no exemplo da seção 1.2.1, só que o modelo deve ser parecido com o da gura 1.19.
Figura 1.19: Modelagem do Exemplo da seção 1.2.5 no Excel
A gura 1.19 apresenta uma das possíveis maneiras de representar o PPL em uma planilha do Excel. Nesta planilha as células a seguir designarão cada uma das entidades:
- C7 irá representar o valor da FO a ser maximizada;
- C6 a N6 representarão os valores que as variáveis de decisão assumirão na solução;
- O11 a O20 irão representar os LHS das 10 restrições;
- P11 a P20 irão representar os RHS das 10 restrições. As fórmulas utilizadas são apresentadas na tabela a seguir. Fórmulas utilizadas nas células da modelagem do Exemplo 5 C7 =C6C5+D6D5+E6E5+... +M6M5+N6N P11 =C11$C$6+D11$D$6+E11$E$6+... +M11$M$6+N11$N$ P12 =C12$C$6+D12$D$6+E12$E$6+... +M12$M$6+N12$N$ P13 =C13$C$6+D13$D$6+E13$E$6+... +M13$M$6+N13$N$ P14 =C14$C$6+D14$D$6+E14$E$6+... +M14$M$6+N14$N$ P15 =C15$C$6+D15$D$6+E15$E$6+... +M15$M$6+N15$N$ P16 =C16$C$6+D16$D$6+E16$E$6+... +M16$M$6+N16$N$ P17 =C17$C$6+D17$D$6+E17$E$6+... +M17$M$6+N17$N$ P18 =C18$C$6+D18$D$6+E18$E$6+... +M18$M$6+N18$N$ P19 =C19$C$6+D19$D$6+E19$E$6+... +M19$M$6+N19$N$ P20 =C20$C$6+D20$D$6+E20$E$6+... +M20$M$6+N20*$N$ A janela com os parâmetros do SOLVER é apresentado na gura 1.20 e a planilha com os resultados é mostrada na gura 1.21.
Figura 1.20: Janela de entrada dos parâmetros do SOLVER