[VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimensions

[Kotov]

Bonjour,

Ancien de XLD, je suis aujourd'hui un peu rouillé en VBA et je sens que je "patine" dans la conception d'un outil de pilotage.
J'en ai réalisé pas mal par le passé mais là, ça fait 4 jours que je fais, défais et refais sans être convaincu. Et je devine que les cadors que j'ai côtoyé ici il y a quelques années pourront me remettre aisément sur la voie.

Le principe :
- je dispose d'un planning XL (onglet "Data") et de plusieurs tables de paramètres (onglet "PARAM")
- ce tableau XL est chargé dans un tableau virtuel en VBA
- j'ai 5 données de base : {agent}, {équipe}, {service}, {tranche horaire}, {activité ou absence ou vide ou "xx"}

Mon besoin :
Optimiser l'extraction des données utiles du planning :
- agent/activité/tranches horaires
- équipe/activité/tranches horaires
- service/activité/tranches horaires

- agent/absences/tranches horaires
- équipe/absences/tranches horaires
- service/absences/tranches horaires

La finalité :
- à partir de ces tableau je réaliserai un outil de pilotage à partir de Userform (alimentation de Combo, List, Multipages ...)
mais ça pas de soucis, je dois pouvoir me débrouiller seul. J'ai encore les codes réalisés il y a 4 ou 5 ans.

A titre d'exemple, je joins un tableau XL expurgé de données confidentielles.
Ce tableau de l'onglet "Data" reste la base de départ (en réalité plus de 600 agents)

Dans un module VBA, vous trouverez le début de mon code. J'ai matérialisé l'endroit où je sens que je perds pied.
Mais il y a probablement une autre approche que celle retenue et une remise sur le bon chemin m'aiderait grandement.


Merci d'avance pour vos conseils qui me permettront de poursuivre sans faire ce surplace agaçant.

Kotov

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Bonjour Kotov
Bonjour le Fil, Le Forum

Bon retour donc Lol
afin d'éclairer les Exceliens
Ton fichier donne une idée de la structure , mais aucune sur le traitement des Données.
j'ai constaté que dans la feuille "Param" les Services sont du Type S1,S2 et dans la feuille "Data" , "TM1,TM2" Pourquoi ?
a quoi sert la plage de données des Colonnes 15 à 21 de la feuille "Param" ?
Combien d’équipes composent tes différents Services ?
Pourquoi une colonne SERVICES et une Colonne EQUIPES
Et une concaténation des deux dans la Colonne EQUIPES (Colonne Services Vide)
Pourquoi pas

SERVICES et EQUIPES
S1------------- 1

Par exemple cela supprime Une des Nombreuses boucles pour remettre en forme les données
J’ai aussi pensé à remplir les différents tableaux sans boucle

Un extrait du code Modifié

'Implémentation des objets
Set sHData = ThisWorkbook.Sheets("Data")
Set sHParam = ThisWorkbook.Sheets("PARAM")
'------------------------------------------------
 
'sHData.Select
With sHData
Hdat = CDate(.Cells(1, 3)) 'La date du planning
 
'-- alimentation des variables
DerCol_Data = .Cells(2, .Columns.Count).End(xlToLeft).Column
DerLgn_Data = .Cells(.Rows.Count, 2).End(xlUp).Row 'dernière ligne Agent active
'alimentation du tableau
  T_A = .Range(.Cells(2, 2), .Cells(DerLgn_Data, DerCol_Data)).Value
 
nAG = UBound(T_A, 1) - 1 'nombre d'agents dans la base
 
End With
With sHParam
DerLgn_Activites = .Cells(.Rows.Count, 2).End(xlUp).Row  'dernière ligne Activité active
 DerLgn_Absences = .Cells(.Rows.Count, 8).End(xlUp).Row  'dernière ligne Absences active
 DerLgn_Services = .Cells(.Rows.Count, 12).End(xlUp).Row 'dernière ligne Service active
 DerLgn_Cle_Prod = .Cells(.Rows.Count, 15).End(xlUp).Row 'dernière ligne active Clé de productivité
   DerLgn_Equipes = .Cells(.Rows.Count, 23).End(xlUp).Row 'dernière ligne Equipe active
 
'°°°°° Dimensionnement des tableaux de référence puis Alimentation des tableaux à partir de l'onglet "PARAM
T_Act = .Range(.Cells(3, 2), .Cells(DerLgn_Activites, 6)).Value 'Le tableau des paramètres Activité ("PARAM")
 T_Ab = .Range(.Cells(3, 8), .Cells(DerLgn_Absences, 10)).Value 'Le tableau des paramètres Absences("PARAM")
 
 T_Sc = .Range(.Cells(3, 12), .Cells(DerLgn_Services, 13)).Value 'Le tableau des paramètres Services("PARAM")
 T_Eq = .Range(.Cells(3, 23), .Cells(DerLgn_Equipes, 30)).Value 'Le tableau des paramètres Equipe("PARAM")

Dans l'attente
Bonne Journée
Amicalement
Jean Marie

 

[Kotov]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Merci Jean Marie pour cette aide bien appréciée,
Et toutes mes excuses pour les imprécisions qui t'ont fait poser les questions suivantes :

.....
Ton fichier donne une idée de la structure , mais aucune sur le traitement des Données.

Pour éviter de faire un post trop chargé, je vais expliquer le traitement attendu dans le fichier XL joint (onglet "Traitement attendu").
En gros, à partir de ce planning, je souhaite obtenir une vision de la planif par granularité : Chef d'équipe, Responsable, Direction

.....
j'ai constaté que dans la feuille "Param" les Services sont du Type S1,S2 et dans la feuille "Data" , "TM1,TM2" Pourquoi ?

Il s'agit d'une erreur quand j'ai expurgé les données initiales et je m'en excuse.
Les données sont strictement identiques C'est corrigé dans le fichier joint.

.....
a quoi sert la plage de données des Colonnes 15 à 21 de la feuille "Param" ?

Il s'agit d'un tableau dans lequel je recense la clé de productivité avérée des différents services et la productivité attendue (objectif)
Dans la colonne 23 à 30, c'est la même chose, mais pour les équipes.
Pour ne pas complexifier ma demande j'ai laissé de côté ces éléments, mais bien évidement, je vais les utiliser pour octroyer un plan de marche à chaque entité : à partir de la planif, je connais la "consommation prévue" de ressources, et, en la multipliant par les clés de productivité attendue, j'obtiens un tableau de marche.

.....
Combien d’équipes composent tes différents Services ?

Pour l'instant je supervise 5 services composés, selon le cas, de 3 à 4 équipes soit environ 320 personnes. Mais ce périmètre doit s'étendre en 2 fois au cours de l'année 2016 pour arriver à 7 ou 8 services de 5 équipes (500 à 600 personnes).
C'est pour cette raison que mes tableaux de paramètres sont juxtaposés sur la feuille et non superposés (avec DernLign, je garde de la souplesse)

.....
Pourquoi une colonne SERVICES et une Colonne EQUIPES

.....
Et une concaténation des deux dans la Colonne EQUIPES (Colonne Services Vide)

Plusieurs raisons :
1. du fait que ma source, issue d'un logiciel est construite ainsi, même si la colonne "Service" n'est pas complétée
2. j'ai rajouté une boucle dans la macro qui complète la colonne "Service" du tableau virtuel (T_A) (les 3 1ers caractères du nom de service)

.....
J’ai aussi pensé à remplir les différents tableaux sans boucle

Merci, effectivement c'est plus simple et plus rapide.
En furetant sur le forum en début d'aprèm, j'ai justement trouvé ce conseil de Dranreb (cf. post "recherche via un tableau interne")

Merci beaucoup ... et ça fait plaisir de repasser ici autrement que pour constater la perte de membres éminents du forum.

A+
Kotov

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Bonsoir Kotov
Bonsoir le fil
Bonsoir le Forum

Une première approche , qui ne réponds pas encore à la demande (qui devra m'être rappeler Lol)
mais qui montre dans un premier temps ce que l'on peut faire .
Aucun tri Par -: Service, Équipe , Agent n'est encore effectif , mais je pourrais y travailler .
Bonne découverte .

Le Fichier : Regarde la pièce jointe Planning Chti160 3.xlsm

Bonne fin de Journée
Amicalement
Jean Marie

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Bonjour Kotov
Bonjour le Fil
Bonjour le Forum

je constate que dans la feuille "PARAM" la liste des Activités est une suite de 4 activités "T1,T2,T3,T4" répétée .
peux tu m'expliquer pourquoi ?
je n'ai pas vu de "XX" dans cette Liste pourquoi ?
Pourrais tu mettre en Feuille "PARAM" plus d'éléments qui , je me doute pas doivent avoir leur importance ,cela pour éviter de devoir modifier le Code , lors de l'ajout d'éléments dans ces Listes .
Merci par avance
Amicalement
Jean Marie

 

[Kotov]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Bonjour Jean Marie,
Bonjour le forum,

Waouh, tu es allé plus loin que je ne l'imaginais !
Tu as déjà bossé sur la restitution des données alors que j'en suis qu'à l’extraction des données et leur agrégation dans des tableaux virtuels.
Si je sais que je compte utiliser un Usf comme interface de sélection (choix de filtrage, et choix d'édition), je n'ai pas encore vraiment envisagé la configuration finale. J'attends d'avoir toutes les données utiles.
Mais l'idée du Usf avec des Listview me semble excellente et pertinente. Je comptais utiliser des list et des combobox.

Une question toutefois sur ces Listview : elles ne figurent pas d'office dans la boite à outil Excel 2007 (je suis descendu de XL 2010 à XL 2007 vu que la plupart des utilisateurs seront probablement en version 2007). Je suppose qu'on peut les ajouter mais en fouinant sur le net, j'ai relevé que des messages d'erreurs apparaissaient parfois du fait de ce contrôle selon les versions utilisées. C'est toujours d'actualité ?

Quand au code que tu fournis, c'est une vrai mine d'or ... mais je n'ai pas encore tout étudié : le VBA c'est comme une langue étrangère qu'on n'a plus utilisé depuis un moment : ça revient petit à petit, mais j'ai besoin encore d'un peu de temps pour tout décortiquer.

Pendant que tu travaillais dur sur ma demande (je n'en attendais pas autant - merci !), j'ai poursuivi ma quête de données à partir du fichier "Data" initial.
J'en suis arrivé à un tableau virtuel dont je te fournis une extraction.
A partir de ce tableau, je sais faire un filtrage avec une boucle et des "Select Case" mais je me doute que ce n'est pas optimal et qu'on peut envisager d'autres méthodes plus rationnelles et efficaces :
- via la méthode "Find"
- via l'objet "Dictionary" : là je découvre. Quand je pratiquais assidument le VBA dans les années 2005-2010, je n'en avais pas entendu parler. J'ai vu quelques exemples et je devine le potentiel de cette méthode. Est-elle la mieux adaptée à mon tableau, je n'en sais rien ?
- via d'autres méthodes qui ne me viennent pas à l'esprit

Là, inutile de consacrer trop de temps à construire mon projet, je ne déciderai du mode de restitution qu'une fois toute la phase d'extraction réalisée.
J'aimerais juste avoir un avis pertinent sur la meilleure façon de filtrer mon tableau de synthèse (et éventuellement d'un exemple pour "Find" et "Dictionary" si ces méthodes sont adaptées)

Concernant tes demandes :
"XX" : ce n'est ni une Tâche, ni une Absence. Il s'agit de la représentation d'un temps non travaillé quand la personne est présente ce jour là, mais qu'elle n'est pas encore arrivée, qu'elle est en "pause déjeuner", ou qu'elle à terminé son service :
En effet la plage horaire du fichier d'origine "Data" démarre à 06:00 et termine à 19:45 soit 13,8 heures (et je suis tenu malgré tout de faire respecter le Code du travail )
Quand une personne est absente l'intégralité de la journée (congés par exemple), l'Absence correspondante est annoté de 06:00 à 19:45
Quand elle est présente et travaille dans la plage horaire 08:00 16:30, les cellules 06:00-07:45 sont marquées par "XX", idem pendant la pause déjeuner et le soir après sa fin de service.

Les cases vides sont des anomalies puisque :
toutes les cellules de 06:00 à 19:45 doivent être complétées par : une Tâche (T1 à T23), une Absence (A1 à A19), ou "XX"
Si la case est vide, c'est que la planif est incomplète ou qu'une donnée a été supprimée
Le total agent par ligne doit être de 55 quarts d'heure ou 13,8 heures (soit l'équivalent de cette plage 06:00 19:45)
(j'ai volontairement mis quelques cellules vides dans l'exemple pour vérifier que leur décompte fonctionne bien)

J'ai fini la phase d'extraction, j'en suis à la phase de filtrage :
Je te joins un fichier tout simple avec les données telles que je les ai agrégées.
Ne passes pas trop de temps à vouloir finaliser mon projet, j'ai juste une question sur la meilleure façon de filtrer mes données
Tout est expliqué dans le fichier .

Plus tard, quand j'aurais tous les tableaux alimentés par les différents filtres, je verrai comment restituer au mieux et te demanderai si besoin.

Tu vas bien plus vite que moi !

En tout cas un énorme merci pour le travail effectué. Je vais consacrer un moment à détailler ton code.

A+
Kotov

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Re Kotov
Le fil ,Le Forum

Une autre approche (Je vais regarder ton fichier juste après Lol) , le tri pour l'instant n'est pas optimisé (mise en forme etc) .

As tu su installer les ListView ?

Le Fichier : Regarde la pièce jointe Planning Chti160 5.xlsm

Bonne fin de Journée
Amicalement
Jean Marie

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Re
J'ai visionné ton Fichier Lol
Une question :

Tu pars d'une feuille d'extraction Vierge ou les différentes Tâches et Absences sont présentes en Ligne 2 ?

Bonne fin de journée
Merci Par avance
Amicalement
Jean Marie

 

[Kotov]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Re
J'ai visionné ton Fichier Lol
Une question :

Tu pars d'une feuille d'extraction Vierge ou les différentes Tâches et Absences sont présentes en Ligne 2 ?

Bonne fin de journée
Merci Par avance
Amicalement
Jean Marie

Re Jean Marie,

Ce fichier joint représente strictement le contenu d'un tableau virtuel en VBA : les tâches et absences figurent bien sur la ligne (ligne 0).
Je n'ai rien ajouté dans le tableau (à l'exception du code couleur que j'ai indiqué juste pour mieux voir les valeurs quand j'ai contrôlé que le contenu ne comportait pas d'erreur).
Je ne sais pas si ce tableau sera d'ailleurs édité en l'état dans le projet final.
Je l'ai juste imprimé pour qu'on voit bien la structure et le contenu à filtrer.

La prochaine phase, c'est d'obtenir là des tableaux virtuels identiques à celui proposé mais avec des granularités différentes : un tableau Equipes, un tableau Services

Concernant la listview, je n'ai encore rien décidé. Je verrais demain au bureau comment nous sommes équipés.
Si c'est trop contraignant, comme je devais le diffuser à autant de responsables de services et de chefs d'équipes, je veux éviter trop de manœuvres d'installation sources de problèmes éventuels par la suite. Faire simple pour éviter les maintenances.

A+
Merci pour ton aide

++
Kotov

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Re
Excuse , mais je ne saisis pas , ma demande était :
Est ce que les intitulés Tâches et Absences sont déjà présents dans la feuille avant l'extraction(Ligne 2)
ou alors sont ils ? comme les Noms d'Agents ,les Services et les Équipes , ainsi que les Heures ajoutés par la procédure

Excuse encore !!!!

Bonne fin de journée
Amicalement
Jean Marie

 

[Kotov]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Re Jean Marie,

Désolé de te faire perdre du temps en conjectures.

Oui, le tableau exemple [A2:AR32] est la pure copie de mon tableau VBA, sans ajout manuel de la moindre donnée.
Les entêtes de colonnes sont affichées par la procédure.
C'est le nouveau tableau virtuel à partir duquel je souhaite réaliser mes filtres.

Il est issu de la ligne suivante qui recopie le tableau T_SumPlng().
Sheets("Test").[a2].Resize(UBound(T_SumPlng), UBound(T_SumPlng, 2)) = T_SumPlng

(mais pour ce fichier exemple je n'ai gardé qu'une trentaine de lignes, en réalité j'en ai plus de 320
Je ne l'ai édité dans cette feuille "Test" que pour contrôler la fiabilité des données cumulées )

En fait pour construire T_SumPlng, j'ai :
- en ligne 0 : à partir de la colonne 4, ajouté les entêtes de colonnes "Activité + une cellule vide + les colonnes "Absences" : copie à partir des tables de paramètres Activités et Absences
- puis à partir de la ligne 1 et ayant réservé les 3 premières colonnes pour les Noms, Services, Equipes, j'ai bouclé sur le tableau T_A() (le fichier VBA issu de l'onglet source "Data") pour :
- ajouter sur chaque ligne ces noms, services et équipes sur les 3 premières colonnes et poursuivre par le cumul de 1/4 d'heure de chaque item de la ligne 0. Lors du cumul, j'applique un coef (*0.25) pour transformer les 1/4 d'heures en heures.

Bonne fin de soirée,
Kotov

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Bonjour Kotov
Bonjour le fil
Bonjour le Forum

Ok , Merci de ces Infos
Tu ne me fais pas perdre de temps ,car je fais ça pour m'amuser les méninges Lol
je regarde ce que je peux en tirer .
Bonne journée
Amicalement
Jean Marie

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Bonjour Kotov
Bonjour le fil,Le Forum

Une autre Version Perfectible , car boucle sur les activités un peu longue Lol (on peut sûrement améliorer)
Tu regardes , j'ai constaté dans ton dernier fichier que les Heures étaient arrondies , ceux qui fausse un peu les résultats (Je n'ai pas arrondi ) voir feuille Test_Chti160
Certains calculs du Userform ne sont pas finalisés .
Tu regardes et tu me dis ce que tu en penses .

"J'ai Change le Fichier , car j'avais par inadvertance Supprimer une macro indispensable (D'ou ERREUR) "
Le Fichier : Regarde la pièce jointe Planning Chti160 6.2.3.1.xlsm

Bonne fin de Journée
Amicalement
Jean Marie

 

[Kotov]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Hello Jean-Marie,
Bonjour le Forum,

J'ai juste testé ta version et ça paraît superbe.
J'ai toujours un doute sur l'utilisation de la listview : sachant qu'elle n'est pas installée sur les postes de travail de tous les utilisateur, j'hésite à l'intégrer dans mon projet final (pour m'éviter de faire l'installateur en local sur une vingtaine de postes et de me faire opposer un refus par les services informatiques des utilisateurs hors site). A voir.

Je devais travailler sur la compréhension de l'objet Dictionary, mais du fait de l'hospitalisation d'un parent cette semaine, je n'ai pas eu le moindre moment pour m'y mettre. Je vais tenter de m'y mettre ce weekend à partir de ton fichier et de ce que je pourrais trouver sur le net.

Concernant l'arrondi des heures, il ne s'agit que d'une simplification dans le fichier exemple.
Dans la version réelle, l'export "Data" comptabilise des 1/4 d'heure que je transforme ensuite en heure puis en Equivalent Journée de Travail (EJT = 7h). C'est en fait la somme des 1/4 d'heures divisée par 28 et multipliée par la productivité/jour de chaque activité qui me donne un objectif de traitement. Objectif final de fin de journée et plan de marche au top horaire (agent/équipe/service/entité globale).

Il me reste donc à trouver du temps pour décortiquer tes macros et comprendre ce fonctionnement du dictionary.

Un énorme merci pour ton aide et ta patience !

A +
Kotov

 

[ChTi160]

Re : [VBA] - PLANNING - Extraire rationnellement les infos d'un tableau à 2 dimension

Bonjour Kotov
Bonjour le fil,Le Forum

Dommage pour la ListView qui est un bel Outil , car La ListBox ne permette pas autant de mises en formes
je vais regarder , pour modifier le Fichier et passer en ListBox
Pourrais tu me mettre un exemple de traitement des données comme indiqué ci dessous , par quelques exemples significatifs .

Equivalent Journée de Travail (EJT = 7h). C'est en fait la somme des 1/4 d'heures divisée par 28 et multipliée par la productivité/jour de chaque activité qui me donne un objectif de traitement. Objectif final de fin de journée et plan de marche au top horaire (agent/équipe/service/entité globale).

Merci par avance
Bonne fin de Journée
Amicalement
Jean Marie