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

Macros et VBA Excel Discussion :

Comment récupérer certaines lignes d'un tableau structuré en VBA


Sujet :

Macros et VBA Excel

  1. #1
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut Comment récupérer certaines lignes d'un tableau structuré en VBA
    Bonjour

    Je viens de lire cette discussion qui est des plus enrichissante pour mon apprentissage.

    J'ai bien compris, en tout cas "saisie" le principe pour récupérer le numéro d'une ligne, mais est il possible de récupérer tous les numéros de lignes trouvés,
    si oui, quelle fonction utiliser. Je vais aller me renseigner par la suite avec les informations que vous me donnerez.

    Bien à vous.

  2. #2
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Salut

    Il faudrait que tu en dises un peu plus sur la situation de ton classeur: Qu'as-tu au départ, que souhaites-tu obtenir? Qu'as-tu déjà essayé?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    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
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut Ce que j'aimerai faire.
    Pierre merci,

    je tente de remplir un tableau structuré, à partir de deux autres TS, celui-ci sera avec un nombre de colonne qui est égale à la somme des colonne des deux autres.
    Le nombre de ligne sera augmenté à chaque fois qu'une correspondance est trouvée.

    Je vais faire un nouveau post, car d'après mes lectures de ce jour, je vais peut être utiliser Advancefilter pour ce faire.
    Je vais même tenter de trouver sur ce forum des informations avec advancefilter qui utiliserait deux TS t-données et t_critères dont les arguments seraient donnés par une boucle sur chaque ligne de t_critères.

    Je continue mes recherches et je publie.

  4. #4
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Perso, sauf à utiliser une version d'Excel <2010, je te conseillerais POWER QUERY et te déconseillerais les Advanced Filter qui pour moi ont mal vieilli et sont obsolètes, sauf impératif de l'intégrer en VBA dans un process déjà élaboré. Mais même dans ce cas, on peut partir sur du Power Query et forcer l'actualisation par VBA, ou à l'ouverture du fichier.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    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...
    ---------------

  5. #5
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut
    Merci de ton retour Pierre.

    J'aimerai tellement utiliser power query mais je n'ai pas un compte administrateur sur mon ordinateur, je ne peux donc pas télécharger utiliser power query.
    Je vais chercher un autre moyen en vba, et donc autre chose que Advancefilter.
    Serais tu me dire qi il existe une fonction qui recherche avec plusieurs critères, non pas une ligne mais plusieurs lignes en retour.

    Au passage, est ce que la fonction Match travaille par dichotomie? je tente de m'expliquer. Est ce que la recherche s'effectue en découpant un TS en deux, et fait une recherche dans la première partie et découpant encore une fois cette moitié de TS en deux , et ainsi de suite jusqu'à trouver la correspondance?
    Ou bien est ce que Match déroule le TS ligne par ligne?

  6. #6
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Match peut effectuer une recherche séquentielle ou dichotomique. C'est le pendant de EQUIV en Excel, je vais donc l'illustrer en Excel

    Avec son troisième paramètre ayant 0 comme valeur, la recherche est séquentielle et renvoie soit la position de la valeur cherchée, soit #N/A si non trouvée après avoir parcouru toutes les lignes. Plus le nombre de recherches est important, plus la moyenne du nombre de lignes lues pour une recherche approchera de la moitié du nombre de lignes, dans une progression arithmétique: 100 lignes, 50 lectures en moyenne; 1 million de lignes, 500000 lectures en moyenne, par recherche.

    Nom : 2020-10-29_171319.png
Affichages : 903
Taille : 21,7 Ko

    Avec son troisième argument à 1, la recherche est dichotomique et donc, la croissance du nombre de lectures est logarithmique: 1024 lignes, 10 lectures max, 1048576 lignes, 20 lectures max. Mais pour cela, il faut que le tableau soit trié (par ordre croissant pour le troisième argument à 1, par ordre décroissant pour le troisième argument à -1)

    Nom : 2020-10-29_171529.png
Affichages : 862
Taille : 21,9 Ko

    Le problème avec la recherche dichotomique d'EQUIV (ou de RECHERCHEV, le principe est le même), c'est que tu acceptes de récupérer la valeur inférieure la plus proche (troisième argument à 1) ou supérieure la plus proche (troisième argument à -1) à celle cherchée s'il ne la trouve pas. En clair, s'il ne trouve pas la valeur, il s'arrête sur la ligne qui précède celle qu'aurait occupé la valeur si elle avait été présente dans la liste.

    Nom : 2020-10-29_172028.png
