Microsoft 365 Décompte du nombre de présents par plage horaire

[MUGMRG]

Bonjour,

J'essaye de faire quelque chose que je pensais simple, mais qui s'avère compliqué sans doute parce que je m'y prends mal.
J'ai un fichier avec les heures d'arrivée et de départ des salariés (mais avec une particularité, c'est qu'ils peuvent faire du télétravail par demi journée, donc il peuvent être présent sur site (sur site), ou chez eux (TT) pour cela).
Pour déterminer les présences, j'ai repris des tranches de 30 minutes tout au long de la journée, et j'essaye (vainement) de déterminer combien sont présents.
Le résultat que je souhaite obtenir pour la ligne 7 (mercredi 2 novembre) figure en fluo dans le tableau en ligne 6.

J'ai tenté de rentrer une formule du genre dans les cellules A7:X7 mais en vain :

=SOMMEPROD(($AB7:$AU7>A$5)*($AB7:$AU7<=A$4))

Mais je suis loin d'obtenir le résultat attendu.

Quelqu'un voit ce que je ne vois pas ?

Bonne journée,

 

[Wayki]

Bonjour,
En A7 :

=SOMMEPROD(($AB7:$AU7<>0)*($AB7:$AU7<=A$5)*($AB7:$AU7<=A$4))

À étirer vers la droite.
Mais ça disjoncte à un moment, je pense qu'il faut revoir la méthode ou la façon de travailler.
A +

 

[job75]

Bonsoir MUGMRG, bienvenue sur XLD, bonsoir Wayki,

Voyez le fichier joint et cette formule en A7 :

=(MIN(A$4;N($AC7))-MAX(A$5;$AB7)>1/10000)+(MIN(A$4;N($AG7))-MAX(A$5;$AF7)>1/10000)+(MIN(A$4;N($AK7))-MAX(A$5;$AJ7)>1/10000)+(MIN(A$4;N($AO7))-MAX(A$5;$AN7)>1/10000)+(MIN(A$4;N($AS7))-MAX(A$5;$AR7)>1/10000)

à tirer à droite et vers le bas.

Il y a beaucoup d'erreurs dans les résultats attendus en jaune !!!

Explication : MIN (xxx)-MAX(yyy) détermine la largeur de chaque intersection de plages horaires.

A+

 

[TooFatBoy]

Bonjour,

Il y a beaucoup d'erreurs dans les résultats attendus en jaune !!!

C'est parce que tu as oublié de compter les heures de télétravail.
Du coup, il n'y a pas tant d'erreurs que ça : 10:00 à 10:30 et 15:00 à 15:30.


Une proposition en pièce jointe.

 

[mapomme]

Bonjour à tous,

Que doit-on faire si la période de travail du salarié est 14h00 <=> 15h15 et que nous voulions la présence sur la plage 15h00 <-> 15h30 ? On compte 0 (car il ne travaille pas sur la plage entière), on compte 1 (car il a un peu travaillé sur la plage) ou bien 0,5 (car il a travaillé la moitié de la plage) ou encore autre chose ?

 

[mapomme]

Re,

Une version réservée à Office 365. On a considéré qu'un salarié qui travaille même partiellement au sein d'une plage horaire sera compté pour 1 pour cette plage horaire.

• La plage A5:X5 a été nommée DEB
• La plage A4:X4 a été nommée FIN
• La plage courante des horaires a été nomme Plage via le LET (dans la formule)
• Le numéro de la colonne de début des horaires a été nommé PREM via le LET (dans la formule)
• La formule en A7 est à recopier vers la droite et vers le bas (ou le haut)
• Si on ajoute ou ôte des salariés, il suffit dans la formule de modifier la référence de Plage dans le LET (dans la formule)
• Les périodes de télé-travail sont prises en compte

Formule en A7 :

=LET(Plage;$AB7:$AU7;Prem;COLONNE($AB:$AB);NB(Plage)/2-SOMME(--SI(MOD(COLONNE(Plage)-Prem;2)=0;SI(Plage<>"";Plage>=INDEX(Fin;COLONNES($A:A));0);0)) - SOMME(--SI(MOD(COLONNE(Plage)-Prem;2)=1;SI(Plage<>"";Plage<=INDEX(Deb;COLONNES($A:A));0);0)))

 

[mapomme]

Re,

Même chose que dans le précédent message mais adapté à toutes les versions d'Excel.
Les formules sont matricielles.

• La plage A5:X5 a été nommée DEB
• La plage A4:X4 a été nommée FIN
• La formule en A7 est à recopier vers la droite et vers le bas (ou le haut)
• Les périodes de télé-travail sont prises en compte

Formule en A7 à recopier vers la droite et vers le bas (ou le haut) :

