Help macro encadrement données

VBA
#1

Bonjour à tous,
Merci d’abord de me lire.
Alors voici mon souci car mon savoir en matière de macro est limité :frowning_face: j’ai supprimer ce que j’ai fait car rien ne fonctionne :frowning_face:

Je souhaiterai créer 2 macros pour :

1- a partir d’un export dans excel générer un tableau croisé dynamique comme l’exemple dans l’onglet « calendrier » avec en rouge les heures supérieures à 10h. les données du tableau croisé dynamique doivent prendre celle de l’onglet « export » de A1:« Stop » (c’est la ligne à la fin de mon tableau car il évolue souvent).

2- avec le tableau croisé dynamique faire apparaitre en encadré un calcul. La règle étant de mettre en avant les agents qui ont 7 tours consécutifsTravaillés qui dépassent en tout 63h comme l’exemple de mon fichier, onglet « calendrier ».

Merci infiniment d’avance pour votre aide.doc travail HW.xlsx (177,9 Ko)

#2

Bonjour

Ci-joint ma proposition
Elle n’utilise pas l’option tableau croisé dynamique
A partir de l’onglet « Export » le traitement récupère toutes ses données pour les inclurent dans l’onglet « Export trié » et comme son nom l’indique les trie par date croissante en suivant puis les répartit dans l’onglet « Calendrier » à l’identique de celui fourni que j’ai renommé « Calendrier1 »
Il colore en rouge et en gras les heures supérieur à 10h
Pour le 2° point j’avoue ne pas comprendre ton attente :

« 2- avec le tableau croisé dynamique faire apparaitre en encadré un calcul. La règle étant de mettre en avant les agents qui ont 7 tours consécutifs Travaillés qui dépassent en tout 63h comme l’exemple de mon fichier, onglet « calendrier ». »

Si je regarde l’onglet « Calendrier1 » celui que tu nous as fourni tu as encadré 2 séries mais qui pour moi ne sont pas consécutif car sur la suite de chaque encadrement réalisé des jours sont sans aucune donnée le premier le 20 janvier est vide le deuxième le 18 et 19 janvier sont vides
Chaque suite donc est interrompu donc non consécutif

Il me faut comprendre ta logique pour mettre en place un traitement approprié en correspondance

Merci donc d’apporter des précisions pour le 2° point

Onglet « Export trié » clique sur le bouton « Traitement »
Résultat onglet « Calendrier »

Fais des essais et dis moi

doc travail HW (1).xlsm (218,1 Ko)

#3

Bonsoir FFo,

Merci infiniment pour la réponse. c’est génial ce que tu as fait. Il me faudrait juste pouvoir faire un tri avec les « Centre de couts » pour que je puisse voir qui dépasse le temps de travail par services.
Concernant la « régle » en effet c’est consécutif dans notre jargon mais c’est bien 7 tours de travail comme l’exemple. En fait il faut additionner 7 tours de travail sans prendre en compte les éventuels week-end ou vacances. Je ne sais pas si c’est plus clair ?

Redis moi si ça ne l’est pas :slight_smile:

Merci infiniment du temps que tu m’accordes
Excellente soirée

#4

Bonjour
Merci pour le retour
Ravi que cela te convienne
« Il me faudrait juste pouvoir faire un tri avec les « Centre de couts » pour que je puisse voir qui dépasse le temps de travail par services. »
Onglet « Calendrier » à l identique de celui transmis les Centres de coûts ne sont pas ramenés
Si il doivent l être merci de m indiquer de quelle manière en me fournissant un exemple
Je pourrais ainsi inclure en plus de celui des dates un tri les concernant
Dans l attente

#5

D’autre part en étudiant les données :

" En fait il faut additionner 7 tours de travail sans prendre en compte les éventuels week-end ou vacances.]"

Comment repérer les jours non travaillés (Week-end et autres fériés ou vacances) sachant que dans ton exemple d’encadrement pour les jours sans donnée par comparaison sur les lignes autours ces même jours sont remplis :

image
image861×183 67.3 KB

Pour le premier encadrement le 20 janvier est sans donnée pour la ligne mais la ligne du dessus et celle du dessous en ont une (8 et 10,23)
Pour le deuxième encadrement idem pour le 19 janvier aucune donnée sur la ligne mais ligne au dessus 11,33
Difficile d’en déduire un jour non travaillé

Comment distinguer pour ces interruptions une absence de travail donc série non consécutive d’un jour férié ou vacances donc série consécutive ?

