Sommaire
Pour effectuer une recherche de correspondance approximative bidirectionnelle avec plusieurs critères, vous pouvez utiliser une formule matricielle basée sur INDEX et MATCH, avec l'aide de la fonction IF pour appliquer des critères. Dans l'exemple illustré, la formule en K8 est:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
où les données (D6: H16), le diamètre (D5: H5), le matériau (B6: B16) et la dureté (C6: C16) sont des plages nommées utilisées à des fins de commodité uniquement.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée
Explication
Le but est de rechercher une vitesse d'avance basée sur le matériau, la dureté et le diamètre du foret. Les valeurs de vitesse d'avance sont dans les données de plage nommées (D6: H16).
Cela peut être fait avec une formule INDEX et MATCH bidirectionnelle. Une fonction MATCH calcule le numéro de ligne (matériau et dureté) et l'autre fonction MATCH trouve le numéro de colonne (diamètre). La fonction INDEX renvoie le résultat final.
Dans l'exemple illustré, la formule en K8 est:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Sauts de ligne ajoutés pour la lisibilité uniquement).
Le plus délicat est que le matériau et la dureté doivent être traités ensemble. Nous devons limiter MATCH aux valeurs de dureté pour un matériau donné (acier à faible teneur en carbone dans l'exemple illustré).
Nous pouvons le faire avec la fonction IF. Essentiellement, nous utilisons IF pour «jeter» les valeurs non pertinentes avant de rechercher une correspondance.
Détails
La fonction INDEX reçoit les données de plage nommées (D6: H16) comme pour le tableau. La première fonction MATCH détermine le numéro de ligne:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Pour localiser la bonne rangée, nous devons faire une correspondance exacte sur le matériau et une correspondance approximative sur la dureté. Nous faisons cela en utilisant la fonction IF pour filtrer d'abord la dureté non pertinente:
IF(material=K5,hardness) // filter
Nous testons toutes les valeurs du matériau (B6: B16) pour voir si elles correspondent à la valeur de K5 ("Low Carbon Steel"). Si tel est le cas, la valeur de dureté est transmise. Sinon, IF renvoie FALSE. Le résultat est un tableau comme celui-ci:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Notez que les seules valeurs qui subsistent sont celles associées à l'acier à faible teneur en carbone. Les autres valeurs sont désormais FALSE. Ce tableau est renvoyé directement à la fonction MATCH en tant que lookup_array.
La valeur de recherche pour la correspondance provient de K6, qui contient la dureté donnée, 176. MATCH est configuré pour une correspondance approximative en définissant match_type sur 1. Avec ces paramètres, MATCH ignore les valeurs FALSE et renvoie la position d'une correspondance exacte ou la plus petite valeur suivante .
Remarque: les valeurs de dureté doivent être triées par ordre croissant pour chaque matériau.
Avec une dureté de 176, MATCH renvoie 6, livré directement à INDEX comme numéro de ligne. Nous pouvons maintenant réécrire la formule originale comme ceci:
=INDEX(data,6,MATCH(K7,diameter,1))
La deuxième formule MATCH trouve le numéro de colonne correct en effectuant une correspondance approximative sur le diamètre:
MATCH(K7,diameter,1) // get column num
Remarque: les valeurs de diamètre D5: H5 doivent être triées par ordre croissant.
La valeur de recherche provient de K7 (0,75) et lookup_array est le diamètre de plage nommé (D5: H5).
Comme précédemment, MATCH est défini sur une correspondance approximative en définissant match_type sur 1.
Avec un diamètre donné à 0,75, MATCH renvoie 3, livré directement à la fonction INDEX comme numéro de colonne. La formule originale se résout maintenant à:
=INDEX(data,6,3) // returns 0.015
INDEX renvoie un résultat final de 0,015, la valeur de F11.
