Análise de dados do Excel - funções de pesquisa

Você pode usar as funções do Excel para -

Encontre valores em um intervalo de dados - VLOOKUP e HLOOKUP
Obtenha um valor ou a referência a um valor de uma tabela ou intervalo - INDEX
Obtenha a posição relativa de um item especificado em um intervalo de células - MATCH

Você também pode combinar essas funções para obter os resultados necessários com base nas entradas que você tem.

Usando a função VLOOKUP

A sintaxe da função VLOOKUP é

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Onde

lookup_value- é o valor que você deseja pesquisar. Lookup_value pode ser um valor ou uma referência a uma célula. Lookup_value deve estar na primeira coluna do intervalo de células que você especificar em table_array
table_array- é o intervalo de células em que VLOOKUP pesquisará o valor_procurado e o valor de retorno. table_array deve conter
- o lookup_value na primeira coluna, e
- o valor de retorno que você deseja encontrar
  
  Note- A primeira coluna contendo lookup_value pode ser classificada em ordem crescente ou não. No entanto, o resultado será baseado na ordem desta coluna.
col_index_num- é o número da coluna em table_array que contém o valor de retorno. Os números começam com 1 para a coluna mais à esquerda da tabela-array
range_lookup- é um valor lógico opcional que especifica se você deseja que VLOOKUP encontre uma correspondência exata ou aproximada. range_lookup pode ser
- omitido, caso em que é assumido como TRUE e VLOOKUP tenta encontrar uma correspondência aproximada
- TRUE, nesse caso VLOOKUP tenta encontrar uma correspondência aproximada. Em outras palavras, se uma correspondência exata não for encontrada, o próximo maior valor menor que lookup_value é retornado
- FALSE, caso em que VLOOKUP tenta encontrar uma correspondência exata
- 1, caso em que é assumido como TRUE e PROCV tenta encontrar uma correspondência aproximada
- 0, caso em que é considerado FALSO e PROCV tenta encontrar uma correspondência exata

Note- Se range_lookup for omitido ou TRUE ou 1, VLOOKUP funcionará corretamente apenas quando a primeira coluna em table_array for classificada em ordem crescente. Caso contrário, isso pode resultar em valores incorretos. Nesse caso, use FALSE para range_lookup.

Usando a função VLOOKUP com range_lookup TRUE

Considere uma lista de notas dos alunos. Você pode obter as notas correspondentes com VLOOKUP de uma matriz contendo os intervalos de notas e a categoria de aprovação.

table_array -

Observe que as marcas da primeira coluna com base nas quais as notas são obtidas são classificadas em ordem crescente. Portanto, usando TRUE para o argumento range_lookup, você pode obter uma correspondência aproximada que é necessária.

Nomeie esta matriz como Grades.

É uma boa prática nomear arrays dessa maneira, para que você não precise se lembrar dos intervalos de células. Agora, você está pronto para procurar a nota para a lista de notas que você tem da seguinte maneira -

Como você pode observar,

col_index_num - indica que a coluna do valor de retorno em table_array é 2
a range_lookup é verdade
- A primeira coluna que contém o valor de pesquisa nas notas table_array está em ordem crescente. Portanto, os resultados estarão corretos.
- Você também pode obter o valor de retorno para correspondências aproximadas. ou seja, VLOOKUP calcula da seguinte forma -

Marcas	Categoria de aprovação
<35	Falhou
> = 35 e <50	Terceira classe
> = 50 e <60	Segunda classe
> = 60 e <75	Primeira classe
> = 75	Primeira classe com distinção

Você obterá os seguintes resultados -

Usando a função VLOOKUP com range_lookup FALSE

Considere uma lista de produtos contendo a ID do produto e o preço de cada um dos produtos. O ID do produto e o preço serão adicionados ao final da lista sempre que um novo produto for lançado. Isso significa que os IDs do produto não precisam estar em ordem crescente. A lista de produtos pode ser conforme mostrado abaixo -

table_array -

Nomeie essa matriz como ProductInfo.

Você pode obter o preço de um produto dado o ID do produto com a função VLOOKUP, pois o ID do produto está na primeira coluna. O preço está na coluna 3 e, portanto, col_index_ num deve ser 3.

Use a função VLOOKUP com range_lookup como TRUE
Use a função VLOOKUP com range_lookup como FALSE

A resposta correta da matriz ProductInfo é 171,65. Você pode verificar os resultados.

Você observa que você tem -

O resultado correto quando range_lookup é FALSE, e
Um resultado errado quando range_lookup é TRUE.

