Discussion :

[Phil333250]

Bonjour à toutes et tous,

Tous les mois, les assistantes administratives de nos 22 sites saisissent des données dans un outil de suivi de marchés mis en place par une collectivité territoriale. Les données saisies permettent à la collectivité de nous payer.
Le seul moyen de faire un contrôle des données saisies est de faire une extraction de ces données.
Une personne fait donc une extraction tous les mois pour chaque lot d'un marché, (nous avons presque une centaine de lots, les lots étant divisé en sessions).

Dans le process d'extraction il n'y a aucune possibilité de faire de choix. Aussi les données qui sont extraites doivent être re traitées, a commencer par le renommage du fichier extrait. Ce qui représente une somme de travail énorme et source d'erreur.

Ces données retraitées sont ensuite comparées avec les données issues d'un outil interne de suivi des heures travaillées en lien avec le logiciel de paie.

Pour info :
Les données qui remontent reprennent l'ensemble des salariés (du mandataire comme des sous-traitants) intervenant sur le marché quelque soit le lot sur lequel ils interviennent, le tri par lot étant impossible au moment de l'extraction. Ils sont classés par ordre alphabétique. Leur nombre varie en fonction des marchés, ainsi le nombre de lignes est variable.

En revanche, seul pour les salariés du lot ne remontent les heures travaillées.

Ces heures sont indiquées en valeur pour chaque mois dans 3 colonnes : "Real" / "Regul" / "Solde".
La colonne "Solde" n'est renseignée que si il y a une régularisation. Les régularisations négatives sont notées avec le signe -.
La colonne DN "Total" indique en valeur le cumul des heures (depuis le mois de juin 2020) au jour de l'extraction. Cette colonne devrait par principe correspondre à la somme de toutes les colonnes "Solde".

Les retraitements effectués :
-1 Filtre sur la colonne "Prestataire" pour ne garder qu'un prestataire (en ce qui me concerne que Mandataire)
-2 Filtre sur la colonne "Total" pour ne garder que les personnels qui ont fait des heures sur le lot concerné
-3 Filtre sur la colonne "Type" pour ne garder que le type Formateur (H/F)
-4 Identification visuelle des doublons et coloration des lignes correspondantes
-5 Masquage des colonnes postérieures à la période de traitement
-6 Création d'une colonne afin de totaliser les heures réalisées plus les heures régularisées (ce qui donne le solde) mensuellement et en cumul pour la période considérée.

J'ai donc mis en place un fichier Excel dans lequel, par macro, je copie/colle dans une feuille prénommée, l'ensemble des données du fichier extrait. C'est à partir de cette feuille que je travaille.

Les retraitements effectués par macro :
Pour les 1, 2 & 3 => OK cela fonctionne
Pour le 4 => Insertion d'une colonne "A" pour concaténer les Nom & Prénoms, comme cela je traite le problème des doublons et j'ai ainsi un champ commun avec l'autre fichier issue de la paie.
Pour le 5 la macro masque en "dur"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Columns("AB:DM").Select
    'Columns("DP:DQ").Select
    Selection.EntireColumn.Hidden = True
    Range("A1").Select

C'est pour le 6 que j'ai besoin de votre aide. Je ne vois pas comment m'en sortir.

Les lignes 8 et 9 correspondent aux en-têtes du tableau.
Mon premier souci c'est que l'ensemble des cellules A à DN sont fusionnées soit sur 2 lignes, soit sur plusieurs colonnes. Quand je défusionne, je me retrouve avec une collection de cellules vides.
Mon second souci c'est que la majorité des titres étant composés de 2 mots séparés par un espace, je ne peux développer de TCD.
Mon troisième souci c'est que pour 1 mois donné, il y a 3 colonnes concernées "Real" / "Regul" / "Solde".

Un TCD ? mais le tableau n'est pas structuré pour, pb des champs, des cellules vides.
Un tableau structuré ? mais présence de bon nombre de cellules vides dans l'en-tête du tableau et cela ne résous pas mon pb de totalisation.
Renommer les cellules après les avoir défusionnées. Par exemple G8 H8 et I8 en 06_20_Real / 06_20_Regul / 06_20_Solde