=NB($AB7:$AU7)/2-SOMME(--SI(MOD(COLONNE($AB7:$AU7)-COLONNE($AB7);2)=0;SI($AB7:$AU7<>"";$AB7:$AU7>=INDEX(FIN;COLONNES($A:A));0);0)) - SOMME(--SI(MOD(COLONNE($AB7:$AU7)-COLONNE($AB7);2)=1;SI($AB7:$AU7<>"";$AB7:$AU7<=INDEX(DEB;COLONNES($A:A));0);0))

 

[MUGMRG]

Bonjour à tous,

Que doit-on faire si la période de travail du salarié est 14h00 <=> 15h15 et que nous voulions la présence sur la plage 15h00 <-> 15h30 ? On compte 0 (car il ne travaille pas sur la plage entière), on compte 1 (car il a un peu travaillé sur la plage) ou bien 0,5 (car il a travaillé la moitié de la plage) ou encore autre chose ?

Bonjour mapomme,

Effectivement ça soulève une question.

Pragmatiquement, je vais estimer que j'ai quelqu'un, même si ce n'est pas pour le shift de 00:30 complet.

Merci

 

[MUGMRG]

Bonsoir MUGMRG, bienvenue sur XLD, bonsoir Wayki,

Voyez le fichier joint et cette formule en A7 :

=(MIN(A$4;N($AC7))-MAX(A$5;$AB7)>1/10000)+(MIN(A$4;N($AG7))-MAX(A$5;$AF7)>1/10000)+(MIN(A$4;N($AK7))-MAX(A$5;$AJ7)>1/10000)+(MIN(A$4;N($AO7))-MAX(A$5;$AN7)>1/10000)+(MIN(A$4;N($AS7))-MAX(A$5;$AR7)>1/10000)

à tirer à droite et vers le bas.

Il y a beaucoup d'erreurs dans les résultats attendus en jaune !!!

Explication : MIN (xxx)-MAX(yyy) détermine la largeur de chaque intersection de plages horaires.

A+

Whoa merci job75 pour cette formule.

Elle est nettement plus alambiquée que ce que je pensais nécessaire de devoir faire.
Et va nécessiter bcp d'adaptation.
Il y a cependant un point que vous semblez tous avoir traité à part, c'est celui des TT.
Je ne cherche pas à connaître mon effectif sur site, ce que chacun semble avoir tenté de faire (merci).
Je cherche à déterminer mes effectifs, peu importe qu'ils soient en TT ou présents sur site.
Le titre "Présence effectif" y est sans doute pour beaucoup.
Désolé de la confusion.

Bonne journée,

 

[MUGMRG]

Bonjour,


C'est parce que tu as oublié de compter les heures de télétravail.
Du coup, il n'y a pas tant d'erreurs que ça : 10:00 à 10:30 et 15:00 à 15:30.


Une proposition en pièce jointe.

Bonjour TooFatBoy,

Merci pour cette réponse.
C'est une version pragmatique à laquelle je n'avais pas songé.
Opérationnellement par contre, cette formule va prendre 15km supplementaire au fur et à mesure que je vais ajouter des salariés.
Bien qu'elle fonctionne, je cherche sans doute une version plus élégante qui nécessite de mettre moins les mains dans le camboui en cas de modification.

Merci pour ton éclairage en tout cas !

 

[TooFatBoy]

Il y a cependant un point que vous semblez tous avoir traité à part, c'est celui des TT.

Hein !?! Mais quoi qu'y dit lui ???

Bien qu'elle fonctionne, je cherche sans doute une version plus élégante qui nécessite de mettre moins les mains dans le camboui en cas de modification.

 

[MUGMRG]

Re,

Une version réservée à Office 365. On a considéré qu'un salarié qui travaille même partiellement au sein d'une plage horaire sera compté pour cette plage horaire.

• La plage A5:X5 a été nommée DEB
• La plage A4:X4 a été nommée FIN
• La plage courante des horaires a été nomme Plage via le LET (dans la formule)
• Le numéro de la colonne de début des horaires a été nommé PREM via le LET (dans la formule)
• La formule en A7 est à recopier vers la droite et vers le bas (ou le haut)
• Si on ajoute ou ôte des salariés, il suffit dans la formule de modifier la référence de Plage dans le LET (dans la formule)
• Les périodes de télé-travail sont prises en compte

Formule en A7 :

=LET(Plage;$AB7:$AU7;Prem;COLONNE($AB:$AB);NB(Plage)/2-SOMME(--SI(MOD(COLONNE(Plage)-Prem;2)=0;SI(Plage<>"";Plage>=INDEX(Fin;COLONNES($A:A));0);0)) - SOMME(--SI(MOD(COLONNE(Plage)-Prem;2)=1;SI(Plage<>"";Plage<=INDEX(Deb;COLONNES($A:A));0);0)))

Merci Mapomme,

