Encontrar nada em sua planilha excel com funções de pesquisa

Na maioria das vezes, encontrar uma célula em uma planilha do Excel é bastante fácil- se você não pode apenas digitalizar através das linhas e colunas para ele, você pode usar CTRL + F para procurá-lo. Mas se você estiver trabalhando com um realmente grande folha de cálculo, ele pode salvar um monte de tempo para usar uma destas quatro funções de pesquisa. Não importa o tamanho do seu documento Excel, eles vão ser muito mais eficiente.

A função PROCV

Esta função permite que você especifique uma coluna e um valor, e irá retornar um valor da linha correspondente de uma coluna diferente (se isso não faz sentido, ele vai se tornar claro em um momento). Dois exemplos em que você pode fazer isso estão olhando para cima o sobrenome de um empregado pelo seu número de empregado ou encontrar um número de telefone, especificando um sobrenome. Aqui está a sintaxe da função:

= PROCV ([lookup_value], [table_array], [núm_índice_coluna], [range_lookup])

O [lookup_value] é o pedaço de informação que você já ter- por exemplo, se você precisa saber o estado de uma cidade está em, seria o nome da cidade. [Table_array] permite especificar as células em que a função vai olhar para a pesquisa e retornar valores. Ao selecionar sua faixa, certifique-se que a primeira coluna incluído no seu conjunto é o que irá incluir o seu valor de pesquisa! Isto é crucial. [Núm_índice_coluna] é o número da coluna que contém o valor de retorno.

[Range_lookup] é um argumento opcional, e leva 1 ou 0. Se você inserir 1 ou omitir esse argumento, a função procura o valor digitado ou a próxima menor número. Então, na imagem abaixo, um PROCV procurando uma pontuação SAT de 652 retornará 646, como é o número mais próximo na lista que é menos de 652, e [range_lookup] padrão para 1.

vlookup-arredondamento

Vamos dar uma olhada em como podemos usar isso. Como eu tenho em meus artigos sobre operadores booleanos em Excel e contando avançada e adicionando, Eu vou estar usando uma planilha que eu gerado com generatedata.com. Ele contém números de identificação, nome e sobrenome, cidade, estado, e SAT. Vamos dizer que eu quero encontrar a pontuação SAT de uma pessoa com o apelido "Winters". PROCV torna mais fácil. Aqui está a fórmula que usaria:

= PROCV ( "Winters", C2: F101, 4, 0)

Porque os resultados do SAT são a quarta coluna sobre da última coluna nome, eu usei 4 para o argumento de índice de coluna. Note-se que quando você está olhando para o texto, ajustando [range_lookup] a 0 é uma boa idéia-sem ele, você pode obter maus resultados. Aqui está o que Excel dá-nos:

vlookup-excel

Voltou 651, a pontuação SAT pertencente ao estudante chamado Kennedy Winters, que está na linha 92 (exibido na inserção acima). Ele teria levado muito mais tempo para percorrer procurando o nome do que ele fez para digitar rapidamente a sintaxe!

PROCV também pode ser bastante útil se você estiver usando o Excel para fazer seus impostos.

Notas sobre PROCV

Algumas coisas são boas para se lembrar quando você está usando PROCV. Em primeiro lugar, como já referi anteriormente, certifique-se que a primeira coluna em sua faixa é a que inclui o seu valor de pesquisa. Se não estiver na primeira coluna, a função irá retornar resultados incorretos. Se as suas colunas são bem organizados, isso não deve ser um problema.

A segunda coisa a ter em mente é que PROCV só vai retornar um valor. Se tivéssemos usado "Georgia", como o valor de pesquisa, ele teria retornado a pontuação do primeiro aluno da Geórgia, e deu nenhuma indicação de que há, de facto, dois estudantes da Georgia.

A Função PROCH

Onde PROCV encontra valores correspondentes em outra coluna, HLOOKUP encontra valores correspondentes em uma linha diferente. Porque é geralmente mais fácil de fazer a varredura através títulos de coluna até que você encontrar o caminho certo e usar um filtro para encontrar o que você está procurando, HLOOKUP é melhor usado quando você tem realmente grandes planilhas ou você está trabalhando com os valores que são organizados pelo tempo. Aqui está a sintaxe:

= HLOOKUP ([lookup_value], [table_array], [núm_índice_lin], [range_lookup])

