Correspondência aproximada bidirecional com múltiplos critérios
Neste tutorial, vamos falar sobre como procurar uma correspondência aproximada com base em vários critérios listados tanto em colunas quanto em linhas em uma planilha do Excel, com a ajuda das 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 para uma mulher que tem 165,5 cm de altura e pesa 55 kg de acordo com a tabela acima, você pode adicionar duas funções CORRESP à fórmula: uma com uma 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, o ÍNDICE encontrará o tamanho correspondente de acordo com as coordenadas. (Observe que o número da linha vem antes do número da coluna em uma fórmula ÍNDICE.)
Sintaxe genérica
=ÍNDICE(intervalo_retorno;CORRESP(valor_pesquisa1;SE(intervalo_pesquisa2=valor_pesquisa2;intervalo_pesquisa1);tipo_correspondência);CORRESP(valor_pesquisa3;intervalo_pesquisa3;tipo_correspondência))
√ Nota: Esta é uma fórmula de matriz que exige que você a insira com Ctrl + Shift + Enter.
- intervalo_retorno: O intervalo onde você deseja que a fórmula combinatória retorne o tamanho. Aqui refere-se ao intervalo de tamanhos.
- valor_pesquisa: O valor que a fórmula usa para localizar a posição do tamanho correspondente. Aqui refere-se às informações sobre o sexo, altura e peso fornecidos.
- intervalo_pesquisa: O intervalo de células com os valores para comparar com o valor_pesquisa. Aqui refere-se aos intervalos de sexo, altura e peso.
- tipo_correspondência: 1 ou -1.
1 ou omitido (padrão), CORRESP encontrará o maior valor que é menor ou igual ao valor_pesquisa. Os valores no intervalo_pesquisa devem ser colocados em ordem crescente.
-1, CORRESP encontrará o menor valor que é maior ou igual ao valor_pesquisa. Os valores no intervalo_pesquisa devem ser colocados em ordem decrescente.
Para descobrir o tamanho de roupa para a mulher que tem 165,5 cm de altura e pesa 55 kg, copie ou insira a fórmula abaixo na célula M9 e pressione Ctrl + Shift + Enter para 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
=ÍNDICE(D5:J16;CORRESP(M6;SE(B5:B16=M5;C5:C16);1);CORRESP(M7;D4:J4;1))
- SE(B5:B16=M5;C5:C16): A função SE verifica cada valor em B5:B16 se eles correspondem ao valor na célula M5, Feminino. Se sim, a função SE manterá as alturas correspondentes listadas em C5:C16; Se não, SE retornará FALSOS. A fórmula retornará uma matriz assim: {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 força a função CORRESP a retornar a posição do maior valor que é menor ou igual a 165,5 (o valor na célula M6) na matriz. Assim, CORRESP retornará 4, a posição do valor 165.
- CORRESP(M7;D4:J4;1): A função CORRESP retorna 3, já que o maior valor que é menor ou igual ao valor em M7, 55, está na 3ª posição do intervalo D4:J4.
- ÍNDICE(D5:J16D5:J16;CORRESP(M6;SE(B5:B16=M5;C5:C16);1);CORRESP(M7;D4:J4;1)) = ÍNDICE(D5:J16D5:J16;4;3): A função ÍNDICE recupera o valor na interseção da 4ª linha e 3ª coluna no intervalo de tamanhos D5:J16, que é o valor na célula F8, M.
Funções relacionadas
A função ÍNDICE do Excel retorna o valor exibido com base em uma posição dada de um intervalo ou matriz.
A função CORRESP do Excel procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
A função SE é uma das funções mais simples e úteis em uma pasta de trabalho do Excel. Ela realiza um teste lógico simples que depende do resultado da comparação e retorna um valor se o resultado for VERDADEIRO ou outro valor se o resultado for FALSO.
Fórmulas relacionadas
Pesquisa bidirecional com ÍNDICE e CORRESP
Para procurar algo nas linhas e colunas no Excel, ou seja, para encontrar um valor na interseção de uma linha e coluna específicas, podemos usar a ajuda das funções ÍNDICE e CORRESP.
Pesquisar correspondência mais próxima
Para procurar a correspondência mais próxima de um valor de pesquisa em um conjunto de dados numérico no Excel, você pode usar as funções ÍNDICE, CORRESP, ABS e MÍN juntas.
Pesquisar correspondência mais próxima com múltiplos critérios
Em alguns casos, pode ser necessário procurar o valor mais próximo ou aproximado com base em mais de um critério. Com a combinação das funções ÍNDICE, CORRESP e SE, você pode fazer isso rapidamente no Excel.
Pesquisa com múltiplos critérios usando ÍNDICE e CORRESP
Ao lidar com um grande banco de dados em uma planilha do Excel com várias colunas e legendas de linha, sempre é complicado encontrar algo que atenda a múltiplos critérios. Nesse caso, você pode usar uma fórmula de matriz com as funções ÍNDICE e CORRESP.
Correspondência aproximada com ÍNDICE e CORRESP
Há momentos em que precisamos encontrar correspondências aproximadas no Excel para avaliar o desempenho dos funcionários, classificar as notas dos alunos, calcular o frete com base no peso, etc. Neste tutorial, falaremos sobre como usar as funções ÍNDICE e CORRESP para recuperar os resultados de que precisamos.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
O Kutools para Excel Oferece Mais de 300 Recursos, Garantindo que O Que Você Precisa Está Apenas a Um Clique de Distância...
Office Tab - Habilite a Leitura e Edição com Abas no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz Guias Eficientes para o Office (inclui Excel), Assim Como Chrome, Edge e Firefox.