Mostrando postagens com marcador Excel. Mostrar todas as postagens
Mostrando postagens com marcador Excel. 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 

26 de maio de 2020

Gráficos online que atualizam automaticamente

Como fazer gráficos que atualizam dinamicamente quando seus dados mudam? Ou seja, criar um gráfico que se modifique sozinho quando os dados atualizarem. Quem trabalha com dados no jornalismo precisa em algum momento criar um monitor para acompanhar determinado assunto, mantendo as informações atualizadas com certa frequência, puxando direto da fonte oficial ou de uma planilha intermediária. Aí surge a necessidade de criar uma visualização que se alimente desses dados e se atualize sozinha sem que você tenha que toda vez abrir para atualizar os gráficos.

Com os dados sobre o Coronavírus, muita gente precisou criar mais gráficos para acompanhar a evolução da epidemia, e manter os gráficos atualizados se tornou uma dificuldade à parte. Testei muitas fontes e programas para criar um monte de gráficos automatizados neste período até chegar em soluções sustentáveis, e posso indicar 3 ferramentas online gratuitas para criar gráficos que se alimentam de dados dinâmicos: Google Sheets, Datawrapper e Tableau Public. Em todas elas você pode usar o Google Docs como fonte dos dados ou um CSV externo.

1. Google Docs (sheets)

https://docs.google.com/spreadsheets/u/0/

Mapas e gráficos do Google Docs com atualização automática, usando os dados inseridos na mesma planilha


Prós:

  • Atualização rápida (em torno de 5 min, se não for imediata)
  • Gráficos no mesmo lugar onde estão os dados (pode ser uma vantagem para manuntenção)
  • Leves (carregamento rápido quando embedado)

Contras:

  • Gráficos são mais simples
  • Gráficos não são responsivos (se você quiser atender diferentes tamanhos de tela, tem que duplicar e ajustar o tamanho do gráfico)
  • Mapas muito básicos (não dá pra usar só o Brasil, por exemplo, só a América do Sul inteira) e um pouco lentos para carregar
  • A fonte tem que ser o próprio google Sheets (você pode até usar uma fonte externa, mas importando para o Google Docs, com funções como IMPORTXML, IMPORTCSV ou mesmo IMPORTHTML, para elementos table)

Frequência de Atualização:

Imediata (ou geralmente em até 5 minutos)

Como fazer um gráfico dinâmico no Google Sheets

  1. Insira seus dados em uma tabela do Sheets
  2. Selecione os dados
  3. Vá no menu "Inserir gráfico"
  4. O gráfico já aparece na tela, com sugestão de formato de acordo com seus dados. Faça todos os ajustes no editor de gráfico, na barra lateral.
  5. Publique. No botão de opções (três pontinhos) no canto do gráfico, há uma opção Publicar. No popup, escolha Incorporar, e copie o código de embed

2. Datawrapper

https://www.datawrapper.de/

Método de conexão de dados do Datawrapper com o Google Sheets ou um CSV externo, para manter os dados atualizados no gráfico publicado


Prós:

  • Aceita tabelas do Google Docs (existe o modo importação, que deixa os dados estáticos, e o modo "Link external dataset", o mesmo caminho para CSVs externos, que é a opção que mantém seus dados atualizados)
  • Aceita CSVs externos, salvos em algum servidor web
  • Gráficos muito versáteis, de diversos tipos
  • Bonito, visual interessante
  • Responsivo (adapta muito bem em tamanhos diferentes, tem até ajustes específicos mobile/desktop)

Contras:

  • Não é totalmente personalizável.
  • A interatividade é simples (tooltips). Mas não dá para fazer filtros ou seleções
  • O crédito precisa aparecer na versão grátis

Frequência de Atualização:

A cada minuto no primeiro dia após a publicação. Depois de 24h, é atualizado de hora em hora (para resetar a contagem, basta abrir o gráfico para republicar).
Para forçar a atualização, precisa abrir o gráfico, e ir na aba "Check & Describe" para puxar os dados novos (não precisa republicar o gráfico)

