Buscar 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.
Como encontrar a correspondência mais próxima no Excel?
Para saber qual vendedor fez as vendas mais próximas da meta de $20.000 conforme mostrado acima, uma fórmula que combina as funções ÍNDICE, CORRESP, ABS e MÍN ajudará você da seguinte maneira: A função ABS transformará todos os valores de diferença entre as vendas feitas por cada vendedor e a meta de vendas em positivos, depois o MÍN encontrará a menor diferença, o que significa a correspondência mais próxima. Agora podemos usar a função CORRESP para localizar a posição da correspondência mais próxima e ter o ÍNDICE para recuperar o valor na posição correspondente.
Sintaxe genérica
=ÍNDICE(intervalo_retorno;CORRESP(MÍN(ABS(intervalo_pesquisa-valor_pesquisa));ABS(intervalo_pesquisa-valor_pesquisa);0))
√ 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 combinada retorne o vendedor. Aqui refere-se ao intervalo de nomes.
- intervalo_pesquisa: O intervalo de células com os valores para comparar com o valor_pesquisa. Aqui refere-se ao intervalo de vendas.
- valor_pesquisa: O valor a ser comparado para encontrar sua correspondência mais próxima. Aqui refere-se à meta de vendas.
Para saber qual vendedor fez as vendas mais próximas da meta de $20.000, copie ou insira a fórmula abaixo na célula F5 e pressione Ctrl + Shift + Enter para obter o resultado:
=ÍNDICE(B5:B10;CORRESP(MÍN(ABS(C5:C10-20000));ABS(C5:C10-20000);0))
Ou, use uma referência de célula para tornar a fórmula dinâmica:
=ÍNDICE(B5:B10;CORRESP(MÍN(ABS(C5:C10-F4));ABS(C5:C10-F4);0))
Explicação da fórmula
=ÍNDICE(B5:B10;CORRESP(MÍN(ABS(C5:C10-F4));ABS(C5:C10-F4);0))
- ABS(C5:C10-F4): A parte C5:C10-F4 obterá todos os valores de diferença entre cada venda no intervalo C5:C10 e a meta de vendas $20.000 na célula F4 em uma matriz como esta: {-4322;2451;6931;-1113;6591;-4782}. A função ABS transformará todos os números negativos em positivos assim: {4322;2451;6931;1113;6591;4782}.
- MÍN(ABS(C5:C10-F4)) = MÍN({4322;2451;6931;1113;6591;4782}): A função MÍN encontrará o menor número da matriz {4322;2451;6931;1113;6591;4782}, o que significa a menor diferença, ou seja, a correspondência mais próxima. Assim, a função retornará 1113.
- CORRESP(MÍN(ABS(C5:C10-F4));ABS(C5:C10-F4);0) = CORRESP(1113;{4322;2451;6931;1113;6591;4782};0): O tipo_corresp 0 força a função CORRESP a encontrar a posição do número exato 1113 na matriz {4322;2451;6931;1113;6591;4782}. A função retornará 4, já que o número está na 4ª posição.
- ÍNDICE(B5:B10B5:B10,,,CORRESP(MÍN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4);0))) = ÍNDICE(B5:B10B5:B10,,,4): A função ÍNDICE retorna o 4º valor no intervalo de nomes B5:B10, que é Bale.
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 busca um valor específico em um intervalo de células e retorna a posição relativa desse valor.
A função ABS retorna o valor absoluto de um número. Os números negativos serão convertidos em positivos com essa função, mas os números positivos e zero não serão afetados.
Fórmulas relacionadas
Buscar correspondência mais próxima com múltiplos critérios
Em alguns casos, pode ser necessário buscar a correspondência mais próxima ou aproximada 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.
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.
Buscar correspondência mais próxima com múltiplos critérios
Em alguns casos, pode ser necessário buscar a correspondência mais próxima ou aproximada 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.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias 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.