Affichages : 898
Taille : 32,0 Ko



    Si tu veux une recherche dichotomique qui renvoie #N/A, tu dois utiliser un double EQUIV avec un INDEX... Ca reste intéressant mais cela crée une double recherche: 1024 lignes = 20 lectures max; 1048576 lignes = 40 lectures max (au lieu de 500000 en moyenne).

    Nom : 2020-10-29_172301.png
Affichages : 887
Taille : 104,0 Ko


    Dans la cas où tu souhaites la première position de la valeur cherchée, tu pourrais t'orienter vers l'une ou l'autre des solutions ci-dessus selon le nombre de recherche à effectuer et le nombre de lignes de ton tableau, en devant accepter que la recherche dichotomique impose un tri préalable des données.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    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...
    ---------------

  7. #7
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Il faudrait que tu détailles un peu mieux ton besoin, car je ne comprends pas bien la règle qui présiderait à aligner les lignes de deux tableaux structurés si tu as plusieurs lignes qui correspondent aux critères de récupération. Pourrais-tu montrer un extract des deux tableaux sources, éventuellement anonymisés, et le tableau attendu pour le résultat de la fusion (si possible, pas des tableaux de centaines de lignes et de dizaines de colonnes, mais quelque chose de significatif qui correspond à ta situation).
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    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...
    ---------------

  8. #8
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut Un grand merci pour cette explication.
    Je fais une présentation anonyme de ce que je souhaite réaliser et je la poste.

    Merci à toi.

  9. #9
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut
    Voici les trois TS (Ce sont des représentations car je n’ai pas le droit de partager les informations réelles).

    Nom : t_data1_t_data2_final.JPG
Affichages : 824
Taille : 78,7 Ko
    Nom : t_data3_final.JPG
Affichages : 825
Taille : 22,8 Ko

    Le but final est d’avoir toutes les données dans un seul tableau, il y a deux étapes qui peuvent être faites en même temps, ou l’une après l’autre. Vous me conseillerez sur le temps gagné ou perdu dans les deux cas.

    Pour l’explication j’ai créé un tableau nommé « tableau explicatif » qui n’est pas un TS mais qui sera traité bien entendu en TS dans la programmation. L'explication en elle même est ensuite.

    Nom : tableau_explicatif_final.JPG
Affichages : 860
Taille : 105,1 Ko

    Première étape : Aller chercher dans t_data3[REFERENCE3] la correspondance de t_data1[NOMINATION] ligne par ligne et remplacer cette cellule par la cellule de t_data3[FOURNISSEUR] de la ligne trouvée. Je pense le faire avec la fonction MATCH, étudier un peu mieux cette fonction avec les précisions sur le nombre d’opération que tu m’as transmises.

    Seconde étape : le tableau t_data2 sera toujours plus petit que t_data1, je vais donc prendre t_data2 pour référence, et en particulier la paire : t_data2[CODE1] et t_data2[CODE2] de chaque ligne.
    Je vais tenter de trouver cette paire dans les paires t_data1[CODE3] ; t_data1[CODE4].

    Trois possibilités :

    - Rien n’est trouvé. On supprime la ligne de t_data2 (ligne en bleu dans « tableau explicatif » que j'ai laissée pour l'explication)
    - Une correspondance est trouvée : on copie la ligne correspondante de t_data1 à la suite de la ligne de t_data2.
    - Plusieurs lignes sont trouvées : on copie la ligne correspondante de t_data1 à la suite de la ligne de t_data2. Puis on repousse la ligne de t_data2 à la ligne inférieur et on colle à la suite la ligne trouvée de t_data1. (cf les lignes jaune dans le « tableau explicatif »).Et ainsi de suite tant qu’il y a des correspondances.
    Pour ce faire, Et vu que je ne connais pas beaucoup de fonction, je pourrai utiliser la fonction Match avec l’option 1 ou -1 ce qui me ferait donc des opérations par dichotomie.

    Je trouve donc une première ligne que je recopie. Je répète cette action, non pas sur tout l’ensemble du TS mais de la ligne trouvée +1 à la fin du tableau. Et cela tant que le résultat est égal à la paire de référence.

    Que pensez vous de cette technique, qui va certainement vous paraitre barbare…..

    Vers quelle fonction pourriez vous me guider pour réaliser cette recherche ?

    Ensuite j’éliminerai les colonnes t_data1[CODE3] et t_data1[code4]. Pour éviter les colonnes doublons.

    Je pense que je serais gagnant de coller des lignes et ensuite d’éliminer des colonnes au lieu de faire cellule par cellule à recopier.
    Cela fait plus d'informations traitées, mais cela doit être significatif en temps. Du moins je l'espère. ;-)
    Qu’en pensez vous ?

    Une autre question que je me pose est : faut il créer un tableau t_final ou bien modifier t_data2 directement sachant que j'ai une source de t_data2 qui restera intacte car dans un autre fichier.

    En tout cas, un grand merci d'avance pour toutes vos réponses.

  10. #10
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut Pour cette agrégation, quelle fonction utiliser? Pas de ligne de code, pour l'instant un nom et j'étudierai
    Je n'ai pas Power query au travail, alors je vais travailler en vba.
    Je vais faire des représentations des tableaux à utiliser car je n'ai pas le droit de mettre les données en lignes. Les tableaux feront au minimum 50.000 lignes.

    Voici les trois TS sources :

    Nom : t_data1_t_data2_final.JPG
