Correspondência aproximada bidirecional com múltiplos critérios
Neste tutorial, explicamos como encontrar uma correspondência aproximada com base em múltiplos critérios dispostos tanto em colunas como em linhas numa folha de cálculo do Excel, utilizando as funções ÍNDICE, CORRESP e SE.
Como realizar uma correspondência aproximada bidirecional com múltiplos critérios no Excel?
Para descobrir o tamanho de roupa de uma mulher com 165,5 cm de altura e 55 kg, de acordo com a tabela acima, pode combinar duas funções CORRESP na fórmula: uma, com a função SE, obtém o número da linha (sexo e altura); a outra obtém o número da coluna (peso). Em seguida, a função ÍNDICE localiza o tamanho correspondente com base nessas coordenadas. (Lembre-se: numa fórmula ÍNDICE, o número da linha vem antes do número da coluna.)
Sintaxe genérica
=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Nota: Trata-se de uma fórmula de matriz que exige que introduza com Ctrl+Shift+Enter.
- gama_devolução: A gama onde pretende que a fórmula de combinação devolva o tamanho. Refere-se à gama de tamanhos.
- valor_procura: O valor utilizado pela fórmula para localizar a posição do tamanho correspondente. Refere-se às informações indicadas sobre sexo, altura e peso.
- matriz_procura: A gama de células com os valores a comparar com o valor_procura. Refere-se aqui às gamas de sexo, altura e peso.
- tipo_correspondência: 1 ou -1.
1 ou omitido (predefinição): a função CORRESP encontra o maior valor que é menor ou igual ao valor_procura. Os valores na matriz_procura devem estar em ordem crescente.
-1: a função CORRESP encontra o menor valor que é maior ou igual ao valor_procura. Os valores na matriz_procura devem estar em ordem decrescente.
Para descobrir o tamanho de roupada mulhercom 165,5cm de altura e 55kg, copie ou introduza a fórmula abaixo na célula M9 e prima Ctrl+Shift+Enterpara obter o resultado:
=ÍNDICE()D5:J16,CORRESP()M6,SE()B5:B16=M5,C5:C16),1),CORRESP(M7,D4:J4,1))
Explicação da fórmula
=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): A função SE verifica cada valor em B5:B16 para ver se corresponde ao valor na célula M5, Feminino. Se corresponder, a função SE mantém as alturas correspondentes listadas em C5:C16; caso contrário, devolve FALSO. O resultado da fórmula será uma matriz como esta: {157;160;162,5;165;167,5;170;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}.
- CORRESP(M6,)SE(B5:B16=M5,C5:C16),1)=CORRESP(M6,){157;160;162,5;165;167,5;170;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO},1):O tipo_correspondência 1 obriga a função CORRESP a devolver a posição do maior valor que é menor ou igual a 165,5(o valor na célula)M6) na matriz. Assim, a função CORRESP devolve 4, a posição do valor 165.
- MATCH(M7,D4:J4,1):A função CORRESP devolve 3, pois o maior valor menor ou igual ao valor em M7,55, está na 3ª posição da gama D4:J4.
- ÍNDICE()D5:J16,CORRESP(M6,)SE(B5:B16=M5,C5:C16),1),CORRESP(M7,D4:J4,1)) = ÍNDICE(D5:J16: A função ÍNDICE obtém o valor na interseção da 4.ª linha e da 3.ª coluna do intervalo D5:J16, que corresponde à célula F8, contendo o valor M.
Funções relacionadas
A função ÍNDICE do Excel devolve o Valor Exibido com base numa determinada posição de uma gama ou matriz.
A função CORRESP do Excel procura um valor específico numa gama de células e devolve a posição relativa desse valor.
A função SE é uma das funções mais simples e úteis na Pasta de Trabalho do Excel. Realiza um teste lógico básico que, consoante o resultado da comparação, devolve um valor se for VERDADEIRO ou outro valor se for FALSO.
Fórmulas relacionadas
Pesquisa bidirecional com ÍNDICE e CORRESP
Para procurar algo simultaneamente em linhas e colunas no Excel, ou seja, procurar um valor na interseção de uma linha e coluna específicas, podemos utilizar as funções ÍNDICE e CORRESP.
Procurar correspondência mais próxima
Para encontrar a correspondência mais próxima de um valor de pesquisa num conjunto de dados numéricos no Excel, pode combinar as funções ÍNDICE, CORRESP, ABS e MÍNIMO.
Procurar valor de correspondência mais próxima com múltiplos critérios
Em alguns casos, poderá precisar de encontrar o valor correspondente mais próximo ou aproximado com base em mais do que um critério. Ao combinar as funções ÍNDICE, CORRESP e SE, consegue realizar esta tarefa rapidamente no Excel.
Pesquisa com múltiplos critérios com ÍNDICE e CORRESP
Quando trabalha com uma base de dados extensa numa folha de cálculo do Excel, com múltiplas colunas e legendas de linha, encontrar registos que satisfaçam vários critérios pode revelar-se um verdadeiro desafio. Nesses casos, a solução ideal é recorrer a uma fórmula de matriz que combine as funções ÍNDICE e CORRESP.
Correspondência aproximada com ÍNDICE e CORRESP
Por vezes, precisamos de encontrar correspondências aproximadas no Excel — seja para avaliar o desempenho dos colaboradores, classificar as notas dos alunos ou calcular portes com base no peso. Neste tutorial, explicamos como utilizar as funções ÍNDICE e CORRESP para obter os resultados pretendidos.
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.