Exceller

Indexer et faire correspondre sur plusieurs colonnes

Index Match Multiple Columns

Formule Excel: index et correspondance sur plusieurs colonnesFormule générique
{= INDEX (rng1, MATCH (1, MMULT (--(rng2=critera), TRANSPOSE ( COLUMN (rng2)^0)),0))}
Résumé

Pour rechercher une valeur en faisant correspondre plusieurs colonnes, vous pouvez utiliser un formule matricielle basé sur MMULT, TRANSPOSE, COLUMN et INDEX. Dans l'exemple illustré, la formule en H4 est:

 
{= INDEX (groups, MATCH (1, MMULT (--(names=G4), TRANSPOSE ( COLUMN (names)^0)),0))}

où 'names' est le plage nommée C4: E7, et «groupes» est la plage nommée B4: B7. La formule renvoie le groupe auquel appartient chaque nom.



comment appliquer plusieurs filtres dans Excel

Remarque: il s'agit d'un formule matricielle et doit être entré avec entrée de changement de commande.



Explication

En travaillant de l'intérieur vers l'extérieur, les critères logiques utilisés dans cette formule sont:

 
--(names=G4)

où names est la plage nommée C4: E7. Cela génère un résultat TRUE / FALSE pour chaque valeur dans les données, et le double négatif contraint les valeurs TRUE FALSE à 1 et 0 pour donner un tableau comme celui-ci:



 
{0,0,01,0,00,0,00,0,0}

Ce tableau est composé de 4 lignes par 3 colonnes, correspondant à la structure des «noms».

Un deuxième tableau est créé avec cette expression:

 
 TRANSPOSE ( COLUMN (names)^0))

La fonction COLUMN est utilisée pour créer un tableau numérique avec 3 colonnes et 1 ligne, et TRANSPOSE convertit ce tableau en 1 colonne et 3 lignes. Élever à la puissance zéro convertit simplement tous les nombres du tableau en 1. La fonction MMULT est ensuite utilisée pour effectuer la multiplication matricielle:



comment utiliser les volets figés dans Excel
 
 MMULT ({0,0,01,0,00,0,00,0,0},{111})

et le résultat entre dans la fonction MATCH en tant que tableau, avec 1 comme valeur de recherche:

compter le nombre de fois où un mot apparaît dans Excel
 
 MATCH (1,{0100},0)

La fonction MATCH renvoie la position de la première correspondance, qui correspond à la ligne de la première ligne correspondante répondant aux critères fournis. Ceci est alimenté dans INDEX comme numéro de ligne, avec la plage nommée `` groupes '' comme tableau:

 
= INDEX (groups,2)

Enfin, INDEX renvoie «Bear», le groupe auquel Adam appartient.

Le littéral contient pour les critères

Pour vérifier des valeurs de texte spécifiques au lieu d'une correspondance exacte, vous pouvez utiliser les fonctions ISNUMBER et SEARCH ensemble. Par exemple, pour faire correspondre les cellules contenant «pomme», vous pouvez utiliser:

 
= ISNUMBER ( SEARCH ('apple',data))

Cette formule est expliquée ici .

Auteur Dave Bruns


^