Neste artigo, aprenderemos como pesquisar várias instâncias de um valor no Excel.
Valores de pesquisa usando a opção suspensa?
Aqui, entendemos como podemos procurar resultados diferentes usando a fórmula de matriz da função INDEX. Basta selecionar o valor da lista e o resultado correspondente estará lá.
Fórmula Genérica
| {= INDEX (array, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (primeira célula de lookup_value_range) +1), ROW (1: 1)))} |
Variedade: O intervalo de onde você deseja buscar dados.
lookup_value: Seu lookup_value que você deseja filtrar.
lookup_value_range: O intervalo no qual você deseja filtrar lookup_value.
A primeira célula no intervalo lookup_value: se seu intervalo de lookup_value é $ A $ 5: $ A $ 100 então é $ A $ 5.
Importante: Tudo deveria ser referenciado absoluto. lookup_value pode ser relativo de acordo com a necessidade.
Insira-o como uma fórmula de matriz. Depois de escrever a fórmula, use a tecla CTRL + SHIFT + ENTER para torná-la uma fórmula de matriz.
Exemplo de pesquisa para vários resultados
Eu tenho esses dados de alunos dentro do alcance A2: E14. Na célula G1, tenho uma lista suspensa de valores de região, por exemplo Central, Leste, Norte, Sul e Oeste. Agora eu quero, seja qual for a região que eu tiver no G1, uma lista de todos os alunos dessa região deve ser exibida na coluna H.
Para pesquisar vários valores no Excel, vamos identificar nossas variáveis.
Variedade: $ C $ 2: $ C $ 14
lookup_value: $ G $ 1
lookup_value_range: $ A $ 2: $ A $ 14
A primeira célula do intervalo lookup_value: $ A $ 2
De acordo com os dados acima, nossa fórmula para recuperar vários valores no Excel será:
| {= IFERROR (INDEX ($ C $ 2: $ C $ 14, PEQUENO (IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) , ROW (1: 1))), "Não há mais valor")} |
Esta é uma fórmula de matriz. Não use apenas Enter após digitar a fórmula. Use CTRL + SHIFT + ENTER juntos.
Agora vamos entender COMO FUNCIONA.
Mesmo que a fórmula possa parecer complexa, a ideia é simples. Precisamos obter o número do índice de cada ocorrência de valor e, em seguida, recuperar os valores usando a função INDEX do Excel.
Portanto, o principal desafio é obter uma matriz de números de índice de lookup_value. Para obter os números do índice, usamos as funções IF e ROW. A fórmula é de fato completamente complexa, vamos decompô-la.
Queremos obter valores da coluna do aluno, portanto, nossa matriz para a função INDEX é $ C $ 2: $ C $ 14.
Agora precisamos fornecer os números das linhas de $ A $ 2: $ A $ 14 (valor de pesquisa) em que o valor de G1 existe (por enquanto, digamos G1 tem uma central).
SE ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Agora, esta parte retorna o número da linha se o valor de G1 de uma fonte de célula (central) no alcance $ A $ 2: $ A $ 14 mais retorna FALSO. Neste exemplo, ele retornará
{2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
Agora, como o array acima contém números de linha da 1ª linha (1: 1) e precisamos de linhas começando em nosso array (A2: A14). Para fazer isso, usamos -ROW ($ A $ 2) +1 na fórmula IF. Isso retornará um número de linhas antes de iniciar nosso array.
Para este exemplo, é -1. Se estivesse começando de A3, retornaria -2 e assim por diante. Este número será subtraído de cada número na matriz retornada por IF. Finalmente SE ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) isso se traduzirá em {1; FALSE; FALSE; FALSE; FALSE;6;7; FALSE; FALSE;10; FALSE; FALSE; FALSE}.
Em seguida, esta matriz é cercada por uma função PEQUENA. Esta função retorna o enésimo menor valor na matriz fornecida. Agora, temos PEQUENOS ({2; FALSE; FALSE; FALSE; FALSE;7;8; FALSE; FALSE;11; FALSE; FALSE; FALSE}, ROW (1: 1)). ROW (1: 1) retornará 1. Portanto, a função acima retornará o primeiro menor valor na matriz, que é 2.
Quando você copia esta fórmula nas células abaixo, ROW (1: 1) se tornará ROW (2: 2) e retornará o segundo menor valor na matriz, que é 7 e assim por diante. Isso permite que a função retorne o primeiro valor encontrado primeiro. Mas se você deseja obter o último valor encontrado primeiro, use a função LARGE em vez da função SMALL.
Agora, usando os valores retornados acima das funções, a função INDEX retorna facilmente cada valor correspondente de um intervalo.
Você também pode realizar pesquisas de correspondências exatas usando as funções INDEX e MATCH no Excel. Saiba mais sobre como fazer pesquisa sensível a maiúsculas e minúsculas usando a função INDEX & MATCH no Excel. Você também pode procurar as correspondências parciais usando os curingas no Excel.
Espero que este artigo sobre como pesquisar várias instâncias de um valor no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
Use PROCV de duas ou mais tabelas de pesquisa | Para pesquisar em várias tabelas, podemos usar uma abordagem IFERROR. Para pesquisar em várias tabelas, ele considera o erro como uma opção para a próxima tabela. Outro método pode ser uma abordagem If.
Como fazer pesquisa sensível a maiúsculas e minúsculas no Excel | a função VLOOKUP do Excel não diferencia maiúsculas de minúsculas e retornará o primeiro valor correspondente da lista. INDEX-MATCH não é exceção, mas pode ser modificado para diferenciar maiúsculas de minúsculas. Vamos ver como …
Pesquisar texto que aparece com frequência com critérios no Excel | A pesquisa aparece com mais frequência em texto em um intervalo em que usamos INDEX-MATCH com a função MODE. Aqui está o método.
Artigos populares :
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.