#excel1


Minimizando e Maximizando funções do Excel com Solver e OpenSolver


O que é o Solver? Nas palavras da própria Microsoft:


“O Solver é
um suplemento do Microsoft Excel que você pode usar para teste de
hipóteses…”


E o que isso quer dizer? A frase seguinte explica:


“Use o Solver para encontrar um valor ideal
(máximo ou mínimo) para uma fórmula em uma célula — conforme restrições, ou limites,
sobre os valores de outras células de fórmula em uma planilha.”


Excelente! Uma ferramenta para maximizar e minimizar funções
com uma modelagem simples no Excel!


Quantas vezes em engenharia não nos encontramos testando, de
forma braçal, qual a solução mais viável para questões do tipo:


“quais as melhores datas para fazer pedidos de material,
dada a capacidade de produção e estoque?”


“qual a melhor proporção de produtos a serem fabricados,
dados os riscos e rentabilidades diferentes?”


e os dois exemplos que veremos:


“quais projetos aceitar de forma a maximizar o lucro e não
sobrecarregar a equipe?”


“qual a melhor compra de sucos para maximizar o lucro e
manter a qualidade?”


Ok, mas como faço para o Excel entender esses problemas?
Vamos primeiro entender como o Solver funciona.


A ferramenta nativa do Excel é excelente e poderia ser usada
nos exemplos que veremos. Porém, por ela ter algumas limitações, vamos
“apelar” para uma ferramenta um pouco mais robusta: o “OpenSolver” –
uma ferramenta praticamente idêntica, porém mais poderosa e gratuita.


Para baixar o OpenSolver basta ir a “https://openSolver.org/installing-openSolver/”,
baixar o arquivo e salvar a pasta extraída em local mais conveniente.


Na pasta, você vai encontrar um arquivo do tipo “Microsoft
Excel Add-In” (.xlam). Abra esse arquivo e aceite os avisos de segurança.
Pronto. OpenSolver habilitado!




Na aba Dados, no extremo direito, você verá as ferramentas
do OpenSolver.




Vamos explorar primeiro a janela de modelo “Model”.




Nesta janela, vemos algumas partes importantes do nosso
problema.


Primeiramente, vamos definir nossa célula de objetivo, a
qual temos a opção de maximizar o valor, minimizar o valor, ou chegar em um
valor predefinido. Esta última opção, não utilizo muito. Como veremos, é mais
simples modelar como uma restrição o valor alvo, do que habilitar no modelo,
dando a chance de flexibilizar um segundo parâmetro (ficará mais claro à
frente).




Em seguida, devemos definir as células variáveis, isso é, as
células que o Excel terá a liberdade de alterar para encontrar o maior (ou
menor) valor possível para a célula objetivo.




Por último, basta definir as restrições que queremos que o
Excel obedeça. Podemos definir células ou intervalos que queremos manter em uma
determinada faixa de valores, definir como inteiro etc.




Há algumas opções extras, como fazer com que as variáveis
testadas não possam ser negativas.


Assim como há algumas opções de definições da modelagem,
deixaremos todas no padrão da aplicação para estes exemplos.




Vamos ao nosso primeiro exemplo prático:


Suponha que você é o gerente de um departamento de
empreendimentos. Você recebe uma planilha com as demandas conhecidas, neste caso,
50 (ou 100, ou 200, não fará diferença no nosso exemplo, o cálculo ainda será
muito rápido).


Suas informações são:


Valor de cada projeto


Data de início de cada projeto


Seu departamento é dividido em 4 áreas: Técnica,
Administrativa, Conferência e Jurídica.


O “Custo de pessoal” por projeto foi definido da seguinte
forma:


Departamento técnico – Até 30 Mi 1
pessoa 1 mês, >30 Mi 2 pessoas 2 /meses


Departamento administrativo – Até
100 Mi 1 pessoa, >100 Mi 2 pessoas


