Exceller

Extraire plusieurs correspondances dans des colonnes séparées

Extract Multiple Matches Into Separate Columns

Formule Excel: extraire plusieurs correspondances dans des colonnes séparéesRésumé

Pour extraire plusieurs correspondances dans des cellules séparées, dans des colonnes séparées, vous pouvez utiliser une formule matricielle basée sur INDEX et SMALL. Dans l'exemple illustré, la formule en F5 est:

 
{= IFERROR ( INDEX (names, SMALL ( IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1), COLUMNS ($E:E5))),'')}

Il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.



excel calculer le temps écoulé entre deux dates

Après avoir entré la formule dans la première cellule, faites-la glisser vers le bas et à travers pour remplir les autres cellules.



Explication

Remarque: cette formule utilise deux plages nommées : «noms» fait référence à C5: C11 et «groupes» fait référence à B5: B11. Ces noms sont également définis dans la capture d'écran ci-dessus.

L'essentiel de cette formule est le suivant: nous utilisons la fonction SMALL pour générer un numéro de ligne correspondant à une «nième correspondance». Une fois que nous avons le numéro de ligne, nous le passons simplement dans la fonction de fonction INDEX, qui renvoie la valeur à cette ligne.



L'astuce est que SMALL travaille avec un tableau qui est construit dynamiquement par IF dans ce bit:

 
 IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1)

Cet extrait de code teste la plage nommée «groupes» pour la valeur dans E5. S'il est trouvé, il renvoie un numéro de ligne à partir d'un tableau de numéros de ligne relatifs créés avec:

 
 ROW (names)- MIN ( ROW (names))+1

Le résultat final est un tableau qui contient des nombres là où il y a une correspondance et FALSE où pas:



comment faire un sumif en excel

{1FALSEFALSEFALSEFALSE6FALSE}

Ce tableau va dans SMALL. La valeur k pour SMALL (nth) provient d'un gamme élargie :

 
 COLUMNS ($E:E5)

Lorsqu'elle est copiée dans la table de résultats, la plage se développe, provoquant l'incrémentation de k (nième). La fonction SMALL renvoie chaque numéro de ligne correspondant, qui est fourni à la fonction INDEX en tant que row_num, avec la plage nommée «noms» comme tableau.

Gestion des erreurs

Lorsque COLUMNS renvoie une valeur pour k qui n'existe pas, SMALL renvoie une erreur #NUM. Cela se produit après que tous les matchs se sont produits. Pour supprimer l'erreur, nous enveloppons la formule dans la fonction IFERROR pour attraper les erreurs et renvoyer un chaîne vide ('').

Auteur Dave Bruns


^