27 de maio de 2014

Fusion Tables - conferir e editar endereços

O Google Fusion Tables é bom para localizar muitos endereços de uma só vez, por algumas razões:
- localiza bem (usa o mecanismo do google maps)
- o endereço pode estar em vários formatos (endereço, bairro, rodovia, país...)
- permite verificar e corrigir o endereço.

Essa última razão é a mais importante quando se trabalha com muitos dados (já localizamos mil endereços com ele. Ter como conferir as localizações era estritamente necessário).

Conferindo e corrigindo endereços

Veja como conferir os endereços localizados e corrigir o endereço quando necessário:



(As coordenadas no vídeo foram conseguidas usando o QGis. Veja abaixo outras opções)

O filtro permite que você escolha um campo da tabela para ver uma parte dos dados por vez. Dependendo de como estão os dados, é bom filtrar por bairro ou regional, assim é fácil de ver os pontos errados (outliers), pois estarão mais longe. O filtro é fácil de ser usado, há um botão Filter logo acima do mapa. As opções para filtrar aparecem no painel à esquerda.

Formato de coordenadas

O endereço que o mecanismo do Google não encontrou foi substituído por um par de coordenadas. As coordenadas precisam estar nesse formato:

YY.YYYY, XX.XXXX

Sendo:
Y=Latitude
X=Longitude
Vírgula para separar os dois números
Ponto para ser o divisor de números inteiros e decimais
(no lugar da vírgula que usamos no Brasil)

Nesse caso as coordenadas foram coletadas no QGis e invertidas (formato: LAT,LONG) para que o Fusion Tables encontre o local exato.

Como conseguir as coordenadas

O par de coordenadas para o local desejado pode ser conseguido de várias formas. Na seção Mapear tem várias ferramentas para trabalhar com coordenadas. Abaixo as opções mais adequadas para este trabalho com o FusionTables:

1. Um site que fornece coordenadas

Este site dá a coordenada para o ponto que você escolhe no mapa: http://itouchmap.com/latlong.html

Veja outro exemplo usando este site:



Ao clicar no ponto (marker), no balão que se abre já é possível copiar as coordenadas no formato que o FusionTables entende:


Ou ainda, no mesmo site, logo abaixo do mapa tem as coordenadas no mesmo formato. Lembre-se de que para o FusionTables achar corretamente as coordenadas devem estar na sequência explicada acima.

2. Usando o Google Maps Antigo

Se você ainda é um sortudo que tem acesso ao Maps antigo – antes das modernas atualizações que o deixam mais lento e sem as opções que haviam antes – você pode usá-lo para conseguir coordenadas. No painel à esquerda, role até o final e selecione o link pequeno em azul: Labs do Google Maps (Maps Labs).


Na nova janela, ative as opções relativas a coordenadas (ou a LatLng).

Depois disso clique no mapa com o botão direito e selecione a opção "Posicionar marcador do LatLng"("Drop LatLng marker"). O balão que aparece contém as coordenadas em texto pronto para ser copiado (às vezes não está pronto, tem que trocar o divisor de decimal de vírgula por ponto)


3. Usando o QGis

QGis é um software open-source para trabalhar com mapas. Se você tem mapas em shapefile pode ser muito útil trabalhar com ele. Para instalar o plugin de coordenadas vá no menu Plugin > Manage Plugins e marque o Coordinate Capture:


O plugin habilita um painel; clicando no botão Start Capture habilita a ferramenta. Clique no mapa e a coordenada é salva no painel, e pode ser copiada. (Atenção: para o FusionTables, estas coordenadas devem ser invertidas para: Lat, Long).

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.