Trouver la valeur la plus proche dans Excel

Trouver la valeur la plus proche dans Excel
Dernière mise à jour le 05/02/2024
Temps de lecture : 3 minutes

Quelle formule va trouver la valeur la plus proche dans Excel ? Plusieurs situations, et donc plusieurs formules présentées dans cet article.

Trouver la valeur la plus proche

Soit une plage de valeurs non triées en colonne A. Si vous désirez connaître la valeur la plus proche suivez l'exemple ci-dessous.

Liste de données non triées

La formule est un peu complexe et surtout c'est une fonction matricielle.

  • Si vous ne travaillez pas avec Excel 365, la validation des formules suivantes doit se faire avec les touches Ctrl + Shift + Entrée simultanément.
  • Mais si vous travaillez avec Excel 365, valider avec Entrée suffit car les fonctions matricielles sont automatiquement interprétées.

=MIN(SI(ABS(A3:A11-D2)=MIN(ABS(A3:A11-D2));A3:A11))

Valeur la plus proche

Comprendre la formule

Pour comprendre la formule, il faut se servir du raccourci-clavier F9 pour analyser chaque partie de la formule.

Le résultat de l'instruction ABS(A3:A11-D2) effectue la différence entre toutes les valeurs de la colonne A avec la valeur à rechercher en D2.

Détail de la première partie de la formule pour trouver la valeur la plus proche

Donc, la valeur qui se rapproche le plus de 0 est nécessairement la valeur la plus proche dans notre liste. Et cette valeur est obtenue avec l'instruction MIN(ABS(A3:A11-D2))

Finalement, la formule va retourner la valeur correspondant à la position de cette valeur dans la liste initiale.

Position de la valeur dans la plage de données

Pour trouver la position où se situe la valeur obtenue par la formule précédente, nous allons tout simplement utiliser la fonction EQUIV.

=EQUIV(D4;A3:A11;0)

Formule pour trouver la position de la valeur la plus proche dans la liste initiale

ATTENTION : C'est bien le résultat de la formule précédente qu'il faut passer en premier paramètre.

Numéro de ligne de cette valeur

Si maintenant vous souhaitez connaître la ligne d'Excel dans laquelle se trouve cette valeur, nous allons écrire la formule suivante.

=INDEX(LIGNE(A3:A11);D5)

Fonction pour trouver la ligne dans Excel

Test avec une autre valeur

Si maintenant nous voulons rechercher une autre valeur, automatiquement, les formules vont s'adapter.

Les formules retournent de nouvelles valeurs en fonction de la nouvelle valeur à trouver

Formule pour trouver la valeur supérieure la plus proche

Pour trouver la valeur supérieure la plus proche, nous allons utiliser la fonction EQUIVX. Cette fonction n'est accessible qu'avec Excel 365 et Excel Online.

Ici, nous allons supplement positionner à 1 le troisième paramètre de la fonction EQUIVX pour trouver la valeur supérieure la plus proche

=INDEX(A3:A11;EQUIVX(D2;A3:A11;1))

Valeur superieure la plus proche

Formule pour trouver la valeur inférieure la plus proche

Et pour trouver la valeur inférieure la plus proche, il suffit de positionner à -1 le troisième paramètre de la fonction EQUIVX.

=INDEX(A3:A11;EQUIVX(D2;A3:A11;1))

Valeur inferieure la plus proche

4 Comments

  1. Jack
    24/11/2023 @ 16:43

    Bonjour Frédéric,

    Merci pour cet exercice très intéressant.
    Qu'est ce que ça doit donner si deux valeurs sont identiques ou si elles ont le même écart que la valeur recherchée ?
    Par exemple si la valeur recherchée est 14 comme dans l'exercice mais que l'on a 15 en ligne 2 et en ligne 7 ou bien 15 en ligne 2 et 13 en ligne 7.

    Cordialement

    Reply

  2. Pierre
    07/02/2023 @ 20:16

    Bonsoir,

    J'ai une valeur de référence par ligne, sur une colonne et deux valeurs par ligne sur deux colonnes. Je dois trouver laquelle des deux valeurs se rapproche le plus de ma valeur de référence.
    Seriez vous, svp, m'aider sur ce sujet ?

    Merci et bonne fin de journée.

    Reply

  3. Antoine
    09/01/2022 @ 10:50

    Bonjour, je cherche quelque chose dans ce genre, mais avec une valeur juste supérieure au nombre recherché. Merci

    Reply

    • Frédéric LE GUEN
      09/01/2022 @ 12:42

      Je viens de mettre à jour l'article avec la réponse à votre question

      Reply

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

