Monsieur Excel
Pour tout savoir faire sur Excel !

02 décembre 2006

Trouver le code le proche

Le problème que nous résolvons aujourd’hui est un problème un peu particulier. Vous avez en colonne A des codes, en B des noms, en E une liste de codes de référence. Vous voulez obtenir en colonne C le code de référence le plus proche de celui de la colonne A :

Quelle formule devez-vous entrer en C2, pour la recopier ensuite en C3:C6 ?

Une fois encore, c’est une formule matricielle qui nous permet de réaliser le miracle attendu ! La formule à entrer et à valider, comme toute formule matricielle, avec la combinaison Ctrl-Maj-Entrée, est la suivante :

=index($E$1:$E$9;equiv(petite.valeur(abs($E$1:$E$9-A2);1);abs($E$1:$E$9-A2);0))

Cette formule, il faut l’avouer, n’est pas complètement évidente ! Commentons-la donc pas à pas...

La formule petite.valeur(abs($E$1:$E$9-A2);1) trouve le plus petit écart absolu entre la valeur en A2 et les valeurs de la colonne E.

La formule equiv(...;abs($E$1:$E$9-A2);0) trouve la position dans $E$1:$E$9 où se situe la valeur avec le plus petit écart.

La formule index($E$1:$E$9;...) renvoie la valeur de $E$1:$E$9 qui a le plus petit écart avec A2.

Ainsi que je l'ai déjà dit, les possibilités des formules matricielles sont quasiment illimitées !

2 Commentaire(s):

  • Il est possible de simplifier la formule en 2 étapes :

    PETITE.VALEUR() est intéressant car il permet de trouver outre le plus petit élément d’une liste, le second plus petit, ...

    Toutefois, quand on a seulement besoin du minimum, MIN() suffit.

    D'autre part, on peut définir un nom, "Codes" par exemple; associé à la plage $E$1:$E$9. De manière générale, les noms remplacent avantageusement les références absolues : outre une écriture plus naturelle, la lisibilité des formules est améliorée.

    On aboutit ainsi à la formule suivante :

    =INDEX(Codes;EQUIV(MIN(ABS(Codes - A2));ABS(Codes - A2);0))

    By Anonymous Anonyme, sur 10:40 AM  

  • Bonjour M.Excel,
    merci pour votre excellent site web. Je suis un jeune consultant de Montréal et j'apprécie beaucoup votre partage de connaissances. Je vise d'ailleurs à éventuellement à diffuser sur mon site web mes outils et exemples Excel.

    Je n'aime pas les matricielles, alors je me suis mis au défi de trouver une solution plus simple à ce problème.

    J'ai trouvé une façon de faire qui utilise seulement une fonction (au lieu de 3) et qui ne nécessite pas de matricielle :

    =RECHERCHE((A2+A2-RECHERCHE(A;$E$1:$E$8));$E$1:$E$8)

    Comme la fonction recherche ne trouve que des valeurs égales ou inférieures, je recherche l'item de la colonne A + l'écart entre A et ce qu'une recherche normale trouve.

    Par exemple:
    A___B
    5___1
    ____6
    ___10

    Une fonction recherche donnera 1 comme résultat. Nous savons toutefois que 6 est plus près de 5. Alors si je fais une recherche de (5 + (5-1)), le résultat sera 6... qui est la bonne réponse

    By Anonymous Anonyme, sur 4:15 PM  

Enregistrer un commentaire

<< Accueil