Se você tem uma planilha no Excel usada para acompanhar pagamentos e deseja verificar a quantidade de pagamentos atrasados, pendentes e efetuados, a função CONT.SES pode ser sua aliada nessa tarefa.
Nesse artigo, aprenderemos a contar pagamentos atrasados, pendentes e efetuados usando condições atreladas às datas de vencimento e de pagamento. Apesar de bem simples, a sintaxe da função CONT.SES, em alguns casos específicos, merece cuidado, por isso vale a pena dar uma olhadinha nos três casos com atenção.
Versão do Excel utilizada na aula: Microsoft Excel Professional Plus [versão 2019]
Versão do Excel utilizada neste tutorial: Microsoft Excel Professional Plus [versão 2019]
Contando pagamentos atrasados
A planilha a seguir exibe uma lista de valores com suas respectivas datas de vencimento e pagamento (no caso dos pagamentos efetuados).
Para que possamos calcular a quantidade de pagamentos em atraso, precisamos ter em mente as condições lógicas para esse cálculo. Veja bem:
1. A data de vencimento precisa ser anterior à data atual. Caso a data de vencimento seja a data de hoje ou qualquer outra posterior a hoje, isso indica que o pagamento ainda está pendente; não configura atraso.
2. A célula que contém a data de pagamento precisa estar vazia. Como a coluna D é usada para registrar a data de pagamento de um valor, precisamos considerar as células vazias como as de pagamentos ainda não efetuados.
Perceba que as DUAS condições precisam ser respeitadas para que possamos estabelecer o cálculo dos pagamentos em atraso, e não apenas a primeira OU a segunda.
Como nosso propósito é realizar uma contagem, e essa contagem é baseada em condições, o Excel nos fornece uma função perfeita pra esse cálculo: a CONT.SES, que realiza a contagem de células baseando-se em uma ou mais condições simultaneamente.
Antes de prosseguirmos, vale notar que esse cálculo será 100% automatizado, ou seja, a data atual será retornada através da função HOJE, e não digitada dia após dia. Portanto, como se trata de uma função que retorna a data atual de forma automática, os resultados serão variados de acordo com as datas que você inserir em sua própria planilha e também de acordo com a data em que você estiver montado seu arquivo. Atente-se para o fato de que, em nosso exemplo, vamos considerar a data de 27/08/2021 como a data atual.
Vejamos como fica nossa fórmula.
=CONT.SES($C$2:$C$13;”<“&HOJE();$D$2:$D$13;””)
A função CONT.SES precisa de um intervalo de análise e em seguida a condição a ser analisada nesse intervalo, estrutura que se repete quantas vezes for necessária, para a quantidade de critérios que for necessária. Em nosso caso, temos dois critérios a serem levados em consideração. Vamos desmembrar essa fórmula para entendermos seu funcionamento.
$C$2:$C$13 refere-se ao primeiro intervalo de dados, que corresponde às datas de vencimento, portanto, o próximo argumento da função (critério) está ligado a esse primeiro intervalo de análise. Observe que o travamento (com $) não é obrigatório, mas é recomendado, principalmente se sua intenção for usar autopreenchimento, deslocar células, linhas e colunas de lugar ou então copiar a fórmula para demais células.
“<“&HOJE() é justamente o critério a ser considerado no primeiro intervalo. Estamos procurando datas anteriores à data de hoje (27/08/2021, em nosso exemplo). No Excel, usamos o sinal de menor (<) para se referir a datas anteriores, já que elas são armazenadas como números pelo programa. Por conta de uma particularidade da função CONT.SES, devemos notar funções e referências sem aspas dentro dos critérios, mas textos e operadores precisam estar entre aspas, por isso o sinal de menor encontra-se entre aspas duplas. Em seguida, como usaremos uma função (em nosso exemplo, a função HOJE), e funções não podem estar entre aspas, é necessário concatenar o texto anterior (nosso operador matemático) com a função a ser inserida. O caractere de concatenação no Excel é o & (E comercial). Em seguida, entra então a função HOJE, cuja sintaxe exige a abertura e fechamento de parênteses, mesmo sem qualquer argumento.
$D$2:$D$13 é o nosso segundo intervalo de análise, o que se refere, aqui, às datas de pagamento.
Dentro desse segundo intervalo de critérios, precisamos estabelecer uma condição, que é dada pelo uso de aspas duplas (“”), o que representa, no Excel, a notação para vazio.
Em suma, a função contará, na coluna C, as células cujas datas sejam menores que a data atual, e também contará, na coluna D, as células vazias, ou seja, que ainda não possuem data de pagamento registrada. Como se trata de uma contagem cumulativa, a função CONT.SES só levará em conta as ocorrências que obedecerem aos dois critérios simultaneamente.
Note que, de fato, temos 4 pagamentos em atraso, tomando 27/08/2021 como a data de hoje.
Contando pagamentos pendentes
Agora que você já conheceu a estrutura de cálculo que usamos dentro da CONT.SES, fica muito mais fácil montar a sintaxe de nossa próxima fórmula para pagamentos pendentes, já que mudaremos apenas a lógica de cálculo.
Pagamentos pendentes, no nosso caso, são aqueles cuja data de pagamento é igual ou posterior à data atual e que ainda não foram pagos. Aqui, portanto, temos duas condições novamente, já que não podemos considerar qualquer data posterior à data de hoje como referente a um pagamento pendente; é necessário que ele ainda não tenha sido pago.
Vejamos então como fica nossa fórmula.
=CONT.SES($C$2:$C$13;”>=”&HOJE();$D$2:$D$13;””)
A única condição alterada aqui refere-se ao critério para a contagem baseada no intervalo de datas de vencimento. As datas pendentes são aquelas maiores ou iguais (>=) à data de hoje. Como a condição também é a de que o pagamento ainda não foi efetuado, o critério para o intervalo de datas de pagamento é que a célula esteja vazia.
Temos dois pagamentos pendentes em nosso exemplo, como você pode observar. São registros com datas iguais ou posteriores à data de hoje (27/08/2021) e para os quais não foram registradas ainda datas de pagamento.
Contando pagamentos efetuados
Calcular os pagamentos efetuados acaba se tornando ainda mais fácil. Tomando como consideração que são dados como efetuados aqueles pagamentos cuja célula correspondente na coluna D esteja preenchida (ou seja, contenha uma data), é possível realizar essa contagem com apenas uma condição.
=CONT.SES($D$2:$D$13;”<>”&””)
Aqui estamos analisando apenas o intervalo que armazena as datas de pagamento, visto que nosso intuito é apenas contar pagamentos efetuados de uma maneira generalizada, sem segundos critérios (como, por exemplo, os efetuados dentro do prazo e os efetuados depois do vencimento). Nossa única condição é que a CONT.SES analise as células cujo conteúdo é diferente (<>) de vazio.
Basicamente, se a célula da coluna D está preenchida, é porque o pagamento foi efetuado.
Aqui, portanto, temos uma contagem simples, que analisa apenas a quantidade de células preenchidas na coluna D.
- Você pode usar colunas inteiras como intervalo de critérios para a função CONT.SES, no entanto, lembre-se de que isso incluirá também as células do cabeçalho e outras células que talvez não façam parte do intervalo de dados, mas que estejam nessa mesma coluna. No caso do cálculo dos pagamentos efetuados, por exemplo, isso pode gerar erros de cálculo, já que essas células não estão vazias.
- Você pode incrementar sua planilha com validação de dados, para não permitir que uma data de pagamento seja digitada quando não houver antes uma data de vencimento estabelecida.
- No lugar de intervalos como critérios da função CONT.SES, você pode usar nomes definidos, para que você consiga alterar a abrangência desses intervalos editando os nomes definidos, sem necessidade de edição das fórmulas.
- A função HOJE retorna a data atual baseada na data de seu computador, portanto, caso seu computador apresente uma data errada, seu cálculo não funcionará corretamente.
- Como a função HOJE é uma função atualizada automaticamente assim que sua planilha é aberta, é natural que o Excel peça para você salvar alterações no seu arquivo ao fechá-lo, mesmo que você não tenha feito alteração alguma.
- Se você deseja contar pagamentos utilizando uma coluna auxiliar para os status, pode conferir este outro método.
- Se você deseja somar os valores dos pagamentos atrasados, pendentes e efetuados, pode conferir este método ou este.