Departamento Conferência– Até 100 Mi
1 pessoa, >100 Mi 2 pessoas


Departamento jurídico– Até 30 Mi 1
pessoa, entre 30 e 50Mi 2 pessoas, >70 Mi 2 pessoas


Isto é, o departamento jurídico aumenta o custo de pessoal,
porque tem aprovações extras com os conselhos da empresa aumentando, assim, o
trabalho. Desta forma, definiremos o projeto em 6 ciclos.


Ciclo 1 : Técnico


Ciclo 2: Administrativo


Ciclo 3: Conferência


Ciclo 4: Jurídico


Ciclo 5: Jurídico >30


Ciclo 6: Jurídico >70


Vamos montar essa planilha!




De A3 a A52 nomeamos os projetos de 1 a 50. Na coluna B,
vamos gerar valores aleatórios do projeto, na coluna índice, serão valores de 1
a 24 aleatórios. Esse índice é o mês de meta de início dos projetos.


Prioridade é uma coluna em que será marcada: 1 caso seja
obrigado a escolher o projeto e, 0 caso seja livre.


Os ciclos testam o valor do projeto e os profissionais são a
soma de cada tipo de profissional, sendo: 1 o técnico, 2 administrativo, 3
conferência e 4 jurídico.


A Planilha ficará assim:




Os valores foram gerados aleatoriamente, bem como as
prioridades. As fórmulas do ciclo 1 a 6 são apenas para quantificar os
profissionais. Por enquanto, aparenta tudo zerado, mas é porque a fórmula testa
se o projeto foi ou não escolhido. As fórmulas de E3 a O3 são:


E3: “=SE(B3>30000000;2;1)*P3” – testa o valor do projeto
e retorna 1 ou 2 profissionais


F3: “=SE(B3>100000000;2;1)*P3” – testa o valor do projeto
e retorna 1 ou 2 profissionais


G3: “=SE(B3>100000000;2;1)*P3” – testa o valor do projeto
e retorna 1 ou 2 profissionais


H3: “= SE(B3>100000000;2;1)*P3” – testa o valor do
projeto e retorna 1 ou 2 profissionais


I3: “=SE(B3>30000000;1;0)*P3” – testa o valor do projeto
e retorna 0 ou 1 profissionais


J3: “=SE(B3>70000000;1;0)*P3” – testa o valor do projeto
e retorna 0 ou 1 profissionais


K3: “=SOMA(E3:J3)” – Soma o total de profissionais


L3: “=E3” – total profissional técnico


M3: “=F3”- total profissional administrativo


N3: “=G3” – total profissional conferência


O3: “=H3+I3+J3”- total profissional jurídico


Nossa planilha está quase pronta! Agora, já temos os
projetos e as datas, assim como calculamos o “custo da mão de obra”.


Vamos então montar a segunda parte, em que distribuiremos a
demanda no tempo e, então, entre os profissionais.


Coloque a seguinte fórmula em R3 (repare que há colunas e
linhas absolutas. É importante escrever nesta formulação, para ser simples de
arrastar até a coluna AO onde está o 24 índice – final do período estudado -, e
depois arrastar todas as colunas até a linha do último projeto):


R3: “=(SE(R$1=$C3);1;0))*$P3”
– testa a meta de início do projeto, e retorna 0 ou 1 caso o mês esteja
ou não com aquele projeto em curso. O tempo de projeto foi considerado igual à
quantidade de profissionais, sendo 1 mês por profissional. Caso prefira, o
exemplo pode ser feito sem essa simplificação adicionando uma coluna de
durações. Como no nosso exemplo os profissionais e o tempo são considerados
funções diretas do valor, não vamos complicar mais com a coluna de durações.




Depois de arrastar:




Pronto. Nosso cronograma está populado. Vamos agora ao que
queremos medir: meus profissionais estão sobrecarregados?


Para isso, vamos retornar às premissas do problema: a última
informação pertinente é, quantas pessoas eu tenho por área? Vamos supor então:


