domingo, 17 de julho de 2016

Separe CNPJs e CPFs com o filtro do Excel

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 diferenciar CNPJ de CPF

  1. 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.

  2. 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

  1. Ative o filtro no Excel

    Excel - Ativar Filtro
    • 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)
  2. Filtre os CNPJs

    Excel - Filtro ativado
    • 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.
  3. 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.
    • Excel - Fill Down
    • 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.
  4. Limpe o filtro

    • abra o filtro novamente e "Limpar filtro" ou "selecionar tudo"
  5. 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.
  6. 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á.