Exceller

Extraire plusieurs correspondances dans des colonnes distinctes

Extract Multiple Matches Into Separate Columns

Formule Excel : extraire plusieurs correspondances dans des colonnes distinctesSommaire

Pour extraire plusieurs correspondances dans des cellules distinctes, dans des colonnes distinctes, 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 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 :

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

Cet extrait 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éé 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 FAUX là où il n'y en a pas :

comment faire un sumif en excel

{1FALSEFALSEFALSEFALSE6FALSE}

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

 
 COLUMNS ($E:E5)

Lorsqu'elle est copiée dans la table de 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 COLUMNS 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 enveloppons la formule dans la fonction SIERREUR pour détecter les erreurs et renvoyer un chaîne vide ('').

Auteur Dave Bruns


^