XL 2010 Planification : Optimisation formule Excel

Saxophone

XLDnaute Nouveau
Bonjour à toute la communauté,

Je cherche à optimiser un outil de planification qui a comme fonction de ventiler des heures entre 2 dates en prenant en compte la disponibilité de la ressource concernée.
Je vous transmet ci-joint la maquette du fichier.
La formule permettant la ventilation des heures est la suivante : (Celle-ci se situe entre les colonnes DP et HG de l'onglet "Liste_projets")

Code:
=SIERREUR(SI(ET(DP$1>=RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX);DP$1<=RECHERCHEV($S4;Calendrier!$C$3:$I$1464;5;FAUX));
($N4*((RECHERCHEV($S4;Calendrier!$C$3:$I$1464;5;FAUX)-RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX)+1)*(RECHERCHEV($E4;Congés!$B$3:$D$42;3;FAUX)-RECHERCHEV($E4;Congés!$B$3:$D$42;3;FAUX)*RECHERCHEH(Congés!F$1;Congés!$B$1:$CW$42;EQUIV($E4;Congés!$B$3:$B$42;0)+2;FAUX))
/SOMMEPROD((ChoixRessource=$E4)*(Congés!$CY$1:$GP$1>=RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX))*(Congés!$CY$1:$GP$1<=RECHERCHEV($S4;Calendrier!$C$3:$I$1464;5;FAUX))*(Congés!$CY$3:$GP$42))))
*((SOMMEPROD((ChoixRessource=$E4)*(Congés!$CY$3:$GP$42)*(Congés!$CY$1:$GP$1=DP$1)))
/(RECHERCHEV($E4;Congés!$B$3:$D$42;3;FAUX)-RECHERCHEV($E4;Congés!$B$3:$D$42;3;FAUX)*RECHERCHEH(Congés!F$1;Congés!$B$1:$GP$42;EQUIV($E4;Congés!$B$3:$B$42;0)+2;FAUX)))/((RECHERCHEV($S4;Calendrier!$C$3:$I$1464;5;FAUX)-RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX)+1));0);0)

Le hic c'est que le fichier se comporte très bien sur quelques dizaines de lignes... mais une fois que l'on a dépassé un certains nombre de projet (et donc autant de recopie de ligne contenant cette formule), le fichier devient lent à l'ouverture et il plante si l'on touche à la structure du fichier (ajout ou suppression de ligne dans l'onglet voir même création d'onglet impossible dans le fichier).
J'ai appris (après coup) le caractère "volatile" de certaines fonctions... et les problèmes que cela peut poser.

Est-ce qu'il y aurait possibilité d'optimiser la formule pour palier à ces problèmes de lenteur si celle-ci est recopiée sur 1500 lignes environ ?

En espérant que ma demande est assez détaillée, je reste évidemment disponible pour répondre à toute question.

Vous remerciant par avance.

Saxo.
 

Pièces jointes

  • HELP_Maquette_Outil Planif.xlsx
    303.7 KB · Affichages: 23

merinos

XLDnaute Accro
Salut @Saxophone ,

Je regarde ton fichier...

Étant donné l'architecture générale des données, ceci ne sera jamais performant.

C'est tellement compliqué... que j'ai renoncé après 10 minutes a comprendre ce que fais le fichier...
Surtout qu'il n'y a quasi pas d'info sur ce que font les colonnes.

Désolé,

Merinos
 

eriiic

XLDnaute Barbatruc
Bonjour,

Tu ne dis même pas où elle est ta formule.
Et si tu pouvais décrire ce qu'elle est sensée faire plutôt qu'on se prenne la tête à essayer de deviner (peut-être mal) à partir de la formule.

Accessoirement
VB:
=SI(NO.SEMAINE(C3;2)=53;52;NO.SEMAINE(C3;2))
est faux.
Utilise plutôt :
Code:
=NO.SEMAINE(C3;21)
qui te retourne le n° de semaine ISO
eric
 

Saxophone

XLDnaute Nouveau
Bonjour,
Merci pour ces retours. J'ai bien conscience d'avoir été léger sur ma demande... corrigeons tout cela :)
Le fichier contient 4 onglets :
-Calendrier qui permet à partir d'une date de déterminer la quinzaine de l'année
On part sur la simplification qu'une année comporte 48 semaines, 4 semaines par mois, 2 quinzaines par mois.

-Paramètres qui sert à indiquer l'année en cours pour que toutes les colonnes se mettent à jour (cellule A3)

-Congés où se trouve la liste des personnes et leur volume horaire de disponibilité
Une personne (colonne B), se voit attribué des heures de disponibilité (colonne D)
De F à CW on peut indiquer par un coefficient d'absence si la ressource est en congé (si on rempli 1 cela signifie que la personne est absente 1 quinzaines)
De CY à GP il s'agit de : heures dispos-(coeff absence*heures dispo)
De GR à IM : il s'agit simplement de ramener les heures de dispo en prenant compte du coefficient d'absence à la maille mensuelle (d'où le somme décaler)

-Liste projet : cet onglet fait appel à l'onglet Calendrier et l'onglet congés
Colonnes importantes de l'onglet "liste projets"
. colonne E : personne en charge
. colonne N : volumes d'heures à ventiler
. colonne O : date de début de la tâche
. colonne S : date de fin de la tâche

Les cellules contenant la formule de ventilation se situent de la colonne DP à HG.
De DP1 à HG1, chaque colonne à un numéro de quinzaine. De 2 à 192.

Positionnons nous en cellule DP4 :
Exemple de 240h réparties entre le 01/01/2019 et le 31/12/2019 pour une ressource disponible à 100% 76h par quinzaine (ou 152h par mois)
La formule teste la quinzaine indiquée en DP1 par rapport à ce que renvoie la date de début et de fin dans l'onglet calendrier :
SI(ET(DP$1>=RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX);DP$1<=RECHERCHEV($S4;Calendrier!$C$3:$I$1464;5;FAUX)

Si Valeur Vrai :
($N4*
--> [240 h=Volume d'heures de la tâche à répartir]

((RECHERCHEV($S4;Calendrier!$C$3:$I$1464;5;FAUX)-RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX)+1)*(RECHERCHEV($E4;Congés!$B$3:$D$42;3;FAUX)-RECHERCHEV($E4;Congés!$B$3:$D$42;3;FAUX)*RECHERCHEH(Congés!F$1;Congés!$B$1:$CW$42;EQUIV($E4;Congés!$B$3:$B$42;0)+2;FAUX))
--> [47*76= 3572h : nombre de quinzaine entre les dates de début et de fin*Volume d'heures de dispo par quinzaine de la ressource]

/SOMMEPROD((ChoixRessource=$E4)*(Congés!$CY$1:$GP$1>=RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX))*(Congés!$CY$1:$GP$1<=RECHERCHEV($S4;Calendrier!$C$3:$I$1464;5;FAUX))*(Congés!$CY$3:$GP$42))))
56544
*((SOMMEPROD((ChoixRessource=$E4)*(Congés!$CY$3:$GP$42)*(Congés!$CY$1:$GP$1=DP$1)))
2356
--> LE comportement est le suivant : Va chercher le volume de dispo de la personne (E4=76h) puis calcule le volume théorique max de dispo de la ressource sur 4 ans (2019-2022, 192 quinzaines) ramené à la dispo de la personne sur la plage délimitée par la date de début et de fin. Evidemment à interprêter en "matrice" d'où le 2356/56544...

/(RECHERCHEV($E4;Congés!$B$3:$D$42;3;FAUX)-RECHERCHEV($E4;Congés!$B$3:$D$42;3;FAUX)*RECHERCHEH(Congés!F$1;Congés!$B$1:$GP$42;EQUIV($E4;Congés!$B$3:$B$42;0)+2;FAUX)))
-->76 Division par le taux de dispo de la personne la quinzaine en cours (76h-76h*1)

/((RECHERCHEV($S4;Calendrier!$C$3:$I$1464;5;FAUX)-RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX)+1))
-->47 Division par le nombre de quinzaine entre la date de fin et de début (donc multiplication avec 76)

En résumé :
240 * 3572 * (2356/56544) / (76*47)=10

Résultat 10h par quinzaine.
Si la ressource avait été absente sur la période de début et de fin, alors le calcul aurait reventilé les heures sur les autres plages où la ressource est dispo pour bien diviser le volume d'heures à répartir.

En espérant avoir été plus clair sur le fonctionnement du fichier et de la formule.
S'il y a encore des zones d'ombre n'hésitez pas. Merci pour le premier coup d'oeil ;)
Cordialement.
 
Dernière édition:

Saxophone

XLDnaute Nouveau
Au travers de RECHERCHEV($O4;Calendrier!$C$3:$I$1464;5;FAUX), on va chercher d'après la date renseignée par l'utilisateur (ici la date de début), la "quinzaine" associée.
1ère moitié de Janvier 2019 = 2
2ème moitié de Janvier 2019 = 4
1ère moitié de Janvier 2020 = 50
2ème moitié de Janvier 2020 = 52
Etc
Le découpage réalisé est : 4 semaines par mois ou 2 bloc de 15 jours par mois (appelés "quinzaine").

La colonne G dans "Calendrier", donne la "quinzaine" associée à une date sur une période de 4 ans.
C'est ce qui nous absout des problèmes de changement d'année justement :)
 

Saxophone

XLDnaute Nouveau
Je me permets de répondre à la suite.
Je fournis un xl simplifié où j'ai tout regroupé sur le même onglet avec un code couleur pour appréhender le fonctionnement de la formule.
Je vous remercie par avance pour l'aide précieuse que vous pourrez m'apporter.

EDIT : @merinos Avec ce nouveau fichier, est-ce que la problématique est plus claire ?

EDIT 2 : Le fichier avait une erreur dans une formule. La bonne version est jointe.
Cordialement.
 

Pièces jointes

  • HELP_Maquette_Outil Planif_simplifié.xlsx
    116.6 KB · Affichages: 13
  • Ventilation heures.xlsx
    117.4 KB · Affichages: 19
Dernière édition:

Discussions similaires

Réponses
14
Affichages
621

Statistiques des forums

Discussions
311 723
Messages
2 081 934
Membres
101 844
dernier inscrit
pktla