Correspondance approximative pour les tables à tranches

Un tableau de paliers répartit des valeurs continues en tranches. Parmi les exemples, citons les tranches d’imposition, les frais d’expédition selon le poids, les remises selon le volume et les notes en lettres selon le score.

Vous n’avez pas une ligne pour chaque valeur possible, mais uniquement le seuil de départ de chaque tranche. Un score de 87 n’a pas d’entrée exacte, mais il appartient à la tranche qui commence à 80.

C’est là que la correspondance approximative est particulièrement utile : elle trouve la bonne tranche sans exiger une correspondance exacte.

Jusqu’à présent, nous avons utilisé MATCH(value, range, 0) pour une correspondance exacte. Le troisième argument 0 signifie « trouver précisément cette valeur ou renvoyer #N/A ».

Pour les paliers, nous utilisons plutôt le type de correspondance 1. Il trouve la plus grande valeur inférieure ou égale à la valeur recherchée. C’est exactement le comportement attendu pour une recherche de tranche.

Une règle impérative : avec le type de correspondance 1, la liste des seuils doit être triée par ordre croissant.

Imaginez un tableau de notation. La colonne E contient les seuils inférieurs, triés par ordre croissant : 0, 60, 70, 80, 90. La colonne F contient les libellés : F, D, C, B, A.

Un score de 0 à 59 correspond à F, de 60 à 69 à D, et ainsi de suite. Nous enregistrons uniquement le début de chaque tranche, pas chaque score.

Notre objectif : à partir d’un score en G1, renvoyer sa note en lettres.

Utilisez MATCH en mode approximatif pour trouver dans quelle tranche se situe un score. MATCH(G1, E2:E6, 1) avec un score de 87 recherche le seuil le plus élevé qui est inférieur ou égal à 87.

Les seuils sont 0, 60, 70, 80, 90. Le plus grand seuil ne dépassant pas 87 est 80, en position 4. MATCH renvoie donc 4.

Cette position désigne la bonne tranche, même si 87 ne figure pas dans la liste.

Transmettez maintenant la position à INDEX appliqué à la colonne des libellés F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) prend la position 4 et renvoie le quatrième libellé, « B ».

Ainsi, un score de 87 correspond correctement à la note B. Remplacez G1 par 95 : MATCH renvoie 5 et donne « A » ; remplacez-le par 55 : MATCH renvoie 1 et donne « F ».

La fonction MATCH approximative (type 1) exige un ordre croissant dans la plage de recherche. Elle suppose que les données augmentent et s’arrête dès qu’elle dépasse la valeur recherchée.

Si vos seuils ne sont pas dans l’ordre, MATCH peut s’arrêter trop tôt et renvoyer une position erronée, incorrecte sans signalement, sans aucune erreur pour vous avertir. Triez toujours la colonne des seuils du plus petit au plus grand avant d’utiliser une recherche par paliers.

XLOOKUP peut également effectuer une correspondance approximative. Son cinquième argument, le mode de correspondance, accepte -1 pour « correspondance exacte ou élément immédiatement inférieur », ce qui convient parfaitement aux tableaux de paliers.

Cette fonction trouve le seuil le plus élevé qui est inférieur ou égal à G1 et renvoie le libellé correspondant, sans nécessiter INDEX. Pour les recherches de tranches, elle est souvent plus facile à lire que INDEX-MATCH.

Voici maintenant une remise selon le volume. Les seuils en E (quantité commandée) sont : 0, 10, 50, 100. Les remises en F sont : 0 %, 5 %, 10 %, 15 %.

• Commande de 7 : le seuil le plus élevé inférieur ou égal à 7 est 0, en position 1, ce qui renvoie 0 %.
• Commande de 60 : le seuil le plus élevé inférieur ou égal à 60 est 50, en position 3, ce qui renvoie 10 %.
• Commande de 200 : le seuil le plus élevé inférieur ou égal à 200 est 100, en position 4, ce qui renvoie 15 %.

Une seule formule gère toutes les quantités.

Que se passe-t-il si une valeur est inférieure à tous les seuils ? Avec MATCH en mode approximatif, aucune valeur ne lui est inférieure ou égale, et MATCH renvoie donc #N/A.

Pour éviter cela, vérifiez que le premier seuil couvre la limite inférieure (souvent 0), ou entourez la formule de IFERROR afin d’afficher un message clair lorsqu’une donnée saisie est hors plage.

Méfiez-vous de ces pièges des tableaux de paliers :

• Seuils non triés : la première cause de résultats erronés et silencieux.
• Utiliser le type de correspondance 0 : cela impose une correspondance exacte et renvoie #N/A pour toute valeur intermédiaire.
• Enregistrer les fins de tranche au lieu des débuts : MATCH de type 1 attend la borne inférieure de chaque tranche, et non la borne supérieure.
• Seuils textuels : les nombres enregistrés comme texte faussent la comparaison ; conservez-les au format numérique.

Vous pouvez combiner la correspondance approximative avec la technique à deux axes. Imaginez un coût d’expédition déterminé à la fois par une tranche de poids (lignes) et une tranche de zone (colonnes).

Utilisez un MATCH approximatif (type 1) pour trouver la ligne de poids et un autre pour trouver la colonne de zone, puis transmettez les deux positions à INDEX. Comme les deux axes contiennent des seuils triés, chaque MATCH aboutit à la bonne tranche.

Cette méthode combine INDEX-MATCH-MATCH et la logique des paliers pour créer des tableaux tarifaires détaillés.

Pour les recherches de paliers et de tranches :

• Enregistrez le seuil inférieur de chaque tranche, dans l’ordre croissant.
• Utilisez MATCH(value, thresholds, 1) pour trouver la position de la tranche (la plus grande valeur inférieure ou égale à la donnée saisie).
• Entourez-le de INDEX(labels, ...) pour renvoyer la tranche, ou utilisez XLOOKUP(..., -1) pour obtenir le même résultat.

Couvrez la limite inférieure avec un seuil de 0 ou utilisez IFERROR pour les données saisies hors plage, et ne laissez jamais les seuils non triés.