As funções do Excel podem ser muito poderosas se você pegar o jeito de combinar diferentes fórmulas.O que antes parecia impossível pode de repente se tornar uma brincadeira de criança.
Um exemplo é encontrar o valor mais próximo em um conjunto de dados do Excel.
Existem várias funções de pesquisa úteis no Excel (como VLOOKUP e INDEX MATCH) que podem encontrar o mais próximo值(como mostrado no exemplo 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 usar uma fórmula de pesquisa para encontrar a correspondência mais próxima de um valor de pesquisa no Excel.
Existem muitos cenários diferentes em que você precisa encontrar a correspondência mais próxima (ou o valor de correspondência mais próximo).
Aqui está um exemplo que vou abordar neste artigo:
- Encontre taxas de comissão com base nas vendas
- Encontre o melhor candidato (com base na experiência mais próxima)
- Encontre a próxima data do evento
vamos começar!
Conteúdo
Encontre taxas de comissão (procure o valor de venda mais próximo)
Suponha que você tenha um conjunto de dados como o abaixo, no qual deseja encontrar as taxas de comissão para todos os vendedores.
As comissões são distribuídas com base no valor da venda.Isso é calculado usando a tabela à direita.
Por exemplo, se o total de vendas do vendedor for 5000, a comissão será de 0%, e se o total de vendas for de 15000, a comissão será de 5%.
Para obter uma taxa de comissão, você precisa encontrar a faixa de vendas mais próxima que esteja logo abaixo do valor de venda.Por exemplo, para 15000 vendas, a comissão é de 10,000 (ou 5%) e para 25000 vendas, a taxa de comissão é de 20,000 (ou 7%).
Para encontrar o valor de venda mais próximo e obter uma taxa de comissão, você pode usar a correspondência aproximada no PROCV.
A seguinte fórmula pode fazer isso:
= PROCV (B2, $ E $ 2: $ F $ 6,2,1)
Observe que nesta fórmula, o último parâmetro é 1, que informa à fórmula para usar a pesquisa aproximada.Isso significa que a fórmula passará pelo valor de vendas na coluna E e encontrará o valor logo abaixo do valor de pesquisa.
A fórmula PROCV dará então a taxa de comissão para esse valor.
注意: Para fazer isso, você precisa classificar os dados em ordem crescente.
Encontre o melhor candidato (com base na experiência mais próxima)
No exemplo acima, os dados precisam ser classificados em ordem crescente.Mas pode haver casos em que os dados não são classificados.
Então, vamos dar um exemplo e ver como podemos usar combinações de fórmulas para encontrar a correspondência mais próxima no Excel.
Abaixo está um conjunto de dados de exemplo em que preciso encontrar os nomes dos funcionários cuja experiência de trabalho está mais próxima do valor desejado.O valor esperado neste caso é de 2.5 anos.
Observe que os dados não são classificados.Além disso, as experiências mais próximas podem ser menos ou mais do que uma determinada experiência.Por exemplo, 2 anos e 3 anos são igualmente próximos (0.5 anos de diferença).
Aqui está a fórmula que nos dá o resultado:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
O truque nesta fórmula é alterar a matriz de pesquisa e os valores de pesquisa para encontrar a menor diferença empírica entre os valores desejados e reais.
Vamos primeiro entender como você faria isso manualmente (e depois explicarei como essa fórmula funciona).
Ao fazer isso manualmente, você percorre cada célula da coluna B e encontra a diferença entre a experiência que deseja e a experiência que tem.Depois de ter todas as diferenças, você encontra a menor e obtém o nome dessa pessoa.
Isso é exatamente o que fazemos com esta fórmula.
Deixe-me explicar.
O valor de pesquisa na fórmula CORRESP é MIN(ABS(D2-B2:B15)).
Esta seção fornece a diferença mínima entre a experiência fornecida (ou seja, 2.5 anos) e todas as outras experiências.Neste caso retorna 0.3
Observe que uso o ABS para garantir que estou procurando o mais próximo (provavelmente mais ou menos do que a experiência fornecida).
Agora, esse valor mínimo se torna nosso valor de pesquisa.
A matriz de pesquisa na função MATCH é ABS(D2-$B$2:$B$15).
Isso nos dá uma matriz de números dos quais 2.5 (experiência necessária) é subtraído.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({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 é a posição do nome da pessoa com a experiência mais próxima.
A função INDEX usa o número do trabalho para retornar o nome da pessoa.
Observação: se houver vários candidatos com a mesma experiência mínima, a fórmula acima fornecerá o nome do primeiro funcionário correspondente.
Encontre a próxima data do evento
Aqui está outro exemplo em que você pode usar uma fórmula de pesquisa para encontrar a próxima data de um evento com base na data atual.
Abaixo está o conjunto de dados onde tenho nomes de eventos e datas de eventos.
O que eu quero é o nome do próximo evento e a data do evento deste próximo evento.
Aqui está a fórmula que dá o nome do próximo evento:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
A fórmula a seguir fornecerá as próximas datas do evento:
=INDEX($B$2:$B$11,MATCH(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 na coluna B a data atual.Nesse caso, não estamos procurando uma correspondência exata, mas uma correspondência aproximada.Portanto, o último parâmetro da função MATCH é 1 (encontra o maior valor menor ou igual ao valor de pesquisa).
Portanto, a função MATCH retornará a posição da célula cuja data é menor ou igual à data atual.Portanto, neste caso, o próximo evento estará na próxima célula (já que a lista é classificada em ordem crescente).
Portanto, para obter a próxima data do evento, basta adicionar um à posição da célula retornada pela função MATCH e ela fornecerá a posição da célula da próxima data do evento.
Este valor é então dado pela função INDEX.
Para obter o nome do evento, use a mesma fórmula e altere o intervalo na função INDEX da coluna B para a coluna A.
Este exemplo veio à minha mente quando um amigo fez um pedido.Ele lista os aniversários de todos os seus amigos/parentes em uma coluna e quer saber que o próximo aniversário está chegando (e o nome da pessoa).
Os três exemplos acima mostram como usar a fórmula de pesquisa para encontrar o valor mais próximo no Excel.
