IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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


    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    16 589
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

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

    Informations forums :
    Inscription : novembre 2003
    Messages : 16 589
    Points : 47 771
    Points
    47 771
    Billets dans le blog
    88
    Par défaut Power Query: Fusion de tables et extraction, RECHERCHEV et filtre avancé en quelques clics
    Salut.

    Dans ce billet de blog, j'expliquais comment Power Query pouvait remplacer le filtre avancé en utilisant une colonne de la table comme "critère universel". Il suffit que la formule renvoie VRAI pour que l'actualisation du résultat de la requête ne reprenne que les lignes "VRAI".

    Dans cette contribution, je vais illustrer comment on peut fusionner plusieurs tables dont une de critères, pour extraire certaines données de nos tableau Excel. J'illustre par ceci une des réponses possibles à cette discussion, les autres passant soit par les filtres avancés (outil qui a mal vieilli et que Power Query remplace admirablement bien), soit par de la programmation en VBA. Power query peut donc avantageusement remplacer le VBA et dispenser le "non-programmeur" de mettre les mains dans le cambouis.

    Notez ici que je présente toutes les tables sur la même feuille à des fins didactiques, mais qu'idéalement, chaque tableau de données devrait être sur sa propre feuille. Seules les tables d'extraction et de résultat peuvent cohabiter sur la même feuille, selon moi. On pourrait bien entendu placer une liste de validation dans le tableau des critères, mais ce n'est pas l'objet de cette contribution.

    La situation qui nous occupe est la suivante. On dispose d'une table des véhicules et d'une table des interventions à effectuer sur ceux-ci, et l'on souhaite obtenir une table reprenant les informations de ces deux sources en filtrant les véhicules. L'idée va être de créer une troisième table reprenant les plaques d'immatriculation et de s'en servir pour critériser notre extraction.

    Nom : 2020-04-15_074527.png
Affichages : 485
Taille : 8,7 Ko


    En se plaçant sur une des tables, on l'incorpore dans la solution Power Query via l'onglet Données

    Nom : 2020-04-15_074647.png
Affichages : 493
Taille : 36,1 Ko


    On remarque, en cliquant sur l'étape "Source", la ligne de commande Power Query qui intègre le tableau. Il suffit de copier la commande, puis, en une nouvelle requête vide, de copier la ligne de commande et de modifier le nom du tableau incorporé. On répètera l'opération pour le troisième tableau

    Nom : 2020-04-15_074730.png
Affichages : 478
Taille : 70,8 Ko

    Nom : 2020-04-15_074843.png
Affichages : 491
Taille : 86,8 Ko

    Nom : 2020-04-15_074957.png
Affichages : 468
Taille : 8,5 Ko


    Par clic droit, on pourra renommer les requêtes

    Nom : 2020-04-15_075105.png
Affichages : 479
Taille : 10,7 Ko


    Après cette première étape, on va fusionner les tables des véhicules et des interventions pour récupérer les colonnes des deux tables. En se plaçant sur la requête qui servira de tableau final, on appelle l'outil de fusion qui va permettre un "RechercheV" à la sauce Power Query, et via cet outil, on va lier les tables sur la colonne commune, à savoir celle des plaques d'immatriculation. Dans le bas de la fenêtre, on vérifie bien que la liaison (la "jointure") s'est effectuée par la gauche. Cela veut dire que cela reprendra toutes les lignes de la première table,et ça y joindra les colonnes de la seconde. Il est intéressant que, par rapport à un RECHERCHEV, il est possible d'effectuer la jointure sur plusieurs colonnes, et que ces colonnes pourraient être formulées en Power Query. On dispose donc ici d'un outil très puissant d'agrégation de données.

    Nom : 2020-04-15_075207.png
Affichages : 481
Taille : 94,2 Ko

    Nom : 2020-04-15_075300.png
Affichages : 480
Taille : 18,6 Ko


    Cette étape aura pour effet d'amener la table des véhicules "dans" celle des interventions, et l'on pourra éclater la table pour en sélectionner uniquement les colonnes qui nous intéressent. Ici, on va décocher la colonne "Plaque" puisqu'elle se trouve déjà dans l'autre table.

    Nom : 2020-04-15_075342.png
Affichages : 472
Taille : 8,2 Ko


    On va répéter l'opération pour lier la table des critères, mais en faisant attention à prendre une jointure interne. C'est cette jointure interne qui servira de "filtre", en ne reprenant dans le résultat que les lignes des voitures dont les plaques sont renseignées dans la table des critères.

    Nom : 2020-04-15_075409.png