Effectivement c'est une formule qui me va bien.
Maintenance facilitée si on ajoute des salariés. C'est un plus.
Je ne comprends pas la partie avec la sélection de la colonne (la première uniquement) : COLONNE($AB:$AB).
C'est la seule partie qui relève de la magie pour moi.
Je ne connais pas du tout la fonction LET. Jamais utilisée. Une nouveauté Office365.
Et d'après la doc cela accélère même le traitement pour les calculs qui reprennent les mêmes valeurs.

Merci !

 

[TooFatBoy]

Et d'après la doc cela accélère même le traitement pour les calculs qui reprennent les mêmes valeurs.

C'est logique puisque ladite valeur n'est plus calculée qu'une seule fois.

En gros, LET permet d'utiliser des "variables" dans les formules.

 

[mapomme]

Re,

Je ne comprends pas la partie avec la sélection de la colonne (la première uniquement) : COLONNE($AB:$AB).

En gros dans la formule, on traite d'une part le début des plages horaires puis la fin plages horaires.

Pour avoir le début des plages travaillées, on teste si les numéros des colonnes des plages travaillées ont le même reste que la division par 2 que la première colonne.

Exemple :

• si la première colonne est 28 (colonne AB), le numéro de colonne de début de chaque plage travaillée est 28, 30, 32, 34, ...
• Donc la différence de ces colonnes avec 28 est : 0, 2, 4, 6, ...
• Donc le reste de la division par 2 de ces différences est toujours 0.
• donc pour l'ensemble des colonnes des plages travaillés, on sait qu'elles correspondent à un début si : MOD(COLONNE(Plage)-Prem;2)=0
• Il nous faut donc connaitre Prem (le numéro de la première colonne des horaires travaillés).

Il nous faut donc le numéro Prem de la colonne de début des plages travaillées. Deux cas possibles :

1. On nomme "en dur" ce numéro via la gestion des noms [Prem fait référence =COLONNE($AB:$AB)] qui donne bien le numéro de la colonne AB.
2. ou bien, on nomme ce numéro directement dans la fonction LET (c'est ce qui a été choisi)

Un raisonnement analogue permet de repérer les colonnes de fin des plages travaillées. Mais pour ce cas, la différence entre les numéros des colonnes de fin et la colonne de début modulo 2 est 1 (et non pas 0).


A ce stade, on sait repérer chaque heure de début des plages travaillées :

SI(MOD(COLONNE(Plage)-Prem;2)=0; ...

On teste si elles ne sont pas vides :

• Si elles sont vides, on retourne 0
• Si elles sont non vides, alors on teste si elles sont supérieures à l'heure de fin des plages de références (plage d'une demi-heure); Si oui, on retourne 1, si no, on retourne 0

• SI(MOD(COLONNE(Plage)-Prem;2)=0;SI(Plage<>"";Plage>=INDEX(Fin;COLONNES($A:A));0);0)

• précision : INDEX(Fin;COLONNES($A:A) nous donne en fonction de la colonne où est la formule, l'index de l'horaire de fin des plages d'une demi-heure à considérer

En résumé si pour une plage travaillée, l'heure de début est supérieure à la fin de la plage d'une demi-heure, alors le salarié a été absent sur cette plage d'une demi-heure.
On a donc compté somme(--( ... ) ) le nombre de plages d'une demi-heure où le salarié n'a pas travaillé parce que le début de la plage travaillée est après la plage d'une demi-heure.

SOMME(--SI(MOD(COLONNE(Plage)-Prem;2)=0;SI(Plage<>"";Plage>=INDEX(Fin;COLONNES($A:A));0);0))

On fait un raisonnement similaire avec les horaires de fin des plages travaillées.

A ce stade, on a donc le nombre de plages travaillées qui ne chevauchent aucune des plages d'une demi-heure (le salarié a été complètement absent sur ces plages d'une demi-heure).

Ce qu'on recherche, c'est exactement le contraire. Donc on retire ce nombre du nombre total de plages travaillées qui est :

NB(Plage)/2

 

[job75]

Bonjour MUGMRG, Wayky, TooFatBoy, mapomme,

Oui je n'avais pas tenu compte des plages avec TT.

Pour éviter d'avoir des formules trop longues on peut utiliser cette fonction VBA :

Function Presence%(deb#, fin#, r As Range)
For Each r In r.Areas 'zones disjointes
    Presence = Presence - (IIf(fin < r(1, 2), fin, r(1, 2)) - IIf(deb > r(1), deb, r(1)) > 1 / 10000) 'True => -1
Next
End Function

Elle fait le travail MIN-MAX de mon post #3.

Le code doit être placé impérativement dans un module standard.

La formule en A7 :

=Presence(A$5;A$4;($AB7:$AC7;$AF7:$AG7;$AJ7:$AK7;$AN7:$AO7;$AR7:$AS7))+Presence(A$5;A$4;($AD7:$AE7;$AH7:$AI7;$AL7:$AM7;$AP7:$AQ7;$AT7:$AU7))

A+