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 utilizar em conjunto as funções ÍNDICE, CORRESP, ABS e MÍN.
Como encontrar a correspondência mais próxima no Excel?
Para identificar o vendedor cujas vendas estiveram mais próximas do objetivo de $20.000, conforme ilustrado acima, utilize uma fórmula que combina as funções ÍNDICE, CORRESP, ABS e MÍN da seguinte forma: a função ABS converte todas as diferenças entre as vendas de cada vendedor e o objetivo de vendas em valores absolutos (positivos); em seguida, a função MÍN identifica a menor dessas diferenças — ou seja, a correspondência mais próxima. Com essa informação, a função CORRESP localiza a posição dessa correspondência mais próxima, e a função ÍNDICE recupera o nome do vendedor nessa posição.
Sintaxe genérica
=INDEX(return_range,MATCH(MIN(ABS(lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ Nota: Trata-se de uma fórmula de matriz que requer que prima Ctrl+Shift+Enter.
- gama_de_retorno: A gama onde pretende que a fórmula de combinação devolva o vendedor. Refere-se aqui à gama de nomes.
- matriz_de_procura: O intervalo de células com os valores a comparar com o valor_de_procura. Aqui refere-se ao intervalo de vendas.
- valor_de_procura: O valor a comparar para encontrar a correspondência mais próxima. Aqui refere-se ao objetivo de vendas.
Para saber qual o vendedor que realizou vendas mais próximas do objetivo de $20,000, copie ou introduza a fórmula abaixo na célula F5 e prima Ctrl+Shift+Enterpara obter o resultado:
=ÍNDICE()B5:B10,CORRESP(MÍN(ABS()))C5:C10-20000)),ABS(C5:C10-20000),0))
Ou, utilize 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
=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- ABS(C5:C10-F4): A parte C5:C10-F4 obtém todos os valores de diferença entre cada venda no intervalo C5:C10 e a meta de vendas $20,000 na célula F4, resultando num vetor como este: {-4322;2451;6931;-1113;6591;-4782}. A função ABS converte todos os números negativos em positivos, como neste exemplo: {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 identifica o número mais pequeno no vetor {4322;2451;6931;1113;6591;4782}, ou seja, a menor diferença — a correspondência mais próxima. Assim, a função devolve 1113.
- CORRESP()MÍN()ABS(C5:C10-F4)),ABS(C5:C10-F4)O tipo_de_correspondência 0 obriga a função CORRESP a encontrar a posição exata do número 1113 no vetor {4322;2451;6931;1113;6591;4782}. A função devolve 4, uma vez que o número se encontra na 4ª posição.
- ÍNDICE()B5:B10,CORRESP()MÍN()ABS(C5:C10-F4)),ABS(C5:C10-F4)A função ÍNDICE devolve o 4.º valor na gama B5:B10, que é Bale.
Funções relacionadas
A função ÍNDICE do Excel devolve o Valor Exibido com base numa determinada posição de uma gama ou vetor.
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 ABS devolve o Valor absoluto de um número. Os números negativos serão convertidos em positivos com esta função, mas os números positivos e zero não serão afetados.
Fórmulas relacionadas
Procurar valor da correspondência mais próxima com múltiplos critérios
Em alguns casos, poderá precisar de procurar o valor da correspondência mais próxima ou aproximada com base em mais do que um critério. Com a combinação das funções ÍNDICE, CORRESP e SE, pode resolvê-lo rapidamente no Excel.
Correspondência aproximada com ÍNDICE e CORRESP
Há momentos em que precisamos de encontrar correspondências aproximadas no Excel para avaliar o desempenho dos colaboradores, classificar as notas dos alunos, calcular portes com base no peso, etc. Neste tutorial, explicaremos como utilizar as funções ÍNDICE e CORRESP para obter os resultados pretendidos.
Procurar valor da correspondência mais próxima com múltiplos critérios
Em alguns casos, poderá precisar de procurar o valor da correspondência mais próxima ou aproximada com base em mais do que um critério. Com a combinação das funções ÍNDICE, CORRESP e SE, pode resolvê-lo rapidamente no Excel.
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.