Calcul sous certaines conditions

Bonjour à tous,

Je viens de me lancer dans un nouveau fichier de suivi des heures(''pointage''). J'ai commencé à renseigner les premiers éléments, jours fériés et week-ends en tête, mais je bloque désormais sur une formule.

Je souhaiterais demander à Excel de calculer le nombre d'heures réalisées dans la semaine (du lundi au dimanche) mais que le total associé apparaisse idéalement au sein d'une seule case fusionnée (toujours du lundi au dimanche, en colonne ''I''). Si cela n'est pas réalisable, je souhaiterais que le total apparaisse uniquement les dimanches, Je sais que cela est possible, car j'ai trouvé le modèle ''Tableau-Excel-pour-calculer-heures-de-travail" en cherchant la solution, bien que ce ne soit pas exactement le même système de sélection de date. Toutefois, je ne suis pas en mesure de déchiffrer les données contenues au sein dudit fichier, celui-ci étant protégé.

Pouvez-vous s'il vous plaît m'aider à trouver la solution ?

Merci par avance à tous ceux qui participeront à la résolution de ma demande. J'espère avoir apporté suffisamment d'élément et avoir été clair dans mon énoncé.

Post-scriptum : Ne prêtez pas attention aux onglets de février à décembre, ni à l'onglet ''synthèse", ceux-ci ne sont pas encore viable.

Bonjour,

pas sûr d'avoir compris car il suffit de mettre ta somme en I3 =SOMME(F3:F9)
et de tirer vers le bas ta plage fusionnée.
eric

Merci pour ta contribution Éric, mais cela ne fonctionne pas comme cela. En effet, si je change le mois ou l'année du fichier, les cellules fusionnées restent identiques quand les jours de la semaine ont évoluées. Vous pouvez essayer cela en sélectionnant le mois de février à partir du menu déroulant en A1 du mois de janvier. Vous remarquerez que le 1er février 2024 est un jeudi, et non un lundi.

Je n'ai peut-être pas été suffisamment clair dans mon énoncé, je m'en excuse.

