Esse artigo foi publicado originalmente no meu antigo blog do Guru do Excel em 14/Dez/2013. Este é um trabalho que estou realizando de recuperação de publicações antigas minhas. O artigo foi atualizado para refletir técnicas atuais do Excel.

Laennder Alves

Índice

• Introdução
• O que é o CPF?
• Como verificar se um CPF é válido?
• Calculando o Primeiro Dígito Verificador
• Calculando o Segundo Dígito Verificador
• Como fazer no Excel
• Preparando a fórmula
• Criando mega fórmulas

Introdução

Neste artigo você vai aprender como calcular um CPF e verificar se ele é válido ou não. Mas por trás desse tutorial, o objetivo é mostrar que podemos trazer questões do dia a dia, e tentar criar uma solução em Excel. Muito mais do que apenas criar essa validação, é mostrar que podemos utilizar muitos dos recursos do Excel, de forma conjunta para chegar a solução desejada.

Uma solução seria criar uma função personalizada, desenvolvida em VBA. Porém, iremos utilizar os recursos sem programação alguma.

No exemplo, que você pode fazer download no final do artigo, você poderá reparar que foram utilizados os seguintes recursos:

• Funções: LET, SEQUÊNCIA, DIREITA, EXT.TEXTO, SOMARPRODUTO, SE, MOD
• Fórmulas Matriciais
• Formatação Personalizada de Números
• Referências Relativas e Absolutas

Então vamos ver como se calcula um CPF.

O que é o CPF?

O CPF ou Cadastro de Pessoa Física é um documento brasileiro emitido pela Secretaria da Receita Federal do Ministério da Fazenda. Seu número é composto por 11 dígitos, sendo os dois últimos os dígitos verificadores, que atestam se o número do CPF é válido.

Como verificar se um CPF é válido?

Para exemplificar o cálculo, vamos imaginar o número de um CPF hipotético:

• 123.456.789-10

São os dois últimos dígitos que atestam a validade do CPF, e estes são calculados baseando-se nos 9 primeiros dígitos.

Calculando o Primeiro Dígito Verificador

O primeiro dígito verificador do CPF é calculado baseando-se no seguinte algoritmo.

1) Distribua os 9 primeiros dígitos do CPF na primeira linha de uma tabela, e na linha abaixo distribua os pesos 10, 9, 8, 7, 6, 5, 4, 3, 2 conforme representação abaixo:

2) Multiplique os valores de cada coluna:

3) Calcule a somatória dos resultados (10+18+…+24+18) = 210

4) O resultado obtido (210) será divido por 11. Considere como quociente apenas o valor inteiro obtido na divisão, o resto da divisão será responsável pelo cálculo do primeiro dígito verificador.

O resto da divisão é 1. Para calcular o dígito verificador, você deve subtrair o resto encontrado de onze.

11 – 1 = 10

Se o resultado da subtração for maior que 9, o dígito verificador é ZERO. Caso contrário, o dígito verificador é o resultado dessa subtração. Neste caso, o primeiro dígito verificador é ZERO.

Já temos portanto parte do CPF válido, confira: 123.456.789-0X.

Calculando o Segundo Dígito Verificador

1) Para calcular o segundo dígito verificador, fazemos o cálculo de forma análoga ao primeiro dígito, acrescentando ao CPF o dígito encontrado no passo anterior. Na segunda linha, os pesos são distribuídos começando por 11.

2) Faça a somatória da multiplicação das colunas: (11+20+…+27+0) = 255

3) Encontre o resto da divisão da somatória por 11.

O resto da divisão é 2. Para calcular o dígito verificador, você deve subtrair o resto encontrado de onze.

11 – 2 = 9

Como agora o resultado da subtração não é maior que 9, o resultado é o próprio dígito verificador.

Agora já sabemos quais são os dois dígitos verificadores válidos, 0 e 9, podemos afirmar que o CPF hipotético usado no exemplo não é válido.

123.456.789-10 (CPF inválido)

123.456.789-09 (CPF válido)

Bora ver como fazer no Excel