Par exemple pour le lot 002 : Cf. image 1
Les lignes 1 à 7 correspondent à des informations générales, notamment le n° du marché et du lot. (les cellules A1 & B1 sont vides à l'origine, mais c'est moi qui renseigne automatiquement par macro la date de l'extraction.



Merci pour votre aide et conseils

Philippe

[78chris]

Bonjour

A priori si l'image représente un fichier brut (hormis la date), PowerQuery intégré à ta version Excel, est sans doute une meilleure solution.

Il faudrait un exemple de quelques lignes de

• fichier brut (anonymisé mais fidèle à l'original)
• du résultat attendu

[Phil333250]

Bonjour Chris
Merci pour l'intérêt porté à ma demande.
Je prépare un fichier avec tous les éléments demandés.

Ne connaissant pas PQ cela va être l'occasion de découvrir cet outil.

Bonne journée

Philippe

[Phil333250]

Bonjour Chris,

Voici un fichier comprenant 3 onglets :

Le premier nommé "Worksheet" qui correspond à la feuille contenue dans le fichier extrait. Donc c'est ma source.
C'est cette feuille que j'importe par macro dans mon fichier de contrôle dans une feuille nommée "Import xxx"".
Pour rappel, on ne peut au moment de l'extraction faire aucune sélection.


Le second nommé "Import 2d sem 20".
Le troisième nommé "Import 1 trim 21".

Ces 2 onglets sont des exemples après traitement par macro ou manuellement de ce que je fais.
Je vous ai mis bon nombre de commentaires dans chacun de ces onglets.

Encore merci pour l'intérêt que vous portez à ma demande.

Philippe

[78chris]

Bonjour

Le fichier extrait porte quel type de nom et quel nom d'onglet ?

PowerQuery travaille avec une logique base de données dont c'est plus simple si on part d'un cas très fidèle. ET il est plus simple de traiter un fichier externe selon les cas.

Cela me parait dément de travailler sur un fichier de 118 colonnes

On peut normaliser tout cela dans PowerQuery et exploiter par TCD

D'où tires-tu le nombre de sessions ?

J'ai modélisé mais il me reste aussi cet aspect à finaliser...

[Phil333250]

Bonsoir Chris

Le fichier extrait porte quel type de nom et quel nom d'onglet ?

Le fichier extrait est au format xlsx, l'onglet est nommé "Worksheet".
Quant au nom du fichier c'est toujours le même, c'est la raison pour laquelle le fichier est renommé systématiquement dès son extraction en EOS_export_heure_form_Lot_002_HSP_Socle.
La mention Lot_002_HSP_Socle est variable.

c'est plus simple si on part d'un cas très fidèle

Dans le fichier que j'ai joint, l'onglet nommé "Worksheet" correspond à la réalité. La seule modification que j'ai faite c'est de ne pas afficher les 0.

Cela me parait dément de travailler sur un fichier de 118 colonnes

Moi aussi, mais c'est malheureusement la source qui est construite comme cela.
Et, pour ne pas "triturer" la source et avoir une traçabilité complète, j'importe l'ensemble des données dans mon fichier de contrôle.
Mais bon, c'est comme cela que je fais.

D'où tires-tu le nombre de sessions ?

L'appellation sessions est ici ambiguë, je m'en rend compte maintenant. Il faut parler de Permanences. Tu peux la renommer ainsi.
Donc la colonne Nbre de Permanences que je rajoute correspond au nombre d'heures réalisées par une personne / 3.
1 Permanence dure 3 heures. Donc si un nbre de Permanences est un nombre décimal, il y a une erreur de saisie en amont.
Ex : 1 personne qui fait 27h aura fait 27/3 = 9 Permanences

Voilà, j'espère avoir été clair.
Je ne te cache pas que j'ai l'eau à la bouche ...

Encore merci

Philippe

[78chris]

Bonjour

Pour les 118 colonnes, ce n'était pas un reproche mai une suggestion pour travailler plus efficacement en ne travaillant pas sur le tableau mais sur sa synthèse

Une première proposition :
3 variables dans des cellules nommées :

• Dossier
• Fichier
• Durée permanence

On traite par PowerQuery et TCD le fichier laissé dans le dossier. Des segments permettent de filtrer la période

On pourrait traiter un ensemble de fichiers placés dans le dossier indiqué qui pourrait avoir dans son nom la date d'extraction.
Là pour la date j'ai pris la date du jour mais il faut voir si on la remplace par une saisie, un code VBA ou justement une partie du nom du dossier...

Quel est le nombre maxi de lignes d'une extraction ?

Données, actualiser tout quand une des variable change (automatisable par une ligne de code)

pour l'instant je n'ai pas enlevé tes onglets explicatifs

Je joins Extrait1 mais en principe c'est un de tes fichiers bruts

Edit : fichier mis à jour car erreur sur le choix d'une colonne à supprimer

[Phil333250]

Bonjour Chris,

Pour les 118 colonnes, ce n'était pas un reproche

Je ne l'ai pas du tout pris dans ce sens.
Toute remarque (et critique) qu'elles quel soient permettent de construire ses connaissances et d'avancer.

Quel est le nombre maxi de lignes d'une extraction ?

A ce jour, je n'en ai pas la moindre idée.

Le principe :
Un marché est attribué à un groupement solidaire composé d'un mandataire et de co-traitants et de sous-traitants.
Chaque marché correspondant à une commande bien précise du donneur d'ordre, il a ses propres spécificités.

Un marché couvre une zone géographique, le département par exemple. Cette zone géographique est répartie entre les différents membres du groupement.
Sur le département il y a plusieurs lieux d'exécution, par exemple 30.
A chaque lieu est affecté 1 à 3 sessions. Les sessions peuvent avoir des durées d'exécutions différentes, courir sur une année de juin 2020 à juin 2021 par exemple, courir sur 4 mois, et des dates de démarrage en 2021, en 2022, en 2023.

Pour une session, il y a 2 types d'intervention (Accompagnement & Formateur) possibles.
Un salarié (du mandataire ou/et du co-traitants ou/et du sous-traitants) peut intervenir sur les 2 donc 2 lignes
Un salarié peut intervenir sur 1 seul type en 2020 (par exemple) =>donc 1 ligne, puis intervenir ensuite uniquement sur l'autre type =>donc 1 ligne. Et comme l'extraction est toujours en cumul, cela fera 2 lignes pour le salarié.
De la même manière si un salarié n'intervient plus sur le marché, il restera tout de même dans l'extraction.

La volumétrie dépend donc des spécificités de chaque marché.

Ce que je sais, c'est qu'avec le temps (juin 2023 c'est loin) le nombre de lignes augmentera. Embauche de nouveaux salariés dans les structures, tant mandataire que sous-traitants, affectation de salariés, etc.

Donc fixer un nombre de lignes "en dur" m'est difficile.
Le marché sur lequel je travaille actuellement est un tout petit marché et nous sommes déjà à 313 lignes, sachant que bon nombre de sessions n'ont pas encore démarrées.

S'il faut fixer un nombre maximum de lignes, je dirai 5 000.

3 variables dans des cellules nommées

Je travaille déjà comme cela. Cela facilite les choses.

On traite par PowerQuery et TCD le fichier laissé dans le dossier

Donc, si je comprend bien, on ne "rapatrie" dans notre fichier de travail que les données dont on a besoin.
Le fichier source doit il toujours être ouvert ?
Comment sait on que dans un fichier il y a des traitements réalisés par PowerQuery ?

Des segments permettent de filtrer la période

Tu as placé 2 segments Année et Trimestres. Est-il possible d'en avoir 1 pour les Mois ?

Je ne comprends pas tout quant à la structure des segments en ce qui concerne le filtrage des périodes.
Pour le segment "Trimestres", comment fais tu pour que le trimestre3 (par exemple) permette de remonter les données des mois de juillet à août sachant que sur l'extraction ces données ne sont pas des champs (on a "Juillet 2020" "Août 2020" et "Septembre 2020") et que chaque mois a 3 colonnes Real, Regul Solde dans lesquelles se trouvent les valeurs.
Question de néophyte sans doute.
Est-ce fait via PowerQuery ? => je pense que oui

Sur certains de mes TCD, j'utilise une barre chronologique, qui me permet de sélectionner les périodes que je souhaite.
Pourquoi ne te sert tu pas ce cette possibilité ?

On pourrait traiter un ensemble de fichiers placés dans le dossier indiqué qui pourrait avoir dans son nom la date d'extraction.

Pour ce qui est de l'architecture des données sur les disques durs je n'ai pas la main dessus (je ne suis pas décisionnaire).
En revanche, je peux faire "imposer" l'architecture qui conviendrait le mieux pour l'utilisation de l'outil.

Si je comprends bien on pourrait avoir une architecture du type :

Dossier "Nom du Marché Extract du 20/05/21"
Et des fichiers N° du Lot Extract du 20/05/21
N° du Lot Extract du 20/05/21
N° du Lot Extract du 20/05/21

Ou
Dossier "Nom du Marché"
Sous dossier "N° du Lot"
Et des fichiers Extract du 20/05/21
Extract du 21/05/21
Extract du 22/05/21

Le traitement de l'ensemble des fichiers se ferait par macro ?

Là pour la date j'ai pris la date du jour mais il faut voir si on la remplace par une saisie, un code VBA ou justement une partie du nom du dossier...

Tu parles de la cellule A2 dans le TCD ?
La date d'extraction doit être automatique. Elle peux être calculée en I4 du TCD et copier / coller valeur en A2 avec une validation des données (personnalisé) empêchant la modification.
Quant au nommage automatiques des fichiers ou dossiers, j'utilise la concaténation au travers de plusieurs cellules que l'on peut modifier à loisir si besoin. Exemple en I9.


Comment calcules tu la Somme des Heures ? PowerQuery ?
Est-ce du type : Juin 2020 Réal + Juin 2020 Regul + Juillet 2020 Réal + Juillet 2020 + etc.

Enfin, dans le TCD il faut que remontent les n° de sessions.
Pour chaque salarié, on doit pouvoir voir sur quelles sessions ils sont intervenus ainsi que les heures correspondantes.
Par exemple pour ZORRO il serait bien d'avoir 3 lignes correspondants aux 3 sessions sur lesquelles il intervient avec les heures correspondantes.

Voilà,

j'espère avoir été clair.

Merci

Philippe

[78chris]

Bonjour

En dehors de la date, que j'ai commentée, les cellules, le tableau de l'onglet TCD en A1 : D2 est extrait du fichier par PowerQuery (1ère lignes du fichier)

La requête telle que faite peut admettre 16 000 lignes, sinon il faudrait la faire différemment

Le fichier est traité par PowerQuery sans besoin de l'ouvrir (il est même préférable qu'il ne le soit pas)

"Comment sait on que dans un fichier il y a des traitements réalisés par PowerQuery"
on ne sait si tu parles du fichier source ou du fichier de synthèse. Le fichier source n'est pas touché donc rien de l'indique
On voit la liste des requêtes dans le fichier de synthèse : Données, Requêtes et connexions ou en lançant directement PowerQuery Données, Obtenir des données, lancer PQ

En dehors de sa source qui est une requête, c'est un TCD standard : on y met les segments ou chronologies que l'on veut. Je te donne une piste de travail pas une solution clés en main.

Je te conseille de regarder les étapes des requêtes (à droite dans l'interface PowerQuery)
Tu verras notamment que la conversion des périodes comme Juillet 2020 en 01/07/2020 est une étape, très simple
Le calcul des heures nécessite 5 étapes : qui permettent de juxtaposer Real et Regul puis calculer (technique décrite ici http://www.excel-formations.fr/Trucs_astuces/PQ05.php)

On garde le détail de ces heures par mois. Seul le TCD fait une somme selon la période, de façon classique...

Le dernière étape élimine les colonnes non utiles pour l'analyse : c'est un choix fait sur le peu d'infos que j'avais. Et j'ai mal cliqué car je voulais garder Session et non Période/Type.1 (je remets le bon fichier dans mon post précédent)
On peut garder/éliminer ce que l'on veut

Pour l'arborescence : le marché et le lot étant dans le fichier, c'est plus un choix d'organisation qui vous appartient. En revanche la date d'extraction dans le nom du fichier est parfait pour récupérer celle-ci.

Pas besoin de macros pour traiter un dossier complet si tout peut être dans la source du TCD.

PowerQuery permet convertir le traitement fait sur un fichier en une fonction applicable à tous les fichiers d'un dossier sous réserve d'une structure identique.

Ce qui n'est pas très clair pour moi c'est ce qui se passe d'une extraction à l'autre : une synthèse par fichier ou bien un cumul ou bien... ???

[Phil333250]

Chris,

Merci beaucoup pour ton intervention.

Avec tout ce que tu m'a indiqué et fait je vais pouvoir me coucher moins bête ce soir et les suivants (beaucoup je pense !!!).

Je vais fouiller, regarder les liens donnés bref je vais apprendre.

Ce qui n'est pas très clair pour moi c'est ce qui se passe d'une extraction à l'autre : une synthèse par fichier ou bien un cumul ou bien... ???

La durée de vie d'une extraction dure jusqu'à l'extraction suivante. Chaque extraction cumulant depuis juin 2020. Aussi une extraction faite le 31/03/21 ne sera utilisée que pour les cumuls jusqu'à cette date. Ainsi si je fais une extraction le 15 avril, la précédente n'aura plus aucune utilité.
Et dans l'absolu on pourrait la supprimer (mais là, voir avec notre service qualité quant à la traçabilité des données, et les empêcheurs de tourner en rond que sont experts comptables et commissaires aux comptes). Mais c'est un autre débat.

Je clôture cette discussion.

En cas de besoins, j'ouvrirai des discussions spécifiques.

Encore un grand merci.

Philippe

[78chris]

RE

Pas de souci si tu as des questions sur certaines étapes, reposte...