Les fonctions Excel peuvent être très puissantes si vous maîtrisez la combinaison de différentes formules.Ce qui semblait autrefois impossible peut soudainement devenir un jeu d'enfant.
Un tel exemple consiste à trouver la valeur la plus proche dans un jeu de données Excel.
Il existe plusieurs fonctions de recherche utiles dans Excel (telles que VLOOKUP et INDEX MATCH) qui peuvent trouver le plus proche值(comme illustré dans l'exemple ci-dessous).
Mais la meilleure partie est que vous pouvez combiner ces fonctions de recherche avec d'autres fonctions Excel pour faire beaucoup plus (y compris trouver la correspondance la plus proche d'une valeur de recherche dans une liste non triée).
Dans ce didacticiel, je vais vous montrer comment utiliser une formule de recherche pour trouver la correspondance la plus proche d'une valeur de recherche dans Excel.
Il existe de nombreux scénarios différents dans lesquels vous devez trouver la correspondance la plus proche (ou la valeur de correspondance la plus proche).
Voici un exemple que je vais aborder dans cet article :
- Trouver des taux de commission basés sur les ventes
- Trouver le meilleur candidat (basé sur l'expérience la plus proche)
- Trouver la date du prochain événement
commençons!
Table des matières
Trouver des taux de commission (rechercher la valeur de vente la plus proche)
Supposons que vous ayez un ensemble de données comme celui ci-dessous où vous souhaitez trouver les taux de commission pour tous les vendeurs.
Les commissions sont réparties en fonction de la valeur de la vente.Ceci est calculé à l'aide du tableau de droite.
Par exemple, si les ventes totales du vendeur sont de 5000 0, la commission est de 15000 % et si ses ventes totales sont de 5 XNUMX, la commission est de XNUMX %.
Pour obtenir un taux de commission, vous devez trouver la fourchette de ventes la plus proche qui se situe juste en dessous de la valeur des ventes.Par exemple, pour 15000 10,000 ventes, la commission est de 5 25000 (ou 20,000 %) et pour 7 XNUMX ventes, le taux de commission est de XNUMX XNUMX (ou XNUMX %).
Pour trouver la valeur de vente la plus proche et obtenir un taux de commission, vous pouvez utiliser une correspondance approximative dans VLOOKUP.
La formule suivante peut le faire :
= RECHERCHEV (B2, $ E $ 2: $ F $ 6,2,1)
Notez que dans cette formule, le dernier paramètre est 1, ce qui indique à la formule d'utiliser une recherche approximative.Cela signifie que la formule passera par la valeur des ventes dans la colonne E et trouvera la valeur juste en dessous de la valeur de recherche.
La formule VLOOKUP donnera alors le taux de commission pour cette valeur.
注意: Pour ce faire, vous devez trier les données par ordre croissant.
Trouver le meilleur candidat (basé sur l'expérience la plus proche)
Dans l'exemple ci-dessus, les données doivent être triées par ordre croissant.Mais il peut y avoir des cas où les données ne sont pas triées.
Prenons donc un exemple et voyons comment nous pouvons utiliser des combinaisons de formules pour trouver la correspondance la plus proche dans Excel.
Vous trouverez ci-dessous un exemple d'ensemble de données dans lequel je dois trouver les noms des employés dont l'expérience de travail est la plus proche de la valeur souhaitée.La valeur attendue dans ce cas est de 2.5 ans.
Notez que les données ne sont pas triées.De plus, les expériences les plus proches peuvent être inférieures ou supérieures à une expérience donnée.Par exemple, 2 ans et 3 ans sont également proches (différence de 0.5 an).
Voici la formule qui nous donne le résultat :
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
L'astuce dans cette formule consiste à modifier le tableau de recherche et les valeurs de recherche pour trouver la plus petite différence empirique entre les valeurs souhaitées et réelles.
Voyons d'abord comment vous le feriez manuellement (et ensuite j'expliquerai comment cette formule fonctionne).
Lorsque vous faites cela manuellement, vous parcourez chaque cellule de la colonne B et trouvez la différence entre l'expérience que vous souhaitez et celle que vous avez.Une fois que vous avez toutes les différences, vous trouvez la plus petite et obtenez le nom de cette personne.
C'est exactement ce que nous faisons avec cette formule.
Laisse-moi expliquer.
La valeur de recherche dans la formule MATCH est MIN(ABS(D2-B2:B15)).
Cette section vous donne la différence minimale entre l'expérience donnée (c'est-à-dire 2.5 ans) et toutes les autres expériences.Dans ce cas, il renvoie 0.3
Notez que j'utilise l'ABS pour m'assurer que je recherche le plus proche (probablement plus ou moins que l'expérience donnée).
Maintenant, cette valeur minimale devient notre valeur de recherche.
Le tableau de recherche dans la fonction MATCH est ABS(D2-$B$2:$B$15).
Cela nous donne un tableau de nombres auquel 2.5 (expérience requise) est soustrait.
所以現在我們有一個查找值 (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})
La fonction MATCH trouve la position de 0.3 dans ce tableau, qui est la position du nom de la personne ayant l'expérience la plus proche.
La fonction INDEX utilise ensuite le numéro de travail pour renvoyer le nom de la personne.
Remarque : S'il y a plusieurs candidats avec la même expérience minimale, la formule ci-dessus donnera le nom du premier employé correspondant.
Trouver la date du prochain événement
Voici un autre exemple où vous pouvez utiliser une formule de recherche pour trouver la prochaine date d'un événement en fonction de la date actuelle.
Vous trouverez ci-dessous l'ensemble de données où j'ai les noms d'événements et les dates d'événements.
Ce que je veux, c'est le nom du prochain événement et la date de cet événement à venir.
Voici la formule qui donne le nom de l'événement à venir :
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
La formule suivante donnera les dates des événements à venir :
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Laissez-moi vous expliquer comment cette formule fonctionne.
Pour obtenir la date de l'événement, la fonction MATCH recherche dans la colonne B la date actuelle.Dans ce cas, nous ne recherchons pas une correspondance exacte, mais une correspondance approximative.Par conséquent, le dernier paramètre de la fonction MATCH est 1 (elle trouve la plus grande valeur inférieure ou égale à la valeur de recherche).
Ainsi, la fonction MATCH renverra la position de la cellule dont la date est inférieure ou égale à la date actuelle.Donc, dans ce cas, le prochain événement sera dans la cellule suivante (puisque la liste est triée par ordre croissant).
Donc, pour obtenir la date de l'événement à venir, ajoutez simplement un à la position de la cellule renvoyée par la fonction MATCH et cela vous donnera la position de la cellule de la prochaine date de l'événement.
Cette valeur est ensuite donnée par la fonction INDEX.
Pour obtenir le nom de l'événement, utilisez la même formule et modifiez la plage dans la fonction INDEX de la colonne B à la colonne A.
Cet exemple m'est venu à l'esprit lorsqu'un ami m'a fait une demande.Il répertorie les anniversaires de tous ses amis/parents dans une colonne et veut savoir quel est le prochain anniversaire (et le nom de la personne).
Les trois exemples ci-dessus montrent comment utiliser la formule de recherche pour trouver la valeur la plus proche dans Excel.