Je souhaite une mise forme conditionnelle qui définit automatiquement quels sont les jours de la semaine et qui calcule les heures réalisées du lundi au dimanche (sur chaque semaine d'un mois). Idéalement, je souhaite une cellule qui fusionne les 7 jours de la semaine, dans un soucis de lisibilité semaine par semaine, mais cela n'est pas particulièrement bloquant.

bonjour Polymer, Eriiic,

cette feuille est assez bien verrouillé, c'est pourquoi je la déteste et je l'ai copié et déplacé.

Maintenant, vous pouvez utiliser des TCD ou des formules365 (voir feuille TCD)

Bonjour BsAlv,

Merci beaucoup ! je vais pouvoir étudier le fichier et le modifier selon mes besoins.

Si vous n'avez pas d'objection, je laisse le sujet comme non résolu, afin que d'autres puisse participer s'ils le souhaitent.

Bonjour

C'est sûr qu'avec des semaines de 3 jours ça mache moins bien

Ou bien faire une vraie BDD avec toutes les dates sur la même feuille et filtrer le mois voulu si besoin (mais bon, laisser le scrolling sur le mois en cours est suffisant)
Tu as de quoi tenir 2870 ans...
Ensuite tu peux faire toutes les stats et analyses que tu veux sans difficulté.
eric

Bonjour Eric,

C'est une idée, mais je souhaitais vraiment conserver le modèle mensuel, ce qui semble toutefois être possible avec votre tableau.

Je souhaite également préservé le modèle de sélection d'année et de mois pour une utilisation plus ergonomique. Je ne serais pas le seul à utiliser le fichier une fois celui-ci finalisé.

Sur le fichier déverrouillé par BsAlv, la formule permettant de calculer le total des heures de la semaine écoulée est : =SI(JOURSEM([@date];2)=7;SOMME.SI([ISO semaine];[@[ISO semaine]];[Total heures travaillées par jour]);"")

J'ai ajouté une colonne pour définir le numéro de la semaine, puisqu'il semblerait que cela soit nécessaire.

Je ne parviens pas à l'adapter sur mon document. J'ai essayé celle-ci : =SI(JOURSEM(A9;2)=7;SOMME.SI([Semaine];[@[Semaine]];[Total]);""), pour remplacer dans le texte les références de mon tableau, mais cela m'indique une erreur.

J'ai essayé celle-ci : =SI(JOURSEM(A9;2)=7;SOMME.SI($B3;$B3;$G3);""), mais cela me renvoi uniquement ''07:45'', soit le total des heures travaillées le lundi, et non la semaine entière.

La première partie de la formule, =SI(JOURSEM(A9;2)=7;SOMME.SI, est exacte, mais il semblerait que la seconde soit plus difficile à mettre en œuvre.

Je sollicite à nouveau votre aide. Merci encore pour le temps accordé. Je ne peux vous rendre la pareil sur Excel, mais si vous avez quelques questions sur Factorio, je peux peut-être vous aider :)

le fichier d'Eriiic = un tableau mais avec des segmants, donc vous pouvez sélectionner un/plusieurs mois ou semaines dans ces segments.

J'y avais pensé mais comme il semblait préférer la fusion de cellule (à proscrire le plus souvent bien sûr)...
Je pense que c'est la meilleure solution

re,

la fusion des cellules, quelle misère

Ce n'est pas tant que je préfère la fusion de cellule, simplement, mes connaissances ne me permettent pas d'entrevoir autre chose. Pouvez-vous m'expliquer en quoi est-ce à proscrire idéalement ?

J'ai plusieurs petites questions concernant votre réalisation :

Comment fait-on pour configurer la mise en page du tableau et que cela se répercute sur tous les tableaux sélectionnés ? Par exemple, je souhaiterais avoir ''20'' en hauteur de ligne, que seuls les week-end et jours fériés soient grisés (et non, une ligne sur deux) et

Comment fait-on pour modifier le format de cellule pour que dans la colonne semaine, il soit affiché ''1" et non ''24-01'' ? Idem pour les mois.

Est-il possible de ''fixer'' les volets ''mois'' et ''semaine'', situés à droite du tableau, directement dans le fichier Excel ?

Sous ce format, est-il toujours possible de créer un tableau de synthèse par mois et par année ? Les mois non affichés conservent ils leurs données ?

Jusqu'à quelle date est paramétré le document ? Est-ce 2026 ? Est-il possible d'ajouter un volet ''année" au dessus de celui de mois ? Parfois, j'ai besoin de savoir quels seront les jours fériés pour une année bien définie, c'est pour cela.

Comment puis-je réinitialiser à 0 toutes les saisies réalisées ?

Mon onglet "détails'' est-il toujours nécessaire ?

Désolé de vous demander tout cela, mais certaines fonction d'Excel restent assez obscures pour moi.

re,

les cellules fusionnées causent partout des problèmes en comptage ou en décalage, c'est un outil intéressant pour la mise en plage (visuelle) mais il faut essayer à l'éviter si possible. En autre, cela cause des problèmes entre les mois et les semaines. On a un sous-total le dimanche, mais 1 fois sur 4 à 5, ce dimanche est dans un autre mois/année. C'est préférable de calculer les 2 (semaines et mois) séparament, donc un sous-total par mois est la somme du mois complet et pas quelque jours en plus au début du mois en en moins à la fin du mois.

L'hauteur des lignes, vous pouvez le faire comme l'habitude, les couleurs du tableau, c'est la mise en page du tableau, donc facile à modifier, les weekends sont pour le moment en gris, les fériés en vert, cela sont des MFCs (mise en format conditionnelle)

Le "1" à "9" ou lieu de "01" à "09", c'est en changeant le contenu des colonnes A:B (soit la formule, soit le format), mais cela donnera des problèmes de triage dans les segments (voir les semaines, on a "24-1", puis "24-10" à "24-19", puis "24-2" et "24-21" à "24-29", puis les 3's). Donc ce "01" est plutôt une solution pratique.

Fixer les segments des mois et semaines, cela est difficile, si vous défilez vers le bas, les premières lignes restent visible, le reste disparaît quand on est trop en bas. Je ne sais pas si quelqu'un a une solution pour cela.

Maintenant on a 950 lignes dans ce tableau = 2,5 années. Vous pouvez ajouter ou supprimer des lignes. Maintenant la colonne C sont des dates en formules, ce serait mieux de remplacer ces formules par des valeurs fixes. Si vous filtrer le tableau, les données restent présent, mais dépendant de la formule utilisée, ces données seront dans la calculation ou pas. Par exemple la première ligne avec ses formules sous-total compte ou fait la somme des cellules visibles. Les formules de la feuille "Synthèse" colonnes I:J font la somme de toutes les cellules (visibles ou cachées).

Pour sélectionner une année complète, il faut apprendre à utiliser ces segments, par exemple, on commence en supprimant toutes les sélections actives avec le croix rouge en haut à droit du segment, puis on clicque sur 24-1 (pour janvier 2024) et puis en appuyant sur le "Maj" on clicque aussi sur "24-12" (pour Décembre 2024) et on a toute l'année 2024. (C'est pourquoi le triage a une importance). Vous voyez aussi que si vous choississez quelque chose dans le segment des mois, que cela provoque des changements au niveau du segments des semaines et vice versa.

Je ne sais pas pourquoi vous voulez réinitialiser la feuille. Pour une autre personne ? Alors on ajoutera une feuille pour lui/elle.

L'onglet "détails" contient maintenant les jours fériés pour les années 2024-2034. Si vous voulez le déplacer vers par exemple la feuille "Synthèse", vous pouvez supprimer cette feuille.

C'est peut-être beaucoup de nouvelles choses en même temps, mais cela vaut la peine !!!

Merci BsAlv, pour toutes ces précisions. J'ai appris beaucoup de choses grâce à vous.

Pouvoir réinitialiser les données renseignées, ce n'était qu'une idée, il est vrai que cela aurait peu d’intérêt.

Pour ce qui est des jours fériées, est-il nécessaire de les pré-calculer à l'avance ? Les formules que j'avais réalisé semblaient fonctionner, j'ai vérifié jusqu'en 2040. je pensais qu'une seule colonne pouvait suffire en fonction de l'année sélectionnée.

en quoi est-ce à proscrire idéalement ?

En dehors du fait que ça pose des problèmes à savoir où sont les données, en vba ça nécessites autant d'exceptions à traiter (plus de régularité dans les données) et rallonge d'autant le code,
essaie de copier la ligne du 4 janvier pour la coller ailleurs.
Même galère pour copier une colonne avec une fusion horizontale.
Ce sont pourtant des opérations courantes.
Il faut les réserver pour des titres, des mise en page d'infos, pas sur les données.

re,

Les formules que j'avais réalisé semblaient fonctionner, j'ai vérifié jusqu'en 2040. je pensais qu'une seule colonne pouvait suffire en fonction de l'année sélectionnée.

Les jours fériés de 2024 à 2034 (11 années) est peut-être éxagéré, mais 3 années est le minimum (vue le nombre de jours, janvier 2024 à août 2026, dans le tableau).

Si vous déplacez cette plage vers un coin perdu ou ..., pourquoi pas, si vous cachez la feuille "Détails", vous ne la voyez plus mais elle reste active.

Je m'interroge encore sur un point. Comment se fait-il que la formule =SI(JOURSEM([@Date];2)=7; SOMME.SI([Semaine];[@Semaine];[Total]);0), ne fonctionne pas lorsqu'on la transpose ainsi =SI(JOURSEM(A3;2)=7;SOMME.SI($B3;@$B3;$G3);"") dans ce tableau ? Excel ne semble pas prendre en compte les [] et j'ai bien dans mon tableaux les références 'Semaine'' et "Total". Est-ce par que ce n'est pas à proprement parlé un format tableau ?

Il semblerait que la première partie de la formule, =SI(JOURSEM(A3;2)=7;SOMME.SI(, soit exacte, mais pas la seconde lorsqu'on retranscrit ''Semaine'' par la colonne correspondante. Que représente le "0" à la fin de la formule ? Je l'ai remplacé par "", autrement, il affiche une somme pour chaque case, et non uniquement sur les dimanches.

Cette formule : =SI(JOURSEM(A9;2)=7;SOMME($G3:$G9);""), fonctionne uniquement si le premier jour du tableau est un lundi. Peut-être est-il possible de demander à faire la somme des 7 lignes situées au-dessus.

Vous avez résolu ma demande initiale, mais je souhaite désormais comprendre plus avant comment fonctionne Excel (en autodidacte et avec votre concours).

dans le fichier que vous avez transmis, il n'y a pas un tableau structuré, donc on la formule ne comprend pas ce qu'elle doit faire. [@Date] réfère à la date dans la même ligne de ce tableau, [Semaine] = toute la colonne "Semaine" du tableau, mais comme il n'y a pas de tableau, il ne se passe rien.

maintenant dans la cellule I3 vous voyez cette formule qui fonctionne. Il faut savoir que les mois suivants, le premier dimanche ne sera pas toujour le 7eme, donc si vous voulez faire la somme des 6 lignes précédentes + cette ligne de la colonne G, vous pouvez commencer avant la ligne 3 (encore possible), mais votre dimanche se trouve avant la ligne 7, vous essayez à faire la somme avec une ou plusieurs cellules hors cette feuille. Donc, vous devez utiliser une plage dynamique et on peut faire cela avec la fonction "DECALAGE" https://support.microsoft.com/fr-fr/office/decaler-decaler-fonction-c8de19ae-dd79-4b9b-a14e-b4d906d1...

formule de I3 est =SI(JOURSEM(A3;2)=7;SOMME(DECALER(G3;-MIN(6;LIGNE()-3);;MIN(7;LIGNE()-2);));0)

le plus important, c'est cette partie : DECALER(G3;-MIN(6;LIGNE()-3);;MIN(7;LIGNE()-2);)

premier argument = la cellule de référence est G3

2eme argument (en rouge), pour savoir le début de la plage à cumuler >>> on se décale normallement 6 lignes en haut, donc -6 (ce "-" se trouve devant le "min"), mais si vous êtes au début de la feuille, votre plage ne peut pas commencer avant la ligne 3, donc en I3 ligne()-3=0, on ne se décale pas vers le haut.Donc en rouge = on commence à faire la somme 6 lignes avant la ligne de la formule sauf si cela se trouve avant la ligne 3, on prend 3 comme la limite inférieure

3eme argument est pour la décalage des colonnes, mais comme on reste dans la même colonne, elle est vide = on l'ignore

4eme argument (en bleu) = nombre de cellules (toujours un chiffre positif) pour faire la somme = normallement 7 (6 précédent + la ligne de la formule) mais comme on utilise la ligne 3 comme limite inférieur, il faut en tenir compte et ce chiffre est la valeur du 2eme argument multiplié avec -1 et +1.

4eme argument est de nouveau pour les colonnes, et ici donc à ignorer.

à partir de la c'est plus facile à comprendre, la formule est =SI(JOURSEM(A3;2)=7;SOMME(ce décalage);0) >>> si on est le dimanche, on fait la somme de la plage déterminé par ce formule de décalage qui sera entre 1 et 7 cellules. Si ce n'est pas un dimanche, on prend 0 (ou comme vous le voulez "")

Voir colonne J de Janvier, colonne M est la même formule sans réspecter le dimanche

Plus tard, dès que vous comprenez cette formule, on cachera les zéros avec un format personnalisé ou avec une MFC (pour les jours <> dimanche).

Vos explications sont concises, ce qui est une prouesse au regard de la complexité (pour moi, visiblement pas pour vous) et du potentiel d'Excel. Merci beaucoup.

Si je comprend bien, la colonne M, n'est présente qu'à titre informatif et n'entre dans aucune formule ?

Pouvez-vous m'expliquer ce pourquoi il y a deux ";;" dans la formule ? Qu'est ce qu'indique le second point virgule ?

Et existe-t-il une formule qui permette, au dernier jour du mois, de faire le même calcul que pour les dimanches ? Par exemple, une formule FIN.MOIS qui définit automatiquement le dernier jour du mois sélectionné et applique le calcul en conséquence ? Ou peut-être faut-il simplement inverser votre formule en décaler ?

merci pour les compléments

le lien que j'avais ajouté contenait cette formule DECALER(réf, lignes, colonnes, [hauteur], [largeur]) (mais comme c'était une traduction de l'anglais en français, on a oublié de remplacer les "," par des ";"). Donc on voit bien que cette formule a 5 paramètres, donc 3 obligatoire (ceux avec [] sont facultatif). Peut-être difficile à comprendre mais comme on restait dans la même colonne pour notre cumul, on pouvait ignorer le 3eme et 5eme paramètre, mais en ignorant, excel assigne le valeur 0 au 3eme paramètre (donc aucun décalage de colonne) et 1 au 5eme paramètre (le nombre de colonne de la réf, ici c'était une cellule, donc 1 colonne)

Et oui, colonne M ne servait à rien sauf exemple. Maintenant je fais la même chose dans les colonnes suivantes.

J'ai modifier les cellules G2 et G34 en -999999. Excel a des problèmes avec des temps négatifs, donc si le cumul est dehors les lignes 3-33, cela donnera une erreur

colonne N : =SI(JOURSEM($A3;2)=1;SOMME(DECALER($G3;;;MIN(7;FIN.MOIS($A3;0)+1-$A3);));"-") Vous vouliez l'inverser du décalage, donc on demande lundi le cumul de la semaine (mais on a de la chance que le premier janvier était un lundi !!! Seulement à la fin du mois on aura un petit problème, parce que là, on ne peut pas prendre les 7 jours, mais le nombre de jours à partir du dernier lundi jusqu'au dernier jour (inclu) ). Bon, la formule, si le jour est un lundi, on commance à compter à partir de ce jour (2eme et 3eme paramètre sont ignoré(=vide) parce que vous voyez là 3 point-virgules) et pour le 4eme paramètre (en rouge) on prend le petit valeur de 7 et le nombre de jours jusqu'au fin du mois +1, et on ignore le 5eme paramètre

colonne O : =SI($A3=FIN.MOIS($A3;0);SOMME(DECALER($G3;-JOUR($A3)+1;;JOUR($A3);));"-"). Vous vouliez savoir le dernier jour du mois, c'est avec la formule Fin.mois(une date, nombre de mois) https://support.microsoft.com/fr-fr/office/fin-mois-fin-mois-fonction-7314ffa1-2bc9-4005-9d66-f49db1... et comme on veut le dernier jour de ce mois, le 2eme paramètre de "Fin.mois" est 0. Bon, le décalage, premier paramètre est la cellule de la colonne G, 2eme paramètre, on doit décaler le nombre du jours de ce mois multiplié avec -1 et puis +1, (janvier = 31 jours, on doit ce déplacer 30 jours dans l'histoire), ignorer 3eme paramètre (colonnes), 4eme paramètre = nombre de jours (janvier=31) et ignorer 5eme paramètre

colonne P : =SI(JOUR($A3)=1;SOMME(DECALER($G3;;;JOUR(FIN.MOIS($A3;0));));"-") de nouveau facile, le cumul du mois si on est le premier du mois.

colonne Q : =SI(JOURSEM($A3;2)=5;SOMME(DECALER($G3;;;MIN(7;FIN.MOIS($A3;0)+1-$A3);));SI(JOUR($A3)=1;SOMME(DECALER($G3;;;SERIE.JOUR.OUVRE.INTL($A3-1;1;"1111011")-$A3;));"-")) pour compliquer les choses, le premier jour du mois n'est pas toujours le lundi, donc ici je vous donne un exemple à l'inverse, je suppose que la semain commence le vendredi, parce que c'est la situation la plus difficile pour janvier 2024, on a 2 semaines à chevauchement avec le mois précédent et suivant.

sorry, je dois partir, l'explication est pour plus tard ...

Là, cela devient vraiment complexe pour moi. C'est assez surprenant de voir comme le logiciel interprète certaines valeurs ou données, comme vous l'indiquez pour -9999999.

Si je comprends bien, on ne peut appliquer une formule unique pour toutes les cas en ''G'', c'est bien cela ? Si oui, nous devrions différencier chaque cellule par un calcul différent selon si celles-ci se trouvent en début, au milieu ou en fin de mois.

Si cela est possible, cela voudrait probablement dire qu'il n'y a pas vraiment de limite à Excel.