Unidade 16: Funções de Busca e Referência

Unidade 16: Funções de Busca e
Referência
16.0 Introdução
Uma das características mais importantes do Excel é sua habilidade de buscar um valor
específico em um grande número de células contendo dados e retornar um valor daquela
linha (ou coluna). A forma mais fácil de fazer isso é utilizar as funções PROCV e PROCH. Se
você faz muita análise em grandes bancos de dados provavelmente valerá a pena você
aprender a combinação de funções um pouco mais complicadas e muito mais poderosas
chamadas ÍNDICE e CORRESP.
16.1 PROCV
A função PROCV busca por um valor específico na coluna mais a esquerda de uma tabela de
dados e retorna um valor daquela linha em uma das colunas à direita.
Considere os seguintes dados contendo a data de lançamento de alguns sites famosos:
Suponha que a lista tenha milhares de linhas. Suponha que você só esteja interessado nas
quatro redes sociais. Você poderia criar uma pequena lista com apenas os sites que você
© 2010 Excel with Business
1
Unidade 16: Funções de Busca e Referência
está interessado (coluna D) e então utilizar o PROCV para buscá-los na enorme tabela e
retornar as datas de lançamento correspondentes. Escreva o seguinte na célula E2:
=PROCV (D2;A:B;2;FALSE)
resultando:
Onde:




D2 é o valor que estamos buscando. Note que escrever “Facebook.com” para este
argumento teria dado o mesmo resultado, mas não seriamos capazes de copiar a
fórmula para outras linhas sem mudá-la.
A:B é o intervalo em que estamos buscando. Note que a ausência de números nessa
referência significa que a busca é feita em toda a coluna.
2 é o número da coluna do valor que queremos obter. Então, neste caso, a coluna na
qual estamos interessados (Ano de
lançamento) é a segunda, então
Aviso! Não recomendamos correspondências
colocaremos “2”.
inexatas a menos que você tenha plena certeza
FALSO indica que estamos
do que você está fazendo. Isto é, em parte, por
buscando por uma correspondência
que os dados que entram podem ser
exata. Deixar isto em branco ou
manipulados para uma forma precisa e
escrever VERDADEIRO faria o Excel
parcialmente porque deixar o Excel adivinhar
buscar pela correspondência mais
uma correspondência pode trazer resultados não
próxima.
desejados e incertos.
© 2010 Excel with Business
2
Unidade 16: Funções de Busca e Referência
Limitações

Os dados que você está buscando devem estar na coluna mais a esquerda do intervalo
que você selecionar
16.2 PROCH
Você também pode usar o PROCH para dados organizados horizontalmente. Suponha que
desejássemos empreender o mesmo exercício para os mesmos dados, mas que nossos
dados estivessem organizados horizontalmente.
Então, ao colocar os sites nos quais estamos interessados nas células B4:E4 e então
colocando a seguinte fórmula na célula B5:
=PROCH (B4;B1:AN2;2;FALSE)
Obteremos:
De fato, seria melhor (pelo menos visualmente)
neste tipo de exemplo reformatar o texto
verticalmente e utilizar um PROCV. Se você se
encontrar utilizando o PROCH vale a pena se
perguntar se os dados estão dispostos da
melhor forma.
Já que muitos bancos de
dados exibem os dados
dispostos em colunas para
baixo em vez de linhas, a
© 2010 Excel with Business
Você pode converter dados organizados
verticalmente
para
dados
organizados
horizontalmente através de uma Troca de
Posição Especial:
Página Inicial > Colar > Colar Especial >
Transpor
3
Unidade 16: Funções de Busca e Referência
PROCV é a função mais útil das duas.
As limitações da PROCV também se aplicam a PROCH também.
16.3 CORRESP
A função CORRESP é na verdade uma função de busca que não retorna nenhum dado. Ela
apenas lhe diz se encontrou o valor que você pediu que fosse buscado, e, caso o encontre,
diz onde o encontrou. Existem três argumentos em uma função CORRESP: o que você está
buscando, onde você está buscando e o tipo de correspondência que você está tentando
fazer. Utilizando nosso exemplo de páginas de internet populares, suponha que estamos
buscando por mais outras duas redes sociais (Bebo e Friendster) e queremos saber se elas
aparecem em nossa grande base de dados. Digite na célula E2:
=CORRESP (D2;A:A;0)
E você obterá algo assim:
Onde:



