Na maioria das vezes, encontrar uma célula em uma planilha do Excel é bem fácil; Se você não pode apenas verificar as linhas e colunas para isso, você pode usar CTRL + F para procurá-lo. Mas se você estiver trabalhando com uma planilha muito grande Como dividir uma planilha enorme do Excel CSV em arquivos separados Como dividir uma planilha enorme do Excel CSV em arquivos separados Uma das deficiências do Microsoft Excel é o tamanho limitado de uma planilha. Se você precisar tornar seu arquivo do Excel menor ou dividir um arquivo CSV grande, continue lendo! Leia mais, pode economizar muito tempo para usar uma dessas quatro funções de pesquisa. Não importa o tamanho do seu documento do Microsoft Excel, eles serão muito mais eficientes.
A função VLOOKUP
Esta função permite que você especifique uma coluna e um valor, e retornará um valor da linha correspondente de uma coluna diferente (se isso não fizer sentido, ficará claro daqui a pouco). Dois exemplos em que você pode fazer isso são procurar o sobrenome de um funcionário pelo número do funcionário ou encontrar um número de telefone especificando um sobrenome. Aqui está a sintaxe da função:
= VLOOKUP ([lookup_value], [table_array], [col_index_num], [range_lookup])
O [lookup_value] é a informação que você já tem; por exemplo, se você precisa saber em que estado uma cidade se encontra, seria o nome da cidade. [table_array] permite especificar as células nas quais a função procurará os valores de pesquisa e retorno. Ao selecionar seu intervalo, certifique-se de que a primeira coluna incluída em sua matriz seja a que incluirá seu valor de pesquisa. Isso é crucial. [col_index_num] é o número da coluna que contém o valor de retorno.
[range_lookup] é um argumento opcional e recebe 1 ou 0. Se você digitar 1 ou omitir esse argumento, a função procurará o valor digitado ou o próximo número mais baixo. Portanto, na imagem abaixo, um VLOOKUP procurando uma pontuação SAT de 652 retornará 646, pois é o número mais próximo na lista que é menor que 652, e [range_lookup] assume como padrão 1.
Vamos dar uma olhada em como podemos usar isso. Como tenho em meus artigos sobre operadores booleanos no Microsoft Excel Mini Tutorial do Excel: Use lógica booleana para processar dados complexos Mini Excel Tutorial: usar lógica booleana para processar dados complexos Operadores lógicos SE, NOT, AND e OR podem ajudá-lo a obter Novato do Excel para o usuário avançado. Nós explicamos os fundamentos de cada função e demonstramos como você pode usá-los para resultados máximos. Leia Mais e contando e adicionando funções avançadas ao Excel Mini Tutorial: Usando a Contagem Avançada e Adicionando Funções ao Excel Excel Tutorial: Usando Contagem Avançada e Adicionando Funções no Excel A contagem e a adição de fórmulas podem parecer banais se comparadas às fórmulas mais avançadas do Excel. Mas eles podem ajudar você a economizar muito tempo quando precisar coletar informações sobre os dados em sua planilha. Leia mais, eu vou estar usando uma planilha que eu gerou com generatedata.com. Ele contém números de identificação, nomes e sobrenomes, escores de cidade, estado e SAT. Digamos que eu queira encontrar a pontuação SAT de uma pessoa com o sobrenome “Winters”. O VLOOKUP torna isso fácil. Aqui está a fórmula que usaríamos:
= VLOOKUP ("Winters", C2: F101, 4, 0) Como as pontuações do SAT são a quarta coluna da coluna do último nome, usei 4 para o argumento de índice da coluna. Observe que, quando você está procurando por texto, definir [range_lookup] como 0 é uma boa ideia. sem isso, você pode obter resultados ruins. Veja o que o Microsoft Excel nos oferece:
Ele retornou 651, a pontuação do SAT pertencente ao aluno chamado Kennedy Winters, que está na linha 92 (exibida no encarte acima). Levaria muito mais tempo para percorrer procurando o nome do que digitar rapidamente a sintaxe!
O VLOOKUP também pode ser bastante útil se você estiver usando o Microsoft Excel para fazer seus impostos. Fazendo seus impostos? 5 fórmulas Excel você deve saber fazer seus impostos? 5 Fórmulas Excel Você deve saber É dois dias antes de seus impostos serem devidos e você não quer pagar outra taxa de depósito tardio. Este é o momento de aproveitar o poder do Excel para colocar tudo em ordem. Consulte Mais informação .
Notas sobre o VLOOKUP
Algumas coisas são boas para lembrar quando você está usando o VLOOKUP. Primeiro, como mencionei anteriormente, verifique se a primeira coluna do seu intervalo é a que inclui o valor de pesquisa. Se não estiver na primeira coluna, a função retornará resultados incorretos. Se suas colunas são bem organizadas, isso não deve ser um problema.
A segunda coisa a ter em mente é que o VLOOKUP só retornará um valor. Se tivéssemos usado "Geórgia" como o valor de pesquisa, ele teria retornado a pontuação do primeiro aluno da Geórgia e não teria dado indicações de que há, de fato, dois estudantes da Geórgia.
A função HLOOKUP
Onde VLOOKUP encontra os valores correspondentes em outra coluna, HLOOKUP encontra os valores correspondentes em uma linha diferente. Como geralmente é mais fácil verificar cabeçalhos de coluna até encontrar o caminho certo e usar um filtro para encontrar o que você está procurando, o HLOOKUP é melhor usado quando você tem planilhas muito grandes ou está trabalhando com valores organizados pelo tempo . Aqui está a sintaxe:
= HLOOKUP ([lookup_value], [table_array], [row_index_num], [intervalo_lookup])
[lookup_value], novamente, é o valor que você conhece e deseja encontrar um valor correspondente para. [table_array] são as células nas quais você deseja pesquisar. [row_index_num] especifica a linha da qual 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 procurar somente correspondências exatas.
Para este exemplo, criei uma nova planilha com o generatedata.com. Este contém uma linha para cada estado, juntamente com uma pontuação SAT (vamos dizer que é a pontuação média para o estado) nos anos 2000-2014. Usaremos o HLOOKUP para encontrar a pontuação média em Minnesota em 2013. Veja como faremos isso:
= HLOOKUP (2013, A1: P51, 24)
(Note que 2013 não está entre aspas porque é um número, e não uma string; também, o 24 vem de Minnesota estando na 24ª linha.) Como você pode ver na imagem abaixo, a partitura é retornada:
Os minnesotans tiveram uma pontuação média de 1.014 em 2013.
Notas sobre o HLOOKUP
Como com o VLOOKUP, o valor de pesquisa precisa estar na primeira linha da matriz da tabela; isso raramente é um problema com o HLOOKUP, pois você geralmente estará usando um título de coluna para um valor de pesquisa. O HLOOKUP também retorna apenas um único valor.
As funções INDEX e MATCH
INDEX e MATCH são duas funções diferentes, mas quando usadas juntas elas podem tornar a busca de uma planilha grande muito mais rápida. Ambas as funções têm desvantagens, mas combinando-as, vamos aproveitar os pontos fortes de ambas. Primeiro, porém, a sintaxe:
= INDEX ([array], [row_number], [column_number]) = MATCH ([lookup_value], [lookup_array], [match_type])
No INDEX, [array] é o array no qual você estará pesquisando. [row_number] e [column_number] podem ser usados para restringir sua pesquisa. vamos dar uma olhada nisso daqui a pouco.
[Lookup_value] da MATCH é um termo de pesquisa que pode ser uma string ou um número; [lookup_array] é a matriz na qual o Microsoft Excel procurará pelo termo de pesquisa. [match_type] é um argumento opcional que pode ser 1, 0 ou -1; 1 retornará o maior valor menor ou igual ao seu termo de pesquisa; 0 só retornará seu termo exato; e -1 retornará o menor valor que é maior ou igual ao seu termo de pesquisa.
Pode não estar claro como vamos usar essas duas funções juntas, então vou explicar isso aqui. MATCH pega um termo de pesquisa e retorna uma referência de célula. Na imagem abaixo, você pode ver que em uma pesquisa pelo valor 646 na coluna F, MATCH retorna 4.
O INDEX, por outro lado, faz o oposto: pega uma referência de célula e retorna o valor nela. Você pode ver aqui que, quando solicitado a devolver a sexta célula da coluna City, o INDEX retorna “Anchorage”, o valor da linha 6.
O que vamos fazer é combinar os dois para que o MATCH retorne uma referência de célula e o INDEX use essa referência para procurar o valor em uma célula. Digamos que você se lembre de que havia um aluno cujo sobrenome era Waters e quer ver qual era a pontuação desse aluno. Aqui está a fórmula que vamos usar:
= ÍNDICE (F: F, CORRESPONDÊNCIA ("Águas", C: C, 0)) Você notará que o tipo de correspondência está definido como 0 aqui; quando você está procurando por uma string, é isso que você vai querer usar. Aqui está o que temos quando executamos essa função:
Como você pode ver a partir da inserção, Owen Waters marcou 1720, o número que aparece quando executamos a função. Isso pode não parecer tão útil quando você pode apenas olhar algumas colunas, mas imagine quanto tempo você salvaria se tivesse que fazer isso 50 vezes em uma planilha de banco de dados grande. Acesso - Uma planilha pode substituir um banco de dados? Excel vs. Acesso - Uma planilha pode substituir um banco de dados? Qual ferramenta você deve usar para gerenciar dados? O Access e o Excel apresentam filtragem de dados, comparação e consulta. Mostraremos qual é a mais adequada às suas necessidades. Leia mais que continha várias centenas de colunas!
Deixe a pesquisa começar
Microsoft Excel tem um monte de funções extremamente poderosas 3 Crazy Excel fórmulas que fazem coisas surpreendentes 3 Crazy Excel fórmulas que fazem coisas surpreendentes O poder do Microsoft Excel está em suas fórmulas. Deixe-me mostrar as maravilhas que você pode fazer com fórmulas e formatação condicional em três exemplos úteis. Leia mais, e os quatro listados acima apenas arranham a superfície. Mesmo com essa visão geral superficial, você deve economizar muito tempo ao trabalhar com planilhas grandes. Se você quiser ver o quanto mais você pode fazer com o ÍNDICE, confira “O Índice Imponente” no Microsoft Excel Hero.
Como você executa pesquisas em planilhas grandes? Como você achou essas funções de pesquisa úteis? Compartilhe seus pensamentos e experiências abaixo!
Créditos de Imagem: lupa em uma planilha via Shutterstock