Voltar para: Excel avançado

Validação de CPF

Funcionalidades usadas nesta atividade:

• Função CONCAT.
• Função DIREITA.
• Função EXT.TEXTO.
• Função INT,
• Função NÚM.CARACT.
• Função SE.
• Função SUBSTITUIR.
• Função VALOR.

O CPF é um número de 11 dígitos em que os dois últimos são dígitos verificadores. Esses dígitos previnem erros de digitação, pois se isso acontecer os dígitos verificadores não batem e somos alertados que o número é inválido. A verificação do CPF dos brasileiros é baseada em um método chamado módulo 11 que pode ser entendido na página da Wikipedia:

https://pt.wikipedia.org/wiki/Dígito_verificador

Nesta atividade vamos criar uma planilha que verifica se um CPF é válido. O algoritmo poderá ser adotado em outras planilhas em que valores de CPF precisem ser testados. Lembre que esta verificação indica apenas que o CPF está dentro do padrão, o que não quer dizer que está ativo na Receita Federal ou que pertence à pessoa que o informou Depois de pronta, a planilha deve ficar como este exemplo:

Vamos por etapas começando pelo preparo do número antes de aplicar o método módulo 11.

Preparação do número a ser validado

1. A primeira providência é criar uma área para o usuário digitar o CPF e receber o resultado da validação. Digite um CPF válido na célula para termos um ponto de partida.
2. A célula onde faremos a remoção dos caracteres indesejados terá uma função SUBSTITUIR composta. Com isso, vamos limpar caracteres como espaços, pontos e hífens, caso o usuário os digite. O conteúdo da célula deve ficar como este:
   =SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(B3;” “;””);”-“;””);”.”;””)
3. Outra célula fará a conversão do texto limpo para número com a função VALOR.
   =VALOR(C9)
4. Em outra célula faremos a contagem de caracteres do número de CPF limpo.
   =NÚM.CARACT(C10), Essa informação é importante, pois se o número de caracteres for diferente de 11, o processo se encerra e o CPF é dado como inválido.

Módulo 11

Vamos dividir os passos do módulo 11 em duas partes: uma para calcular o primeiro dígito verificador e outra para o segundo.

Primeiro dígito verificador

1. Reserve uma célula para cada um dos primeiro nove dígitos do CPF.
2. Extraia os dígitos um a um do CPF e os inclua nas células reservadas. Use a função EXT.TEXTO que pode ser usada para obter um caractere específico dentro de uma cadeia de caracteres.
   =EXT.TEXTO($C$10;1;1)  Leia-se: extraia da célula C10 a partir da primeira posição um caractere.
3. Multiplique o primeiro dígito por dez, o segundo por nove, o terceiro por 8 e assim sucessivamente.
4. Faça a soma dos produtos.
   =SOMA(D15:L15)
5. Divida o resultado da soma por 11 e considere apenas a parte inteira usando a função INT.
   =INT(M15/11)
6. Encontre o resto da divisão.
   =M15-N15*11
7. Tire a diferença: 11 – resto = Dígito verificador 1. Se o resto resultar em 10 ou 11 o DV será 0.

Segundo dígito verificador

O método para obter o segundo dígito verificador é similar ao feito acima. A diferença é que DV 1 será acrescentado à sequência de produtos. O primeiro dígito será multiplicado por 11, o segundo dígito por 10, o terceiro por 9 e assim por diante.

Validação

Para decidir se o CPF é válido precisamos atender a três condições:

• O número de dígitos deve ser 11.
• O primeiro DV deve coincidir com o décimo dígito.
• O segundo DV deve coincidir com o décimo primeiro dígito.

Para simplificar o teste faça a concatenação dos dois dígitos verificadores:

=CONCAT(P15;Q19)

Na célula que expressa o resultado da validação inclua uma função SE composta que verifica primeiro se o número de dígitos é 11 e depois se os dois últimos dígitos do CPF coincidem com os DVs concatenados.

=SE(C11=11;SE(O22=O21;”VÁLIDO”;”INVÁLIDO”);”INVÁLIDO”)

Se quiser melhorar a apresentação do resultado faça uma formatação condicional na célula.

• Formate em verde se o conteúdo for “VÁLIDO”.
• Formate em vermelho se o conteúdo for “INVÁLIDO”.