XL 2016 Réorganiser tableau de données

[loulourav]

Bonjour à tous,

J'en appel à vos meilleurs astuces pour mon petit problème ... j'ai récupérer un fichier ou j'ai un onglet par année, à l'intérieur de chaque onglet j'ai plusieurs tableaux (1 par semaine) qui me détaille l'activité jour/jour et unité par unité.
On me demande une étude pour calculer l'activité les lundis, mardis etc..en moyenne depuis 4 ans...
vu lea tête du fichier je ne vois pas d'autre solution que de reprendre les données pour les mettre en forme de base de données (cf onglet BDD du fichier joint).
Mis à part le copié/collé long et fastidieux, y aurait-il des formule de recherche qui permettrait de gagner du temps ?
Je pense que l'on pourrait à minima récupérer facilement la liste exhautive des unités, ensuite la liste des jours sera forcément facilement connue, ne resterait "qu'à" trouver les valeurs pour l'unité à la date correspondante a retrouver dans l'onglet.
Je maitrise les rechercheV, index(equiv mais faire une recherche sur toute une feuille avec des trous en colonne et en ligne je ne vois pas comment faire ...
je ne suis même pas sûr que chaque tableau hebdomadaire soit strictement de la même taille (au sens nb d'unité) au sein d'une même année ...(par contre c'est sûr qu'ils différents d'une année pour l'autre)
le VBA pourrait aider ?

Bref je suis prêt à passer plusieurs heures de copié/collé mais si une solution un peu plus rapide est possible je suis preneur !

d'avance merci :!

 

[chris]

Bonjour à tous

Ci-joint classeur contenant :

• un code permettant de choisir le classeur à traiter
• un code transformant les zones mensuelles en tableaux si le titre de la colonne du dimanche contient une valeur
  Le fichier ainsi transformé est sauvegardé et fermé et son chemin et nom sont renseignés dans la cellule nommée Fichier
• une fonction PowerQuery de traitement d'un tableau
• une requête PowerQuery chargeant le fichier choisi, traitant ses n tableaux au moyen de la fonction et restituant le résultat dans l'onglet Résultat

A l'issue de la requête le fichier est enregistré sous le nom du fichier choisi, avec le suffixe _Traité, en xlsx après suppression du bouton de choix de fichier.

 

[loulourav]

Bonjour chris et merci beaucoup !

j'ai une erreur ici :

juste après avoir sélectionné le fichier en question.
je teste sur le fichier original avec plein d'onglet peut être est ce la le pb ?

edit : oui en ne gardant qu'un seul onglet sur le fichier je n'ai plus d'erreur !

franchement je ne sais pas quoi ni comment te remercier ! bref je suis impressioné et jaloux à la fois (de ne pas réussir à faire tout ca moi même ...^^)

edit 2 : ca fonctionne pour toutes mes années sauf 2018 ... je ne comprends pas pq ... j'ai une erreur : "un tableau ne peut pas en chevaucher un autre :

 

[chris]

Bonjour

Le fichier que tu joins a déjà ses 52 tableaux d'où le PB

Tu as repris un fichier déjà traité : comme expliqué au #16, le fichier est sauvegardé après la première partie qui ajoute les tableaux.

Mais tu devrais avec le second fichier qui traite à la suite ce fichier ou alors le plantage est plus loin.

Essaye de retrouver le fichier 2018 initial et poste le

 

[loulourav]

Bonjour Chris,

Rien à faire, même avec le fichier originel j'ai cette erreur qui apparait :

 

[chris]

RE

AI101
O3
T51

 

[loulourav]

bonjour Chris

Ok j'ai repris ces cellules et ca a fonctionné !
franchement chapeau c'est ultra propre et réponds complétement à ce que je souhaitais ! un grand merci !
juste pour info, je peux trouver ou le code powerquery utilisé pour essayer de comprendre, à tête reposée comment tu as procéder ?

encore merci !

 

[st007]

Bonjour,
place toi dans le tableau onglet résultat, puis onglet requête -->modifier

let
    Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Fichier"]}[Content][Column1]{0}), null, true),
    #"Lignes filtrées" = Table.SelectRows(Source, each Text.StartsWith([Item], "_")),
    #"Lignes filtrées1" = Table.SelectRows(#"Lignes filtrées", each [Kind] = "Table"),
    #"Lignes triées" = Table.Sort(#"Lignes filtrées1",{{"Name", Order.Ascending}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes triées", "Personnalisé", each Traitement([Data])),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Personnalisée ajoutée",{"Name", "Personnalisé"}),
    #"Personnalisé développé" = Table.ExpandTableColumn(#"Autres colonnes supprimées", "Personnalisé", {"UF ", "Service demandeur", "Valeur", "Date"}, {"UF ", "Service demandeur", "Valeur", "Date"}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Personnalisé développé",{"Name", "UF ", "Service demandeur", "Date", "Valeur"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes permutées",{{"Date", type date}, {"Valeur", Int64.Type}, {"UF ", type text}, {"Service demandeur", type text}})
in
    #"Type modifié"

 

[chris]

Bonjour à tous

Et le code de la fonction de traitement d'un tableau

let
    Source = (LaTable as table)=>
let
    Source = LaTable,
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(LaTable, {"UF ", "Service demandeur"}, "Dat", "Valeur"),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Supprimer le tableau croisé dynamique des autres colonnes", "Date", each Date.From(Text.Replace(Text.AfterDelimiter([Dat]," ")," fev ","/02/"))),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée",{"Dat"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"UF ", type text}, {"Service demandeur", type text}, {"Date", type date}, {"Valeur", Int64.Type}})
in
    #"Type modifié"
in
    Source

Mais en dehors de la fonction, il est plus facile de comprendre et plus formateur de regarder chaque étape de la requête à droite.
On code rarement directement : on se sert des options des onglets et du clic droit et le code M s'écrit en fonction de ces actions.
J'ai traité ainsi un tableau avant d'en faire une fonction en ajoutant 2 lignes au code obtenu.