Organizando dados: Funções de procura e Tabela dinâmica

Existem duas ferramentas em softwares para organização de dados (Calc da Libre Office, Excel da Microsoft e provavelmente em outros também) muito úteis e um tanto subutilizadas. A tabela dinâmica é relativamente bem conhecida e às vezes pessoas falam com olhar surpreso, “Como assim você não sabe usar a tabela dinâmica?”. Funções de procura são menos utilizadas, e às vezes pessoas me falam com olhar surpreso, “Nooooossa, não sabia que dá pra fazer isso, que lindo!”. Tá, não com exatamente essas palavras, e acho que aconteceu talvez duas vezes nos últimos três anos, mas enfim. rs Se você quer aprender a usar essas funções – éis a oportunidade que você tanto esperava! 🙂

Estou usando o Calc 5.1.6.2 (Libre Office) neste exemplo, porque não uso o Excel desde 2012 e porque o Calc 5.2 às vezes fica muito lento no meu computador (Linux também tem suas peculiaridades, rs). Mas essas funções estão também disponíveis no Excel, sem grandes diferenças.

Parte 1. Procura vertical – PROCV ou VLOOKUP

Digamos que você tenha uma planilha com a abundância de diferentes espécies em diferentes sítios*:

VLOOKUP1.pngE digamos que você tenha uma planilha relacionando cada espécie com o seu grau de ameaça, usando as categorias da IUCN (LC – least concern, NT – nearly threatened, VU – vulnerable, EN – endangered, CR – critically endangered):

VLOOKUP2.png

E digamos que você queria saber quantas espécies ameaçadas há em cada lugar. Para isso, primeiro precisaríamos, digamos, juntar as duas planilhas, adicionaríamos, na primeira planilha, uma coluna informando o grau de ameaça de cada espécie. Para fazer isso manualmente, o procedimento seria o seguinte:

  1. Olhar para a primeira linha abaixo do cabeçalho (linha 1);
  2. Olhar o nome da espécie na primeira planilha (Monoceros ghealdanus) ;
  3. Procurar essa espécie na segunda planilha;
  4. Olhar o grau de ameaça dessa espécie (EN);
  5. Adicionar esse grau de ameaça na primeira planilha;
  6. Repetir os passos 2-5 para a linha 3, linha 4, …, linha 26;
  7. Ficar com sono e raiva e pensar que precisa haver um jeito mais rápido de fazer isso, sangue e cinzas! 🙂

A boa notícia é que há um jeito mais rápido de fazer isso, e esse jeito consiste simplesmente em mandar o software (Calc ou Excel) fazer esses passos pra você. São as funções de procura – têm esse nome porque a função olha o nome em uma planilha e procura ele em outra planilha (ou outra parte da mesma planilha). Pode ser uma espécie e seu grau de ameaça; ou pode ser um sítio estudado e a quantidade de floresta nele; ou, bom, qualquer coisa que você queira. Se procuramos em coluna – que é como sempre usei essa função – isso é uma procura vertical, porque colunas estão na vertical então, né, parece até meio óbvio mas acho que demorei uns anos perceber a relação... Daí o nome, PROCV (PROCura Vertical) em português ou VLOOKUP (Vertical LOOKUP) em inglês.