Essa parte do tutorial foi atualizado para utilizar recursos novos que foram acrescentando após a publicação da primeira versão do artigo, e que facilitam implementar a solução.

Primeiro vamos criar uma formatação personalizada de número para exibir o CPF com a formatação com números e pontos. Isso vai garantir que visualmente, na célula onde for digital o CPF consigamos identificar todos os 11 caracteres (inclusive dos CPFs iniciados em zero).

Como complemento você pode ler o artigo sobre Formatação Personalizada de Números no Excel.

Selecione a célula destinada ao CPF e pressione CTRL + 1. Na guia número selecione a opção Personalizada e digite “000\.000\.000-00”.

Para testar digite o CPF 12345678910

Agora vamos começar a preparar a fórmula

Para facilitar o entendimento, vamos construindo a fórmula por partes.

Vamos separar em uma matriz dinâmica os 11 caracteres do CPF. Para garantir que os CPFs iniciados em zero não perca os caracteres iniciais, vamos precisar usar a função TEXTO para formatar o CPF.

Na sequência vamos utilizar a função EXT.TEXTO para extrair cada um dos 9 primeiros caracteres individualmente. A função SEQUÊNCIA é responsável por criar a lista de 1 a 9 que vai indicar a posição do caractere a ser extraído.

=EXT.TEXTO(TEXTO(B3;"00000000000");SEQUÊNCIA(9);1)

A fórmula vai derramar em uma matriz dinâmica cada um dos 9 caracteres conforme a imagem a seguir.

Em seguida vamos precisar multiplicar essa matriz por uma outra matriz de numerada de 10 a 2, que vai ser gerada com a função SEQUÊNCIA. Vamos subtrair do número 11 uma sequência de 1 a 9.

= 11-SEQUÊNCIA(9)

E por fim vamos multiplicar as duas matrizes e somar os resultados utilizando a função SOMARPRODUTO.

=SOMARPRODUTO(EXT.TEXTO(TEXTO(B3;"00000000000");SEQUÊNCIA(9);1);11-SEQUÊNCIA(9))

A fórmula completa deveria ficar igual ao exemplo acima, entretanto, se você fez percebeu que o resultado foi zero, certo? Isso acontece porque a primeira matriz é reconhecida como texto, já que foi utilizada a função EXT.TEXTO para separar os algarismos. O truque aqui é transformar tudo em número. Para isso basta você aplicar o duas negações (–) antes da fórmula, ficando assim:

=SOMARPRODUTO(--EXT.TEXTO(TEXTO(B3;"00000000000");SEQUÊNCIA(9);1);11-SEQUÊNCIA(9))

Esse truque aí já valeu a leitura né?!

Criando mega fórmulas

A fórmula começa a ganhar corpo, e vai ficar extensa. Nesses casos o ideal é separar cada uma das etapas de cálculo e dar nomes a eles, e na sequência reutilizar esses nomes em etapas seguintes.

Então para melhorar a legibilidade, vou utilizar a função LET separando cada um dos cálculos que fizemos. Eu já gravei um vídeo onde eu ensino a criar MEGA FÓRMULAS no Excel e aplico a função LET. Você pode dar uma conferida clicando aqui.

A sintaxe da função LET é basicamente a seguinte:

LET(nome1; nome_do_valor1 ; calculo_ou_nome2; [nome_valor2])

Basicamente vamos criar um nome, e definir o cálculo desse nome. O último parâmetro deve ser sempre o resultado final da fórmula.

Então aplicando a estrutura acima par aos primeiros cálculos, teremos a seguinte fórmula.

=LET(
   novedigitos; --EXT.TEXTO(TEXTO(B3;"00000000000");SEQUÊNCIA(9);1);
   mult_div1; SOMARPRODUTO(novedigitos; 11- SEQUÊNCIA(9));
   mult_div1
)

Dica: utilize ALT + Enter para quebrar a fórmula em várias linhas.

Para o CPF de exemplo que estamos calculando o resultado deverá ser 210.

Na fórmula acima criamos o nome novedigitos e atribuímos a primeira fórmula, depois o nome mult_div1 e atribuímos a segunda fórmula que havíamos criado.

Dando continuidade, precisamos encontrar o resto da divisão por 11 do valor encontrado. Basta aplicar a função MOD a fórmula já desenvolvida, onde o segundo argumento será 11.

O resto da divisão deve ser o primeiro dígito verificador. Lembre-se de verificar se o resto da divisão é maior que 9, pois caso verdadeiro o dígito verificador é zero.

Vamos criar então mais dois nomes dentro da função LET: mod_div1 que vai calcular o resto da divisão e subtrair de 11. e o _div1 que vai fazer a verificação condicional utilizando a função SE.

A estruturação dos cálculos com a função LET deve ficar assim:

=LET(
   novedigitos; --EXT.TEXTO(TEXTO(B3;"00000000000");SEQUÊNCIA(9);1);
   mult_div1; SOMARPRODUTO(novedigitos; 11- SEQUÊNCIA(9));
   mod_div1; 11 - MOD(mult_div1; 11);
   _div1; SE(mod_div1 > 9; 0; mod_div1);
   _div1
)

A fórmula acima retorna o primeiro dígito verificador, que corresponde ao décimo caractere do CPF.

Para dar continuidade ao cálculo, vamos agora acrescentar o dígito encontrado aos nove primeiros dígitos, e refazer o processo conforme descrito na primeira parte desse artigo.

Vamos utilizar a função CONCAT para juntar os noveprimeiros dígitos com o _div1. E depois explodir novamente em uma matriz de 10 dígitos.

--EXT.TEXTO(CONCAT(novedigitos;_div1);SEQUÊNCIA(10);1)

Replicando os passos que fizemos para o primeiro dígito verificador, o resultado da fórmula completa para retornar o segundo dígito ficaria assim:

=LET(
novedigitos; --EXT.TEXTO(TEXTO(B3;"00000000000");SEQUÊNCIA(9);1);
mult_div1; SOMARPRODUTO(novedigitos; 11- SEQUÊNCIA(9));
mod_div1; 11 - MOD(mult_div1; 11);
_div1; SE(mod_div1 > 9; 0; mod_div1);
dezdigitos; --EXT.TEXTO(CONCAT(novedigitos;_div1);SEQUÊNCIA(10);1);
mult_div2;SOMARPRODUTO(dezdigitos; 12-SEQUÊNCIA(10));
mod_div2; 11 - MOD(mult_div2; 11);
_div2; SE(mod_div2 > 9; 0; mod_div2);
_div2
)

Agora nós já temos dois nomes _div1 e _div2 que representa os dois dígitos válidos para o CPF informado. Basta compará-los com dois últimos caracteres do CPF informado.

Para extrair os dois últimos caracteres do CPF utilize a função DIREITA.

Uma dica extra é criar um nome no início da função LET para armazenar a célula com o CPF que está sendo verificado, que no exemplo é a célula B3.

A fórmula final ficaria assim:

=LET(
   cpf; B3;
   novedigitos; --EXT.TEXTO(TEXTO(cpf;"00000000000");SEQUÊNCIA(9);1);
   mult_div1; SOMARPRODUTO(novedigitos; 11- SEQUÊNCIA(9));
   mod_div1; 11 - MOD(mult_div1; 11);
   _div1; SE(mod_div1 > 9; 0; mod_div1);
   dezdigitos; --EXT.TEXTO(CONCAT(novedigitos;_div1);SEQUÊNCIA(10);1);
   mult_div2;SOMARPRODUTO(dezdigitos; 12-SEQUÊNCIA(10));
   mod_div2; 11 - MOD(mult_div2; 11);
   _div2; SE(mod_div2 > 9; 0; mod_div2);
   div_informados; DIREITA(cpf;2);
   resultado; div_informados = CONCAT(_div1;_div2);
   resultado
)

UFA! Que mega fórmula.

Arquivo de Exemplo

Clique aqui para fazer o download do arquivo de exemplo.