Calculs heures hebdo avec sommeprod ne fonctionne que pour un mois
Résolu
BP29270
Messages postés
16
Date d'inscription
Statut
Membre
Dernière intervention
-
BP29270 Messages postés 16 Date d'inscription Statut Membre Dernière intervention -
BP29270 Messages postés 16 Date d'inscription Statut Membre Dernière intervention -
Bonjour,
J'ai quelques soucis avec ma formule de calcul des heures hebdomadaires (tableau du bas)
lorsque je choisis le mois de janvier dans A1 de l'année 2019 dans A2, pas de soucis, mon tableau fonctionne parfaitement, heures hebdos comprises. En revanche dès que je passe sur février, ma formule me compte 0 malgré que le nom de postes soient bien rentrés sur chaque ligne de chaque salarié et sur chaque jour...Quelqu'un accepterait-il de m'aider svp?
formule des numéros de semaines, qui se modifient selon le choix du mois et de l'année en a1 + a2:
=SI(OU(JOURSEM(E$6;2)=1;JOUR(E$6)+MOIS(E$6)=2);NO.SEMAINE(E$6;21)+SI(ET(NO.SEMAINE(E$6;21)=1;MOIS(E$6)>1);52;0);"")
formule de calcul des heures hebdos:
=SI(E$7<>"";SOMMEPROD(($E$6:$AI$6>=$E$6+8-JOURSEM($E$6;2)+(7*(E$7-2)))*($E$6:$AI$6<=$E$6+7-JOURSEM($E$6;2)+(7*(E$7-1)));$E52:$AI52);"")
En espérant que quelqu'un puisse me donner un coup de main,
par avance merci bien!
BEN
J'ai quelques soucis avec ma formule de calcul des heures hebdomadaires (tableau du bas)
lorsque je choisis le mois de janvier dans A1 de l'année 2019 dans A2, pas de soucis, mon tableau fonctionne parfaitement, heures hebdos comprises. En revanche dès que je passe sur février, ma formule me compte 0 malgré que le nom de postes soient bien rentrés sur chaque ligne de chaque salarié et sur chaque jour...Quelqu'un accepterait-il de m'aider svp?
formule des numéros de semaines, qui se modifient selon le choix du mois et de l'année en a1 + a2:
=SI(OU(JOURSEM(E$6;2)=1;JOUR(E$6)+MOIS(E$6)=2);NO.SEMAINE(E$6;21)+SI(ET(NO.SEMAINE(E$6;21)=1;MOIS(E$6)>1);52;0);"")
formule de calcul des heures hebdos:
=SI(E$7<>"";SOMMEPROD(($E$6:$AI$6>=$E$6+8-JOURSEM($E$6;2)+(7*(E$7-2)))*($E$6:$AI$6<=$E$6+7-JOURSEM($E$6;2)+(7*(E$7-1)));$E52:$AI52);"")
En espérant que quelqu'un puisse me donner un coup de main,
par avance merci bien!
BEN
Configuration: Windows / Chrome 75.0.3770.100
A voir également:
- Calculs heures hebdo avec sommeprod ne fonctionne que pour un mois
- Tableau calcul heures supplémentaires ✓ - Forum MacOS
- Excel mois en lettre ✓ - Forum Excel
- Convertir trimestre en mois - Forum Excel
- Convertir un mois en chiffre en lettre RESOLU ✓ - Forum Bureautique
- Youtube premium 2 mois gratuit - Accueil - TV & Vidéo
8 réponses
Bonjour, et bienvenue.
Je ne sais malheureusement pas travailler sur image.
Je verrai si je peux t'aider à avancer quand tu auras joint ton fichier Excel.
Je ne sais malheureusement pas travailler sur image.
Je verrai si je peux t'aider à avancer quand tu auras joint ton fichier Excel.
1) Tu vas dans https://www.cjoint.com/
2) Tu cliques sur [Parcourir] pour sélectionner ton fichier (15 Mo maxi)
3) Tu défiles vers le bas pour cliquer sur le bouton bleu [Créer le lien Cjoint]
4) Au bout de quelques secondes la deuxième page s'affiche, avec le lien en gras ; tu fais un clic-droit dessus et tu choisis "Copier le lien"
5) Tu reviens dans ta discussion sur CCM, et dans ton message tu fais "Coller".
=>Voir la fiche https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers
Il existe aussi :
1) https://mon-partage.fr/
2) https://www.transfernow.net/
Bonjour
Tes formules de ta ligne 7 sont trop compliquées
proposition:
par ex en E7
=sem_iso(E6)
puis en F7 à tirer vers la droite ( je n'ai pas encore regardé la fin du mois)
=SI(JOURSEM(F6;2)=1;sem_iso(F6);"")
"sem_iso" est une fonction installée dans un module de l'éditeur VBA
Demain, si j'ai du temps, j'essaierai de regarder le calcul des heures par semaine
Tes formules de ta ligne 7 sont trop compliquées
proposition:
par ex en E7
=sem_iso(E6)
puis en F7 à tirer vers la droite ( je n'ai pas encore regardé la fin du mois)
=SI(JOURSEM(F6;2)=1;sem_iso(F6);"")
"sem_iso" est une fonction installée dans un module de l'éditeur VBA
Function sem_iso(Wdate As Date) As Byte
sem_iso = DatePart("ww", Wdate, vbMonday, vbFirstFourDays)
End Function
Demain, si j'ai du temps, j'essaierai de regarder le calcul des heures par semaine
Bonjour! merci du temps consacré à me répondre
J'ai testé tes fonctions c'est top merci. ça soulage un petit peu mon fichier. avec ta proposition, si je demande à mon fichier de m'afficher le mois de février, il ne m'affiche que les heures de la dernière semaine du tableau et me les met dans la première colonne des heures hebdo. comme si on voulait rallonger le mois de janvier au lieu de passer sur le mois de fevrier (me suis-je bien exprimer?.......^^)
voir le fichier (annule et remplace le précédent lien):
https://www.cjoint.com/c/IFzuqwaahY4
J'ai testé tes fonctions c'est top merci. ça soulage un petit peu mon fichier. avec ta proposition, si je demande à mon fichier de m'afficher le mois de février, il ne m'affiche que les heures de la dernière semaine du tableau et me les met dans la première colonne des heures hebdo. comme si on voulait rallonger le mois de janvier au lieu de passer sur le mois de fevrier (me suis-je bien exprimer?.......^^)
voir le fichier (annule et remplace le précédent lien):
https://www.cjoint.com/c/IFzuqwaahY4
la partie de la formule que j'ai quelque peu du mal à comprendre (c'est bien pour ca que c'est limite du copié-collé et certainement pour ça qu'il y a des erreurs!)
ce n'est pas très appréciable de ne pas comprendre ce qu'on fait!
SOMMEPROD(($E$6:$AI$6>=$E$6+8-JOURSEM($E$6;2)+(7*(E$7-2)))*($E$6:$AI$6<=$E$6+7-JOURSEM($E$6;2)+(7*(E$7-1)));$E52:$AI52)
J'étais en train d'écrire ce message et j'ai eu une idée. mais il faut aller au bout pour soulager le tableau visuellement parlant, pas besoin d'afficher systématiquement le numéro de semaine. il faudrait que je puisse figer le 1er numero de semaine depuis mes cellules d'heures hebdo ...
https://www.cjoint.com/c/IFzuYhm1Hw4
ce n'est pas très appréciable de ne pas comprendre ce qu'on fait!
SOMMEPROD(($E$6:$AI$6>=$E$6+8-JOURSEM($E$6;2)+(7*(E$7-2)))*($E$6:$AI$6<=$E$6+7-JOURSEM($E$6;2)+(7*(E$7-1)));$E52:$AI52)
J'étais en train d'écrire ce message et j'ai eu une idée. mais il faut aller au bout pour soulager le tableau visuellement parlant, pas besoin d'afficher systématiquement le numéro de semaine. il faudrait que je puisse figer le 1er numero de semaine depuis mes cellules d'heures hebdo ...
https://www.cjoint.com/c/IFzuYhm1Hw4
mine de rien j'aurais bossé ce soir! et la j'ai eu un declic
a force de bidouilles j'ai trouvé une solution pour mon tableau!
voici la version, MA version finale. Si l'un de vous s'amuse à chercher un peu je parie qu'il trouvera une façon disons plus académique et moins bordélique de réaliser mon planning mais au moins ça marche!
https://www.cjoint.com/c/IFzw3XkHNl4
(j'ai supprimé les autres liens de fichiers)
a force de bidouilles j'ai trouvé une solution pour mon tableau!
voici la version, MA version finale. Si l'un de vous s'amuse à chercher un peu je parie qu'il trouvera une façon disons plus académique et moins bordélique de réaliser mon planning mais au moins ça marche!
https://www.cjoint.com/c/IFzw3XkHNl4
(j'ai supprimé les autres liens de fichiers)
Proposition à tester
https://mon-partage.fr/f/vjT9R6sZ/
https://mon-partage.fr/f/vjT9R6sZ/
Petite modif dans le module
Option Explicit
'-----------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lig As Byte, Col As Byte, Choix As String
Dim Col_sem As Byte
If Intersect(Target, Range("planning")) Is Nothing Then GoTo fin
'--------coordonnées cellule choisie
Lig = Target.Row
Col = Target.Column
Choix = Target.Value
' ------colonne de la semaine en cours
If Cells(7, Col) = "" Then
Col_sem = Rows(7).Find(What:="*", after:=Cells(7, Col), searchdirection:=xlPrevious).Column
Else
Col_sem = Col
End If
' ----------------inscription des temps de la semaine par appel _
de la fonction personnalisée "Durée" (module routines)
Cells(Lig + 19, Col_sem) = Cells(Lig + 19, Col_sem) + Duree(Choix)
fin:
End Sub
Alors très franchement je comprends a peine comment fonctionne ton code vba, pas la moindre idée de comment il fonctionne, tu m'as noyé déjà! ^^
j'ai ouvert le tableau que tu m'as envoyé, il y a des anomalies d'affichage sur la version que tu m'as envoyé. compatibilité entre nos excel? le 2ème code que tu as envoyé en modif idem, ne sachant trop où le mettre dans le module, j'ai essayé avant, après et à la place du 1er code j'ai carrément des erreurs du sub dans vba
sais tu d'où ça vient?
j'ai ouvert le tableau que tu m'as envoyé, il y a des anomalies d'affichage sur la version que tu m'as envoyé. compatibilité entre nos excel? le 2ème code que tu as envoyé en modif idem, ne sachant trop où le mettre dans le module, j'ai essayé avant, après et à la place du 1er code j'ai carrément des erreurs du sub dans vba
sais tu d'où ça vient?
Re,
il est vrai que dans mon dernier envoi indiquant une modif à faire, j'avais appelé la zone E8:AI24 "planning" et je ne te l"'avais pas signalé
dans le code ci joint j'ai donc Remis E8:AI24; je viens de tester et si on écrit un signe dans une cellule hors zone, la macro est court-circuitée
ce code est une macro dite événementielle qui se déclenche ici (worksheet_change) sur une saisie dans une cellule de la feuille. tu la trouveras dans le module "calendrier de référence"
maintenant tu me parles d'anomalies, ok, mais encore faut-il dire lesquelles.
Tu as le projet de créer des feuilles au fur et à mesure des mois. Actuellement la zone E8;AI24 correspond au nombre de jours de janvier. donc, il faudra paramétrer pour chaque mois en fonction du dernier jour. Pour ma part, je créerais au départ 12 feuilles pour ne pas avoir à re-bidouiller chaque mois; mais de toutes façons, il faudra changer le nom des feuilles ---> tu as du boulot
Ci joint le classeur
https://mon-partage.fr/f/dgtMagH5/
il est vrai que dans mon dernier envoi indiquant une modif à faire, j'avais appelé la zone E8:AI24 "planning" et je ne te l"'avais pas signalé
dans le code ci joint j'ai donc Remis E8:AI24; je viens de tester et si on écrit un signe dans une cellule hors zone, la macro est court-circuitée
ce code est une macro dite événementielle qui se déclenche ici (worksheet_change) sur une saisie dans une cellule de la feuille. tu la trouveras dans le module "calendrier de référence"
maintenant tu me parles d'anomalies, ok, mais encore faut-il dire lesquelles.
Tu as le projet de créer des feuilles au fur et à mesure des mois. Actuellement la zone E8;AI24 correspond au nombre de jours de janvier. donc, il faudra paramétrer pour chaque mois en fonction du dernier jour. Pour ma part, je créerais au départ 12 feuilles pour ne pas avoir à re-bidouiller chaque mois; mais de toutes façons, il faudra changer le nom des feuilles ---> tu as du boulot
Ci joint le classeur
https://mon-partage.fr/f/dgtMagH5/
Salut,
j'ai un soucis avec ta propo, quand je modifie une valeur dans le tableau des postes, le tableau des totaux heures garde en memoire les anciennes valeurs dont je n'ai plus besoin.
question praticité pour le moment, et pourtant je sais bien que mes données ne sont pas simples, je préfère mon tableau. je l'ai un peu modifié depuis l'autre fois.
https://www.cjoint.com/c/IFDjWpvCHk4
Je te remercie pour le temps consacré, je ne vais pas t'embêter plus tu vas péter une durite! ^^
à tout hasard si tu connais une macro qui permet de copié le nom d'une cellule dans le nom de ma feuille je suis preneur ;-)
à + et merci encore
j'ai un soucis avec ta propo, quand je modifie une valeur dans le tableau des postes, le tableau des totaux heures garde en memoire les anciennes valeurs dont je n'ai plus besoin.
question praticité pour le moment, et pourtant je sais bien que mes données ne sont pas simples, je préfère mon tableau. je l'ai un peu modifié depuis l'autre fois.
https://www.cjoint.com/c/IFDjWpvCHk4
Je te remercie pour le temps consacré, je ne vais pas t'embêter plus tu vas péter une durite! ^^
à tout hasard si tu connais une macro qui permet de copié le nom d'une cellule dans le nom de ma feuille je suis preneur ;-)
à + et merci encore
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Désolé : Tu as omis de préciser que c'était un fichier avec macros (format xlsm) ; or je ne pratique pas VBA ...
De toutes façons, tes formules sont un peu trop compliquées pour que je puisse m'y retrouver.
Je passe la main à un autre membre ...
Cordialement
De toutes façons, tes formules sont un peu trop compliquées pour que je puisse m'y retrouver.
Je passe la main à un autre membre ...
Cordialement
une autre idée. mais la je sèche total!
pour rendre plus ludique ce planning, j'aimerais avoir un bouton avec une macro qui me permettrait de rajouter un mois. je m'explique.
actuellement j'ai les onglets suivants:
listes
jours feries
postes
calendrier de référence.
je vais copier mon calendrier de référence et masquer tous les onglets cités. le nouvel onglet créé s'appellera juil-2019.
mon but est de créer un bouton avec macro permettant de rajouter un nouvel onglet dont le nom sera le mois de l'onglet précédent + 1
soit dans le cas présent aout-2019, puis sept-2019, etc...
dans la même action, il faudrait que le fichier s'enregistre.
merci de votre aide!
https://www.cjoint.com/c/IFzw3XkHNl4
pour rendre plus ludique ce planning, j'aimerais avoir un bouton avec une macro qui me permettrait de rajouter un mois. je m'explique.
actuellement j'ai les onglets suivants:
listes
jours feries
postes
calendrier de référence.
je vais copier mon calendrier de référence et masquer tous les onglets cités. le nouvel onglet créé s'appellera juil-2019.
mon but est de créer un bouton avec macro permettant de rajouter un nouvel onglet dont le nom sera le mois de l'onglet précédent + 1
soit dans le cas présent aout-2019, puis sept-2019, etc...
dans la même action, il faudrait que le fichier s'enregistre.
merci de votre aide!
https://www.cjoint.com/c/IFzw3XkHNl4
Bonjour,
Pourquoi avoir partiellement bousillé le boulot que j'avais fait hier: à ma grande surprise en remettant la ligne 7 avec le numéro de semaine tous les jours tu m'obliges à changer de stratégie pour le comptage des heures de la semaine.... bon je ferai avec mais je perdu confiance en toi, mais comme c'est pour le personnel médical....
Je regarde tout ça à partir de la fin de matinée, la canicule m'obligeant à rester calfeutrer
Le comptage des heures se fera par VBA
EDIT:
j'ai Excel 2007, mais avec les versions suivantes, il existe une fonction toute faite pour donner le n° de la semaine ISO
Pourquoi avoir partiellement bousillé le boulot que j'avais fait hier: à ma grande surprise en remettant la ligne 7 avec le numéro de semaine tous les jours tu m'obliges à changer de stratégie pour le comptage des heures de la semaine.... bon je ferai avec mais je perdu confiance en toi, mais comme c'est pour le personnel médical....
Je regarde tout ça à partir de la fin de matinée, la canicule m'obligeant à rester calfeutrer
Le comptage des heures se fera par VBA
EDIT:
j'ai Excel 2007, mais avec les versions suivantes, il existe une fonction toute faite pour donner le n° de la semaine ISO
Bonjour michel_m, le forum,
A tout hasard, si vous ne connaissez pas déjà, on peut ajouter la fonction numéro de semaine Iso aux anciennes versions d'Excel, voir ici :
https://www.commentcamarche.net/telecharger/bureautique/15223-morefunc-macro-complementaire-excel/
A tout hasard, si vous ne connaissez pas déjà, on peut ajouter la fonction numéro de semaine Iso aux anciennes versions d'Excel, voir ici :
https://www.commentcamarche.net/telecharger/bureautique/15223-morefunc-macro-complementaire-excel/
bon!
Tes classeurs envoyés changent à chaque envoi; ainsi pour calculer la somme des heures hebdomadaires, j'ai besoin de la feuille "postes"
Désolé, mais ton impatience et tes brusques changements d'idées, font qu'il est improbable de pouvoir t'aider à moins de bidouiller par ci par là pour aboutir à une usine à gaz.
Définis ton projet définitif détaillé avec un classeur complet pour que quelqu'un puisse te fournir une solution.
J'attend ta réaction pour voir si j'abandonne ou pas.
Tes classeurs envoyés changent à chaque envoi; ainsi pour calculer la somme des heures hebdomadaires, j'ai besoin de la feuille "postes"
Désolé, mais ton impatience et tes brusques changements d'idées, font qu'il est improbable de pouvoir t'aider à moins de bidouiller par ci par là pour aboutir à une usine à gaz.
Définis ton projet définitif détaillé avec un classeur complet pour que quelqu'un puisse te fournir une solution.
J'attend ta réaction pour voir si j'abandonne ou pas.
LOL
c'est vrai que je pars un peu dans tous les sens
concernant les feuilles comme "postes", je les ai juste masquées pour voir le "rendu final". idem pour les lignes qui servent à mes calculs dans calendrier de référence.
la ligne 7 j'ai fait comme tu m'as dis et je me suis trouvé dans un cas un peu bizarre au niveau des résultats des totaux heures hebdo.
du coup j'ai utilisé la fonction somme.si pour voir ce que ca donnait. j'ai retrouvé mes résultats comme je le souhaitais mais avec le résultat affiché dans chaque colonne de chaque jour. pour "effacer mes résultats" dont je n'ai pas besoin (soit du mardi au dimanche) j'ai ajouté une ligne (76) EN PLUS DE la ligne 7 que tu m'as corrigé, ce qui me permet d'intégrer la fonction si pour n'afficher qu'exclusivement le résultat dont j'ai besoin chaque mois.
mais si tu as une solution qui me simplifie le calcul et qui m'évite d'avoir une ligne en plus je prends!
le fichier dans l'état où il était juste après la modif que tu m'as proposé:
https://www.cjoint.com/c/IFAmiSAdLHj
pas de soucis pour le vba
merci pour ton aide (c'est pour ma femme qui bosse en laboratoire, bien vu pour le milieu medical ;-) )
c'est vrai que je pars un peu dans tous les sens
concernant les feuilles comme "postes", je les ai juste masquées pour voir le "rendu final". idem pour les lignes qui servent à mes calculs dans calendrier de référence.
la ligne 7 j'ai fait comme tu m'as dis et je me suis trouvé dans un cas un peu bizarre au niveau des résultats des totaux heures hebdo.
du coup j'ai utilisé la fonction somme.si pour voir ce que ca donnait. j'ai retrouvé mes résultats comme je le souhaitais mais avec le résultat affiché dans chaque colonne de chaque jour. pour "effacer mes résultats" dont je n'ai pas besoin (soit du mardi au dimanche) j'ai ajouté une ligne (76) EN PLUS DE la ligne 7 que tu m'as corrigé, ce qui me permet d'intégrer la fonction si pour n'afficher qu'exclusivement le résultat dont j'ai besoin chaque mois.
mais si tu as une solution qui me simplifie le calcul et qui m'évite d'avoir une ligne en plus je prends!
le fichier dans l'état où il était juste après la modif que tu m'as proposé:
https://www.cjoint.com/c/IFAmiSAdLHj
pas de soucis pour le vba
merci pour ton aide (c'est pour ma femme qui bosse en laboratoire, bien vu pour le milieu medical ;-) )
en tout cas voila l'explication pour mon cheminement ^^
après je fais avec mes connaissances, elles sont plus grandes que celles d'un parfait novice mais pas encore assez pour simplifier un tableau pareil aussi bien que des gens comme toi, d'où ma jolie usine a gaz comme tu dis ^^
et j'aime bien essayé des choses, ce qui passe effectivement pour de l'impatience alors que ce n'est pas vraiment le cas!
après je fais avec mes connaissances, elles sont plus grandes que celles d'un parfait novice mais pas encore assez pour simplifier un tableau pareil aussi bien que des gens comme toi, d'où ma jolie usine a gaz comme tu dis ^^
et j'aime bien essayé des choses, ce qui passe effectivement pour de l'impatience alors que ce n'est pas vraiment le cas!
Ok, je m'y mets; j'aurai peut-^tre pas fini aujourd'hui
Pense à mettre en couleur les jours fériés et ajouter des lignes pour d'éventuels ponts (bien que dans ce milieu professionnel....)
Le + difficile dans la réalisation d'une appli est de définir au départ le projet et ensuite de "raffiner" en allant de plus dans le détail. ca se fait souvent sur papier et est souvent "prise de t^te"....
Pense à mettre en couleur les jours fériés et ajouter des lignes pour d'éventuels ponts (bien que dans ce milieu professionnel....)
Le + difficile dans la réalisation d'une appli est de définir au départ le projet et ensuite de "raffiner" en allant de plus dans le détail. ca se fait souvent sur papier et est souvent "prise de t^te"....
https://www.cjoint.com/c/IFzgqWllAr4