Primeiro, selecionamos a célula onde queremos que a informação apareça e começamos a digitar a função nela. Para colocar uma fórmula ou função no Calc ou no Excel, usamos o sinal de igual (=), depois o nome da função (VLOOKUP) e depois abrimos parênteses, onde vão os argumentos da função. No caso do VLOOKUP, são quatro argumentos. O primeiro argumento é a coisa que queremos procurar – no caso, o nome da espécie, que está na célula B2. Então fica =VLOOKUP(B2,

VLOOKUP3.png

(Importante: se seu Calc ou Excel estiver em português, talvez os argumentos sejam separados por ponto e vírgula (;). Eu recomendo deixar ele sempre em inglês, facilita pra quando for salvar dados pra usar em R etc.)

O segundo argumento é onde queremos procurar essa informação – no caso, é na segunda planilha, células A1 até B8. Podemos mudar de planilha usando o mouse ou usar as teclas de atalho CTRL+PAGE DOWN (CTRL+PAGE UP pra planilha anterior), e selecionar os dados com o mouse ou com o teclado. É importante que a informação procurada esteja na primeira coluna selecionada, senão o comando não funciona:

VLOOKUP4.png

Aqui eu estou na segunda planilha, mas ainda estou editando a fórmula da primeira planinha, como podem ver acima. Eu chamei a planilha de Especies. Então a informação “Especies.A1:B8” significa que estamos procurando nas colunas A e B, linhas 1 até 8, da planilha Especies. Sim, talvez pareça complicado. Não, não sei explicar melhor 🙂

Depois do Especies.A1:B8 aparece o número 2. Esse número é a coluna cujo valor vamos pegar. O Calc (ou Excel, o princípio é o mesmo) procura a informação na primeira coluna selecionada, e retorna o valor da 2a ou 3a ou qualquer coluna que queiramos, entre as selecionadas. Em língua de gente, estamos dizendo “Querido Calc, procure a informação da célula B2 da primeira planilha entre as linhas 1 e 8 da coluna A da segunda planilha e, caso encontre, retorno o valor correspondente que está na mesma linha, na segunda coluna.” E o Calc vai responder “Sim senhor, senhor! Achei, Monoceros ghealdanus, o valor correspondente é EN! Senhor!”. Caso essa informação estivesse na terceira coluna, colocaríamos um 3 no lugar do 2, etc.

E finalmente, depois do 2, existe um 0. Este zero diz que os dados não estão ordenados e que é pro programa procurar o valor exato. Ou seja, se tiver um Monoceros ghealdanu, ele não vai retornar nada. Se quisermos valores parciais, e a coluna estiver em ordem crescente, colocaríamos 1 no lugar do 0. Mas não consigo pensar numa situação ecológica em que queiramos isso, então você pode simplesmente decorar que o quarto e último número de uma função VLOOKUP é 0.

A fórmula final então fica =VLOOKUP(B2, Especies.A1:B8,2,0)

Primeiro qual valor procurar (no caso, célula B2), depois onde procurar ele (na planilha Especies, células A1 até B8), depois qual valor retornar (o da segunda coluna), e depois um 0 mostrando que os dados não estão ordenados e queremos a correspondência exata.

Apertamos ENTER, voltamos pra primeira planilha, e voilà:

VLOOKUP5.png

Pronto!

Bom, quase. Precisamos repetir isso pras outras células. O jeito mais fácil é levar o mouse pro canto direito inferior da célula selecionada (ele vai virar uma cruzinha) e clicar duas vezes, isso vai repetir a fórmula pra células abaixo. Só que ele vai repetir a fórmula mudando o seu conteúdo – porque na terceira linha, ele vai procurar a célula B3, e na quarta, a célula B4, etc. O que é muito bom! Só que ele vai mudar também o segundo argumento, e ao invés de procurar da célula A1 até a B8, vai da célula A2 até a B9; e depois da A3 até a B10; e assim por diante. Que é o que nós não queremos! Então adicionamos o cifrão, $, antes do número da linha que não queremos que mude:

=VLOOKUP(B2, Especies.A$1:B$8,2,0)

VLOOKUP6.png

Fazendo isso, os valores procurados irão mudar (célula B3, B4 etc) mas a região de busca permanece sempre a mesma (Especies.A1 até Especies.B8). Sim, parece complicado. Não, não sei explicar melhor. 🙂

E éis o resultado:

VLOOKUP7.png

Reparem que apareceram alguns #N/A aí. Isso é porque a espécie Monoceros montanus não está na lista de espécies ameaçadas. #N/A significado “valor não encontrado” ou “cara, foi mal aí, mas esse valor que você me pediu, ele não tá nessa planilha não, não posso fazer mais nada.”

Feito isso, eu gosto de substituir as fórmulas pelos valores, assim eu posso, por exemplo, copiar essa planilha pra outro arquivo sem perder informações; e também valores brutos deixam o arquivo mais leve do que fórmulas (acho). Para isso, eu seleciono a coluna com o grau de ameaça, copia ele, e colo especial (em cima dela mesma), colando apenas valores. No Calc, o atalho para colar especial é CTRL+SHIFT+V.

VLOOKUP8.png

Apertamos ENTER, e estamos com a nossa planilha pronta! Com um pouco de prática, isso tudo gasta uns cinco segundos pra fazer (bem menos do que escrever este post, rs. Ou fazer manualmente!)

Parte 2. Tabela Dinâmica ou Pivot Table

Agora que nós temos na mesma planilha o nome da espécie e o grau de ameaça, podemos calcular, por exemplo, o número de espécies mais e menos ameaçadas em cada sítio. Podemos fazer isso manualmente – mas novamente, há uma forma simples de fazer isso no Calc ou no Excel, algo análogo às funções aggregate e table do R.

Selecionamos os dados de interesse, vamos em Inserir (Insert), Tabela Dinâmica (Pivot Table) (em versões anteriores essa opção pode estar em Dados, não em Inserir):

VLOOKUP9.png

Depois definimos como queremos que seja o nosso resultado – eu quero que o sítio vá nas linhas (Row Fields) e o grau de ameaça nas colunas (Column Fields); a abundância vai nos valores das células (Data Fields), mas neste caso poderia ser o grau de ameaça ou outra coisa. E finalmente, clico duas vezes no Data Fields pra definir a função a ser usada – no caso, contagem, que conta o número de vezes que cada combinação de sítio e grau de ameaça aparece:

VLOOKUP9b.png

O resultado:

VLOOKUP9c.png

O maior número de espécies está em Manetheren**, e vemos rapidamente quais lugares contém espécies criticamente ameaçadas, ameaçadas etc; olhando pra isso, eu diria que ações de conservação são mais necessárias em Mayene e Shienar e Tar Valon está mais de boa. Isso está sujeito a debates. (E bom, os números foram aleatórios mesmo, rs).

Se quiséssemos saber o número total de indivíduos ameaçados, usaríamos a função Sum ao invés de Count. E assim vai.

Enfim… Tabela dinâmica ajuda muito a entenderemos, organizarmos e analizarmos os nossos dados; e funções de procura são sensacionais para quando precisamos relacionar informações que estão em planilha distintas. Eu demorei algum tempo pra aprender a usar de verdade, então recomendo prática, porque vale a pena saber usar essas funções bem. Eu pessoalmente muitas vezes prefiro organizar dados em Calc e importar pro R os dados já organizados; a não ser que seja muita coisa e dê muito trabalho em Calc, aí eu prefiro fazer no R direto, e salvar a planilha organizada. Pra coisas relativamente simples, como essa que exemplifiquei aqui, Calc ou Excel dão conta do recado tranquilamente. 🙂

*E digamos que a pessoa digitou o nome de alguns lugares errado – sim gente, isso acontece nas suas planilhas de campo e deixa eu fingir que foi intencional, tá? Tai’shar Manetheren!

**Talvez por Manetheren estar num passado distante, então deve ter sido feita uma viagem temporal pra ver isso… Ninguém se importa com anacronismos aqui 🙂

 

Anúncios

2 pensamentos sobre “Organizando dados: Funções de procura e Tabela dinâmica

  1. Pingback: Organizando dados: de dados brutos a dados para análise, em R – Mais Um Blog de Ecologia e Estatística

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s