D2 é o que estamos buscando (na linha 2 o Facebook.com)
A:A é a coluna na qual estamos buscando
0 significa que estamos buscando por uma correspondência exata (assim com o PROCV e
PROCH, é uma boa ideia utilizar a forma de correspondência exata quando você está
esperando uma correspondência exata, se você utilizar as formas aproximadas de
correspondência, os dados devem ser classificados de acordo)
A tabela resultante é:
© 2010 Excel with Business
4
Unidade 16: Funções de Busca e Referência
O “4” na célula E2 exibe a posição onde o Facebook.com aparece na coluna A (é o quarto
item no intervalo que selecionamos). Note que, neste caso, isto inclui o cabeçalho. Se
quiséssemos remover isso teríamos digitado =A2:A1048576 em vez de A:A. Algo semelhante
com 6, 13, 26. Os “#N/A”s nas células E6 e E7 indicam que nem o “Bebo.com” nem o
“Friendster.com” estão na vasta lista.
16.4 ÍNDICE-CORRESP
A combinação ÍNDICE-CORRESP não é algo simples, mas vale muito a pena reservar um
tempo para aprendê-la particularmente se você trabalha com muitos dados
frequentemente. A ÍNDICE-CORRESP tem algumas vantagens sobre as PROCVs:


Ela pode ser utilizada em dados organizados em colunas ou linhas
Você pode encontrar uma coluna com base na correspondência do cabeçalho em vez de
ter que escolher um ‘número’ de coluna em particular
ÍNDICE
A função ÍNDICE é bem simples e também pode ser utilizada por si só. Ela possui dois
argumentos: a lista que você deseja que o Excel busque e o número que você deseja obter
dessa lista. Então, caso você quisesse retornar a décima quarta página de internet da lista
abaixo, você escreveria:
= ÍNDICE(A2:A40;14)
© 2010 Excel with Business
5
Unidade 16: Funções de Busca e Referência
E assim a “ESPN.go.com” seria retornada.
Suponha que você tem outra base de dados com informações de páginas de internet
baseadas na classificação dos usuários de várias geografias, tudo isso em ordem alfabética:
Você deseja consolidar as duas bases de dados em apenas uma base de dados mestre
contendo toda essa informação. Primeiro você precisa decidir se quer adicionar a
informação de classificação à planilha ou vice versa. Isto pode depender de que ordem e
formato tem precedência. Digamos que vamos adicionar dados de classificação aos dados
de lançamento:
© 2010 Excel with Business
6
Unidade 16: Funções de Busca e Referência
Então, nessa base de dados, digitaremos:
=ÍNDICE(Ratings!B:B;CORRESP (Launch!A2;Ratings!A:A;0)
Ratings=Classificação
Launch=Lançamento
Onde:



A parte amarela da fórmula indica onde nós pedimos que o Excel fizesse a busca (coluna
B da planilha de classificações).
A parte verde retorna a posição na coluna de classificações A, (onde os sites estão
listados em ordem alfabética) onde o Google.com aparece. Digamos que este é o
centésimo lugar.
A ÍNDICE, portanto, sabe retornar a centésima classificação na coluna B da planilha de
classificações.
Agora, para obter TODOS os dados da planilha de classificações, vamos precisar copiar uma
versão da fórmula na direita. Já que sempre vamos querer utilizar uma correspondência da
coluna A da planilha de lançamento (mas nem sempre da mesma linha) na coluna A da
planilha de classificações, podemos corrigir algumas referências de células (veja a Unidade
11: Referências de Célula) para:
=ÍNDICE (Ratings!B:B;CORRESP (Launch!$A2;Ratings!$A:$A;0))
Assim, ao estender a fórmula para baixo e para a esquerda obteremos:
© 2010 Excel with Business
7
Unidade 16: Funções de Busca e Referência
Cuidado
Assim como qualquer outra fórmula e função do Excel, é fácil inserir valores incorretos (e é
complicado localizar o erro em alguns casos). Você deve, portanto, tomar os seguintes
cuidados:



Fazer verificações constantes. No exemplo acima, você talvez queira escolher uma
célula aleatoriamente, digamos a F15, para verificar se o Go.com tinha a classificação 9.8
na planilha de classificações.
Classificar dados. Certifique-se de criar funções ÍNDICE-CORRESP de forma que elas
ainda retornem o resultado correto ainda que a ordem de classificação dos dados seja
modificada.
Manter dados precisos. Enquanto defendemos o uso do tipo de correspondência exata,
isto requer que os dados entre os quais você está fazendo a busca correspondam
exatamente ao valor que você está buscando. Às vezes só o que falta é uma operação
para alcançar isto. Por exemplo, se todas as planilhas de classificação de páginas da
internet possuíssem “www.”, nós talvez tivéssemos que adicionar esse “www.” às
entradas correspondentes na planilha de lançamento (usando o &) ou teríamos de
removê-los da planilha de classificação (utilizando o Localizar e substituir ou o
ESQUERDA E NÚM.CARACT).
© 2010 Excel with Business
8
Unidade 16: Funções de Busca e Referência