XL 2016 Réorganiser tableau de données

loulourav · 2 Juin 2022

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 :!

st007 · 2 Juin 2022

Bonjour,
gros sujet,
ta bdd peut se construire avec power query, il faut tout de même créer autant de tableau que de semaine (ici 9), selectionner les deux premières colonnes, puis dépivoter les autres, créer la connection uniquement, puis additionner les 52 requetes . Pfouuuuu. quand même
Ce n'est que mon avis d'amateur

piga25 · 2 Juin 2022

Bonjour,
Une solution facile à réaliser.
Mettre à la fin de chaque mois un nouveau tableau pour indiquer les sommes en fonction des jours, puis en fin d'année un nouveau tableau récapitulant ces sommes en fonction du jour.

loulourav · 3 Juin 2022

st007 à dit:
Bonjour,
gros sujet,
ta bdd peut se construire avec power query, il faut tout de même créer autant de tableau que de semaine (ici 9), selectionner les deux premières colonnes, puis dépivoter les autres, créer la connection uniquement, puis additionner les 52 requetes . Pfouuuuu. quand même
Ce n'est que mon avis d'amateur

bonjour et merci st007,
arff malheureusement je ne sais pas suffisemment bien manipuler power query ... je ne suis pas de ne pas passer plus de temps ...

piga25 à dit:
Bonjour,
Une solution facile à réaliser.
Mettre à la fin de chaque mois un nouveau tableau pour indiquer les sommes en fonction des jours, puis en fin d'année un nouveau tableau récapitulant ces sommes en fonction du jour.

Bonjour et merci piga25
Effectivement c'est malin et pratique, mais je pense qu'il n'est pas improbable qu'on me demande une saisonnalité, j'aimerai donc dans l'idéal conserver les dates précises et pas seulement concaténer les lundis, mardis etc ... de chaque mois.

Galère

st007 · 3 Juin 2022

interro surprise, tes n°de semaine sont toujours à la même place par rapport à "UF" en a3 ou l3 par ex
les autres sont des cellules fusionnées ou décallées

loulourav · 3 Juin 2022

oui ils ont l'air d'etre tjs au même endroit
je ne crois pas avoir de cellule fusionnée pour le moment ...

st007 · 3 Juin 2022

l52 de ton exemple ???

loulourav · 3 Juin 2022

je cherchais i52 et ne comprenais pas ...^^
oui les n°semaine c'est naze ...
par contre les jours sont bien dans des cellules non fusionnées ...

st007 · 3 Juin 2022

Alors, à essayer, donc travail bien sur une copie de ton fichier réel
en exécutant la macro, celle-ci devrait créer tous les tableaux par semaine, la requête power query devrait pouvoir être copiée via l'éditeur avancé,
à voir

chris · 3 Juin 2022

Bonjour à tous

Les lignes Nb lits, de chaises, de valides, sont-il utiles ou non ?

J'ai fait 2 PowerQuery l'un où je les récupère, l'autre pas

J'ai traité directement la totalité des tableaux semaines : cela marche mais c'est un peu tordu

Pour la solution de st007 : une fois les tableaux créés, il est inutile de faire 52 requêtes.

Il est cependant à noter qu'on a une date en 2002, des dates sans valeurs en 2020, des tableaux sans dates.

La liste des services est également fluctuante d'une semaine à l'autre

Donc des corrections à faire sur ces tableaux heddo...

loulourav · 4 Juin 2022

Bonjour chris,

Non je ne pense pas que les lignes Nb lits, de chaises, de valides etc .. soient essentielles.

Et oui malheureusement entre les erreurs de saisie, les cellules fusionnées et les cellules vides il y a de quoi les faire "manger" le clavier oO
Est ce que c'est adaptable a d'autres onglets avec d'autres années et d'autres longueur de tableau ? (avce les mêmes soucis de format de cellule et autres ...)

loulourav · 4 Juin 2022

st007 à dit:
Alors, à essayer, donc travail bien sur une copie de ton fichier réel
en exécutant la macro, celle-ci devrait créer tous les tableaux par semaine, la requête power query devrait pouvoir être copiée via l'éditeur avancé,
à voir

Bonour st007,
et merci pour ton aide.
j'ai une erreur au moment d'excuter la macro :

Erreur d'execution 1004:
Un tableau ne peut pas en chevaucher un autre

le débogage s'arrête sur cette ligne :
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(lig, colo), Cells(lig + 39, colo + 8)), , xlYes).Name = ntab

Peut être que je m'y prends mal ? j'ai simplement executer la macro sur le fichier que tu as joins sans faire quoi que ce soit avec powerquery, (que je ne maitrise que très très peu) c'est peut être là le pb ?

edit : en executant la macro en étant sur la feuille _1 je n'ai pas l'erreur en revanche les tableaux générés sont vides

chris · 4 Juin 2022

loulourav à dit:
Est ce que c'est adaptable a d'autres onglets avec d'autres années et d'autres longueur de tableau ? (avce les mêmes soucis de format de cellule et autres ...)

Les dates erronées donneront des valeurs à ces dates...

Il reste une inconnue sur les mois absents (juin, juillet, octobre, novembre et décembre) car j'ai du traiter le mot fev non reconnu comme un mois.

Le traitement utilise les colonnes A à BB jusqu'à le ligne 624 en passant par un nom Data défini pour l’onglet.
Donc si la définition est correcte, cela devrait fonctionner.

Pour le code VBA de st007, l'écartement vertical des mois étant fluctuant, il faut repérer chaque ligne commençant par UF.
Je le laisse avancer et si cela fonctionne, je posterai le PowerQuery plus simple qui traitera l'ensemble des tableaux

st007 · 4 Juin 2022

Me serais-je planté de fichier ?

VB:

Sub Mise_en_tableau()
ntab = 1
For lig = 3 To 578 Step 52 ' lignes UF 3-55-107-159-211-263-315-367-419-471-523-575
    For colo = 1 To 45 Step 11  'colonne UF  A-L-W-AH

    Range(Cells(lig, colo), Cells(lig + 39, colo + 8)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(lig, colo), Cells(lig + 39, colo + 8)), , xlYes).Name = ntab
    ntab = ntab + 1
    Next
Next

End Sub

avec une query

Code:

let
    Source = Excel.CurrentWorkbook(),
    #"Dernières lignes supprimées" = Table.RemoveLastN(Source,1),
    #"Content développé" = Table.ExpandTableColumn(#"Dernières lignes supprimées", "Content", {"UF ", "Service demandeur", "Colonne1", "Colonne2", "Colonne3", "Colonne4", "Colonne5", "Sam 01 jan 2022", "dim 02 jan 2022", "lun 03 jan 2022", "mar 04 jan 2022", "mer 05 jan 2022", "jeu 06 jan 2022", "ven 07 jan 2022", "sam 08 jan 2022", "dim 09 jan 2022"}, {"Content.UF ", "Content.Service demandeur", "Content.Colonne1", "Content.Colonne2", "Content.Colonne3", "Content.Colonne4", "Content.Colonne5", "Content.Sam 01 jan 2022", "Content.dim 02 jan 2022", "Content.lun 03 jan 2022", "Content.mar 04 jan 2022", "Content.mer 05 jan 2022", "Content.jeu 06 jan 2022", "Content.ven 07 jan 2022", "Content.sam 08 jan 2022", "Content.dim 09 jan 2022"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Content développé",{"Name"}),
    #"Supprimer le tableau croisé dynamique des autres colonnes1" = Table.UnpivotOtherColumns(#"Colonnes supprimées", {"Content.UF ", "Content.Service demandeur"}, "Attribut", "Valeur"),
    #"Colonnes renommées" = Table.RenameColumns(#"Supprimer le tableau croisé dynamique des autres colonnes1",{{"Content.UF ", "UF"}, {"Content.Service demandeur", "Service demandeur"}}),
    #"Colonne personnalisée ajoutée" = Table.AddColumn(#"Colonnes renommées", "Personnalisé", each let splitAttribut = Splitter.SplitTextByDelimiter("jan", QuoteStyle.None)([Attribut]), splitsplitAttribut1 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(splitAttribut{1}?) in Text.Combine({Text.Middle([Attribut], 12, 2), "/01", Text.Combine(splitsplitAttribut1, "/")}), type text),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Colonne personnalisée ajoutée",{"Attribut"}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes supprimées1",{"UF", "Service demandeur", "Personnalisé", "Valeur"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes permutées",{{"Personnalisé", type date}}),
    #"Colonnes renommées1" = Table.RenameColumns(#"Type modifié",{{"Personnalisé", "Dates"}})
in
    #"Colonnes renommées1"

st007 · 4 Juin 2022

Après, que tu aies des tableaux vide, c'est normal puisque je suppose une année.
Pour les step, toutes les x colonnes et x lignes, en cas d'écart, çà va m.....
à vérifier l'emplacement des cellules contenants UF comme dit chris

XL 2016 Réorganiser tableau de données

loulourav

XLDnaute Occasionnel

Pièces jointes

st007

XLDnaute Barbatruc

Pièces jointes

piga25

XLDnaute Barbatruc

Pièces jointes

loulourav

XLDnaute Occasionnel

st007

XLDnaute Barbatruc

loulourav

XLDnaute Occasionnel

st007

XLDnaute Barbatruc

loulourav

XLDnaute Occasionnel

st007

XLDnaute Barbatruc

Pièces jointes

chris

XLDnaute Barbatruc

loulourav

XLDnaute Occasionnel

loulourav

XLDnaute Occasionnel

chris

XLDnaute Barbatruc

Pièces jointes

st007

XLDnaute Barbatruc

Pièces jointes

st007

XLDnaute Barbatruc

Discussions similaires