Discussion :

[Patrice740]

Comme je pense que je ne vais pas avoir le choix aussi d'ajouter des colonnes pour les calculs, car en effet, ta proposition est vraiment très propose sur la feuille Salaires, mais malheureusement, les taux de CSG, mutuelle et autre, sont amenés à changer de temps à autre.

Tu peux effectivement ajouter des colonnes lorsque les valeurs changement en cours d'année (par ex. 3 colonnes CSG), mais il arrive aussi que la méthode de calcul change et là, il n'y a pas de solution miracle !

[batmat86]

Bonjour,

Et oui malheureusement des fois les méthodes de calcul change et cela est un gros bazar ^^ Pour cela d'ailleurs que je dois tout reprendre car avant nos périodes de paie étant par semaine calendaire aux alentours du 15 .... Et aussi oui il y a eu moins d'un an pas mal de changement aussi sur les taxes ....

Pour revenir à mes moutons, j'ai bon chercher et faire des tests, je n'arrive à m'en sortir sur la 2eme méthode, puisque j'ai un souci sur les premiers jours de l'année que je n'arrive pas à résoudre pour le moment, voir mes messages précédents

J'ai repris exactement les mêmes calculs que toi. J'ai simplement ajouté un calcul 'générique' dans la colonne Oui/Non que tu peux forcer à 1 (voir 10 juin).



Ce calcul est volontairement différent de la semaine calendaire pour simplifier le calcul du cumul des heures supplémentaires.
Si tu veux le numéro de semaine calendaire, il faut aussi revoir le principe du cumul, vu que d'une année sur l'autre les semaines portent le même numéro :
- soit en remplaçant le SOMME.SI() par SOMME.SI.ENS() qui prendra aussi en compte l'année,
- soit en utilisant un format de semaine intégrant l'année (ex AA-SS)

J'ai donc tenté de me pencher sur la première solution que tu m'indiques avec le SOMME.SI.ENS, mais là aussi je cale. J'ai bien le début avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI([J]=1;SOMME.SI.ENS([Total Heures sup];[Sem];[@Sem]);0)

mais pour ajouter l'année, je ne vois pas, ou alors faut que j'ajoute un colonne supplémentaire au tableau, mais même en ajoutant, une colonne pour l'année, je n'arrive pas à faire fonctionner correctement la fonction


EDIT : En parallèle, je continue d'essayer de développer le fichier, mais là aussi je galère, donc soit j'ai vraiment perdu énormément en excel par manque de pratique, soit je dois être vraiment fatigué...

Je n'arrive même pas à faire en sorte de savoir le nombre de jour travaillé par année, ou le nombre de nuit travaillé .... Avant j'utilisais qu'un seul fichier par année, donc c'était assez simple avec la fonction NB et NB.SI, avec par exemple pour les nuits :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI(PlageAnnee;23,25/24)

J'ai bien vu qu'il existe aussi des NB.Si.ENS mais sauf erreur de ma part, tout ce qui est en .ENS ne peut contenir de formules à l'intérieur des paramètres.
Car je pense que l'on doit tester la date pour ensuite ne garder que la plage souhaitée mais pareil, je bloque. Je pense que je ne doit penser aux bonnes formules ....

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ANNEE([DATE])=2019;NB(Heures[Début]);0)

Et je continue de voir avec l'aide ici : https://fauconnier.developpez.com/tu...ux-structures/ car je l'avais vu il y a plus d'un an, et j'avais oublié certainement chose ...
Et ce Tutoriel de Pierre m'avait bien aidé à refaire l'intégralité de mon fichier de suivi de consommation de ma voiture, avec de belle formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((MOIS(TableauPleins[Date Plein])=1)*(ANNEE(TableauPleins[Date Plein])=2019)*(TableauPleins[Prix Plein]))

Mais j'avoue que les NB et NB.SI me pose plus de souci qu'un SOMMEPROD dans un tableau structuré pour le moment.

En tout cas, je l'admets, le tableau structuré, c'est tout de même plus simple à manipuler ...

[Patrice740]

Re,

Pour le second choix, dans la colonne semaine, il suffit que l'année commence au 16 décembre précédent :
=NO.SEMAINE.ISO([@Date])&" - "&ANNEE(MOIS.DECALER([@Date];1)-15)

[batmat86]

Re Patrice,

Malheureusement cette solution ne fonctionne pas

En effet le 16 décembre 2018 se trouve codé en semaine 50 - 2019 et du coup le 15 décembre 2019 se retrouve aussi codé en semaine 50 - 2019.

Par conséquent le cumul des heures supplémentaires hebdomadaires se retrouve être le même à un an d'intervalle. Même si j'avoue que j'ai du regarder sur plus d'un an pour voir ce souci.

[Patrice740]

Re,

Sur mon fichier ça fonctionne EDIT : pas biien !:
fichier supprimé

[batmat86]

Re, même sur ton fichier, j'ai me même souci, que je décris