Affichages : 846
Taille : 78,7 Ko

    Nom : t_data3_final.JPG
Affichages : 814
Taille : 22,8 Ko

    Le but final est d’avoir toutes les données dans un seul tableau, il y a deux étapes qui peuvent être faites en même temps, ou l’une après l’autre. Vous me conseillerez sur le temps gagné ou perdu dans les deux cas.

    Pour l’explication j’ai créé un tableau nommé « tableau explicatif » qui n’est pas un TS mais qui sera traité bien entendu en TS dans la programmation. L'explication en elle même est ensuite.

    Nom : tableau_explicatif_final.JPG
Affichages : 827
Taille : 105,1 Ko

    Première étape : Aller chercher dans t_data3[REFERENCE3] la correspondance de t_data1[NOMINATION] ligne par ligne et remplacer cette cellule par la cellule de t_data3[FOURNISSEUR] de la ligne trouvée. Je pense le faire avec la fonction MATCH, étudier un peu mieux cette fonction avec les précisions sur le nombre d’opération lorsque Match est utilisé avec son aspect Dichotomique.

    Seconde étape : le tableau t_data2 sera toujours plus petit que t_data1, je vais donc prendre t_data2 pour référence, et en particulier la paire : t_data2[CODE1] et t_data2[CODE2] de chaque ligne.
    Je vais tenter de trouver cette paire dans les paires t_data1[CODE3] ; t_data1[CODE4].

    Trois possibilités :

    - Rien n’est trouvé. On supprime la ligne de t_data2 (ligne en bleu dans « tableau explicatif » que j'ai laissée pour l'explication)
    - Une correspondance est trouvée : on copie la ligne correspondante de t_data1 à la suite de la ligne de t_data2.
    - Plusieurs lignes sont trouvées : on copie la ligne correspondante de t_data1 à la suite de la ligne de t_data2. Puis on repousse la ligne de t_data2 à la ligne inférieur et on colle à la suite la ligne trouvée de t_data1. (cf les lignes jaune dans le « tableau explicatif »).Et ainsi de suite tant qu’il y a des correspondances.
    Pour ce faire, Et vu que je ne connais pas beaucoup de fonction, je pourrai utiliser la fonction Match avec l’option 1 ou -1 ce qui me ferait donc des opérations par dichotomie.

    Je trouve donc une première ligne que je recopie. Je répète cette action, non pas sur tout l’ensemble du TS mais de la ligne trouvée +1 à la fin du tableau. Et cela tant que le résultat est égal à la paire de référence.

    Que pensez vous de cette technique, qui va certainement vous paraitre barbare…..

    Vers quelle fonction pourriez vous me guider pour réaliser cette recherche ?

    Ensuite j’éliminerai les colonnes t_data1[CODE3] et t_data1[code4]. Pour éviter les colonnes doublons.

    Je pense que je serais gagnant de coller des lignes et ensuite d’éliminer des colonnes au lieu de faire cellule par cellule à recopier.
    Cela fait plus d'informations traitées, mais cela doit être significatif en temps. Du moins je l'espère. ;-)
    Qu’en pensez vous ?

    Une autre question que je me pose est : faut il créer un tableau t_final ou bien modifier t_data2 directement sachant que j'ai une source de t_data2 qui restera intacte car dans un autre fichier.

    En tout cas, un grand merci d'avance pour toutes vos réponses.

  11. #11
    Expert éminent sénior

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

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

    Informations forums :
    Inscription : Février 2010
    Messages : 8 405
    Points : 16 245
    Points
    16 245
    Par défaut
    Bonjour

    A défaut de POwerQuery, toutes les versions ont MsQuery

    Des calculs de croisement de données sur 50000 lignes c'est ingérable
    Chris
    PowerQuery existe depuis plus de 13 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    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é...

  12. #12
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut Merci de ce retour.
    Merci 78chris pour ton retour d'expérience.

    Je vais donc regarder de chez moi pour gérer cela avec Power Query.
    Aurais tu un lien d'un site qui contient des informations pertinentes afin d'apprendre le plus possible sur cet outil?

    Une autre question qui m'aidera lors de mon apprentissage : Est ce que la constitution du "tableau explicatif", dans mon exemple, est possible sans codage? ou bien est ce que je dois me pencher sur le langage M pour y parvenir?

    Encore merci à toi.

  13. #13
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Salut.

    J'ai fusionné les discussions puisque tu parles du même problème dans les deux. Il est plus rentable pour toi et moins lourd pour les personnes qui te répondent que tu n'aies qu'une discussion par sujet que tu traites.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    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...
    ---------------

  14. #14
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Voici un mode opératoire Power Query pour fusionner ces trois tableaux en un seul qui reprend toutes les colonnes de tous les tableaux. Pour coller à ton exemple, certaines références ne se retrouvent pas dans tous les tableaux, et il n'y a aucun tableau qui les a toutes. De gauche à droite, les tableaux t_Désignations, t_Matières et t_Fournisseurs.

    Chose importante: Power Query est Case Sensitive. Tu seras donc particulièrement attentif aux majuscules/minuscules...

    Nom : 2020-11-05_093155.png
