Correspondance approximative bidimensionnelle avec plusieurs critères
Dans ce tutoriel, nous vous expliquons comment effectuer une recherche approximative selon plusieurs critères présents à la fois dans les colonnes et les lignes d’une feuille Excel, à l’aide des fonctions INDEX, EQUIV et SI.
Comment effectuer une correspondance approximative bidimensionnelle selon plusieurs critères dans Excel ?
Pour déterminer la taille vestimentaire d’une femme mesurant 165,5 cm et pesant 55 kg à l’aide du tableau ci-dessus, combinez deux fonctions EQUIV à votre formule : l’une, associée à la fonction SI, permet d’obtenir le numéro de ligne (sexe et taille), tandis que l’autre fournit le numéro de colonne (poids). La fonction INDEXUtilisez ensuite ces coordonnées pour identifier la taille correspondante. (Notez que, dans une formule)INDEX, le numéro de ligne précède toujours le numéro de colonne.)
Syntaxe générique
=INDEX()return_range,MATCH()lookup_value1,IF()lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Remarque : Il s’agit d’une formule matricielle que vous devez valider en appuyant sur Ctrl+Maj+Entrée.
- plage_de_retour : La plage à partir de laquelle vous souhaitez que la formule combinée renvoie les tailles. Il s’agit ici de la plage des tailles.
- valeur_recherchée : La valeur utilisée par la formule pour déterminer la position de la taille correspondante. Il s’agit ici des informations relatives au sexe, à la taille et au poids indiquées.
- plage_recherche : La plage de cellules contenant les valeurs à comparer avec la valeur_recherchée. Il s’agit ici des plages relatives au sexe, à la taille et au poids.
- type_correspondance : 1 ou -1.
1 ou omis (valeur par défaut) : la fonction EQUIV recherche la plus grande valeur qui est inférieure ou égale à la valeur_recherchée. Les valeurs de la plage_recherche doivent être triées en ordre croissant.
-1 : EQUIV recherche la plus petite valeur qui est supérieure ou égale à la valeur_recherchée. Les valeurs de la plage_recherche doivent être triées en ordre décroissant.
Pour déterminer la taille vestimentairede la femmemesurant 165,5cm et pesant 55kg, veuillez copier ou saisir la formule ci-dessous dans la cellule M9, puis appuyer sur Ctrl+Maj+Entréepour obtenir le résultat :
=INDEX()D5:J16;EQUIV()M6;SI()B5:B16=M5;C5:C16);1);EQUIV(M7;D4:J4;1))
Explication de la formule
=INDEX()D5:J16,MATCH(M6,)IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- SI(B5:B16=M5;C5:C16):La fonction SI examine chaque valeur de la plage B5:B16 pour vérifier si elle correspond à la valeur de la cellule M5, soit Femme. Lorsque c’est le cas, elle conserve les tailles correspondantes issues de la plage C5:C16 ; dans le cas contraire, elle renvoie FAUX. Le résultat est un tableau comme celui-ci : {157;160;162,5;165;167,5;170;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX}.
- EQUIV(M6;)SI(B5:B16=M5;C5:C16);1)=EQUIV(M6;){157;160;162,5;165;167,5;170;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX};1):Le type_correspondance 1oblige la fonction EQUIV à renvoyer la position de la plus grande valeur inférieure ou égale à 165,5(la valeur située dans la cellule)M6) dans le tableau. Ainsi, EQUIV renvoie 4, la position de la valeur 165.
- EQUIV(M7;D4:J4;1):La fonction EQUIV renvoie 3, car la plus grande valeur inférieure ou égale à celle située dans M7 (55) se trouve à la 3e position de la plage D4:J4.
- INDEX()D5:J16;EQUIV(M6;)SI(B5:B16=M5;C5:C16);1);EQUIV(M7;D4:J4;1)) = INDEX(D5:J16:La fonction INDEX récupère la valeur située à l’intersection de la 4e ligne et de la 3e colonne dans la plage D5:J16, soit la valeur contenue dans la cellule F8,M.
Fonctions associées
La fonction INDEX d’Excel renvoie la valeur affichée à une position donnée dans une plage ou un tableau.
La fonction EQUIV d’Excel recherche une valeur précise dans une plage de cellules et renvoie sa position relative.
La fonction SI est l’une des plus simples et des plus utiles d’Excel. Elle effectue un test logique simple et renvoie une valeur si le résultat est VRAI, ou une autre si le résultat est FAUX.
Formules associées
Recherche bidimensionnelle avec INDEX et EQUIV
Pour rechercher une information à la fois dans les lignes et les colonnes d’Excel — autrement dit, pour trouver la valeur située à l’intersection d’une ligne et d’une colonne précises — combinez intelligemment les fonctions INDEX et EQUIV.
Rechercher la correspondance la plus proche
Pour trouver la correspondance la plus proche d’une valeur dans un ensemble de données numériques sous Excel, combinez les fonctions INDEX, EQUIV, ABS et MIN.
Rechercher la valeur correspondant le plus près selon plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la valeur correspondant le plus près ou de manière approximative en vous basant sur plusieurs critères. En combinant les fonctions INDEX, EQUIV et SI, vous pouvez obtenir rapidement ce résultat sous Excel.
Recherche multicritères avec INDEX et EQUIV
Lorsque vous travaillez avec une grande base de données dans une feuille Excel comportant de nombreuses colonnes et légendes de lignes, il est souvent difficile de trouver une entrée répondant à plusieurs critères. Dans ce cas, vous pouvez utiliser une formule matricielle combinant les fonctions INDEX et EQUIV.
Correspondance approximative avec INDEX et EQUIV
Il arrive que nous ayons besoin de trouver des correspondances approximatives dans Excel, par exemple pour évaluer la performance des employés, attribuer des notes aux élèves ou calculer les frais postaux en fonction du poids. Dans ce tutoriel, nous expliquons comment utiliser les fonctions INDEX et EQUIV pour obtenir les résultats souhaités.
Les meilleurs outils de productivité Office
Kutools pour Excel – Vous permet de vous démarquer de la foule
Kutools pour Excel propose plus de 300 fonctionnalités,garantissant que ce dont vous avez besoin est toujours à portée de clic...
Office Tab – Active la lecture et l’édition par onglets dans Microsoft Office (y compris Excel)
- Passez d’un document à l’autre en une seconde, même avec des dizaines de documents ouverts !
- Réduisez de centaines vos clics de souris chaque jour et dites adieu au syndrome de la main de la souris.
- Augmentez votre productivité de 50 % en consultant et en modifiant simultanément plusieurs documents.
- Apporte à Office (y compris Excel) une efficacité comparable à celle des navigateurs tels que Chrome, Edge et Firefox.