As funções do Excel podem ser extremamente poderosas se você conseguir combinar fórmulas diferentes. Coisas que pareciam impossíveis de repente começavam a parecer uma brincadeira de criança.
Um exemplo é encontrar a correspondência mais próxima de um valor de pesquisa em um conjunto de dados no Excel.
Existem algumas funções de pesquisa úteis no Excel (como VLOOKUP & INDEX MATCH), que podem encontrar a correspondência mais próxima em alguns casos simples (como mostrarei nos exemplos abaixo).
Mas a melhor parte é que você pode combinar essas funções de pesquisa com outras funções do Excel para fazer muito mais (incluindo encontrar a correspondência mais próxima de um valor de pesquisa em uma lista não classificada).
Neste tutorial, mostrarei como encontrar a correspondência mais próxima de um valor de pesquisa no Excel com fórmulas de pesquisa.
Encontre a correspondência mais próxima no Excel
Pode haver muitos cenários diferentes onde você precisa procurar a correspondência mais próxima (ou o valor de correspondência mais próximo).
Abaixo estão os exemplos que irei cobrir neste artigo:
- Encontre a taxa de comissão com base nas vendas
- Encontre o melhor candidato (com base na experiência mais próxima)
- Encontrando a próxima data de evento
Vamos começar!
Clique aqui para baixar o arquivo de exemplo
Encontre a taxa de comissão (em busca do valor de vendas mais próximo)
Suponha que você tenha um conjunto de dados conforme mostrado abaixo, onde deseja encontrar as taxas de comissão de todo o pessoal de vendas.
A comissão é atribuída com base no valor de venda. E isso é calculado usando a tabela à direita.
Por exemplo, se um vendedor realiza vendas totais de 5.000, a comissão é de 0% e se ele / ela realiza vendas totais de 15.000, a comissão é de 5%.
Para obter a taxa de comissão, você precisa encontrar a faixa de venda mais próxima logo abaixo do valor de venda. Por exemplo, para o valor de venda de 15.000, a comissão seria de 10.000 (que é 5%) e para o valor de venda de 25.000, a taxa de comissão seria de 20.000 (que é 7%).
Para encontrar o valor de venda mais próximo e obter a taxa de comissão, você pode usar a correspondência aproximada em PROCV.
A fórmula abaixo faria isso:
= PROCV (B2, $ E $ 2: $ F $ 6,2,1)
Observe que, nesta fórmula, o último argumento é 1, o que diz à fórmula para usar uma pesquisa aproximada. Isso significa que a fórmula examinaria os valores de venda na coluna E e encontraria o valor que é apenas inferior ao valor de pesquisa.
Em seguida, a fórmula VLOOKUP fornecerá a taxa de comissão para este valor.
Observação: Para que isso funcione, você precisa ter os dados classificados em ordem crescente.Clique aqui para baixar o arquivo de exemplo
Encontre o melhor candidato (com base na experiência mais próxima)
No exemplo acima, os dados precisavam ser classificados em ordem crescente. Mas pode haver casos em que os dados não são classificados.
Então, vamos cobrir um exemplo e ver como podemos encontrar a correspondência mais próxima no Excel usando uma combinação de fórmulas.
Abaixo está um conjunto de dados de amostra onde preciso encontrar o nome do funcionário que tem a experiência de trabalho mais próxima do valor desejado. O valor desejado neste caso em 2,5 anos.
Observe que os dados não são classificados. Além disso, a experiência mais próxima pode ser menor ou maior do que a experiência oferecida. Por exemplo, 2 anos e 3 anos são igualmente próximos (diferença de 0,5 anos).
Abaixo está a fórmula que nos dará o resultado:
= ÍNDICE ($ A $ 2: $ A $ 15, CORRESPONDÊNCIA (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
O truque nesta fórmula é alterar a matriz de pesquisa e o valor de pesquisa para encontrar a diferença mínima de experiência nos valores necessários e reais.
Vamos primeiro entender como você faria isso manualmente (e então explicarei como essa fórmula funciona).
Ao fazer isso manualmente, você percorrerá cada célula da coluna B e encontrará a diferença na experiência entre o que é necessário e o que uma pessoa tem. Depois de ter todas as diferenças, você encontrará a que for mínima e buscará o nome dessa pessoa.
Isso é exatamente o que estamos fazendo com esta fórmula.
Deixe-me explicar.
O valor de pesquisa na fórmula MATCH é MIN (ABS (D2-B2: B15)).
Esta parte dá a você a diferença mínima entre a experiência dada (que é de 2,5 anos) e todas as outras experiências. Neste exemplo, ele retorna 0,3
Observe que usei o ABS para ter certeza de que estou procurando o mais próximo (que pode ser mais ou menos do que a experiência fornecida).
Agora, esse valor mínimo se torna nosso valor de pesquisa.
O array de pesquisa na função MATCH é ABS (D2- $ B $ 2: $ B $ 15).
Isso nos dá uma matriz de números da qual 2,5 (a experiência necessária) foi subtraída.
Portanto, agora temos um valor de pesquisa (0,3) e uma matriz de pesquisa ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})
A função MATCH encontra a posição de 0,3 nesta matriz, que também é a posição do nome da pessoa que tem a experiência mais próxima.
Este número de posição é então usado pela função INDEX para retornar o nome da pessoa.
Nota: No caso de haver vários candidatos com a mesma experiência mínima, a fórmula acima fornecerá o nome do primeiro funcionário compatível.
Encontre a próxima data do evento
Este é outro exemplo em que você pode usar fórmulas de pesquisa para encontrar a próxima data de um evento com base na data atual.
Abaixo está o conjunto de dados onde tenho os nomes dos eventos e as datas dos eventos.
O que eu quero é o nome do próximo evento e a data do próximo evento.
Abaixo está a fórmula que dará o nome do próximo evento:
= ÍNDICE ($ A $ 2: $ A $ 11, CORRESPONDÊNCIA (E1, $ B $ 2: $ B $ 11,1) +1)
E a fórmula abaixo fornecerá a data do próximo evento:
= ÍNDICE ($ B $ 2: $ B $ 11, CORRESPONDÊNCIA (E1, $ B $ 2: $ B $ 11,1) +1)
Deixe-me explicar como essa fórmula funciona.
Para obter a data do evento, a função MATCH procura a data atual na coluna B. Neste caso, não estamos procurando uma correspondência exata, mas aproximada. E, portanto, o último argumento da função MATCH é 1 (que encontra o maior valor que é menor ou igual ao valor de pesquisa).
Assim, a função MATCH retornaria a posição da célula que tem a data que é apenas menor ou igual à data atual. Portanto, o próximo evento, neste caso, seria na próxima célula (pois a lista é classificada em ordem crescente).
Portanto, para obter a data do próximo evento, basta adicionar um à posição da célula retornada pela função MATCH, e isso lhe dará a posição da célula da próxima data do evento.
Este valor é então fornecido pela função INDEX.
Para obter o nome do evento, a mesma fórmula é usada e o intervalo na função INDEX é alterado da coluna B para a coluna A.
Clique aqui para baixar o arquivo de exemplo
A ideia desse exemplo veio a mim quando um amigo se aproximou com um pedido. Ele tinha uma lista de aniversários de todos os seus amigos / parentes em uma coluna e queria saber o próximo aniversário que viria (e o nome da pessoa).Estes são três exemplos que mostram como encontrar o valor de correspondência mais próximo no Excel usando fórmulas de pesquisa.
Você também pode gostar das seguintes dicas / tutoriais do Excel
- Obtenha o último número de uma lista usando a função VLOOKUP.
- Obtenha vários valores de pesquisa sem repetição em uma única célula.
- VLOOKUP vs. ÍNDICE / CORRESPONDÊNCIA
- Excel INDEX MATCH
- Encontre a última ocorrência de um valor de pesquisa em uma lista no Excel
- Encontre e remova duplicatas no Excel
- Formatação condicional.
