1. Ce site utilise des "témoins de connexion" (cookies) conformes aux textes de l'Union Européenne. Continuer à naviguer sur nos pages vaut acceptation de notre règlement en la matière. En savoir plus.

Heure supplémentaire de nuit

Discussion dans 'Forum Excel' démarrée par Mathieu18, 9 Janvier 2017.

  1. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    19
    "J'aime" reçus :
    0
    Bonjour,

    Je sais que ce sujet a été abordé maintes et maintes fois mais ma situation est relativement spécifique au vue de son intéraction avec d'autres cellules, j 'arrive au bout de mon tableau mais il y a un véritable écart entre ce que je projette dans ma tete et mon niveau de compétence pour le réaliser. De ce fait je me permets de vous solliciter car je suis complètement noyé.
    Mon cas ne peut pas trop s'expliquer via un post, je vous joins donc un exemple que j'ai essayé d'expliciter au maximum.

    Je vous remercie.

    Edit : intégration du dernier exemple sur le 1 er message.
     

    Pièces jointes:

    Dernière édition: 10 Janvier 2017
  2. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    Dans E2, tu peux faire avec
    Code (Text):
    SI(D2="H";0;SOMMEPROD((LIGNE(INDIRECT(B2*60*24&":"&C2*60*24))<6*60)+(22*60<LIGNE(INDIRECT(B2*60*24&":"&C2*60*24))))/24/60)
    il me semble, pour obtenir les mêmes résultats que toi.

    Pour ce qui est des autres conditions, et de la colonne F, je verrai demain...

    @ plus
     
  3. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    19
    "J'aime" reçus :
    0
    Tout d'abord merci de ton aide.

    Malheureusement la formule bug lorsqu'on entre 00:00 comme heure de début, et lorsqu'on mets par exemple 22h00 - 02h00, ça compte 22:01 au lieu de 4:00.

    Le second point est que je ne l'ai pas illustré sur mon exemple, mais dans mon tableau pour définir la plage d'horaire de nuit dans ma formule je fais référence à des plages nommés qui sont elles indiqué en heure, me permettant une meilleur lisibilité et des modifications futures plus faciles, or ta formule défini la plage via des chiffres et non des heures, j'ai bien compris que tu as suivi mon modèle, je t'apporte donc la précision.

    Par ailleurs j'ai continué à me creuser la tete sur ma problématique, je te joins ce nouvel exemple qui cible mieux la chose.

    Vivement demain alors. Merci encore.
     

    Pièces jointes:

    Dernière édition: 10 Janvier 2017
  4. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonjour

    Oui, ma formule ne fonctionne pas pour des périodes à cheval sur deux jours. Je sais, mais la méthode proposée est modifiable pour résoudre ce problème.

    Je vais regarder ton nouveau fichier de ce pas.

    @ plus
     
  5. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonjour

    Est-ce que le fichier en pièce jointe te donne les bons résultats ?

    @ Plus

    P.S 1 : Attention, les formules dans les colonnes I et K sont matricielles (à cause de la partie SI(D2="H";1;(B2*24*60+B$9*24*60)<périodeenminutes)), donc à valider avec Ctrl+maj+entrer. Si je trouve une solution sans formule matricielle, je la posterai.
    P.S 2 : Les colonnes E à K sont indépendantes les unes des autres. Je les ai mises juste pour pouvoir vérifier la validité de la méthode utiliser. Tu peux donc en supprimer certaines, si inutiles pour toi. Ou, au contraire, simplifier certaines formules en combinant les résultats des autres colonnes.
    P.S 3 : Cela ne fonctionnera pas si tu as des durées de travail de plus de 24 h. Mais comme je ne pense pas que cela soit le cas :).
    P.S 4 : Je viens de voir qu'il y avait une erreur de frappe dans la formule dans la colonne I, un 34 au lieu d'un 24. C'est corrigé dans la pièce jointe.
     

    Pièces jointes:

    Dernière édition: 10 Janvier 2017
  6. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    19
    "J'aime" reçus :
    0
    Bonjour Cisco,

    Si j'ai bien compris les colonnes F,G,H seront des colonnes cachés, elles sont juste la pour permettre de réaliser le calcul.
    Malheureusement la combinaison marche de façon aléatoire, selon la plage horaire défini elle fait parfaitement job et avec d'autres non.

    J'ai essayé un maximum de combinaison pour voir ou se situe le dysfonctionnement, à priori c'est la colonne I des heures supplémentaire de jour qui ne fait pas le job.
    Je t'ai illustré et commenté l'exemple afin que tu puisse poser ton diagnostic.

    Merci encore et encore :) !
     

    Pièces jointes:

  7. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    Tu n'as pas corrigé le 34 en 24 dans la colonne I.

    D'autre part, je n’avais pas prévu le cas présenté sur la 3 ème ligne de ton dernier fichier. Je corrige les formules en conséquence (du moins j'essaye).

    @ plus
     
  8. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    19
    "J'aime" reçus :
    0
    Désolé Cisco, il n'y avait que ton Edit 1 qui s'affiché, je viens de voir la suite de tes EDIT.
    Donc oui j'ai corrigé la faute de frappe ca marche nickel fabuleux !
    Donc oui le dernier scénario est de pouvoir répartir heure sup de jour/nuit lorsqu'on attaque en nuit et fini en journée.

    Après je ne souhaites pas abuser de ton temps non plus, tu m en as deja consacré beaucoup.

    Mais franchement merci et bravo quoi.
     
  9. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    J'ai l'impression que c'est bon comme ça. Testes davantage et dis moi si c'est OK.

    @ plus

    P.S : Les colonnes E, F et G peuvent être supprimées.
     

    Pièces jointes:

  10. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    19
    "J'aime" reçus :
    0
    Bon ben cette fois-ci j'ai pas réussi à trouver la faille..... t'as gagné... je suis à court de piège.

    Cisco merci merci beaucoup ! Tu m'as regalé ! Vraiment super, je vais garder tes formules très précieusement car assurément elles pourront servir à enormement de monde dans énormement de casde figure, super.

    Merci encore !
     
  11. GILBERTO BRAGA

    GILBERTO BRAGA XLDnaute Occasionnel

    Inscrit depuis le :
    17 Août 2009
    Messages :
    109
    "J'aime" reçus :
    1
    Bonjour à tous, Cisco, Mathieu 18


    Utilisation de la fonction MEDIANE
    Il peut être utile

    Gilberto - Brasil
     

    Pièces jointes:

  12. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    19
    "J'aime" reçus :
    0
    Bonjour,

    Gilberto ta formule à l'air un peu plus simplifié en effet neanmoins elle présente l'inconvénient que les colonnes sont dépendantes les unes des autres, merci pour ta solution.

    Cisco j essaie d'intégrer ta formule dans mon tableau, la plage nommée ''périodeenminutes" correspond bien à aux colonnes B début et C fin ? Si oui c'est quoi que je dois mettre B2:C2 ? C2-B2 ? B$2+C$2 ?

    Merci.
     
  13. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonjour

    Pour ceux que cela intéresse ou qui auront besoin de modifier ma dernière proposition, j'explique un peu la méthode utilisée dans mon dernier fichier.

    Dans H2, on n'utilise pas une formule avec un truc du style SI(condition 1; heure x - heure y ;SI(condition 2;Heure w - heure y ou du style SI(ET(condition 1 ; condition 2); heure g - heure f;...
    mais :

    * On crée une matrice listant toutes les minutes de la période de travail avec
    périodeenminutes=LIGNE(INDIRECT($B2*60*24+1&":"&SI($C2<$B2;$C2+1;$C2)*60*24))
    ce qui donne par exemple {321;322;323;....1200} pour B2 = 5:00 et C2 = 20:00

    * On élimine les minutes ne convenant pas en imposant des conditions . Par exemple, pour n'avoir que les minutes du premier jour entre 6:00 (=B$11) et 22:00 (=B$12) de périodeenminutes, on écrit (B$11*24*60<périodeenminutes)*(périodeenminutes<=B$12*24*60) ce qui donne une matrice de 0 et de 1, du style {0;0;0;0;.......;1;1;1;1......;1;1;1;0;0;.....;0} pour une période començant le matin avant 6:00 et finissant avant 22:00, les 1 correspondant aux minutes de périodeenminutes comprises entre 6:00 et 22:00. (Cf. remarque à la fin de ce post)

    * Si on doit garder une autre période, on l'additionne. Par exemple, pour garder les minutes de la deuxième journée (en supposant que la période ne déborde jamais après les 22:00 du second jour : (B$11+1 donne le début des heures de jour de la seconde journée)
    (B$11*24*60<périodeenminutes)*(périodeenminutes<=B$12*24*60)+((B$11+1)*24*60<périodeenminutes)
    ce qui donne aussi une matrice de 0 et de 1.

    * Si on doit imposer une autre condition, on multiplie la formule précédente par cette condition, ce qui donne par exemple, pour déterminer les heures normales, avec dans B$13 le nombre d'heures normales journalières maximales (7,5 dans le fichier, écrit sous la forme 7:30).
    ((B$11*24*60<périodeenminutes)*(périodeenminutes<=B$12*24*60)+((B$11+1)*24*60<périodeenminutes))*(périodeenminutes<=((B2+B$13)*24*60))
    ce qui donne aussi une matrice de 0 et de 1.

    Autrement dit, il faut "jouer" avec des * et des + entre des inégalités, suivant qu'il s'agit d'un ET ou d'un OU.

    Finalement, il ne reste plus qu'à faire le total des 1 avec SOMMEPROD et à diviser cela par 60 et par 24 pour avoir cela en durée utilisant le jour comme unité, mis au format heure à l'affichage.

    Pour avoir les heures de travail de jour, mais sup, on prend presque la même formule, en changeant un peu le dernier terme (puisqu'on ne veut plus garder les heures avant la durée B$13, mais après), ce qui donne
    ((B$11*24*60<périodeenminutes)*(périodeenminutes<=B$12*24*60)+((B$11+1)*24*60<périodeenminutes))*((B2+B$13)*24*60< périodeenminutes)

    Pour avoir les heures de travail de nuit, normales, on fait avec
    ((périodeenminutes<=B$11*24*60)+(B$12*24*60<périodeenminutes))*(périodeenminutes<=(B$11+1)*24*60)*(périodeenminutes<=((B2+B$13)*24*60))

    On voit bien qu c'est le même genre de formule, mais l'ordre des inégalités ayant été modifié, certaines parenthèses ayant été déplacées, des * remplaçant des +, etc...

    @ plus

    Rem : Si on n'a pas beaucoup de conditions, on doit pouvoir faire plus court avec un FREQUENCE car on a
    SOMMEPROD((B$11*24*60<périodeenminutes)*(périodeenminutes<=B$12*24*60)) = INDEX(FREQUENCE(périodeenminutes;B$11:B$12*24*60);2). Dans le cas présent, je ne vois pas comment rajouter simplement la condition pour faire la différence entre les heures normales et les heures sup avec la formule utilisant la fonction FREQUENCE.
     
    Dernière édition: 13 Janvier 2017
  14. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    On doit certainement pouvoir tout écrire en utilisant que des formules du style de celle utilisant la fonction MEDIANE, mais je trouve que c'est moi explicite que la méthode que j'explique dans mon post précédent, où chaque condition est bien visible sous la forme d'une inégalité.

    Regarde la définition de périodeenminutes dans le gestionnaire de noms. Il suffit de remplacer dedans B2 et C2 par les références du début et de la fin de ta période de travail sur la ligne en cours.

    Si la transposition vers ton vrai fichier te pose trop de problème, mets en un extrait sur le forum, que j'essaye de faire le travail pour toi.

    @ plus
     
    Dernière édition: 11 Janvier 2017
  15. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    19
    "J'aime" reçus :
    0
    Alors oui je viens de trouver : =LIGNE(INDIRECT(Feuil1!$B11*60*24+1&":"&SI(Feuil1!$C11<Feuil1!$B11;Feuil1!$C11+1;Feuil1!$C11)*60*24))

    J'ai essayé de le coller par dessus periodeenminutes dans la formule mais ca ne fonctionne pas !

    Alors 2 choses, oui je peux te partager mon document de travail mais il est fait sur google sheets et non sur excel.

    La seconde chose c'est que plutôt d'avoir tout ceci condenser dans une plage nommé, que tu m'auras caler dans mon tableau, ca me serait plus facile de l'avoir formulé complètement avec les plages tout ça (comme le reste de ta formule quoi) afin que je puisse avoir la maitrise de la formule, si un jour je suis mener a la déplacer ou à l'intégrer dans un autre tableau je n'aurai plus qu'à modifier les feuille 1, feuille 2, B2, C3 etc... Alors que la avec Ligne(indirect qui me renvoie toujours à la colonne B C c'est vrai que c'est efficace mais j'avoue etre dépasser et n'ai pas envie de venir t embeter à chaque nouveau tableau que je ferai :p !

    Merci merci Cisco.
     
  16. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    Si tu mets la définition de périodeenminutes directement dans les formules, il faut toutes les valider en matriciel, pour que la partie LIGNE(INDIRECT(...&":"&...)) liste bien toutes les minutes de B2 à C2 lorsque tu as sélectionné une cellule de la ligne 2.

    A mon avis c'est bien plus pratique d'utiliser un nom, puisque la partie correspondante se retrouve très souvent dans les formules.
    Si tu procèdes sans le nom périodeenminutes, à chaque changement de ref, il faut "rentrer" toutes les modifications dans H2, I2, J2 et K2, et plusieurs fois dans chacune de ces formules. Si tu fais avec, il suffit de faire les changements dans le gestionnaire de noms (uniquement le B et le C à changer si tu es sur la ligne 2).

    Ne peux tu mettre sur le forum une pièce jointe Excel en positionnant les colonnes comme sur ton fichier réel, avec juste quelques lignes ?

    @ plus

    P.S : J'ai un peu simplifié la partie correspondant à périodeenminutes sous la forme LIGNE(INDIRECT($B2*60*24+1&":"&(($C2<$B2)+$C2)*60*24))
     

    Pièces jointes:

    Dernière édition: 11 Janvier 2017
  17. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    19
    "J'aime" reçus :
    0
    Ca y est je viens de réussir à l intégrer a mon tableau, absolument fabuleux ! Je te remercierai jamais assez Cisco ! Tu as pris le temps et tu m'as pas laché malgré toutes mes exigences, franchement super de ta part, merci encore.

    Pour te répondre, si j'ai bien compris ce que tu dis. Alors oui je comprends bien que ta manière est bien plus performante mais pour ma part voila 3 semaines que je me suis mis à faire des tableaux sans aucune formation ou cours, j'ai la chance d etre tres familier avec le monde informatique donc ca m'aide un petit peu à apprehender tout ceci.
    Le tableau que je fabrique m'a contraint à m'orienter vers des mécaniques et formules qui ne sont pas du tout de mon niveau, j'ai reussi à passer des formules sans meme trop savoir ni comment ni pourquoi.. Donc une fois que je l'aurai fini et qu'on pourra l'exploiter, la je prendrai afin le temps de me former aux bases d'excel et pouvoir comprendre et définir tout ce que j'ai réalisé.
    Tout ceci pour t'expliquer que pour le moment je dois me contenter de formule et mécanique que j'arrive à exploiter meme si elles s'averent contre productive plutot que de mécaniques super performante que je serai incapable d'exploiter dans des modifications futures.
    Quand je serai plus au niveau j y reviendrai certainement pour optimiser ma méthode comme tu me l'as si bien démontré.

    Voila donc ma formule dans mon tableau sheets : =ArrayFormula(SI(C6="H";"";SOMMEPROD(((LIGNE(INDIRECT($D6*60*24+1&":"&(($E6<$D6)+$E6)*60*24))<=DébHeuJou*24*60)+(FinHeuJou*24*60<LIGNE(INDIRECT($D6*60*24+1&":"&(($E6<$D6)+$E6)*60*24))))*(LIGNE(INDIRECT($D6*60*24+1&":"&(($E6<$D6)+$E6)*60*24))<=(DébHeuJou+1)*24*60)*(LIGNE(INDIRECT($D6*60*24+1&":"&(($E6<$D6)+$E6)*60*24))<=(D6*24*60+NomHeuNor*24*60)))/60/24))

    Avec des copier coller et des glissements je pourai le dupliquer dans mon tableau tres facilement, je l'ai construit pour. Pour ce qui est de modifier les plages horraires DébHeuJou, NomHeuNor,FinHeuJou me permettront d'ajuster le curseur à ma guise dans tout le tableau.

    Je garde précieusement toutes tes formules qui assurément me serviront toute ma carrière et puis j'espère d ici quelques temps pouvoir comprendre au moins 10 pourcent de ton poste qui explique comment tu as construit la formule :p !


    Cisco, la metropole aix-marseille, le service piscine, le pôle technique et l'ensemble d'abrutis qui me servent de collegue de travail nous te remercions tous vivement !
     
  18. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    Si ça fonctionne... c'est OK.

    Au plaisir.

    @ plus
     
  19. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    9427
    "J'aime" reçus :
    286
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    Mais que fait le maitre nageur.. ;) ?

    @ plus
     
    Dernière édition: 11 Janvier 2017
    Mathieu18 aime votre message.
  20. GILBERTO BRAGA

    GILBERTO BRAGA XLDnaute Occasionnel

    Inscrit depuis le :
    17 Août 2009
    Messages :
    109
    "J'aime" reçus :
    1
    Bonsoir à tous,

    Bonsoir Cisco

    Je te serai toujours reconnaissant de l'aide que tu m'as donné lors du sujet " des heures nocturnes".

    Je vous pose une question : est-ce possible adapter les formules dans le cas qu'un travailleur travaillant, par exemple, entre 18 et 22 h, et entre 23 et 5 h ( l'intervalle ce n'est pas pris en compte dans la journée).

    Merci d'avance. Bien cordialement.

    Gilberto
     

Partager cette page