Como fazer um gráfico dinâmico no Datawrapper

  1. Crie um novo gráfico (New Chart, New Map ou New table)
  2. Na tela de Upload Data, escolha "Link external dataset" (a opção "Import Google Spreadsheet" não vai manter seu gráfico atualizado, ela só importa os dados uma vez)
  3. Cole o link de um CSV externo. No caso de uma tabela do Google, ative o compartilhamento da planilha antes (Compartilhar > Copiar link > Mudar para qualquer pessoa com link. Depois disso, pode copiar até o link pela barra de endereços)
  4. Siga as etapas para criar o gráfico
  5. Finalize em "Publish & Embed" para copiar o código

Exemplo de gráfico automático com Datawrapper:

3. Tableau

https://public.tableau.com/

Método de conexão de dados do Tableau Public com o Google Sheets, para manter os dados atualizados no gráfico publicado


Prós:

  • Gráficos mais complexos e dashboards
  • Possível criar filtros e permitir maior interatividade com os gráficos
  • Possível criar cálculos, agrupamentos e transformações com os dados

Contras:

  • Pesado (carregamento mais lento)
  • Curva de aprendizado maior
  • Não é online (tem para windows e mac)

Frequência de Atualização:

1 vez por dia. Para forçar a atualização, precisa estar logado na conta do Tableau Public e clicar em "Solicitar atualização", no rodapé do gráfico, que fica no seu perfil.

Como fazer um gráfico dinâmico no Tableau Public

  1. Connect to Data > Google Sheets
  2. Dê as permissões no navegador
  3. Escolha a planilha da sua conta
  4. Crie a visualização
  5. Save to Tableau Public...
  6. Deixe marcada a opção "Manter meus dados sincronizados..." ("Keep my data in sync with Google Sheets and embed my Google credentials")

Exemplo de gráfico automático com Tableau

Mapa e evolução do coronavírus por cidades no Brasil

4. Flourish (menção honrosa)


Método de upload de dados para criar visualizações de dados com o Flourish (CSV, TSV, Json, GeoJson)


O Flourish é uma ferramenta sensacional. Possui interatividades e animações (o modelo da famosa bar chart race), vários tipos de gráficos interessantes, e é versátil nas aplicações, como a possibilidade de criar "histórias", com uma sequência de gráficos, ou até mesmo embedar um gráfico dentro do outro.

Mas o Flourish não tem, até o momento, um método simples de conexão com uma fonte de dados dinâmica. Há apenas a opção de inserir dados via Copiar e Colar, ou upload de arquivos Excel, CSV, TSV, Json e Geojson. Quando lançaram o modelo de Growth Comparison, para comparação do crescimento da Covid-19 entre países, vi que eles possuem API. Para quem vai construir o gráfico via API, é possível usar dados dinâmicos via Json, por exemplo, mas aí depende de saber programar, diferente das outras soluções.




18 de maio de 2020

Como copiar só as células visíveis no Excel

Uma dúvida comum para quem usa bastante o Filtro do Excel é: depois de filtrar, como copiar apenas as células que estou vendo? Ou: se eu copiar essa seleção, as células ocultas vão ser copiadas junto?

Além de a resposta não ser óbvia do ponto de vista lógico ou de usabilidade, o próprio Excel não é muito previsível ou consistente, principalmente no Windows. O ideal, para usuários frequentes do filtro, é testar bastante até se acostumar com a ferramenta e ter certeza do que você vai estar fazendo. Estar seguro sobre isso pode garantir a qualidade dos dados com que você está trabalhando.

Vamos lá. Veja como selecionar apenas as células visíveis no Microsoft Excel:

Excel para windows

Como copiar somente células visíveis no Excel para Windows - Menu Find & Select
  1. Na barra de ferramentas do Excel, vá até a "Página inicial" (Home), e encontre o "Localizar e selecionar" (Find & Select)
  2. Ir para > Especial... (Go To > Special)
  3. Apenas células visíveis (Visible cells only) e selecione OK
  4. Agora é só copiar e colar onde você deseja

