Calcul des heures hebdomadaires - Tableau par mois

Bonjour à tous !

Cela fait longtemps que je m'inspire, ou m'aide du forum sans jamais avoir besoin de créer un compte pour poser une question. Mais aujourd'hui je n'arrive pas à m'en sortir... :/

Le cas est assez simple et déjà rencontré de multiple fois :

- je souhaite être en mesure d'avoir un aperçu du nombre d'heure faites ou à faire par semaine. (Comme pour le cas du "par mois" sur la feuille de calcul en haut à droite).

- je ne souhaite pas voir apparaitre des "ilots" par n° de semaine sur une feuille de calcul de 10 pieds de longs

J'ai épluché pas mal de sujet, et testé pas mal de chose, notamment avec les numéro de semaine, les jours ouvrés....etc. Mais je ne vois pas vraiment comment faire.

J'ai sans doute également besoin d'optimisation dans ce fichier je vous laisserait en juger par vous-même. Juste une chose : le fichier doit être utilisable par des personnes très peu à l'aise sur Excel, ou après avoir suivis une formation sur ce document spécifiquement.

Je vous remercie d'avance pour le temps que vous y consacrerez !

ps : z'êtes les meilleurs !

Bonjour

Avec 2 colonnes supplémentaires

Merci Chris, je ne connaissait pas le "type de renvoi" = 21 cela équivaut à une semaine du lundi au dimanche si je comprend bien, et cela même si la semaine est coupé entre deux mois différents.

Je viens d'apprendre quelque chose, je la mettrait en place dès demain, et procèderais à quelque ajustement concernant l'affichage des données.

En effet, je souhaiterais supprimer les colonnes (et non pas simplement les masquer), et avoir un tout petit tableau en haut à droit récapitulatif.
Par exemple : Horaires réalisées pour la semaine du 9 janvier 2020 +/- (en fonction du résultat) sur 35:00 (fonction concatener plus un somme.si...et autres chose que je testerais) [un peu comme pour le tableau que j'ai fais au mois : 2 lignes en ahut à droite].

Je vais essayer de mon côté et si j'ai besoin je reviendrais vers le forum, sinon je mettrai en résolu le topic !

Merci une nouvelle fois 👌

RE

21 c'est pour le numéro de semaine ISO et non selon la norme US et nos semaines commencent le lundi

Bonjour !

Bon j'ai testé pas mal de formules en vain, le meilleur résultat obtenu est 0 (au lieu de ######), ça veux au moins dire qu'il y a une certaine logique dans le raisonnement....

J'ai ajouté quelque lignes en haute a droite pour avoir un petit tableau récapitulatif par semaine.

J'aimerais avoir le même principe que ce que j'ai fais par mois ; à savoir :
afficher un + ou un - devant le nombre d'heure pour signifier que l'on est en avance ou en retard de travail par rapport au nombre d'heure par mois ;
mais ici je le veux par semaine.

Je ne sais pas si cela est assez clair. Mais nous travaillons avec des intérimaire et c'est le nombre d'heures par semaine qui prime sur le nombre d'heures par mois. D'où cette nécessitée que chacun puisse avoir un aperçu rapide et modifier son planning en fonction.

Pour le formule NO.SEMAINE(XX;21) quel est la différence entre 21 et 2 (celui que j'ai appris à utiliser en cours). La norme ISO correspond au système international, qu'est ce que cela signifie ?

Bonjour

2 signifie que la semaine commence le lundi mais utilise la norme US donc en 2021 et suivante, le résultat est faux

ISO est la norme utilisée en France

On ne peut calculer la semaine d'une plage de dates même en matriciel

Il faut donc ruser pour ton tableau si tu ne met pas de colonne Numéro de semaine

Un mois, sauf février commençant un lundi une année non bissextile, contient 5 à 6 semaines dont 4 entières

J'ai renommé tes tableau dont les noms étaient inutilisables en l'état et ajouté les formules

(on ne garde jamais les noms autiomatiques des tableaux structurés, encore sur des copies de copies de copies)

Je comprend mieux le soucis. Merci pour ton aide. Je vais devoir simplifier car je serais incapable de l'expliquer aux utilisateurs et contrôleurs de ce fichier.

Donc pour faire au plus simple c'est : colonne avec numéro de semaine et somme.si en fonction du numéro de semaine...et ensuite masquage des colonnes.
Mais cette fois-ci je ne trouve pas mon erreur pour que cette formule (en rouge). Dans sa lecture, je ne décèle pas de faute (parenthèse, ou point-virgule mal placé).

Est-ce que tu peux m'aider à débloquer cette situation ?

RE

Comme expliqué il y a jusqu'à 6 semaines par mois (mai et août sont dans ce cas cette année)

Si chaque année tu as envie de changer les formules de chaque onglet je te laisse faire...

SOMME.SI est périmé : utilise SOMME.SI.ENS

La somme de temps pour la 1ère semaine est

=SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];H9)
pour les autres (semaine 2 à 6)
=SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)))

en adaptant le nom du tableau sur chaque onglet

Je vais creuser la question des formules de référence car je ne saisi pas bien leur fonctionnement.

