SOMME.SI.ENS & plages de critères - NB.JOURS.OUVRES & dates de début/fin

[babap1]

Bonsoir,

j'essaye d'apporter de nouvelles fonctionnalités à une petit outil en calculant un taux d'occupation (travail planifié) à l'aide des formules SOMME.SI.ENS
L'idée est de calculer un pourcentage d'occupation d'une équipe, sur une période donnée, avec une date de début (cellule E20), un nombre de jour pour définir la période (cellule E21) et un critère d'absence qui peut se trouver dans la (colonne A) de mon tableau modélisé entre A3 et G13

J'aimerais ajouter une plage de critère pour que ma période soit prise en compte dans mes formules : =SOMME.SI.ENS(F3:F13;D3: D13;"Equipe 2";A3:A13;"<>")
J'aimerais aussi ne pas figer la fin de mes plages de critères par une ligne fixe mais plutôt par la dernière cellule non vide car le tableau est constamment incrémenté de nouvelles lignes.

En pièce jointe voici ce que j'ai commencé avec quelques commentaires.

Merci d'avance, Baptiste

 

[njhub]

Bonjour babap1,

Voyez si les modification apportées à votre fichier peuvent vous convenir

 

[Dugenou]

Bonjour,
Une petite amélioration sur le fichier de @njhub : on utilise la fonction décaler pour s'adapter à la longueur du tableau. Pour que ça reste lisible on nomme chaque formule (formules/gestionnaire de noms)
Cordialement

 

[babap1]

Merci à vous deux pour ces premières réponses.

Dans le fichier, il semblerait que les absences de l'équipe 4 (ligne 13) ne soient pas prises en compte sur la période du 13/10 au 28/10 puisque le nombre d'absence est 0.

ex : sur la ligne 13 du fichier, il y a 5 jours d'absence de renseignés dont 4 sur la période analysée et 0 calculé dans la cellule H2 (au lieu de 4). idem pour les jours planifiés qui se retrouvent à cheval sur la période analysée.

 

[Dugenou]

En effet, puisque la date de fin de l'arrêt est supérieure à la date de fin de période, le somme.si.ens n'en tient pas compte. On aura le même problème avec les arrêts qui commencent avant la période définie et les jours de travail. Je propose d'ajouter un calcul du nb de jours travaillés ou d'arrêt en fonction des dates de la période choisie et de faire le somme.si sur cette colonne.
Un essai en pj, je vous laisse vérifier
Cordialement

 

[babap1]

Un essai en pj, je vous laisse vérifier

Bonjour @Dugenou et merci pour l'avancée du fichier.
Je n'arrive pas à comprendre pourquoi sur la ligne 13 le nombre de jours sur période est de 3 au lieu de 4 sachant que le 28/10/2020 est bien un jour ouvré... sauriez-vous m'aiguiller ?
Merci d'avance

 

[Dugenou]

Votre date de fin de période est au 28/10 donc on doit déduire le 29. Ne sachant s'il fallait compter le jour de début et de fin comme inclus dans l'arrêt, je n'ai pas fait de correction dans la différence entre le la date d'arrêt et la date de fin (F13-(NB.JOURS.OUVRES($C$4;G13;JF_2))) : je pense qu'il faut corriger la formule avec +1 dans les deux parties.

Voir si c'est mieux dans le fichier joint

 

[babap1]

Voir si c'est mieux dans le fichier joint

Effectivement je n'avais pas précisé l'état des dates de début et fin... merci pour la correction.

Dans le fichier joint, j'ai noté encore un souci sur le "nombre de jours planifiés" et le "nombre de jour sur période"... si vous avez la gentillesse de jeter un nouvel oeil. Il me semble que cela est dû à la date de fin.

Merci d'avance, Baptiste

 

[Dugenou]

Bonjour,
Voir si c'est mieux, je ne suis pas très bon sur les dates et la prise en compte du jour début/fin
Cordialement

 

[babap1]

Bonsoir le forum, @Dugenou

je galère toujours avec mes dates de fin/début... j'arrive toujours à tomber sur un cas qui remet en cause ma formule
J'ai intégrer le code dans mon fichier final pour tenter d'y voir plus clair et toujours rien ! J'ai en permanence un "+1" ou "-1" qui se balade..

=SI(ET(G31<>"";$H$2>0);SI(OU(G31>$E$2;I31<$D$2);0;SI(ET(G31>=$D$2;I31<=$E$2);H31;SI(G31<=$D$2;SI(I31<=$E$2;H31-(NB.JOURS.OUVRES(G31;$D$2;JF)-1);H31-(NB.JOURS.OUVRES(G31;$D$2;JF)-1)-(NB.JOURS.OUVRES($E$2;I31;JF)-1));H31-(NB.JOURS.OUVRES($E$2;I31;JF)-1))));0)

Voici en PJ le fichier compilé.
Si quelqu'un a la gentillesse de jeter un coup d'oeil car je m'arrache les cheveux..

Merci d'avance, Baptiste

 

[babap1]

EUREKA ! Je m'en suis sorti grâce à la fonction SI.MULTIPLE() car les SI imbriqués étaient autant indigestes à lire qu'à contrôler...

=SI.CONDITIONS(G30="";0;OU(I30<$D$2;G30>$E$2);0;ET(G30>=$D$2;I30<=$E$2);H30;ET(G30<=$D$2;I30>=$E$2);$H$2;ET(G30<=$D$2;I30<=$E$2);H30-NB.JOURS.OUVRES(G30;$D$2-1;JF);ET(I30>=$E$2;G30>=$D$2);H30-NB.JOURS.OUVRES($E$2+1;I30;JF))

Merci à tous pour vos bons conseils. @Dugenou @njhub et ceux qui se seraient pencher sur le sujet.