Excel para Mac

A princípio, o Excel no Mac já copia só as células visíveis e as ocultas não vão ser coladas junto com o restante, quando você usa o filtro.
Mas, se quiser garantir, no Mac OS o caminho é parecido. Quando você usa outras funções, como os Subtotais, também é necessário fazer isso para copiar:
No Excel mais novo, deve ser possível seguir o mesmo caminho do Windows. Na versão Excel for Mac 2011, faça o seguinte:
  1. Vá até o Menu superior "Edit > Go To..." Como copiar somente células visíveis no Excel para Mac - Menu Edit > Go To
  2. Botão "Special..." Como copiar somente células visíveis no Excel para Mac - Botõa Special
  3. Selecione "Visible Cells Only" e dê OK Como copiar somente células visíveis no Excel para Mac - Botão Visible Cells Only

Google Sheets

No programa de planilhas do Google Docs, as células visíveis são copiadas por padrão, tanto na função Filtro como nos Subtotais. Por isso não há um caminho para isso.

Subtotal

Além do filtro, uma outra função que também oculta linhas e é comum para quem trabalha com dados é o uso dos Subtotais. Quando você "recolhe" as linhas para mostrar apenas só os subtotais e não os itens, a seleção também pode copiar as linhas e células ocultas.

Veja no vídeo abaixo como fazer a seleção. O exemplo é no Excel for Mac, usando a função subtotal.


Veja também

Como agrupar dados com subtotais
Outros tutoriais de excel



1 de fevereiro de 2018

Como colocar em ordem alfabética no Google Planilhas

Criar uma tabela é o caminho mais fácil para reordenar (classificar) linhas, em ordem alfabética, ou por ordem de valores. Veja como mudar a sequência de linhas usando a ferramenta de planilhas do Google, o Google Sheets, ou Planilhas Google, em português.





  1. O primeiro passo é travar o cabeçalho (se não tem cabeçalho, crie um) arrastando aquela linha mais grossa entre as linhas 1 e 2. 
  2. Depois, clique na setinha que abre as opções da coluna desejada e "Classificar de A - Z" ou "Classificar de Z - A". Serve tanto para colunas de texto (ordem alfabética) quanto de números (ordem por valor numérico).

Use esse recurso para criar rankings, organizar listas, ou no caso do exemplo, poderia ser usado para agrupar os nomes dos senadores por partido ou por estado (Classificando a coluna "Partido", nomes do PT ficariam todos juntos, do PSDB também, assim por diante).

O que é o Google Planilhas

O Planilhas Google é uma espécie de excel online, software totalmente gratuito da suite Google Docs. Todos os arquivos editados lá são salvos automaticamente e ficam armazenados na nuvem, na sua conta do Google Drive.

Classificar linhas no Excel

Se você precisa ordenar linhas em ordem alfabética (ou em ordem de valores) no Excel o caminho é parecido. Dê uma olhada nessa explicação:
Ordenar listas ou tabelas no Excel

Outro exemplo: ordenando uma tabela no google sheets


23 de novembro de 2017

Transforme uma tabela em mapa interativo do jeito fácil




Veja no passo a passo abaixo como transformar uma tabela do excel com endereços em um mapa interativo no Google Maps. Nesse exemplo os endereços são estados brasileiros.

No Google Maps é possível montar uma visualização de dados simples de um jeito fácil e rápido, formatando cores de forma automática para os pinos, de acordo com uma coluna escolhida na tabela.

Baixe o arquivo de exemplo 


Passo a passo




Mais detalhes

Se você não conseguir reproduzir alguma etapa, veja este tutorial versão detalhada:

Como localizar uma lista ou tabela de endereços com o Google Maps

Ou este tutorial em vídeo:

1 de julho de 2017

Preciso de um infográfico: Usando recursos visuais para comunicar

