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 o ÍNDICE, JOGO, ABS e as funções MIN juntas.
Como encontrar a correspondência mais próxima no Excel?
Para saber qual o vendedor fez as vendas mais próximas da meta de US $ 20,000 como mostrado acima, uma fórmula combina as funções INDEX, MATCH, ABS e MIN irá ajudá-lo desta forma: A função ABS tornará todos os valores de diferença entre as vendas feitas por cada vendedor e a meta de vendas positiva, então MIN encontrará o menor diferença, o que significa a correspondência do armário. Agora podemos usar a função MATCH para localizar a posição da combinação do armário e fazer com que INDEX recupere o valor na posição correspondente.
Sintaxe genérica
=INDEX(return_range,MATCH(MIN(ABS(lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + Shift + Entrar.
- intervalo_de_retorno: O intervalo de onde você deseja que a fórmula de combinação retorne o vendedor. Aqui se refere ao intervalo de nomes.
- lookup_array: O intervalo de células com os valores para comparar com o lookup_value. Aqui se refere à faixa de vendas.
- valor_pesquisa: O valor com o qual comparar para encontrar sua correspondência mais próxima. Aqui se refere à meta de vendas.
Para saber qual o vendedor fez as vendas mais próximas da meta de US $ 20,000, copie ou insira a fórmula abaixo na célula F5 e pressione Ctrl + Shift + Entrar para obter o resultado:
= INDEX (B5: B10, CORRESPONDÊNCIA (MIN (ABS (C5: C10-20000)),ABDÔMEN(C5: C10-20000), 0))
Ou use uma referência de célula para tornar a fórmula dinâmica:
= INDEX (B5: B10, CORRESPONDÊNCIA (MIN (ABS (C5: C10-F4)),ABDÔMEN(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 obterá todos os valores de diferença entre cada venda no intervalo C5: C10 e as vendas-alvo $20,000 na cela F4 em uma matriz como esta: {-4322; 2451; 6931; -1113; 6591; -4782}. A função ABS tornará todos os números negativos positivos como este: {4322;2451;6931;1113;6591;4782}.
- MIN (ABS (C5: C10-F4)) = MIN ({4322;2451;6931;1113;6591;4782}): A função MIN encontrará o menor número da matriz {4322;2451;6931;1113;6591;4782}, o que significa a menor diferença, ou dizemos o jogo do armário. Então a função irá retornar 1113.
- PARTIDA(MIN (ABS (C5: C10-F4)),ABS (C5: C10-F4), 0) = PARTIDA(1113,{4322;2451;6931;1113;6591;4782}, 0): A tipo_correspondência 0 força a função MATCH para encontrar a posição do número exato 1113 na matriz {4322;2451;6931;1113;6591;4782}. A função irá retornar 4 uma vez que o número está no 4posição.
- ÍNDICE(B5: B10,PARTIDA(MIN (ABS (C5: C10-F4)),ABS (C5: C10-F4), 0)) = INDEX (B5: B10,4): A função INDEX retorna o 4o valor na faixa de nome B5: B10, o que é Fardo.
Funções relacionadas
A função INDEX do Excel retorna o valor exibido com base em uma determinada posição de um intervalo ou matriz.
A função MATCH do Excel procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
A função ABS retorna 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 o número zero não serão afetados.
Fórmulas Relacionadas
Pesquisar valor de correspondência mais próxima com vários critérios
Em alguns casos, pode ser necessário pesquisar o valor de correspondência mais próximo ou aproximado com base em mais de um critério. Com a combinação das funções INDEX, MATCH e IF, você pode fazer isso rapidamente no Excel.
Correspondência aproximada com INDEX e MATCH
Há momentos em que precisamos encontrar correspondências aproximadas no Excel para avaliar o desempenho dos funcionários, dar notas aos alunos, calcular a postagem com base no peso, etc. Neste tutorial, falaremos sobre como usar as funções INDEX e MATCH para recuperar o resultados que precisamos.
Pesquisar o valor de correspondência mais próximo com vários critérios
Em alguns casos, pode ser necessário pesquisar o valor de correspondência mais próximo ou aproximado com base em mais de um critério. Com a combinação das funções INDEX, MATCH e IF, você pode fazer isso rapidamente no Excel.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do 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.
