Moyenne horaire hebdo sur planning annuel

[stapsounet]

Bonjour,

Encore une fois, je viens solliciter les lumières de ce forum

J'ai un planning annuel du type
colonne A : date
colonne B : jour de la semaine
Colonne C : planning du matin
Colonne D : planning de l'après-midi
Colonne E : total d'heure de la journée
Colonne F : cellules fusionnées avec la somme d'heure par semaine (somme des cellules de la colonne E du Lundi au Dimanche)

Et ce schéma est reproduit 1 fois pour chaque mois.

Afin de rester dans un cadre légal (cela concerne un emploi annualisé), je souhaite calculer la moyenne horaire sur 12 semaines consécutives, et lui attribuer un format spécifique si cela dépasse 44h.

Jusque là, je faisais tout à la main (=moyenne(cellule 1;cellule 2;…) )
Mais du coup, je dois tout refaire tous les ans puisque mes cellules ne sont jamais au même endroit et cela est source d'erreur de ma part, et peu productif!

Auriez-vous une solution à me proposer pour simplifier mon calcul et la façon de le mettre en place?

mon fichier de travail (ou je fais mes essais excel!) est dispo à l'adresse suivante et je précise que je travaille sur mac (10.5) avec office 2004. J'ai souvent des problèmes avec les macros qui font planter excel. Du coup, je suis un peu frileux avec celles-ci...

Merci d'avance

 

[stapsounet]

Re : Moyenne horaire hebdo sur planning annuel

Personne n'a une idée de génie pour m'aider?
Même une idée tout court, je prends, même une toute petite idée

 

[bbb38]

Re : Moyenne horaire hebdo sur planning annuel

Bonjour Stapsounet, le forum,
Pour essayer de te venir en aide (malgré mes modestes connaissances), peux-tu m’indiquer :
- si la présentation des données de ton fichier sont figées, ou si l’on peut la modifier ?
- le calcul de la moyenne horaire sur 12 semaines consécutives débute-t-il à la première semaine de chaque année (dans l’hypothèse ou le salarié est déjà présent au 31 décembre de l’année précédente) ou ce calcul s’effectue à partir de la date d’entrée du salarié ?
Si j’ai bien compris ta demande, tu désires obtenir un tableau sur lequel tu n’auras pas à modifier les formules chaque année.
Cordialement,
Bernard

 

[stapsounet]

Re : Moyenne horaire hebdo sur planning annuel

Bonjour bbb38,

Alors en ce qui concerne la présentation, elle est tout a fait modifiable. Je suis en train de tester d'ailleur de mon coté une présentation ou je ferais des mois sur 5 semaines du lundi au dimanche et ou je n'aurais qu'à décaler les dates chaque année en fonction du jour...
Donc on peut modifier la présentation.

Pour la moyenne sur 12 semaines consécutives, je fonctionne sur la période de référence de l'emploi (ici de Janvier à Décembre, mais dans d'autre cas, c'est de Mars à Février...). Ce qui ne change pas grand chose puisque le planning sera toujours sur 12 mois sans rapport avec l'année civile.
Je calcule la moyenne dès la première semaine et sur les 11 suivantes, et jusqu'à présent, je décale le tout d'une semaine à chaque fois pour avoir tous mes résultats dans un tableau.
L'objectif reste: on ne doit pas avoir une moyenne de + de 44h travaillée sur une période de 12 semaines consécutives.

Et effectivement, le but pour moi est de simplifier mes calculs annuels donc ne pas avoir à refaire mes formules tous les ans. Ou si ça n'est pas possible, trouver un moyen pour le faire rapidement!

Merci d'avance!

 

[hoerwind]

Re : Moyenne horaire hebdo sur planning annuel

Bonjour,

Avec en pièce jointe un petit extrait de ton planning, ce serait plus facile pour te proposer une solution.
N'oublies pas d'y indiquer des valeurs et les résultats à obtenir, calculés manuellement.

Une petite question :
La moyenne se calcule-t-elle sur le nombre de jours ouvrés ou sur le nombre de jours de présence, une personne pouvant être absente pour cause de maladie, congé, ... ?

 

[stapsounet]

Re : Moyenne horaire hebdo sur planning annuel

hoerdwind,

J'ai mis un lien sur mon premier poste pour le fichier car il est trop volumineux pour le mettre sur le site (je m'y prend mal, j'ai toujours des fichiers de plus de 100ko!)

