IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Calcul heures semaine [XL-2016]


Sujet :

Excel

  1. #1
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut Calcul heures semaine
    Bonjour,

    Je suis nouveau sur Excel et j'aimerais faire un tableau qui me permettrait de calculer le nombre d'heures de travail par semaine faite par mes employés
    Il faut savoir que s'ils travaillent jusque midi, 13h ou 14h00, ils n'ont pas de pause sinon ils ont droit à une pause d'1/2h.
    Quelqu'un pourrait m'aider ?

    J'ai essayer de faire ceci , voir piece jointe mais cela marche pas, à partir de la cellule H5--->M5 les heures s'enlèvent et le calcul est bien sûr faux

    Merci pour votre réponse

    ED
    Fichiers attachés Fichiers attachés

  2. #2
    Membre émérite
    Femme Profil pro
    Ingénieur
    Inscrit en
    Octobre 2016
    Messages
    1 703
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 29
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2016
    Messages : 1 703
    Points : 2 813
    Points
    2 813
    Par défaut
    Bonjour et bienvenue sur le forum !

    Avant de te proposer quelque chose, petite remarque : évite de mettre des fichiers joints, essaie plutôt de nous expliquer ton problème et de décrire et écrire les formules que tu utilises. Peu de personnes ouvrent les pieces joints, en particulier lors du premier message.

    Pour ta question : essaie de d'utiliser NB.SI.ENS() plutôt que seulement NB.SI(). Cela te permet de mettre plusieurs conditios et ainsi, tu pourras mettre <12 et <>0 (ou ESTVIDE() = FAUX). Tu peux aussi essayer d' utiliser NB.SI() avec une condition ET().
    D'autre part : j'ai l'impression que tu prends en compte les heures de début dans ta condition <12h, est-ce normal?

  3. #3
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut
    Merci pour ta réponse et pour le conseil

    Non en effet je me suis trompé pour >12h

    Pourrais-tu me donner un exemple avec NB.SI.ENS() , je galère là !

    Merci

    ED

  4. #4
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    Bonjour,
    Il suffit de remplacer < par > dans la formule pour avoir un résultat correct à condition qu'aucun employé ne commence à travailler l'après-midi.
    D'après le message, je m'attendais à voir 14:00 et non 12:00 en R1.
    Cordialement
    Claude

  5. #5
    Membre émérite
    Femme Profil pro
    Ingénieur
    Inscrit en
    Octobre 2016
    Messages
    1 703
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 29
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2016
    Messages : 1 703
    Points : 2 813
    Points
    2 813

  6. #6
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut
    Je remarque que dans mon exemple à partir du moment où les heures de travail dépassent 24h00, le total se remet à 0. J'ai dû oublié quelque chose ?

  7. #7
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    Re,
    La cellule doit avoir le format personnalisé [h]:mm.
    Cordialement
    Claude

  8. #8
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut
    Merci Claude, riaolle : cela marche... Je commence à voir le bout du tunnel

    Par contre, si j'ai des gens qui commence à et/ou 14h00, est-il possible de que la 1/2h ne soit pas retiré ?

    =SOMME((M5-L5);(K5-J5);(I5-H5);(G5-F5);(E5-D5);(C5-B5))-(NB.SI(C5:E5:G5:I5:K5:M5;">"&R1)*0,5/24)


    R1 = 14:00

  9. #9
    Membre émérite
    Femme Profil pro
    Ingénieur
    Inscrit en
    Octobre 2016
    Messages
    1 703
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 29
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2016
    Messages : 1 703
    Points : 2 813
    Points
    2 813
    Par défaut
    Si tu utilises NB.SI.ENS() tu peux mettre plusieurs critères de sélection. Du coup, tu peux mettre un critère sur les cellules de début de service.
    Ensuite, je ne suis pas sûre que ta syntaxe "C5:E5:G5:I5:K5:M5" soit très correcte. Je pense qu'il vaut mieux que tu utilises plusieurs NB.ENS.SI et que tu traites chaque cellules à part, en attribuant à chaque cellule le critère qui lui correspond.

    Voici un exemple:
    Nom : nbsiens2.png
