calcul de cumul d' heure selon criteres

[remy8966]

bonjour
j'ai besoin d'aide
je dois monter un fichier ou environ 60 personnes doivent s'inscrire pour de l'astreinte sous forme date début/heure de début et date fin/heure de fin
je dois ressortir le volume horaire par date en jour et en nuit selon des créneaux horaires

merci de votre aide

 

[CISCO]

Re : calcul de cumul d' heure selon criteres

Bonjour

Une possibilité en pièce jointe avec un tableau intermédiaire.

Il faut copier toute la liste de tes intervenants et la coller, en la transposant, en M11.

Il y a une formule, pour le jour, en M12, et une autre pour la nuit, en M13. Ces formules peuvent être tirées vers la droite aussi longtemps que désiré, pour les autres intervenants.

Pour obtenir ces résultats pour plus de dates, il suffit de copier ce pavé de 2 lignes, et de le coller en M14, M16 et ainsi de suite.

@ plus

P.S 1 : On doit pouvoir faire plus simple pour les nuits (en simplifiant, parce que h nuit = 24 - h jour), en prenant certaines précautions.
P.S 2 : On doit aussi pouvoir faire sans tableau intermédiaire, mais pour le moment...

 

[CISCO]

Re : calcul de cumul d' heure selon criteres

Bonsoir

Simplification de la formule concernant les heures de nuit en M13, N13, M15 et N15. On peut toujours la tirer vers la droite.

@ plus

 

[remy8966]

Re : calcul de cumul d' heure selon criteres

un grand merci pour cette solution que je n'aurais jamais pu formuler!!
après faut juste que ca fonctionne aprés le comment et pourquoi je devrais y laisser quelques heures
je met ca en application
encore merçi

 

[CISCO]

Re : calcul de cumul d' heure selon criteres

Bonjour

Une possibilité sans tableau intermédiaire.

Pour comprendre comment cela fonctionne, il faut étudier la formule matricielle (à valider avec Ctrl+maj tempo+entrer) la plus simple, celle pour les heures de jour, en K12 par exemple :

SOMME(SI((fintravail<=débutjour)+(finjour<=débuttravail);0;SI(fintravail<finjour;fintravail;finjour)-SI(débuttravail>débutjour;débuttravail;débutjour)))

avec
débutjour =$H12+$H$3 (donc jour en cours + 8:00)
finjour =$H12+$I$3 (donc jour en cours + 18:00)
débuttravail =$B$4:$B$5+$C$4:$C$5 (formule en K12 en matriciel donc pour prendre en compte toutes les lignes utilisées par ce nom)
fintravail =$D$4:$D$5+$E$4:$E$5 (formule en K12 en matriciel donc pour prendre en compte toutes les lignes utilisées par ce nom)


SI((fintravail<=débutjour) signifie "Si la fin du travail a lieu avant le début du jour en cours" (jour en cours + 8:00 dans l'exemple), on ne compte rien, donc 0.
+(finjour<=débuttravail) signifie "Ou si le début du travail a lieu après la fin du jour en cours" (jour en cours + 18:00 dans l'exemple), 0.
Sinon on fait la différence MIN(fin du travail;fin du jour) - MAX(début du jour; début du travail). Malheureusement, comme les fonctions MAX et MIN ne fonctionnent pas correctement dans le cas présent (elles gardent en mémoire une seule valeur, le max ou le min de toute la "liste", alors qu'on veut qu'elles gardent plusieurs valeurs car on travaille en matriciel), on fait cette dernière partie avec SI(fin du travail <fin du jour;fin du travail;fin du jour)-SI(début du travail>début du jour;début du travail;début du jour).

On fait la SOMME en matriciel de tout cela pour avoir le résultat de l'ensemble des intervenants.

En K13, dans astreinte 4, pour la nuit, on procède de même, avec la même méthode, mais en considérant d'abord la nuit le matin de 00:00 à 8:00 (de zéroheure à finnuitmatin)

SOMME(SI((fintravail<=zéroheure)+(finnuitmatin<=débuttravail);0;SI(fintravail<finnuitmatin;fintravail;finnuitmatin)-SI(débuttravail>zéroheure;débuttravail;zéroheure)))+

auquel on ajoute les heures comprises le soir entre 18:00 à 24:00 (entre débutnuitsoir et minuit).

SOMME(SI((fintravail<=débutnuitsoir)+(minuit<=débuttravail);0;SI(fintravail<minuit;fintravail;minuit)-SI(débuttravail>débutnuitsoir;débuttravail;débutnuitsoir)))

La aussi on fait la SOMME en matriciel pour prendre en compte toutes les lignes du tableau du haut.

Comme dans les fichiers précédents, si on veut les résultats pour d'autres dates, il faut copier K12:K13 et coller ce pavé au bon endroit en dessous, dans la colonne K.

Comme dans ton fichier réel tu as plus d'intervenants, il suffit de modifier les noms débuttravail= $B$4:$B$5+$C$4:$C$5 et fintravail= $D$4:$D$5+$E$4:$E$5 (qui ne prennent en compte que deux lignes, donc deux intervenants) dans le gestionnaire de noms en remplaçant les 5 par le n° de la dernière ligne correspondant au dernier intervenant (ou par un nombre plus grand).

En pratique, comme on a les égalités suivantes, en K13 par exemple :
zéroheure=$H13
finnuitmatin=débutjour
débutnuitsoir=finjour
minuit=$H13+1

on peut simplifier la formule en K13 donnant les heures de nuit en n'utilisant que $H13, débutjour, finjour, débuttravail et finttravail (et pas zéroheure, ni finnuitmatin, ni débutnuitsoir, ni minuit), ce qui donne la formule en L13.

Une fois le gestionnaire de noms nettoyé, cela donne le fichier astreinte 5 ci-dessous.

A vérifier bien sûr.

@ plus