Si 2 séries consécutives sont à encadrer décalées d’un jour comment le traduit-on ?

image
image887×190 63.8 KB

premier encadrement de la colonne K à la colonne R
deuxième encadrement de la colonne L à la colonne S

Merci pour ces précisions

#6

Bonjour FFO,

Pour le centre de cout dans mon exemple avec le tableau croisé dynamique il y est. Sinon ne pourrait on pas juste rajouter la colonne dans ton travail ce qui permettrai de faire par le « tri » apparaitre les différents centre de coût.

Pour les 63h, c’est là ou j’étais bloqué. Serait il possible de lui dire de partir de gauche à droite et de balayer les chiffres dés qu’il rencontre une série qui a un total de 63h ou plus il l’encadre et saute cette série pour continuer. Avec mes connaissances actuelle je ne sais pas si cela est possible :slight_smile:
Car tu as raison ce sont des agents pour qui il n’y a pas de notion de week end traditionnel « samedi+dimanche ». Ils ont souvent des week end décalés et/ou raccourci. c’est bien la toute la difficulté. Est ce que Excel peut partir du principe d’analyser 7 cellules non vide consécutives ?

Merci infiniment pour le temps que tu m’accordes.

Bonne journée

#7

Bonjour

Ci-joint ma nouvelle version
En colonne B onglet « Calendrier » la liste des Centres triés
Si la somme de 7 cellules consécutives non vides est supérieure à 63 pour une même ligne l’ensemble de ces 7 cellules avec celles qui sont vides sont encadrés d’une bordure rouge

J’espère que cela te convient

Fais des essais et dis moi

doc travail HW (2).xlsm (222,5 Ko)

#8

Bonjour FFO,

Merci infiniment pour ton travail.

C’est génial ça fonctionne à merveille.

Puis je encore abuser en te demandant s’il est possible de compter par centre de cout le nombre d’agent qui dépasse les 10h et ceux qui dépassent les 63h ?

Merci ++++ je me rend compte que j’ai énormément de choses à apprendre encore.

Bon aprem

#9

Merci pour le retour

"Puis je encore abuser en te demandant s’il est possible de compter par centre de cout le nombre d’agent qui dépasse les 10h et ceux qui dépassent les 63h "

Chaque ligne est un agent
Toutes les lignes ont au moins un encadrement de 7 cellules non vides qui dépassent les 63h

Donc tous les agent dépassent sur l’ensemble de leur ligne les 63h obligatoirement et à Fortiori les 10h
Donc le nombre total d’agents est le nombre de ceux qui dépassent les 63h et les 10h

Je ne sais pas si c’est la réponse à ta question

Merci de dire et dans la négative d’être plus précis avec un exemple à la clé

#10

Bonjour FFO,

Merci pour ta réponse. Dans le document joint j’ai rajouté une feuille « Analyse » dedans tu y trouvera 2 codes mais je ne sais pas trop comment les intégrer à ton code. Il y a aussi la forme que j’aimerai pour le résultat. Dans l’idéal il faudrait que le résultat se mette à jour par un bouton par exemple.

Merci infiniment.
Excellente journée
doc travail HW 2.xlsm (214,5 Ko)

#11

Bonjour

J’ai inclus tes codes fonction dans un nouveau module mais en les modifiant ainsi :

Function total10(ligne)
Dim somme As Long
somme = 0
For Each c In ligne
If c <> «  » And IsNumeric© Then
If c.Borders(xlEdgeBottom).Color = 255 And c.Borders(xlEdgeTop).Color = 255 Then
somme = somme + 1
End If
End If
Next

total10 = somme
End Function

Function total63(ligne)
Dim somme As Long
somme = 0
For Each c In ligne
If c <> «  » And IsNumeric© Then
If c.Borders(xlEdgeTop).Weight = xlMedium And c.Borders(xlEdgeBottom).Weight = xlMedium Then
somme = somme + 1
End If
End If
Next

total63 = Round(somme / 7)
End Function

