Ventilation d'heures

Bonjour à toute la communauté,

Nouvel arrivant sur ce forum, merci de m'accueillir parmi vous.

Je cherche à optimiser une formule qui rend très lent mon fichier excel car celle-ci est dupliquée un grand nombre de fois (environ 1500 fois en ligne et 192 fois en colonne).

Le fichier excel sert à réaliser de la planification. La formule se trouve dans les cellules S4:AP4 du fichier joint.

Le principe de la fonction est de ventiler un volume d'heures (en O4) d'une tâche entre 2 dates (début (P4) et fin (Q4)) selon les heures de disponibilités de la personne (M4) sur cette même période (AR9:BO9).

Les dates de début et de fin vont faire référence à des numéros de "quinzaines" au travers de rechercheV pour les associer à des tronçons de l'année (S1:AP1).

1 au 15 janvier = 1 ère quinzaine = 2

1 au 15 février = 3 ème quinzaine = 6

etc

Exemple :

1 personne est disponible 100% du temps entre le 1er janvier et le 1 mars 2019.

La période entre le 1er janvier et le 1er mars couvre 5 "périodes de quinzaines".

1 tâche est associée à cette même personne, durée de la tâche : 100h

La formule va ventiler sur les 5 quinzaines de cette période les heures, soit 20h/quinzaine sur les 5 quinzaines de la période.

Par contre, si on indique que du 1er au 15 janvier 2019 (1 période de quinze jours), la personne est indisponible alors la formule va augmenter le volume d'heures à ventiler sur les périodes de disponibilité, soit 25h/quinzaine sur les 4 quinzaines de dispo parmi les 5 quinzaines de la période.

J'espère que j'ai bien posé la situation

Si par formule, du fait qu'elle est dupliquée à chaque fois qu'on rajoute des tâches, il n'y a pas de solution en terme de lenteur; peut être que par macro c'est possible ?

On pourrait imaginer un fonctionnement de ce type :

Si une ligne de tâche contient un volume d'heure, une date de début, une date de fin alors selon la disponibilité de la personne sur la période concernée, calcul des heures à ventiler et inscription dans les cellules de la plage correspondante... Mais là je suis hors jeu niveau développement...^^

Merci par avance.

Bonjour,

en effet, pas étonnant que cela rame

1- peux-tu nous dire quel résultat tu attends par le biais de cette formule ?

2- je pense du coup que, en ajoutant une colonne qui indiquera la quinzaine dans la tableau, on aura le même résultat plus rapidement avec un TCD

Bonjour Steelson,

Merci de t'être intéressé à mon sujet.

illustration

Le résultat attendu est quelque chose de ce type (voir illustration).

L'idée c'est d'avoir une répartition des heures selon la disponibilité de la personne selon une date de début et une date de fin.

