Mostrando postagens com marcador Formato de dados. Mostrar todas as postagens
Mostrando postagens com marcador Formato de dados. Mostrar todas as postagens

10 de fevereiro de 2023

Como formatar CPF e CNPJ no excel


Padrão de formatação de CPNJ e CPF

Quantos números tem um CPF e um CNPJ por padrão? Os registros de pessoa física e jurídica seguem o seguinte formato, definidos pela Receita Federal:

Quantos números tem o CPF?

O formato padrão do CPF são 11 dígitos, sendo o 9º responsável por identificar a região fiscal, e os dois últimos, dígitos verificadores. A formatação padrão é a seguinte, com pontos e traços:

XXX.XXX.XXX-XX

Quantos números tem o CNPJ?

O formato do CNPJ definido pela Receita Federal tem 14 dígitos, nesse padrão, com barra e traço:

XX.XXX.XXX/0001-XX


O CNPJ tem o sinal de barra e normalmente aquele "mil ao contrário", mas não é regra. O número de dígitos difere entre os dois, mas ambos tem dois dígitos verificadores no final.


Veja também: mais dicas de Excel

Como formatar CPF no Excel

Depois de colocar uma lista de CPFs ou CNPJs no excel, eles podem ter perdido a formatação com pontos, traços e barras. Ou você apenas precisa deixar uma tabela pronta para preenchimento desses registros de pessoa física ou jurídica. Veja abaixo como criar uma máscara no excel, formatando um número como CPF, com zeros à esquerda, quando houver:

  1. Clique com o botão direito na célula que contém o CPF e vá na opção "Formatar células"
  2. 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


  3. Selecione a opção "Personalizado" ou "Custom"
  4. Digite ou copie e cole esse código para formatar o CPF:
    000"."###"."###-##
  5. Pronto. Qualquer número com 11 dígitos inserido nessa célula será formatado corretamente como um CPF

Como formatar CNPJ no Excel

Use essa fórmula para mascarar qualquer CNPJ no Excel, inclusive mantendo zeros à esquerda na frente do número, quando houver:
  1. Clique com o botão direito na célula que contém o CNPJ e vá na opção "Formatar células"
  2. Selecione a opção "Personalizado"
  3. Digite ou copie e cole esse código para formatar o CNPJ:
    00\.000\.000\/0000-00
  4. Pronto. Qualquer número com 14 dígitos inserido nessa célula será formatado corretamente como um CNPJ

Fórmula para formatar CPF e CNPJ no Excel ao mesmo tempo

Use essa máscara para formatar números que possam ser tanto CPFs como CNPJs inseridos na mesma célula. O código contém uma condicional que aplica uma ou outra formatação com base na diferença no número de dígitos
  1. 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"
  2. Selecione a opção "Personalizado"
  3. Digite ou copie e cole esse código para formatar:
    [<=99999999999]000\.000\.000-00;00\.000\.000\/0000-00

Como converter PDF para excel

Se você tem uma tabela que foi trancafiada armazenada num formato PDF, provavelmente não consegue utilizar aquela informações para nenhuma análise, cálculo ou mesmo fazer uma nova ordenação dos dados. O melhor a fazer é extrair essa tabela para um formato editável como uma planilha de excel.


Há algumas opções de ferramentas disponíveis, grátis e pagas, para converter arquivos PDF em tabelas no formato XLSX. A melhor solução para essa conversão é o Acrobat Pro, ele consegue interpretar bem vários problemas típicos de tabela em PDF como células mescladas, várias linhas de cabeçalho, rodapés com imagem, entre outros. É uma ferramenta paga e vem junto da assinatura do Adobe Creative Cloud; então se você precisa fazer isso esporadicamente, pode pedir para algum amigo designer que já tenha a assinatura fazer essa conversão.


O melhor conversor gratuito de PDF para excel é o I Love PDF, é fácil de usar e entrega um arquivo com qualidade. 


Dica antes de começar: para obter um resultado melhor, prepare o arquivo deixando somente as páginas que possuem tabelas. Você também pode remover as páginas desnecessárias do arquivo usando essa outra ferramenta no site I love PDF (Excluir páginas do PDF).


Veja onde acessar cada conversor de PDF e os detalhes de cada um a seguir:


Adobe Acrobat Pro