Quoiqu'il en soit j'ai réussi, avec ton aide surtout à obtenir ce que je voulais.

=SI(SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)))>$K$3;CONCATENER("+ ";(TEXTE(SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)))-$K$3;"[h]:mm")));"Saisir temps")

Pour afficher le solde en positif du nombre d'heure par semaine si heure en plus.
Ce qui donne + 1:05

Je classe donc le sujet en résolu. Je te remercie pour ton aide, et te félicité pour la barre de 7000 messages que tu as dépassé en me répondant quotidiennement !

Je te souhaite une excellente journée Chris !

RE

Si les heures sont positives inutile de faire toute cette concaténation, un format personnalisé suffit :

"+ "[hh]:mm

si le négatif peut se produire, calculer en positif (le plus grand - le plus petit) puis appliquer un format basé sur une MFC

Bien le bonjour,

J'ai essayé d'appliquer ce que tu m'a recommandé. Mais malheureusement j'ai échoué.

C'est comme si la mise en forme conditionnelle prenait le dessus sur le "résultat".

Dans la cellule en rouge, si le résultat est positif, ce dernier apparait quand même en négatif (du à la MFC).

Bonjour

Ta formule est entre ""

Les MFC ne comprennent pas les formules tableaux

Utiliser une formule pour janvier =$K$4:$K$7

=SOMME.SI.ENS($F$9:$F$39;$H$9:$H$39;SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)))<$K$3

ou, pour éviter l'application de la couleur aux textes,
=ET(K4<>"Saisir temps";SOMME.SI.ENS($F$9:$F$39;$H$9:$H$39;SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)))<$K$3)

C'est parfait ! Je ne savais pas non plus que les MFC ne prennais pas en compte les formules tableau.

Don maintenant que cela fonctionne pour le mois de janvier il faut que je l'ai pour toute l'année.

J'ai trouvé une formule permettant de retrouver le nom de la feuille. Je pense qu'il faudrait l'integrer à la formule de base.

=DROITE(CELLULE("nomfichier";A1);NBCAR(CELLULE("nomfichier";A1))-TROUVE("]";CELLULE("nomfichier";A1)))

Permet de trouver le nom de la feuille

Donc dans la formule de base sur le petit tableau fonctionnel. pour rappel :

=SI(SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)))>$K$3;(SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)))-$K$3);$K$3-(SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)))))

Il faudrait remplace le terme "Janvier" (qui est le nom de la feuille) par la formule au dessus. Ceci me permettra de facilement copier/coller ou dupliquer la feuille actuelle pour les autres mois.

Bien entendu avant de demander de l'aide j'ai essayé, et devine quoi, cela ne fonctionne pas. Surement une histoire de parenthèse, de priorité dans la formule....

Si jamais cela devient vraiment infaisable, ou si tu n'as pas le temps, pas de soucis, je trouverais un moyen plus archaïque pour faire cela sur les 12 mois.

ps : quand je duplique la feuille, le terme "Janvier" se transforme en "JanvierX". X étant la variable correspondant au nombre de fois que je l'aurais dupliquée.

ps 2 : je te joins la dernière version du fichier.

Bonjour à tous,

@Chris, qu'entends-tu quand tu dis que la fonction SOMME.SI est périmée ?

RE

YouniCornnn

C'est parfait ! Je ne savais pas non plus que les MFC ne prennais pas en compte les formules tableau.

Don maintenant que cela fonctionne pour le mois de janvier il faut que je l'ai pour toute l'année.

J'ai trouvé une formule permettant de retrouver le nom de la feuille. Je pense qu'il faudrait l'integrer à la formule de base.

=DROITE(CELLULE("nomfichier";A1);NBCAR(CELLULE("nomfichier";A1))-TROUVE("]";CELLULE("nomfichier";A1)))

Permet de trouver le nom de la feuille

Ici ce n'est pas le nom de la feuille mais du tableau structuré

Il faudrait savoir ce que tu veux :

• tu es parti sur des plages, puis tu es passé au tableaux structurés
  le nom des champs entre [ ] est propre aux tableaux structurés
• tu as rejeté mes premières formules "Je vais devoir simplifier car je serais incapable de l'expliquer aux utilisateurs et contrôleurs de ce fichier"
  changer les formules pour y intégrer ta formule trouvant le nom de la feuille ne simplifie pas...
  Fais un cherche et remplace dans chaque onglet : ce sera plus rapide
  En plus tu sembles vouloir adapter tes calculs de semaine selon le mois et l'année : tu auras des années où tu ne pourras éliminer la semaine à cheval sur 2 années car il y aura des jours ouvrés sur la semaine 53... et as plus de 4 semaines sur nombre de mois

Joyeuxnoël

JoyeuxNoel a écrit :

@Chris, qu'entends-tu quand tu dis que la fonction SOMME.SI est périmée ?

SOMME.SI.ENS fonctionne pour 1 ou n arguments et continuer à utiliser les 2 c'est se faire des nœuds inutiles dans la tête

1. car les arguments ne sont pas dans le même ordre
2. et donc être obligé de tout reprendre si on veut ajouter un critère...

