Se você tem uma planilha no Excel usada para acompanhar pagamentos e deseja verificar a soma dos valores atrasados, pendentes e efetuados, a função SOMASES pode ser sua aliada nessa tarefa.
Nesse artigo, aprenderemos a somar pagamentos atrasados, pendentes e efetuados usando condições atreladas às datas de vencimento e de pagamento. Apesar de bastante simples, a sintaxe da função SOMASES, em casos específicos, merece cuidado, por isso vale a pena dar uma olhadinha nos três casos a seguir 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]
Somando 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 somar o valor dos 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 somar os valores dos pagamentos em atraso, e não apenas a primeira OU a segunda.
Essa soma de valores não é uma soma comum, visto que ela depende de condições específicas para que determinados valores sejam ou não somados. Diante de uma situação como essa, que requer uma soma condicional, podemos usar, por exemplo, a função SOMASES, que realiza a soma dos valores contidos nas 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 31/08/2021 como a data atual.
Vejamos como fica nossa fórmula.
=SOMASES($B$2:$B$13;$C$2:$C$13;”<“&HOJE();$D$2:$D$13;””)
A função SOMASES precisa, antes de tudo, de um intervalo de análise, ou seja, o intervalo onde se encontram os valores a serem analisados. Em seguida, estabelecemos 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 (31/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 SOMASES, 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 somará, entre os valores presentes na coluna B, aqueles que corresponderem, na mesma linha, aos critérios estabelecidos para a coluna C e D, ou seja, datas menores que a data atual na coluna C e células vazias na coluna D, que indicam a falta de registro daquele pagamento específico. Como se trata de uma soma cumulativa, a função SOMASES só levará em conta as ocorrências que obedecerem aos dois critérios simultaneamente.
Note que, de fato, temos o valor de R$ 450,00 em pagamentos em atraso, tomando 31/08/2021 como a data de hoje.
Somando pagamentos pendentes
Agora que você já conheceu a estrutura de cálculo que usamos dentro da SOMASES, 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.
=SOMASES($B$2:$B$13;$C$2:$C$13;”>=”&HOJE();$D$2:$D$13;””)
A única condição alterada aqui refere-se ao critério para a soma 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 quatro pagamentos pendentes em nosso exemplo, como você pode observar. São registros com datas iguais ou posteriores à data de hoje (31/08/2021) e para os quais não foram registradas ainda datas de pagamento. A soma desses valores resulta R$ 680,00.
Somando pagamentos efetuados
Somar os valores dos 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.
=SOMASES($B$2:$B$13;$D$2:$D$13;”<>”&””)
Aqui estamos analisando apenas o intervalo que armazena as datas de pagamento, visto que nosso intuito é apenas somar os valores dos 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 SOMASES 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. Somando suas correspondentes na coluna B, obteremos o total de R$ 840,00.
- Você pode usar colunas inteiras como intervalo de critérios para a função SOMASES, 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 SOMASES, 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 a quantidade de pagamentos atrasados, pendentes e efetuados, confira este artigo ou este.
