Hésitation TCD / Power Pivot, tableau VBA

Bonjour à tous,

Je sollicite votre aide pour me mettre sur la voie pour la résolution d'un petit problème.

Vous trouverez les données dans le classeur ci-joint.

En gros, les données se trouvent dans les cellules I à M. Je cherche à obtenir les jours des activités pour chaque paire enfant/activité. Je récupère la 1ère date du mois où on trouve une de ces paires, ainsi que la dernière.

Je crée par VBA un TCD (colonnes O à S) qui me récapitule les données.

À partir de là, une macro boucle sur les valeurs et me donne les cellules en saumon (colonnes U à X).

Y Z et AA sont calculées à l'aide de formules.

Ça fonctionne ainsi, mais c'est laborieux et je sens que je peux faire bien mieux. D'autant plus qu'il me faudrait sur chaque ligne en colonne AB:AD (en rose) les dimanches qui seraient occupés par une activité pour l'enfant et le motif concerné.

Mes questions donc. D'après-vous :

Est-ce réalisable à l'aide d'un tableau en VBA ? (pour éviter le TCD et la macro qui boucle)

Est-ce réalisable à l'aide de Power Query ? (je my suis mis récemment grâce à vous et c'est génial, mais je n'ai pas vu comment je pourrais faire ça ici).

Par avance merci pour vos éclaircissements.

Bonjour

Une solution PowerQuery

J'ai nommée par formule la plage I5:M36 avec un nom commençant par T_

La requête ne prend que les plages nommées ainsi préfixées.

Pour d'autres mois il faudra suivre la même logique : la requête les traitera automatiquement

Le tableau résultant peut être filtré sur le mois...

La colonne L n'est pas utilisée donc pourrait être enlevée (mais quelques corrections à faire dans la requête)

Il peut y avoir 5 dimanches dans un mois (Mars et Mai 2020) donc 5 colonnes prévues

Super, merci beaucoup.

Je n'ai accès qu'à mon téléphone ce soir, je regarde ça demain matin.

Hate de voir power query à l'œuvre là-dessus!

Bonjour à tous,

Un immense merci 78Chris pour cette réponse qui fonctionne parfaitement.

Après avoir attentivement regardé tout cela, c'est vrai que c'est impressionnant ce qu'on peut faire avec cet outil.

Par contre, autant j'arrive à modifier ta requête crée, autant je ne vois toujours pas comment la créer à partir de 0 et donc l'appliquer à mon vrai document.

J'ai regardé quelques tutos, mais rien de super bien fait. Tu aurais un ou des lien(s) ?

Bon, j'ai bien avancé grâce à ton travail, j'y vois plus clair.

Je n'arrive cependant pas à afficher le résultat de la requête où je veux sur ma feuille active. Il va toujours la mettre dans une nouvelle feuille

Bonjour

JoyeuxNoel a écrit :

Par contre, autant j'arrive à modifier ta requête crée, autant je ne vois toujours pas comment la créer à partir de 0 et donc l'appliquer à mon vrai document.

Comme indiqué j'ai créé une plage nommée avec un nom préfixé T_suivi du mois sur 2 digit

Ensuite PowerQuery, A partir d'autres sources, Requête vide : ce qui ouvre PQ

• Renommer la requête et dans la barre de formule, taper
  = Excel.CurrentWorkbook()
• Filtrer la colonne Name : Filtre textuel, Commence par T_
• Cliquer sur la double flèche près du titre Table, décocher la case tout en bas
• Transformer, Utiliser la 1ère ligne comme en-tête et, pour simplifier l'étape suivante, renommer cette étape Etat0
• cliquer sur Fx à gauche de la barre de formule et taper :
  = Table.RenameColumns(Etat0,{{Table.ColumnNames(Etat0){5}, "Mois"}})
  ceci pour renommer la 6ème colonne (dont on ne connait pas le nom en cas de multi source) "Mois"
• ...

pour la suite je pense que tu a compris sinon demande

Quant on sort utiliser Fermer et charger dans, Table et préciser l'endroit.

Il n'aime pas insérer sous ou au-dessus d'autres données donc si c'est le cas placer à droite ou sur un autre onglet puis déplacer ensuite le tableau

Hello Chris,

Génial, je progresse à une vitesse folle grâce à tes indications !

En effet, j'étais arrivé à décrypter le fonctionnement des différentes requêtes et à modifier le code qui est dans l'éditeur avancé.

Mais je bloquais vraiment sur cette étape de fermer et charger dans. Grâce à toi, c'est de l'histoire ancienne. Je crois que je vois même à peu près comment tu as crée les champs personnalisés. Je me pencherai dessus très prochainement.

Maintenant que ça fonctionne bien, je suis face à un nouveau problème :

En effet, le fichier sur lequel tu as travaillé est généré, nommé et enregistré automatiquement par un autre fichier.

C'est donc sur le fichier maître que j'ai intégré tout ceci.

Problème : tant que le fichier maître est ouvert, pas moyen de mettre à jour la requête ? C'est quelque chose de connu et contournable ?

En tout cas, encore merci beaucoup.

J'espère que sous peu, je pourrai être ton assistant "Power Query c'est génial" en plus de "c'est mieux avec un tableau structuré"

RE

L'éditeur avancé, je n'y vais quasiment jamais.

Tout est fait par le ruban ou le clic droit et pour les cas un peu tordus, la barre de formule suffit les 9/10èmes du temps

Pas sûr d'avoir tout compris sur tes maîtres et esclaves

Peux-tu préciser un peu plus le contexte et les divers fichiers

Oui en fait l'éditeur avancé ne montre ni plus ni moins que toutes les étapes d'un coup. On n'a pas forcément intérêt à en voir plusieurs simultanément et la sélection de l'étape à droite suffit largement.

En fait, j'ai donc un fichier de base qui actuellement comporte 2 onglets (on va l'appeler tableur source) :

1 onglet avec une BDD qui se met à jour à l'ouverture en lançant une requête Power Query vers une BDD centralisée

1 onglet qui ressemble à la page que tu as vue.

On choisit dessus le mois qu'on veut, le nom qu'on veut et il charge toutes les infos de la personne, génère un nouveau fichier avec le mois voulu, les week-ends et jours fériés matérialisés, etc... C'est une partie de ce fichier généré que tu as vu. (Appelons-le tableur actuel)

C'est donc sur le tableur actuel que je vais bosser, en rentrant les activités, les noms, les durées, etc. Et c'est sur celui-là que je dois faire mes regroupements (plus sympa avec Power Query qu'en enchainant les boucles VBA).

Problème : tant que le tableur source reste ouvert, il ne veut pas actualiser la requête Power Query sur le tableur actuel. J'ai le message d'erreur que je t'ai fait voir dans le dernier message.

Je peux contourner ce problème en transformant mon tableur source en modèle s'il le faut, même si je préférerais l'éviter. J'étais juste étonné du plantage.

Ahah par contre, si on veut protéger le document juste après la mise à jour de la requête, ça plante parce qu'il protège avant d'avoir terminé la mise à jour. Faut quand même pas mettre un timer, si?

S'il n'y a pas de solution sympa à ces deux petits soucis, Power Query va perdre 2 points dans mon estime

RE

Pourquoi ne pas garder la requête dans le fichier principal, la faire tourner en lui passant en paramètre nom et mois et exporter le résultat ?

Peut-être un conflit de noms de requête et/ou source

Peut-être exporter les données, créer le nom ou renommer le tableau structuré , copier la requête mais sous un autre nom que dans le fichier maître.

Sinon je n'ai pas la réponse mais on doit la trouver via google (faut trouver la version US du message car peut de chose en français)

Pour la mise à jour : on peut désactiver la MAJ de la requête en arrière plan : voir si cela fait office de timer

Hello,

Un petit retour après 2 jours à peaufiner tout ceci.

Dans l'organisation actuelle, j'ai tourné le problème dans tous les sens et ce n'est pas possible de laisser la requête sur le fichier maître. Il va y avoir beaucoup de fichiers à créer, avec pas mal de variables différentes et des centaines de combinaisons possibles.

J'ai vu que les fichiers ont été pas mal téléchargés donc si certains sont intéressés, je peux vous confirmer quelques petits points :

• Ça fait bien exactement la même chose qu'un TCD, puis des macros qui viennent boucler sur les valeurs pour les extraire.
• C'est plus abordable techniquement si on n'est pas un pro du VBA

Je me demande par contre encore si la même chose n'est pas réalisable avec un tableau en VBA. Pour le coup, j'imagine que ça serait encore plus rapide et moins contraignant comme ça. Mais je ne connais vraiment pas assez pour pouvoir apporter des réponses à cela.

En tout cas, un immense merci à toi, 78Chris, pour l'aide apportée.

RE

Pas sûr qu'on se soit compris

JoyeuxNoel a écrit :

1 onglet qui ressemble à la page que tu as vue.

On choisit dessus le mois qu'on veut, le nom qu'on veut et il charge toutes les infos de la personne, génère un nouveau fichier avec le mois voulu, les week-ends et jours fériés matérialisés, etc...

Tu génères bien un fichier par cas

Donc avec une unique requête avec tous les noms et les mois, il suffit d'exporter un sous-ensemble du résultat obtenu

Je me sens bête, je ne comprends pas ce que tu veux dire par-là.

Mais oui, je génère un fichier par cas. Et ce fichier sera dans un dossier différent à chaque fois en fonction du nom, du mois, de l'année.

RE

La requête que j'ai fait peut ne pas être filtrée sur le nom et le mois : elle affiche alors toutes les données de la ou les sources :

on peut donc copier une partie du résultat, plutôt que la requête dans le classeur à créer et sauvegarder...

Bonjour,

Je reviens auprès de toi après des heures passées à galérer sur ces requêtes. J'essaie de tenir bon, mais je tourne vraiment en rond pour le moment.

Comme je te le disais, j'ai pu à peu près adapter ce que je voulais sur mon vrai fichier. Le souci, c'est qu'il me faudrait les jours fériés de la même manière que tu as sorti les dimanches, c'était la perfection.

J'ai donc supprimé la ligne où tu vires la colonne "férié", j'insère une colonne conditionnelle, avec la régle qui dit qu'on garde les valeurs de la colonne Férié si elles sont égales à 2, sinon on laisse vide. Bref, j'essaie de reproduire tant bien que mal ce que tu avais fait, mais je bloque au moment de fractionner la colonne. En fait, je n'arrive pas à comprendre comment tu as bien déclaré ta colonne en tant que table. Comme tu peux le voir sur l'image ci-contre, pour moi ça foire.

Je joins à nouveau le document. La requête s'exécute sur la feuille requête (c'est bien foutu, des fois !)

SI jamais tu arrivais à me faire comprendre comment il faut faire, ça serait génial. Si tu ne t'en sentais pas l'envie, pas de souci, je comprendrais. Tout ce que tu as fait pour moi est déjà génial.

RE

Ci-joint le fichier modifié. En mai on peut avoir jusqu'à 4 JF (1, 8, Ascension et parfois Pentecôte). Je pense c'est le maxi

On pourrait, supprimer la colonne L, et soit charger ta liste de JF, soit utiliser un calendrier complet dans PQ, puis croiser les 2 tables

Si cela te convient j'expliquerai les étapes qui manquaient (et d'autres si tu as des questions)

Hello, un grand merci pour tout cela !

De ce que je vois, il me manquait effectivement quelques étapes cruciales pour arriver à mes fins. Je ne pensais pas que tes 2 colonnes DimH et DimNb étaient aussi importantes.

Pour la colonne L, si tu parles de celle de la table requête (avec les mois), oui on peut l'enlever.

Si tu parles de celle de ma feuille de base, ça me sert ensuite de coefficient multiplicateur. Je pourrais m'en sortir autrement, mais elle ne gène pas vraiment pour le moment.

Pour les jours fériés, j'avais pris l'habitude de les générer sur pas mal d'année en avance, et puis je suis passé très récemment à un système perpétuel, comme tu as pu le voir. Mais j'aurais dû le passer en tableau comme tu l'as fait. Ça me permet d'adapter ma formule au mieux ensuite dans la colonne L, justement.

Pourquoi avoir remplacé la formule en Q1 qui permettait justement d'avoir cette génération perpétuelle des JF ?

Le passage en tableau structuré a enlevé la formule, j'avais zappé ça. Dommage.

Mais du coup, oui, je veux bien tes précieuses informations concernant la création de cette requête !

RE

On peut faire du perpétuel en stockant l'année (ou le jour de début) ailleurs que dans le titre et il faut tous les JF dans une seule colonne pour ne pas compliquer. J'ai des calendriers auto Excel sur 5 ans ou je ne change que la date de début...

Pour L si tu l'utilise ailleurs, laissons

Les DimH et DimNb et équivalent Fériés servent à calculer le nombre d'heures et le nombre de ces éléments

J'ai créé une plage nommée avec un nom préfixé T_suivi du mois sur 2 digit

Ensuite PowerQuery, A partir d'autres sources, Requête vide : ce qui ouvre PQ

• Renommer la requête et dans la barre de formule, taper
  = Excel.CurrentWorkbook()
• Filtrer la colonne Name : Filtre textuel, Commence par T_
• Cliquer sur la double flèche près du titre Table, décocher la case tout en bas
• Transformer, Utiliser la 1ère ligne comme en-tête et, pour simplifier l'étape suivante, renommer cette étape Etat0
• cliquer sur Fx à gauche de la barre de formule et taper :
  = Table.RenameColumns(Etat0,{{Table.ColumnNames(Etat0){5}, "Mois"}})
  ceci pour renommer la 6ème colonne (dont on ne connait pas le nom en cas de multi source) "Mois"
• sélectionner les colonnes par groupe de même type et Accueil, Type de données : choisir le type
• filtrer la colonne Nombre d'heures : <> null
• Ajouter une colonne, Colonne Personnalisée : nom Dimanche, formule :
  =if Date.DayOfWeek([Date], Day.Monday)=6 then [Date] else null
• Ajouter une colonne, Colonne Personnalisée : nom DimH, formule :
  =if Date.DayOfWeek([Date], Day.Monday)=6 then [#"Nombre d'heures"] else null
• Ajouter une colonne, Colonne Personnalisée : nom DimNb, formule :
  =if Date.DayOfWeek([Date], Day.Monday)=6 then 1 else 0
• Ajouter une colonne, Colonne Personnalisée : nom Jour_férié, formule :
  =if [Férié] = 2 then [Date] else null
• Ajouter une colonne, Colonne Personnalisée : nom FériéH, formule :
  =if [Jour_férié] <> null then [#"Nombre d'heures"] else null
• Ajouter une colonne, Colonne Personnalisée : nom FériéNb, formule :
  if [Jour_férié] <> null then 1 else 0
• sélectionner les colonnes : Mois, Enfant, Activité, Transformer, Regrouper par, et pour les opérations
  Debut, Min, Date
  Fin, Max, Date
  Total, Somme, Nombre d'Heures
  Dimanche, Somme, DimH
  Nb Dimanches, Somme, DimNb
  jour_férié, Somme, Four_férié
  Fériés, Somme, FériéH
  Nb Fériés, Somme, FériéNb
  Tabl, Toutes les lignes (permet de garder les champs qui disparaissent dans le regroupement)
  Ces opérations font un peu comme un TCD
• pour récupérer les valeurs du champ Dimanche : Ajouter une colonne, Colonne Personnalisée : nom Dimanche, formule :
  =Table.Column([Tabl],"Dimanche")
• cliquer sur la double flèche de la colonne Dimanche : extraire, séparateur ;
• sélectionner la colonne Dimanche , clic droit, Fractionner la colonne, par délimiteur ; et dans les options avancées 5 colonnes
• pour récupérer les valeurs du champ Jour_férié : Ajouter une colonne, Colonne Personnalisée : nom Férié, formule :
  Table.Column([Tabl],"Jour_férié")
• cliquer sur la double flèche de la colonne Férié: extraire, séparateur ;
• sélectionner la colonne Férié, clic droit, Fractionner la colonne, par délimiteur ; et dans les options avancées 4 colonnes
• supprimer la colonne Tabl
• sélectionner les 9 colonnes Dimanche.x et Fériés.x et Accueil, Type de données : Date
• trier par Mois et Enfant

Dommage qu'il faille un miracle pour la béatification ! (mais peut-être en as-tu déjà réalisé?)

Pour le côté perpétuel oui, la cellule peut tout à fait être ailleurs. J'ai voulu éditer, mais je n'en avais plus la possibilité. En plus j'ai vu qu'il y avait un problème, c'est ici le 31 mai qui est férié et non le 1er juin. Il faudra que je regarde cela de plus près.

Pour tout le reste, un IMMENSE merci, c'est génial ce que tu fais. Les explications sont limpides. On dirait que ça fait des années que tu le répètes en boucle sur tous les forums

Par contre, si je comprends bien, ça veut dire qu'il faut apprendre le langage spécifique ?? Tu écris tout en dur, y'a pas vraiment d'assistant ? (je suis de la jeune génération, j'aime bien être assisté des fois quand même )

RE

Je n'ai rien écrit à part les formules, tout le reste est par les onglets ou clic droite (commandes en gras, italique)

Les formules c'est un peu comme dans Excel : il faut découvrir les diverses fonctions et éventuellement les combiner

A part celles de type =Table.Column([Tabl],"Dimanche") qui extraient des données d'une table invisible, le reste est basique

Il est bien d'avoir une logique BD : PQ est un requêteur, donc basé sur cette logique plus que sur celle d'un tableur, donc si on a déjà touché aux BD Access ou SQL, ça aide