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

Na maioria das vezes, encontrar uma célula em uma planilha do Excel é muito fácil- se você não pode apenas digitalizar através das linhas e colunas para isso, você pode usar CTRL + F para procurá-lo. Mas se você estiver trabalhando com um realmente grande planilha, 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.

Como dividir uma planilha enorme CSV Excel em arquivos separadosComo dividir uma planilha enorme CSV Excel em arquivos separadosconsulte Mais informação

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 onde você pode fazer isso estão olhando para cima o sobrenome de um empregado por seu número de empregado ou encontrar um número de telefone, especificando um sobrenome. Aqui está a sintaxe da função:

= PROCV ([valor_procurado], [table_array], [núm_índice_coluna], [procurar_intervalo])

O [valor_procurado] é o pedaço de informação que você já ter- por exemplo, se você precisa saber o estado de uma cidade se encontra, seria o nome da cidade. [Table_array] permite especificar as células em que a função vai olhar para a pesquisa e valores de retorno. 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] representa o número da coluna que contém o valor de retorno.

Video: Excel Avançado - Função Localizar

[Procurar_intervalo] é 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 [procurar_intervalo] o padrão é 1.

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 gerada 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 sobrenome “Winters”. PROCV torna mais fácil. Aqui está a fórmula que usaria:Mini Excel Tutorial: Use a lógica booleana para processar dados ComplexMini Excel Tutorial: Use a lógica booleana para processar dados ComplexOs operadores lógicos IF, NOT, AND e OR, pode ajudá-lo a partir de novato Excel para o usuário poder. Nós explicar os conceitos básicos de cada função e demonstrar como você pode usá-los para o máximo de resultados.consulte Mais informação

= 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, definindo [procurar_intervalo] para 0 é uma boa idéia-sem ele, você pode obter maus resultados. Aqui está o que Excel dá-nos:

Ele 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.Fazendo seus impostos? 5 Excel fórmulas que você deve saberFazendo seus impostos? 5 Excel fórmulas que você deve saberÉ dois dias antes de seus impostos são devidos e você não quer pagar uma outra taxa de depósito atrasado. Este é o momento de aproveitar o poder do Excel para colocar tudo em ordem.consulte Mais informação

Notas sobre PROCV

Algumas coisas são boas para se lembrar quando você está usando PROCV. Primeiro, 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 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 dado nenhuma indicação de que há, de facto dois estudantes da Georgia.

A função HLOOKUP

Onde PROCV encontra valores correspondentes na outra coluna, PROCH encontra valores correspondentes de uma linha diferente. Porque é geralmente mais fácil de fazer a varredura através cabeçalhos das colunas até 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:

= PROCH ([valor_procurado], [table_array], [núm_índice_lin], [procurar_intervalo])

[Valor_procurado], 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 [procurar_intervalo] é 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. Usaremos PROCH para encontrar a pontuação média em Minnesota, em 2013. Aqui está como vamos fazê-lo:

= PROCH (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 é devolvido:

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:

= Índice ([gama], [row_number], [column_number]) = FÓSFORO ([valor_procurado], [matriz_consulta], [tipo_correspondência])

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

Video: EXCEL AVANÇADO - PROCV - Pesquisa de clientes

A partida de [valor_procurado] é um termo de pesquisa que pode ser uma cadeia ou um número- [lookup_array] é a matriz em que 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 retornará 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.

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

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 resultado deste aluno foi. Aqui está a fórmula usaremos:

= Índice (F: F, FÓSFORO ("Waters", C: C, 0))

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

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 continha várias centenas de colunas!Vs. Excel Acesso - Pode uma planilha Substituir um banco de dados?Vs. Excel Acesso - Pode uma planilha Substituir um banco de dados?Qual ferramenta você deve usar para gerenciar dados? Access e Excel ambos possuem filtragem de dados, agrupamento e consulta. Nós vamos mostrar-lhe qual é o mais adequado para suas necessidades.consulte Mais informação

Deixe a Pesquisa Begin

Excel tem um monte de funções extremamente poderosos, e a 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, veja “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!