Assistir ao vídeo - Pesquise o segundo, o terceiro ou o enésimo valor correspondente
Quando se trata de pesquisar dados no Excel, há duas funções incríveis que eu uso com frequência - VLOOKUP e INDEX (principalmente em conjunto com a função MATCH).
No entanto, essas fórmulas são projetadas para localizar apenas a primeira instância do valor de pesquisa.
Mas e se você quiser pesquisar o segundo, o terceiro, o quarto ou o enésimo valor.
Bem, é possível com um pouco de trabalho extra.
Neste tutorial, mostrarei várias maneiras (com exemplos) de como pesquisar o segundo ou o enésimo valor no Excel.
Pesquise o segundo, terceiro ou enésimo valor no Excel
Neste tutorial, abordarei duas maneiras de pesquisar o segundo ou o enésimo valor no Excel:
- Usando uma coluna auxiliar.
- Usando fórmulas de matriz.
Vamos começar e mergulhar de cabeça.
Usando a coluna auxiliar
Suponha que você seja um coordenador de treinamento em uma organização e tenha um conjunto de dados conforme mostrado abaixo. Você deseja listar todo o treinamento na frente do nome de um funcionário.
No conjunto de dados acima, os funcionários receberam treinamento em diferentes ferramentas do Microsoft Office (Excel, PowerPoint e Word).
Agora, você pode usar a função VLOOKUP ou a combinação INDEX / MATCH para encontrar o treinamento que um funcionário concluiu. No entanto, ele retornará apenas a primeira instância correspondente.
Por exemplo, no caso de John, ele fez todos os três treinamentos, mas quando procuro seu nome com VLOOKUP ou INDEX / MATCH, ele sempre retornará 'Excel', que é o primeiro treinamento para seu nome na lista .
Para fazer isso, podemos usar uma coluna auxiliar e criar valores de pesquisa exclusivos nela.
Aqui estão as etapas:
- Insira uma coluna antes da coluna que lista o treinamento.
- Na célula B2, insira a seguinte fórmula:
= A2 e CONT.SE ($ A $ 2: $ A2, A2)
- Na célula F2, insira a seguinte fórmula e copie e cole para todas as outras células:
= IFNA (PROCV ($ E2 & COLUNAS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
A fórmula acima retornaria o treinamento para cada funcionário na ordem em que aparece na lista. Caso não haja nenhum treinamento listado para um funcionário, retorna um espaço em branco.
Como essa fórmula funciona?
A fórmula CONT.SE na coluna auxiliar torna o nome de cada funcionário único, adicionando um número a ele. Por exemplo, a primeira instância de John se torna John1, a segunda instância se torna John2 e assim por diante.
A fórmula VLOOKUP agora usa esses nomes de funcionários exclusivos para encontrar o treinamento correspondente.
Observe que $ E2 & COLUMNS ($ F $ 1: F1) é o valor de pesquisa na fórmula. Isso adicionaria um número ao nome do funcionário com base no número da coluna. Por exemplo, quando esta fórmula é usada na célula F2, o valor de pesquisa torna-se “John1”. Na célula G2, torna-se “John2” e assim por diante.
Usando Fórmula de Matriz
Se você não quiser alterar o conjunto de dados original adicionando colunas auxiliares, também pode usar uma fórmula de matriz para pesquisar o segundo, terceiro ou enésimo valor.
Suponha que você tenha o mesmo conjunto de dados mostrado abaixo:
Esta é a fórmula que retornará o valor de pesquisa correto:
= IFERROR (ÍNDICE ($ B $ 2: $ B $ 14, PEQUENO (SE ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ""), COLUNAS ($ E $ 1 : E1))), "")
Copie esta fórmula e cole-a na célula E2.
Observe que esta é uma fórmula de matriz e você precisa usar Control + Shift + Enter (segure as teclas Control e Shift e pressione a tecla Enter), em vez de pressionar apenas a tecla Enter.
Clique aqui para baixar o arquivo de exemplo.
Como essa fórmula funciona?
Vamos quebrar essa fórmula em partes e ver como funciona.
$ A $ 2: $ A $ 14 = $ D2
A parte acima da fórmula compara cada célula em A2: A14 com o valor em D2. Neste conjunto de dados, ele verifica se uma célula contém o nome “John” ou não.
Ele retorna uma matriz TRUE ou FALSE. Se a célula tiver o nome ‘John’, seria True, caso contrário, seria False.
Abaixo está a matriz que você obteria neste exemplo:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Observe que ele possui TRUE na 1ª, 7ª e 111ª posição, pois é aí que o nome John aparece no conjunto de dados.
SE ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
A fórmula IF acima usa a matriz de VERDADEIRO e FALSO e substitui VERDADEIRO pela posição de sua ocorrência na lista (fornecida por ROW ($ A $ 2: $ A $ 14) -1) e FALSO por “” (espaços em branco). A seguir está a matriz resultante que você obtém com esta fórmula IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Observe que 1, 7 e 11 são as posições de ocorrência de João na lista.
PEQUENO (SE ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), COLUNAS ($ E $ 1: E1))
A função SMALL agora escolhe o primeiro menor, o segundo menor, o terceiro menor número deste array. Observe que ele usa a função COLUMNS para gerar o número da coluna. Na célula E2, a função COLUNAS retorna 1 e a função PEQUENA retorna 1. Na célula F2, a função COLUNAS retorna 2 e a função PEQUENA retorna 7.
ÍNDICE ($ B $ 2: $ B $ 14, PEQUENO (SE ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), COLUNAS ($ E $ 1: E1) ))
A função INDEX agora retorna o valor da lista na Coluna B com base na posição retornada pela função SMALL. Portanto, na célula E2, ele retorna 'Excel', que é o primeiro item em B2: B14. Na célula F2, ele retorna o PowerPoint, que é o sétimo item da lista.
Como há casos em que há apenas um ou dois treinamentos para alguns funcionários, a função INDEX retornaria um erro. A função IFERROR é usada para retornar um espaço em branco no lugar do erro.
Observe que, nestes exemplos, usei referências de intervalo. No entanto, em exemplos práticos, é benéfico converter os dados em uma tabela do Excel. Ao converter em uma Tabela do Excel, você pode usar referências estruturadas, o que torna mais fácil criar fórmulas. Além disso, uma Tabela do Excel pode contabilizar automaticamente quaisquer novos itens de treinamento que são adicionados à lista (para que você não precise ajustar as fórmulas todas as vezes).
O que você faz quando precisa pesquisar o segundo, o terceiro ou o enésimo valor? Tenho certeza de que existem mais maneiras de fazer isso. Se você usar algo mais fácil do que o listado aqui, compartilhe conosco na seção de comentários.
Clique aqui para baixar o arquivo de exemplo.
