Si vous utilisez Google Sheets et que vous souhaitez trouver la correspondance la plus proche dans un tableau de valeurs numériques, nous avons la solution pour vous ! Utilisez la combinaison de fonctions Filter, Min et Abs.
La méthode Filter est également populaire parmi les utilisateurs d’Excel, et elle fonctionne également dans Google Sheets. Cependant, elle présente un petit problème : si vous avez deux correspondances proches, une valeur plus élevée et une valeur plus basse, la formule ne renverra que la valeur la plus basse.
Mais avec la combinaison Filter, Min et Abs, vous pouvez obtenir les deux valeurs. Laissez-moi vous expliquer cela avec un exemple ci-dessous.
Formules pour trouver la correspondance la plus proche dans Google Sheets
Données d’exemple : B1:B (où B1 contient le nom de la colonne)
Valeur à évaluer dans les données d’exemple : D2
Formule pour trouver la correspondance la plus proche :
Insérez l’une des formules suivantes dans la cellule E2.
Formule n° 1 (Méthode Filter [Google Sheets]) :
=filter(B2:B,ABS(D2-B2:B)=min(ABS(D2-B2:B)))
Formule n° 2 (Méthode Index-Match [Excel]) :
=index(B2:B10,match(min(ABS(D2-B2:B10)),ABS(D2-B2:B10),0))
Grâce à ces formules, vous pouvez trouver la correspondance la plus proche de la valeur située en D2 dans le tableau B2:B.
Essayez de changer la valeur en D2 en 500 et trouvez la valeur la plus proche en utilisant les deux formules.
La première formule renverra deux valeurs, 400 et 600.
La seconde formule ne renverra qu’une seule valeur, c’est-à-dire 400.
Explications des formules
Formule 1 – Méthode Filter
Il s’agit essentiellement d’une formule FILTER.
Syntaxe : FILTER(plage, condition1, [condition2, …])
Dans cette formule, nous savons que la plage à filtrer est B2:B. Que dire de l’argument condition1 ?
C’est ABS(D2-B2:B)=min(ABS(D2-B2:B))
Nous pouvons diviser cette condition1 en deux parties.
Partie 1 : ABS(D2-B2:B)
Partie 2 : min(ABS(D2-B2:B)) ou nous pouvons dire min(partie_1)
Que retournent les formules partie_1 et partie_2 alors ?
Vous pouvez les tester dans une feuille (l’utilisation d’ArrayFormula avec elles est obligatoire en dehors de Filter).
Test de la partie_1 dans la cellule F2 :
=ArrayFormula(ABS(D2-B2:B))
La partie_1 renvoie la différence entre les chiffres de B2:B et D2, c’est-à-dire D2-B2:B.
Note : J’ai utilisé ABS, c’est-à-dire ABS(D2-B2:B), pour obtenir les nombres absolus (des nombres sans signe négatif).
En enveloppant la formule ci-dessus avec MIN, nous pouvons obtenir la valeur de différence minimale, c’est-à-dire la partie_2.
C’est la clé pour trouver la correspondance la plus proche dans Google Sheets.
Test de la partie_2 dans la cellule G2 :
=ArrayFormula(MIN(ABS(D2-B2:B)))
C’est la troisième valeur dans la plage des cellules F2:F (partie_1).
Si nous filtrons B2:B (plage) en utilisant la condition F2:F=G2 (partie_1=partie_2), nous obtiendrons 50.
De cette manière, nous pouvons trouver la correspondance la plus proche dans Google Sheets.
Formule 2 – Méthode Index-Match
Si vous comprenez comment utiliser la combinaison Filter, Min et Abs pour trouver la correspondance la plus proche dans Google Sheets, la méthode Index-Match est simple à apprendre.
Ici aussi, nous utiliserons les parties_1 et partie_2 mentionnées ci-dessus à des fins d’explication.
Dans cette approche, j’ai utilisé la fonction MATCH pour trouver la position (relative) de la sortie de la partie_2 dans la sortie de la partie_1.
Ensuite, en utilisant INDEX, j’ai réussi à décaler autant de lignes dans B2:B pour obtenir la correspondance la plus proche.
Trouver la correspondance la plus proche dans Google Sheets et son utilisation dans la vie réelle
Par exemple, vous êtes l’un des fournisseurs de matériaux de paysagisme et vous avez un stock de graviers blancs de 20 à 40 mm.
Supposons que le prix unitaire de cet article varie en fonction de la quantité commandée.
Cela signifie que pour une quantité de commande de 10 kg, vous vendez l’article à 1,43 € le kg.
Si la quantité de commande est de 25 kg, alors vous vendez le même article à 1,28 € le kg.
Le tarif unitaire est de 1,14 € pour 50 kg et de 1 € pour 100 kg.
Vous souhaitez trouver le prix unitaire le plus proche lorsque quelqu’un commande 45 kg.
Dans ce cas, vous pouvez utiliser l’une de mes formules ci-dessus, qui trouve la correspondance la plus proche dans Google Sheets. Voici comment faire.
=filter(C2:C,ABS(D2-B2:B)=min(ABS(D2-B2:B)))
La formule trouve la correspondance la plus proche de la quantité et renvoie son prix unitaire.
Ressources
- Trouver la date la plus proche du passé ou du futur par rapport à aujourd’hui dans Google Sheets
- Requête pour filtrer la date la plus proche du futur dans Google Sheets
- Correspondance la plus proche supérieure ou égale à la clé de recherche dans Vlookup dans Google Sheets
- Alternatives à la fonction XMATCH dans Google Sheets
- Alternatives à XLOOKUP dans Google Sheets – Modes de recherche et de correspondance
- Comment rechercher les dernières dates dans Google Sheets [Formule matricielle]
- Extraire l’enregistrement le plus ancien ou le plus récent dans chaque catégorie en fonction de la date et de l’heure
- Recherche de la dernière valeur – Différences entre Excel et Google Sheets
- Formule pour fusionner les lignes et obtenir les dernières valeurs dans Google Sheets
Maintenant que vous savez comment trouver la correspondance la plus proche dans Google Sheets, vous pouvez l’utiliser dans diverses situations pour simplifier votre travail. Appliquez ces formules et voyez les résultats par vous-même !
Alors, qu’attendez-vous ? Essayez-les maintenant et économisez du temps et des efforts précieux dans vos feuilles de calcul Google Sheets !
