XL 2016 Décompte bloc de jour travaillé

[corsu2a]

Bonjour @jcf6464 Bonjour tout le monde
j'ai pour mission de quantifié les jours travaillés pour plusieurs agents qui ont pour certains une polyvalence et donc qui occupent plusieurs postes.

Je m'explique :
Mon premier travail était, sur un planning de 4 semaines, décompter les agents qui ont fait :
Une semaine complète soit du Lundi au Dimanche soir
Un samedi
Un dimanche
Un jour férié.
J'ai utilisé des fonctions NB SI ENS imbriqués, nickel.

la ou je bloque un peu c'est que :

Maintenant on me demande de peaufiner un peu en précisant qui a travaille sur :
Une semaine complète soit du Lundi au Dimanche soir
Du Lundi au Jeudi soir
Du Vendredi au Dimanche soir
Un Samedi
Un Dimanche ou Jour férie
Une nuit en semaine.
voir fichier Joint

Tout en sachant que ces variables ne se cumulent pas, par exemple, les Samedi ou dimanche ne se cumulent pas avec la semaine complète, vous l'avez compris.
Etant donné que chaque variable correspond à une valeur.

Merci pour votre aide

 

[chris]

Bonjour

Qu'est qui permet de reconnaitre la nuit ?

Que fait-on de ceux qui ne travaillent 1 ou 2 jours dans la semaine ?

 

[corsu2a]

Bonjour @chris
Merci pour ta réponse rapide, concernant la nuit, c'est pas bien grave, cette variable ne concerne que 1 poste qui ne figure pas dans le tableau, c'est à dire qu'il fait 12h en présentielle et 12h en astreinte.
Dans ce tableau il ne s'agit que d'astreinte.
Pour les jours de la semaine on va considérer que 1 lundi = 15/4 soit 3.75 le Lundi.
Petit changement, la variable B inclus le Vendredi, qui vaut tout seul 3.75.
Bien à toi

 

[chris]

RE

Pas très clair : tu ajoutes un jour mais tu divises par 4...

 

[corsu2a]

Pardon /5

 

[chris]

Re

Une proposition par PowerQuery + TCD

Edit : fichier modifié (quelque simplifications)

Explications :

Des titres sont ajoutés en colonnes B et C du tableau de Feuil2, le tableau est mis sous forme de tableau structuré nommé Data

Le tableau de paramètres est reconstitué dans l'onglet Ref en changeant légèrement la forme et mis sous forme de tableau structuré nommé Paramètres

Chacun des 2 tableaux est basculé dans PowerQuery : depuis une cellule du tableau Données, à partir d'un tableau, et après la 1ère bascule on sort par Fermer et Charger dans, connexion seulement

La fonction Semaine_ISO que j'ai fournie est collée dans la liste des requêtes, soit dans le volet des requêtes d'Excel, soit dans l'interface PowerQuery en cliquant dans la liste à gauche avant de coller.

Requêtes

• Paramètres
  • Supprimer l'étape de typage automatique
  • Sélectionner les colonnes Type, Libellé, Montant, clic droit Dépivoter les autres colonnes
    Dans la barre de formules, remplacer Attribut par Jour
  • Sélectionner les colonnes Type, Libellé, Montant, clic droit Regrouper par,
    opération Toutes les lignes nommée Tabl
    opération Compter les lignes nommée NbJ
  • Ajouter une colonne, Colonne personnalisée : nom Jours, formule
    =List.Distinct(Table.Column([Tabl],"Jour"))
  • Cliquer sur la double flèche à côté du titre Jours, Extraire les valeurs, séparateur point virgule
  • Supprimer la colonne Tabl
  • Ajouter une colonne, Colonne personnalisée : nom Mtt_J, formule
    =if [NbJ]<7 then [Montant]/[NbJ] else 0
• Data
  • Supprimer l'étape de typage automatique
  • Sélectionner les colonnes Date, J clic droit Dépivoter les autres colonnes
    Dans la barre de formules, remplacer Attribut par Poste et Valeur par Personne
  • Typer la colonne Date en type Date et les autres colonnes en type Texte (clic droit, Modifier le type)
  • Ajouter une colonne, Colonne personnalisée : nom Semaine, formule
    =SemaineISO([Date])
  • Ajouter une colonne, Colonne personnalisée : nom Jour, formule
    =if [J]="M" then Text.Proper(Text.Start(Date.DayOfWeekName([Date]),2)) else[J]
  • Trier par Semaine, Date, Personne
  • Sélectionner les colonnes Personne, Semaine, Jour, clic droit Regrouper par,
    opération Nombre de lignes distinctes, nom Nombre
  • Sélectionner les colonnes Personne, Semaine, clic droit Regrouper par, opération Toutes les lignes nommée Tabl
  • Ajouter une colonne, Colonne personnalisée : nom Jours, formule
    =List.Distinct(Table.Column([Tabl],"Jour"))
  • Cliquer sur la double flèche à côté du titre Jours, Extraire les valeurs, séparateur point virgule
  • Supprimer la colonne Tabl
  • Accueil, Fusionner les requêtes, sélectionner Paramètres en seconde requête, sélectionner dans chaque requête la colonne Jours, jointure Externe gauche
  • Cliquer sur la double flèche à côté du titre Paramètres et décocher tout sauf Type, Libellé, Montant, NbJ
