Calcul des heures d'un planning par semaine

[turbule]

Bonjour,

J'aurais besoin d'un coup de main pour finir un fichier où je souhaite calculer les heures effectués par semaine pour chaque personne.

Le numéro de semaine se trouve en dessous des dates. Si possible, je souhaiterais calculer les heures effectués en une seule formule sans passer par des colonnes intermédiaires.

Je pourrais me servir facilement de Sommeprod si c'étais des nombres que je souhaitais additionner mais je en trouve pas la solution avec les heures.

Merci d'avance à toutes les personne qui se pencheront sur le problème.

Bonne soirée à tous

 

[mapomme]

Re : Calcul des heures d'un planning par semaine

Bonsoir turbule,

Un essai dans le fichier joint.

Saisir en B47 la formule matricielle suivante (puis la tirer/copier vers la droite et vers le bas):

=SOMME((DECALER($C$1:$AU$1;EQUIV($A47;$A$1:$A$27;0)-1;0)-DECALER($B$1:$AT$1;EQUIV($A47;$A$1:$A$27;0)-1;0))*(MOD(COLONNE($C$1:$AU$1);2)=1)*($B$6:$AT$6=B$46))

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

 

[turbule]

Re : Calcul des heures d'un planning par semaine

Bonjour Mapomme,

Merci beaucoup pour cette formule qui fonctionne à merveille. Sans vouloir abuser, je pense avoir compris les 3/4 de la formule. sauf une partie :

(MOD(COLONNE($C$1:$AU$1);2)=1)

Ce code renvoie VRAI dans tous les cas soit en matriciel la valeur 1 si je l'enlève la formule fonctionne malgré tout. Du coup, je souhaiterai comprendre dans quels objectifs tu as placé ce bout de code ?

Merci d'avance

Cordialement

Turbule

 

[mapomme]

Re : Calcul des heures d'un planning par semaine

Bonsoir turbule,

Sans vouloir abuser, je pense avoir compris les 3/4 de la formule. sauf une partie :

(MOD(COLONNE($C$1:$AU$1);2)=1)

Ce code renvoie VRAI dans tous les cas soit en matriciel la valeur 1 si je l'enlève la formule fonctionne malgré tout. Du coup, je souhaiterai comprendre dans quels objectifs tu as placé ce bout de code ? (...)

Le principe (pour une ligne donnée ex: ligne 7 , formule de la cellule B47) est de faire les différences des heures entre chaque colonne (je passe sur le decaler / equiv pour simplifier)

On calcule donc la matrice:
(c7-b7; d7-c7; e7-d7 ; f7-e7; g7-f7; ... ; au7-at7)

Parmi ces valeurs seules les différences des colonnes c-d, e-d, g-f, ... , au-at sont à considérer (heures figurant dans la même journée), les autres faisant la différence entre l'heure du matin et l'heure du soir de la veille sont à éliminer.

Pour les éliminer, il suffit de ne prendre en compte que les différences commençant en colonnes c, e, g, ... au. Ces colonnes sont les colonnes de numéro impair. Pour déterminer si une colonne est impaire, j'utilise la division modulo 2 d'un nombre qui renvoie 1 si le nombre est impair. On trouve la formule (matricielle dans notre cas): MOD(COLONNE($C$1:$AU$1);2)=1

On arrive à la formule matricielle:
=SOMME((DECALER($C$1:$AU$1;EQUIV($A47;$A$1:$A$27;0)-1;0)-DECALER($B$1:$AT$1;EQUIV($A47;$A$1:$A$27;0)-1;0))*(MOD(COLONNE($C$1:$AU$1);2)=1))

A ce stade, dans le tableau des résultats, la formule donne les heures travaillées sur le mois entier (faites le test sur votre exemple, pour XXXX on trouve 37:00)

Si vous ôtez de cette formule le terme contenant MOD, la formule devient:
SOMME((DECALER($C$1:$AU$1;EQUIV($A47;$A$1:$A$27;0)-1;0)-DECALER($B$1:$AT$1;EQUIV($A47;$A$1:$A$27;0)-1;0))) et le résultat ne donne pas 37:00.

J'ai ensuite rajouté la condition de semaine: *($B$6:$AT$6=B$46) pour aboutir à la formule matricielle finale: =SOMME((DECALER($C$1:$AU$1;EQUIV($A47;$A$1:$A$27;0)-1;0)-DECALER($B$1:$AT$1;EQUIV($A47;$A$1:$A$27;0)-1;0))*(MOD(COLONNE($C$1:$AU$1);2)=1)*($B$6:$AT$6=B$46))

Votre remarque est pertinente:
En effet la condition $B$6:$AT$6=B$46 donne un résultat positif si:
b6=b46, c6=b46, d6=b46, e6=b46, f6=b46, g6=b46, ... , at=b46

Or seules b6, d6, f6, ..., at6 contiennent une valeur de n° de semaine.
Les cellules (ça ne se voit pas à cause de la fusion de cellule) c6, e6, g6 sont vides. Donc dans la formule finale, on ne prendra en compte que les différences des heures correspondantes aux colonnes b6, d6, f6, h6, ... dans l'expression de la condition ($B$6:$AT$6=B$46) (à condition bien sûr que leur valeur soit égale à B46).

Or dans la formule matricielle complète, la colonne b de la condition correspond à la colonne c de (MOD(COLONNE($C$1:$AU$1);2)=1), la colonne d correspond à la colonne e de (MOD(COLONNE($C$1:$AU$1);2)=1), ...

On s'aperçoit donc que les termes intéressants de la condition $B$6:$AT$6=B$46 correspondent aux colonnes impaires de (MOD(COLONNE($C$1:$AU$1);2)=1)

Autrement dit la condition sur la semaine implique la condition sur le modulo. On peut donc se passer du terme MOD.

Mais comme je l'ai montré plus haut, si on n'a pas de condition sur la semaine, le terme modulo est indispensable.

 

[turbule]

Re : Calcul des heures d'un planning par semaine

Bonjour,

Merci pour ta réponse complète et d'avoir pris le temps ce qui m'a permis de mieux comprendre une utilisation possible du modulo.