Travailler avec des tableaux volumineux dans Excel peut s'avérer fastidieux si vous n'utilisez pas les bons outils. Power Query offre un moyen puissant de nettoyer et de transformer les données rapidement et avec précision, sans avoir à répéter les étapes manuellement. Que vous souhaitiez supprimer des colonnes vides, fusionner des tableaux, convertir des formules ou filtrer les doublons, des commandes spécifiques permettent de réaliser facilement ces tâches. Dans cet article, nous passerons en revue un ensemble de commandes Power Query essentielles pour organiser vos données de manière professionnelle et gagner un temps précieux sur le traitement manuel.
Avez-vous déjà entendu parler de Power Query dans Microsoft Excel, mais avez-vous toujours pensé qu'il était réservé aux experts d'Excel ? Je m'arrête là. En réalité, Power Query est conçu pour être facile à utiliser et, surtout, pour vous épargner beaucoup d'efforts lors de l'organisation de vos données.
ملاحظة
Ce guide s'adresse aux lecteurs qui découvrent Microsoft Excel ou Power Query. Ces conseils ne nécessitent aucune connaissance ni utilisation des langages de formules Power Query et constituent un excellent point de départ pour se familiariser avec certaines commandes de ce puissant outil de manipulation de données.
1. Divisez les cellules à l'aide du délimiteur pour isoler les valeurs
Il existe plusieurs façons dans Excel de diviser des données en plusieurs colonnes, notamment l'outil Convertir le texte en colonnes, le remplissage instantané et les fonctions intégrées. Cependant, la méthode la plus simple consiste à lancer l'éditeur Power Query et à y effectuer l'opération.
Supposons que vous ayez trouvé Tableau sur Wikipédia Vous souhaitez le convertir avec Power Query. Pour importer la table, cliquez d'abord sur « Obtenir les données » dans l'onglet « Accueil » du ruban, puis sélectionnez « De ». Autres sources > Du Web.
Ensuite, après avoir collé l'URL dans le champ de texte de la boîte de dialogue,Depuis le Web Et cliquez "D'ACCORD", J'ai sélectionné le tableau dans le volet gauche du navigateur, puis j'ai cliqué "Transformation des données".
Lorsque vous chargez la table dans l'éditeur Power Query, vous remarquez que la colonne Durée de carrière contient deux éléments de données : l'année où chaque joueur a commencé sa carrière et la dernière année où il a joué. Vous souhaitez que ces deux éléments soient dans des colonnes distinctes.
Pour ce faire, faites un clic droit sur l'en-tête de la colonne, puis passez la souris dessus. « séparation de colonne »Et sélectionnez "Selon l'intervalle.
ملاحظة
Un séparateur est une lettre, un symbole ou un espace utilisé pour séparer les éléments d'une séquence.
À ce stade, l'éditeur Power Query examine les données de la colonne pour détecter une éventuelle rupture. Dans ce cas, il détermine que chaque cellule contient un tiret, supposant à juste titre qu'il s'agit du point de division de la cellule. Cependant, vous pouvez choisir une autre rupture dans le premier menu déroulant si nécessaire.
Comme il n'y a qu'un seul séparateur dans chaque cellule de la colonne sélectionnée dans cet exemple, vous n'avez pas besoin d'apporter d'autres modifications aux options de la boîte de dialogue, alors cliquez surOk« et voir les données nouvellement transformées dans l'éditeur Power Query.
Pour renommer de nouvelles colonnes, double-cliquez sur les en-têtes de colonne et saisissez les nouvelles étiquettes de données.
Vous avez maintenant réussi à fractionner les données avec l'éditeur Power Query, mais avez-vous remarqué une différence dans l'alignement des cellules des deux nouvelles colonnes ? En effet, les valeurs de la colonne « Première exécution » sont toutes des nombres (comme indiqué par le symbole 123 à côté de l'en-tête de colonne), mais comme les valeurs de la colonne « Dernière exécution » contiennent à la fois du texte et des nombres, Excel traite cette colonne comme une colonne de texte (d'où le symbole ABC dans l'en-tête de colonne).
Pour résoudre ce problème, sélectionnez le symbole ABC dans la colonne Dernière exécution, puis cliquez sur Entier.
Désormais, les deux colonnes sont formatées comme des ensembles de données numériques.
Cependant, comme certaines cellules de la colonne « Dernière exécution » contiennent des valeurs textuelles, elles apparaissent désormais comme des erreurs. Ne vous inquiétez pas ! Lisez la suite pour savoir comment résoudre ce problème !
2. Remplacer les erreurs à utiliser dans les calculs.
Power Query dans Excel est un outil puissant pour traiter toutes les erreurs qui apparaissent dans vos données.
En regardant la requête dans l'exemple précédent, certaines cellules de la colonne « Dernière exécution » contiennent des erreurs, car elles contenaient auparavant la valeur de texte « Présent » dans une colonne au format entier.
Actuellement, si vous cliquez sur « Fermer et charger » dans le coin supérieur gauche de l'éditeur Power Query, les cellules contenant des valeurs erronées seront vides dans le tableau résultant. Cela peut sembler idéal, mais les cellules vides dans une colonne Excel peuvent poser des problèmes lors du tri et du filtrage des données, ou lors du référencement des en-têtes de colonnes dans les formules. Il est donc préférable de les remplir avec des valeurs utiles.
Pour ce faire, faites un clic droit sur l'en-tête de la colonne, puis sélectionnez « Remplacer les erreurs. »
ملاحظة
Vous pouvez sélectionner « Supprimer les erreurs » dans le menu contextuel. Cependant, cela supprimera la ligne entière, et pas seulement l'erreur ; ne cliquez donc sur cette option que si vous le souhaitez.
Dans la boîte de dialogue Remplacer les erreurs, saisissez la valeur à remplacer. Dans ce cas, comme les erreurs apparaissent dans la cellule où « Actuel » est indiqué, vous pouvez saisir l'année en cours.
Lorsque vous cliquezOk« , toutes les valeurs d'erreur sont remplacées par cette nouvelle valeur.
Étant donné que toutes les cellules des colonnes de droite contiennent les mêmes types de données sans aucune erreur, vous pouvez créer une nouvelle colonne pour calculer le nombre total d'années jouées pour chaque joueur.
Ici, j'ai cliqué sur Colonne personnaliséeDans l'onglet Ajouter une colonne, j'ai changé le nom de la colonne en Années de jeu et j'ai utilisé la liste des colonnes dans la liste Colonnes disponibles pour effectuer une soustraction simple.
Ensuite, lorsque j’ai cliqué sur OK, cette nouvelle colonne, sans aucune erreur, a été ajoutée sur le côté droit de la requête.
Une fois terminé, cliquez sur l’icône Fermer et charger dans l’onglet Accueil de l’éditeur Power Query pour envoyer le tableau vers une nouvelle feuille de calcul dans votre classeur Excel.
3. Dépivoter les données pour faciliter l'analyse
Lors de la création d'un nouvel ensemble de données dans Microsoft Excel, je suis, autant que possible, le principe du champ d'enregistrement, où :
- Chaque ligne contient une collection de types de données liés mais différents (également appelés enregistrement), et
- Chaque colonne contient un type de données unique (également appelé champ) lié à chaque enregistrement.
Dans cet exemple simple, chaque pays est un enregistrement et ses continents, sa population et ses devises sont des champs.
L'utilisation de ce format prépare vos données pour une analyse plus approfondie, car vous pouvez facilement filtrer et trier les données, faire référence aux en-têtes de colonnes dans les formules et visualiser les statistiques.
Comparez le tableau de l’exemple ci-dessus à la capture d’écran suivante, où chaque colonne (champ) a des types de données identiques.
Par conséquent, il est impossible de filtrer les données par année et de déterminer facilement l'année la plus rentable pour chaque entreprise. Pour réaliser ces analyses, vous devrez dépivoter (aplatir) les données, ce qui implique de convertir les données d'un tableau large en un tableau long en convertissant chaque colonne d'année en ligne. Autrement dit, toutes les valeurs financières doivent être stockées dans un seul champ.
Tout d’abord, sélectionnez une cellule dans les données et, dans l’onglet Données du ruban, cliquez sur À partir du tableau/de la plage.
ملاحظة
À ce stade, si vos données ne sont pas formatées sous forme de tableau Excel, une boîte de dialogue apparaîtra vous demandant de résoudre ce problème afin que l'éditeur Power Query puisse lire vos données plus facilement.
Ensuite, dans l'éditeur Power Query, sélectionnez les en-têtes de toutes les colonnes à dé-pivoter. Dans cet exemple, cliquez sur l'en-tête « 2020 », maintenez la touche Maj enfoncée, puis cliquez sur l'en-tête « 2024 ».
Maintenant, dans l’onglet Transformer du ruban, développez le menu déroulant Colonnes non pivotées et cliquez sur Colonnes non pivotées sélectionnées uniquement.
Désormais, chaque colonne est un champ distinct (entreprise, année et bénéfice) et chaque ligne est un enregistrement détaillé de l'entreprise.
Enfin, double-cliquez sur les en-têtes de colonne pour les renommer afin qu'ils correspondent au champ qu'ils représentent, puis cliquez sur les icônes de formatage des nombres pour vous assurer que chaque colonne contient le type de données correct.
Maintenant, lorsque vous cliquez sur "fermer et charger" Dans l’onglet Accueil du ruban, le tableau non croisé dynamique est envoyé vers une nouvelle feuille de calcul dans votre classeur Excel.
Par conséquent, vous pouvez effectuer des analyses de données qui étaient impossibles avant la normalisation du tableau, comme filtrer les données par année et par entreprise ou trier les bénéfices des entreprises par ordre décroissant.
ملاحظة
Après avoir ajouté des données supplémentaires au tableau d'origine, par exemple un autre tableau de chiffres annuels, accédez au tableau créé précédemment dans Power Query et, dans l'onglet Requête, cliquez sur Actualiser. Excel ajoutera les nouvelles données au tableau obtenu dans son format non croisé dynamique, vous évitant ainsi de revenir à l'éditeur Power Query à chaque fois.
4. Remplissez les cellules vides en vous basant sur la cellule ci-dessus (ou ci-dessous) :
Comme je l'ai mentionné précédemment, les cellules vides dans un ensemble de données peuvent causer des problèmes lors du tri et du filtrage des données ou de l'utilisation de formules faisant référence aux en-têtes de colonnes. Il est donc recommandé de les remplir.
Dans cet exemple, seul le premier joueur de chaque équipe reçoit un numéro d'équipe dans la colonne A. Par conséquent, si vous réorganisez les données, vous ne pourrez pas savoir quel joueur appartient à quelle équipe. De plus, vous ne pouvez actuellement pas filtrer les données par numéro d'équipe.
Idéalement, les cellules A3 à A5 devraient contenir le chiffre 1, les cellules A7 à A9 le chiffre 2, et ainsi de suite. Saisir ces chiffres manuellement prend du temps, surtout si le jeu de données contient de nombreuses lignes, comme dans cet exemple. Utilisez Power Query pour remplir les cellules vides en quelques secondes.
Tout d’abord, après avoir sélectionné une cellule dans les données, cliquez sur « À partir du tableau/de la plage » dans l’onglet Données du ruban.
Lorsque vous ouvrez l'éditeur Power Query, vous remarquerez que les cellules vides de la colonne en question contiennent le mot « null ». Pour corriger ce problème, sélectionnez la colonne en cliquant sur son en-tête, puis dans l'onglet « Transformation », cliquez sur « Remplir ».
Ensuite, choisissez si vous souhaitez remplir les données par ordre décroissant ou croissant. Cliquer sur « Descendre » permet de trouver une cellule de la plage sélectionnée contenant une valeur et de remplir les cellules vides situées en dessous avec la même valeur. Cliquer sur « Monter » permet de remplir les cellules vides situées au-dessus de la cellule contenant une valeur. Dans ce cas, comme seule la première ligne de chaque groupe contient un nombre, elle doit être remplie par ordre décroissant.
Désormais, chaque joueur dispose d'un numéro d'équipe correctement attribué, vous pouvez donc cliquer sur Fermer et charger dans l'onglet Accueil pour envoyer la requête vers une nouvelle feuille de calcul.
Grâce à ce changement rapide mais important, vous pouvez trier et filtrer l'ensemble de données, sachant que vous ne perdrez pas la trace de quel joueur fait partie de quelle équipe.
Si vous disposez de plusieurs tableaux dans des feuilles de calcul Excel distinctes, à condition qu'ils aient les mêmes en-têtes de colonnes, vous pouvez utiliser Power Query pour consolider les données en un seul tableau. Cela implique une étape supplémentaire (mais simple) : créer des liens de données entre chaque tableau avant de combiner les requêtes dans l'éditeur Power Query.
Utiliser efficacement Power Query ne nécessite pas de connaissances approfondies en programmation. Maîtriser quelques commandes de base peut faire toute la différence dans votre façon de travailler avec les données. Les commandes que nous avons présentées vous aident à éliminer les données superflues, à transformer des tableaux complexes en formats organisés et à obtenir des résultats précis avec un minimum d'effort. Une fois que vous les aurez essayées, vous comprendrez comment Power Query transforme votre expérience Excel, passant d'une simple feuille de calcul à un environnement de traitement de données flexible et intelligent.