[Lookup_value], mais uma vez, é o valor que você sabe e quer encontrar um valor correspondente para. [Table_array] é as células em que você deseja pesquisar. [Núm_índice_lin] especifica a linha que o valor de retorno virá. E [range_lookup] é o mesmo que acima deixe em branco para obter o valor mais próximo quando possível, ou digite 0 para apenas olhar para correspondências exatas.

Para este exemplo, eu criei uma nova planilha com generatedata.com. Este contém uma linha para cada estado, junto com uma pontuação SAT (vamos dizer que é a pontuação média para o Estado) nos anos 2000-2014. Vamos usar HLOOKUP para encontrar a pontuação média em Minnesota, em 2013. Aqui está como vamos fazê-lo:

= HLOOKUP (2013, A1: P51, 24)

(. Note-se que 2013 não está entre aspas porque é um número, e não um String, também, o 24 vem de Minnesota estar na linha 24) Como você pode ver na imagem abaixo, a pontuação é retornado:

PROCH-excel

Minnesotans em média uma pontuação de 1,014, em 2013.

Notas sobre HLOOKUP

Tal como acontece com PROCV, o valor de pesquisa precisa estar na primeira linha da tabela array- isso raramente é um problema com HLOOKUP, como normalmente você vai estar usando um título de coluna para um valor de pesquisa. HLOOKUP também só retorna um único valor.

As funções INDEX e MATCH

INDEX e MATCH são duas funções diferentes, mas quando eles são usados ​​juntos, eles podem tornar a busca uma planilha grande muito mais rápido. Ambas as funções têm desvantagens, mas, combinando-os, vamos construir sobre os pontos fortes de ambos. Primeiro, porém, a sintaxe:

= INDEX ([matriz], [row_number], [column_number]) = MATCH ([lookup_value], [lookup_array], [tipo_correspondência])

Em INDEX, [matriz] é a matriz em que você vai estar procurando. [Row_number] e [column_number] pode ser usado para limitar a sua Procurar- vamos dar uma olhada em que, em um momento.

A partida de [lookup_value] é um termo de pesquisa que pode ser uma cadeia ou uma numeração [lookup_array] é a matriz em que o Excel irá procurar o termo de pesquisa. [Tipo_correspondência] é um argumento opcional que pode ser de 1, 0 ou -1 1 retornará o maior valor que é menor ou igual à sua pesquisa prazo- 0 só irá devolver o prazo- exata e -1 voltará a menor valor que é maior ou igual ao seu termo de pesquisa.

Pode não ser claro como vamos usar essas duas funções em conjunto, por isso vou colocá-lo aqui. JOGO leva um termo de pesquisa e retorna uma referência de célula. Na imagem abaixo, você pode ver que em uma busca para o valor 646 na coluna F, JOGO retorna 4.

match-exemplo

ÍNDICE, por outro lado, faz o contrário: leva uma célula de referência e devolve o valor nele. Você pode ver aqui que, quando disse para voltar a sexta célula da coluna City, INDEX retorna "Anchorage", o valor da linha 6.

índice de exemplo

O que vamos fazer é combinar os dois, para que correspondem retorna uma referência de célula e INDEX usa essa referência para procurar o valor em uma célula. Vamos dizer que você lembre-se que havia um estudante cujo sobrenome era Waters, e você quer ver o que pontuação deste aluno foi. Aqui está a fórmula usaremos:

= INDEX (F: F, MATCH ( "Waters", C: C, 0))

Você vai notar que o tipo de correspondência é definido como 0 aqui- quando você está à procura de uma corda, que é o que você vai querer usar. Aqui está o que temos quando executar essa função:

índice de-jogo

Como você pode ver a partir do inserir, Owen Waters marcou 1720, o número que aparece quando executar a função. Isso pode não parecer tão útil quando você pode simplesmente olhar algumas colunas mais, mas imagine quanto tempo você salvar se você tivesse que fazê-lo 50 vezes em um planilha grande banco de dados que continham várias centenas de colunas!

Deixe a Pesquisa Begin

Excel tem um monte de funções extremamente poderosos, e os quatro listados acima apenas arranhar a superfície. Mesmo com esta visão geral superficial, no entanto, você deve ser capaz de guardar um monte de tempo em que você está trabalhando com planilhas grandes. Se você quiser ver o quanto mais você pode fazer com o índice, confira "O Índice Imponente"No Excel Hero.

Como você executar pesquisas em grandes planilhas? Como você encontrou essas funções de pesquisa para ser útil? Compartilhe seus pensamentos e experiências abaixo!

» » Encontrar nada em sua planilha excel com funções de pesquisa