Técnico: 10


Administrativo: 4


Conferência : 2


Jurídico : 4


Com isso, vamos montar a seguinte planilha para distribuir
as demandas por área.




Montamos logo abaixo da planilha anterior, na linha 62 os
meses de janeiro a dezembro dos dois anos de análise, além de inserir o nosso
controle de índice do mês em que estamos.


Na célula B62, vamos inserir a seguinte formula:


“=SOMASE($C$3:$C$52;B60;$L$3:$L$52)”


Arraste a fórmula da célula B62 até a Y62.


 Na célula B69 vamos
inserir a seguinte fórmula:


“=SOMASE($C$3:$C$52;B60;$M$3:$M$52)”


Arraste a fórmula da célula B69 até a Y69.


Na célula B76 vamos inserir a seguinte fórmula:


“=SOMASE($C$3:$C$52;B60;$N$3:$N$52)”


Arraste a fórmula da célula B76 até a Y76.


Na célula B83 vamos inserir a seguinte fórmula:


“=SOMASE($C$3:$C$52;B60;$O$3:$O$52)”


Arraste a fórmula da célula B83 até a Y83.




Pronto! Já temos praticamente tudo para o nosso cálculo.
Vamos apenas fazer 2 ajustes para ficar condizente com nosso problema.


Primeiro ajuste


A fórmula da forma como colocada, só soma os trabalhos
iniciados no mês.  Como não temos uma
coluna de durações, vamos supor o pior caso possível (que os projetos mais
caros, além de ocupar mais profissionais, também os ocupam por igual tempo).
Por exemplo, um projeto que custa 3 profissionais técnicos, também dura 3 meses
na área técnica. Essa suposição pode ser facilmente trocada e ajustada para que
a planilha leve isso em consideração. No nosso caso, como é um exemplo teórico
seguiremos assim para simplificar.


Realizando o primeiro ajuste, faremos o seguinte: contaremos
se em determinado mês houve um profissional a mais, ou se houve mais. Nesse
exemplo, iremos até o 3, mas poderiam ser testados quantos quisessem (nossa
planilha não passou de 3 em nenhum momento).


Na célula B63 e B64, vamos inserir as seguintes fórmulas:


B63: “=CONT.SES($C$3:$C$52;B$60;$L$3:$L$52;2)”


B64: “=CONT.SES($C$3:$C$52;B$60;$L$3:$L$52;3)”


Arraste as fórmulas da coluna B até a Y.


Na célula B70 e B71, vamos inserir as seguintes fórmulas:


B63: “=CONT.SES($C$3:$C$52;B$60;$M$3:$M$52;2)”


B64: “=CONT.SES($C$3:$C$52;B$60;$M$3:$M$52;3)”


Arraste as fórmulas da coluna B até a Y.


Na célula B77 e B78, vamos inserir as seguintes fórmulas:


B63: “=CONT.SES($C$3:$C$52;B$60;$N$3:$N$52;2)”


B64: “=CONT.SES($C$3:$C$52;B$60;$N$3:$N$52;3)”


Arraste as fórmulas da coluna B até a Y.


Na célula B84 e B85, vamos inserir as seguintes fórmulas:


B63: “=CONT.SES($C$3:$C$52;B$60;$O$3:$O$52;2)”


B64: “=CONT.SES($C$3:$C$52;B$60;$O$3:$O$52;3)”


Arraste as fórmulas da coluna B até a Y.


Mas o que fizemos?


Contamos quais meses a demanda é 2 ou 3 (não havia
resultados com 4 ou maior) e então, vamos somar as demandas de 2 ao mês
seguinte e as demandas de 3 aos 2 meses seguintes. Para representar melhor as
ocupações naqueles meses, faremos o seguinte:


D65:
“=D62+C63+B64”


D72:
“=D69+C70+B71”


D79:
“=D76+C77+B78”


