Maison > Article > Les sujets > Partage de compétences pratiques sur Excel : utilisez Power Query pour fusionner des classeurs dans des dossiers
Je vous ai déjà présenté l'utilisation de la nouvelle fonction Power Query d'EXCEL pour résumer les feuilles de calcul du classeur, mais la fonction de Power Query est bien plus que cela. Aujourd'hui, je vais vous présenter une technique de fusion plus avancée : l'utilisation. Power Query fusionne les classeurs dans des dossiers.
Comme indiqué ci-dessous, il existe des données de ventes pour quatre régions dans le dossier « Ventes » sur le bureau. Les noms de titres dans chaque classeur sont cohérents et l’ordre peut être différent. La valeur de la colonne ville dans chaque classeur est le nom du classeur, ce qui est pratique pour visualiser l'effet de fusion ultérieurement.
L'opération est la suivante :
Fermer le fichier dans le dossier, créer un nouveau classeur, puis cliquez sur l'option de données Sous la carte, groupe [Get and Convert] "Nouvelle requête" --- "Depuis le fichier" --- "Depuis le dossier".
Entrez le chemin du dossier ou parcourez pour sélectionner l'emplacement du dossier et cliquez sur OK.
Cette interface répertorie tous les classeurs du dossier, cliquez sur Modifier.
Entrez dans l'interface de l'éditeur Power Query. Le haut est la barre de menu, le milieu est la zone du tableau et les données qui seront renvoyées à la feuille de calcul à la fin. La fenêtre de configuration de la requête sur la droite affiche l'enregistrement de l'opération Power Query.
Comme introduit précédemment, la police verte dans la colonne "Contenu" signifie que cette cellule contient un fichier. Cliquez sur une cellule pour prévisualiser son contenu. (Remarque : lors de la prévisualisation du contenu d'une cellule, vous devez placer la souris sur l'espace vide de la cellule, pas sur le texte. Cliquer sur le texte ouvrira directement le fichier dans la cellule) Puisque le fichier est extrait directement à partir du dossier, au format binaire, de sorte que le classeur au format binaire apparaisse dans le volet d'aperçu ci-dessous.
Le binaire en tant que fichier binaire ne peut pas être affiché dans un tableau, nous devons donc le changer au format tableau et développer les données dans le tableau. Supprimez d'abord les autres colonnes inutiles.
Sélectionnez la colonne "Contenu", cliquez sur "Supprimer la colonne" - "Supprimer les autres colonnes" dans le groupe [Gérer les colonnes] sous l'onglet Accueil.
De cette façon, toutes les autres colonnes à l'exception de la colonne "Contenu" sont supprimées.
Alors, comment convertir des fichiers binaires en tables ordinaires nécessite l'utilisation du langage de programmation spécial de Power Query - le langage M. Ici, je vais vous présenter une fonction couramment utilisée.
Cliquez sur « Colonne personnalisée » dans le groupe [Général] sous l'option Ajouter une colonne.
Dans la fenêtre de colonne personnalisée, entrez =Excel.Workbook([Content],true) dans la "Formule de colonne personnalisée", où "[Contenu]" peut cliquer sur "Contenu" dans les colonnes disponibles sur le à droite, puis cliquez sur le coin inférieur droit pour insérer (Remarque : la majuscule de la formule ne doit pas être fausse).
Analyse de formule :
Excel.Workbook
Fonction : Renvoie les enregistrements de la feuille de calcul à partir du classeur Excel.
Paramètres : Excel.Workbook (classeur en binaire, useHeaders facultatif en logique nullable, facultatifdelayTypes en nullable logique) comme table
Cette fonction renvoie un tableau. Le premier paramètre du classeur est au format binaire, et le deuxième paramètre est la valeur logique du paramètre facultatif true signifie que le titre de la table d'origine sera utilisé comme table. titre de la nouvelle table. La valeur par défaut est false à utiliser. Les nouveaux noms de colonnes remplacent les titres de la feuille de calcul d'origine. Ne vous inquiétez pas du troisième paramètre.
Ici, nous utilisons toujours le titre original du formulaire, alors remplissez vrai. Cela évite l'étape ultérieure d'amélioration de la première ligne du titre.
La nouvelle colonne a été ajoutée avec succès. Prévisualisez l'une des cellules. Ce qui est affiché ci-dessous est un classeur de style tableau. Cela peut être directement étendu à la table.
La colonne "Données" affiche un tableau au format Tableau, incluant les données du tableau. Ici, il suffit d'extraire cette colonne. Cliquez sur le bouton de développement dans le coin supérieur droit de la colonne personnalisée, sélectionnez la colonne développée « Données » et décochez « Utiliser le nom de la colonne d'origine comme préfixe ».
Le nom de la colonne devient "Données". À ce stade, nous prévisualisons les données dans « Données » et ce qui apparaît ci-dessous correspond aux données originales du tableau. Extrayez ensuite toutes les données ci-dessous.
Cliquez de la même manière sur le bouton Développer en haut à droite de la colonne personnalisée, choisissez de développer toutes les colonnes et ne cochez pas "Utiliser le nom de la colonne d'origine comme préfixe".
De cette façon, nous obtenons les données dans la feuille de calcul en perçant couche par couche.
Enfin, supprimez la colonne "Contenu". Sélectionnez la colonne "Contenu" et faites un clic droit pour la supprimer.
Enfin, téléchargez simplement ce formulaire sur le formulaire.
Cliquez sur « Fermer et télécharger » dans le groupe [Fermer] sous l'onglet Accueil.
De cette façon, les données sont résumées dans la feuille de calcul.
Lorsque vous cliquez sur le bouton de filtre dans la colonne "Ville", vous verrez que les données des quatre classeurs sont toutes dans le tableau.
Que se passe-t-il lorsqu'il y a un classeur supplémentaire dans le dossier ? Essayez de placer un nouveau classeur « Xi'an » dans ce dossier.
Retournez au tableau où vous venez de faire des statistiques et cliquez sur "Actualiser tout" dans le groupe [Connexion] sous l'onglet Données.
La colonne ville contient désormais "Xi'an", et les données représentant ce nouveau classeur ont été ajoutées.
Résumé : Power Query fusionne les dossiers tant que les titres de chaque feuille de calcul sont les mêmes, elle peut être fusionnée et résumée, quel que soit le nombre de classeurs contenus dans le dossier. Et toutes les modifications de données peuvent être mises à jour en un seul clic grâce à toutes les actualisations.
Power Query est un outil puissant pour l'analyse des données EXCEL. Grâce à des opérations graphiques simples, combinées au langage M intégré et à l'enregistreur d'opérations, il nous aide à exploiter plus de données de manière unifiée et à terminer rapidement le traitement et l'optimisation des données. De plus, il est plus rapide à démarrer et plus facile à utiliser que VBA, et le fonctionnement graphique peut répondre à la plupart de nos besoins. Tout le monde, dépêchez-vous et apprenez !
Recommandations d'apprentissage associées : Tutoriel Excel
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!