Exceller

Extraire plusieurs correspondances dans des lignes séparées

Extract Multiple Matches Into Separate Rows

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

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

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

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



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



commande pour insérer une ligne dans Excel
Explication

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

L'essentiel de cette formule est la suivante: nous utilisons la fonction SMALL pour obtenir un numéro de ligne qui correspond à 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:

comment insérer un graphique dans Excel
 
 IF (groups=E, ROW (names)- MIN ( ROW (names))+1)

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

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

Le résultat est un tableau qui contient les numéros de ligne où il y a une correspondance et FALSE où pas. Le tableau ressemble à quelque chose comme ceci:



{1FALSEFALSEFALSEFALSE6FALSE}

comment utiliser le tableau croisé dynamique dans Excel 2010

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

 
 ROWS ($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 ROWS 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 utilisons IFERROR pour intercepter l'erreur et renvoyer un chaîne vide ('').

Auteur Dave Bruns


^