Como contar apenas campos com números com CONT.SES

Uma situação na qual me encontrei recentemente é a seguinte:

CONT.SES e comissões

Uma tarefa que deveria ser simples provou-se mais complexa do que eu esperava: como definir se um pagamento é o primeiro, mas sem perder o controle dos boletos bancários que você já emitiu e que fez a baixa no banco?

Geralmente usamos a função CONT.SE do Excel para contar quantas ocorrências de um número de identificação único, no caso a ID_Cliente na coluna A, aparece. Sendo o número 1, definimos que o pagamento é o “Primeiro” e tudo se resolve. Contudo, quando você quer que apenas seja contado se a Liquidação for um número (como uma data), e não um texto, já não é tão simples.

A fórmula que gera a nossa coluna E é a seguinte, na célula E2:

=SE(ÉTEXTO($C2);"Não Foi Feito";SE(CONT.SES($A$2:$A2;$A2;$C$2:$C2;"<>?*")=1;"Primeiro";"Recorrente"))

Vou tentar explicar como chegamos nesse ponto.

$

Uma funcionalidade básica de Excel: toda referência de coluna ou linha precedida por um $ não muda se você copiar a fórmula para outra célula. Por exemplo: “$A$2” é a mesma coisa que “A2”, mas se você copiar esse valor para a célula A3, ele vai continuar “$A$2”.

Você vai notar intervalos como “$A$2:$A2”. Por quê? É porque queremos que o número da célula no fim do intervalo mude de acordo com a linha onde está. Quando você mexer essa equação para a linha 3, ela automaticamente mudará para “$A$2:$A3”. Se mover para a linha 15, será “$A$2:$A15”.

CONT.SES

O CONT.SES tem o propósito de fazer uma contagem de todas as ocorrências verdadeiras dos critérios que você definir até a linha que você determinar. Para que um valor saia “1”, você deve parar de calcular na primeira linha onde os critérios são verdadeiros, por isso limitamos a busca com intervalos como A2:A2, depois A2:A3 e assim por diante.

Neste caso, estamos pedindo ao Excel que conte um valor específico da coluna A, desde que o valor na coluna C não seja texto na mesma linha:

CONT.SES($A$2:$A2;$A2;$C$2:$C2;"<>?*")

Portanto, na célula E2, pedimos que num intervalo entre A2 e A2, se o resultado for igual a A2, o Excel retorne um valor verdadeiro.

Contudo, também pedimos ao Excel que verifique num intervalo entre C2 e C2 se o resultado é diferente de texto, ou seja, se é um número ou uma célula em branco. Caso seja diferente de texto, pedimos que ele retorne um valor verdadeiro.

“Diferente” no Excel é expresso com “<>”. A ideia de “qualquer caractere de texto” é expressa com os caracteres coringa “?*”.

Repetimos essa fórmula até a célula E6, sendo os cinco resultados: 0, 1, 1, 2, 2.

SE interno

Existindo a fórmula de CONT.SES anterior para calcular o número de ocorrências de um ID_Cliente na coluna A até a linha em que ele se encontra, a função SE permite que utilizemos o número obtido para retornar certos valores se um teste lógico retornar verdadeiro ou falso. Neste caso, nosso teste lógico é: nossa fórmula anterior, do CONT.SES, é igual a “1”?

Caso verdadeiro, o Excel escreverá a palavra “Primeiro” na linha correta da coluna E. Caso seja falso, ele escreverá “Recorrente”.

Contudo, quando há texto na coluna C, o nosso CONT.SES retorna “0”, o que é interpretado por esse SE interno como “Recorrente”. Nós queremos evitar isso, para que a coluna F não calcule qualquer tipo de comissão.

SE externo

O teste lógico utiliza a função ÉTEXTO do Excel, verificando a célula adequada na coluna C. Se for texto, o teste lógico retorna verdadeiro, e o Excel escreverá “Não Foi Feito”, conforme o instruímos. Caso a célula da coluna C não seja texto, o Excel fará o SE interno que explicamos anteriormente. O Excel sempre cumprirá sua primeira instrução válida, ignorando todas as outras subsequentes, por isso é importante que esse teste lógico seja o primeiro.

Contudo, é difícil verificar a necessidade dele sem escrever todo o resto, por isso estou explicando-o por último.

Comissão

Novamente aqui há dois SEs: um externo, verificando se há necessidade de calcular a comissão, e um interno, que calcula a comissão a partir dos critérios estipulados. A fórmula para o cálculo da comissão na célula F2 é a seguinte:

=SE($E2="Não Foi Feito";0;SE($E2="Primeiro";$D2*0,2;$D2*0,1))

Se E2 for igual a “Não Foi Feito”, então o Excel deverá retornar o valor “0”, negando qualquer comissão.

Contudo, se o valor for “Primeiro”, ele deve pegar o valor do boleto em D2 e multiplicá-lo por 0,2 (20%), obtendo-se assim o valor de comissão de um primeiro pagamento. Mas se for qualquer outro caso, ou seja, o “Recorrente”, então o valor deve ser multiplicado por 0,1 (10%), finalizando nossa tarefa.

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s