Et autant de ligne que de tâche finalement (d'où la lenteur extrême).

Ton calendrier est évidemment super complexe !

Ce qui consomme des ressources est de l'avoir établi et ensuite d'aller y puiser les valeurs. C'est logique mais long en process de calcul.

Je te donne juste un avis car je n'ai pas encore bien compris la structure des données de base : il faut remplacer tous les RECHERCHEV par la transformation de la date en colonne P et Q en mois et en quinzaine (<15 ou >15 du mois) en simplifiant le calendrier que tu propose (non plus basé sur des semaine mais sur la date (<15 ou >15 du mois). Néanmoins si tu souhaites conserver ton principe de semaine entière, alors il faut une formule plutôt que RECHERCHEV qui balaye de façon séquentielle toutes les lignes de ton tableau.

Bonjour Steelson,

Au niveau structuration des données :

1 zone calendrier De C:G. Où à partir d'une date on trouve le numéro de "quinzaine" de l'année, numéro qui se retrouvent au dessus de chaque mois dans les autres zones. C'est ce qui aux formules de se repérer chronologiquement (et cela permet de faire du pluri-annuel facilement car on raisonne par tranche ferme de 15 jours, la semaine 1 de l'année suivante sera forcément dans la 1ère quinzaine de l'année suivante).

1 zone "indisponibilité" De P7:AP10.

avec la même structure des numéros de "quinzaines" au dessus des mois.

Zone où on trouve l'information telle que : le volume d'heure de base de dispo d'une personne sur 1 quinzaine : 76h

1 zone "calcul des heures disponibles" De AR7:BO10.

avec la même structure des numéros de "quinzaines" au dessus des mois.

Zone où se réalise le calcul suivant : heure dispo de base - (heure dispo de base * coeff absence) --> renvoie les heures de dispo par personne et par quinzaine sur l'année

1 zone "heures d'une tâche à ventiler sur la période selon la dispo de la ressource associée" De M1:AP4.

avec la même structure des numéros de "quinzaines" au dessus des mois.

Zone où les données d'entrées telles que volume à répartir, date de début, de fin et personne associée sont renseignées.

Si je découpe la formule afin de mieux expliquer son fonctionnement (avec mes mots ^^) :

En me situant en S4 :

=SIERREUR(SI(ET(S$1>=RECHERCHEV($P4;Calendrier!$C$3:$I$1464;5;FAUX);S$1<=RECHERCHEV($Q4;Calendrier!$C$3:$I$1464;5;FAUX));

Test si le numéro de quinzaine de la colonne en cours (S) est bien situé entre les dates de début et de fin.

Si vrai :

($O4Volume d'heures à ventiler

*((RECHERCHEV($Q4;Calendrier!$C$3:$I$1464;5;FAUX)-RECHERCHEV($P4;Calendrier!$C$3:$I$1464;5;FAUX)+1)Différence de numéro de quinzaine entre date de fin et de début+1

*(RECHERCHEV($M4;$P$9:$Q$10;2;FAUX)-RECHERCHEV($M4;$P$9:$Q$10;2;FAUX)*RECHERCHEH(S$7;$P$7:$AP$10;EQUIV($M4;$P$9:$P$10;0)+2;FAUX))

Volume d'heure de dispo de base de la personne (76h)-Volume d'heure de dispo de base de la personne*coeff absence

/SOMMEPROD(($P$9:$P$10=$M4)*($AR$7:$BO$7>=RECHERCHEV($P4;Calendrier!$C$3:$I$1464;5;FAUX))*($AR$7:$BO$7<=RECHERCHEV($Q4;Calendrier!$C$3:$I$1464;5;FAUX))*($AR$9:$BO$10))))

Filtre sur la personne concernée, selon la zone des numéros de quinzaine, le volume d'heure de dispo de la personne

*((SOMMEPROD(($P$9:$P$10=$M4)*($AR$9:$BO$10)*($AR$7:$BO$7=S$1)))

Filtre sur la personne concernée, selon le numéro de quinzaine, le volume d'heure de dispo de la personne

/(RECHERCHEV($M4;$P$9:$Q$10;2;FAUX)-RECHERCHEV($M4;$P$9:$Q$10;2;FAUX)*RECHERCHEH(S$7;$P$7:$AP$10;EQUIV($M4;$P$9:$P$10;0)+2;FAUX)))

Volume d'heure de dispo de base de la personne (76h)-Volume d'heure de dispo de base de la personne (76h)*coeff d'absence de la ressource

/((RECHERCHEV($Q4;Calendrier!$C$3:$I$1464;5;FAUX)-RECHERCHEV($P4;Calendrier!$C$3:$I$1464;5;FAUX)+1));0);0)

Différence de numéro de quinzaine entre date de fin et de début+1

Je joints également un screen du découpage de la formule, pour une tâche de 240h entre le 1 er janvier et le 1 mars 2019 (soit entre quinzaine n°2 et 10).

capture

1 zone calendrier De C:G. Où à partir d'une date on trouve le numéro de "quinzaine" de l'année, numéro qui se retrouvent au dessus de chaque mois dans les autres zones. C'est ce qui aux formules de se repérer chronologiquement (et cela permet de faire du pluri-annuel facilement car on raisonne par tranche ferme de 15 jours, la semaine 1 de l'année suivante sera forcément dans la 1ère quinzaine de l'année suivante).

Ok c'est parfaitement logique, mais c'est ce qui consomme beaucoup de ressources à cause des RECHERCHEV qu'il faut arriver à supprimer. C'est leur nombre qui ralentit considérablement le calcul.

Soit tu passes à une logique 1-14 = 1ère quinzaine, 15-31 = 2ème quinzaine et tu intègre cela dans les formules directement sans passer par ce tableau calendrier, soit tu restes avec cette logique, mais en tout état de cause tu supprimes ce tableau et tu intègres les formules dans ton calcul.

avec un peu de temps je vais me replonger dans ton explication

Merci Steelson, une piste d'optimisation

Pour que le reste de la formule fonctionne, c'est à dire qu'il y ait correspondance entre les différentes zones ET que l'avancée chronologique se fasse, il faut garder le système de "clé" de numéro de quinzaine.

Donc une formule qui à partir d'une date me renvoie directement mon numéro de quinzaine et cela sans discontinuité sur 4 ans glissants.

Du 1er au 15 janvier XXXX = 2

Du 15 au 31 décembre XXXX+4 = 192

Cette formule me renverra la quinzaine souhaitée à partir de la date de début ou de fin (ici en P4 je testais sur la date de début) :

=(MOIS(P4)*2-(JOUR(P4)<16))*2

Le problème est pour la continuité lorsque l'année change...

31/12/2019 me donnera 48

01/01/2020 me donnera 2 et la chronologie ne fonctionne plus car 2 sera associé à janvier 2019.

EDIT : hum...

Etant donné que en dessous des numéros de quinzaine il y a le mois avec l'année... peut être moyen de récupérer cette info comme dans calendrier...

J'ai réussi à me passer du calendrier...

Voilà la formule qui permet de convertir directement la date (ici de début: F4) en quinzaine ce qui permet de se passer des RechercheV des dates dans le calendrier.

=(SI(ANNEE($F4)>2019;SI(ET(ANNEE($F4)>2019;((MOIS($F4)*2-(JOUR($F4)<16))*2)=48);((MOIS($F4)*2-(JOUR($F4)<16))*2)*(ANNEE($F4)-2019);(ANNEE($F4)-2019)*48+((MOIS($F4)*2-(JOUR($F4)<16))*2));((MOIS($F4)*2-(JOUR($F4)<16))*2)))

J'ai pu l'incorporer dans ma formule initiale via un CTRL+H... le rendu fait mal aux yeux ! Est-ce qu'on ne pourra pas nommer des plages dans la formule pour mieux s'y retrouver ?

De plus, j'ai étiré celle-ci sur 1600 lignes et 192 colonnes; il y a des ralentissements importants à l'ajout d'une ligne mais cela ne plante pas, suffit d'être patient... La formule peut-elle être simplifiée ?

Cette formule me renverra la quinzaine souhaitée à partir de la date de début ou de fin (ici en P4 je testais sur la date de début) :

=(MOIS(P4)*2-(JOUR(P4)<16))*2

Le problème est pour la continuité lorsque l'année change...

31/12/2019 me donnera 48

01/01/2020 me donnera 2 et la chronologie ne fonctionne plus car 2 sera associé à janvier 2019.

Pourquoi multiplier par 2 ?

=(MOIS(P4)*2-(JOUR(P4)<16))

suffit ?


Après, pour des questions de lisibilité, ce que je ferais :

  • à côté des dates de début et de fin, je mettrais l'année et la quinzaine, soit comme ceci 2019|12 , ou mieux (voir la suite) 2019,12 (donc en numérique décimal)
  • en tête du calendrier, je mettrais la quinzaine sous la même forme

La formule devrait alors se simplifier pour ne faire appel qu'à ces valeurs calculées une seule fois.


Ensuite, pour des questions de rapidité, plutôt que RECHERCHEH ou EQUIV, j'irai directement chercher la valeur souhaitée par un DECALER qui prend en compte la différence entre les quinzaines par rapport à une origine, genre

100*(quinzaine-2019,00).

Je reconnais que pour passer une nouvelle année, il faudra compléter cette formule.

Bonjour Steelson,

Le *2 n'est pas nécessaire effectivement si on raisonne en une année qui va de 1 à 24 (en gardant en tête qu'il s'agit de quinzaine).

Ici le fait de multiplier par 2 permet de coller à un avancement en semaine (48).

Je te remercie pour la piste concernant les RechercheV lié au calendrier qui rendait lourd la démarche.

Je me suis donc absout du calendrier.

Malgré cette optimisation, la multiplication des lignes contenant cette formule reste trop importante. C'est pourquoi le passage à une macro est nécessaire... et j'ai besoin des experts présents ici

L'idée est de garder une seule ligne avec les formules, et pour toute nouvelle ligne où on doit ventiler les heures, faire via macro :

. copier zone contenant formules : Liste_projets!DP4:HG4

. sur la zone souhaitée, coller formules Liste_projets!DPX:HGX

. calculer la feuille

. copier la sélection, coller en tant que valeur Liste_projets!DPX:HGX

Cette fonction doit s'effectuer :

. dès l'ouverture du fichier pour toutes lignes dont la cellule en colonne "R" de l'onglet "Liste_projets" contient le terme "RETARD"

. à chaque pression sur un bouton pour toutes lignes où le curseur a sélectionné des cellules (exemple je sélectionne les cellules D10:H15 --> la fonction se lance sur les zones DP10:HG10 ; DP11:HG11 ; DP12:HG12 ; DP13:HG13 ; DP14:HG14 ; DP15:HG15)

Pour l'instant j'ai le code suivant pour que les cellules devant afficher "RETARD" se calculent dès l'ouverture du fichier (ces cellules estiment le retard par rapport à "AUJOURDHUI()".

Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
End Sub

Via l'enregistreur de macro j'ai généré ce que je voulais pour un seul cas :

'Sub TEST()

    Range("DP4:HG4").Select
    Selection.Copy

    Range("DP11").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'End Sub

Ensuite j'ai trouvé quelques bouts de code que j'ai essayé d'adapter à mon problème, ici il référence toutes les cellules contenant le mot "RETARD" :

Sub Cherche_retard()

Dim Cellule As Range, Retard As String, Result As String

Set Cellule = Range("R:R").Find(What:="RETARD", LookIn:=xlValues, LookAt:=xlWhole) '<-- cherche "RETARD" dans la colonne R
    If Not Cellule Is Nothing Then '<-- si "RETARD" est trouvé
        Retard = Cellule.Address '<-- on mémorise l'adresse de la première occurence trouvée
        Do
                Result = Result & Chr(10) & Cellule.Address(0, 0) '<-- on mémorise l'adresse de la ligne

            Set Cellule = Range("R:R").FindNext(Cellule) '<-- on continue la recherche
        Loop While Not Cellule Is Nothing And Cellule.Address <> Retard '<-- tant qu'on trouve "RETARD" et que l'occurence trouvée est différente de la première occurence trouvée
    End If

'affichage du résultat
MsgBox "les lignes sont :" & Chr(10) & Result
End Sub

Il manque l'action souhaitée de copier/coller les formules, calculer la feuille etc pour chaque occurrence selon les lignes contenant "RETARD".

Enfin le fait de pouvoir lancer la démarche de copier/coller les formules, calculer la feuille etc pour toutes les lignes sélectionnées par l'utilisateur avec action de sa part sur un bouton.

Pouvez-vous m'aider ?

Je vous joints un fichier pour illustration.

Merci.

27vba-planif.xlsb (86.92 Ko)

Ici le fait de multiplier par 2 permet de coller à un avancement en semaine (48).

hum, j'avais bien vu cette bizarrerie ... donc là je décroche totalement du sujet. Car à la fin on ne s'y retrouve plus entre les semaines calendaires et les fausses semaines qui sont des subdivisions de quinzaine !

Si tu veux relancer le débat autour d'un macro, je pense qu'il vaudrait mieux ouvrir un nouveau post.

Merci Steelson, je considère le sujet résolu et ouvre un nouveau topic pour l'optimisation du VBA.

Rechercher des sujets similaires à "ventilation heures"