Les fonctions Excel peuvent être extrêmement puissantes si vous savez combiner différentes formules. Des choses qui auraient pu sembler impossibles commenceraient soudainement à ressembler à un jeu d'enfant.
Un tel exemple consiste à trouver la correspondance la plus proche d'une valeur de recherche dans un ensemble de données dans Excel.
Il existe quelques fonctions de recherche utiles dans Excel (telles que VLOOKUP & INDEX MATCH), qui peuvent trouver la correspondance la plus proche dans quelques cas simples (comme je le montrerai avec les exemples 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 trouver la correspondance la plus proche d'une valeur de recherche dans Excel avec des formules de recherche.
Trouver la correspondance la plus proche dans Excel
Il peut y avoir de nombreux scénarios différents où vous devez rechercher la correspondance la plus proche (ou la valeur correspondante la plus proche).
Voici les exemples que je vais couvrir dans cet article :
- Trouvez le taux de commission en fonction des ventes
- Trouver le meilleur candidat (basé sur l'expérience la plus proche)
- Trouver la date du prochain événement
Commençons!
Cliquez ici pour télécharger le fichier exemple
Trouver le taux de commission (à la recherche de la valeur de vente la plus proche)
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous où vous souhaitez trouver les taux de commission de tout le personnel de vente.
La commission est attribuée en fonction de la valeur de vente. Et cela est calculé à l'aide du tableau de droite.
Par exemple, si un vendeur réalise le total des ventes de 5000, alors la commission est de 0% et s'il réalise le total des ventes de 15000 alors la commission est de 5%.
Pour obtenir le taux de commission, vous devez trouver la fourchette de vente la plus proche juste inférieure à la valeur de vente. Par exemple, pour une valeur de vente de 15 000, la commission serait de 10 000 (soit 5 %) et pour une valeur de vente de 25 000, le taux de commission serait de 20 000 (soit 7 %).
Pour trouver la valeur de vente la plus proche et obtenir le taux de commission, vous pouvez utiliser la correspondance approximative dans RECHERCHEV.
La formule ci-dessous ferait ceci:
=RECHERCHEV(B2,$E$2:$F$6,2,1)
Notez que dans cette formule, le dernier argument est 1, ce qui indique à la formule d'utiliser une recherche approximative. Cela signifie que la formule passerait par les valeurs de vente dans la colonne E et trouverait la valeur qui est juste inférieure à la valeur de recherche.
Ensuite, la formule RECHERCHEV donnera le taux de commission pour cette valeur.
Noter: Pour que cela fonctionne, vous devez trier les données par ordre croissant.Cliquez ici pour télécharger le fichier exemple
Trouver le meilleur candidat (basé sur l'expérience la plus proche)
Dans l'exemple ci-dessus, les données devaient être triées par ordre croissant. Mais il peut y avoir des cas où les données ne sont pas triées.
Voyons donc un exemple et voyons comment trouver la correspondance la plus proche dans Excel en utilisant une combinaison de formules.
Vous trouverez ci-dessous un exemple d'ensemble de données où je dois trouver le nom de l'employé qui a l'expérience de travail la plus proche de la valeur souhaitée. La valeur souhaitée dans ce cas dans 2,5 ans.
Notez que les données ne sont pas triées. De plus, l'expérience la plus proche peut être inférieure ou supérieure à l'expérience donnée. Par exemple, 2 ans et 3 ans sont tous deux également proches (différence de 0,5 an).
Ci-dessous la formule qui nous donnera le résultat :
=INDICE($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
L'astuce de cette formule consiste à modifier le tableau de recherche et la valeur de recherche pour trouver la différence d'expérience minimale entre les valeurs requises et réelles.
Comprenons d'abord comment vous le feriez manuellement (puis j'expliquerai comment fonctionne cette formule).
Lorsque vous faites cela manuellement, vous parcourez chaque cellule de la colonne B et trouvez la différence d'expérience entre ce qui est requis et celui qu'une personne a. Une fois que vous avez toutes les différences, vous trouverez celle qui est minimale et récupérez 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 partie vous donne la différence minimale entre l'expérience donnée (qui est de 2,5 ans) et toutes les autres expériences. Dans cet exemple, il renvoie 0,3
Notez que j'ai utilisé l'ABS pour m'assurer que je recherche le plus proche (ce qui peut être 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 dont 2,5 (l'expérience requise) a été soustrait.
Nous avons donc maintenant une valeur de recherche (0,3) et un tableau de recherche ({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 également la position du nom de la personne qui a l'expérience la plus proche.
Ce numéro de position est ensuite utilisé par la fonction INDEX pour renvoyer le nom de la personne.
Remarque : Dans le cas où plusieurs candidats ont la même expérience minimale, la formule ci-dessus donnera le nom du premier employé correspondant.
Trouver la date du prochain événement
Il s'agit d'un autre exemple où vous pouvez utiliser des formules 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 des événements et les dates des é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 donnera le nom de l'événement à venir :
=INDICE($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Et la formule ci-dessous donnera la date de l'événement à venir :
=INDICE($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Laissez-moi vous expliquer comment fonctionne cette formule.
Pour obtenir la date de l'événement, la fonction MATCH recherche la date actuelle dans la colonne B. Dans ce cas, nous ne cherchons pas une correspondance exacte, mais approximative. Et par conséquent, le dernier argument de la fonction MATCH est 1 (qui trouve la plus grande valeur inférieure ou égale à la valeur de recherche).
Ainsi, la fonction MATCH renverrait la position de la cellule dont la date est juste inférieure ou égale à la date actuelle. Ainsi, le prochain événement, dans ce cas, serait dans la cellule suivante (car 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 alors donnée par la fonction INDEX.
Pour obtenir le nom de l'événement, la même formule est utilisée et la plage de la fonction INDEX est modifiée de la colonne B à la colonne A.
Cliquez ici pour télécharger le fichier exemple
L'idée de cet exemple m'est venue lorsqu'un ami s'est approché avec une demande. Il avait une liste de tous les anniversaires de ses amis/parents dans une colonne et voulait connaître le prochain anniversaire à venir (et le nom de la personne).Voici trois exemples qui montrent comment trouver la valeur correspondante la plus proche dans Excel à l'aide de formules de recherche.
Vous aimerez peut-être aussi les conseils/tutoriels Excel suivants
- Obtenez le dernier numéro d'une liste à l'aide de la fonction RECHERCHEV.
- Obtenez plusieurs valeurs de recherche sans répétition dans une seule cellule.
- RECHERCHEV vs. INDEX/MATCH
- MATCH D'INDICE Excel
- Trouver la dernière occurrence d'une valeur de recherche dans une liste dans Excel
- Rechercher et supprimer les doublons dans Excel
- Mise en forme conditionnelle.
