Como usar o filtro do Excel para preencher uma lista de valores em uma coluna nova.
Para facilitar a apuração para uma matéria, uma tabela de doações de campanha precisava ter uma coluna identificando os registros como CNPJ ou CPF.
Normalmente tabelas de prestação de contas vêm apenas com o número, e não especificam se a doação veio de uma pessoa física ou pessoa jurídica. O mesmo acontece em tabelas de gastos. Na verdade, se o documento veio identificado com nome ou razão social, você já pode ficar feliz. A Assembleia Legislativa do Paraná, por exemplo, publica a prestação de contas dos deputados estaduais sem identificação, apenas com o número.
Com um truque simples no excel é possível acrescentar uma coluna com a diferenciação:
Passo a passo detalhado abaixo:
Padrão de formatação de CPNJ/CPF
Geralmente, os registros de pessoa física e jurídica vem no seguinte formato:
Padrão de CNPJ
XX.XXX.XXX/0001-XX
14 dígitos
Padrão de CPF
XXX.XXX.XXX-XX
11 dígitos
O CNPJ tem barra e normalmente aquele "mil ao contrário", mas não é regra. O número de dígitos também difere entre os dois, mas talvez existam cnpjs ou cpfs fora do padrão. Desconfie sempre das tabelas.
Como formatar CPF no Excel
- Clique com o botão direito na célula que contém o CPF e vá na opção "Formatar células"
- Para isso você também pode usar o atalho Ctrl + 1 (ou Cmd + 1 no Excel for Mac) ou usar a formatação de número na barra de ferramentas
- Selecione a opção "Personalizado" ou "Custom"
- Digite ou copie e cole esse código para formatar o CPF:
000"."###"."###-## - Pronto. Qualquer número com 11 dígitos inserido nessa célula será formatado corretamente como um CPF
Como formatar CNPJ no Excel
- Clique com o botão direito na célula que contém o CNPJ e vá na opção "Formatar células"
- Selecione a opção "Personalizado"
- Digite ou cole esse código para formatar o CPF:
00\.000\.000\/0000-00 - Pronto. Qualquer número com 14 dígitos inserido nessa célula será formatado corretamente como um CNPJ
Fórmula para separar CPF E CNPJ no Excel
- Clique com o botão direito na célula com o número do CPF ou CNPJ e vá na opção "Formatar células"
- Selecione a opção "Personalizado"
- Digite ou cole esse código para formatar o CPF:
[<=99999999999]000\.000\.000-00;00\.000\.000\/0000-00
Como diferenciar CNPJ de CPF
Verifique a qualidade dos dados
Passe o olho na tabela para verificar se informações parecidas estão formatadas do mesmo jeito.
Neste caso a tabela estava bem organizada, os números estão formatados com um padrão correto (pontos, traços, barras, ...) e mesma quantidade de dígitos.
Identifique um padrão para poder separar os dados
Se a tabela não vem com uma coluna "Pessoa Física/Jurídica", é preciso criá-la. É possível fazer isso encontrando a diferença entre os dois tipos de número. Uma diferença fácil é a quantidade de dígitos, mas isso não é tão simples de "selecionar" no excel. Já a presença da barra (/) nas células de CPNJ é um padrão fácil de ser selecionado.
Como separar CNPJ e CPF no Excel
Ative o filtro no Excel
- Selecione a tabela toda (Ctrl + A / Cmd + A)
- Certifique-se de que sua tabela não tem "buracos" (linhas ou colunas totalmente vazias). Se tiver, a seleção da tabela ou o filtro podem não funcionar. Se tiver dúvida, faça a seleção com o mouse.
- Dados > Filtro (Ctrl + Shift + F / Cmd + Shift + F)
Filtre os CNPJs
- Clique no filtro da coluna CPF/CNPJ (botão com a seta pra baixo no cabeçalho)
- Digite a barra "/". Acho que no Windows a janela do filtro precisa de um OK a mais.
- Quando o filtro funcionar, a tabela irá exibir apenas linhas nas quais as células da coluna selecionada contenham o caractere barra. Ou seja, neste caso, linhas referentes a CNPJs.
- Quando o filtro está ativado, o número das linhas na esquerda muda de cor, para indicar que existem linhas que estão ocultas. O ícone do filtro também muda na coluna onde ele foi ativado.
Crie uma coluna nova
- Dê um nome à coluna nova "CNPJ ou CPF", "PF ou PJ", ou algo assim.
- Ao lado do primeiro CNPJ encontrado, digite "CNPJ" ou "Pessoa Jurídica".
- Preencha todas as células abaixo ("Fill down") com a mesma informação. Clique no quadradinho no canto da célula e arraste para baixo até o final da tabela.
- Dica para um Fill Down mais rápido: se sua tabela está "sem buracos" (células vazias), dê dois cliques no quadradinho que ele irá se preencher até o final da tabela (ou até encontrar uma célula vazia à esquerda). Sempre vá até o final pra ver e garantir que deu certo.
Limpe o filtro
- abra o filtro novamente e "Limpar filtro" ou "selecionar tudo"
Use o filtro na coluna nova
- Agora que você já preencheu CNPJ na coluna nova, é só preencher "CPF" nas células que ficaram vazias.
- Clique no filtro da coluna nova
- Desmarque "CNPJ" e deixe marcadas a células vazias.
- Digite CPF na primeira célula e faça o Fill Down novamente.
Desative o filtro e pronto!
Avançado: Limpar listas grandes de CNPJs e CPFs
Essa parte é um epílogo um pouco mais avançado para quem usa Mac. O script limpa a formatação dos números (pontos, barras, traços) deixando só os dígitos e acrescentando uma coluna de texto identificando se é CNPJ ou CPF.
- Use o TextWrangler (editor de texto para Mac)
- Copie do Excel apenas a coluna de cnpjs e cpfs
- Cole a lista no Textwrangler
- Baixe esse applescript e aperte play.
- A lista está pronta com os números limpos e uma coluna a mais
- Insira uma coluna a mais no excel (já que agora são duas) e cole de volta o conteúdo lá.
