Comment utiliser les requêtes et connexions dans Excel avec Power Query

Maîtriser les formules Excel est une compétence utile, surtout si vous travaillez avec de grands jeux de données et plusieurs feuilles de calcul à la fois. Dans un autre article, nous vous avons montré les deux procédures de base pour transférer automatiquement des données d'une feuille de calcul Excel à une autre comme alternative simple au copier-coller manuel. Même si Coller avec liaison et les références entre feuilles de calcul peuvent vous simplifier la vie, Excel propose des fonctionnalités intégrées qui demandent encore moins d'effort.

Dans ce guide, nous vous montrons comment utiliser Power Query dans Excel pour importer des données de différentes sources, puis combiner et fusionner des tableaux comme vous le souhaitez, sans avoir à apprendre de code.

Qu'est-ce que Power Query dans Excel ?

Power Query est un outil de Microsoft Excel qui simplifie les deux tâches principales de tout utilisateur de tableur : importer et trier des données. Avant de présenter toutes ses applications, voyons brièvement comment il fonctionne.

Comment fonctionne Power Query dans Excel ?

Les requêtes Power Query sont des commandes configurées dans Excel qui se décomposent en plusieurs étapes :

• Importation ou connexion : Vous pouvez connecter des données stockées dans le cloud, dans un service spécifique ou sur votre ordinateur. Vous pouvez aussi vous connecter à plusieurs sources pour réunir toutes vos données au même endroit.
• Modification ou transformation : Vous pouvez modifier les données comme vous le souhaitez, car ces changements n'affectent pas la source d'origine.
• Automatisation ou chargement : Une fois la requête terminée, vous pouvez la charger dans une feuille de calcul ou dans le modèle de données et l'actualiser régulièrement, sans refaire le même processus.

Alors, Power Query est-il facile à utiliser ? Oui. Power Query ne demande aucune connaissance en code ni en programmation et, depuis 2016, il est intégré à Excel : plus besoin d'installer un outil tiers. Comme tout outil d'automatisation, son principal atout est d'obtenir les mêmes résultats en deux fois moins de temps.

Comment utiliser Power Query dans Excel ?

Avant de voir comment utiliser Power Query dans Excel, notez qu'il ne fonctionne que dans les versions suivantes d'Excel :

• Power Query pour Excel 2010 et 2013 : Pour l'utiliser, vous avez besoin du complément Power Query de Microsoft. Microsoft ne prend plus en charge ces deux versions. Malheureusement, aucun complément n'est disponible pour macOS et vous pouvez seulement actualiser la requête existante, sans en créer une nouvelle ni la modifier.
• Power Query pour Excel 2016, 2019, 2021, 2024 ou Microsoft 365 : Selon la version, Power Query apparaît dans le groupe "Récupérer et transformer des données" ou sous le bouton "Obtenir des données". Cette fonctionnalité intégrée vous permet de créer et de modifier des requêtes sous Windows et Mac.

Cet article vous montre comment utiliser Power Query dans Excel pour Microsoft 365 sur Mac.

Comment importer des données avec Power Query dans Excel ?

Importer vos données avec Power Query est simple, car Excel propose une grande variété de connexions de données accessibles depuis la barre d'outils. Maintenant que vous savez ce qu'est Power Query et quels formats de données il peut importer, voyons comment importer des données depuis une feuille de calcul Excel.

Comment importer les données d'une seule feuille avec Power Query dans Excel ?

Imaginez que vous voulez analyser les ventes de voitures d'un employé en 2021. Avec Power Query, vous pouvez importer les données sans toucher au fichier source.

1. Ouvrez une nouvelle feuille de calcul Excel et accédez à Données > Obtenir des données (Power Query). Comme expliqué plus haut, plusieurs options existent selon le type de source. Ici, je sélectionne "Obtenir des données (Power Query)" pour importer une feuille de calcul enregistrée en local.

2. Sélectionnez "Classeur Excel".

3. Une fois la source de données choisie, définissez les "Paramètres de connexion". Ici, je peux sélectionner le fichier local en cliquant sur "Parcourir".

4. Pour une recherche plus rapide, tapez le nom du fichier source dans la barre de recherche. Ici, j'ai tapé "SALES". Sélectionnez le fichier, cliquez sur "Obtenir des données", puis sur le bouton "Suivant" en bas à droite de la fenêtre d'origine.

5. Vous devriez maintenant voir la fenêtre d'aperçu des données. Sous Windows, vous pouvez transformer ou modifier vos données à cette étape. Sur Mac, vous pouvez aussi cliquer sur Transformer les données dans Excel pour Microsoft 365. Les anciennes versions pour Mac permettent seulement de charger les données et d'appliquer les modifications ensuite.

Une fois les données chargées, Excel enregistre automatiquement la requête. Chaque fois que vous voulez importer ou mettre en forme (sous Windows), cliquez sur l'icône "Actualiser" en haut à droite pour répéter les étapes. Vous éliminez ainsi le travail manuel répétitif et les erreurs humaines inutiles.