Abrir slides em outra janela



Curso no Congresso Abraji 2017 (Associação Brasileira de Jornalismo Investigativo)

Dicas de como visualizar dados no dia-a-dia de uma redação com truques simples no próprio Excel e com outras ferramentas grátis como o Google Maps, Tableau Public e o Raw Graphs.

Com o propósito de democratizar a produção de infográficos, gráficos, mapas e tabelas, o foco do curso é que qualquer um pode aprender a comunicar informações visualmente usando estratégias visuais básicas como Cor, Tamanho, Ordenação e Agrupamentos

Ferramentas extremamente simples são muito bem-vindas, como capturas de tela (o famoso print screen) ou geradores de gifs.

Download da tabela de exemplo

27 de junho de 2017

Como calcular taxa por 100 mil habitantes

A imagem explica a fórmula para calcular uma taxa por 1 milhão, 100 mil habitantes, ou similar. O indicador é dividido pela população e multiplicado por 100.000


A taxa por 100 mil habitantes é simples de calcular:
  • Pegue o indicador principal (número de crimes, nascimentos, mortes, casos de doença)
  • Divida pela população do local
  • Multiplique o resultado por 100.000
A fórmula fica assim:

Taxa por 100 mil habitantes = Número de casos / População x 100.000

No Excel, o cálculo da taxa na célula com a fórmula deve ser algo assim:

=  B2 / C2 * 100.000, sendo B2 o número que você quer calcular e C2, a população do lugar. Para que a taxa seja por 1 milhão de habitantes, por exemplo, seria só acrescentar um zero no denominador da fração.

Veja também outros tutoriais de Excel

Como calcular casos ou mortes por milhão de habitantes

Para calcular a taxa de mortes por 1 milhão de habitantes:
  • Pegue o número de casos ou mortes
  • Divida pela população do país ou estado
  • Multiplique o resultado por 1.000.000
A fórmula fica assim:

Mortes por milhão de habitantes = Mortes / População x 1.000.000

No Excel, o cálculo da taxa na célula com a fórmula deve ser algo assim:

=  B2 / C2 * 1.000.000, sendo B2 o número de casos/mortes e C2, a população do lugar (cidade, estado, país).

Com a pandemia do Coronavírus alguns tipos de visualização ficaram mais comuns e passaram até a ser requisitadas pelas pessoas para entender melhor a propagação da doença e a situação do local onde mora. Gráficos com escala logarítmica, médias móveis, taxas por milhão de habitantes saíram do universo dos criadores de visualizações para o público comum, que teve que aprender a ler mais gráficos para compreender as notícias. As comparações por milhão ou cem mil ajudam a comparar países ou estados com tamanhos de população muito diferentes, embora alguns especialistas tenham dito que, pela forma como a epidemia de Covid-19 se alastra, os indicadores per capita não colaboram muito

Porque usar a taxa por 100 mil

Indicadores como a taxa por cem mil habitantes são proporcionais ao tamanho da população em questão, logo, eles permitem comparar populações (países, cidades, bairros) de tamanhos diferentes.

Dividindo o indicador por fatias menores de pessoas, é possível entender a distribuição geográfica de forma mais significativa.

A taxa também pode ser por 1 milhão de habitantes, 10 mil, ou 1 mil, o princípio é o mesmo. Na fórmula, só muda o número multiplicador. Também existem outras formas de relativizar os números, como dividir pela área, ao invés da população.

O problema dos números absolutos

Quando não se usa um indicador relativizado, proporcional à população (ou "normalizado"), é difícil visualizar coisas diferentes de "aqui tem mais gente, aqui tem menos gente".

Mapas, tabelas ou rankings com números absolutos são importantes para determinadas conclusões. Afinal, o país onde mais gente passa fome é uma informação importante, independente de quantas pessoas moram lá. Para outras conclusões, os números relativos podem ajudar mais.

Alguns mapas com números absolutos, em geral, são iguais a mapas populacionais. Veja esses mapas de Curitiba por bairros:




