Pierre Fauconnier
Power Query Excel: Alternative efficace au filtre avancé, dynamique de surcroît!
par , 18/09/2019 à 05h23 (14460 Affichages)
Salut.
Je vois régulièrement passer sur le forum des demandes pour extraire des données d'une table en fonction d'une condition. Il existe depuis très longtemps les filtres avancés sur Excel pour réaliser la manipulation. Mais il faut avouer que l'outil a vieilli, notamment parce qu'il ne prend pas en compte les tables de données ou tableaux structurés.
Lorsque je dis qu'il a vieilli, c'est que l'ergonomie est loin d'être au top, que l'extraction étant régulièrement réalisée vers une autre feuille, il ne faut pas se louper dans la manœuvre et que le résultat de l'extraction n'est pas déposé dans une table de données, mais dans une plage classique. De plus, tout ou presque est à refaire lorsque les données de la source ont été modifiées:
- condition qui change;
- ajout, modification ou suppression de données.
EUREKA!! Si vous utilisez une version au moins égale à XL2010, Power Query offre une solution légère, rapide et DYNAMIQUE sans aucune ligne de VBA, sans nécessiter la présence d'une zone de critères, sans besoin de relancer "toute" la mécanique du filtre avancé...
Considérons la table suivante reprenant des données de vente, nommée t_Ventes.
On souhaite extraire de cette table les données concernant la Belgique dans une table qui sera créée sur une autre feuille. On souhaite en plus placer la colonne Pays en début de table et ne pas reprendre la colonne de la zone. On souhaite également que les conditions d'extraction puissent être modifiées par la suite. Bref, du travail pour le filtre avancé... mais aussi et surtout pour Power Query.
On va d'abord ajouter une colonne formulée en exprimant la condition souhaitée (ici, [Pays] = "Belgique")
En se plaçant dans la table, on crée la requête Power Query via l'onglet Données
Power Query s'ouvre et la requête est affichée. Ici comme pour les autres étapes, ne vous préoccupez pas de ce que vous voyez apparaître dans la barre de formule de Power Query. C'est le script enregistré lors de votre manipulation (une sorte d'enregistreur de macro à la sauce Power Query).
Avant toute modification de la table, on remarque que la colonne Date est passée en DateTime et on la remet en Date. Ici comme partout avec Office, le clic droit nous conduit où il faut...
Nous allons filtrer la table comme nous le ferions dans Excel, en choisissant True dans la colonne Condition (Eh oui, Power Query parle anglais). Ce filtre se place comme dans Excel, en cliquant sur l'outil de filtre placé sur les intitulés de colonnes (ici aussi, on remarque au passage la ligne de script Power Query sur la barre de formule)... On en profite pour modifier le nom de la requête par clic droit sur la requête dans le panneau de gauche...
A ce stade, nous avons presque fini notre travail... Nous devons encore réorganiser les colonnes et supprimer les deux que nous ne souhaitons pas récupérer (Zone et Condition). Déplacer une colonne s'effectue par simple glisser-déplacer, la suppression des deux colonnes s'effectuant par clic droit.
Il nous reste à renvoyer le résultat de la requête dans Excel, ce qui s'effectue par le bouton Fermer et Charger. Ici, pour les besoins du billet, je vais effectuer la manipulation en choisissant Fermer et charger dans... tout à gauche du bouton d'accueil, et placer le résultat sur la même feuille que celle des données...
Et voilà le travail. Et après, si la condition est modifiée, que faut-il faire? Pas grand-chose, en fait:
- changer la formule de la condition;
- rafraîchir la requête.
Si je veux les ventes du Bénélux supérieures à 20.000 euros, j'adapte la formule de la façon suivante puis j'actualise ma requête (la table verte) par clic droit... Je procéderai aussi en actualisant la table de résultat si j'ai modifié, ajouté ou supprimé des données. De surcroît, Power Query permet que les données de la source ne soient pas dans le même classeur. Elles peuvent même être d'une autre nature qu'Excel (Access, Sql, csv, ...) car Power Query est un puissant intégrateur de données permettant de les préparer pour l'analyse avec notre outil préféré...
Conclusions
Power Query est un outil puissant aux ressources multiples. Il est l'allié idéal des tables de données (tableaux structurés) pour plus d'efficacité à moindre coût avec Excel...
Et vous, vous connaissiez cette technique?
Consulter le profil
Marquer ce blog comme lu
