Comment utiliser QUERY Excel pour extraire et analyser vos données ?

Qu'est-ce que QUERY Excel et pourquoi l'utiliser ?

La fonction QUERY (ou Power Query, dans les versions récentes d'Excel) est un outil d'extraction, de transformation et de chargement (ETL) de données intégré à Excel. Elle permet de se connecter à différentes sources de données (fichiers Excel, bases de données, fichiers texte, etc.), de filtrer, trier, agréger et transformer ces données, puis de les charger dans une feuille de calcul Excel pour analyse et reporting.

Les avantages de QUERY Excel

• Gain de temps : Automatisez l'extraction et la transformation de données, réduisant ainsi le temps passé sur des tâches manuelles répétitives.
• Flexibilité : Connectez-vous à diverses sources de données et combinez-les pour obtenir une vue complète de votre activité.
• Fiabilité : Assurez-vous que vos données sont toujours à jour grâce à la possibilité d'actualiser les requêtes.
• Facilité d'utilisation : L'interface graphique conviviale de Power Query rend l'ETL accessible même aux utilisateurs non techniques.
• Puissance : Réalisez des transformations complexes de données grâce à un langage de requête puissant (M).

Accéder à QUERY dans Excel

La manière d'accéder à QUERY dépend de votre version d'Excel :

• Excel 2010 et 2013 : Vous devez télécharger et installer le complément Power Query depuis le site de Microsoft.
• Excel 2016 et versions ultérieures : Power Query est intégré à Excel sous l'onglet "Données", groupe "Récupérer et transformer des données".

Où trouver Power Query dans Excel ?

1. Ouvrez Excel.
2. Cliquez sur l'onglet "Données".
3. Recherchez le groupe "Récupérer et transformer des données". Vous y trouverez les commandes suivantes :
   • Obtenir des données externes : Permet de se connecter à différentes sources de données.
   • Requêtes et connexions : Affiche la liste des requêtes et connexions existantes.
   • Lancer l'éditeur Power Query : Ouvre l'éditeur Power Query pour créer et modifier des requêtes.

Créer une requête QUERY étape par étape

Voici les étapes générales pour créer une requête QUERY :

1. Sélectionner la source de données :

   • Cliquez sur "Données" > "Obtenir des données externes".
   • Choisissez le type de source de données (par exemple, "À partir d'un fichier" > "À partir d'un classeur Excel").
   • Sélectionnez le fichier contenant les données que vous souhaitez importer.

2. Naviguer dans les données :

   • L'éditeur Power Query s'ouvre, affichant un aperçu des données de votre source.
   • Sélectionnez la feuille de calcul ou la table contenant les données à importer.

3. Transformer les données (facultatif) :

   • Utilisez les outils de l'éditeur Power Query pour filtrer, trier, supprimer des colonnes, remplacer des valeurs, etc.
   • Par exemple, pour filtrer les données, cliquez sur la flèche en haut d'une colonne et choisissez les critères de filtrage.

4. Charger les données :

   • Cliquez sur "Fermer et charger" pour charger les données transformées dans une nouvelle feuille de calcul Excel.
   • Vous pouvez également choisir de charger uniquement la connexion et de créer un tableau croisé dynamique ou un graphique à partir des données.

Exemple pratique : Filtrer les ventes par région

Supposons que vous ayez un tableau Excel contenant les ventes de différents produits dans différentes régions. Vous souhaitez extraire uniquement les ventes de la région "Nord".

1. Importez le tableau Excel dans Power Query.
2. Sélectionnez la colonne "Région".
3. Cliquez sur la flèche en haut de la colonne "Région".
4. Choisissez "Filtres de texte" > "Égal à".
5. Entrez "Nord" dans la boîte de dialogue.
6. Cliquez sur "OK".
7. Cliquez sur "Fermer et charger" pour charger les données filtrées dans une nouvelle feuille de calcul.

Exemple avancé : Combiner plusieurs fichiers Excel

Imaginez que vous ayez plusieurs fichiers Excel contenant des données de ventes mensuelles. Vous souhaitez combiner ces fichiers en un seul tableau pour avoir une vue d'ensemble des ventes annuelles.

1. Cliquez sur "Données" > "Obtenir des données externes" > "À partir d'un dossier".
2. Sélectionnez le dossier contenant les fichiers Excel.
3. Power Query affiche la liste des fichiers dans le dossier.
4. Cliquez sur "Combiner et transformer".
5. Power Query analyse les fichiers et vous propose un exemple de fichier à utiliser comme modèle.
6. Si les fichiers ont la même structure, Power Query combine automatiquement les données de tous les fichiers.
7. Vous pouvez ensuite transformer les données combinées si nécessaire.
8. Cliquez sur "Fermer et charger" pour charger les données combinées dans une nouvelle feuille de calcul.

Les fonctions clés de QUERY Excel

Power Query offre une large gamme de fonctions pour transformer les données. Voici quelques-unes des plus importantes :

• Filtrer des lignes : Permet de sélectionner uniquement les lignes qui répondent à certains critères.
• Trier des colonnes : Permet de trier les données par ordre croissant ou décroissant.
• Supprimer des colonnes : Permet de supprimer les colonnes inutiles.
• Remplacer des valeurs : Permet de remplacer des valeurs erronées ou manquantes.
• Ajouter une colonne calculée : Permet de créer une nouvelle colonne en effectuant des calculs sur les colonnes existantes.
• Grouper par : Permet d'agréger les données par groupe (par exemple, calculer la somme des ventes par région).
• Fusionner des requêtes : Permet de combiner des données provenant de différentes sources (équivalent à la fonction RECHERCHEV).
• Ajouter des colonnes conditionnelles : Permet de créer des colonnes basées sur des conditions (équivalent à la fonction SI).

Le langage M

Le langage M est le langage de programmation utilisé par Power Query. Il permet de réaliser des transformations de données plus complexes que celles possibles avec l'interface graphique. Bien qu'il ne soit pas nécessaire de maîtriser le langage M pour utiliser Power Query, sa connaissance peut être très utile pour automatiser des tâches complexes et personnaliser vos requêtes.

Bonnes pratiques et astuces pour optimiser vos requêtes QUERY

• Nommez vos requêtes de manière descriptive : Cela facilitera l'identification et la gestion de vos requêtes.
• Évitez d'importer des colonnes inutiles : Cela réduira la taille de vos données et améliorera les performances.
• Utilisez le filtrage dès le début de la requête : Cela réduira la quantité de données à traiter par les étapes suivantes.
• Actualisez régulièrement vos requêtes : Pour vous assurer que vos données sont toujours à jour.
• Utilisez les commentaires pour documenter vos requêtes : Cela facilitera la compréhension et la maintenance de vos requêtes par vous-même et par d'autres utilisateurs.
• Apprenez le langage M : Pour réaliser des transformations de données plus complexes et personnaliser vos requêtes.

Erreurs courantes et comment les éviter

• Erreur de type de données : Assurez-vous que le type de données des colonnes est correct (par exemple, nombre, texte, date).
• Erreur de source de données : Vérifiez que la source de données est accessible et que les informations de connexion sont correctes.
• Erreur de syntaxe du langage M : Vérifiez que la syntaxe de vos expressions en langage M est correcte.
• Problèmes de performance : Optimisez vos requêtes en filtrant les données dès le début et en évitant d'importer des colonnes inutiles.

Si vous rencontrez des erreurs, consultez la documentation de Power Query ou recherchez des solutions en ligne.

Conclusion

La fonction QUERY (Power Query) est un outil puissant et polyvalent pour extraire, transformer et charger des données dans Excel. En maîtrisant ses différentes fonctionnalités, vous pouvez automatiser des tâches complexes, améliorer l'efficacité de vos analyses et prendre des décisions éclairées basées sur des données fiables. N'hésitez pas à explorer les nombreuses ressources disponibles en ligne pour approfondir vos connaissances et exploiter pleinement le potentiel de QUERY Excel.