Correspondance approximative bidirectionnelle avec plusieurs critères
Dans ce tutoriel, nous allons parler de la manière de rechercher une correspondance approximative basée sur plusieurs critères listés à la fois dans les colonnes et les lignes d'une feuille de calcul Excel, avec l'aide des fonctions INDEX, MATCH et SI.
Comment effectuer une correspondance approximative bidirectionnelle avec plusieurs critères dans Excel ?
Pour trouver la taille de vêtement pour une femme mesurant 165,5 cm et pesant 55 kg selon le tableau ci-dessus, vous pouvez ajouter deux fonctions MATCH à la formule : l'une avec une fonction SI obtient le numéro de ligne (sexe et taille), l'autre obtient le numéro de colonne (poids). Ensuite, INDEX trouvera la taille correspondante en fonction des coordonnées. (Notez que le numéro de ligne précède le numéro de colonne dans une formule INDEX.)
Syntaxe générique
=INDEX(plage_retour,MATCH(valeur_recherche1,SI(tableau_recherche2=valeur_recherche2,tableau_recherche1),type_correspondance),MATCH(valeur_recherche3,tableau_recherche3,type_correspondance))
√ Remarque : Il s'agit d'une formule matricielle qui nécessite d'être validée avec Ctrl + Maj + Entrée.
- plage_retour : La plage où vous souhaitez que la formule combinée retourne la taille. Ici, cela fait référence à la plage de tailles.
- valeur_recherche : La valeur que la formule utilise pour localiser la position de la taille correspondante. Ici, cela fait référence aux informations concernant le sexe, la taille et le poids donnés.
- tableau_recherche : La plage de cellules contenant les valeurs à comparer avec la valeur_recherche. Ici, cela fait référence aux plages de sexe, de taille et de poids.
- type_correspondance : 1 ou -1.
1 ou omis (par défaut), MATCH trouvera la plus grande valeur inférieure ou égale à la valeur_recherche. Les valeurs dans le tableau_recherche doivent être placées dans l'ordre croissant.
-1, MATCH trouvera la plus petite valeur supérieure ou égale à la valeur_recherche. Les valeurs dans le tableau_recherche doivent être placées dans l'ordre décroissant.
Pour trouver la taille de vêtement pour la femme mesurant 165,5 cm et pesant 55 kg, veuillez copier ou entrer la formule ci-dessous dans la cellule M9, puis appuyez sur Ctrl + Maj + Entrée pour obtenir le résultat :
=INDEX(D5:J16,MATCH(M6,SI(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
Explication de la formule
=INDEX(D5:J16,MATCH(M6,SI(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- SI(B5:B16=M5,C5:C16): La fonction SI vérifie chaque valeur dans B5:B16 si elles correspondent à la valeur dans la cellule M5, Femme. Si c'est le cas, la fonction SI conservera les tailles correspondantes listées dans C5:C16 ; sinon, SI renverra FAUX. La formule retournera un tableau comme celui-ci : {157;160;162.5;165;167.5;170;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX}.
- MATCH(M6,MATCH(M6,SI(B5:B16=M5,C5:C16),1),1) = MATCH(M6,MATCH(M6,{157;160;162.5;165;167.5;170;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX},1),1) : Le type_correspondance 1 force la fonction MATCH à retourner la position de la plus grande valeur inférieure ou égale à 165,5 (la valeur dans la cellule M6) dans le tableau. Ainsi, MATCH retournera 4, la position de la valeur 165.
- MATCH(M7,D4:J4,1): La fonction MATCH retourne 3, car la plus grande valeur inférieure ou égale à la valeur dans M7, 55, est à la 3ème position de la plage D4:J4.
- INDEX(D5:J16D5:J16,MATCH(M6,SI(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1)) = INDEX(D5:J16D5:J16,4,3): La fonction INDEX récupère la valeur à l'intersection de la 4ème ligne et de la 3ème colonne dans la plage de tailles D5:J16, qui est la valeur dans la cellule F8, M.
Fonctions connexes
La fonction INDEX d'Excel renvoie la valeur affichée en fonction d'une position donnée dans une plage ou un tableau.
La fonction MATCH d'Excel recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de cette valeur.
La fonction SI est l'une des fonctions les plus simples et les plus utiles dans un classeur Excel. Elle effectue un test logique simple qui dépend du résultat de la comparaison, et elle renvoie une valeur si le résultat est VRAI, ou une autre valeur si le résultat est FAUX.
Formules connexes
Recherche bidirectionnelle avec INDEX et MATCH
Pour rechercher quelque chose à la fois dans les lignes et les colonnes dans Excel, ou pour rechercher une valeur à l'intersection d'une ligne et d'une colonne spécifiques, nous pouvons utiliser les fonctions INDEX et MATCH.
Recherche de la correspondance la plus proche
Pour rechercher la correspondance la plus proche d'une valeur de recherche dans un jeu de données numérique dans Excel, vous pouvez utiliser les fonctions INDEX, MATCH, ABS et MIN ensemble.
Recherche de la correspondance la plus proche avec plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la correspondance la plus proche ou approximative basée sur plusieurs critères. Avec la combinaison des fonctions INDEX, MATCH et SI, vous pouvez rapidement y parvenir dans Excel.
Recherche avec plusieurs critères avec INDEX et MATCH
Lorsque vous travaillez avec une grande base de données dans une feuille de calcul Excel avec plusieurs colonnes et légendes de lignes, il est toujours difficile de trouver quelque chose qui répond à plusieurs critères. Dans ce cas, vous pouvez utiliser une formule matricielle avec les fonctions INDEX et MATCH.
Correspondance approximative avec INDEX et MATCH
Il arrive parfois que nous devions trouver des correspondances approximatives dans Excel pour évaluer les performances des employés, noter les scores des étudiants, calculer les frais de port en fonction du poids, etc. Dans ce tutoriel, nous expliquerons comment utiliser les fonctions INDEX et MATCH pour récupérer les résultats dont nous avons besoin.
Les meilleurs outils de productivité pour Office
Kutools pour Excel - Vous aide à vous démarquer de la foule
Kutools pour Excel compte plus de 300 fonctionnalités, garantissant que ce dont vous avez besoin est à portée d'un clic...
Office Tab - Activer la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour passer entre des dizaines de documents ouverts !
- Réduit de centaines le nombre de clics souris pour vous chaque jour, dites adieu à la main de la souris.
- Augmente votre productivité de 50 % lors de la visualisation et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.