D86: “=D83+C84+B85”


Agora temos as demandas reais por área, se arrastarmos essas
fórmulas entre as colunas até a Y. Começamos pela coluna D, porque as colunas B
e C apresentarão erros por referenciar células de texto na operação. Ajustando
essas colunas pra não pegar a referência errônea, temos:




Segundo ajuste


Como os meses de meta eram o início dos projetos, vamos
transladar as atividades das equipes 2 a 4 para representar melhor as data de
início e fim dos setores:




Pronto! Agora já podemos definir nossas metas e rodar!


Primeiro, vamos tirar a média da carga de trabalho por
equipe. Faça nas células:


B66: ”=B65/10”


B73: ”=B72/4”


B80: ”=B79/2”


B86: ”=B85/10”


Arraste todas até a coluna AC.


Em seguida, defina uma meta. Assim, faremos a linha meta
igual a 2, porque nosso modelo vai tentar manter a carga de trabalho por
funcionário abaixo de 2 (poderia ser um intervalo ou outros valores, sinta-se
livre pra testar).




Agora faça a seguinte operação, na célula B55:


“=SOMARPRODUTO(B3:B52;P3:P52)”


Isso mostra o total dos projetos aceites.


Pronto!


Já temos tudo para nosso modelo.


Abra a janela do modelo do Solver e faça:




Isso diz ao Solver que ele deve maximizar o valor obtido na
célula B55 de acordo com as opções de aceite ou não aceite da coluna P.


Além disso, temos que definir nossas restrições.


Definimos que a carga de trabalho da equipe de B66 a AC66
deve ser menor que a carga meta da linha 67, assim como as linhas 73, 80, 86
serão menores que as respectivas metas.


Teremos assim:




Mas temos ainda mais algumas restrições: a escolha do
projeto tem que ser no máximo a quantidade disponível. Assim, faremos a coluna
“estoque”, que seria a quantidade disponível, e obrigar nossa escolha a ser
menor que ela!




Agora, vamos definir os projetos que tem prioridade 1 como
uma escolha automática, igualando as células da escolha a 1.




Por último, vamos definir o intervalo de escolha como
inteiros para não ter soluções fracionárias:






Pronto! Aperte em salvar o modelo!


Aceite o aviso, e vá a aba “dados” dentro das
ferramentas do Solver, clique em Show/Hide model.


Isso ativará a parte visual do modelo para conferência:








Mas você não precisa dessa parte! Apenas volte a aba e
clique em “Solve”.




SEGURA ESSA PLANILHA JÁ COM AS ESCOLHAS FEITAS!!




Repare que na coluna P ele não escolheu alguns dos
projetos!!!


Por quê? Para satisfazer nossa restrição e não saturar as
equipes.




Repare que as equipes estão claramente desbalanceadas, pois,
a equipe 4 está sobrecarregada nos meses de novembro do primeiro ano e maio do
segundo ano.


Enquanto isso, a equipe 1 está sempre tranquila. Isso mostra
que os projetos recusados devem ser repactuados em outras datas, ou que você
deve realocar pessoas da equipe, ou ainda, caso não queira recusar os projetos
independente da formatação atual, podemos alterar esse problema, em que a nova
pergunta não seria “quais projetos aceitar?”, e sim, “quais devo
aceitar para sobrecarregar o mínimo possível a equipe?”.


Já voltaremos a isso. Vamos explorar a planilha que já temos,
a segunda opção será assunto do próximo blog.


Primeiro: o que acontece se mudamos as datas dos 10
primeiros projetos pra janeiro? Vai gerar um acúmulo que nos leva a crer que
seriam aceitos só os poucos projetos de maior valor. Vamos testar!




Deu certo! Repare que, dos 11 primeiros projetos que tinham
a data de janeiro do primeiro ano (índice 1), apenas 4 foram escolhidos para
atender nosso critério!


Otimização de EquipeBaixar