Idem pour MOYENNE, MIN, MAX

Salut Chris !

Je sais que tu as raison car je suis actuellement un peu perdu (en ce qui concerne les arguments des formules que tu m'a soumis : j'aurais dû te demander directement les explications détaillées...).

Selon toi :

1. Quel est le meilleur moyen pour éviter le chevauchement de certaine semaines entre 2 années ? (par exemple : 53 et 1)
2. Comment optimiser le fichier afin qu'il soit dupplicable à l'infini en changeant uniquement LA DATE DE DÉPART (pour que ça créée les séries de jours dans chaque tableau) ==> surement du VBA...
3. Quel est le moyen le plus simple de faire tout le travail que nous avons fait en amont sur le calcul des heures par semaine (avec si possible affichage dans le petit tableau tel qu'actuellement) ?
4. Comment est-ce que tu aurais abordé la chose en partant de rien ?
5. ...j'ai l'impression que tu m'en veux un peu, et que le sujet te gonfle, c'est le cas ? ^^'

RE

Tu peux simplifier tes formules

=MAX(SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)));$K$3)-MIN(SOMME.SI.ENS(Janvier[Temps travaillé];Janvier[N°semaine];SI($H$9>=52;LIGNE(A1);$H$9+LIGNE(A1)));$K$3)

C'est la MFC qui mettra + ou -

On a forcément des semaines à cheval sur 2 années

En fait j'ai revérifié : pour les jours ouvrés pas de problème en semaine 52 ou 53, on n'a que le week end et le 1er qui peuvent y être

C'est en décembre que l'on peut avoir des jours ouvrés sur la semaine 1 de l'année suivante donc pour la 5ème semaine de décembre

=MAX(SOMME.SI.ENS(Décembre[Temps travaillé];Décembre[N°semaine];SI(NB.SI(Décembre[N°semaine];1)>0;1;H$9+LIGNE(A5)));$K$3)-MIN(SOMME.SI.ENS(Décembre[Temps travaillé];Décembre[N°semaine];SI(NB.SI(Décembre[N°semaine];1)>0;1;$H$9+LIGNE(A5)));$K$3)

A noter que ton découpage par mois donne des semaines incomplètes et donc que ce calcul donne forcément un reliquat... en 1ère ou dernière semaine

Pour le 2. écrire l'année et le mois chacun dans une cellule , I1 et I2 par exemple et

• en B9 de chaque onglet utiliser =DATE($I$1;$I$2;LIGNE(A1))
• étirer juqu'en bas.
• prévoir 29 jours en février et masquer la ligne les 3 années non bissextiles

5. c'est toujours un peu agaçant quand on part sur un classeur avec déjà 12 mois mais qui n'est en fait qu'une maquette
Si dès le début tu pars sur 2 mois en disant que tu veux des conseils, que ce n'est pas finalisé, on perd moins de temps a rentrer dans ta logique qui n'est pas stabilisée et on a plus de latitude pour te proposer une autre approche...

Okay je vois ce que tu veux dire pour le point 5. En réalité je pensais (à tort) que proposer une maquette la plus complete possible était plus simple pour les tuteurs pour saisir l'objectif de la demande d'aide.

Avant de poster ce sujet, j'ai ouvert 2 ou 3 planning disponible en téléchargement, et je me suis rendu compte que cela ne correspondais pas à ce que je voulais. En créant le sujet je me suis alors dis que cela correspondrais forcément à mon sujet et que cela allais être assez simple de répondre à ma question, à tort encore une fois.

Alors non ce tableau n'est pas décisif, et il attendra le 1er janvier avant sa 1ere utilisation, et surtout après que tout soit réglé comme du papier à musique.
Alors oui, si toi, ou n'importe qui d'autre à une idée, pour avoir ce fameux calcul des heures par semaines (avec +/- des heures restantes / à faire) dans un tableau plus simple, sous un autre forme : je suis preneur.

Si tu souhaite toujours m'aider, et si tu pense qu'il y a effectivement un moyen plus simple, mais qu'il faut changer le modèle de ce classeur Excel, alors je te suivrais sans problème !! Après tout, c'est toi qui me donne tout ces conseils depuis le début, et qui n'hésite pas à m'expliquer, et me re-expliquer tes 1eres réponses..

Bonjour,

@Chris,

Ok, je vois ce que tu veux dire. C'est vrai que j'ai toujours tendance à les présenter en formation, parce que j'insiste beaucoup en amont sur la structure d'une formule, pour que ce soit bien compris.
On part des fonctions statistiques, puis conditionnelles, et là c'est la montée en puissance. À ce stade, les personnes découvrent une nouvelle architecture et je ne voulais pas les perdre avec critère 1, plage 1, etc.

Mais après tout, pourquoi pas, c'est à essayer.

Bonjour

J'ai laissé tomber SOMME.SI et MOYENNE.SI en formation sans soucis

Le fait de d'annoncer vous avez le choix du nombre de critères et, pour chaque critère, de définir la plage et le critère passe très bien

Un exo mono critère et un multi et ça roule.

Ce qui les coince plus c'est quand le critère porte sur la même plage que la SOMME