Mostrando postagens com marcador Limpar. Mostrar todas as postagens
Mostrando postagens com marcador Limpar. Mostrar todas as postagens

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

3 de agosto de 2015

Excel: extrair comentários de células (get cell comments)

Não entendo o motivo, mas algumas pessoas ou instituições inserem comentários em células do Excel. A informação fica escondida e péssima de utilizar, no caso de você precisar dela:

célula com comentário escondido no Excel

Quando são uma ou duas, é fácil copiar e colar. Agora se você tiver 50 ou 100, vai perder um tempo detestável fazendo isso.

Veja abaixo como retirar os conteúdos inseridos como “Comentário” no Excel e colocá-los em células comuns como texto. Fiz no Excel para Mac, mas deve ser uma solução similar no Windows.

Solução rápida e permanente

  1. Baixe o Add-in “Get Cell Comments

  2. Instale o Add-in: mova o arquivo .xla para a pasta Documents > Microsoft User Data > Excel

    image

  3. Agora, abra o Excel e utilize a fórmula =getComment(XXXXX), onde no lugar de XXX você coloca a referência para a célula com os comentários.

    image

Caso não funcione, tente uma dessas soluções manuais, que foi como eu gerei o arquivo .xla:

Soluções manuais

Pra funcionar uma vez só (na planilha atual)

  1. No Excel, abra o editor Visual Basic: Menu Tools > Macro > Visual Basic Editor

    image

  2. Menu Insert > Module

    image

  3. Na janela em branco que apareceu, cole o código a seguir:

    Function getComment(incell) As String ' aceepts a cell as input and returns its comments (if any) back as a string On Error Resume Next getComment = incell.Comment.Text End Function

    Vai ficar assim: image

  4. Volte para a janela da sua planilha e use digite a fórmula =getComment(XXXXX), onde no lugar de XXX você coloca a célula com os comentários.

    image

Solução permanente, pra funcionar sempre

O procedimento é quase o mesmo, mas envolve salvar o arquivo como um Add-in para deixar instalado e funcionar sempre das próximas vezes.

  1. Crie um documento novo

  2. Abra o editor Visual Basic: Menu Tools > Macro > Visual Basic Editor

    image

  3. No painel lateral (“Project”), selecione o item que parece conter a sua planilha atual. No meu caso foi “VBA Project (Workbook1)”

    image

  4. Insira um novo módulo. Pode ser clicando com o botão direito em cima do item que você acabou de selecionar, ou no menu Insert > Module.

  5. Na janela em branco que apareceu, cole o código a seguir:

    Function getComment(incell) As String ' aceepts a cell as input and returns its comments (if any) back as a string On Error Resume Next getComment = incell.Comment.Text End Function

  6. Salve o arquivo como “Excel Add-in”, um formato com extensão .XLA

    image

  7. Depois de salvo esse Add-in pode ser instalado, movendo-o o arquivo .xla para a pasta Documents > Microsoft User Data > Excel:

    image

  8. Agora, no Excel é só utilizar a fórmula =getComment(XXXXX), onde no lugar de XXX você coloca a célula com os comentários.

    image


Referências utilizadas:

http://chandoo.org/wp/2009/09/03/get-cell-comments/

http://www.extendoffice.com/documents/excel/765-excel-convert-comments-to-cells.html

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.

30 de abril de 2014

Excel: formatando tabelas complexas

 

 Passo a passo. Formatação de uma tabela com muitas variáveis em um formato de tabela em que seja possível usar filtros, fazer cálculos, subtotais (tabela normalizada). No final, uma tentativa frustrada de gerar um gráfico rápido, mas o Excel não se mostrou muito simples.