Como gerar CPF e validar CPF no Excel usando funções no Excel sem a necessidade de usar VBA.
Funções para Gerar CPF e Validar CPF no Excel
O Excel não possui uma função própria para muitas coisas.
Por exemplo, não existe uma função própria para gerar CPF ou para validar CPF.
Antes era necessário fazer cálculos muito complexos com várias funções ou ainda usar VBA para criar funções que realizassem estas tarefas.
Mas com a vinda da função Lambda temos a possibilidade de criar funções próprias no Excel, inclusive com loops, usando a recursividade.
Neste artigo criamos duas funções, uma para Gerar e outra pra validar CPFs.
O objetivo da função Gerar CPF no Excel é criar uma lista aleatória e dinâmica de CPFs válidos.
Isso é muito interessante para desenvolvedores, para gerarem bases de dados para testes ou realização de testes unitários.
E também criamos uma função para Validar CPF no Excel.
Esta outra função já foi vista nestes artigo mais profundamente e também a respeito das funções que usamos para construir a função de geração automática.
No vídeo ao topo tem mais explicações sobre, e no passo-a-passo seguinte você verá como instalar na sua pasta de trabalho estas funções.
Como Instalar Função Gerar CPF
Para instalar uma função Lambda é necessário colocar ela no Gerenciador de Nomes.
A função de validação e a de geração de cpf precisam de cálculos recursivos, e para isso usamos funções auxiliares lambda para os dígitos.
Abaixo uma lista de todas as funções que precisam ser criadas:
Aqui o texto de cada uma das funções para poder copiar mais fácil:
| DESCRIÇÃO | LAMBDA | NOME |
|---|---|---|
| CÁLCULO DO DÍGITO | =LAMBDA(CPF;POS;SOMAR1;MULTIPLICADOR; EXT.TEXTO(DIREITA(“00000000000″&CPF;11);POS;1)*(MULTIPLICADOR+SOMAR1)) | DIGT1 |
| CÁLCULO DA SOMA | =LAMBDA(CPF;POS;SOMAR1;MULTIPLICADOR; SE(POS=1;DIGT1(CPF;POS;SOMAR1;MULTIPLICADOR); PRIMDIG2(CPF;POS-1;SOMAR1;MULTIPLICADOR+1)+DIGT1(CPF;POS;SOMAR1;MULTIPLICADOR))) | PRIMIDIG2 |
| VALIDAÇÃO CPF | =LAMBDA(CPF;LET(CALCULODIGITO1;MOD(PRIMDIG2(CPF;9;0;2);11); RESULTADO1;SE(CALCULODIGITO1>=2;11-CALCULODIGITO1;0); CALCULODIGITO2;MOD(PRIMDIG2(CPF;9;1;2)+(RESULTADO1*2);11); RESULTADO2;SE(CALCULODIGITO2>=2;11-CALCULODIGITO2;0); SE(DIREITA(CPF;2)=RESULTADO1&RESULTADO2;VERDADEIRO;FALSO) )) | FNVALIDARCPF |
| GERAÇÃO DE CPF | =LAMBDA(linhas;LET(ALEAT;MATRIZALEATÓRIA(linhas;;10000000000;99999999999;1); CALCULODIGITO1;MOD(PRIMDIG2(ALEAT;9;0;2);11); RESULTADO1;SE(CALCULODIGITO1>=2;11-CALCULODIGITO1;0); CALCULODIGITO2;MOD(PRIMDIG2(ALEAT;9;1;2)+(RESULTADO1*2);11); RESULTADO2;SE(CALCULODIGITO2>=2;11-CALCULODIGITO2;0); ESQUERDA(ALEAT;9)&RESULTADO1&RESULTADO2)) | FNGERARCPF |
Para cada item da tabela acima copie e clique em Fórmulas->Gerenciador de Nomes e Definir Nome.
Adicione conforme temos abaixo o nome e o código em refere-se a.
Se desejar, coloque no comentário o texto da descrição da tabela.
Repetindo o processo, temos então os campos definidos criados conforme ficou no Gerenciador de Nomes abaixo:
Testando as Funções de Gerar CPF e Validar no Excel
Para usar as funções é muito simples.
A sintaxe da função fnGerarCPF é a seguinte:
=fnGerarCPF(linhas)
Em linhas substitua pela quantidade de linhas que deseja gerar de CPF’s válidos.
No exemplo acima temos 100 cpf’s gerados automaticamente à partir da quantidade definida na célula ao lado de Gerar CPFs:
Com isso temos a lista dinâmica criada logo abaixo de Gerar CPF com a quantidade de linhas solicitadas, todos válidos.
E por fim temos a função de validar CPF que criamos, cuja sintaxe é:
=fnValidarCPF(CPF)
Nela passamos como parâmetro o número do CPF que é então validado e retorna VERDADEIRO se estiver correto e FALSO se estiver incorreto.
Veja a aplicação:
Como resultado temos uma lista de VERDADEIRO, no caso, pois todos os CPFs criados são corretos, mas se alterar um número do CPF ele já acusará que é FALSO, ou seja, CPF Inválido na fórmula do Excel.
Download Gerar CPF no Excel e Validar CPF no Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
