Repérer et extraire les textes longs avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Identifier les textes longs

Cette nouvelle astuce Excel propose de montrer comment repérer et extraire des informations longues dans une source de données. Et nous le verrons, cette extraction peut de plus être soumise à un ou des critères.



Dans l'exemple finalisé illustré par la capture, l'utilisateur choisit une équipe à l'aide d'une liste déroulante située sur la droite du tableau. Dès lors, deux calculs matriciels d'extraction s'activent. Le premier isole et retranscrit le nom le plus long quelle que soit l'équipe d'appartenance. Le second est plus fin. Il extrait le nom le plus long dans l'équipe ainsi mentionnée.

Classeur source et présentation
Pour la mise en place de cette astuce, nous proposons de récupérer ce classeur dans sa version non encore aboutie.

• Télécharger le classeur extraire-textes-longs-critere.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Les équipes sont renseignées en colonne B. Les participants y appartenant sont énumérés en colonne C. Une liste déroulante est disponible en cellule G4. Elle permet de désigner l'une de ces quatre équipes en guise de critère d'extraction. Deux cases vides se proposent en cellules G7 et G10. La première attend le calcul matriciel permettant d'extraire le nom le plus long quelle que soit l'équipe, donc sans critère imposé. La seconde attend le calcul matriciel permettant d'extraire le nom le plus long pour l'équipe ainsi choisie, donc selon critère. Des plages possèdent des noms pour simplifier la construction des formules matricielles. Celle des équipes est nommée Equipes et celle des noms est nommée Noms. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel.

Texte le plus long
Pour extraire le nom possédant le plus grand nombre de caractères, nous devons engager un raisonnement matriciel impliquant les fonctions Index, Equiv, Max et NbCar. La fonction Index est la fonction d'extraction destinée à renvoyer le nom le plus long trouvé. Pour cela, elle doit s'appuyer sur la fonction Equiv afin de trouver sa position dans la colonne. Cette position peut être décelée en cherchant le texte possédant le plus grand nombre, avec la fonction Max, de caractères, avec la fonction NbCar.

• Cliquer sur la cellule G7 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des noms par son intitulé, soit : Noms,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Inscrire la fonction de recherchede position, suivie d'une parenthèse, soit : Equiv(,
• Inscrire la fonction pour la plus grande valeur, suivie d'une parenthèse, soit : Max(,
• Inscrire la fonction pour le nombre de caractères, suivie d'une parenthèse, soit : NbCar(,
• Désigner de nouveau la colonne des noms par son intitulé, soit : Noms,
• Fermer la parenthèse de la fonction NbCar,
• Puis, fermer la parenthèse de la fonction Max,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Appliquer de nouveau la fonction NbCar sur les noms, soit : NbCar(Noms),

De cette manière, nous réalisons la recherche matricielle du plus grand nombre de caractères dans les noms sur le nombre de caractères de chacun de ces noms.

• Taper un point-virgule (;) suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,
• Dès lors, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Le résultat tombe et le nom extrait comporte en effet un grand nombre de lettres. Il est à noter qu'en cas d'égalité, la formule d'extraction s'arrête bien entendu sur la première concordance trouvée. La syntaxe de la formule matricielle que nous avons bâtie est la suivante :

{=INDEX(Noms; EQUIV(MAX(NBCAR(Noms)); NBCAR(Noms); 0))}

Texte le plus long selon critère
Pour extraire le nom le plus long en fonction de l'équipe choisie, le principe est sensiblement identique. La matrice de l'élément cherché doit être recoupée avec une matrice conditionnelle. Cette matrice conditionnelle consiste à poser le critère sur la plage des équipes. La plage de recherche doit être recoupée par cette même matrice conditionnelle. C'est ainsi que la position du nom le plus long dans l'équipe désignée pourra être décelée et retournée à la fonction Index, qui se chargera de l'extraction.

• En cellule G10, adapter la précédente syntaxe comme suit :

{=INDEX(Noms; EQUIV(MAX(NBCAR(Noms)*(Equipes=G4)); NBCAR(Noms)*(Equipes=G4); 0))}

Bien sûr, il est primordial de la valider avec le raccourci clavier CTRL + MAJ + Entrée.



Si l'équipe est toujours désignée, c'est le même nom qui ressort dans les deux cas : Strofobe. Ce participant appartient à l'équipe B. Mais si vous changez d'équipe avec la liste déroulante, l'extraction s'actualise aussitôt et diffère de la précédente.

Textes les plus longs en couleur
Pour une solution plus saisissante, nous proposons de repérer automatiquement et en couleur les noms possédant le plus grand nombre de caractères, dans le tableau. Et pour cela, nous devons bâtir deux règles de mise en forme conditionnelle. Et ces règles sont triviales. Il suffit simplement d'établir la correspondance entre le nom cherché dans le tableau et celui extrait par le calcul.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:D26,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, construire la syntaxe suivante : =$C4=$G$10,

Nous cherchons à trouver la correspondance entre le nom du participant en cours d'analyse ($C4) par la règle de mise en forme conditionnelle et celui extrait sur le critère de l'équipe ($G$10). L'analyse d'une mise en forme conditionnelle est chronologique. C'est pourquoi nous partons du premier nom et libérons la ligne de la cellule C4 pour qu'ils puissent être tous passés en revue. Lorsque cette correspondance est avérée, nous devons faire ressortir le nom dans un vert semblable à celui utilisé pour l'extraction.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un vert assez vif pour la couleur du texte,
• Valider ce réglage par le bouton Ok,
• De retour sur la première boîte de dialogue, valider la règle par le bouton Ok,

Le focus est rendu à la feuille. Et instantanément, la ligne du participant extrait sur critère surgit en vert.

De la même façon, après avoir sélectionné les mêmes données, il convient de construire la règle suivante : =$C4=$G$7 et de lui associer un orange semblable à celui de la donnée extraite pour le nom le plus long dans l'absolu.



Dès lors, les extractions sont dynamiquement repérées. Et vous pouvez le constater en modifiant l'équipe par le biais de la liste déroulante. En même temps que le nom extrait s'actualise, la couleur se déplace pour l'identifier.