Affichages : 816
Taille : 49,7 Ko

    1. Incorporer tes trois tableaux dans la solution Power Query;
    2. Renommer la colonne contenant le code du produit de la même façon dans les trois tables;
    3. Consolider tes données pour créer la liste des références uniques des trois tables ans une nouvelle requête nommée par exemple Résultat;
    4. Garder uniquement la colonne Référence de cette requête Résultat et supprimer les doublons puis trier;
    5. Fusionner cette requête avec les autres tables, une par une, et reprendre les colonnes de chaque tableau sauf la référence;
    6. Renvoyer le résultat dans Excel.


    1. Pour incorporer les tableaux, tu as plusieurs solutions selon que tu reprends tous les tableaux source de ton classeur ou seulement certains. Ici, j'ai incorporé le premier puis dupliqué la requête et modifié le nom du tableau structuré dans l'étape Source de chaque requête

    Nom : 2020-11-05_091414.png
Affichages : 801
Taille : 11,3 Ko



    2. Le renommage des colonnes s'effectue par clic droit, qui est omniprésent dans les manipulations de Power Query. Le clic droit est ton premier formateur à l'utilisation de Power Query.

    Nom : 2020-11-05_091432.png
Affichages : 810
Taille : 8,6 Ko



    3. Cette consolidation s'appelle dans Power Query "Ajouter des requêtes". Elles consiste à empiler les lignes de deux ou plusieurs tableaux en reprenant Toutes les colonnes de tous les tableaux. Ici, j'ai ajouté les requêtes dans une nouvelle requête appelée Résultat.

    Nom : 2020-11-05_091535.png
Affichages : 810
Taille : 12,5 Ko

    Nom : 2020-11-05_091547.png
Affichages : 816
Taille : 67,4 Ko



    4. Dans cette nouvelle requête, par clic droit, on supprime les autres colonnes que la colonne Référence puis on supprime les doublons. On obtient donc une liste des références uniques reprises dans tous les tableaux.

    Nom : 2020-11-05_091708.png
Affichages : 798
Taille : 64,9 Ko

    Nom : 2020-11-05_091730.png
