Exceller

INDEX et MATCH avec plusieurs critères

Index Match With Multiple Criteria

Formule Excel: INDEX et MATCH avec plusieurs critèresFormule générique
{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}
Résumé

Pour rechercher des valeurs avec INDEX et MATCH, en utilisant plusieurs critères, vous pouvez utiliser une formule matricielle. Dans l'exemple illustré, la formule en H8 est:

 
{= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

Remarque: il s'agit d'un formule matricielle , et doit être entré avec Ctrl + Maj + Entrée, sauf dans Excel 365 .



Explication

C'est une formule plus avancée. Pour les bases, voir Comment utiliser INDEX et MATCH .



comment ajouter des jours dans Excel

Normalement, une formule INDEX MATCH est configurée avec MATCH défini pour parcourir une plage d'une colonne et fournir une correspondance basée sur des critères donnés. Sans concaténer les valeurs dans un colonne d'assistance , ou dans la formule elle-même, il n'y a aucun moyen de fournir plus d'un critère.

Cette formule contourne cette limitation en utilisant logique booléenne pour créer un déployer de uns et de zéros pour représenter les lignes correspondant aux 3 critères, puis en utilisant MATCH pour correspondre au premier 1 trouvé. Le tableau temporaire de uns et de zéros est généré avec cet extrait:



 
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

Ici, nous comparons l'article en H5 à tous les articles, la taille en H6 à toutes les tailles et la couleur en H7 à toutes les couleurs. Le résultat initial est trois tableaux de résultats TRUE / FALSE comme ceci:

 
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

Conseil: utilisez F9 pour voir ces résultats . Sélectionnez simplement une expression dans la barre de formule et appuyez sur F9.

L'opération mathématique (multiplication) transforme les valeurs TRUE FALSE en 1s et 0s:



à quoi sert la mise en forme conditionnelle dans Excel
 
{1110001}*{0010010}*{1010001}

Après multiplication, nous avons un seul tableau comme celui-ci:

 
{0010000}

qui est introduit dans la fonction MATCH comme tableau de recherche, avec une valeur de recherche de 1:

 
 MATCH (1,{0010000})

À ce stade, la formule est une formule INDEX MATCH standard. La fonction MATCH renvoie 3 à INDEX:

 
= INDEX (E5:E11,3)

et INDEX renvoie un résultat final de 17,00 $.

Visualisation des tableaux

Les tableaux expliqués ci-dessus peuvent être difficiles à visualiser. L'image ci-dessous montre l'idée de base. Les colonnes B, C et D correspondent aux données de l'exemple. La colonne F est créée en multipliant les trois colonnes ensemble. C'est le tableau transmis à MATCH.

INDEX et MATCH avec plusieurs critères - visualisation de tableau

Version sans baie

Il est possible d'ajouter un autre INDEX à cette formule, en évitant d'avoir à entrer sous forme de formule matricielle avec Ctrl + Maj + Entrée:

 
= INDEX (rng1, MATCH (1, INDEX ((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))

La fonction INDEX peut gérer les tableaux de manière native, de sorte que le deuxième INDEX est ajouté uniquement pour «attraper» le tableau créé avec l'opération logique booléenne et renvoyer le même tableau à MATCH. Pour ce faire, INDEX est configuré avec zéro ligne et une colonne. L'astuce de la ligne zéro oblige INDEX à renvoyer la colonne 1 du tableau (qui est déjà une colonne de toute façon).

Pourquoi voudriez-vous la version sans baie? Parfois, les gens oublient d'entrer une formule matricielle avec Ctrl + Maj + Entrée, et la formule renvoie un résultat incorrect. Ainsi, une formule sans matrice est plus «à l'épreuve des balles». Cependant, le compromis est une formule plus complexe.

Remarque: dans Excel 365 , il n'est pas nécessaire de saisir des formules matricielles d'une manière spéciale.

comment calculer un ratio dans excel
Pièces jointes Déposer INDEX et MATCH avec plusieurs critères.xlsx Auteur Dave Bruns


^