Version imprimable
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.
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é?
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.
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.
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?
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.
Pièce jointe 582572
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)
Pièce jointe 582573
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.
Pièce jointe 582574
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).
Pièce jointe 582575
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.
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).
Je fais une présentation anonyme de ce que je souhaite réaliser et je la poste.
Merci à toi.
Voici les trois TS (Ce sont des représentations car je n’ai pas le droit de partager les informations réelles).
Pièce jointe 582649
Pièce jointe 582650
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.
Pièce jointe 582651
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.
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 :
Pièce jointe 582964
Pièce jointe 582965
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.
Pièce jointe 582966
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.
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
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.
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.
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...
Pièce jointe 583053
- Incorporer tes trois tableaux dans la solution Power Query;
- Renommer la colonne contenant le code du produit de la même façon dans les trois tables;
- 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;
- Garder uniquement la colonne Référence de cette requête Résultat et supprimer les doublons puis trier;
- Fusionner cette requête avec les autres tables, une par une, et reprendre les colonnes de chaque tableau sauf la référence;
- 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
Pièce jointe 583044
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.
Pièce jointe 583045
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.
Pièce jointe 583047
Pièce jointe 583048
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.
Pièce jointe 583049
Pièce jointe 583050
Pièce jointe 583051
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
Pièce jointe 583052
Pièce jointe 583054
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.
Pièce jointe 583055
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
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.
Bonjour
C'est un ad on Microsoft, intégré à toutes les versions Excel 2016 et plus si cela peut le convaincre...
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.
RE
Non si l'add on n'est pas installé, cela ne peut s'actualiser
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)
On pourrait essayer le truc suivant en VBA...
Toujours sur base de mes 3 tableaux t_Désignations, t_Matières et t_Fournisseurs et du tableau de résultat t_Résultat qui reprend les différentes colonnes des trois premiers tableaux, voici un code d'une dizaine de lignes
Pièce jointe 583584
L'idée est de
- coller les colonnes de référence les unes en dessous des autres;
- d'en supprimer les doublons;
- d'ajouter les INDEX/EQUIV formulés;
- optionnellement, de transformer en valeur (copier/collage spécial valeurs à la sauce VBA).
On retrouve ici les différentes étapes du Power Query...
A voir sur tes 50000 lignes pour les performances, mais je pense que ça devrait tenir la route. On peut désactiver l'affichage et le calcul en début de macro et les restaurer en fin de macro pour accélérer l'exécution de manière significative.
Pièce jointe 583586Code:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Le code VBA travaille avec des tableaux structurés, et donc tu peux bien sûr les déplacer dans ton classeur (le tableaut_Résultat" ne peut normalement pas se trouver sous les autres tableaux mais sur une feuille à part) sans que ton code doive être modifié. Tu as juste à adapter les noms des tableaux et des colonnes et, bien sûr, les formules utilisées.
Pour les formules, l'idée est de les saisir dans Excel dans le tableau de résultat pour les récupérer en VBA et les inclure dans ton code (attention à doubler les guillemets)
Pièce jointe 583589
Merci Pierre de ce message et de ces explications. J'ai été longtemps absent du forum pour raison médicale.
Je vais prendre le temps de bien comprendre celui-ci et je vais tester sur mes tableaux.
Encore merci, je te tiens informé de ma progression.