O resultado é parecido com essa tirinha clássica do xkcd:



Em todos eles, o resultado é bem óbvio: o bairro onde tem mais ocorrências de qualquer coisa é o CIC, que é o bairro mais populoso e de maior área. No Brasil, São Paulo deve dominar a maioria dos rankings feitos com números absolutos.

A distribuição geográfica de eleitores, mortes no trânsito ou homicídios, segue quase exatamente a mesma proporção da população. A lógica é simples: onde tem mais gente, tende a ter: mais eleitores, mais mulheres, mais homicídios, mais acidentes de trânsito...



Outros indicadores proporcionais

Não só de taxas por 100 mil se fazem boas comparações. Veja esses outros mapas de Curitiba, que usam outros tipos de cálculo:

A porcentagem de área verde de cada bairro é feita em relação a área do bairro. A densidade populacional também, já que ela é a divisão da população do local pela área. O rendimento médio é uma média, é o quanto em média as pessoas daquela região ganham mensalmente.

Assim como a taxa por 100 mil, esses tipos de indicador (per capita, médias, e em proporcionais à área) permitem comparações mais ricas entre os locais. No exemplo do mapa de Curitiba, eles permitem uma compreensão melhor da cidade.

Os dados de exemplo

Os dados utilizados são do IBGE e do Ippuc; os números de mortes no trânsito são do Projeto Vida no Trânsito.

Os mapas de polígonos foram feitos no Tableau. O dashboard interativo está aqui.



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

19 de maio de 2014

Cruzar dados com Excel: como usar a fórmula VLOOKUP (PROCV)

Quando preciso disso?

Se você já se fez alguma dessas perguntas, o PROCV é pra você:
  • Como cruzar dados usando Excel?
  • Como incrementar minha tabela com informações que estão em outra tabela?
  • Como transportar dados de uma tabela para outra usando um campo comum entre elas?
  • Como ir buscar dados em outra página (folha) da planilha?
  • Como cruzar dados no Google Sheets?
Cruzamento de dados em geral é utilizado por quem trabalha com linguagem SQL e bancos de dados. Mas pra quem isso é grego e precisa trabalhar com quantidades de dados menores, o Excel resolve bem com uma fórmula de busca vertical. 

Demora um pouquinho pra pegar o jeito, mas não desista: pode te salvar muito trabalho. Aqui vão dois vídeos de como usar a fórmula, e a explicação detalhada de como ela funciona:

A fórmula: VLOOKUP

Ou PROCV, no Excel em português e no Google Sheets. É a fórmula para busca vertical.

O que ela faz

Busca um determinado valor em um conjunto de células.
O valor em questão deve estar contido nos dois conjuntos (duas tabelas) para poder ser feito o cruzamento de dados. Esse valor comum exerce uma função parecida ao que chamam de identificador único (unique identifier) ou chave primária (primary key) quando se trata de banco de dados.
Se o valor comum for encontrado, a fórmula pode:
  • devolver o mesmo valor (indica que o valor está contido nas duas tabelas)
  • devolver outros valores (que estão em outras colunas) referentes ao valor pesquisado

Para que é útil

  • verificar listas de nomes, se nomes de uma lista estão contidos em outra.
  • incluir colunas a partir de dados em outra tabela

Como usar

👉 Mais vídeos: Dados Finos no Youtube


Sintaxe

VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)

lookup_value:
valor a ser procurado, comum às duas tabelas.
  • Pode ser um valor, ou uma referência de célula.
  • Pode ser selecionado clicando direto na célula enquanto se edita a fórmula.
  • Ex.: A1

table_array:
conjunto de células (range/array) onde o valor comum vai ser pesquisado*
  • Podem ser várias células em uma mesma coluna, ou em várias colunas.
  • Podem estar na mesma planilha, em outra planilha do mesmo arquivo, ou em outro arquivo.
  • Ex.: A1:A30 ou A$1:A$30
  • Quando os dados estiverem em outra planilha/arquivo: ao editar o segundo campo da fórmula, é só mudar de planilha ou arquivo, selecionar as células e voltar para terminar de editar a fórmula. O campo da fórmula continua ativo para digitação mesmo mudando de janela.

col_index_num:
número da coluna que contém o valor desejado para a fórmula retornar.
O número é relativo ao conjunto de células selecionados no table_array.
  • Se você fez uma seleção envolvendo 3 colunas, e o que você quer é o valor que está na 2ª coluna das 3, coloque o número 2. 
  • Em outras palavras: Ao buscar por um nome em uma tabela de 'nomes' e 'idades', a fórmula pode retornar o nome, se o col_index_num for 1, ou pode retornar a idade correspondente, se o col_index_num for 2
  • Eu sempre coloco 1 para começar,  para testar se a fórmula dá certo. Depois de conferido, altero para o número da coluna desejada.

range_lookup:
opcional na fórmula.
define se a busca vai ser por valores exatos ou aproximados.
  • Colocar TRUE para uma busca aproximada, ou FALSE para busca exata.
  • (Se nada é especificado, acho que é realizada uma busca exata, e em seguida uma aproximada; se nenhum valor exato for encontrado, o valor retornado é o da próxima célula com valor maior ao buscado.)
  • Utilize FALSE para valores textuais (nomes, cidades, ...)

Cuidados especiais!

Travar o range (table_array)

Depois de feita a fórmula para a primeira célula, use o quadradinho no canto da célula para arrastá-la, replicando a fórmula para as outras linhas. 
  • Problema: Se o range de células (table_array) na fórmula não estiver travado (A1:A30), o Excel vai mudar a fórmula à medida que ela for sendo duplicada para outras células, como A2:A31, A3:A32, e assim por diante.
  • Coloque o cifrão antes do número das células para que a seleção permaneça a mesma para todas as fórmulas que você duplicar: A$1:A$30.
  • Quando os dados são selecionados em outro arquivo, o Excel já adiciona o cifrão ($) automático no range de células.

Eliminar a fórmula, manter só os valores

Importante para que você não precise mais da tabela original ou quando quiser mudar a posição das células na planilha sem perder os dados recém calculados:
  1. Selecionar os valores
  2. Copiar
  3. Colar Especial (Paste special). Opção disponível com o botão direito do mouse ou no menu Edit.
  4. Selecionar a opção "Valores" (Values)
  5. Agora os valores que você colou não tem mais a fórmula. (pode mover as células sem problemas ou apagar as originais)

Outro exemplo




Documentação oficial da fórmula VLOOKUP
http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

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:

Ordenar listas ou tabelas no Excel em ordem alfabética

👉 Mais vídeos: Dados Finos no Youtube


Ordenar valores em ordem crescente/decrescente com o Filtro.

  1. Certifique-se de ter um cabeçalho
  2. Selecione todas as células (se selecionar só algumas, a ordem vai bagunçar depois)
  3. Vá até a aba Dados (Data) > Filtro (Filter). Atalho no Mac: Cmd + Shift + F
  4. Use os botões com seta que surgiram no cabeçalho das colunas para ordenar (sort) as linhas em ordem crescente/decrescente (ordem alfabética em caso de texto)



Filtro automático

Usando o filtro sem selecionar todas as células da tabela

O filtro é criado mesmo que você só tenha uma célula selecionada.
Para funcionar corretamente dessa forma:
a tabela não deve ter linhas ou colunas vazias.
Se tiver,  o filtro vai pegar só uma parte dos seus dados, e vai parar assim que encontrar uma linha ou coluna vazia.
(Por tabela quero dizer apenas as células que contém os dados em questão dentro de uma planilha, não todas as células até o fim da planilha)


No Google Planilhas

O caminho no editor de tabelas do Google é até mais simples. Dê uma olhada:
Ordenar tabelas no Google Docs


