Convertion d'un tableau (VBA ou formule)
RE
Oui dans étapes appliquées tu vois le principal mais quand il y a un rouage à droite de la ligne tu vois comment cela a été paramétré : on n'écrit pas grand chose, on paramètre et cela s'écrit un peu tout seul si la logique est bonne et la formule le cas échéant...
Edit : A noter que les 2 première étapes se créent toutes seules et correctement en principe.
Edit 2 : toutes les commandes sont le le ruban du haut : ensuite on paramètre dans la fenêtre qui s'ouvre.
Si tu faire en parallèle, il faut ouvrir 2 instances Excel afin de pouvoir juxtaposer 2 car quand on est dans l'interface PowerQuery on ne peut rien faire en dehors de la fenêtre...
Merci pour les infos,
Je suis en train de digérer toutes les infos car je découvre cette facon de faire, et en fait cela ressemble fortement à power pivot.
Par contre je reviens donc sur ton idée de faire une colonne unique avec les dates : comment je pourrais présenter cette colonne au niveau de la syntaxe?
Merci pour ton retour,
Cordialement,
RE
Non c'est très différent de PowerPivot...
Il faudrait au lieu d'avoir 1, 1, 2, 2, d'un côté et 12,26,9,23, de l'autre avoir 12/1 ou mieux 26/01 (ou autre séparateur) donc 12/01, 26/01, 9/02, 23/02 ce qui permettrait d'éclater une seule fois dans une seule requête avant de croiser avec ENFANTS
Re 78chris,
Ok je vais essayer de chercher la formule qui me permettra d’obtenir le résultat que tu souhaiterais : 26/01,12/02 ... et je reposte le fichier
Par curiosité comment fait on dupliquer une table sur power query?,
Merci d'avance,
Cordialement,
Re
A droite de l'interface PQ tu as la liste des requêtes (il faut éventuellement déplier) : clic droit...
Edit : Pour tester tu peux partir de tes 2 colonnes jours et MOIS, après tu pourras revoir a construction de cette cotonne Excel mais cela ne changera plus rien à ce qui est fait dans PQ
Ok merci, j’essaye de voir pour la formule qui donnera les dates sur une seule colonne,
Ne tkt pas si je reviens pas, je ne pourrais pas me connecté ce week end,
Merci d'avance pour l'aide,
Cordialement,
Re
OK bon week end déconnecté
Bonjour 78Chris,
J'ai enfin trouvé la formule qui me donne en une seule colonne les dates (colonne H du fichier joint),
Pourriez vous me refaire un power query sur cette base?,
Merci d'avance,
Cordialement,
Bonjour
Il manque le mois pour la première occurrence du 22/02.
Pour le tableau final : on ventile toujours en colonnes mois et jour ou une colonne date suffit ?
Bonjour,
Je ne comprends pas car je ne vois pas ou il manque quelque chose dans les dates ?, pourriez vous me dire où?
Oui pour le tableau final, effectivement il me faudrait une séparation des jours et des mois,
Merci beaucoup
RE
1ère ligne 1/02;8/02;15/02;22;22/02;
Je rejoins le fichier, mais la je ne vois pas car je lis : 1/02;8/02;15/02;22/02;22/02; en cellule H1?,
Tu es sur? lol
Re
Je n'avais changé que la formule pas le libellé !
Belle formule au demeurant même si je ne renie pas mes remarques
Ci-joint le classeur avec le PowerQuery allégé
Re, alors je vais regarder et surtout essayer de reproduire ton tableau via les requêtes, je pense maitriser plus facilement cette méthode que je pourrais éventuellement reproduire dans d'autres circonstances que la macro présenté auparavant qui est uniquement valable pour le sujet présenté (même si le résultat est le même) !
Dans la même logique, c'est sur que la formule est bien complexe lol mais au moins je peux la maitriser ce qui n'est pas le cas d'un VBA et au vu du peu de lignes qui se présenteront à chaque utilisation, mon fichier ne sera pas bien lourd !
Merci beaucoup pour m'avoir aiguillé vers l'utilisation de Query !
Je clôture ce post dés que j'aurais compris les étapes des requêtes !
Merci 78chris
Bonjour massari,
Dans ton message du 23 avril à 11:24, tu as écrit :
« Je clôture ce post dès que j'aurais compris les étapes des requêtes ! »
Dans ton message du 20 avril à 08:59, tu as écrit :
« Effectivement le résultat donné par la macro est nickel, à un détail près : peux-tu me l'expliquer ? »
Merci pour ton retour, et oui, je peux bien sûr te l'expliquer, à un détail près : je pensais le faire ce weekend, mais j'ai été débordé et j'en n'ai pas eu le temps ; en plus, j'ai un autre dossier urgent à traiter avant de repasser à ton dossier ; je préférerai que tu ne clôtures pas encore ton post ; même s'il est clôturé, je mettrai plus tard une explication détaillée du code VBA (pour toi et / ou d'autres lecteurs intéressés par ce sujet) ; donc non, je ne t'ai pas oublié(e), et tu pourras revenir sur ce sujet pour y lire l'explication demandée.
dhany
Bonjour Dhany,
Merci beaucoup, oui l'explication VBA sera intéressante, merci et pas de soucis je comprends que vous soyez occupé
Cordialement,
Re
Re, alors je vais regarder et surtout essayer de reproduire ton tableau via les requêtes...
Merci beaucoup pour m'avoir aiguillé vers l'utilisation de Query !
Je clôture ce post dés que j'aurais compris les étapes des requêtes !
Si tu as besoin d'explication sur une étape, demande.
La plupart sont toutes simples et se font juste en cliquant sur les options de PQ.
Celles un peu complexe sont les formules
- Text.Split qui permet d'éclater une chaîne selon un séparateur puis ensuite bien opter pour "développer" afin de démultiplier les lignes.
- List.Count(Text.Split([Dates],";"))-1qui permet de compter le nombre de dates en comptant les séparateurs moins le dernier
Il y a sans doute d'autres possibilités équivalentes
Pour les fonctions de chaîne
- Text.Start est l'équivalent de Gauche
- Text.PositionOf l'équivalent de Trouve mais sans nécessiter le -1
- Text.BetweenDelimiters très souple qui permet de récupérer une portion de chaîne, comme STXT, mais de façon très simple entre deux délimiteurs différents
On trouve facilement la liste fournie par MS par type de fonctions en utilisant Google...
Bonjour massari,
Comme je l'avais mentionné dans mon message du 23 avril à 14:22, voici donc l'explication de mon code VBA :
1) La fonction privée Élément() est pour « décomposer » une chaîne de caractères telle que "1,8,15,22,22," en plusieurs éléments séparés individuels, et retourner le ième élément ; cela grâce au séparateur virgule ; cette fonction est privée car elle est appelée uniquement par la sub Essai() : elle n'a pas à être appelée par l'utilisateur depuis Excel.
Private Function Élément(chn$, i As Byte) As String
Dim Tbl: Tbl = Split(chn, ","): Élément = Tbl(i)
End FunctionPour mon exemple ci-dessus, j'envoie en 1er paramètre la chaîne de caractères qui est en P2 : "1,8,15,22,22,"
C'est l'instruction Tbl = Split(chn, ",") qui fait la décomposition selon le séparateur virgule, le 1er élément étant placé dans Tbl(0) ; donc si j'envoie en 2ème paramètre 0 : i vaut 0 et ça retourne Tbl(0) : 1 ; si j'envoie 1, ça retourne Tbl(1) : 8 ; puis avec 2, j'ai Tbl(2) : 15 ; etc...
⚠ Normalement, la chaîne de caractères en P2 ne devrait pas se terminer par une virgule, car il n'y a pas d'autre élément après ! à cause de cela, Split() va être induit en erreur et retourner une chaîne vide supplémentaire, mais lui n'est pas en faute : il a fait son job correctement ; c'était l'utilisateur qui aurait dû faire une saisie plus rigoureuse : toi ou celui qui t'a envoyé le fichier
⚠ Si le travail de cette fonction n'avait dû être fait que pour la colonne P, j'aurais mis ce job dans la sub Essai() sans ajouter aussi la fonction séparée Élément() ; c'est parce qu'on devra faire ce même job pour les colonnes L, N, O, P qui a justifié l'intérêt d'ajouter une fonction séparée (vive la programmation structurée et modulaire !
2) Venons-en maint'nant à la sub Essai() : c'est elle que tu peux lancer via le raccourci clavier Ctrl e (ou via Alt F8)
If ActiveSheet.Name <> "Table conversion" Then Exit Sub : c'est grâce à cette instruction que la sub Essai() ne se lance que depuis la 1ère feuille "Table conversion" : son exécution à partir de la 2ème feuille "TABLE ENFANTS" est absolument sans objet et n'a aucun intérêt ! la macro Essai() ne se sert pas de la 2ème feuille, et tout se passe uniquement sur la 1ère feuille.
Dim Tbl, celX As Range, chn$, dlig&, lig&, lg2&, i As Byte : déclaration des variables nécessaires ; comme dans la fonction Élément(), Tbl est déclaré sans type défini, donc il est implicitement du type Variant (c'est c'qu'il faut pour une variable tableau destinée à recevoir des éléments séparés par Split) ; toutes les autres variables sont typées explicitement : Range ; Byte (plage de nombres entiers non signés : de 0 à 255) ; "$" ➯ chaîne de caractères ; "&" ➯ Long
dlig = Cells(Rows.Count, 10).End(xlUp).Row : dernière ligne utilisée, selon la colonne n° 10 (J) intitulée "ENFANTS" ➯ 3
If dlig = 1 Then Exit Sub : ah ben oui : si ton tableau de départ contient aucune donnée, dlig vaut 1 et y'a aucun job à faire : sortie de sub, et grève technique !
Application.ScreenUpdating = 0: Columns("Q:W").ClearContents : empêche la mise à jour de l'écran ➯ exécution plus rapide ; puis effacement du contenu des colonnes Q à W (emplacement du tableau de résultat final ; ainsi, pas d'interférences plutôt gênantes entre d'anciens résultats et les nouveaux)
[Q1] = "ENFANTS": [R1] = "Date facture": [S1] = "Pièce": [T1] = "Libellé pièce" : 1ère partie de la ligne d'entêtes du tableau final, de Q1 à T1 ; j'ai utilisé la notation abrégée : la 1ère instruction est strictement équivalente à : Range("Q1") = "ENFANTS"
[U1] = "CPTA": [V1] = "MOIS CONSULT.": [W1] = "JR CONSULT.": lg2 = 2 : 2ème partie de la ligne d'entêtes du tableau final, de U1 à W1 ; lg2 est initialisé à 2 car les résultats seront écrits à partir de la ligne 2.
For lig = 2 To dlig : rappelle-toi que dlig a pris la valeur 3, car ton tableau gauche de départ contient seulement 2 lignes de données (c'est peu, mais suffisant pour que VBA ne fasse pas grève
Set celX = Cells(lig, 10): Tbl = Split(celX, ",") : on affecte à celX la référence de la ligne lig, colonne 10 (J) ; c'est donc les ENFANTS de la ligne "en cours" ; utilisation de Split() pour les séparer grâce à la virgule ; on va écrire autant de lignes qu'il y a d'enfants.
For i = 0 To UBound(Tbl) : rappelle-toi que le 1er indice du Tableau est 0 ; l'indice le plus haut est retourné par UBound() ; on va donc traiter tous les éléments du tableau Tbl(), un par un.
chn = Tbl(i) : un élément de Tbl() est mis dans la variable chn.
If chn <> "" Then : voilà ce qui permet d'éviter de traiter un élément vide ; je te rappelle que ce test aurait été inutile si y'avait pas de virgule finale sans rien à droite dans tes données ! (c'est pas sympa d'me refiler du boulot en plus !
With Cells(lg2, 17) : on prend pour base la cellule de la ligne lg2, colonne 17 (Q) ; les décalages d'Offset() qui suivront se feront à partir de cette cellule.
.Value = chn: .Offset(, 1) = celX.Offset(, -9) : en colonne Q, on écrit le 1er enfant ; 1 colonne à droite, donc en colonne R, on écrit ce qu'il y a 9 colonnes à gauche de celX ; si tu regardes plus haut, celX pointait sur la colonne J, donc -9 mène à la colonne A : "Date CDE" ; c'est la colonne "Date facture" du tableau final.
.Offset(, 2) = Élément(celX.Offset(, 2), i) : 2 colonnes à droite, donc en colonne S, on écrit le ième élément de c'qu'il y'a 2 colonnes à droite de celX : J + 2 colonnes mène à la colonne L "Pièce" : ok, c'est bon ... même si c'est une autre preuve que tous les chemins mènent pas à Rome (tu rendras visite à Jules César une aut' fois !
.Offset(, 3) = celX.Offset(, -4) : 3 colonnes à droite, donc en colonne T, on écrit ce qu'il y a 4 colonnes à gauche de celX ; J - 4 colonnes = ? oui, je sais : c'est plutôt légèrement fastidieux, mais c'est les maths du VBA ; si tu préfères être fermière et aller traire les vaches, j'ai rien contre !
.Offset(, 4) = Élément(celX.Offset(, 4), i) : 4 colonnes à droite : col U ; re-4 colonnes à droite, mais de celX : J + 4 = N (c'est l'évidence même, mon cher Watson ! épatant, hein, l'algèbre moderne !) : "CPTA" (même intitulé en tableau final).
.Offset(, 5) = Élément(celX.Offset(, 5), i) : bon, pour cette ligne et la suivante, j'crois qu't'as compris l'topo !
lg2 = lg2 + 1 : on incrémente de 1 lg2 pour écrire la prochaine ligne de résultat une ligne en dessous.
J'crois qu'tu sauras t'débrouiller avec le reste : c'est End With, Endive End If, Next i, et Next lig
Voici enfin le plus intéressant : End Sub : ça signifie qu'tu peux aller prendre un repos bien mérité (et moi aussi).
Alors, ça va ? mon explication t'a plu ? j't'ai pas trop dégoûtée du VBA ? j'espère que non, hein ? bon, j'te laisse, pa'c'que
moi j'vais aller pêcher ! les poissons d'l'étang d'à-côté sont délicieux cuisinés aux p'tits oignons.
dhany
Bonjour Dahny,
Oui ton explication est très détaillé et très explicite, je t'en remercie beaucoup car çela me permet de progresser dans la compréhension et qui sait un jour dans l'écriture d'un code VBA,
J’espère que la pêche fut bonne
Cordialement,