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
Link permanente
Obrigado ! Ajudou muito.
Excelente trabalho.