--
Sorting ascending/descending values with Filter in Excel.

--
Veja também: mais dicas de Excel

Outro exemplo: ordenando uma tabela no Excel



Excel: contas simples



Divisão, arredondamento e soma.

Excel: arredondando números grandes



Como arredondar números com Excel

Veja como abreviar milhões e bilhões no Excel, arredondando e simplificando números que são inteiros (mas muito grandes). Por exemplo transformando:
 
2.653.459.989 em 2,65 bilhões ou
    1.300.000.000 em 1,3 bilhão* 

    (*bilhão no singular, pois é menor que dois, apesar das casas decimais confundirem)
    1. Crie uma coluna extra, ao lado daquela que tem seus números grandes
    2. Escreva o cabeçalho novo ("Nome do indicador" em milhões/bilhões/milhares…)
    3. Na célula da primeira linha da coluna nova:
      • Digite o sinal de igual (=) para iniciar uma fórmula
      • Com o mouse clique na célula ao lado que tem o número a ser abreviado
      • Digite o sinal de barra (/) para fazer a divisão
      • Digite o número pelo qual você quer dividir: 1000000 para abreviar para milhões (um seguido de seis zeros), 1000000000 para abreviar para bilhões (um seguido de nove zeros), e assim por diante.
      • Digite enter para finalizar
    4. Arraste a fórmula até onde for preciso (pelo quadradinho azul no canto inferior direito da célula). Se der dois cliques no quadradinho a fórmula vai até o final da sua coluna automaticamente.
    5. Ajuste o número de casas decimais. Diminuir pra uma, duas ou nenhuma, dependendo da necessidade. (botão com seta azul e zeros na aba Home/Página Inicial ou no menu Format > Cells… > Number > Decimal Places).

    [etapa opcional]

    Elimine a fórmula para deixar só os valores

    Essa etapa é opcional, mas importante quando você quiser eliminar a coluna original ou mudar a posição das células na planilha sem perder os dados recém calculados. É mais segura para quando você vai ficar fazendo muitas modificações na tabela:
    •     Selecione os valores
    •     Copie (Ctrl + C)
    •     Colar Especial (Paste special). Opção disponível clicando com o botão direito ou no menu Edit.
    •     Selecione a opção "Valores" (Values)
    •     Agora os valores que você colou não tem mais a fórmula. (Pode mover as células sem problemas ou apagar as originais)

    --
    How to round numbers with Excel.
    Rounding/simplifying big numbers (millions, billions, ...) with Excel.

    Veja também 

    Entendendo números grandes

    Quanto é um trilhão?

    Um trilhão tem dozes zeros, é a forma escrita do número 1.000.000.000.000. São mil bilhões, ou um milhão de vezes um milhão. Em Portugal, um trilião (escrito com i e não h) é outro número, com dezoito zeros.

    Quanto é um bilhão?

    Um bilhão tem nove zeros, é a forma escrita do número 1.000.000.000. São mil vezes um milhão. Pode haver confusão com o português de Portugal, em que 1 bilião (grafado diferente) tem 12 zeros, número que para os brasileiros seria um trilhão.

    Quanto é um milhão?

    Um milhão tem seis zeros, é a forma escrita do número 1.000.000. São mil vezes mil.

    Quanto é cem mil? 

    Cem mil tem cinco zeros, é a forma escrita do número 100.000.

    Bilhão ou bilião?

    Veja a diferença na nomenclatura dos números grandes entre Brasil e Portugal:

    NúmeroQuantos zerosPortuguês BrasileiroPortuguês EuropeuPotência de 10
    1.000três zerosmilmil3
    1.000.000seis zerosmilhãomilhão6
    1.000.000.000nove zerosbilhãomil milhões9
    1.000.000.000.000doze zerostrilhãobilião12
    1.000.000.000.000.000quinze zerosquatrilhãomil biliões15
    1.000.000.000.000.000.000dezoito zerosquintilhãotrilião18

    Tabela adaptada daqui.

    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.