Recherche de la correspondance la plus proche
Pour rechercher la correspondance la plus proche d'une valeur dans un ensemble de données numériques dans Excel, vous pouvez utiliser les fonctions INDEX, EQUIV, ABS, et MIN ensemble.
Comment trouver la correspondance la plus proche dans Excel ?
Pour savoir quel vendeur a réalisé les ventes les plus proches de l'objectif de 20 000 $ comme indiqué ci-dessus, une formule combinant les fonctions INDEX, EQUIV, ABS et MIN vous aidera de cette manière : La fonction ABS transformera toutes les valeurs de différence entre les ventes réalisées par chaque vendeur et l'objectif de vente en valeurs positives, puis MIN trouvera la plus petite différence, ce qui signifie la correspondance la plus proche. Maintenant, nous pouvons utiliser la fonction EQUIV pour localiser la position de la correspondance la plus proche et avoir INDEX pour récupérer la valeur à la position correspondante.
Syntaxe générique
=INDEX(plage_retour,EQUIV(MIN(ABS(plage_recherche-valeur_recherche)),ABS(plage_recherche-valeur_recherche),0))
√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être validée avec Ctrl + Maj + Entrée.
- plage_retour : La plage où vous souhaitez que la formule combinée retourne le vendeur. Ici, cela fait référence à la plage des noms.
- plage_recherche : La plage de cellules avec les valeurs à comparer avec la valeur_recherche. Ici, cela fait référence à la plage des ventes.
- valeur_recherche : La valeur à comparer pour trouver sa correspondance la plus proche. Ici, cela fait référence à l'objectif de vente.
Pour savoir quel vendeur a réalisé les ventes les plus proches de l'objectif de 20 000 $, veuillez copier ou saisir la formule ci-dessous dans la cellule F5, puis appuyer sur Ctrl + Maj + Entrée pour obtenir le résultat :
=INDEX(B5:B10,EQUIV(MIN(ABS(C5:C10-20000)),ABS(C5:C10-20000),0))
Ou, utilisez une référence de cellule pour rendre la formule dynamique :
=INDEX(B5:B10,EQUIV(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
Explication de la formule
=INDEX(B5:B10,EQUIV(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- ABS(C5:C10-F4): La partie C5:C10-F4 obtiendra toutes les valeurs de différence entre chaque vente dans la plage C5:C10 et l'objectif de vente de 20 000 $ dans la cellule F4 sous forme de tableau comme ceci : {-4322;2451;6931;-1113;6591;-4782}. La fonction ABS transformera tous les nombres négatifs en positifs comme ceci : {4322;2451;6931;1113;6591;4782}.
- MIN(MIN(ABS(C5:C10-F4))) = MIN(MIN({4322;2451;6931;1113;6591;4782})): La fonction MIN trouvera le plus petit nombre du tableau {4322;2451;6931;1113;6591;4782}, ce qui signifie la plus petite différence, ou disons la correspondance la plus proche. Donc, la fonction retournera 1113.
- EQUIV(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0) = EQUIV(1113,{4322;2451;6931;1113;6591;4782},0) : Le type_correspondance 0 force la fonction EQUIV à trouver la position du nombre exact 1113 dans le tableau {4322;2451;6931;1113;6591;4782}. La fonction retournera 4 car le nombre est à la 4ème position.
- INDEX(B5:B10B5:B10,,,EQUIV(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4),0))) = INDEX(B5:B10B5:B10,,,4) : La fonction INDEX retourne la 4ème valeur dans la plage de noms B5:B10, qui est Bale.
Fonctions connexes
La fonction INDEX Excel renvoie la valeur affichée en fonction d'une position donnée dans une plage ou un tableau.
La fonction EQUIV Excel recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de la valeur.
La fonction ABS renvoie la valeur absolue d'un nombre. Les nombres négatifs seront convertis en positifs avec cette fonction, mais les nombres positifs et zéro ne seront pas affectés.
Formules connexes
Recherche de la correspondance la plus proche avec plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la correspondance la plus proche ou approximative basée sur plusieurs critères. Avec la combinaison des fonctions INDEX, EQUIV et SI, vous pouvez rapidement y arriver dans Excel.
Correspondance approximative avec INDEX et EQUIV
Il arrive que nous devions trouver des correspondances approximatives dans Excel pour évaluer la performance des employés, noter les scores des étudiants, calculer les frais d'affranchissement en fonction du poids, etc. Dans ce tutoriel, nous parlerons de la façon d'utiliser les fonctions INDEX et EQUIV pour récupérer les résultats dont nous avons besoin.
Recherche de la correspondance la plus proche avec plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la correspondance la plus proche ou approximative basée sur plusieurs critères. Avec la combinaison des fonctions INDEX, EQUIV et SI, vous pouvez rapidement y arriver dans Excel.
Les meilleurs outils de productivité pour Office
Kutools pour Excel - Vous aide à vous démarquer de la foule
Kutools pour Excel compte plus de 300 fonctionnalités, garantissant que ce dont vous avez besoin est à portée d'un clic...
Office Tab - Activer la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour passer entre des dizaines de documents ouverts !
- Réduit de centaines le nombre de clics souris pour vous chaque jour, dites adieu à la main de la souris.
- Augmente votre productivité de 50 % lors de la visualisation et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.