Desktop (win/mac)

  • A melhor solução para conversão de Excel em PDF
  • Licença: paga. Vendido individualmente (R$ 86/mês), ou junto com a suíte Adobe Cloud (R$ 124/mês)
  • O Acrobat PRO a partir da versão 10 (X) tem uma função File > Export > XLSX
  • Interpreta células mescladas, várias linhas de cabeçalho, rodapés com imagem, entre outros. Se a tabela não sai perfeita, reconhece a maioria das tabulações e separa bem as colunas
  • Funciona em quase todos os casos
  • Entrada: PDF
  • Saída: XLS

http://www.adobe.com/br/products/acrobatpro.html

Conversores gratuitos

I Love PDF

Conversor online

  • Licença: grátis
  • Entrada: PDF
  • Saída: XLS, DOC (baixa direto na página)
  • A melhor solução gratuita para conversão de PDFs

http://www.ilovepdf.com/pt/pdf_para_excel

Comet Docs

Conversor online

  • Licença: grátis pra 5 conversões por semana
  • Entrada: PDF
  • Saída: XLS, TXT… (envia um link por email)
  • Funciona bem

http://www.cometdocs.com/


Tabula

Desktop (mac/win). Roda no browser (tem uma versão online do Tabula aqui, mas os PDFs ficam públicos no site)

  • Licença: Free / Open Source
  • Serve pra: Extrair dados de PDFs (tabelas)
  • Fácil de usar
  • Entrada: PDF
  • Saída: CSV ou TSV
  • Como usar: desenhar um retângulo sobre uma tabela, e ele identifica os dados e oferece opção de copiar ou baixar. Se o arquivo tiver tabelas idênticas (mesma posição e tamanho) em todas as páginas, tem um botão "Repetir seleção".
  • Bom pra PDFs com poucas páginas (se tiver que desenhar a seleção em todas) ou com estrutura idêntica em todas as páginas (pra usar o "Repetir seleção").

https://tabula.technology/


Veja mais 

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 formatar CPF no Excel

Depois de colocar uma lista de CPFs ou CNPJs no excel, eles podem ter perdido a formatação com pontos, traços e barras. Veja abaixo como formatar um número no excel como CPF:

  1. Clique com o botão direito na célula que contém o CPF e vá na opção "Formatar células"
  2. 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


  3. Selecione a opção "Personalizado" ou "Custom"
  4. Digite ou copie e cole esse código para formatar o CPF:
    000"."###"."###-##
  5. Pronto. Qualquer número com 11 dígitos inserido nessa célula será formatado corretamente como um CPF

Como formatar CNPJ no Excel

  1. Clique com o botão direito na célula que contém o CNPJ e vá na opção "Formatar células"
  2. Selecione a opção "Personalizado"
  3. Digite ou cole esse código para formatar o CPF:
    00\.000\.000\/0000-00
  4. 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

Use essa máscara para formatar números que possam ser tanto CPFs como CNPJs inseridos na mesma célula. O código contém uma condicional que aplica uma ou outra formatação com base na diferença no número de dígitos
  1. 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"
  2. Selecione a opção "Personalizado"
  3. Digite ou cole esse código para formatar o CPF:
    [<=99999999999]000\.000\.000-00;00\.000\.000\/0000-00

A documentação a seguir se aplica mais a limpeza e identificação de listas já prontas de CPF e CNPJ. Foi um processo realizado com dados públicos, como tabelas de doação de campanha ou de prestadores de serviço para deputados.

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

29 de outubro de 2014

Edição de texto: Transformar listas em tabelas

Como transformar textos lineares, digitados em formato de "ficha", em tabelas, com a ajuda de um editor de texto e fazendo buscas e substituições. Aqui foi usado o editor de texto gratuito para mac TextWrangler e o Open Refine. O TextWrangler tem suporte a buscas e substituições mais avançadas (expressões regulares, ou GREP). Uma opção para windows é o Notepad++, mas até o Word quebra alguns galhos.

Porque a tabela?

Uma tabela é muito mais útil para trabalhar com muitas ocorrências de estrutura semelhante, comparáveis e estruturadas. Com a tabela as informações podem ser filtradas, reordenadas, calculadas, visualizadas, etc., coisas que não poderiam ser feitas com o texto puro.


Link direto do vídeo: http://youtu.be/SOBvEj7KQl4

Transformar listas em tabelas

Aqui vai uma descrição rápida do processo que eu fiz enquanto limpava esse texto e gravava o vídeo:
1. Conferir/identificar padrões
2. Usar um editor de texto mais poderoso (com suporte a expressões regulares "GREP")
3. Identificar número de ocorrências, e se os mesmos itens estão em todas as ocorrências
4. Organizar anotações:
- número de campos
- ordem
- como vai ficar a tabela
- número de tabs pra organizar as colunas
5. Começar a limpar o texto.
- eliminar os campos e jogar o número de tabs correspondentes à posição de cada campo
6. Fill Down com o Google/Open Refine
- adicionar cabeçalho
- identificar caractere separador dos campos: tab (formato TSV)
- garantir que ele esteja lendo a primeira linha como cabeçalho (se você já pos o cabeçalho.)
- criar projeto
- fazer o Fill Down em todas as colunas até chegar na última, sem fazer na última. Opções da coluna > Edit Cells > Fill Down.
- o Fill Down preenche as células vazias com duplicando o conteúdo até encontrar a próxima célula preenchida, e assim por diante.
- o objetivo é fazer com que a última linha da sequência esteja com todos os campos preenchidos. 
- assim que a última linha da sequência estiver inteira preenchida (feito o Fill Down em todas as colunas necessárias), pode-se apagar as outras linhas temporárias.
- é possível fazer isso identificando as linhas que tem o último campo vazio.
- Opções da coluna > Facet > Customized Facet > Facet by Blank. O facet identifica e indexa todo o conteúdo que tem naquela coluna. A partir do index que ele faz, você pode filtrar a tabela, clicando no item que você quer mostrar. O facet que escolhemos (Facet by Blank) identifica as linhas vazias e preenchidas. Então ao clicar em true, só as linhas vazias (naquela coluna) vão aparecer. E clicando em false, só as linhas preenchidas vão aparecer.
- Apagar linhas vazias: Selecionar "true" > Opções da coluna "All" > Edit Rows > Remove all matching rows.
- agora todas as linhas estão preenchidas. Exportar > Escolher um formato 
7. Puxar pro Excel.
8. Feito.

Um outro exemplo:

Link direto do vídeo: http://youtu.be/yxrkg9xFxyo

--
Using GREP or RegEx (regular expressions) to transform textual lists into tables / spreadsheets. TextWrangler (mac) and Open Refine and Microsoft Excel helped to do the trick.

18 de maio de 2014

Dados normalizados (reshape data)

O que é

Reshape (ou unpivot ou normalizar) de uma tabela é um processo necessário em alguns casos pra conseguir visualizar os dados usando algum software como Tableau, Raw, R, ou mesmo pra utilizá-los no Excel com mais funcionalidades (como Filtro, Subtotais, …).

Uma tabela normalizada (reshaped) é uma tabela em um formato que o software vai entender. Visualmente (como tabela) pode ficar mais confusa ou redundante. Mas o propósito básico dela é conseguir fazer cálculos ou utilizar os dados para visualização. Então, sabendo quando utilizar, pode ser muito útil ou essencial.

Como devem ficar os dados

Essa é a tabela antes do reshape.
Ela é lida célula por célula, como no batalha naval. ("Cidade 3" tem o "Problema B" = 3. Bomba!)
Nas células no meio do caminho, estão o número de ocorrências.
É uma boa tabela para compilar dados e visualizar como tabela no Excel mesmo.
É comum também cada coluna ser um período (2006, 2007, 2008...)

Tabela antes do reshape
Em alguns casos, a tabela precisa do reshape para ficar neste formato.
Ela deve ser lida linha por linha.
Agora, cada coluna tem apenas uma "característica" (Problema e Valor) da "coisa" (Cidade) que está na primeira coluna. 
Esse formato de tabela é utilizado pelos softwares de banco de dados (tabela normalizada). Uma das regras da tabela normalizada é que cada linha pode ser lida individualmente e se refere apenas a uma coisa. Cada linha é um registro, uma ocorrência. Na linha 6, o "Problema B" e o valor "3" dizem respeito à "Cidade 3". 
Problema e Valor são características da Cidade. O conjunto dos 3 na mesma linha é um registro.

Tabela depois do reshape





Como fazer



Plugins

(Tools for reshaping / unpivot / normalize data)
Neste caso foi usado um plugin para Excel para fazer a transformação:

Reshaping Data - Tableau Add-in. (Funciona só em Windows)
http://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel

O Tableau também tem um tutorial de como preparar os dados
http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis

Alternativa para Excel no Mac:

Add-in "Table 2 DB":
https://drive.google.com/file/d/0Bya641p0XuQjQ3czOFUxeE1TUEk/view?usp=sharing
(Excel for Mac Reshape Add-in)

Veja onde instalar:
Table 2 DB installation folder (Excel for Mac Add-in)

Outra forma possível é o Data Wrangler (http://vis.stanford.edu/wrangler/).

Outro Exemplo

Um exemplo de reshape com dados reais: