Aprenda como construir do zero uma planilha de horas trabalhadas Excel que atualiza automaticamente as informações!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Nesta aula eu quero te mostrar com construir do zero uma planilha de horas trabalhadas Excel de forma automática.
Vamos partir de um arquivo em branco e eu vou te mostrar o passo a passo para construir essa planilha que vai ser muito útil para você calcular as horas trabalhadas e as horas extras dos funcionários da sua empresa.
Esse processo não é nada complexo e você poderá incrementar a planilha como desejar, adapatando-a para a sua realidade. Tendo assim um controle de horas que se encaixe ao seu negócio.
O objetivo desta planilha será registrar diariamente a hora de entrada e saída dos funcionários. Para isso, é necessário incluir o nome do funcionário, a data, o horário de entrada e saída, o horário de saída para o almoço e o retorno do almoço.
Além disso, é importante registrar a carga horária especificada que o funcionário deveria cumprir e a carga horária efetivamente trabalhada, que representará o total de horas trabalhadas pelo funcionário naquele dia.
Por fim, a planilha também calculará as horas extras, que correspondem à diferença entre a carga horária efetivamente trabalhada e a carga horária especificada.
Com as nossas colunas definidas, podemos formatá-las como uma tabela para criar um padrão de formatação para nossas informações.
Para isso, vá até a guia Inserir, selecione a opção Tabela e clique em OK.
Vamos adicionar alguns funcionários e algumas informações para cada um deles.
A carga trabalhada e a hora extra não serão preenchidas manualmente. Em vez disso, vamos utilizar fórmulas para calcular esses valores automaticamente.
O cálculo da carga horária trabalhada será feito com base nas informações de Entrada, Saída Almoço, Retorno Almoço e Saída. Para realizar esse cálculo, utilizaremos a seguinte fórmula:
= [@[Saída Almoço]] - [@Entrada] + [@Saída] - [@[Retorno Almoço]]Isso significa que estamos subtraindo o horário de entrada do horário de saída para o almoço e, em seguida, somando esse valor à diferença entre o horário de saída e o retorno do almoço.
Para calcular as horas extras, é importante que a carga horária e a carga trabalhada estejam no mesmo formato. Portanto, ao invés de escrever “8” na Carga Horária, iremos definir como “08:00”.
E o cálculo que utilizaremos será bem simples:
=[@[Carga Trabalhada]]-[@[Carga Horária]]
Vamos preencher as informações dos outros funcionários também.
Perceba que a Camila e a Marcelle trabalharam menos do que a carga horária, portanto, as horas extras delas resultam em um valor negativo, o que é exibido como ############. Isso é um “erro” que ocorre no Excel quando lidamos com horas negativas.
O Excel, por padrão, não está configurado para aceitar números negativos associados a datas ou horas. Para resolver esse problema, é necessário utilizar um formato de data chamado Data 1904.
Para ativar o formato de data 1904 no Excel, você precisa ir em Arquivo > Opções > Avançado. Nessa janela você irá buscar por “Usar sistema de data 1904” e selecionar essa opção.
Feito isso, as horas que antes estavam dando erro agora serão exibidas.
Dessa forma, temos nosso controle de horas de entrada e saída completo, incluindo o cálculo das horas extras.
Agora, vamos adicionar mais algumas informações à nossa tabela para o funcionário Fred.
Já temos nossa tabela de controle de horas funcionando perfeitamente. Agora precisamos criar uma tabela de cadastro de funcionários.
Vamos criar uma segunda planilha neste arquivo chamada Cadastro de Funcionários e podemos renomear a primeira para Controle de Horas.
No cadastro dos funcionários, precisamos incluir o nome, cargo, carga horária e salário. Portanto, vamos criar uma coluna para cada uma dessas informações e preencher com alguns dados de exemplo.
Vamos também formatar esta tabela como fizemos com a de Controle de Horas e podemos formatar a coluna Salário como moeda.
Além disso, podemos adicionar uma coluna que irá nos informar o valor pago por cada hora extra trabalhada. Embora exista um cálculo exato que os departamentos de Recursos Humanos utilizam para calcular o valor da hora extra, utilizaremos uma versão mais simplificada como exemplo.
Vamos considerar 22 dias trabalhados, então nossa fórmula ficará assim:
=[@Salário]/(HORA([@[Carga Horária]])*22)Nessa fórmula, estamos dividindo o salário total pela quantidade de dias trabalhados multiplicados pela carga horária diária.
Com nossa tabela de cadastro de funcionários pronta, podemos adaptar a nossa tabela de Controle de Horas. Em vez de informar a carga horária manualmente, podemos utilizar a função PROCV para puxar essa informação automaticamente.
Voltando à nossa tabela de Controle de Horas, vamos inserir a seguinte fórmula na coluna de Carga Horária:
=PROCV([@Funcionário];'Cadastro de Funcionários'!A:E;3;FALSO)Caso você queira entender tudo sobre como essa função PROCV funciona e outras funções PROC no Excel, confira esta aula aqui!
Agora, com essa fórmula aplicada, a carga horária dos nossos funcionários será preenchida automaticamente.
Podemos observar que a Carga Horária da Marcelle foi atualizada de 08:00 para 05:00.
Além disso, para garantir que apenas os funcionários cadastrados apareçam na nossa coluna Funcionário, vamos adicionar uma Validação de dados. Selecione a coluna Funcionário, vá para guia Dados > Validação de dados e definimos como tipo Lista. Na Fonte, selecionamos a coluna Funcionário da planilha Cadastro de Funcionários.
Além disso, podemos definir a mensagem de erro que será exibida caso alguém tente adicionar o nome de um funcionário não cadastrado. Basta ir para a aba Alerta de erro da Validação de dados e configurar nossa mensagem.
Dessa forma, se tentarmos inserir um funcionário não cadastrado, receberemos a seguinte mensagem:
Na aula de hoje, você aprendeu como construir uma planilha de horas trabalhadas Excel que atualiza automaticamente as informações.
Passamos por todo o processo passo a passo para construir essa tabela que permite calcular as horas trabalhadas e as horas extras dos funcionários da sua empresa.
A partir dela, você pode personalizá-la conforme desejar, realizando cálculos de pagamentos ou adaptando a planilha à realidade do seu negócio e da sua empresa. Mesmo que você não a utilize no mercado de trabalho, é um ótimo exercício para praticar e estudar o Excel.
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Intermediário, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Python e virar uma referência na sua empresa? Inscreva-se agora mesmo no Python Impressionador