Plus récemment, nous avons discuté de l'utilisation de la fonction FILTER.XML pour importer des données XML à partir d'Internet - la tâche principale à laquelle cette fonction est en fait destinée. En cours de route, cependant, une autre utilisation inattendue et magnifique de cette fonction a fait surface - pour diviser rapidement le texte collant en cellules.
Disons que nous avons une colonne de données comme celle-ci :
Bien sûr, pour plus de commodité, je voudrais le diviser en colonnes distinctes : nom de l'entreprise, ville, rue, maison. Vous pouvez le faire de différentes manières :
- Utilisez Texte par colonnes de l'onglet Données (Données — Texte vers colonnes) et faire trois étapes Analyseur de texte. Mais si les données changent demain, vous devrez répéter tout le processus.
- Chargez ces données dans Power Query et divisez-les là-bas, puis téléchargez-les à nouveau dans la feuille, puis mettez à jour la requête lorsque les données changent (ce qui est déjà plus facile).
- Si vous avez besoin de mettre à jour à la volée, vous pouvez écrire des formules très complexes pour trouver des virgules et extraire le texte entre elles.
Et vous pouvez le faire de manière plus élégante et utiliser la fonction FILTER.XML, mais qu'est-ce que cela a à voir avec cela ?
La fonction FILTER.XML reçoit comme argument initial un code XML - du texte balisé avec des balises et des attributs spéciaux, puis l'analyse en ses composants, en extrayant les fragments de données dont nous avons besoin. Le code XML ressemble généralement à ceci :
En XML, chaque élément de données doit être entouré de balises. Une balise est un texte (dans l'exemple ci-dessus, il s'agit de manager, name, profit) entre crochets. Les balises viennent toujours par paires - ouverture et fermeture (avec une barre oblique ajoutée au début).
La fonction FILTER.XML peut facilement extraire le contenu de toutes les balises dont nous avons besoin, par exemple, les noms de tous les responsables, et (surtout) les afficher tous en même temps dans une seule liste. Notre tâche consiste donc à ajouter des balises au texte source, en le transformant en code XML adapté à une analyse ultérieure par la fonction FILTER.XML.
Si nous prenons la première adresse de notre liste comme exemple, alors nous devrons la transformer en cette construction :
J'ai appelé l'ouverture globale et la fermeture de toutes les balises de texte t, et les balises encadrant chaque élément sont s., mais vous pouvez utiliser n'importe quelle autre désignation - cela n'a pas d'importance.
Si nous supprimons les retraits et les sauts de ligne de ce code - complètement, soit dit en passant, facultatifs et ajoutés uniquement pour plus de clarté, alors tout cela se transformera en une ligne :
Et il peut déjà être obtenu relativement facilement à partir de l'adresse source en remplaçant les virgules par quelques balises en utilisant la fonction REMPLACER (REMPLAÇANT) et coller avec le symbole & au début et à la fin des balises d'ouverture et de fermeture :
Pour étendre horizontalement la plage résultante, nous utilisons la fonction standard TRANSP (TRANSPOSER), en y enveloppant notre formule :
Une caractéristique importante de toute cette conception est que dans la nouvelle version d'Office 2021 et d'Office 365 avec prise en charge des tableaux dynamiques, aucun geste spécial n'est requis pour la saisie - il suffit d'entrer et de cliquer sur Entrer – la formule elle-même occupe le nombre de cellules dont elle a besoin et tout fonctionne avec un bang. Dans les versions précédentes, où il n'y avait pas encore de tableaux dynamiques, vous devrez d'abord sélectionner un nombre suffisant de cellules vides avant d'entrer la formule (vous pouvez avec une marge), et après avoir créé la formule, appuyez sur le raccourci clavier Ctrl+Shift+Entrerpour le saisir sous forme de formule matricielle.
Une astuce similaire peut être utilisée lors de la séparation de texte collé dans une cellule par un saut de ligne :
La seule différence avec l'exemple précédent est qu'au lieu d'une virgule, nous remplaçons ici le caractère invisible de saut de ligne Alt + Entrée, qui peut être spécifié dans la formule à l'aide de la fonction CHAR avec le code 10.
- Les subtilités de travailler avec des sauts de ligne (Alt + Entrée) dans Excel
- Diviser le texte par colonnes dans Excel
- Remplacer le texte par SUBSTITUTE