Tu peux tester en mettant des heures supplémentaires sur ta ligne du 16 décembre 2018 en ligne 3. Et ce cumul va être reporté à la ligne 367 correspondant au 15 décembre 2019.

[Patrice740]

Re, même sur ton fichier, j'ai me même souci, que je décris

Tu peux tester en mettant des heures supplémentaires sur ta ligne du 16 décembre 2018 en ligne 3. Et ce cumul va être reporté à la ligne 367 correspondant au 15 décembre 2019.

Effectivement, j'ai été un peu vite, il faut aussi que dans SS-AA, l'année ne change que le dimanche:
=NO.SEMAINE.ISO([@Date])&" - "&ANNEE(FIN.MOIS([@Date]-15-JOURSEM([@Date];3);1))

[Patrice740]

Avec le bon fichier :
Paie 2019-5.xlsx

[batmat86]

Effectivement, j'ai été un peu vite, il faut aussi que dans SS-AA, l'année ne change que le dimanche:
=NO.SEMAINE.ISO([@Date])&" - "&ANNEE(FIN.MOIS([@Date]-15-JOURSEM([@Date];3);1))

Re, bon toujours pareil ^^ cette fois c'est la ligne 374 qui se reporte en 738 ...

A mon avis, on va rester sur ta toute première suggestion avec un compteur propre pour le calcul, ainsi pas de risque de doublon, au pire que je pourrai masquer cette colonne et garder juste le numéro de semaine pour le visuel ... Cela est efficace, donc pourquoi compliquer

Je vais continuer de chercher comment compter le nombre de nuits faites par année par contre, car je galère pour savoir comment le réaliser. Si je n'avais qu'une plage de données par année, cela serait simple. Mais là le fichier est prévu pour gérer plusieurs années, donc faut que je compte le nombre de cellules ayant en début 23h15 par année, ne pouvant utiliser :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI(PlageAnnee;23,25/24)

En tout cas, encore vraiment merci

[Patrice740]

Re, bon toujours pareil ^^ cette fois c'est la ligne 374 qui se reporte en 738 ...

Il n'y a pas de ligne 738 dans le fichier que je viens de poster. Comment veux-tu que je comprenne de quoi tu parles ?
Postes le fichier qui pose problème ou crée le problème dans mon fichier.

[batmat86]

Ces lignes étant dans l'ancien fichier 4 qui tu avais mis.

Dans ton fichier 5, si l'on étire me tableau pour avoir une plus grande période, cela se produit en ligne 403-767. Je n'ai rien modifié à ton fichier, simplement étendu la zone pour aller voir en 2021, si pas de souci ... Je te mets ton fichier 5 simplement avec la zone étendue.

Pour cela que je pense, que ton idée initiale, avec un compte propre, indépendant du numéro de semaine est la valeur solution, car avec un incrémentation continue, pas de risque d'avoir une valeur en double ... Comme c'est le cas avec le fait que le numéro de semaine soit cyclique sur plusieurs années ....

Edit : Bon en fin de compte, je suis enfin arrivé à faire le calcul du nombre de nuit travaillées, simplement avec cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((ANNEE(Heures[Date])=[@Année])*(Heures[Début]=23,25/24))

comme quoi, ne pas rester buté à chercher avec un NB ou NB.SI comme je le faisais sur mon ancien fichier. Le tableau structuré offre de nombreux avantages, faut juste arrivé à comprendre pour commencer à l'exploiter correctement

[Patrice740]

J'avais réussi à reproduire le problème, finalement pour sem c'est plus simple :
=NO.SEMAINE.ISO([@Date])&" - "&ANNEE([@Date]+7-JOURSEM([@Date];3)) .
Ça fonctionne aussi dans ton fichier.

[batmat86]

Re, encore merci à toi Patrice

Mais cela fonctionne jusqu'en fin 2023, cela se reproduit sur les lignes 1873 et 2237 si l'on va jusqu'à fin 2024 mais d'un côté je pense que cela est normal du fait que l'on ajoute et soustrait des jours à une date, donc fatalement à un moment cela poser souci.

Je pense que tu ne dois pas chercher pas plus pour ce souci, puisque déjà cette dernière version fonctionne jusqu'en fin 2023, donc il y a de forte chance, que d'ici là, j'ai du refaire entièrement mon fichier suite aux diverses modifications qui peuvent arriver, entre les accords d'entreprise, modifications de période de paie, de calcul de charge et autres .....

Et puis ta toute première version, elle marchera même au delà de 2023 ....

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ENT((D153-DATEVAL("10/12/2018"))/7)

donc je ne suis pas à une colonne prêt dans mon fichier, surtout qu'elle n'est pas large et que je peux la masque dans le pire des cas ....

En tout cas, j'avance petit à petit, et j'apprends beaucoup grâce à votre aide (Patrice et Pierre). Donc vraiment merci.

Je vois d'ici les jours à venir, si je peux enfin mettre en résolu ce sujet, dès que j'ai fini de mettre tout çà en place et faire quelques tests.

