Transposer un tableau vertical en calendrier

onjour,

Je suis nouveau sur votre forum, aussi, il est possible que ma demande ai déjà été posée, mais lorsque j'ai vu 2016 pages de sujets, j'avoue que j'ai cracké - lol !!!...

Voici mon problème, il est simple mais je crois récurrent pour beaucoup d'entre nous.

1. A partir d'une requête simple qui produit un tableau vertical simple de 5 champs : Matricule (clé); nom; prénom, Date début activité; Complément.

ATTENTION : plusieurs stages peuvent être fait le même jour coloré en jaune ci-dessous (c'est important pour la suite...) :

2. Je veux transposer ses données brutes sous la forme d'un calendrier horizontal sur une ligne par matricule comme suit :

3. Démarche :

3.1. A l'aide de la formule =SI("date Stage" = "date du calendrier" ALORS afficher Stage SINON afficher "blanc", j'arrive à afficher le Stage dans la bonne "colonne" du calendrier MAIS SUR AUTANT DE LIGNES QU'IL Y A DE STAGES.

Avec cette formule, j'obtiens ceci, c'est un bon début. Dans ce tableau, les colonnes "Date Stage" et "Stage" sont toujours présentes :

Mais ce n'est pas ce que je veux ! je veux ramener chaque matricule sur une seule ligne en "compilant" tous les stages sur une ligne.

De plus, s'il y a plusieurs stages le même jour, il faut afficher tous les stages sur la journée (en jaune ci-dessous). Dans le tableau final tel que souhaité les colonnes "Date Stage" et "Stage" ne sont plus utiles... :

ET oui mes amis, si c'était simple, je l'aurais fait moi même - lol !

Je me débrouille avec Excel mais là c'est plus fort que moi. Je vous joins mon fichier, pouvez-vous me donner la solution à ce problème insoluble svp ?

PS : je cherche de préférence, une solution AVEC UNIQUEMENT DES FORMULES. Si ce n'est pas possible, alors je me contenterai d'un programme VBA.

Je vous remercie d'avance,

Bonjour

Une solution PowerQuery : j'ai pris dynamiquement les dates comprises entre le 1er jour de stage et le dernier jour de stage mais on peut lister le mois entier si nécessaire

Version 2017 ? Si tu es sur Mac cela ne marche pas

Salut Chris,
Je suis sur PC.

C'est parfait, le résultat correspond bien à mon besoin mais comment as-tu fait ?

Je ne vois aucunes formules dans la feuille "Nom sur Stage PowerQuery", seulement les résultats en dur ?

Hole

Au fait Chris, je suis impressionné !!!

RE

C'est une requête PowerQuery : il faut lancer l'éditeur Données, Obtenir des données, Lancer PowerQuery pour voir les requêtes

J'ai oublié il suffit d'un clic droit Actualiser sur le planning pour tenir compte des modifications de la source

Bonjour Chris,

J'ai compris qu'il s'agissait d'une requête et non d'une formule, OK pour moi. Toutefois dans l'onglet [Données], je ne trouve pas la commande Obtenir des données --> Lancer PowerQuery ?

Est-ce une question de version d'Excel ?

Il faut malheureusement me détailler un peu plus le process que je puisse l'appréhender.

RE

Tu dois avoir 2016 (et non 2017 qui n'existe pas, même sur Mac il me semble)

Afficher les requêtes, clic droit sur la requête Planning, Modifier

OK j'ai compris !

Merci beaucoup, je n'ai plus qu'à me familiariser avec les lignes SQL que tu as écrites.

Un grand merci,

Hole.

Une dernière question : tu as semble-t-il modifié le format de traitement de la date. Peux-tu me préciser pourquoi exactement ?

RE

Ce n'est pas du SQL mais le langage de PowerQuery M

Que l'on n'utilise pas vraiment sauf pour les formules car on ne se sert que des options du ruban et du clic droit et le code s'écrit tout seul...

Les dates sont comme dans la source et si on veut les formater autrement (j/m/aaaa), sachant que titres de tableau structuré sont convertis en texte, if faut compliquer la requête sinon le classement ne serait pas chronologique... Il serait plus simple de réduire l'année à 2 digit jj/mm/aa

Merci pour ces précisions.

Il est vrai que peu de personne (dont moi), connaisse ce générateur de requête d'Excel et son éditeur à partir d'un ruban évite les erreurs de syntaxes. Son potentiel a l'air très puissant pour certains traitements, je vais m'y intéresser...

0 nouveau merci et bonne soirée,

Hole.

RE

Il existe depuis 12 ans (Excel 2010) et se répand bien notamment depuis 2 ans au point que certains ont abandonné VBA...

Si tu cherches PowerQuery dans les archives du forum, tu verras nombre de solutions l'utilisant

Bonjour Chris,
Comme promis je débute doucement avec P.Q. Je ne veux pas vous accaparer, vous avez d'autres "élèves" que moi à vous occuper... Le fichier transmis n'étais qu'un échantillon, le fichier réel contient 250 000 lignes et 10 000 Stagiaires sur une fenêtre calendaire de 2 ans AVEC LES MEMES REGLES que le fichier de test.

C'est ma dernière question promis : quelle est la manip pour faire un "COPIER - COLLER" des Traitements du fichier de test dans le fichier réel ?

Sous VBA, il est facile de réutiliser le code d'un classeur vers un autre par copier coller : comment faire avec P.Q. ? J'ai essayé hier soir sans trouver l'astuce qui doit pourtant être simple ?

Au delà de cette manip, c'est la possibilité de récupérer d'autres codes dans d'autres classeurs...

Merci d'avance pour ce dernier coup de pouce, je vous souhaite une belle journée,

Hole.

Bonjour

La tableau structuré source a été nommé Stages

Si le tableau de l'autre classeur est aussi nommé Stages il suffit d'afficher le volet des requêtes, sélectionner les 2 requêtes, les copier puis les coller dans le même volet dans l'autre classeur

Il est aussi possible de modifier la requête pour qu'elle s'applique sur un classeur externe dont on renseigne le chemin et nom dans une cellule nommée

Cette cellule est soit remplie manuellement soit via quelques lignes de VBA permettant à l'utilisateur de choisir son classeur via une interface.

Bonjour Chris,

J'ai réussi à créer en tâtonnant la première requête "Source BO SST".

Malgré mes nombreux essais et mes recherches sur le net depuis plusieurs jours, je n'arrive pas à recopier votre requête "FINAL" dans mon fichier de travail ! Je dois faire une fausse manip quelque part mais je ne vois pas où - GRRRRRRRRR !!!!!!!!!

Voici votre fichier pour rappel. On trouve 2 requêtes "Stages" et "Final"

Je rappelle ci-dessous votre 1 ere ligne de code pour Source = Record.Field(Table.Min(Stages, "Date Stage"),"Date Stage") :

Cette 1ere ligne remmène la date Min du Tableau (c'est le point de départ...).

Dans mon fichier de travail qui contient 382756 lignes, il n'y a que 2 différences :

• Le nom de la feuille se nomme "Source BO SST"
• Le nom du champ se nomme "Date début activité LOC"

Lorsque je veux écrire cette 1ere étape et malgré un grand nombre d'essais, j'obtiens ce message :

Le message précise qu'il n'a pas pu convertir la valeur en format Table ???

Pouvez-vous m'expliquer où est mon erreur car je désespère - sic...

J'ai voulu essayer de créer une nouvelle requête mais il y a beaucoup d'option et je ne sais pas laquelle choisir :

RESUME : je cherche toujours à transformer mon tableau brut sous une forme de calendrie

• Les colonnes vertes sont "les clés" : au final 1 ligne par agent.
• les colonnes jaunes sont les données de stages et de dates qui doivent s'afficher sur un calendrier horizontal.

je suis navré d'être aussi mauvais et m'excuse de vous solliciter à nouveau, mais j'ai besoin d'un peu d'aide...

Ci-joint un extrait de 1500 lignes environ car le fichier est trop gros (il y plusieurs autres feuilles).

L'objectif est que je comprenne comment TRANSOSER FACILEMENT vos requêtes sur mon fichier de travail.

Merci d'avance et désolé si je suis un peu lent,

Thierry.

Bonjour

C'est sûr qu'en prenant un exemple aussi éloigné du fichier réel tu te mets des bâtons dans les roues.

PowerQuery, comme tout outil basé sur la logique base de données, se base sur les titres de colonnes pour nommer celles-ci

Ici

• Stage n'est pas Stage mais Libellé code complément 1
• Date n'est pas Date mais Date début activité LOC
  

et le tableau source n'est pas sous forme de tableau structuré nommé Stage

Ce qui nécessite de change quasi chaque étape...

Et j'imagine qu'il va falloir utiliser la Date de fin...

Bon sang, ça à l'air si simple !

Je te remercie beaucoup pour ta réactivité, je vais itérer à partir de ta nouvelle version.

Je ne suis toujours pas sûr d'avoir bien compris la manip pour copier ta requête dans mon fichier de 382000 lignes.

Encore merci.

Thierry.

RE

Salut Chris,
Ca marche !!!

BONUS : est-il normal que Power Query colorise systématiquement le tableau résultat avec la couleur verte ? Je préfèrerai qu'il n'y ai pas de mise en forme des résultats.

- Est-ce une option ???

Merci et bonne journée,

Thierry.

J'ai surtout l'impression que les mises en formes appliquées par PQ, augmente considérablement le volume final du Rapport : il fait 20M° de base et après le traitement PQ, il passe à 120M° !!!

Power Query est vraiment un bon outil, mais pas s'il multiplie par 5 le poids initial du fichier ???

Thierry.