Baixe o app para aproveitar ainda mais
Prévia do material em texto
Solver Dayse Mendes Introdução Nesta aula aprenderemos como solucionar problemas de Programação Linear usando um método computacional específico, o suplemento Solver do Microsoft Excel. Que tal conhecer este recurso? Objetivos de aprendizagem Ao final desta aula, você será capaz de: • entender como resolver problemas usando o Solver do Microsoft Excel. 1 O solver do excel Os problemas de Programação Linear, conforme Hillier e Lieberman (2013), podem ser resolvidos por vários métodos. Os meios por resolução gráfica ou manual só são eficazes para poucas variáveis de decisão. Para mais do que duas variáveis de decisão, se recomenda o computacional. O mais sim- ples e acessível é o uso de um suplemento do Microsoft Excel para testes de hipóteses, o Solver. Conforme o suporte do Microsoft Office (2017), o Solver pode ser utilizado para encontrar valor máximo ou mínimo para uma fórmula em uma célula, de acordo com possíveis restrições, ou limites, sobre os valores de outras células de fórmula em uma planilha. O Solver trabalha com um grupo de células, chamadas variáveis de decisão. Elas são usadas no cálculo das fórmulas nas células de objetivo e de restrição. O Solver ajusta os valores nas célu- las variáveis de decisão para satisfazer os limites sobre células de restrição e produzir o resultado ótimo para a célula objetiva. SAIBA MAIS! Além do Solver existem outros métodos computacionais de resolução de pro- blemas de Pesquisa Operacional, como o LINDO® – Linear Discrete Optimizer e o CPLEX®, para problemas de Programação Linear e Não Linear. Para Simulação há, entre outros, o ARENA®. Disponíveis em: <http://www.lindo.com>, <http://www. ILOG.com> e < http://www.paragon.com.br/arena-academico-student/>. Nesta aula trabalharemos com a versão do Solver aplicável ao Excel 2016, Excel 2013, Excel 2010 e Excel 2007, pois versões anteriores têm orientações diferentes. http://www.paragon.com.br/arena-academico-student/ FIQUE ATENTO! Versões do Excel anteriores a 2007 terão denominações diferentes para elementos que são inseridos nos parâmetros do Solver, para a resolução de problemas de Pesquisa Operacional; em especial os de Programação Linear. Figura 1 – Microsoft Excel Fonte: dennizn/Shutterstock.com Para que tenhamos solução proveniente do Solver, é necessário entender as etapas de utilização. 2 Modelagem do problema O primeiro passo para a resolução de um problema de Programação Linear, independente da ferramenta a ser utilizada, é a modelagem. Marins (2011) comenta que a modelagem estabelece as equações e inequações lineares que demonstram como é o problema em termos de formula- ção matemática. Definimos as variáveis de decisão, e formulamos a função objetivo, as inequa- ções das restrições técnicas e de não negatividade. Todas estas formulações são estruturadas com base nas informações sobre o problema. EXEMPLO Um exemplo adaptado de Barbosa e Zanardini (2015) pode nos ajudar a observar como se faz a modelagem de um problema de Programação Linear. Para fazer cada terno, um alfaiate utiliza 2 metros de algodão, 1 metro de seda e 1 metro de lã. Para cada vestido, usa 1 metro de algodão, 2 metros de seda e 3 metros de lã. Os recursos disponíveis são: 16 metros de algodão, 11 metros de seda e 15 metros de lã. A receita unitária da venda de cada terno é de R$300,00 e a receita de cada vestido é R$500,00. Quantos ternos e quantos vestidos devem ser produzidos para maximizar a receita deste alfaiate? Variáveis de decisão: x1 = quantidade a produzir de ternos x2 = quantidade a produzir de vestidos Função objetivo: MAX R = 300 x1 + 500 x2 Sujeito às restrições: 2x1 + 1x2 ≤ 16 restrição de algodão 1x1 + 2x2 ≤ 11 restrição de seda 1x1 + 3x2 ≤ 15 restrição de lã x1, x2 ≥ 0 3 Executando o solver no excel Para poder utilizar o Solver na resolução de problemas de Programação Linear, é necessário primeiro habilitá-lo no Excel. Como qualquer outro suplemento, precisamos instalá-lo e ativá-lo. Para executar essa operação é só seguir os passos do suporte do Office (2017). • Clique na guia Arquivo. • Clique em Opções e, em seguida, na categoria Suplementos. • Próximo ao final da caixa de diálogo Opções do Excel, verifique se Suplementos do Excel está selecionado na caixa Gerenciar e clique em Ir. • Na caixa de diálogo Suplementos, selecione Ferramentas de Análise e Solver; clique em OK. • Se o Excel exibir uma mensagem declarando que não pode executar esse suplemento e solicitar que você o instale, clique em Sim para instalá-lo. • Na guia Dados, observe que um grupo Análise foi adicionado. Esse grupo contém botões de comando de Análise de Dados e do suplemento Solver. Figura 2 – Habilitando o Solver Fonte: elaborada pela autora, 2017. Com o Solver habilitado no Excel, é possível começar a inserção dos dados para a resolução do problema. 4 Inserindo os dados do problema na tela do solver Para usar o Solver na resolução de um problema de Programação Linear, primeiro precisa- mos modelar e, posteriormente, inseri-lo modelado na tela. Ao fazer esta transposição devemos entender a lógica do Excel com relação às formulações matemáticas. O suporte do Office (2017) sugere alguns passos para esta inserção. O primeiro consiste em definir um conjunto de células em que se obterá os valores das variáveis de decisão. Logo após se define um outro conjunto de células para custos, lucros, receitas e para os recursos disponíveis, ou seja, para os parâmetros do problema. Finalmente se reserva uma célula para a função objetivo. EXEMPLO No conjunto de células do Excel para os parâmetros e na célula para a função ob- jetivo, montamos as equações e inequações necessárias à resolução do problema. Utilizando um exemplo adaptado de Barbosa e Zanardini (2015) podemos observar como montar as equações na planilha do Excel no quadro Planilha de problema para resolução no Solver. Temos duas variáveis de decisão, uma função objetivo de maximização de receita e três restrições técnicas, de acordo com o exemplo, que devem ser inseridas no Excel. Figura 3 – Planilha de problema para resolução no Solver Fonte: elaborado pela autora, 2017. Cada célula da planilha do Excel montada para a resolução do problema terá uma diretriz, conforme segue. • O número de linhas será igual ao número de restrições técnicas. • Na célula b9 se coloca o valor multiplicado à variável de decisão x1 na inequação. • Na célula c9 se coloca o valor multiplicado à variável de decisão x2 na inequação. • Na célula f9 se coloca o valor disponível do recurso relacionado à restrição 1. • Na célula f10 se coloca o valor disponível do recurso relacionado à restrição 2. • Na célula f11 se coloca o valor disponível do recurso relacionado à restrição 3. • Na célula d9 se insere a fórmula da restrição 1, com os itens à direita da inequação. • Na célula d10 se insere a fórmula da restrição 2, com os itens à direita da inequação. • Na célula d11 se insere a fórmula da restrição 3, com os itens à direita da inequação. • Nas células e9, e10 e e11 se coloca o sinal da inequação como indicação para a configuração no Solver. Deve ficar claro que, num primeiro momento, se insere toda a modelagem no Excel como em qualquer outra situação de cálculo em que se usa este aplicativo. FIQUE ATENTO! Para inserir corretamente as equações e inequações na planilha de resolução do Solver, não esqueça de fazer as operações algébricas necessárias. Como, por exemplo, na célula da função objetivo programar um SOMAPRODUTO entre as cé- lulas do resultado das variáveis com as dos valores unitários de contribuição de cada variável à função objetivo. 5 Configuração das opções Após a inserção de todos os dados na planilha, é possível configurar o Solver para a resolução do problema. É preciso ir até a aba Dados do Excel e clicar em Solver. A tela com os parâmetros do programa vai aparecer. Veja a figura Configuração do Solver. Figura 4 – Configuração do Solver Fonte: MICROSOFT, 2017. O primeiro passoconsiste em selecionar a célula que contém a função objetivo. Logo após se define qual o objetivo desejado, clicando em Máx para maximizar o resultado; Mín para minimi- zar o resultado; ou em Valor, caso deseje que a célula objetivo tenha um valor definido. O próximo passo é à configuração das restrições do problema. Para configurar, devemos, na caixa de Sujeito às Restrições, inseri-las procedendo da seguinte forma: • na caixa de diálogo Parâmetros do Solver, clicar em Adicionar; • na caixa Referência de Célula, inserir a referência de célula em que está a fórmula da restrição, do lado esquerdo na linha da restrição na planilha; • clicar na relação <=, =, >= desejada entre a célula referenciada e a restrição; • finalmente selecionar a célula do lado direito na linha da restrição na planilha; • adicionar cada restrição, até finalizar todas; • quando finalizar, pode clicar em ok. O próximo passo é clicar em Tornar as Variáveis Irrestritas Não Negativas para obedecer a restrição de não-negatividade. SAIBA MAIS! Na caixa de diálogo do Solver é possível especificar até 200 células variáveis. Assim, conseguimos resolver problemas com grande quantidade de dados, me- lhorando o alcance de solução otimizado em relação aos métodos manuais. Veja mais dicas no site de Suporte da Microsoft. Disponível em: <https://support.offi- ce.com/pt-br/article/definir-e-resolver-um-problema-usando-o-Solver-5d1a388f- -079d-43ac-a7eb-f63e45925040?ui=pt-BR&rs=pt-BR&ad=BR&fromAR=1>. Enfim selecionamos o Método de Solução. No caso de Programação Linear, usamos LP Sim- plex. Agora, clicar em Resolver (ou Solver, conforme a versão de Excel) e a solução aparecerá na planilha montada. FIQUE ATENTO! Ao configurar as opções do Solver para resolução de problema de Pesquisa Ope- racional, não esqueça de selecionar o método adequado. Quando se tratar de um problema de Programação Linear, será o LP Simplex (Hillier e Lieberman, 2017). Fechamento Nesta aula, você teve a oportunidade de: • entender os passos necessários para habilitar o suplemento Solver do Excel; • compreender como inserir informações no Solver para a resolução de problemas de Pesquisa Operacional. https://support.office.com/pt-br/article/definir-e-resolver-um-problema-usando-o-Solver-5d1a388f-079d-43ac-a7eb-f63e45925040?ui=pt-BR&rs=pt-BR&ad=BR&fromAR=1 https://support.office.com/pt-br/article/definir-e-resolver-um-problema-usando-o-Solver-5d1a388f-079d-43ac-a7eb-f63e45925040?ui=pt-BR&rs=pt-BR&ad=BR&fromAR=1 https://support.office.com/pt-br/article/definir-e-resolver-um-problema-usando-o-Solver-5d1a388f-079d-43ac-a7eb-f63e45925040?ui=pt-BR&rs=pt-BR&ad=BR&fromAR=1 Referências BARBOSA, Marcos Antonio; ZANARDINI, Ricardo Alexandre Deckmannn. Iniciação à pesquisa ope- racional no ambiente de gestão. 3. ed. Curitiba: Intersaberes, 2015. HILLIER, Frederick; LIEBERMAN, Gerald. Introdução à pesquisa operacional. 9. ed. Porto Alegre: AMG Editora, 2013. Disponível em <https://books.google.com.br/books?id==-88A0a-Kx0QC&pg=P3A&dq- introdu%C3%A7%C3%A3o+a+pesquisa+operacional&hl=pt-BR&sa=X&ved=0ahUKEwi3_O2au- vPUAhXKIpAKHZs2BUIQ6wEIKDAA#v=onepage&q=introdu%C3%A7%C3%A3o%20a%20pes- quisa%20operacional&f=false>. Acesso em 04 jul. 2017. IBM. IBM ILOG CEPLEX Optimization Studio. Disponível em: <http://www.ILOG.com>. Acesso em: 24 ago. 2017. LINDO SYSTEMS INC. Linear Discrete Optimizer. Disponível em: <http://www.lindo.com>. Acesso em: 24 ago. 2017. MARINS, Fernando Augusto Silva. Introdução à Pesquisa Operacional. São Paulo: Cultura Acadê- mica, 2011. MICROSOFT OFFICE. Definir e resolver um problema usando o Solver. Disponível em: <https:// support.office.com/pt-br/article/Definir-e-resolver-um-problema-usando-o-Solver-5d1a388f- -079d-43ac-a7eb-f63e45925040>. Acesso em: 01 mar. 2017. PARAGON DECISION SCIENCE. Arena Acadêmico (student). Disponível em: <http://www.paragon. com.br/arena-academico-student/>. Acesso em: 24 ago. 2017. http://www.ILOG.com http://www.lindo.com https://support.office.com/pt-br/article/Definir-e-resolver-um-problema-usando-o-Solver-5d1a388f-079d-43ac-a7eb-f63e45925040 https://support.office.com/pt-br/article/Definir-e-resolver-um-problema-usando-o-Solver-5d1a388f-079d-43ac-a7eb-f63e45925040 https://support.office.com/pt-br/article/Definir-e-resolver-um-problema-usando-o-Solver-5d1a388f-079d-43ac-a7eb-f63e45925040 _hl0fd9hy6267 _ygbyvuf7yj2y _GoBack
Compartilhar