Isso ocorre porque a primeira coluna na matriz ProductInfo não é classificada em ordem crescente. Portanto, lembre-se de usar FALSE sempre que os dados não forem classificados.

Usando a função HLOOKUP

Você pode usar HLOOKUP função se os dados estiverem em linhas em vez de colunas.

Exemplo

Tomemos o exemplo das informações do produto. Suponha que a matriz tenha a seguinte aparência -

Nomeie este Array ProductRange. Você pode encontrar o preço de um produto dado o ID do produto com a função HLOOKUP.

A sintaxe da função HLOOKUP é

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Onde

lookup_value - é o valor a ser encontrado na primeira linha da tabela
table_array - é uma tabela de informações na qual os dados são pesquisados
row_index_num - é o número da linha em table_array a partir do qual o valor correspondente será retornado
range_lookup - é um valor lógico que especifica se você deseja que HLOOKUP encontre uma correspondência exata ou aproximada
range_lookup pode ser
- omitido, caso em que é assumido como TRUE e HLOOKUP tenta encontrar uma correspondência aproximada
- TRUE, caso em que HLOOKUP tenta encontrar uma correspondência aproximada. Em outras palavras, se uma correspondência exata não for encontrada, o próximo maior valor menor que lookup_value é retornado
- FALSE, caso em que HLOOKUP tenta encontrar uma correspondência exata
- 1, caso em que é assumido como TRUE e HLOOKUP tenta encontrar uma correspondência aproximada
- 0, caso em que é considerado FALSE e HLOOKUP tenta encontrar uma correspondência exata

Note- Se range_lookup for Omitted ou TRUE ou 1, HLOOKUP funcionará corretamente apenas quando a primeira coluna em table_array for classificada em ordem crescente. Caso contrário, isso pode resultar em valores incorretos. Nesse caso, use FALSE para range_lookup.

Usando a função HLOOKUP com range_lookup FALSE

Você pode obter o preço de um produto dado o ID do produto com a função HLOOKUP, pois o ID do produto está na primeira linha. O preço está na linha 3 e, portanto, row_index_ num deve ser 3.

Use a função HLOOKUP com range_lookup como TRUE.
Use a função HLOOKUP com range_lookup como FALSE.

A resposta correta da matriz ProductRange é 171,65. Você pode verificar os resultados.

Você observa que, como no caso de VLOOKUP, você tem

O resultado correto quando range_lookup é FALSE, e
Um resultado errado quando range_lookup é TRUE.

Isso ocorre porque a primeira linha na matriz ProductRange não é classificada em ordem crescente. Portanto, lembre-se de usar FALSE sempre que os dados não forem classificados.

Usando a função HLOOKUP com range_lookup TRUE

Considere o exemplo de notas de alunos usadas em VLOOKUP. Suponha que você tenha os dados em linhas em vez de colunas, conforme mostrado na tabela abaixo -

table_array -

Nomeie essa matriz como GradesRange.

Observe que as marcas da primeira linha com base nas quais as notas são obtidas são classificadas em ordem crescente. Portanto, usando HLOOKUP com TRUE para o argumento range_lookup, você pode obter as notas com correspondência aproximada e é isso que é necessário.

Como você pode observar,

row_index_num - indica que a coluna do valor de retorno em table_array é 2
a range_lookup é verdade
- A primeira coluna que contém o valor de pesquisa em table_array Grades está em ordem crescente. Portanto, os resultados estarão corretos.
- Você também pode obter o valor de retorno para correspondências aproximadas. ou seja, HLOOKUP calcula da seguinte forma -

Marcas	<35	> = 35 e <50	> = 50 e <60	> = 60 e <75	> = 75
Categoria de aprovação	Falhou	Terceira classe	Segunda classe	Primeira classe	Primeira classe com distinção

Você obterá os seguintes resultados -

Usando a função INDEX

Quando você tem uma matriz de dados, pode recuperar um valor na matriz especificando o número da linha e o número da coluna desse valor na matriz.

Considere os dados de vendas a seguir, nos quais você encontra as vendas em cada uma das regiões Norte, Sul, Leste e Oeste pelos vendedores listados.

Nomeie a matriz como SalesData.

Usando a função INDEX, você pode encontrar -

As vendas de qualquer um dos vendedores em uma determinada região.
Vendas totais em uma região por todos os vendedores.
Vendas totais por um vendedor em todas as regiões.

Você obterá os seguintes resultados -

Suponha que você não saiba os números das linhas dos vendedores e os números das colunas das regiões. Em seguida, você precisa encontrar o número da linha e o número da coluna antes de recuperar o valor com a função de índice.

Você pode fazer isso com a função MATCH, conforme explicado na próxima seção.