Planilha de escala de trabalho Excel – Cálculo automático Solver
Planilha de escala de trabalho Excel – Cálculo automático Solver
Neste artigo é disponibilizada uma planilha de escala de trabalho Excel com cálculo automático da necessidade de funcionários para a escala, horário de entrada e saída, e também a quantidade de funcionários que estarão disponíveis em cada horário. Para este cálculo é utilizado o Solver para a solução do problema de forma automática.
Há problemas que são muito complexos para serem resolvidos sem utilizar programação ou ferramentas que nos auxiliam nos cálculos.
No exemplo deste artigo temos uma escala de trabalho de um Hospital, no qual temos que ter uma quantidade mínima de funcionários em cada horário, sem desperdiçar recursos contratando somente o mínimo necessário e ainda sabendo o horário que os funcionários irão entrar para trabalhar, o seu horário de saída e ainda a quantidade de funcionários que estarão disponíveis em cada horário. Ufa!!!
Para a solução de alguns problemas que são muito complexos podemos utilizar o SOLVER, que é uma ferramenta disponibilizada no Excel para resolução de problemas de cálculo linear. Veja mais sobre solver no artigo: Como utilizar o Solver – Álgebra linear no Excel.
Vamos então fazer passo-a-passo a solução deste problema.
Definição do problema
O Hospital da cidade está com problemas para definir a escala de trabalho da Enfermagem que deve seguir as seguintes regras:
O hospital deve ter funcionários disponíveis em todos os horários;
Os enfermeiros trabalham 6 horas por dia;
Deve haver uma quantidade mínima de funcionários conforme a tabela abaixo:[table id=2 /]
Deve ser definido quantos enfermeiros iniciarão em qual horário;
Deve ser calculado quantos enfermeiros deverão ser contratados no total para a escala.
Solução do problema – Preparando a estrutura
Montaremos então a estrutura para depois habilitar o Solver e realizar os cálculos necessários para a solução do problema. Caso tenha dúvidas sobre o problema ainda, você pode ver o nosso vídeo de treinamento disponível no topo deste artigo.
Crie a seguinte estrutura em uma planilha Excel:Ao lado esquerdo temo o horário de entrada, no topo temos o horário de trabalho, e na direita temos a quantidade de funcionários que irão entrar em cada horário.
Preencha o quadro de escala com 1, conforme a imagemEstes números representam os horários que cada funcionário irá trabalhar dentro do horário, por exemplo, um funcionário que iniciou ás 00:00h irá trabalhar 6h, até ás 05:00h, da mesma forma um funcionário que iniciou ás 23:00h trabalhará até ás 04:00h.
Preencha agora os seguintes camposNestes campos da escala de trabalho, iremos colocar a linha Plantonistas no horário, que indicará quantos plantonistas temos disponíveis naquele horário, na linha Plantonistas necessários, temos a quantidade de enfermeiros mínima para o horário e em contratar temos a quantidade de pessoas que devem ser contratadas para cumprir a escala de trabalho.
Em Plantonistas no horário digite a fórmula =SOMARPRODUTO(D2:D25;$AB$2:$AB$25) na primeira célula vazia e arraste para as demais. Este cálculo irá realizar a soma da multiplicação do intervalo D2:D25 pelo intervalo AB2:AB25, que é a coluna de quantidade de enfermeiros que entraram no horário.
Em contratar digite a função =SOMA(AB2:AB25), ela irá somar a quantidade de funcionários que entraram em cada horário e com isso teremos a quantidade de funcionários que precisam ser contratados.
Solução do problema – Cálculo escala de trabalho
Para a solução deste problema, como havíamos comentado no início, iremos utilizar o Solver, que é uma ferramenta disponível em todas as versões do Excel, e que realiza cálculos de álgebra linear. No início deste artigo há uma indicação de um artigo que explica exatamente a funcionalidade, por isso iremos diretamente na solução do problema neste artigo.
Para habilitar o Solver clique em Arquivo->Opções e selecione Suplementos;
Em Gerenciar selecione Suplementos, e clique no botão Ir, fica no final desta tela;
Na tela que segue selecione o suplemento Solver e clique em OK.
Neste momento o Solver já estará disponível para uso. Clique na guia Dados e no botão Solver, ele ficou disponível no canto direito desta guia;
Na tela que segue selecione em Definir objetivo a célula correspondente á Contratar, no caso C32 e marque a opção Mín, desta forma teremos que o nosso objetivo será o valor mínimo para a célula C32 na solução deste problema;
Em Alterando Células Variáveis selecione as células AB2:AB25, que é correspondente á quantidade de enfermeiros que iniciam em cada horário;
Em Sujeito ás restrições clique no botão Adicionar e preencha conforme a imagem. Estas restrições dizem que a quantidade de enfermeiros trabalhando nos horários deve ser maior ou igual ás quantidades mínimas definidas no problema.
Em selecionar um método de solução do problema, selecione o tipo LP Simplex. A sua configuração deve ficar assim.
Clique no botão Resolver. O Excel irá buscar uma solução para o problema definido, ele possui várias análises que podem ser vistas nesta tela, mas clique apenas em OK para ver a sua planilha solucionada.
Veja o resultado da análise, você tem então a quantidade de enfermeiros que irão entrar em cada horário na coluna Qtde. Entrada, na linha Plantonistas no horário você tem a quantidade de enfermeiros que estarão disponíveis em cada horário e em Contratar terá a quantidade mínima de funcionários que precisam ser contratados para cumprir as restrições do problema.
BÔNUS
Neste artigo você tem como realizar os cálculos para a montagem de uma escala de trabalho que resolve vários problemas complexos usando o Solver no Excel.
No vídeo no topo deste artigo você tem ainda mais detalhes, como criar um resumo especial da solução do problema, envolvendo funções com cálculos complexos de matriciais, transpor e formatação condicional, tudo muito bem explicado, além de tudo o que você leu de forma passo-a-passo.
Se puder dê um Joinha no vídeo, se inscreva para receber nossos treinamentos gratuitos semanais e compartilhe para nos ajudar a divulgar nossos trabalhos.
Abaixo o download da planilha desenvolvida, totalmente gratuita, basta colocar seu nome e e-mail e clicar no botão download. O sistema irá gerar um link para você fazer o download e mostrar uma oportunidade de treinamento especial em Excel.
Neste produto você verá detalhes de um sistema de planilha de escala de trabalho Excel, que realiza de forma automática a distribuição dos turnos e folgas entre os funcionários.
Adquirindo este produto você terá dois sistemas, um com um modelo de escala com uma folga por dia da semana e uma folga em um domingo por mês, ou ainda um sistema com atribuição automática de plantonistas nas folgas, essencial para postos de gasolina.