Affichages : 467
Taille : 86,6 Ko


    On pourra, à cette étape et par exemple par clic droit, supprimer la colonne de cette table. Elle ne nous sert plus à rien.


    La suite se trouve dans le message suivant...
    Images attachées Images attachées   
    "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...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  2. #2
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    16 589
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

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

    Informations forums :
    Inscription : novembre 2003
    Messages : 16 589
    Points : 47 771
    Points
    47 771
    Billets dans le blog
    88
    Par défaut Intégration dans Excel et modification des critères
    Dans le premier message, j'illustre comment créer le jeu de requêtes dans Power Query.

    Nous allons voir maintenant comment récupérer la requête de résultat dans Excel, comment modifier nos critères et mettre notre requête à jour.

    Excel peut afficher les requêtes et connexions dans le panneau de droite, et nous allons pouvoir charger la requête de résultat par clic droit pour la placer sur une feuille de calcul.

    Nom : 2020-04-15_080017.png
Affichages : 455
Taille : 4,8 Ko

    Nom : 2020-04-15_080035.png
Affichages : 463
Taille : 21,7 Ko


    Nous obtenons alors notre table de résultats qui contient les lignes "filtrées" sur base de la table des critères. C'est la jointure interne réalisée dans Power Query qui permet ce filtre.

    Nom : 2020-04-15_080108.png
Affichages : 452
Taille : 53,9 Ko


    Si nous souhaitons récupérer les données pour d'autres véhicules, il suffira de saisir les plaques d'immatriculation dans la table de critères, puis d'actualiser la table des résultats par clic droit.

    Nom : 2020-04-15_080851.png
Affichages : 463
Taille : 14,5 Ko

    Nom : 2020-04-15_080913.png
Affichages : 457
Taille : 6,8 Ko



    Nous pourrions bien entendu, grâce à Power Query, réaliser ce travail avec des tables de données se trouvant dans des classeurs différents, voire dans d'autres applications de gestion de données (Access, SQL Server, fichier txt ou xml, ...). Il serait d'ailleurs judicieux, dans une entreprise dans laquelle plusieurs personnes partageraient les fichiers, de placer la table des véhicules dans un fichier à part, les interventions elles aussi dans un fichier qui leur est propre, et de placer les tableaux de critères et de résultat dans le fichier de travail.


    J'espère que cette contribution vous permet de voir UNE des possibilités de Power Query et qu'elle vous donnera le goût "de vous y mettre" si ce n'est déjà fait.
    "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...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  3. #3
    Membre chevronné Avatar de mfoxy
    Homme Profil pro
    Automation VBA
    Inscrit en
    février 2018
    Messages
    744
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : Belgique

    Informations professionnelles :
    Activité : Automation VBA
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : février 2018
    Messages : 744
    Points : 1 925
    Points
    1 925
    Par défaut
    Bonjour Pierre,

    Encore une belle démonstration, de ta part, de la puissance de power query, outils qui est selon moi est trop peu utilisé malgré son efficacité et facilité de prise en main.

    Il remplace aisément et facilement bon nombre de code Vba : synthèse, ventilation, concaténation, comparaison de données/feuilles/workbook, ainsi que les tableaux croisés dynamiques.

    Cet outil est, couplé à quelques codes Vba (refresh, export, mailing,..), le moyen par excellence ( fournis dans Excel) de fournir un travail qualitif et modulable d'analyse de "Bd" Excel, sql,...

    Vivement la suite, je me doute que ce n'est que les prémisses de ton tutoriel 😉.

    Bat,

    Mfoxy
    Michaël

    Si mon aide/avis vous a été profitable , n'hésitez pas à cliquer sur , ça fait toujours plaisir...
    _________________________________________________________________________________________________________________

    "Tout le monde est un génie. Mais si on juge un poisson sur sa capacité à grimper à un arbre, il passera sa vie à croire qu'il est stupide..."
    Albert Einstein

  4. #4
    Membre émérite
    Avatar de cb_60
    Homme Profil pro
    Chargé de mission technique
    Inscrit en
    juillet 2007
    Messages
    1 241
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Chargé de mission technique
    Secteur : Industrie

    Informations forums :
    Inscription : juillet 2007
    Messages : 1 241
    Points : 2 344
    Points
    2 344
    Billets dans le blog
    14
    Par défaut
    Bonjour
    Merci pour ce document, j'aimerais refaire l'ensemble du premier fichier, qui se trouve sur le blog, serait il possible d'avoir la base excel, pour le reconstruire de façon similaire.

    "Salut.

    Dans ce billet de blog, j'expliquais comment Power Query pouvait remplacer le filtre avancé en utilisant une colonne de la table comme "critère universel". Il suffit que la formule renvoie VRAI pour que l'actualisation du résultat de la requête ne reprenne que les lignes "VRAI"."
    L'imagination est plus importante que le savoir.... A . Einstein.
    https://www.developpez.net/forums/blogs/179588-cb_60/
    Merci de cliquer sur pour clore cette discussion.

  5. #5
    Rédacteur

    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    janvier 2010
    Messages
    10 511
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : janvier 2010
    Messages : 10 511
    Points : 24 943
    Points
    24 943
    Billets dans le blog
    28
    Par défaut
    Bonjour Pierre,
    D'abord, bravo pour ta contribution

    On remarque, en cliquant sur l'étape "Source", la ligne de commande Power Query qui intègre le tableau. Il suffit de copier la commande, puis, en une nouvelle requête vide, de copier la ligne de commande et de modifier le nom du tableau incorporé. On répètera l'opération pour le troisième tableau
    J'avais déjà essayé ton exemple avec Power Query Office 2013 et n'avais pas eu cette ligne de commande que tu évoques.
    Depuis une semaine, j'ai Office 365 Windows 10 et je retente ma chance et toujours rien (voir illustration ci-dessous)

    Y a t'il une option à cocher ?

    Nom : Power Query Capture.png