MVP_logo_2017

Trouver la valeur la plus proche dans Excel

Reading time: 3 minutes
Dernière mise à jour le 05/02/2024

Quelle formule va trouver la valeur la plus proche dans Excel ? Plusieurs situations, et donc plusieurs formules présentées dans cet article.

Trouver la valeur la plus proche

Soit une plage de valeurs non triées en colonne A. Si vous désirez connaître la valeur la plus proche suivez l'exemple ci-dessous.

Liste de données non triées

La formule est un peu complexe et surtout c'est une fonction matricielle.

  • Si vous ne travaillez pas avec Excel 365, la validation des formules suivantes doit se faire avec les touches Ctrl + Shift + Entrée simultanément.
  • Mais si vous travaillez avec Excel 365, valider avec Entrée suffit car les fonctions matricielles sont automatiquement interprétées.

=MIN(SI(ABS(A3:A11-D2)=MIN(ABS(A3:A11-D2));A3:A11))

Valeur la plus proche

Comprendre la formule

Pour comprendre la formule, il faut se servir du raccourci-clavier F9 pour analyser chaque partie de la formule.

Le résultat de l'instruction ABS(A3:A11-D2) effectue la différence entre toutes les valeurs de la colonne A avec la valeur à rechercher en D2.

Détail de la première partie de la formule pour trouver la valeur la plus proche

Donc, la valeur qui se rapproche le plus de 0 est nécessairement la valeur la plus proche dans notre liste. Et cette valeur est obtenue avec l'instruction MIN(ABS(A3:A11-D2))

Finalement, la formule va retourner la valeur correspondant à la position de cette valeur dans la liste initiale.

Position de la valeur dans la plage de données

Pour trouver la position où se situe la valeur obtenue par la formule précédente, nous allons tout simplement utiliser la fonction EQUIV.

=EQUIV(D4;A3:A11;0)

Formule pour trouver la position de la valeur la plus proche dans la liste initiale

ATTENTION : C'est bien le résultat de la formule précédente qu'il faut passer en premier paramètre.

Numéro de ligne de cette valeur

Si maintenant vous souhaitez connaître la ligne d'Excel dans laquelle se trouve cette valeur, nous allons écrire la formule suivante.

=INDEX(LIGNE(A3:A11);D5)

Fonction pour trouver la ligne dans Excel

Test avec une autre valeur

Si maintenant nous voulons rechercher une autre valeur, automatiquement, les formules vont s'adapter.

Les formules retournent de nouvelles valeurs en fonction de la nouvelle valeur à trouver

Formule pour trouver la valeur supérieure la plus proche

Pour trouver la valeur supérieure la plus proche, nous allons utiliser la fonction EQUIVX. Cette fonction n'est accessible qu'avec Excel 365 et Excel Online.

Ici, nous allons supplement positionner à 1 le troisième paramètre de la fonction EQUIVX pour trouver la valeur supérieure la plus proche

=INDEX(A3:A11;EQUIVX(D2;A3:A11;1))

Valeur superieure la plus proche

Formule pour trouver la valeur inférieure la plus proche

Et pour trouver la valeur inférieure la plus proche, il suffit de positionner à -1 le troisième paramètre de la fonction EQUIVX.

=INDEX(A3:A11;EQUIVX(D2;A3:A11;1))

Valeur inferieure la plus proche

4 Comments

  1. Jack
    24/11/2023 @ 16:43

    Bonjour Frédéric,

    Merci pour cet exercice très intéressant.
    Qu'est ce que ça doit donner si deux valeurs sont identiques ou si elles ont le même écart que la valeur recherchée ?
    Par exemple si la valeur recherchée est 14 comme dans l'exercice mais que l'on a 15 en ligne 2 et en ligne 7 ou bien 15 en ligne 2 et 13 en ligne 7.

    Cordialement

    Reply

  2. Pierre
    07/02/2023 @ 20:16

    Bonsoir,

    J'ai une valeur de référence par ligne, sur une colonne et deux valeurs par ligne sur deux colonnes. Je dois trouver laquelle des deux valeurs se rapproche le plus de ma valeur de référence.
    Seriez vous, svp, m'aider sur ce sujet ?

    Merci et bonne fin de journée.

    Reply

  3. Antoine
    09/01/2022 @ 10:50

    Bonjour, je cherche quelque chose dans ce genre, mais avec une valeur juste supérieure au nombre recherché. Merci

    Reply

    • Frédéric LE GUEN
      09/01/2022 @ 12:42

      Je viens de mettre à jour l'article avec la réponse à votre question

      Reply

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.