[Patrice740]

C'est liée au fait que certaines semaines sont doublés en début et en fin d'année. Celle là fonctionne (au moins jusqu'en 2090) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NO.SEMAINE.ISO([@Date]) & " - " & SI(NO.SEMAINE.ISO([@Date]) =1;GAUCHE([@[Période paye]];4);ANNEE([@Date]-JOURSEM([@Date];3)))

[batmat86]

Bonjour,

Bravo à toi Patrice, tu es plus tenace que moi Car je serai parti sur ta toute première proposition avec une colonne en plus pour moi ...

Mais là j'avoue qu'en effet cette proposition fonctionne à merveille, et puis si tu me dis que c'est bon jusqu'à au minimum 2090, cette formule m'enterrera ou au minimum je serai à la retraite depuis très longtemps

Encore merci d'avoir persisté

Je continue à mettre en place un nouvel onglet pour me faire un récapitulatif annuel pour comparer rapidement d'une année à l'autre. J'ai réussi à compter le nombre de jours travaillés, de nuit, de ticket restaurant ... par année avec ce type de formule par exemple pour le nombre de jour travaillés :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((ANNEE(Heures[Date])=[@Année])*(ESTNUM(Heures[Début])))

Dans mon travail, j'ai très peu de week end complet en repos (samedi et dimanche), dans la théorie 8 par an mais par exemple l'année dernière, je n'en ai eu qu'un ...
Mais je ne vois pas comment calculer le nombre de samedi et dimanche en repos consécutif. Je vois comment calculer le nombre de samedi en repos avec cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((ANNEE(Heures[Date])=[@Année])*(Heures[Début]="REPOS")*(JOURSEM(Heures[Date];2)=6))

mais compter uniquement samedi et dimanche consécutif je ne vois pas.

Et oui dans ta colonne J, j'ai changé le format de JOURSEM pour mettre en 2. Car cela me faisait bizarre d'avoir un dimanche en 1, je préfère lundi en 1 et dimanche 7, surtout que je n'avais qu'à changer le J=1 en J=7 dans ta formule de la colonne du cumul des heures supplémentaires en :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SI([J]=7;SOMME.SI([Sem];[@Sem];[Total Heures sup]);0)

[batmat86]

Bonjour,

Si cela peut aider, voici mon fichier dans l'état actuel ....

Désolé Patrice pour la mise en page qui est nettement moins propre que ta proposition pour la feuille Salaire mais j'ai préférer mettre les données en colonne, pour anticiper les prochains changements de ces valeurs .... Même si je ne suis pas fan et que je préférais ta proposition. Mais je pense que je vais avoir des collègues qui me demanderont aussi le fichier car simple à utiliser, juste à saisir ses plannings, rien d'autres, et la plus part, n'aurai pu ajuster le tableau avec les cellules nommées ...

Je reviens vous demander de l'aide, car je n'arrive toujours à compter les week end complet en repos, soit samedi et dimanche consécutif. Voir dans la feuille Recapitulatif.

Merci encore grandement de toute votre aide.

[Patrice740]

Bonjour,

Avec une colonne supplémentaire dans le tableau Heures et une autre période de comptage en Bilan2 :
Paie 2019.xlsx

[batmat86]

Bonsoir et merci Patrice, je regarde plus profondément dans la journée demain car je suis couché donc sur téléphone je vois moins l’ensemble

Pour la période sur récapitulatif, pour les jours travaillés et de nuit, c’est normal que je ne prenne pas les périodes de références. Car pour les impôts et donc les frais kilométriques et ticket restaurant, il me faut les valeurs par année civile et non en référence de paie. Oui là aussi cela serait trop simple si tout le monde utilisé les mêmes périodes de référence faut croire ^^

Après il est possible que j’ai laissé passer des erreurs aussi de mon côté..... je vais regarder ça et avec ta proposition. Après peut être pour ça aussi que je ne voyais pas de solution possible à mon niveau de connaissance. Car je tenter de voir pour tout faire sans réfléchir à ajouter une colonne .... comme quoi des fois tout n’est peut être pas si simple sur Excel même avec un haut niveau comme toi.

En tout cas encore merci, j’ai même profité de ce que tu m’as appris dans ce sujet, pour compléter et améliorer mon fichier Excel de suivi de budget et aussi celui des dépenses, consommation et kilométrage de ma voiture ;-)

[batmat86]

Bonjour à vous,

Je reviens et je peux mettre en résolu le sujet

Ayant eu mon bulletin de paie de juillet, j'ai pu voir quelques erreurs de ma part de formule, et ajuster le calcul des heures supplémentaires sur la paie.

Mais grâce à vous, j'ai maintenant un fichier qui fonctionne parfaitement, et je suis au centime prêt par rapport à la paie et cela seulement en complétant ses heures et ses primes variables mensuelles donc c'est parfait, encore merci.

Bonne journée, cordialement