APROG - Civil
Excel
Técnicas de pesquisa de informação em tabelas
Instituto Superior de Engenharia do Porto
2000-2007
Elaborado por: António Silva (DEI-ISEP)
Pesquisa de Informação em Tabelas
O Excel possui uma série de funções que permitem efectuar pesquisas de
informação em tabelas como a que é exemplificada na figura 1.
Nomes
Manuel Silva
Joana Costa
João Tavares
Rui Baptista
Maria Santos
Dora Mesquita
Roberto Sousa
Sara Barros
Goreti Moura
Susana Meireles
Agostinho Sá
Mário Mendes
Nº Aluno
952001
980112
990520
990521
990522
990523
990524
990525
990526
990527
990528
990529
Nota prát. Nota Teór. Nota Final Faltas
12
15
8
5
9
15
11
10
18
7
10
9
13
12
10
3
6
15
13
9
15
11
11
13
12.5
13.5
9
4
7.5
15
12
9.5
16.5
9
10.5
11
3
1
3
7
5
1
0
2
2
3
4
2
Figura 1
Uma tabela ou lista é uma área da folha de cálculo englobando uma série de linhas
contendo informação similar e encabeçada por uma linha contendo os títulos de
cada coluna. Assim, cada linha dessa tabela funciona como um registo de uma base
de dados, constituindo cada célula um campo desse registo.
Para melhor compreensão das técnicas de pesquisa de informação em tabelas vamos
tratar separadamente a pesquisa exacta e pesquisa inexacta ou de gama.
Técnicas de pesquisa exacta
Função VLOOKUP
Sintaxe: Vlookup(Valor_a_procurar; Gama_onde_procurar; Num_Coluna; tipo_pesquisa)
Valor_a_procurar
Gama_onde_procurar
Num_coluna
Tipo_pesquisa
3
representa o valor (numérico ou alfanumérico) pelo qual
se pretende efectuar a pesquisa.
representa a tabela ou sub-tabela em que o valor vai ser
pesquisado.
é um número identificando a coluna da tabela em que o
resultado se encontra, caso a pesquisa obtenha sucesso.
é um valor booleano (0 ou 1, TRUE ou FALSE) que
especifica se a pesquisa deve ser exacta ou não (vamos,
para já considerar apenas o primeiro caso)
Para melhor entender o funcionamento desta função, encare o seguinte exemplo
(Figura 2):
Início da pesquisa
(1ª coluna da sub-tabela)
Encontrou o
nº de faltas!
1ª Fase
2ª Fase
Encontrou o
nº de aluno!
Figura 2
Pretende-se conhecer a informação relevante de um dado aluno (Nota Prática, Nota
Final e Nº de Faltas) identificado pelo seu Número de Aluno.
Para saber qual o número de faltas do aluno cujo número tenha sido introduzido na
célula G20, a função Vlookup pode ser utilizada do seguinte modo numa fórmula a
inserir na célula D23:
=Vlookup(D20; C5:G16;5;0)
O funcionamento desta função pode ser visto como dividido em duas fases:
Numa primeira fase a função procura na 1ª coluna da tabela (ou sub-tabela)
especificada no 2º parâmetro (C5:G16, neste caso) a informação contida na célula
indicada no 1º parâmetro (D20). Como o último parâmetro é zero, a pesquisa
efectuada será exacta. Caso o valor a pesquisar não seja encontrado na 1ªcoluna da
sub-tabela especificada, a função devolverá uma mensagem de erro (#N/A). Caso a
pesquisa tenha sucesso, a função terá encontrado nessa coluna da tabela uma célula
contendo esse valor. A linha em que essa célula se encontra é a informação essencial
para a segunda fase do processo.
Nessa segunda fase, a função vai apresentar o conteúdo da célula definida pela
intercepção da linha que foi encontrada na 1ª fase com a coluna especificada no 3º
parâmetro (4, neste caso). Repare-se que a numeração da coluna em que o resultado
se encontrará se refere à posição da coluna dentro da tabela (ou sub-tabela
seleccionada) e não à coluna da folha de cálculo (que seria neste caso a coluna G).
4
Uma limitação importante desta função é o facto de a pesquisa apenas poder ser
iniciada pela primeira coluna da tabela. É por isso que no exemplo acima foi
necessário seleccionar uma sub-tabela (C5:G16) deixando de fora a coluna B. Assim,
toda a informação contida nas colunas da tabela situadas à esquerda da coluna em
que se efectua a pesquisa ficará inacessível. No caso desta tabela, organizada como
está, não será possível com a função Vlookup saber qual o nome do aluno que
corresponde a um determinado Nº (ver as funções Match e Index, mais à frente).
A fórmula acima permite, pois, conhecer o número de faltas do aluno considerado.
Para poder fornecer a informação respeitante à nota prática e à nota final, nas
células D21 e D22 deverão existir as seguintes fórmulas, respectivamente:
=Vlookup(D20; C5:G16;2;0)
=Vlookup(D20; C5:G16;4;0)
[D21]
[D22]
Função Hlookup
Sintaxe: Hlookup(Valor_a_procurar; Gama_onde_procurar; Num_linha; tipo_pesquisa)
Valor_a_procurar
Gama_onde_procurar
Num_Linha
Tipo_pesquisa
representa o valor (numérico ou alfanumérico) pelo qual
se pretende efectuar a pesquisa.
representa a tabela ou sub-tabela em que o valor vai ser
pesquisado.
é um número identificando a linha da tabela em que o
resultado se encontra, caso a pesquisa obtenha sucesso.
é um valor booleano (0 ou 1, TRUE ou FALSE) que
especifica se a pesquisa deve ser exacta ou não (vamos,
para já considerar apenas o primeiro caso)
Como pode ser facilmente verificado, esta função é o equivalente ao Vlookup
“rodado de 90º”. Em vez de a pesquisa se efectuar na 1ª coluna da tabela, faz-se
pela 1ª linha da tabela (ou sub-tabela). Assim, como 3º parâmetro é fornecida a
informação de qual a linha em que se encontrará a informação pretendida caso a
pesquisa pela primeira linha tenha obtido sucesso.
No exemplo da Figura 3, pretende-se apresentar nas células E14 a E16 a informação
das compras efectuadas pelos 3 principais clientes duma empresa no mês indicado
na célula H14. A fórmula a colocar na célula E14 (as restantes são do mesmo tipo)
deveria ser:
=HLOOKUP($H$14;$D$3:$O$9;5;0)
A fórmula Hlookup irá procurar na 1ª linha da tabela D3:O9 (2º parâmetro) uma
célula com um conteúdo idêntico ao da célula H14 (1º parâmetro). Quando a
encontrar (neste caso na 7ª coluna da tabela) devolverá como resultado o conteúdo
da célula que se encontra no cruzamento da 7ª coluna (neste caso correspondendo à
coluna H da folha de cálculo) com a linha especificada no 3º parâmetro (a 5ª linha da
tabela), que corresponde ao cliente “Minas&Armadilhas”. O mesmo acontecerá para
as outras duas células, mudando apenas nas fórmulas o valor do 3º parâmetro.
5
Início da pesquisa
(1ª linha da sub-tabela)
Linha 5
Encontrou
as compras
de M&A!
Figura 3
No exemplo da Figura 4 pretende-se aumentar a flexibilidade da informação
prestada de modo a que seja possível apresentar na célula E24 a informação àcerca
do volume de compras do cliente especificado na célula E20 (através do seu código)
no mês especificado através da célula E22. Repare-se que, para maior facilidade, se
atribuiu a cada cliente um código que corresponde à posição da linha em que se
encontra na tabela (por ex., o cliente com o código 3, encontra-se na linha 3 da
tabela). A fórmula a colocar na célula E24 seria:
=HLOOKUP(E22;D3:O9;E20;0)
Repare-se que neste caso o 3º parâmetro (indicando o nº da linha em que se deve
encontrar a informação requerida) não é um número introduzido directamente na
fórmula. Em vez disso é inserida a referência da célula em que essa informação se
encontra.
Figura 4
6
É de referir, finalmente, que a função Hlookup sofre duma limitação idêntica à já
apontada à função Vlookup: a pesquisa deve obrigatoriamente ser efectuada a partir
da primeira linha da tabela seleccionada. Tal implica que, caso a pesquisa se deva
fazer por outra linha, haverá que seleccionar uma sub-tabela da tabela original de
modo a que essa linha passe a ser a primeira, deixando, no entanto, de fora algumas
linhas, cuja informação não poderá ser acedida por esta via.
Funções Match e Index
Sintaxe:
Match(Valor_a_procurar; Gama_onde_procurar; tipo_pesquisa)
Index(Tabela; Num_linha; Num_coluna)
Estas duas funções são muitas vezes utilizadas em conjunto quando se pretende
ultrapassar as limitações das funções Vlookup e Hlookup, ou seja, quando se
pretende, encontrar informação contida numa coluna à esquerda da coluna pela qual
a pesquisa é feita. Veja-se a tabela da figura seguinte:
Figura 5
Caso se pretenda saber qual a referência do produto com o custo mais elevado,
dever-se-á em 1º lugar obter essa informação usando a função Max
[ =MAX(C4:C14) ]. No entanto, não podemos pedir a uma função Vlookup que nos
pesquise na coluna do Custo este valor e nos diga o que se encontra no cruzamento
da linha encontrada com uma coluna que se encontre à esquerda da coluna de
pesquisa. A razão deste impedimento reside no facto da função Vlookup pesquisar
sempre pela 1ª coluna da tabela seleccionada (nem que para isso haja que
seleccionar uma sub-tabela, deixando de fora uma ou mais colunas da tabela
original).
7
Nestas situações, seremos forçados a recorrer a uma técnica diferente envolvendo o
uso combinado das funções Match e Index (Figura 6).
Max(C4:C14) -> 45
Match(45;C4:C15;0) -> 3
Index(B4:H14; 3; 1)
Figura 6
Pode-se considerar que a função Match executa o equivalente à 1ª fase do trabalho
da função Vlookup, i.é, procura o Valor_a_procurar dentro da gama de células
designada por Gama_onde_procurar (ver sintaxe na página anterior). Caso encontre
uma célula cujo conteúdo seja igual ao valor que procura, devolverá como resultado
a posição dessa célula dentro da gama especificada. Isto funciona quer para linhas
quer para colunas.
Essa informação deverá agora ser passada à função Index que é uma função que
genericamente, dada uma Tabela, nos diz qual o conteúdo da célula que se encontra
no cruzamento da linha na posição Num_linha com a coluna na posição
Num_coluna (mais uma vez, estas posições dizem respeito à Tabela seleccionada e
não às linhas ou colunas da folha de cálculo). Assim a informação requerida poderia
ser obtida colocando nas células F18, F19 e F20 respectivamente as fórmulas:
=INDEX(B4:H14;F20;1) =MAX(C4:C14)
=MATCH(F19;C4:C14;0)
[3]
[1]
[2]
Note que as linhas 19 e 20 da folha de cálculo representada na Figura 5 se
encontram escondidas.
A fórmula [1] encontra o maior valor contido na gama de células C4 a C14. A fórmula
[2] procura qual a linha dessa gama em que se encontra a célula contendo esse
8
valor. A fórmula [3] diz-nos qual o conteúdo da célula que se encontra nessa linha
mas na 1ª coluna da tabela.
Seria possível, no entanto, substituir as fórmulas acima por uma única. É o que se
passa no exemplo seguinte, em que se pretende uma fórmula única que nos dê a
informação de qual a referência do produto cujo valor tem uma maior peso
percentual no valor do “stock” (essa informação está presente na coluna H). Assim, a
fórmula a inserir na célula F22 para obter esse resultado seria a seguinte:
=INDEX(B4:H14;MATCH(MAX(H4:H14);H4:H14;0);1)
Esta fórmula corresponde a embutir umas nas outras as fórmulas seguintes:
MAX(H4:H14)
MATCH(____;H4:H14;0)
INDEX(B4:H14;__________;1)
Técnicas de pesquisa inexacta ou de gama
É , por vezes, conveniente fazer corresponder um dado valor numérico a uma
classificação qualitativa, ou seja, determinar a que gama de valores um dado valor
numérico pertence. Veja-se, por exemplo, o caso de transformar uma nota numérica
(de 0 a 20) numa classificação qualitativa (de Mau a Muito Bom).
Figura 7
Estes problemas podem ser resolvidos mediante o uso da função Vlookup mas
obrigam a alguns cuidados na construção da tabela. Por um lado, a coluna pela qual
9
a pesquisa será feita (no exemplo da figura 6, a gama de células B5 a B9) deverá
obrigatóriamente estar ordenada. Por outro lado, as classificações qualitativas que
se encontram na segunda coluna devem estar alinhadas (na mesma linha) com os
limiares inferiores das gamas respectivas (por exemplo, a classificação de Bom deve
estar na mesma linha que o limite inferior da gama correspondente, 14).
Para poder, no exemplo da Figura 6, apresentar na célula C14 a classificação
qualitativa correspondente à nota numérica presente na célula C13, deverá existir na
primeira célula a seguinte fórmula:
=VLOOKUP(C13;B5:C10;2;1)
Repare-se que o quarto parâmetro da função Vlookup é aqui ‘1’ (poderia igualmente
ser TRUE, porque o que é esperado é um valor Booleano). É este valor que especifica
que a pesquisa a efectuar pelo Vlookup não deve ser exacta. Neste caso, a função
Vlookup comportar-se-á da seguinte forma (descrita na Figura 7):
Gama
onde obter
o resultado
Gama
onde
procurar
Valor a
procurar
Figura 8
Irá pesquisar a primeira coluna da tabela considerada (2º Parâmetro) até encontrar
uma célula com um valor que seja maior ou igual ao valor a pesquisar. Caso o
encontre (e seja maior), recua uma linha e fornece como resultado o conteúdo da
célula que esteja no cruzamento dessa linha com a coluna especificada no 3º
parâmetro (a segunda coluna, neste exemplo). A figura 7 ilustra essa sequência de
operações. Caso o valor encontrado seja igual ao valor a pesquisar, comporta-se
como no caso da pesquisa exacta (0 ou FALSE no 4º parâmetro).
No entanto, a limitação inerente a usar a função Vlookup mantém-se também nestes
casos, ou seja, não é possível efectuar a pesquisa por outra coluna que não seja a
primeira. Assim, em casos como o representado na figura 8, se quisermos fazer
corresponder uma determinada mensagem contida numa coluna à esquerda a
valores dentro de uma determinada gama definida numa coluna à direita não
poderemos de facto usar a função Vlookup. Termos pois que recorrer à função
Lookup.
10
Início da pesquisa
Figura 9
Função Lookup
Sintaxe:
Lookup(Valor_a_procurar; Gama_onde_procurar; Gama_onde_obter_resultado)
Quando é encontrado um intervalo de valores dentro de Gama_onde_procurar em
que o Valor_a_procurar se possa encaixar, é fixada a linha correspondente ao limite
inferior desse intervalo e fornecido como resultado o conteúdo da célula que está na
mesma posição da Gama_onde_obter_resultado. De notar que os valores contidos
nas células da Gama_onde_procurar devem obrigatoriamente estar ordenados.
No exemplo da Figura 8, para obter a mensagem adequada ao valor introduzido na
célula F13, deverá ser colocada na célula F14 uma fórmula como a seguinte:
=LOOKUP(F13;F5:F8;E5:E8)
A função Lookup procurará na gama F5:F8 uma célula com um valor igual ou
superior ao conteúdo de F13. Quando a encontra, sendo maior, escolhe a posição da
linha anterior e usa essa mesma posição para determinar em E5:E8 qual a célula cujo
conteúdo deverá ser devolvido como resultado. Como se vê, a função Lookup não
sofre da limitação atingindo a função Vlookup, já que é possível especificar
livremente qual a coluna em que a pesquisa se efectuará e qual a coluna em que o
resultado se encontrará. Mas, por outro lado, esta função pode apenas ser usada em
pesquisa inexacta por exigir que o campo a pesquisar esteja ordenado.
11
Conclusão
Em forma de conclusão e para auxiliar na escolha da função adequada a cada
situação, procurou-se na tabela abaixo sistematizar os critérios de selecção da
função (ou funções) a utilizar.
Pesquisa
Exacta
Inexacta
Busca por 1ª coluna
Vlookup (False) Vlookup (True)
Lookup
1ª linha
Hlookup (False) Hlookup (True)
Lookup
outras colunas Match + Index
Lookup
outras linhas
Match + Index
Lookup
ISEP, Março de 2000 / Dezembro de 2007
12
António Silva