Rechercher la correspondance la plus proche
Pour rechercher la correspondance la plus proche d’une valeur dans un ensemble de données numériques sous Excel, combinez les fonctions INDEX, EQUIV, ABS et MIN.
Comment trouver la correspondance la plus proche dans Excel ?
Pour identifier le commercial ayant réalisé le chiffre d’affaires le plus proche de l’objectif fixé à 20 000 $, comme illustré ci-dessus, combinez les fonctions INDEX, EQUIV, ABS et MIN de la manière suivante : la fonction ABS transforme en valeurs positives toutes les différences entre les ventes réalisées par chaque commercial et l’objectif ; ensuite, la fonction MIN repère la plus petite de ces différences — c’est-à-dire la performance la plus proche de la cible. Grâce à la fonction EQUIV, vous localisez alors la position de cette valeur minimale, puis la fonction INDEX vous permet d’extraire le nom du commercial correspondant.
Syntaxe générique
=INDEX()return_range,MATCH(MIN(ABS()))lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ Remarque : Il s’agit d’une formule matricielle que vous devez valider en appuyant sur Ctrl+Maj+Entrée.
- plage_de_retour : La plage dans laquelle vous souhaitez que la formule combinée renvoie le nom du commercial. Il s’agit ici de la plage contenant les noms.
- plage_recherche : La plage de cellules contenant les valeurs à comparer avec la valeur_recherchée. Il s’agit ici de la plage des ventes.
- valeur_recherchée : La valeur à comparer pour trouver sa correspondance la plus proche. Ici, il s’agit de l’objectif de vente.
Pour identifier le commercial ayant réalisé le chiffre d’affaires le plus proche de l’objectif fixé à $20,000, veuillez copier ou saisir la formule ci-dessous dans la cellule F5, puis appuyer sur Ctrl+Maj+Entréepour obtenir le résultat :
=INDEX()B5:B10,EQUIV(MIN(ABS()))C5:C10-20000)),ABS(C5:C10-20000),0))
Vous pouvez également utiliser 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,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- ABS(C5:C10-F4):La partie C5:C10-F4 calcule toutes les différences entre chaque chiffre d’affaires de la plage C5:C10 et l’objectif de ventes $20,000, situé en cellule F4, sous forme de tableau comme suit : {-4322;2451;6931;-1113;6591;-4782}. La fonction ABS transforme tous les nombres négatifs en positifs, ce qui donne : {4322;2451;6931;1113;6591;4782}.
- MIN()ABS(C5:C10-F4))=MIN(){4322;2451;6931;1113;6591;4782}):La fonction MIN identifie la plus petite valeur du tableau {4322;2451;6931;1113;6591;4782}, soit la différence minimale — autrement dit, la correspondance la plus proche. Elle renvoie donc 1113.
- EQUIV()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)Le paramètre type_correspondance 0 oblige la fonction EQUIV à rechercher la position exacte du nombre 1113 dans le tableau {4322;2451;6931;1113;6591;4782}. La fonction renvoie donc 4, puisque ce nombre se trouve à la 4e position.
- INDEX()B5:B10,EQUIV()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)La fonction INDEX renvoie la 4e valeur de la plage de noms B5:B10, soit Bale.
Fonctions associées
La fonction INDEX d’Excel renvoie la Valeur affichée à partir d’une position donnée dans une plage ou un tableau.
La fonction EQUIV d’Excel recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de cette valeur.
La fonction ABS renvoie la Valeur absolue d’un nombre. Les nombres négatifs sont convertis en positifs par cette fonction, tandis que les nombres positifs et zéro restent inchangés.
Formules associées
Rechercher la valeur correspondante la plus proche selon plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la valeur correspondante la plus proche ou approximative en vous basant sur plusieurs critères. En combinant les fonctions INDEX, EQUIV et SI, vous pouvez obtenir rapidement ce résultat 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, attribuer des notes aux étudiants, calculer les frais postaux en fonction du poids, etc. Dans ce tutoriel, nous expliquons comment utiliser les fonctions INDEX et EQUIV pour obtenir les résultats souhaités.
Rechercher la valeur correspondante la plus proche selon plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la valeur correspondante la plus proche ou approximative selon plusieurs critères. En combinant les fonctions INDEX, EQUIV et SI, vous obtenez ce résultat rapidement dans Excel.
Les meilleurs outils de productivité Office
Kutools pour Excel – Vous permet de vous démarquer de la foule
Kutools pour Excel propose plus de 300 fonctionnalités,garantissant que ce dont vous avez besoin est toujours à portée de clic...
Office Tab – Active la lecture et l’édition par onglets dans Microsoft Office (y compris Excel)
- Passez d’un document à l’autre en une seconde, même avec des dizaines de documents ouverts !
- Réduisez de centaines vos clics de souris chaque jour et dites adieu au syndrome de la main de la souris.
- Augmentez votre productivité de 50 % en consultant et en modifiant simultanément plusieurs documents.
- Apporte à Office (y compris Excel) une efficacité comparable à celle des navigateurs tels que Chrome, Edge et Firefox.