• SemaineRef
  • Accueil, Nouvelle source, Autres sources, Requête vide et renommer Requête1 en SemaineRef
  • Dans la barre de formule taper
    =Data
  • Filtrer la colonne Type pour supprimer null
  • Sélectionner les colonnes Personne, Semaine, Libellé, Montant, clic droit Supprimer les autres colonnes
• S&D
  • Accueil, Nouvelle source, Autres sources, Requête vide et renommer Requête1 en S&D
  • Dans la barre de formule taper
    =Data
  • Filtrer la colonne Type pour ne garder que null
  • Supprimer les colonnes Type, Libellé, Montant, NbJ
  • Sélectionner la colonne Jours, clic droit, Fractionner la colonne par délimiteur, délimiteur point virgule, Options avancées Lignes
  • Accueil, Fusionner les requêtes, sélectionner Paramètres en seconde requête, sélectionner dans chaque requête la colonne Jours, jointure Interne
  • Cliquer sur la double flèche à côté du titre Paramètres et décocher tout sauf Type, Libellé, Montant, NbJ
  • Sélectionner les colonnes Personne, Semaine, Libellé, Montant, clic droit Supprimer les autres colonnes
• SemainePart
  • Accueil, Nouvelle source, Autres sources, Requête vide et renommer Requête1 en SemainePart
  • Dans la barre de formule taper
    =Data
  • Filtrer la colonne Type pour ne garder que null
  • Supprimer les colonnes Type, Libellé, Montant, NbJ
  • Sélectionner la colonne Jours clic droit, Fractionner la colonne par délimiteur, délimiteur point virgule, Options avancées Lignes
  • Filtrer la colonne Jours pour éliminer S et D
  • Ajouter une colonne, Colonne personnalisée : nom Type, formule
    ="B"
  • Accueil, Fusionner les requêtes, sélectionner Paramètres en seconde requête, sélectionner dans chaque requête la colonne Type, jointure Interne
  • Cliquer sur la double flèche à côté du titre Paramètres et décocher tout sauf Libellé, Mtt_J
    dans la barre de formule modifier le dernier Mtt_J en Montant
  • Sélectionner les colonnes Personne, Semaine, Libellé, Montant, clic droit Supprimer les autres colonnes
• Final
  • Accueil, ajouter des requêtes comme étant nouvelles, Au moins 3 tables et sélectionner SemaineRef, SemainePart, S&D
  • Renommer la requête
• Sortir par Fermer et Charger dans, connexion seulement
• Créer un TCD utilisant comme source Final (données externes)

 

[corsu2a]

Bonjour @chris
Merci pour ces explications détaillées, ça marche parfaitement.
j'ai tenté de modifier le montant, mais le TCD ne se met pas a jour.
Merci encore

 

[chris]

Bonjour

Données, Actualiser tout : que ce soit un simple TCD ou des requêtes il est nécessaire d'actualiser

 

[corsu2a]

Bonjour

Données, Actualiser tout : que ce soit un simple TCD ou des requêtes il est nécessaire d'actualiser

Tout a fait
En fait j'ai essaye de mon PC perso et ça fonctionne, sinon avec windows defender j'ai ces 2 boites de dialogues qui s'ouvrent:

 

[chris]

RE

Toute utilisation de PowerQuery affiche ce message car une requête peut potentiellement accéder à des données externes.
Il faut valider par OK puisque tu sais que ce n'est pas le cas

C'est comme le message demandant si tu actives ou non les macros : selon la source tu acceptes ou non...

 

[corsu2a]

Tout a fait ç'est ce que je fais
j'ai aussi comparé les options Excel avec mon perso mais je ne vois rien.

 

[chris]

RE

Cela marche parfaitement sur le classeur que j'ai fourni, non ?

J'ai modifié les montants dans le tableau de l'onglet Ref et tout se met bien à jour...

Edit : j'ai refait en suivant le mode opératoire et remarqué 2 ou 3 détails oubliés et un erreur de recopie.
J'ai édité le mode opératoire et corrigé en couleur rouge

 

[corsu2a]

je vais le refaire directement sur mon PC
Merci pour tout

 

[chris]

RE
Tu n'as pas répondu à la question

Cela marche parfaitement sur le classeur que j'ai fourni, non ?

 

[corsu2a]

je pensais avoir répondu, oui tout a fait.
J'ai essaye de mon PC perso fonctionne très bien.
Par contre
Toujours sur le même PC, je dois avoir un problème avec Excel, J'ai essaye d'ouvrir le travail avec excel 2016, je n'ai pas le même problème. Lorsque j'actualise avec Excel 2016 les colonnes disparaissent.

Merci en tout cas