O Solver é um suplemento gratuito desde os dias do Lotus 1-2-3
O Excel não foi o primeiro programa de planilha. O Lotus 1-2-3 não foi o primeiro programa de planilha. O primeiro programa de planilha foi o VisiCalc em 1979. Desenvolvido por Dan Bricklin e Bob Frankston, o VisiCalc foi publicado por Dan Fylstra. Hoje, Dan dirige a Frontline Systems. Sua empresa escreveu o Solver usado no Excel. Também desenvolveu um conjunto completo de software analítico que funciona com o Excel.
Se você tiver o Excel, terá o Solver. Pode não estar habilitado, mas você o tem. Para ativar o Solver no Excel, pressione alt = "" + T seguido por I. Adicione uma marca de seleção ao lado do Solver.
Para usar o Solver com sucesso, você deve construir um modelo de planilha que tenha três elementos:
- Deve haver uma única célula de objetivo. Esta é uma célula que você deseja minimizar, maximizar ou definir para um valor específico.
- Pode haver muitas células de entrada. Esta é uma melhoria fundamental em relação ao Goal Seek, que só pode lidar com uma célula de entrada.
- Pode haver restrições.
Seu objetivo é criar os requisitos de agendamento para um parque de diversões. Cada funcionário trabalhará cinco dias seguidos e depois terá dois dias de folga. Existem sete maneiras diferentes de agendar alguém para cinco dias consecutivos e dois dias de folga. Eles são mostrados como texto em A4: A10. As células azuis em B4: B10 são as células de entrada. Aqui é onde você especifica quantas pessoas você tem trabalhando em cada horário.
A célula da meta é a folha de pagamento total por semana, mostrada em B17. Isto é matemática simples: Total de Pessoas de B11 vezes $ 68 de salário por pessoa por dia. Você pedirá ao Solver para encontrar uma maneira de minimizar a folha de pagamento semanal.
A caixa vermelha mostra os valores que não serão alterados. Este é o número de pessoas que você precisa para trabalhar no parque em cada dia da semana. Você precisa de pelo menos 30 pessoas nos dias movimentados de fim de semana - mas apenas 12 na segunda e terça-feira. As células laranja usam SUMPRODUCT para calcular quantas pessoas serão agendadas a cada dia com base nas entradas nas células azuis.
Os ícones na linha 15 indicam se você precisa de mais pessoas, ou menos pessoas ou se você tem exatamente o número certo de pessoas.
Primeiro, tentei resolver isso sem o Solver. Fui com 4 funcionários por dia. Isso foi ótimo, mas não tive gente suficiente no domingo. Então, comecei a aumentar as agendas que me dariam mais funcionários aos domingos. Acabei com algo que funciona: 38 funcionários e US $ 2.584 de folha de pagamento semanal.
Clique no ícone Solver na guia Dados. Diga ao Solver que você está tentando definir o valor mínimo da folha de pagamento em B17. As células de entrada são B4: B10.
As restrições se enquadram em categorias óbvias e não tão óbvias.
A primeira restrição óbvia é que D12: J12 deve ser> = D14: J14.
Mas, se você tentasse executar o Solver agora, obteria resultados bizarros onde você teria um número fracionário de pessoas e possivelmente um número negativo de pessoas trabalhando em determinados horários.
Embora pareça óbvio para você que você não pode contratar 0,39 pessoas, você precisa adicionar restrições para dizer ao Solver que B4: B10 são> = 0 e que B4: B10 são inteiros.
Escolha Simplex LP como método de solução e escolha Solve. Em alguns momentos, o Solver apresenta uma solução ótima.
O Solver encontrou uma maneira de cobrir a equipe do parque de diversões usando 30 funcionários em vez de 38. A economia por semana é de US $ 544 - ou mais de US $ 7.000 durante o verão.
Observe as cinco estrelas abaixo de Funcionários necessários. O cronograma proposto pelo Solver atende às suas necessidades exatas de cinco dos sete dias. O subproduto é que você terá mais funcionários na quarta e quinta-feira do que realmente precisa.
Posso entender como o Solver encontrou essa solução. Você precisa de muitas pessoas no sábado, domingo e sexta-feira. Uma maneira de fazer as pessoas chegarem nesses dias é dar-lhes folga na segunda e na terça. É por isso que o Solver colocou 18 pessoas com folga na segunda e na terça.
Mas só porque o Solver apresentou uma solução ótima não significa que não existam outras soluções igualmente ótimas.
Quando eu estava tentando adivinhar a equipe, não tinha uma boa estratégia.
Agora que o Solver me deu uma das soluções ideais, posso colocar meu chapéu lógico. Ter 28 funcionários em idade universitária na quarta e quinta-feira, quando você só precisa de 15 ou 18 funcionários, vai causar problemas. Não haverá o suficiente para fazer. Além disso, com o número de funcionários exatamente correto em cinco dias, você terá que chamar alguém para horas extras se outra pessoa ligar dizendo que está doente.
Eu confio no Solver, pois preciso de 30 pessoas para fazer este trabalho. Mas aposto que posso reorganizar essas pessoas para equilibrar a programação e fornecer um pequeno buffer nos outros dias.
Por exemplo, dar a alguém folga na quarta e quinta-feira também garante que a pessoa esteja no trabalho na sexta, no sábado e no domingo. Portanto, movi manualmente alguns trabalhadores da linha de segunda, terça para a linha de quarta e quinta. Continuei conectando manualmente as diferentes combinações e descobri essa solução, que tem as mesmas despesas com a folha de pagamento do Solver, mas intangíveis melhores. A situação de excesso de pessoal agora existe em quatro dias em vez de dois. Isso significa que você pode lidar com cancelamentos de segunda a quinta sem ter que ligar para alguém do fim de semana.
É ruim eu ter conseguido uma solução melhor do que o Solver? Não. O fato é que eu não teria conseguido chegar a essa solução sem usar o Solver. Depois que o Solver me deu um modelo que minimizou os custos, pude usar a lógica sobre intangíveis para manter a mesma folha de pagamento.
Se você precisar resolver problemas mais complexos do que o Solver pode resolver, verifique os solucionadores premium do Excel disponíveis na Frontline Systems: http://mrx.cl/solver77.
Obrigado a Dan Fylstra e Frontline Systems por este exemplo. Walter Moore ilustrou a montanha-russa XL.
Assistir vídeo
- O Solver é um suplemento gratuito desde os dias do Lotus 1-2-3
- Solver é um produto do fundador da Visicorp Dan Fylstra
- O Solver em seu Excel é uma versão menor dos solucionadores de serviço pesado
- Saiba mais sobre solucionadores profissionais: http://mrx.cl/solver77
- Para instalar o Solver, digite alt = "" + T e, em seguida, I. Verifique o Solver.
- O Solver pode ser encontrado no lado direito da guia Dados
- Você deseja ter uma célula objetiva que está tentando minimizar ou maximizar.
- Você pode especificar várias células de entrada.
- Você pode especificar restrições, incluindo algumas que você não esperaria:
- Sem meio-pessoas: use INT para números inteiros
- O Solver encontrará uma solução ideal, mas pode haver outras que sejam empate
- Depois de obter a solução Solver, você poderá ajustá-la.
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2036 - Introdução ao Solver!
Tudo bem, estou enviando um podcast para este livro inteiro. Clique no “i” no canto superior direito para acessar a lista de reprodução, onde você pode reproduzir todos os vídeos!
Bem-vindo de volta ao netcast, sou Bill Jelen. Nós conversamos sobre algumas análises de variações hipotéticas recentemente, como Atingir Meta, você sabe, com uma célula de entrada que você está mudando, mas e se você tiver algo mais complexo? Existe uma ótima ferramenta chamada Solver, o Solver já existe há muito tempo, garanto que se você tem o Excel e está executando o Windows, tem o Solver, provavelmente ele não está ativado. Então, para ligá-lo, você precisa ir para alt = "" T e depois I, então T para Tom, I para sorvete, e marcar esta caixa para Solver, clicar em OK e, após alguns segundos, você terá uma guia Solver aqui no lado direito. Tudo bem, e vamos definir um modelo aqui que o solucionador possa resolver, temos um parque de diversões, estamos tentando descobrir quantos funcionários agendar. Todo mundo trabalha cinco dias consecutivos, então aí 'S realmente sete programações possíveis em que você está de folga, domingo segunda, segunda terça, terça quarta Precisamos descobrir quantos funcionários colocar em cada uma dessas programações.
E então apenas uma pequena matemática simples aqui, fazendo alguns SUMPRODUTOS, número de funcionários vezes no domingo para descobrir quantas pessoas estavam lá no domingo, segunda, terça, quarta-feira. E o que aprendemos ao operar esse parque de diversões é que precisamos de muitas pessoas no sábado e no domingo. 30 pessoas no sábado e no domingo, durante a semana segunda, terça, meio lento, 12 funcionários vão poder atender. Tudo bem então, apenas vindo aqui e apenas brincando, você sabe, tentando descobrir os números certos, você pode simplesmente continuar conectando as coisas, mas com sete opções diferentes, levaria uma eternidade, tudo bem.
Agora, no Solver, o que temos é uma série de células de entrada e, na versão gratuita do Solver, acho que você pode ter, cem? Não sei, há alguns números e, se você tiver que ir além disso, há um Premium Solver que você pode obter na Frontline Systems. Tudo bem, então temos algumas células de entrada, algumas células de restrição, e então você tem que trazer tudo para um número final. Então, no meu caso, estou tentando minimizar a folha de pagamento por semana, então esse número verde é o que eu quero tentar e otimizar, certo, então aqui está o que vamos fazer!
Solver, aqui está a célula objetiva, é a célula verde, e eu quero definir isso para um valor mínimo, descobrir a equipe que me dá o valor mínimo, alterando essas células azuis. E então aqui estão as restrições, certo, então a primeira restrição é que o total do cronograma tem que ser> = a seção vermelha, e podemos fazer tudo isso como uma única restrição. Veja como isso é legal, todas essas células têm que ser> = essas células correspondentes aqui, incrível, clique em Adicionar, certo, mas há outras coisas nas quais você não pensaria. Por exemplo, o Solver neste ponto pode decidir que é melhor ter 17 pessoas nesta programação, 43 pessoas na programação e -7 pessoas nesta programação. Tudo bem, então temos que dizer ao Solver que essas células de entrada devem ser um número inteiro, clique em Adicionar. E também, não podemos deixar que alguém não apareça,e eles vão nos devolver o salário deles, certo? Então, vamos dizer que essas células devem ser> = 0, clique em Adicionar, vamos voltar agora, temos nossas três restrições lá.
Existem três maneiras diferentes de resolver, e esta segue a matemática linear, então podemos usar o Simplex LP. Se este não funcionar, tente os outros dois, já tive casos em que o Simplex diz que não consegue encontrar uma solução e um dos outros dois funciona. A Frontline Systems tem ótimos tutoriais sobre o Solver. Estou apenas tentando mostrar a você o seu primeiro aqui hoje. Não proclamo ser um especialista em Solver. Uma vez eu tinha um Solver que não funcionava e enviei uma nota para a Frontline Systems e, uau, recebi de volta essa carta incrível de 5 páginas do próprio Dan Fylstra, o presidente do Solver! E começou: “Caro Bill, ótimo ouvir de você!” E então continuou por 4,9 páginas, tudo isso estava completamente acima da minha cabeça, certo. Mas você sabe, eu sei o suficiente sobre o Solver para passar por isso, certo,então vamos clicar aqui em Solve, ele encontrou uma solução, “Todas as restrições e condições de otimização foram satisfeitas.” Vou manter isso, posso criar alguns relatórios, não preciso fazer isso agora. Oh, eu posso realmente salvar um cenário, eu zombei dos cenários ontem, talvez o Solver consiga criar um novo cenário para mim, então clicaremos em OK.
Tudo bem, e com certeza isso nos salvou dinheiro, escrevemos 2584 antes, e agora nos reduziu a 2040. Então, precisamos de muitas pessoas de folga na segunda e terça-feira, certo, algumas pessoas, 2 pessoas de folga na quarta-feira, quinta-feira, e então sexta-feira sábado. Bem, isso é incrível, eu nunca teria sugerido aleatoriamente esse conjunto de respostas, certo, mas isso significa que é a melhor resposta? Bem, isso significa que é a folha de pagamento mínima, mas provavelmente posso chegar a um conjunto diferente de respostas que ainda teria essa folha de pagamento mínima. Existem outras maneiras de fazer isso, que pode ser um cronograma um pouco melhor. Por exemplo, agora temos 28 pessoas na quarta e quinta, quando só precisamos de 15 e 18, é muita gente. Pense em quem trabalha em parques de diversões, são universitários em casa para o descanso,isso vai ser um problema se tivermos tantas pessoas extras. E na segunda, terça, estaremos mortos, exatamente onde queremos estar. Então, isso significa que se alguém vai parar de ficar doente, agora vamos ter que, você sabe, chamar alguém e pagar uma hora e meia, porque eles já trabalharam cinco dias.
Tudo bem, então apenas com um pouco de matemática simples aqui, se eu pegasse 8 de segunda-feira terça-feira e aumentasse para 10, e pegasse esses 8 e adicionasse a quarta-feira quinta-feira, tudo bem. Agora eu tenho uma solução Solver com exatamente a mesma resposta, 2040, eles têm o número certo de pessoas. Acabei de equilibrar o cronograma e agora temos 8 extras, 8 extras, 3 extras e 2 extras, e exatamente o que precisamos no fim de semana que é, você sabe, o cenário de equipe completa. Para mim, isso é um pouco melhor do que o que o Solver criou. Isso significa que ele falhou? Não, absolutamente não, porque eu nunca teria chegado tão perto sem o Solver. Depois que o Solver me deu a resposta, sim, fui capaz de ajustá-lo um pouco e chegar lá, certo. Dica nº 37, “40 melhores dicas de Excel de todos os tempos”, chegando perto do final das primeiras 40, ótima introdução ao Solver.O guia para todos os podcasts desta série está aqui, “MrExcel XL - 40 melhores dicas de Excel de todos os tempos”, você pode ter o e-book por apenas $ 10, imprimir o livro por $ 25, clique no “i” no topo -Canto da mão direita!
Tudo bem, recapitulando: Solver, se você estiver nas versões do Excel para Windows, Lotus 1-2-3, está lá, criado pelo fundador da Visicorp, Dan Fylstra. É uma versão gratuita dos solucionadores pesados, aqui está um link para verificar os solucionadores pesados, que estará abaixo nos comentários do YouTube. É provável que eles simplesmente não estejam instalados, alt = "" TI, marque o Solver, procure no lado direito da guia Dados para localizar o Solver. Tudo bem, você deve ter uma célula objetiva que está tentando minimizar ou maximizar ou definir para um valor, um intervalo de células de entrada. Especifique as restrições, incluindo algo que não seria de esperar, como eu tive que dizer “Não pela metade” e “Nenhuma pessoa negativa”. O Solver encontrará a solução ideal, mas pode haver outros empatados e você pode ajustá-lo para obter uma solução melhor.
Tudo bem, aí está, eu quero agradecer por você passar por aqui, nos vemos na próxima vez para outro netcast de!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2036.xlsx