Colonne B/C j’ai intégré les formules de ces 2 fonctions pour l’ensemble des lignes de l’onglet « Calendrier » (une ligne pour ces 2 colonnes pour une ligne onglet « Calendrier ») qui correspond au centre « FB »
Idem colonne D/E pour le centre « FL »
En fin de ces colonnes de ces formules j’ai intégré une ligne « Total général » faisant la somme par colonne
Voir le nombre de lignes à prévoir pour la récupération de toutes les lignes onglet « Calendrier »
Je pense que leur importance variera d’un traitement à l’autre
donc prévoir de la marge onglet « Analyse » pour parer à toute éventualité
Très simple il suffit de reporter le « Total général » en laissant un nombre suffisant au dessus de lignes qui devront traiter l’ensemble des lignes de l’onglet « Calendrier »
Ne pas hésiter à en mettre plus un excès n’altèrera en rien le résultat ce qui ne sera pas le cas du contraire
Faire un Copier/Coller des formules colonne B/C/D/E en place ligne 16 par exemple sur toutes les lignes jusqu’à celle au dessus de la ligne « Total général »
Refaire éventuellement les formules de somme sur cette dernière ligne « Total général »

En espérant que cela correspond à tes attentes

Dis moi

doc travail HW 2.xlsm (215,9 Ko)

#12

Re FFO,

Merci pour le temps que tu m’accordes.

En revanche ce document est destiné à prendre en considération plusieurs milliers de lignes et surtout environ une 50aines de centres de coûts différents du coup est il possible que l’« analyse » soit sur la verticale comme mon exemple car ce document sera surement imprimé.

J’ai fait plusieurs test mais malheureusement il n’y a pas de résultat. Est ce moi qui ne suis pas doué :blush: c’est surement cela :slight_smile:
doc travail HW 3.xlsm (214,9 Ko)
Merci infiniment pour ton aide.

Bel aprèm

#13

Non tu as très bien travaillé !!!

Pas de souci juste un petit rafraichissement pour relancer le traitement des fonctions avec toutes les données remis en place par le traitement général

J’ai à cette fin rajouté un bouton onglet « Analyse » en A1/A2 bouton « Actualisation »
Le traitement principal terminé onglet « Analyse » tu actives le bouton et le miracle s’opère

Pour un peu plus de confort j’ai mis onglet « Export trié » en G2 le nombre de lignes totales à traiter en H2 un compteur de ligne d’avancement qui mets la ligne en cours de traitement
Ainsi de mieux patienter quand celui-ci s’exécute

Onglet « Analyse » j’ai amélioré les formules
Chacune fait référence à la cellule de la ligne 1 de sa colonne
la colonne B traite le centre « FB » pour les 10h
la colonne C traite le centre « FB » pour les 63h
la colonne D traite le centre « FL » pour les 10h
la colonne E traite le centre « FL » pour les 63h

Etc.……

Le centre traité est celui en ligne 1 pour la colonne

Toutes les formules se base sur cette ligne pour la colonne considérée
J’ai intégré des « $ » approprié concernant le ciblage de cette cellule ainsi que pour le ciblage du Centre colonne B onglet « Calendrier »
Ainsi maintenant pour tout nouveau centre juste à mettre son sigle ligne 1 pour les 2 premières colonne de libre (attention de bien respecter le même sigle à la casse prêt onglet « Calendrier » colonne B)
Puis de copier 2 colonnes déjà équipées de formules et de les recopier à l’identique sur les 2 nouvelles colonnes choisies
C’est comme cela que j’ai rajouté les centres FB2/K/SAA
Cela fonctionne nickel
Il faudra peut être ensuite faire un coup de réactualisation
A voir
Pour les lignes copier l’intégralité de la dernière sur les suivantes autant de fois que nécessaire en fonction du nombre de ligne de l’onglet « Calendrier » à traiter
Rajouter ensuite juste après (ligne suivante) le Total général pour toutes les colonnes (simple somme de toutes les cellules de la colonne)

Pas très compliqué

Testes ainsi cette version et dis moi

doc travail HW 3.xlsm (221,4 Ko)

#14

Bonjour FFO,

C’est génial en effet le cheminement parait simple :slight_smile: par contre pourquoi il n’y a rien dans la colonne A. Je m’explique serait il possible que dans la colonne A il y a les mois où se trouve les soucis ?
Par exemple colonne A « janvier » : FB : 11 souci avec les 10h et 5 souci avec les 63h pour les 63h le total devant être pris au premier jour.

Idéalement pour si le souci des 63h est à cheval sur 2 mois janvier et février par exemple il faudrait qu’il soit intégré au mois du premier soit soit en janvier. Si cela est possible.

J’abuse je suis désolée. Je comprendrai si tu en as marre de mon cas. Merci infiniment pour ton aide.

Belle journée

#15

