Exceller

Extraire plusieurs correspondances dans des lignes distinctes

Extract Multiple Matches Into Separate Rows

Formule Excel : extraire plusieurs correspondances dans des lignes distinctesSommaire

Pour extraire plusieurs correspondances dans des cellules distinctes, dans des lignes distinctes, 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 le suivant : 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 transmettons simplement à la 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 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 là où il y a une correspondance et FALSE là où il n'y en a pas. Le tableau ressemble à ceci :

{1FALSEFALSEFALSEFALSE6FALSE}

comment utiliser le tableau croisé dynamique dans Excel 2010

Ce tableau passe en SMALL. La valeur k pour SMALL (nième) provient d'un gamme en expansion :

 
 ROWS ($E:E5)

Lorsqu'il est copié dans le tableau des résultats, la plage s'étend, entraînant l'incrémentation de k (ntiè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 'names' 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 ont eu lieu. Pour supprimer l'erreur, nous utilisons IFERROR pour intercepter l'erreur et renvoyer un chaîne vide ('').

Auteur Dave Bruns


^