Affichages : 790
Taille : 11,7 Ko

    Nom : 2020-11-05_092917.png
Affichages : 820
Taille : 3,8 Ko



    5. On fusionne chaque tableau source avec cette requête pour récupérer les colonnes souhaitées (c'est un peu le RECHERCHEV de Power Query). On vérifie bien la jointure puisque toutes les références ne se retrouvent pas dans tous les tableaux

    Nom : 2020-11-05_091852.png
Affichages : 797
Taille : 88,0 Ko

    Nom : 2020-11-05_091931.png
Affichages : 791
Taille : 24,6 Ko



    6. Lorsque tu as fusionné tes requêtes et récupéré les colonnes souhaitées, tu peux renvoyer ta requête Résultat dans Excel.

    Nom : 2020-11-05_092034.png
Affichages : 816
Taille : 16,0 Ko
    Fichiers attachés Fichiers attachés
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    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...
    ---------------

  15. #15
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut Premier pas dans Power Query.
    Merci doublement Pierre;

    En premier lieu d'avoir rapatrié cette conversation dans cette file, et dans un second, d'avoir pris le temps de m'expliquer.

    je vais faire mes premiers pas, mais en lisant ta publication, je pense que je n'ai pas bien exprimé mon intention car je ne crois pas que ton message réponde, ce qui veut dire que j'ai mal posé ma question.

    Je reviens très vite, après un peu de pratique avec Power Query.

    Merci

  16. #16
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut Un énorme merci.
    Pierre,

    Il est évident que le résultat de ces échanges est un nouvel adepte de Power Query.
    Je ne fais que découvrir cet outil, mais en quelques click j'ai réussi à faire mes premiers pas.

    Je continue donc cette exploration et tanne mon employeur pour avoir la possibilité de l'installer, version Excel 2013, sur mon poste de travail.

    Encore merci.

  17. #17
    Expert éminent sénior

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

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

    Informations forums :
    Inscription : Février 2010
    Messages : 8 405
    Points : 16 245
    Points
    16 245
    Par défaut
    Bonjour

    C'est un ad on Microsoft, intégré à toutes les versions Excel 2016 et plus si cela peut le convaincre...
    Chris
    PowerQuery existe depuis plus de 13 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    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é...

  18. #18
    Futur Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2020
    Messages
    15
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Bâtiment

    Informations forums :
    Inscription : Octobre 2020
    Messages : 15
    Points : 8
    Points
    8
    Par défaut Est ce possible?
    Bonjour à tous.

    J'ai fais la demande, je verrai bien.
    Si la réponse est négative. Pensez vous qu'il puisse exister une possibilité pour que je travail la requête Power Query sur Excel 2016 ou postérieur et que je ne fasse qu'une actualisation sous l'onglet Donnée dans Excel 2013?

    Si oui, pourriez vous l'indiquer la procédure à ériger dans Excel 2016?

    Un grand merci.

  19. #19
    Expert éminent sénior

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

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

    Informations forums :
    Inscription : Février 2010
    Messages : 8 405
    Points : 16 245
    Points
    16 245
    Par défaut
    RE

    Non si l'add on n'est pas installé, cela ne peut s'actualiser
    Chris
    PowerQuery existe depuis plus de 13 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    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é...

  20. #20
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Tu pourrais mettre ton classeur 2016 "fait maison" à disposition du 2013 et incorporer les données du tableau 2016 en 2013 via l'import de données (soit en direct soir en MS Query, normalement dispo en 2013)
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    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. [PowerShell] Comment récupérer certaines lignes d'un fichier pour ensuite s'en servir
    Par Zipper963 dans le forum Scripts/Batch
    Réponses: 5
    Dernier message: 13/12/2012, 16h35
  2. Récupérer certaines lignes contenu dans un tableau
    Par alexbts dans le forum Langage
    Réponses: 14
    Dernier message: 27/01/2009, 18h03
  3. Réponses: 8
    Dernier message: 21/12/2007, 17h42
  4. [HTML] comment récupérer une ligne d'un tableau avec HTML?
    Par jaafarerraji dans le forum Balisage (X)HTML et validation W3C
    Réponses: 5
    Dernier message: 01/10/2007, 01h21
  5. Réponses: 5
    Dernier message: 20/07/2007, 16h15

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