Se você tem uma planilha no Excel usada para acompanhar pagamentos e deseja verificar a quantidade de pagamentos atrasados, pendentes e efetuados, você pode criar uma coluna que calcula os status de pagamentos de acordo com as datas e depois contá-los usando a função SES. Com essas informações calculadas, podemos usar a função CONT.SES para calcular quantas vezes esses status aparecem em nossa lista de dados.
Nesse artigo, aprenderemos a usar esse método para realizar a contagem dos status de nossos pagamento, passando por observações importantes e detalhes que não podem passar despercebidos no momento dos cálculos.
Versão do Excel utilizada na aula: Microsoft Excel 365 [versão 2109]
Versão do Excel utilizada neste tutorial: Microsoft Excel 365 [versão 2109]
Definindo os status dos pagamentos
Em um artigo anterior, havíamos visto como contar pagamentos usando as condições de cálculo diretamente na fórmula final, sem que houvesse uma coluna auxiliar para nos mostrar o status de cada pagamento.
Nesse artigo, vamos fazer um procedimento um pouco diferente, desmembrado: iremos usar uma coluna para definir automaticamente a situação atual de cada pagamento e, com base nessas informações, contaremos quantas vezes cada um desses status se repete nessa coluna, assim como mostrado na planilha a seguir.
Para que possamos elaborar uma planilha que realize esses cálculos automaticamente de acordo com a data atual, é necessário que usemos a função HOJE, que retorna a data atual de forma automática toda vez que a planilha é aberta e/ou atualizada. Assim, podemos sempre comparar nossos pagamentos de acordo com o dia de hoje sem que precisemos inserir esse dia em uma célula específica ou mesmo manualmente na fórmula.
Como essa função é sempre atualizada de acordo com a data atual, é importante que você considere o dia 23/11/2021 como a data atual para o nosso caso, que foi o dia em que este exemplo foi montado.
O primeiro passo é entender os princípios básicos desse cálculo: um pagamento atrasado é aquele cuja data de vencimento é anterior à data de hoje e para o qual não há data de pagamento registrada; um pagamento pendente é aquele cuja data de vencimento é hoje ou algum dia posterior a hoje e para o qual também não há data de pagamento registrada; já um pagamento efetuado é aquele que possui uma data de pagamento registrada.
Note que, nos dois primeiros casos de teste, o Excel precisa verificar se DUAS condições são aceitas simultaneamente para que ele exiba um status. Por exemplo, não basta que não haja registro de pagamento para um pagamento atrasado; é necessário também que sua data de vencimento seja anterior à data atual. Nesse caso, quando duas condições precisam ser verdadeiras ao mesmo tempo, podemos fazer uso da função E, que tem o propósito de retornar o valor lógico verdadeiro se todas as condições estabelecidas dentro dela forem verdadeiras.
No entanto, a função E é apenas uma parte de nosso cálculo. No nosso exemplo, ela ficará dentro da função SES, responsável por realizar diversos testes lógicos, o que nos dará a definição dos status.
Vamos esmiuçar esse cálculo para um melhor entendimento.
Antes de tudo, certifique-se de que a célula E2 esteja selecionada, já que ela receberá o status do primeiro registro da planilha.
=SES(E(C2<HOJE();D2=””);”ATRASADO”;E(C2>=HOJE();D2=””);”PENDENTE”;D2<>””;”PAGO”)
A função SES analisa três condições, destacadas com cores diferentes na fórmula acima. Basicamente, ela analisa um teste 1 e retorna um resultado 1 se este teste for verdadeiro; em seguida, analisa um teste 2 e retorna um resultado 2 se este teste for verdadeiro; e assim por diante.
Esses testes e resultados é que vão definir os status de nossos registros. No primeiro caso, estamos transcrevendo em nossa fórmula a condição para o pagamento atrasado: C2 (a data de vencimento) precisa ser menor que a data de hoje, ao mesmo tempo que D2 (onde se registra a data de pagamento) precisa estar vazia. Como estamos usando a função E, isso significa que as duas condições precisam ser verdadeiras para que a função retorne verdadeiro e, assim, o resultado seja exibido. No nosso exemplo, esse resultado é o texto ATRASADO, escrito entre aspas duplas por se tratar de um texto, não de uma referência.
Em seguida, o segundo teste analisa se a data de pagamento é maior ou igual à data atual e, simultaneamente, se a célula que contém a data de pagamento está vazia. Ambas as condições sendo verdadeiras, a função E retorna o valor verdadeiro, o que satisfaz esse argumento da função SES e, portanto, o Excel exibe o texto PENDENTE como resultado para esse segundo teste.
Por fim, vamos supor que nossa condição para um pagamento efetuado seja apenas o preenchimento da célula destinada à data de pagamento, independentemente se este foi efetuado dentro ou fora do prazo. Sendo assim, nossa única condição é a de que esta célula seja diferente (<>) de vazio. Por analisarmos apenas uma única condição, não há necessidade de se usar a função E.
O próximo passo é usar o autopreenchimento a partir desta fórmula para que o Excel calcule as demais células de nosso intervalo.
Contando os status dos pagamentos
O processo lógico agora é bastante fácil: com todos os status calculados para cada um dos pagamentos, basta que contemos quantas vezes cada um deles aparece em nosso intervalo de dados. Podemos fazer uso de funções de contagem, como a CONT.SE ou a CONT.SES. Em nosso exemplo, vamos usar a CONT.SES.
Na célula que receberá a quantidade de pagamentos atrasados, bastar inserir a seguinte fórmula:
=CONT.SES(E2:E13;”ATRASADO”)
E2:E13 refere-se ao intervalo onde estão os dados a serem analisados pela função (nossos status), enquanto “ATRASADO” denomina a expressão a ser analisada e contada dentro desse intervalo. Como se trata de um texto, essa expressão precisa estar entre aspas duplas.
Temos 3 pagamentos atrasados, já que a palavra ATRASADO aparece 3 vezes dentro do intervalo de dados analisado.
O procedimento é idêntico para os outros dois cálculos, bastando que alteremos o termo a ser pesquisado dentro de nosso intervalo:
=CONT.SES(E2:E13;”PENDENTE”)
=CONT.SES(E2:E13;”PAGO”)
Pronto! Agora temos uma planilha que nos mostra os status de cada um dos pagamentos e também a soma desses grupos.
- Em vez de um intervalo específico de dados a ser analisado para a contagem dos pagamentos, você pode usar uma coluna inteira (por exemplo, A:A indica todas as células da coluna A). Assim, mesmo que você alimente sua planilha com mais dados, eles já serão incluídos no cálculo, sem necessidade de atualização das fórmulas. Mas 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.
- A função HOJE busca a data atual de acordo com a data de seu computador. Se seu computador estiver com a data desconfigurada, a função levará em conta essa data para realizar o cálculo, o que trará resultados equivocados.
- 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.
- 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.