Comment importer les données de plusieurs feuilles avec Power Query dans Excel ?

Comme mentionné plus haut, Power Query peut importer des données à partir de plusieurs fichiers en même temps. C'est très utile quand vous ne voulez pas importer toutes les données d'un gros fichier, mais seulement certaines feuilles. Voici comment récupérer les données de plusieurs feuilles.

1. Répétez les étapes 1 à 3 de la section précédente.

2. Recherchez le fichier général plutôt qu'une feuille précise. Ici, je recherche mon fichier "SALES2021". Cliquez sur "Obtenir des données", puis sur "Suivant" en bas à droite.

3. Vous pouvez maintenant sélectionner les feuilles précises que vous voulez importer. J'ai sélectionné deux feuilles, une pour chaque vendeur que je veux comparer. Cliquez ensuite sur "Charger".

4. Vous devriez maintenant avoir un nouveau classeur Excel avec les deux feuilles chargées. Ci-dessous, vous voyez comment Excel a converti mes données en tableau, chacun dans un onglet distinct.

Sous Windows, Power Query vous permet aussi de charger les données dans un tableau croisé dynamique, dans un graphique croisé dynamique ou simplement de créer une connexion pour la requête. Cette dernière option n'importe pas de données, mais elle reste utile pour accélérer tout processus de transformation des données.

Maintenant que vous savez charger les données d'une feuille ou de plusieurs feuilles, voyons comment modifier la requête dans Excel.

Comment modifier les propriétés de la plage de données externes dans Excel ?

Pour ajuster vos prochaines importations avec Power Query, modifiez les paramètres de la plage de données externes.

1. Accédez à Données > Propriétés pour modifier l'un des éléments suivants.

Voici un résumé des trois propriétés les plus utiles pour vos prochaines requêtes :

• Définition de requête : Laissez cochée la case "Enregistrer la définition de la requête" et donnez-lui un nom plus facile à retenir. N'oubliez pas que Power Query sert à réduire le nombre d'étapes dans les processus de transformation des données.
• Contrôle de l'actualisation : Le plus important ici est l'option "Actualiser les données lors de l'ouverture du fichier". Si vous la choisissez, toute modification apportée au fichier d'origine se répercute automatiquement dans le fichier de destination.
• Mise en forme et disposition des données : Si vous sélectionnez "Insérer des cellules pour les nouvelles données, supprimer les cellules inutilisées", les nouvelles lignes ajoutées au fichier source seront transférées et mises en forme comme le reste du tableau Power Query, et les cellules inutilisées seront supprimées.

Il est important de comprendre comment Excel peut transformer vos données automatiquement pour gagner en productivité. Si les fonctionnalités de base de Power Query dans Excel fonctionnent de façon similaire sous Windows et Mac, les fonctionnalités d'automatisation plus avancées peuvent varier nettement.

Pour éviter les incompatibilités entre versions et créer le même workflow automatisé de feuilles de calcul sur n'importe quel système d'exploitation, vous pouvez aussi consolider des fichiers Excel automatiquement avec Sheetgo, une plateforme d'automatisation des données basée sur l'IA.

Comment supprimer une requête de Requêtes et connexions dans Excel

Si vous voulez supprimer une connexion Power Query dans Excel, suivez ces étapes :

Excel répertorie toutes les requêtes du classeur dans le volet Requêtes et connexions. De là, vous pouvez actualiser une requête, ouvrir ses propriétés ou la supprimer.

1. Accédez à Données > Requêtes et connexions.

2. Cliquez sur la requête à supprimer et sélectionnez "Supprimer".

3. Excel affiche un message d'alerte pour vous demander de confirmer.

Si vous n'utilisez pas d'outil d'automatisation des workflows, Power Query est un bon moyen d'automatiser les mises à jour, les modifications ou les tâches répétitives, et aussi de suivre les changements apportés au fichier.

Conclusion

Dans cet article, nous vous avons montré comment utiliser Power Query à un niveau de base dans Excel 2016 et les versions ultérieures, y compris Microsoft 365, sur Mac et Windows. C'est un outil de requête puissant qui importe des données de nombreuses sources, quel que soit le type de fichier ou le système de stockage. En quelques étapes, vous pouvez automatiser vos tâches répétitives les plus courantes, éviter les erreurs manuelles et rendre votre entreprise plus efficace.

Si vous voulez découvrir des méthodes plus avancées pour automatiser votre travail dans Excel, consultez notre guide sur l'utilisation des macros dans Excel pour automatiser des tâches.

Power Query importe aussi les fichiers texte/CSV. Si vous recevez un nouvel export CSV chaque semaine, découvrez comment importer automatiquement un CSV dans Excel. Pour ne déplacer que certaines lignes, découvrez comment transférer des données Excel filtrées d'un classeur à un autre.