Regroupement d'heures en fonction de date et de critères
Bonjour,
Venant d'intégrer un nouveau poste, je cherche à optimiser la saisie des heures sous format informatique.
A l'heure actuelle, les heures sont saisies de manières détaillées sous un formulaire access.
Je dois les transmettre de manières moins détaillées sous un format excel.
Je mets les fichiers excel feuille d'heure et extraction access (2 onglets) en pièces jointes
Les informations que je souhaite reprendre sont spécifiquement la date, le type de travail, le nombre d'heures en fonction de la personne.
Pour l'instant, j'ai le détail par journée de tous les travaux effectuées. Il peut donc y avoir plusieurs entrées pour une même date, pour des types de travaux différents.
L'objectif est de cumuler les heures par type de travail (et par personne) pour venir compléter la feuille excel standardisée.
J'ai tenté de faire une première approche en extrayant depuis access vers excel.
De là, faire un onglet spécifique regroupant les infos utiles seulement.
Et enfin d'additionner les heures en fonction de la date, du type de travail et de la personne.
C'est ici que j'ai besoin de vos lumières : je n'arrive pas à regrouper plusieurs lignes ayant la même date et le même type de travail et la même personne.
Je voulais utiliser une boucle de type
TANT QUE la date est identique, et que la personne est X, on fait la somme des heures pour le type de travail X
Puis on refait la même opération pour le type de travail Y
Puis on refait la même opération pour le type de travail Z
Puis on passe à la même formule mais pour la personne Y soit
TANT QUE la date est identique, et que la personne est Y, on fait la somme des heures pour le type de travail X
Puis on refait la même opération pour le type de travail Y
Puis on refait la même opération pour le type de travail Z
Puis on passe à la date suivante
etc...
Je ne connais pas du tout le VBA, je pense que c'est la solution, pouvez-vous me le confirmer ?
Si oui, est-ce simple ?
J'espère avoir été le plus clair possible, sinon je reste à disposition pour tout détail complémentaire.
Merci !
Bonjour DjiDji59430,
Déjà un grand merci pour ta réponse très rapide
Je n'ai jamais utilisé de tableau croisé dynamique mais ce que tu as fait semble effectivement bien convenir !
Comme je ne comprends pas entièrement le processus, comment faire pour modifier les listes de choix (sur l'onglet que tu as créé, soit feuil 1) ?
Pour le reste, je vais gratter un peu et tâcher de comprendre. Je reposterai si j'ai des questions.
Encore merci !
re
Le tcd, c'etait pour verifie que ma formule dans le tableau juillet 15 etait bonne
pour le tcd, il suffit de cliquer sur les entonnoirs, et modifier le choix.
Ensuite, si tu cliques le filtre etiquette de ligne (on peut mettre l'intitulé que l'on désire) ==> filtre chronologique
Merci.
Je n'avais pas vu que le tableau croisé dynamique n'était pas "utile" dans la formule.
Du coup, je ne pense utiliser que ta formule.
Dans celle-ci, je ne comprends tout de même pas comment tu va chercher les données dans l'onglet d'extraction.
Si je regarde la formule =SOMMEPROD((lieu='Juillet 15'!F$8)*((Date)=$A19)*(qui=$P$4)*(Nheures))
Tout "reste" dans la feuille Juillet 15 ?
Désolé si c'est évident, mais je souhaite bien comprendre
(edit : en fait, je pense que c'est la dernière partie (Nheures) qui me pose problème. Lorsque j'utilise les antécédents pour la formule, c'est n'est relié à rien, or, les calculs fonctionnent bien. Comment récupérer ce Nheures en n'indiquant que ce texte ?!!
Merci !)
(edit 2 : je pense avoir trouvé, en réécrivant la formule, une étiquette apparaît pour proposer la fin du libellé. Par contre autant j'arrive à faire fonctionner sur ton fichier, autant je n'y arrive plus en faisant une extraction "fraîche" et en modifiant les champs... ça me donne une erreur #REF)
re
Un nom est valable, soit pour la feuille, soit pour le classeur.
Si sur la feuil20 tu fais reference a date, ce sera =Tableau1[Date] ou entreedonnees!B2:B487 (donc pas de ref a juillet 15)
es tu allé voir dans Formule==>gerer les noms ?
Renvoie ton nouveau fichier, si tu veux !
Aide très appréciée !
Je remets le fichier en pièce joint avec les "vraies" valeurs.
J'ai mis en jaune les colonnes qui nous intéressent, les autres ne sont pas utiles.
Je n'arrive pas à réger ce problème de #ref :/
Merci encore
re
Tu ne peux pas demander de rechercher H2O Vouvry (lieu='Juillet 15'!G$8) de la feuille juillet 15 alors que H2O Vouvry n'existe pas dans ta feuille entreedonnees.
Tu dois obligatoirement avoir les memes references pour les memes désignation.
Parcs et bâtiments Stap et Réseau EU Service Privé Séance Fontaines PGEE Hydrantes n'existent pas dans ta feuille entreedonnees.
tu revoies ou la 1 éré ou la 2 éme feuille pour que tout soit cohérent
Merci encore
Je vois pour modifier le fichier access maintenant pour récupérer d'autres données et je testerai une fois l'extraction access sera ok.
Je reposerai des questions sur un autre post si jamais.
Je note celui ci en résolu et te renouvelle mes remerciements.
Bonne continuation !
Grmllmlml !
J'ai pu modifier mon fichier access (non sans peine !) pour avoir la possibilité d'extraire une nouvelle colonne "catégorie" qui me permettra de venir incrémenter directement la feuille d'heure en fonction des entrées quotidiennes inscrites sur le fichiers access.
Le tout, grâce à ta formule.
J'ai donc réalisé une extraction en ne mettant que quelques jours (18 et 19 aout) pour tester cette incrémentation via ta formule.
Ça me donne une erreur #REF alors que la colonne catégorie comporte bien le nom recherché (Step, également pris comme base de recherche dans la feuille d'heure), à la date donnée (19.08.15), pour la personne donnée (Alain)
Une idée de mon erreur ?
D'avance merci !
Bonjour,
tu veux rajouter un nouveau critere categorie =>ça fonctionne
fais attention. sommeprod est une fonction matricielle => ça demande que pour faire des operations comme on fait, les matrices soient de longueurs égales.
mais sommeprod() donne toujours en resultat des nombres !!!
Salut DjiDji59430,
Merci encore
Juste je ne comprends pas lorsque tu dis "ça demande que [...] les matrices soient de longueurs égales"
Si tu peux m'éclairer ?
Ensuite, j'ai voulu retoucher encore, en supprimant le critère "lieu" puisque seul le critère "Catégorie" est suffisant.
J'ai également voulu tester sur une extraction fraîche mais les formules ne semblent alors plus fonctionner.
Pour reprendre ma démarche :
1. Remplissage Base de données Access
2. Extraction depuis Access vers Excel, création d'un nouveau fichier
3. Ouverture de la feuille d'heure "automatique" comportant l'onglet août 2015 et les formules
4. Suppression de l'ancien onglet d'exportation
5. Déplacement de l'exportation fraîche excel vers la feuille d'heure automatique
De là, j’espérai qu'en remettant un onglet de même nom, avec la même configuration, la feuille d'heure irait directement récupérer les heures et les incrémenterait comme une grande.
Certaines cellules "catégorie" étaient vides.
En les complétant, alors les formules fonctionnent après la procédure décrite dessus.
Je vais devoir optimiser encore ces étapes de transfert de données vers la feuille de calcul pour simplifier les opérations.
Si jamais tu as des idées, je suis preneur
Sinon, un tout grand merci pour ton soutien, ta réactivité, et tes solutions !
re
Renvoie une extraction fraiche, a l'ombre, sur une chaise, sous un parasol, cri-cri ,cri-cri, cri-cri ........
Je vais regarder si on peut faire qque chose avec catégorie a la place de lieu.
Quand je dis que les matrices doivent etre de longueurs egales, si la premiere est B2:B483, elle comporte 482 éléments. les autres doivent avoir le même nombre d'éléments (dans notre cas). le genre H:H est a proscrire, car trop long et tu melande du texte et des nombres ( pas bon) et tu risques de faire ramer ton PC, les matricielles sont lourdes.
Le but est toujours de calculer un nombre d'heure ???
Bonjour DjiDji59430,
Pour te répondre, oui, l'objectif est toujours de calculer le nombre d'heures.
A l'heure actuelle, j'ai un tableau qui fonctionne... Presque !
Avec les anciennes données, les formules fonctionnent correctement.
Par contre, si je veux "mettre à jour" avec des données récentes, ça ne semble plus fonctionner.
Pour être plus clair : j'avais les données jusqu'au 19 août et nous avons créé les formules à ce moment là => le tableau de synthèse fonctionne.
Si maintenant je veux rajouter les données entre le 19 et le 23 août, les formules ne semblent plus calculées pour ces "nouvelles" dates.
Grâce à ton dernier message, je comprends maintenant que l'on assigne manuellement une longueur de matrice via le gestionnaire de noms et que mes "nouvelles" entrées ne sont donc pas dans cette plage.
1) Puisque l'on ne peut pas sélectionner l'ensemble d'une colonne, quelle solution reste-t-il ? Venir sélectionner chaque fois la "bonne plage" pour avoir des matrices identiques et ayant les mêmes "bonnes" données ?
2) A la rigueur, je n'ai besoin des données que du mois "précédent" (on fait la feuille d'heure du mois x en début de mois x+1)
Si je détermine des noms de fonction et des matrices qui englobent tout un mois, est ce que je peux venir coller chaque mois les données "par dessus" les entrées du mois précédent par exemple ?
3) Dans le gestionnaire de nom, j'ai plusieurs "date". Un "date" classeur et un "date" pour l'onglet aout 2015. Est-ce normal ? Ne devrait-il pas n'y en avoir qu'un ?
(idem pour "qui" "Nheures")
4) Toujours dans le gestionnaire de nom, j'ai un "tableau 1" que je ne peux pas modifier ? Est-ce normal ?
J'espère être clair... Mais pas d'hésitation s'il faut des détails complémentaires.
Je remets le fichier en PJ pour illustration
re
J'ai rendu toutes les plages des noms dynamiques
Tu peux ajouter autant de lignes que tu veux
Pour le tableau, il est dynamique. Ça se voit pas, mais il est.
C'est rapide
Merci DjiDji59430 !
J'ai vu que tu as modifié les formules dans gestionnaire de nom, je suppose que c'est ce que tu appelles rendre le tableau dynamique !
Pour optimiser et simplifier les transferts depuis access et excel, je voudrais savoir si on peut faire quelques lignes VBA / macro qui ferait, en gros "récupères les données du mois M et vient les placer dans la feuille FET modif dynamique.
Le tout pour éviter les erreurs de manipulations dues au copié collé d'onglet à onglet.
Pour simplifier :
1) j'exporte d'access vers excel en le nommant "truc"
2) j'ouvre le fichier FET dynamique
3) je copie l'onglet "truc" dans FET dynamique
=> 1) j'appuie sur un bouton sur FET dynamique qui vient récupérer les données depuis au mieux access, sinon depuis "truc"
Bon, ça n'enlève qu'une étape, pas sûr que ce soit si avantageux !!
En tous les cas, encore merci pour ton aide et ta disponibilité.
Je ne sais pas si j'ai bien compris, mais si tu es en vacances comme tu semblais le dire dans ton avant dernier message, ne t'occupe pas de ma dernière demande et profite bien de tes congés !!!
re
Je suis toujours en vacances .......Je suis retraité !
Quant au VBA, je ne sais pas faire.
repose ta question dans un nouveau post en reprennant ce que tu as ecrit dans le dernier.
"Pour optimiser et simplifier les transferts depuis access et excel, je voudrais savoir si on peut faire quelques lignes VBA / macro qui ferait, en gros "récupères les données du mois M et vient les placer dans la feuille FET modif dynamique.
=> 1) j'appuie sur un bouton sur FET dynamique qui vient récupérer les données depuis au mieux access, sinon depuis "truc" "
et avec les fichiers qui vont bien !
Crdmt
Alors bonne retraite !
Et merci à nouveau pour ton aide précieuse et tes solutions