Affichages : 450
Taille : 114,2 Ko
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Ma dernière contribution : VBA - Les macros complémentaires

  6. #6
    Membre chevronné Avatar de mfoxy
    Homme Profil pro
    Automation VBA
    Inscrit en
    février 2018
    Messages
    744
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 33
    Localisation : Belgique

    Informations professionnelles :
    Activité : Automation VBA
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : février 2018
    Messages : 744
    Points : 1 925
    Points
    1 925
    Par défaut
    Bonjour Philippe,

    Je me permet de répondre.
    Dans l'écran sur lequel vous êtes, il faut aller dans les paramètres d'affichage, et cocher barre de formule.

    https://support.microsoft.com/en-ie/...e-a83578f66d25

    Bàv,

    MFoxy
    Michaël

    Si mon aide/avis vous a été profitable , n'hésitez pas à cliquer sur , ça fait toujours plaisir...
    _________________________________________________________________________________________________________________

    "Tout le monde est un génie. Mais si on juge un poisson sur sa capacité à grimper à un arbre, il passera sa vie à croire qu'il est stupide..."
    Albert Einstein

  7. #7
    Rédacteur

    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    janvier 2010
    Messages
    10 511
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : janvier 2010
    Messages : 10 511
    Points : 24 943
    Points
    24 943
    Billets dans le blog
    28
    Par défaut
    Bonjour mfoxy,
    Merci pour ta réponse
    Au moment où j'ouvre la discussion pour indiquer que j'avais enfin trouvé, je découvre ton message. Effectivement c'est comme dans l'affichage d'excel on peut afficher ou pas la barre des formules.
    Comme quoi, lorsque l'on décide de s'y mettre, il faut prendre le temps.
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Ma dernière contribution : VBA - Les macros complémentaires

  8. #8
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    16 589
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

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

    Informations forums :
    Inscription : novembre 2003
    Messages : 16 589
    Points : 47 771
    Points
    47 771
    Billets dans le blog
    88
    "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...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

Discussions similaires

  1. Fusion impossible avec Power Query
    Par tourniermanu dans le forum Excel
    Réponses: 2
    Dernier message: 12/12/2019, 12h48
  2. Réponses: 0
    Dernier message: 09/06/2019, 22h56
  3. Réponses: 0
    Dernier message: 16/05/2018, 12h20
  4. [tables systèmes] extraction de la structure d'une base
    Par laffreuxthomas dans le forum Autres SGBD
    Réponses: 6
    Dernier message: 23/03/2006, 14h24
  5. importation et fusion de tables access
    Par M@X_be dans le forum Access
    Réponses: 2
    Dernier message: 20/11/2005, 01h37

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