Affichages : 8403
Taille : 3,7 Ko
    Tu traites chaque service avec un NB.SI.ENS() et chaque heure avec son critère.

  10. #10
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    Re,
    Autre proposition pour la cellule O5:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME((M5-L5);(K5-J5);(I5-H5);(G5-F5);(E5-D5);(C5-B5))-SOMMEPROD((B5:M5>R1)*EST.IMPAIR(COLONNE(B5:M5))-(B5:M5>R1)*EST.PAIR(COLONNE(B5:M5)))/48
    (le calcul " .../48 " est une simplification de " ...*0,5/24 " , l'écriture originale restant plus explicite)
    Cordialement
    Claude

  11. #11
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut
    Bonsoir Claude,

    Je pense qu'il y a quelque chose qui ne fonctionne pas car à partir du jeudi les heures dans la cellule O5 se remettent à zéro. Du lundi au mercredi aucuns soucis, mais dès que j'entre des heures dans jeudi--vendredi--samedi cela se met à zéro dans la cellule O5 pour le total de la semaine

    Merci pour votre réponse

    ED

  12. #12
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    Bonjour,
    La cellule est-elle bien au format personnalisé [h]:mm ou est-elle de nouveau au format Heure ?
    Cordialement
    Claude

  13. #13
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut
    Bonjour, la cellule est bien en [h]:mm

    Nom : horaire semaine.jpg
Affichages : 9396
Taille : 369,4 Ko

  14. #14
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Salut.

    Pour moi, on a un problème de conception de classeur.

    Règle capitale d'Excel: Ne pas mélanger les données et leur analyse.

    La saisie des données s'effectue dans un tableau de cinq colonnes, et un tableau récapitulatif soit en TCD (tableau croisé dynamique) soit avec somme.si.ens qui réalise le travail d'agrégation. C'est un million de fois plus simple et tu ne crées le semainier qu'une seule fois, quelle que soit la semaine que souhaites afficher. Ton fichier peut même servir plusieurs années.
    Si tu choisis l'option d'un tableau formulé plutôt qu'un TCD, voici les étapes à réaliser.

    1. Création du tableau (on remarque la formule en E qui calcule la durée)

    Nom : 2017-03-14_212009.png
Affichages : 24853
Taille : 38,5 Ko


    2. Mise en place du semainier (on remarque en C1 la formule qui incrémente la date saisie en B1 => il suffit de modifier cette date pour avoir les données d'une autre semaine). Les cellules B2:I4 sont au format [H]:MM

    Nom : 2017-03-14_212457.png
Affichages : 8653
Taille : 14,8 Ko



    3. Création de la formule SOMME.SI.ENS (Attention, à recopier via Ctrl+C puis coller et pas par le bouton de recopie, car les colonnes du tableau deviennent alors relatives!!!... Oui, je sais, c'est une connerie des concepteurs d'Excel )

    Nom : 2017-03-14_212100.png
Affichages : 8812
Taille : 14,5 Ko


    4 Formule du total = une simple somme
    Nom : 2017-03-14_213258.png
Affichages : 8630
Taille : 5,9 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  15. #15
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut
    Merci Pierre,

    Etant vraiment nouveau dans Excel, je dois dire que pour moi c'est un peu du chinois.
    Pourriez-vous me donner un fichier .xlsx pour que je puisse décortiquer la mise place du tableau et de ses formules ?
    Merci

    ED

  16. #16
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Voici un fichier avec trois feuilles: données, semainier (formulé) et TCD (très puissant). J'ai ajouté le calcul du numéro de semaine pour simplifier la sélection des données dans le TCD

    Nom : 2017-03-15_072308.png
Affichages : 9668
Taille : 10,3 Ko
    Fichiers attachés Fichiers attachés
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  17. #17
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut
    Merci Pierre, cela m'aide...mais c'est un peu complexe
    Sachant qu'en fait je n'ai besoin que d'un planning reprenant le nom de l'employé son heure d'entrée et de sortie et le total d'heure sur la semaine. En retirant 1/2h sur la journée s'il travaille plus tard que 14h.

    Question comment je peux faire pour obtenir un tableau ressemblant plus à mon exemple, avec le même type de formule que celle donnée par votre fichier ?
    Nom : horaire semaine ex.jpg
Affichages : 8443
Taille : 77,8 Ko


    Merci pour votre aide et votre réponse

    ED

  18. #18
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Ce que fait mon classeur est ce que tu demandes et c'est bien moins complexe que ce que tu essaies de faire (la preuve, le nombre de messages dans cette discussion).

    Cela te semble complexe parce que tu ne connais pas les moyens mis en oeuvre, qui sont très simples.

    Le calcul de la demi-heure de midi est réalisé dans la colonne E du tableau de données. La fonction SOMME.SI.ENS, qui peut sembler complexe, est une fonction très importante et très intéressante d'Excel.

    Ma proposition permet de créer ton semainier pour n'importe quelle semaine de n'importe quelle année, alors que tu vas devoir dupliquer ton tableau pour toutes les semaines que tu dois encoder.

    La mise en place du tableau de données permettra en plus d'autres analyses sans réencodage des données alors que tes données, telles que saisies dans ton tableau, ne peuvent servir que pour ce tableau.

    Je rappelle la règle capitale d'Excel: Ne pas mélanger les données et leur présentation.

    Crois-moi, ma solution est mille fois plus simple que ton tableau, elle est pérenne et respecte les règles de bonne conception.

    Si tu débutes en Excel, prends de suite les bonnes habitudes de conception du classeur. Tu ne pourras qu'y gagner.

    Cela étant, libre à toi de faire compliqué...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  19. #19
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Voilà un exemple qui récupère les données d'entrée et de sortie... Attention que je n'ai tenu compte que du numéro de la semaine. Il faudrait normalement aussi tenir compte de l'année, et calculer le lundi en fonction de l'année et du numéro de semaine.

    Nom : 2017-03-15_083257.png
Affichages : 8502
Taille : 10,3 Ko

    Nom : 2017-03-15_083313.png
Affichages : 8541
Taille : 11,5 Ko
    Fichiers attachés Fichiers attachés
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  20. #20
    Candidat au Club  
    Homme Profil pro
    manager
    Inscrit en
    Mars 2017
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : manager
    Secteur : Alimentation

    Informations forums :
    Inscription : Mars 2017
    Messages : 9
    Points : 3
    Points
    3
    Par défaut
    Merci Pierre,

    Je comprend bien la démarche et la simplicité que tu as apporté. Et je prend évidemment, je préfère bien commencer que de faire les choses à l'envers.
    Ce que je n'ai peut-être pas mentionné c'est que ce tableau je dois le sortir sur papier chaque semaine pour l'afficher aux valves. Donc il est important pour moi de pouvoir avoir la sortie comme dans mon exemple.
    Avec tous les membres du personnel, heures entrées/sorties et total sem.

    Mais je vais essayer de me dépatouiller, reprendre ton exemple et l'appliquer à mon besoin.
    Je te remercie pour ta patience, ton aide et tes conseils

    Bonne journée à Toi

    ED

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. [MySQL] Calculer un nombre d'heure / semaine à cheval sur 2 mois !
    Par phapha89 dans le forum PHP & Base de données
    Réponses: 1
    Dernier message: 08/12/2010, 18h40
  2. requete calcul heures travaillées par semaine
    Par ben35 dans le forum Modélisation
    Réponses: 2
    Dernier message: 09/06/2008, 10h56
  3. [Formule] Calcul Heures AVEC EXCEL
    Par JLDEB dans le forum Excel
    Réponses: 2
    Dernier message: 16/04/2007, 18h08
  4. Calcul Heure format "hh:mm"
    Par bnisaid dans le forum VB 6 et antérieur
    Réponses: 3
    Dernier message: 26/03/2007, 11h56
  5. Calcul heure UCT par rapport à heure locale
    Par bobic dans le forum Oracle
    Réponses: 1
    Dernier message: 11/05/2006, 23h45

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo