▷ Cálculo dos Digitos Verificadores para CPF e CNPJ no MS Excel

Olá seja bem vindo ao meu mais novo artigo

Neste artigo propõem resolver um desafio comentado na live do Instituto X, que segue abaixo

O desafio é digitar os 9 primeiros números do CPF (onde a mesma não pode estar formatada como texto) em uma célula e o MS Excel deve realizar o cálculo do digito verificador, deixando no formato de texto, com toda a acetuação, conforme o exemplo abaixo, para caso o CNPJ, deverá digitar os 12 primeiros dígitos, sendo que não pode ser ativado o CSE (Contronl+Shift+Enter), nem utilizar as funções novas do Excel 365/2019, para caso seja necessário abrir em uma versão antiga (Excel 2010) que funcione a resposta.

Segue as fórmulas do desafio

Desafio CPF | Célula D2
=TEXTO(TEXTO(C2;"000000000")&SE(11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C2;"000000000");COL($A$1:$I$1);1))*(11-COL($A$1:$I$1));0));11)>9;0;11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C2;"000000000");COL($A$1:$I$1);1))*(11-COL($A$1:$I$1));0));11))&SE(11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C2&SE(11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C2;"000000000");COL($A$1:$I$1);1))*(11-COL($A$1:$I$1));0));11)>9;0;11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C2;"000000000");COL($A$1:$I$1);1))*(11-COL($A$1:$I$1));0));11));"0000000000");COL($A$1:$J$1);1))*(12-COL($A$1:$J$1));0));11)>9;0;11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C2&SE(11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C2;"000000000");COL($A$1:$I$1);1))*(11-COL($A$1:$I$1));0));11)>9;0;11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C2;"000000000");COL($A$1:$I$1);1))*(11-COL($A$1:$I$1));0));11));"0000000000");COL($A$1:$J$1);1))*(12-COL($A$1:$J$1));0));11));"000\.000\.000-00")

ou em inglês

=TEXT(TEXT(C2;"000000000")&IF(11-MOD(SUM(INDEX((MID(TEXT(C2;"000000000");COLUMN($A$1:$I$1);1))*(11-COLUMN($A$1:$I$1));0));11)>9;0;11-MOD(SUM(INDEX((MID(TEXT(C2;"000000000");COLUMN($A$1:$I$1);1))*(11-COLUMN($A$1:$I$1));0));11))&IF(11-MOD(SUM(INDEX((MID(TEXT(C2&IF(11-MOD(SUM(INDEX((MID(TEXT(C2;"000000000");COLUMN($A$1:$I$1);1))*(11-COLUMN($A$1:$I$1));0));11)>9;0;11-MOD(SUM(INDEX((MID(TEXT(C2;"000000000");COLUMN($A$1:$I$1);1))*(11-COLUMN($A$1:$I$1));0));11));"0000000000");COLUMN($A$1:$J$1);1))*(12-COLUMN($A$1:$J$1));0));11)>9;0;11-MOD(SUM(INDEX((MID(TEXT(C2&IF(11-MOD(SUM(INDEX((MID(TEXT(C2;"000000000");COLUMN($A$1:$I$1);1))*(11-COLUMN($A$1:$I$1));0));11)>9;0;11-MOD(SUM(INDEX((MID(TEXT(C2;"000000000");COLUMN($A$1:$I$1);1))*(11-COLUMN($A$1:$I$1));0));11));"0000000000");COLUMN($A$1:$J$1);1))*(12-COLUMN($A$1:$J$1));0));11));"000\.000\.000-00")
Desafio CNPJ | Célula D4
=TEXTO(TEXTO(C4;"000000000000")&SE(11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C4;"000000000000");COL(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11)>9;0;11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C4;"000000000000");COL(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11))&SE(11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C4&SE(11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C4;"000000000000");COL(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11)>9;0;11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C4;"000000000000");COL(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11));"0000000000000");COL(A1:M1);1))*(({6\5\4\3\2\9\8\7\6\5\4\3\2}));0));11)>9;0;11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C4&SE(11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C4;"000000000000");COL(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11)>9;0;11-MOD(SOMA(ÍNDICE((EXT.TEXTO(TEXTO(C4;"000000000000");COL(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11));"0000000000000");COL(A1:M1);1))*(({6\5\4\3\2\9\8\7\6\5\4\3\2}));0));11));"00\.000\.000\/0000-00")

ou em inglês

=TEXT(TEXT(C4;"000000000000")&IF(11-MOD(SUM(INDEX((MID(TEXT(C4;"000000000000");COLUMN(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11)>9;0;11-MOD(SUM(INDEX((MID(TEXT(C4;"000000000000");COLUMN(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11))&IF(11-MOD(SUM(INDEX((MID(TEXT(C4&IF(11-MOD(SUM(INDEX((MID(TEXT(C4;"000000000000");COLUMN(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11)>9;0;11-MOD(SUM(INDEX((MID(TEXT(C4;"000000000000");COLUMN(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11));"0000000000000");COLUMN(A1:M1);1))*(({6\5\4\3\2\9\8\7\6\5\4\3\2}));0));11)>9;0;11-MOD(SUM(INDEX((MID(TEXT(C4&IF(11-MOD(SUM(INDEX((MID(TEXT(C4;"000000000000");COLUMN(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11)>9;0;11-MOD(SUM(INDEX((MID(TEXT(C4;"000000000000");COLUMN(A1:L1);1))*({5\4\3\2\9\8\7\6\5\4\3\2});0));11));"0000000000000");COLUMN(A1:M1);1))*(({6\5\4\3\2\9\8\7\6\5\4\3\2}));0));11));"00\.000\.000\/0000-00")

Espero que tenha gostado desse desafio!

Caso tenha alguma sugestão de outra técnica ou melhoria do conteúdo acima, poste aqui nos comentários.

Se gostou, curta e compartilhe este artigo para que todos saibam o que é possível fazer dentro do MS Excel.

E ja conhece o meu novo curso online de Excel?

Abraços a todos e até o próximo artigo!

Fabio BALDINI

Frase do Dia: "A matemática, vista corretamente, possui não apenas verdade, mas também suprema beleza " Autor: Bertrand Russell

1 comentário

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *