Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    14 749
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 14 749
    Points : 40 641
    Points
    40 641
    Billets dans le blog
    58
    Par défaut Power Query, alternative efficace et dynamique au filtre avancé d'Excel
    Salut.

    Voyant régulièrement des demandes concernant l'extraction de données d'une table de données, je vous propose un nouveau billet traitant d'une alternative efficace et dynamique à l'utilisation du filtre avancé. La dynamique liée à Power Query permet une actualisation de l'extraction sans aucune ligne de VBA et sans devoir "relancer" le filtre avancé...

    Et vous...
    Connaissiez-vous cette technique?
    Maîtrisez-vous Power Query?
    Avez-vous pu "refaire l'exercice" pour prendre Power Query en mains?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  2. #2
    Expert éminent sénior

    Homme Profil pro
    Développeur Vba Excel
    Inscrit en
    avril 2013
    Messages
    4 330
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Développeur Vba Excel
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : avril 2013
    Messages : 4 330
    Points : 11 027
    Points
    11 027
    Billets dans le blog
    29
    Par défaut
    Citation Envoyé par Pierre Fauconnier Voir le message
    Bonjour Pierre,

    Ta contribution vient à point nommé, je suis entrain en ce moment de mettre au point un code VBA pour automatiser des filtres avancés. Je vais regarder ça de plus près. Il y a parfois des synchronicités...
    Eric KERGRESSE
    https://sites.google.com/site/erickergresseeirl/
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter

  3. #3
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    14 749
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 14 749
    Points : 40 641
    Points
    40 641
    Billets dans le blog
    58
    Par défaut
    Salut Eric,

    Je pense que tu vas aimer... Plus de VBA et plus de manipulations. Juste mettre en place le système puis actualiser...

    Ton retour d'expérience m'intéressera, dans la suite de ce fil
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  4. #4
    Membre du Club
    Homme Profil pro
    autre
    Inscrit en
    septembre 2015
    Messages
    18
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Yvelines (Île de France)

    Informations professionnelles :
    Activité : autre

    Informations forums :
    Inscription : septembre 2015
    Messages : 18
    Points : 40
    Points
    40
    Par défaut
    L’éditeur de requêtes est assez pratique, ainsi, j’ai tendance à ne pas créer de colonne critère.

    Power Query est très pratique avec une base de données.

    Pour des requêtes paramétrables, je crée un tableau qui ne contient qu’un paramètre, crée une requête qui retourne la valeur de ce paramètre (requêtes d’après un tableau, drill down sur la valeur). Sauve la requête sans créer de table (admettons sous le nom ville) Ensuite, dans l’éditeur de requêtes, je filtre mon tableau en sélectionnant une ville (ex Bruxelles), puis remplace dans la formule de filtre, "Bruxelles" (avec guillemets) par ville (sans guillemets). Cela crée une requête paramètrable : on change le paramètre, actualisons le tableau filtré et le tour est joué.

  5. #5
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    14 749
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 14 749
    Points : 40 641
    Points
    40 641
    Billets dans le blog
    58
    Par défaut
    Chaque technique a ses avantages.

    L'avantage de la colonne critère, c'est qu'elle permet de réaliser la requête sur n'importe quel critère formulable en Excel. C'est, en fait, le premier objectif du billet: montrer comment on remplace le critère formulé du filtre avancé en Power Query. Mais c'est au prix d'un traitement Excel sur l'entièreté des lignes. Ca a éventuellement un coût en terme de performance.

    Si je veux critériser sur un pays en particulier, je préfère alors la technique suivante qui n'impose pas les manips que tu détailles côté Power Query, car je ne comprends pas bien l'utilité du drill down dans ce cas ni de la modification du filtre. 78Chris parlait, dans une réponse sur mon blog, d'une plage nommée, et il proposait alors la même manip que toi sur la requête vide et la modif "à la main" de l'expression du critère.

    Je crée une table pour le pays critérisé au lieu de la plage nommée dont 78Chris parlait (Ca coûte la ligne d'entête en plus), car ça me permet de réaliser le filtre par fusion de requête en "inner join" sans devoir transformer à la main l'expression du critère dans Power Query. Pour le même prix et sans les manips dans PQ, on a la possibilité de filtrer sur plusieurs pays. Ca ne nécessite aucune modif à la main côté Power Query.

    Nom : 2019-09-19_195702.png
Affichages : 2401
Taille : 21,6 Ko

    Nom : 2019-09-19_091708.png
Affichages : 2402
Taille : 18,4 Ko

    Nom : 2019-09-19_195715.png
Affichages : 2399
Taille : 52,8 Ko

    Nom : 2019-09-19_195739.png
Affichages : 2386
Taille : 56,7 Ko



    Quoi qu'il en soit, et quelle que soit la technique privilégiée, je pense que Power Query sonne le glas du filtre avancé (deuxième objectif de ce billet).
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  6. #6
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    février 2010
    Messages
    6 380
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : février 2010
    Messages : 6 380
    Points : 11 884
    Points
    11 884
    Par défaut
    RE à tous

    Je précise que dans ma proposition, on n'associe la variable qu'une fois à la construction de la requête.

    Donc ce n'est pas si lourd. C'est surtout quand on ne veut pas trop de cellules en plus...

    Mais de façon générale et surtout pour le multicritères, la requête croisant tableau de données et tableau de critères est aussi ce que j'utilise le plus.

    De toute façon dans la liste des critères du filtre avancé il fallait bien aussi une ligne d'en-tête donc pour tous ceux qui utilisaient le filtre avancé, cela ne change guère la logique en y apportant un gros plus.
    Chris

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  7. #7
    Membre averti Avatar de Bryce de Mouriès
    Profil pro
    CPI
    Inscrit en
    mars 2007
    Messages
    189
    Détails du profil
    Informations personnelles :
    Âge : 32
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : CPI

    Informations forums :
    Inscription : mars 2007
    Messages : 189
    Points : 353
    Points
    353
    Par défaut
    J'ai eu une formation Power Query il y a deux ans, avec un peu de Power BI aussi

    Je trouve ça hyper utile pour croiser / fusionner de la donnée. Cette année j'ai changé de poste avec des excels sur plusieurs équipes sur différents supports, différents formats etc. Avec Power Query je peux facilement me faire des tableaux des bords pour croiser X fichiers excels, travailler rapidement le format, sans formules / sans VBA.
    Ca fait des fichiers de reporting rapides car aucune formule, le temps d'attente n'est qu'à l'actualisation du fichier. Ca s'interface facilement avec des fichiers stockés sur Teams, Sharepoint, OneDrive, des WebServices, du fichier texte etc.
    C'est comme un mini ETL, directement dans Excel.

    Avant cette formation j'en avais jamais entendu parler, je suis assez étonné que ça ne soit pas plus répandu finalement.
    Infinity - To The Top, shoot'em up développé en Haxe / OpenFL pour FLASH et Android, piou piou rythmé dans l'espace

  8. #8
    Membre expert Avatar de QuestVba
    Homme Profil pro
    Enseignant
    Inscrit en
    juillet 2012
    Messages
    2 197
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 49
    Localisation : Belgique

    Informations professionnelles :
    Activité : Enseignant
    Secteur : Service public

    Informations forums :
    Inscription : juillet 2012
    Messages : 2 197
    Points : 3 595
    Points
    3 595
    Par défaut
    Merci, Pierre.
    Ce que j'aime : en quelques mots, en quelques images... tu titilles nos cerveaux à partir d'exemples simples et efficaces.
    Maintenant, j'utilise PQ très régulièrement et c'est un outil très performant. Seule limite (pour le moment, pour mon cas), le nombre maximal de lignes.

  9. #9
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    14 749
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 14 749
    Points : 40 641
    Points
    40 641
    Billets dans le blog
    58
    Par défaut
    Salut QuestVBA,

    Merci pour ton appréciation.

    Qu'entends-tu par "limité par le nombre de lignes"?

    Le nombre de lignes restituées dans Excel est évidemment de 1 048 576 qui est une limite EXCEL, mais pas une limite PowerQuery. Tu peux intégrer des tables avec beaucoup plus de lignes dans PQ, du moment que la requête renvoyée dans Excel ne contienne pas plus de lignes que la limite de ta feuille.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  10. #10
    Membre expert Avatar de QuestVba
    Homme Profil pro
    Enseignant
    Inscrit en
    juillet 2012
    Messages
    2 197
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 49
    Localisation : Belgique

    Informations professionnelles :
    Activité : Enseignant
    Secteur : Service public

    Informations forums :
    Inscription : juillet 2012
    Messages : 2 197
    Points : 3 595
    Points
    3 595
    Par défaut
    C'est bien cela, Pierre ; la limite d'excel en nombre de lignes.
    Je travaille sur de très grands fichiers (à ma petite échelle) qui peuvent facilement dépasser 2.000.000 de lignes et donc... Mais, il suffit de trouver quelques subterfuges (comme découper son fichier...).

    Mais, je le répète : au début de l'année 2019, on m'a mis dans les mains une dizaine de DB à recouper... et pour le moment, j'ai pu tout faire avec PQ et assez facilement ; bon, les formules c'est pas toujours évident à cause des réflexes Excel. Pour cela, j'utilise souvent : https://docs.microsoft.com/en-us/pow...tion-reference.

  11. #11
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    14 749
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 14 749
    Points : 40 641
    Points
    40 641
    Billets dans le blog
    58
    Par défaut
    Ok. Merci pour la précision...

    Le lien que tu renseignes est à mon sens la bible pour découvrir les fonctions Power Query. Merci de l'avoir donné ici
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

Discussions similaires

  1. Liste déroulante dynamique avec filtre
    Par merlinus3000 dans le forum Excel
    Réponses: 7
    Dernier message: 05/01/2014, 19h16
  2. [XL-2007] Tableau dynamique avec filtre et champs calculés
    Par Kanou92 dans le forum Excel
    Réponses: 0
    Dernier message: 17/12/2013, 16h16
  3. [XL-2007] Tableau croisé dynamique et filtre
    Par formabox dans le forum Macros et VBA Excel
    Réponses: 1
    Dernier message: 07/09/2012, 18h20
  4. [AC 2007] Graphique croisé dynamique et filtres
    Par cilou0904 dans le forum VBA Access
    Réponses: 1
    Dernier message: 07/09/2012, 17h42

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo