XL 2013 Courbe gaussienne avec condition

[Exl-Noob]

Bonjour,

Voilà la sujet. J'ai récupéré les pointages entrants et sortant du personnel.
Je souhaite avoir par mois, une courbe gaussienne qui indique le cumul d'effectif tout au long de la journée (heures en abscisse + effectif en ordonné).
Ainsi je pourrais savoir à peu de chose près à partir de quelle heure je peux compter sur la présence d'un effectif minimal, ou à partir de quel heure j'atteint un seuil maximal de présence.
Dans mon idée, je pourrais me contenter d'un graphique croisé dynamique avec une chronologie pour afficher mes variations mensuelles.

Les salariés peuvent pointer 4 fois, ou 2 fois, selon s'ils font une journée complète ou une demi-journée.
Et c'est là que je bloque.
J'ai ajouté une colonne calculée pour déterminer à quel pointage de la journée on en est à l'aide de la formule :

=NB.SI.ENS([Mat.];[@[Mat.]];[Fin];"="&[@Fin];[HrSaisie];"<="&[@HrSaisie])

Le pointage 1 et 3 augmentent donc les effectifs à un instant T.
Le pointage 2 et 4 réduisent donc les effectifs à un instant T.

Sauf qu'ensuite je ne vois pas comment faire mon TCD (ou toute autre solution), pour augmenter/réduire mon effectif avec ces conditions, et illustrer le résultat dans un graphique.

J'ai inclus 10.000 lignes de mon export pour faire des tests. Comme je vais travailler sur une année complète, et je devrais avoir 120.000 lignes à traiter. Il faudrait donc que la solution proposée puisse traiter ce volume de données. Ca rentre dans un fichier Excel classique.

Merci de vos lumières.

 

[Laliepomm]

Bonjour,
J'ai regardé un peu ton fichier et j'ai un souci avec les défauts de pointage car ils faussent les formules...
ci-joint mon brouillon de fichier avec le repérage des défauts de pointage.
Je pense que tu dois avoir le même sujet, donc je conseille de corriger déjà ce point avant de continuer.
Salutations
Lalie

 

[Exl-Noob]

Bonjour @Laliepomm,

Pas de fichier en vue pour l'instant
J'ai repéré quelques erreurs de pointage en effet (pointage impair sur une journée) ce que je ne peux corriger, car je ne peux pas inventer un pointage.

De mon côté je crois que j'ai une piste.
J'ai modifié ma formule pour calculer les effectifs, en partant du principe que :

• 1er badgeage = arrivée sur site
• 2e badgeage = départ du site (pause repas)
• 3e badgeage = retour sur site (retour pause repas)
• 4e badgeage = départ du site

J'ajoute donc -1 aux badgeages paires et +1 aux badgeages impairs :

=CHOISIR(NB.SI.ENS([Mat.];[@[Mat.]];[Fin];"="&[@Fin];[HrSaisie];"<="&[@HrSaisie]);1;-1;1;-1)

J'arrive donc à construire un graphique à l'aide d'un TCD, mais je ne suis pas en cumulé, ce qui me donne des valeurs négatives

Je pourrais rajouter une colonne calculée à mes données dans l'onglet Sheet1, en effectuant un cumul sur une journée, mais cela nécessite que je trie ma liste en ordre croissant, et il doit y avoir plus joli/pratique que cela.

Des idées ?

 

[Exl-Noob]

Et bien la solution ne venait pas d'un TCD.
Il me suffisait en fait de faire une SOMME.SI.ENS pour constituer un tableau de synthèse comme ceci :

Puis de traiter le résultat avec un graphique pour avoir un peu plus de lisibilité. J'ai utilisé les graphiques de contour filaire, ça fait comme des courbes de niveau et ça donne ça :

Ca me permet de voir que les heures d'arrivée et de départ son à peu près les mêmes toute l'année, sauf en août ou tout le monde écourte ses journée (haha petits coquins, quand les boss sont en congés...), et que le créneau 7:00-17:00 est clairement le plus fréquenté avec seulement moins de 20 personnes qui arrivent avant ou repartent après.
Et ça c'est parfait c'est exactement le genre d'infos que je voulais savoir.