ÍNDICE e CORRESP com múltiplas matrizes
Imagine que tem várias tabelas com as mesmas legendas, como ilustrado abaixo; encontrar um valor de pesquisa que corresponda aos critérios indicados nessas tabelas pode revelar-se uma tarefa difícil. Neste tutorial, explicamos como procurar um valor em múltiplas matrizes, intervalos ou grupos, correspondendo a critérios específicos, com as funções ÍNDICE, CORRESP e ESCOLHER.
Como procurar um valor em várias matrizes?
Para identificar os líderes de diferentes grupos pertencentes a departamentos distintos, comece por utilizar a função ESCOLHER para selecionar a tabela da qual obter o nome do líder. Em seguida, a função CORRESP determina a posição do líder na respetiva tabela. Por fim, a função ÍNDICE recupera o nome do líder com base nessa posição e na coluna especificada onde estão listados os nomes dos líderes.
Sintaxe genérica
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: O número utilizado pela função CHOOSE para indicar qual a matriz da lista array1, array2, … a partir da qual obter o resultado.
- array1, array2, …: As matrizes a partir das quais se pretende obter o resultado. Neste caso, referem-se às três tabelas.
- lookup_value: O valor que a fórmula combinada utiliza para localizar a posição do respetivo líder. Refere-se aqui ao grupo indicado.
- lookup_array: O intervalo de células onde o lookup_value está listado. Neste caso, refere-se ao intervalo dos grupos. Nota: Pode utilizar o intervalo de grupos de qualquer departamento, uma vez que são todos iguais e precisamos apenas de obter o número da posição.
- column_num: A coluna que indica de onde pretende recuperar os dados.
Para saber o líder do Grupo D pertencente ao Departamento A, copie ou introduza a fórmula abaixo na célula G5 e prima Enterpara obter o resultado:
=ÍNDICE(ESCOLHER())1,$B$5:$C$8 ,$B$11:$C$14 ,$B$17:$C$20),CORRESP(F5,$B$5:$B$8,0),2)
√ Nota: Os símbolos de dólar ($) acima indicam referências absolutas, o que significa que os intervalos de nomes e classes na fórmula não mudarão quando mover ou copiar a fórmula para outras células. Depois de introduzir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células seguintes e altere o array_numem conformidade.
Explicação da fórmula
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): A função CHOOSE devolve a 1.ª matriz das três matrizes listadas na fórmula. Assim, devolverá $B$5:$C$8, ou seja, o Intervalo de Dados do Departamento A.
- MATCH(F5,$B$5:$B$8,0): O tipo de correspondência 0 obriga a função CORRESP a devolver a posição da primeira ocorrência de Grupo D, o valor na célula F5, na matriz $B$5:$B$8, que é 4.
- ÍNDICE()CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2) = ÍNDICE($B$5:$C$8,4,2 A função ÍNDICE obtém o valor na interseção da 4.ª linha e da 2.ª coluna do intervalo $B$5:$C$8, que é Emily.
Para evitar alterar o array_numNa fórmula, sempre que a copiar, pode utilizar a coluna auxiliar — a coluna D. A fórmula ficaria assim:
=ÍNDICE(ESCOLHER())D5,$B$5:$C$8 ,$B$11:$C$14 ,$B$17:$C$20),CORRESP(F5,$B$5:$B$8,0),2)
√ Nota: Os números 1,2,3na coluna auxiliar indicam a array1,array2,array3dentro da função ESCOLHER.
Funções relacionadas
A função ÍNDICE do Excel devolve o valor exibido com base na posição especificada num intervalo ou matriz.
A função CORRESP do Excel procura um valor específico num intervalo de células e devolve a sua posição relativa.
A função ESCOLHER devolve um valor da lista de argumentos com base no número de índice indicado. Por exemplo, ESCOLHER(3;«Apple»;«Peach»;«Orange») devolve «Laranja», já que o número de índice é 3 e «Laranja» corresponde ao terceiro valor da lista após o índice na função.
Fórmulas relacionadas
Intervalo de valor de pesquisa de outra folha de cálculo ou livro
Se já sabe utilizar a função PROCV para procurar valores numa folha de cálculo, procurar valores noutra folha ou noutro livro não será um problema para si.
PROCV com nome de folha dinâmico
Em muitos casos, precisará de recolher dados de várias folhas de cálculo para um resumo. Ao combinar as funções PROCV e INDIRECTO, pode criar uma fórmula que procure valores específicos em diferentes folhas, utilizando um nome de folha dinâmico.
Pesquisa com múltiplos critérios com ÍNDICE e CORRESP
Gerir uma grande base de dados numa folha de cálculo do Excel com múltiplas colunas e legendas de linha pode tornar-se desafiante quando precisa de encontrar registos que cumpram vários critérios. Nesses casos, a solução ideal é utilizar uma fórmula matricial com as funções ÍNDICE e CORRESP.
As Melhores Ferramentas de Produtividade para o Office
Kutools for Excel – Ajuda-o a destacar-se da multidão
Kutools for Excel Oferece mais de 300 funcionalidades,garantindo que tudo o que precisa está a apenas um clique…
Office Tab – Ative a leitura e edição com separadores no Microsoft Office (incluindo o Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques diários e diga adeus à tendinite do rato.
- Aumente a sua produtividade em 50 % ao visualizar e editar vários documentos simultaneamente.
- Traz uma navegação eficiente com separadores ao Office (incluindo o Excel), tal como no Chrome, Edge e Firefox.