En ce qui concerne la moyenne, je n'ai guère de précision sur les textes, donc je calcul la somme d'heure par semaine (du lundi au dimanche) dans une colonne, et ensuite, je fais une moyenne des ces sommes sur 12 semaines consécutive.
Je travail sur un planning prévisionnel donc je ne prend pas en compte les congés et absence éventuels. Pour le réel effectué, l'emploi étant annualisé, la moyenne est faite sur les jours de la semaine du lundi au dimanche sans tenir compte des jours ouvrés ou non... exactement comme pour le prévis. L'outil sert juste à réajuster les récup' en cas de dépassement.

Merci d'avance!

 

[hoerwind]

Re : Moyenne horaire hebdo sur planning annuel

Re,

Ton fichier est difficilement exploitable :
- des cellules fusionnées (à proscrire, cela n'engendre que des ennuis)
- les dates ne sont pas des dates, mais du texte
- il n'est fait nulle part référence à une année (probablement 2010 à cause du 01/01 qui tombe un vendredi)
- aucun numéro de semaine n'est indiqué dans les tableaux.
- le tableau Obligations légales contient des messages d'erreur

Il me semble que la première chose à faire serait de transposer les données dans un seul tableau, avec les dates, numéros de semaine et mois en en-têtes de colonnes et les autres données dans les lignes du dessous.
Fait une petite recherche dans la zone de téléchargement de ce site ou sous les anciens messages, tu y trouveras de nombreux exemples.

 

[stapsounet]

Re : Moyenne horaire hebdo sur planning annuel

Re,

Alors, j'ai fais un essai en faisant un tableau unique, avec le jour en colonne A, la date en B, le numéro de semaine en C, mon planning dans les colonnes D,E,F, mon prévi horaire journalier en G, le total par semaine en H et le total mensuel en I.
Je ne sais pas si j'ai bien compris ce qu'il m'était suggéré...

J'ai du coup une première question: y a t il une formule pour calculer la somme de cellules de la colonne G en fonction du numéro de la semaine ?(soit: pour tous les numéros de la colonne C identiques, calcul de la somme des cellules correspondantes en colonne G).

Ensuite, je n'ai toujours pas trouvé ma solution pour faire une moyenne sur 12 semaines consécutives et cela sur toute une année...

Je remet un lien vers mon nouveau fichier d'essai. Les chiffres sont juste pour l'essai.

J'ai bien sur cherché dans les téléchargements, mais je n'ai pas trouvé ce que je cherche (il y a aussi des fichiers que je n'arrive pas à ouvrir sous mac avec les macros).

Merci du coup de main!

 

[bbb38]

Re : Moyenne horaire hebdo sur planning annuel

Bonjour Stapsounet, le forum,
Une idée de présentation. Regardes si cela te convient.
Je n’ai pas réalisé à ce stade, les tableaux d’obligations légales (comme sur ton fichier), ni la récapitulation annuelle des heures.
Les colonnes de A à D peuvent être masquées.
J’ai supposé que les dimanches n’étaient jamais travaillés.
Le fichier est protégé sans mot de passe.
La feuille « planning » permet l’entrée d’un salarié sans modifier les formules, mais en indiquant sur la ligne de la date d’entrée, en colonne « N » le chiffre1.
Cordialement,
Bernard

 

[stapsounet]

Re : Moyenne horaire hebdo sur planning annuel

Bonjour bernard,

J'étais finalement arrivé au même genre de résultat (en moins aboutit!)...
J'ai repris la formule de calcul pour la somme hebdomadaire et j''apprécie l'idée d'une colonne calculant le total annuel au quotidien...

Reste mon problème initial du calcul de moyenne hebdomadaire sur 12 semaines consécutives, et ce pour toute l'année. Une idée?
En jouant sur le numéro des semaines je pense qu'il y a une possibilité, mais je n'ai pas encore trouvé l'astuce!

Merci encore!

 

[bbb38]

Re : Moyenne horaire hebdo sur planning annuel

Bonjour Stapsounet, le forum,
Un début de solution pour le calcul de moyenne hebdomadaire sur 12 semaines.
Cijoint.fr - Service gratuit de dépôt de fichiers
Regardes si la méthode de calcul est bonne. Le fichier mérite d’être simplifié.
Pour éviter des risques d’erreur, on pourrait créer 2 modèles de fichier (pour les années bissextiles, et pour les autres).
Cordialement,
Bernard

 

[stapsounet]

Re : Moyenne horaire hebdo sur planning annuel

Nickel!!

Le report des données par la fonction recherchev permet effectivement de faire la moyenne sur 12 semaines et ensuite de faire un copier vers le bas de la formule!

Je vais adapter ça à ma sauce pour mon fichier, mais avec ce début, je devrais réussir à finir mon fichier!!!

Merci Beaucoup!