Imaginez que vous devez trouver la correspondance la plus proche d’une valeur donnée dans un tableau de valeurs numériques dans Google Sheets. Eh bien, ne vous inquiétez pas, j’ai une solution pour vous ! Dans cet article, je vais vous montrer comment utiliser les fonctions Filter, Min et Abs pour obtenir la correspondance la plus proche dans Google Sheets.
Formules pour trouver la correspondance la plus proche dans Google Sheets
Commençons par les formules pour trouver la correspondance la plus proche dans Google Sheets. Voici les étapes à suivre :
- 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 (Approche Filter [Google Sheets]):
=filter(B2:B,ABS(D2-B2:B)=min(ABS(D2-B2:B)))
Formule n° 2 (Approche Index-Match [Excel]):
=index(B2:B10,match(min(ABS(D2-B2:B10)),ABS(D2-B2:B10),0))
En utilisant ces formules, vous pouvez trouver la correspondance la plus proche de la valeur dans la cellule D2 dans le tableau B2:B. Par exemple, si vous changez la valeur de D2 en 500, les deux formules retourneront 400 et 600 comme correspondances les plus proches. Cependant, la première formule retournera les deux correspondances, tandis que la deuxième formule n’en retournera qu’une seule, à savoir 400.
Maintenant que nous avons vu les formules, passons à une explication plus détaillée.
Explication de la Formule 1 – Approche Filter
La formule n° 1 est essentiellement une formule FILTER. Voici sa syntaxe : FILTER(plage, condition1, [condition2, ...]). Dans ce cas, la plage à filtrer est B2:B. Mais qu’en est-il de l’argument condition1 ? Il s’agit de 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 diremin(partie_1)
Que retournent ces formules partie_1 et partie_2 ?
Nous pouvons les tester dans une feuille de calcul (l’utilisation d’ArrayFormula en dehors de Filter est nécessaire).
Test de la partie_1 dans la cellule F2 :
=ArrayFormula(ABS(D2-B2:B))
La partie_1 retourne la différence entre les nombres de B2:B et D2, c’est-à-dire D2-B2:B. Notez que j’ai utilisé ABS pour obtenir les nombres absolus (sans le signe -).
En enveloppant la formule ci-dessus avec MIN, nous pouvons obtenir la plus petite différence de valeur, 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)))
Sa sortie est 10, qui est le troisième nombre dans la plage F2:F (partie_1). Si nous filtrons B2:B (plage) en utilisant la condition F2:F=G2 (partie_1=partie_2), nous obtiendrons 50. C’est ainsi que nous pouvons trouver la correspondance la plus proche dans Google Sheets.
Explication de la Formule 2 – Approche Index-Match
Si vous avez compris comment utiliser la combinaison Filter, Min et Abs pour trouver la correspondance la plus proche dans Google Sheets, la méthode Index-Match pour faire la même chose est simple à comprendre.
Ici aussi, nous utiliserons les parties_1 et parties_2 ci-dessus à des fins d’explication. Dans cette approche, j’ai utilisé la fonction MATCH pour trouver la position (relative) de la sortie de partie_2 dans la sortie de 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
Prenons un exemple concret. Supposons que vous soyez l’un des fournisseurs de matériaux pour l’aménagement paysager et que vous ayez un stock de gravier blanc de 20 à 40 mm. Le prix unitaire de cet article varie en fonction de la quantité commandée. Par exemple, pour une quantité de commande de 10 kg, vous vendez l’article à 1,43 € par kg. Si la quantité de commande est de 25 kg, vous vendez le même article à 1,28 € par kg. Le tarif unitaire est de 1,14 € pour 50 kg et de 1 € pour 100 kg. Vous voulez 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 pour trouver la correspondance la plus proche dans Google Sheets. Voici comment :
=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.
Conclusion
Voilà, vous savez maintenant comment trouver la correspondance la plus proche dans Google Sheets en utilisant les fonctions Filter, Min et Abs. Cette technique peut être très utile dans de nombreuses situations, notamment lors du traitement de données numériques. Alors n’hésitez pas à l’essayer et à l’adapter à vos propres besoins. Pour plus de conseils et d’astuces sur Google Sheets, vous pouvez visiter Crawlan.com.
