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á fez alguma dessas perguntas:
• 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?
Via de regra, isso é um assunto para programas de banco de dados (Access, MySQL). Mas pra quem isso é grego e pra coisas simples, 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).

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

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 duplicar 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:
   • Selecionar os valores
   • Copiar
   • Colar Especial (Paste special). Opção disponível com o botão direito do mouse ou no menu Edit.
   • Selecinar 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)

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:

Excel: ordernar listas



Ordenar valores em ordem crescente/decrescente com o Filtro.

0. Certificar-se de ter um cabeçalho
1. Selecionar todas as células (se selecionar só algumas, a ordem vai bagunçar depois)
2. Aba Dados (Data) > Filtro (Filter). Atalho no Mac: Cmd + Shift + F
3. Usar 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)


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

Excel: contas simples



Divisão, arredondamento e soma.

Excel: arredondando números grandes



Como arredondar números com Excel

Arrendondando/simplificando números que são inteiros (mas muito grandes).
1.300.000.000 > 1,3 bilhões

1. Criar uma coluna extra
2. Escrever o cabeçalho novo (XXX em milhões/bilhões/milhares…)
3. Digitar fórmula na 1a linha:
   • Sinal de igual (=)
   • Clicar na célula com o valor inteiro
   • Sinal de barra (/) para fazer a divisão
   • Enter pra finalizar
4. Arrastar a fórmula até onde for preciso (pelo quadradinho no canto inferior direito da célula). Se der dois cliques a fórmula vai até o final automaticamente.
5. Ajustar 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).

[opcional]
Eliminar a fórmula para deixar só os valores
(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):
   • Selecionar os valores
   • Copiar
   • Colar Especial (Paste special). Opção disponível clicando com o botão direito ou no menu Edit.
   • Selecinar 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.