Bonjour
J’espère que pour l’instant le résultat des différents traitements te convient
« pourquoi il n’y a rien dans la colonne A. »
J’ai réservé cette colonne pour les titres de lignes (Total général par exemple)
Tu peux l’enrichir pour une meilleur compréhension à ta convenance

« Par exemple colonne A « janvier » : FB : 11 souci avec les 10h et 5 souci avec les 63h »

Qu’entends tu par souci ?
Qu’est ce que c’est que ces 11 soucis pour FB avec les 10h
Qu’est ce que c’est que ces 5 soucis pour FB avec les 63h

Peux tu m’expliquer avec des exemples je suis un peu dans le brouillard

Merci

#16

Re,

Les soucis sont les ceux en rouge (c’est à dire les personne qui dépassent 10h de travail) et les encadrés rouges (c’est à dire ceux qui dépassent 63h en 7 jours).

J’ai compté sur janvier tous ceux qui dépassent les 10h pour le centre de cout FB c’est égal à 11 cases qui sont en rouge. idem pour les 63h il y a 5 paquets de 7 jours encadré en rouge.

Ce serai génial si dans « analyse » je pouvais avoir ces chiffres. avec en ligne 1 les centres de couts et dans la colonne A les mois. Je prévois de faire cet export tous les 3 mois environ ce qui va représenté plusieurs milliers de lignes d’exports avec plus de 40 centre de cout différents.

Cet onglet analyse permettrai de voir plus rapidement combien il y a de souci pour les 10h et pour les 63h.

J’espère que mon explication t’aidera à m’aider :blush:

Belle journée

#17

Ci-joint nouvelle proposition

Derrière le bouton « Actualisation » onglet « Analyse » j’ai intégré une procédure qui réalise le détail par mois des dépassements 10h rapporté en colonne A en regard de chaque ligne qui correspond à la ligne concernée onglet « Calendrier »
Les cellules de cette colonne qui détail ce type d’anomalie sont déjà bien surchargées et ce pour seulement 3 mois de Janvier à Mars
Je crains le pire avec un nombre de mois accru et avec le pendant pour les dépassements 63h à ajouter
En une cellule pour une ligne je pense que c’est trop d’information
A toi de me dire

Ci-joint cette version avec les dépassements 10h par mois détaillés

Onglet « Analyse » cliques sur le bouton « Actualisation » résultat en colonne A

Donnes moi ton avis

doc travail HW 3 (2).xlsm (215,6 Ko)

1 J'aime
#18

Bonjour FFO,

Merci pour ton analyse qui me permet de remettre en question mon cheminement.
Je viens de faire un test sur un groupe test avec plus de 3500 lignes le final devrait compter plus de 30 000 lignes.

Bravo pour ton travail car j’avais peur qu’avec beaucoup de lignes dans l’export le « traitement » soit long mais ça a mis seulement 16 secondes.

Petite souci : dans l’onglet calendrier plusieurs cellules des colonnes C, D, E, F, G sont encadrés pourrait on réparer cela ?

Concernant l’analyse, tu as raison cela risque d’être trop lourd. Du coup j’ai crée un onglet « analyse 2 », serait il possible que :
1- la colonne centre de cout s’alimente avec chaque centre de cout en colonne ?
2- que les colonne B (sup à 10h) et C (sup à 63h) se mettent à jour par un bouton ?

Merci infiniment de ton aide.
Belle journéedoc travail HW 4.xlsm (392,1 Ko)

#19

Bonjour

« Petite souci : dans l’onglet calendrier plusieurs cellules des colonnes C, D, E, F, G sont encadrés pourrait on réparer cela ? »

Pour moi ces encadrements font suite au traitement 63h qui entoure 7 données dont la somme dépasse cette valeur encadrement prévu pour l’ensemble de ces 7 données avec les cellules vides comprises
Lorsque le traitement s’effectue il le fait à partir de la colonne C
Pour les cellules des premières colonnes sans données (C,D,E,F,G) celles -ci font parti d’un groupe de 7 données dont la somme dépasse 63h donc normal qu’elles soient encadrée avec les autres
C’étaient tes consignes
A toi de voir si elles doivent être améliorées

Dis moi

1 J'aime
#20

Re bonjour FFO,

En effet c’était bien ma consigne tu as raison. c’est juste étrange que les premiers jours de l’année vide soient encadrés. mais tkt c’est toi qui a raison. Désolée.

Bon aprem