Table des matières
Comment remplacer rapidement et en masse le texte selon la liste de références par des formules - nous l'avons déjà trié. Essayons maintenant de le faire dans Power Query.
Comme cela arrive souvent effectuer cette tâche est beaucoup plus facile que d'expliquer why ça marche, mais essayons de faire les deux 🙂
Nous avons donc deux tableaux dynamiques "intelligents" créés à partir de plages ordinaires avec un raccourci clavier Ctrl+T ou équipe Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau):
J'ai appelé la première table Données, le deuxième tableau – Annuaireen utilisant le champ Nom de la table (Nom de la table) languette Constructeur (Conception).
Tâche : remplacer les adresses dans le tableau Données toutes les occurrences d'une colonne Trouver Manuel à leurs homologues corrects correspondants de la colonne Représentant(e) suppléant(e). Le reste du texte dans les cellules doit rester intact.
Étape 1. Chargez le répertoire dans Power Query et transformez-le en liste
Après avoir défini la cellule active à n'importe quel endroit du tableau de référence, cliquez sur l'onglet Données (Date)ou sur l'onglet Requête d'alimentation (si vous avez une ancienne version d'Excel et que vous avez installé Power Query en tant que complément sur un onglet séparé) sur le bouton De table/gamme (À partir du tableau/plage).
La table de référence sera chargée dans l'éditeur de requête Power Query :
Afin de ne pas gêner, une étape ajoutée automatiquement type modifié (type modifié) dans le panneau de droite, les étapes appliquées peuvent être supprimées en toute sécurité, ne laissant que l'étape Identifier (La source):
Maintenant, pour effectuer d'autres transformations et remplacements, nous devons transformer cette table en une liste (liste).
Digression lyrique
- lampe de table est un tableau à deux dimensions composé de plusieurs lignes et colonnes.
- Enregistrer (Enregistrer) – chaîne de tableau à une dimension, composée de plusieurs éléments de champs avec des noms, par exemple [Nom = "Masha", Sexe = "f", Âge = 25]
- Liste – un tableau-colonne unidimensionnel, composé de plusieurs éléments, par exemple {1, 2, 3, 10, 42} or { "Foi Espoir Amour" }
Pour résoudre notre problème, nous nous intéresserons principalement au type Liste.
L'astuce ici est que les éléments de liste dans Power Query peuvent être non seulement des nombres ou du texte banals, mais également d'autres listes ou enregistrements. C'est dans une telle liste délicate (list), constituée d'enregistrements (records) que nous devons tourner notre répertoire. Dans la notation syntaxique de Power Query (entrées entre crochets, listes entre accolades), cela ressemblerait à :
{
[ Trouver = "St. Pétersbourg", Remplacer = "St. Pétersbourg”] ,
[ Trouver = "St. Pétersbourg", Remplacer = "St. Pétersbourg”] ,
[ Trouver = "Pierre", Remplacer = "St. Pétersbourg”] ,
et ainsi de suite
}
Une telle transformation est effectuée à l'aide d'une fonction spéciale du langage M intégrée à Power Query - Table.ToRecords. Pour l'appliquer directement dans la barre de formule, ajoutez cette fonction au code de l'étape ici Identifier.
Il était la suivante:
Après:
Après avoir ajouté la fonction Table.ToRecords, l'apparence de notre table changera - elle se transformera en une liste d'enregistrements. Le contenu des enregistrements individuels peut être vu au bas du volet d'affichage en cliquant dans l'arrière-plan de la cellule à côté de n'importe quel mot Enregistrement (mais pas en un seul mot !)
En plus de ce qui précède, il est logique d'ajouter un trait supplémentaire - pour mettre en cache (tampon) notre liste créée. Cela forcera Power Query à charger une fois notre liste de recherche en mémoire et à ne pas la recalculer lorsque nous y accéderons ultérieurement pour la remplacer. Pour ce faire, enveloppez notre formule dans une autre fonction - Liste.Buffer:
Une telle mise en cache donnera une augmentation très notable de la vitesse (de plusieurs fois !) avec une grande quantité de données initiales à effacer.
Ceci termine la préparation du manuel.
Il reste à cliquer sur Accueil – Fermer et charger – Fermer et charger dans… (Accueil — Fermer&Charger — Fermer&Charger dans..), sélectionner une option Créez simplement une connexion (Créer uniquement une connexion) et retournez dans Excel.
Étape 2. Chargement de la table de données
Tout est banal ici. Comme auparavant avec le livre de référence, on monte à n'importe quel endroit du tableau, on clique sur l'onglet Données bouton (dans la fenêtre de contrôle qui apparaît maintenant) À partir du tableau/plage et notre table Données entre dans Power Query. Étape ajoutée automatiquement type modifié (type modifié) vous pouvez également supprimer :
Aucune action préparatoire spéciale n'est requise pour cela, et nous passons à la chose la plus importante.
Étape 3. Effectuer des remplacements à l'aide de la fonction List.Accumulate
Ajoutons une colonne calculée à notre table de données à l'aide de la commande Ajout d'une colonne - Colonne personnalisée (Ajouter une colonne — Colonne personnalisée): et entrez le nom de la colonne ajoutée dans la fenêtre qui s'ouvre (par exemple, adresse corrigée) et notre fonction magique Liste.Accumuler:
Il reste à cliquer sur OK – et on obtient une colonne avec les remplacements effectués :
Noter que:
- Étant donné que Power Query est sensible à la casse, il n'y avait pas de remplacement dans l'avant-dernière ligne, car dans le répertoire, nous avons "SPb", et non "SPb".
- S'il y a plusieurs sous-chaînes à remplacer à la fois dans les données source (par exemple, dans la 7e ligne, vous devez remplacer à la fois "S-Pb" et "Prospectus"), cela ne crée aucun problème (contrairement au remplacement par des formules de la méthode précédente).
- S'il n'y a rien à remplacer dans le texte source (9e ligne), alors aucune erreur ne se produit (contrairement, encore une fois, au remplacement par des formules).
La vitesse d'une telle demande est très, très décente. Par exemple, pour une table de données initiales d'une taille de 5000 lignes, cette requête a été mise à jour en moins d'une seconde (sans buffering, soit dit en passant, environ 3 secondes !)
Fonctionnement de la fonction List.Accumulate
En principe, cela pourrait être la fin (à moi d'écrire, et à vous de lire) cet article. Si vous voulez non seulement pouvoir, mais aussi comprendre comment cela fonctionne "sous le capot", alors vous devrez plonger un peu plus profondément dans le terrier du lapin et gérer la fonction List.Accumulate, qui a fait tout le remplacement en vrac Travaille pour nous.
La syntaxe de cette fonction est :
=Liste.Accumuler(liste, seed, accumulateur)
De
- liste est la liste dont nous itérons les éléments.
- seed - Etat initial
- accumulateur – une fonction qui effectue une opération (mathématique, textuelle, etc.) sur l'élément suivant de la liste et accumule le résultat du traitement dans une variable spéciale.
En général, la syntaxe d'écriture des fonctions dans Power Query ressemble à ceci :
(argument1, argument2, … argumentN) => quelques actions avec arguments
Par exemple, la fonction de sommation pourrait être représentée par :
(a, b) => a + b
Pour List.Accumulate , cette fonction d'accumulateur a deux arguments obligatoires (ils peuvent être nommés n'importe quoi, mais les noms habituels sont Etat и actuel, comme dans l'aide officielle de cette fonction, où :
- Etat – une variable où le résultat est cumulé (sa valeur initiale est celle mentionnée ci-dessus seed)
- actuel – la prochaine valeur itérée de la liste liste
Par exemple, regardons les étapes de la logique de la construction suivante :
=Liste.Accumuler({3, 2, 5}, 10, (état, courant) => état + courant)
- Valeur variable Etat est mis égal à l'argument initial seedIe état = 10
- On prend le premier élément de la liste (courant = 3) et ajoutez-le à la variable Etat (Dix). On a état = 13.
- Nous prenons le deuxième élément de la liste (courant = 2) et ajoutez-le à la valeur accumulée actuelle dans la variable Etat (Dix). On a état = 15.
- Nous prenons le troisième élément de la liste (courant = 5) et ajoutez-le à la valeur accumulée actuelle dans la variable Etat (Dix). On a état = 20.
C'est le dernier cumul Etat la valeur est notre fonction List.Accumulate et les sorties en conséquence :
Si vous fantasmez un peu, alors en utilisant la fonction List.Accumulate, vous pouvez simuler, par exemple, la fonction Excel CONCATENATE (dans Power Query, son analogue s'appelle Texte. Combiner) en utilisant l'expression :
Ou même rechercher la valeur maximale (imitation de la fonction MAX d'Excel, qui dans Power Query s'appelle Liste.Max):
Cependant, la principale caractéristique de List.Accumulate est la capacité de traiter non seulement de simples listes textuelles ou numériques comme arguments, mais aussi des objets plus complexes - par exemple, des listes à partir de listes ou des listes à partir d'enregistrements (bonjour, Directory !)
Reprenons la construction qui a effectué le remplacement dans notre problème :
Liste.Accumuler(Annuaire, [Adresse], (state,current) => Text.Replace(state, current[Find], current[Replace]) )
Que se passe-t-il vraiment ici ?
- Comme valeur initiale (seed) on prend le premier texte maladroit de la colonne [Adresse] notre table: 199034, Saint-Pétersbourg, str. Béringa, d. 1
- Ensuite, List.Accumulate parcourt les éléments de la liste un par un - Manuel. Chaque élément de cette liste est un enregistrement constitué d'un couple de champs « Quoi trouver – Quoi remplacer par » ou, en d'autres termes, la ligne suivante dans le répertoire.
- La fonction d'accumulateur met dans une variable Etat valeur initiale (première adresse 199034, Saint-Pétersbourg, str. Béringa, d. 1) et y exécute une fonction d'accumulateur - l'opération de remplacement à l'aide de la fonction M standard Texte. Remplacer (analogue à la fonction SUBSTITUTE d'Excel). Sa syntaxe est :
Text.Replace( texte original, ce que nous recherchons, ce que nous remplaçons par )
et ici nous avons:
- Etat est notre sale adresse, qui se trouve dans Etat (y aller depuis seed)
- courant[Rechercher] – valeur du champ Trouver à partir de la prochaine entrée itérée de la liste Annuaire, qui appartient à la variable actuel
- courant[Remplacer] – valeur du champ Représentant(e) suppléant(e) à partir de la prochaine entrée itérée de la liste Annuairecouché dans actuel
Ainsi, pour chaque adresse, un cycle complet d'énumération de toutes les lignes de l'annuaire est exécuté à chaque fois, en remplaçant le texte du champ [Rechercher] par la valeur du champ [Remplacer].
J'espère que vous avez compris l'idée 🙂
- Remplacer en masse du texte dans une liste à l'aide de formules
- Expressions régulières (RegExp) dans Power Query