Discussion :

[batmat86]

Bonjour à vous,

Je me permets de venir vous solliciter afin d'optimiser ma formule car je pense qu'il doit y avoir plus simple et court ....

Je cherche à faire une plage de données variable pour ma somme, en fonction du jour de la semaine contenu dans une autre cellule.

Voici ma formule qui fonctionne mais je pense que vous allez me dire qu'en effet, pas très courte ^^

Code :

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

=SI(JOURSEM(B36;2)=1;SOMME(G6:G35);SI(JOURSEM(B36;2)=2;SOMME(G6:G34);SI(JOURSEM(B36;2)=3;SOMME(G6:G33);SI(JOURSEM(B36;2)=4;SOMME(G6:G32);SI(JOURSEM(B36;2)=5;SOMME(G6:G31);SI(JOURSEM(B36;2)=6;SOMME(G6:G30);SOMME(G6:G36)))))))

Merci à vous pour votre aide, cordialement

[Patrice740]

Bonjour,

Essaies :

Code :

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

=SOMME(DECALER($G$6;;0;32-JOURSEM(B36;1)))

[Pierre Fauconnier]

Salut.

Sers-toi de DECALER et de JOURSEM pour réduire ta plage

Code :

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

=SOMME(DECALER(G6;0;0;LIGNES(G6:G35)-JOURSEM(B36;3);1))

Joursem(...;3) renvoie 0 pour lundi et 6 pour dimanche. Le quatrième paramètre de DECALER permet de définir le nombre de lignes de la plage renvoyée par DECALER. En soustrayant le numéro du jour de la semaine (0-6) du nombre de lignes, tu réduis ta plage à mesure que tu avances dans la semaine.

EDIT: Grillé par Patrice (Hello)

[Patrice740]

Bonjour Pierre,

Code :

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

=SOMME(DECALER(G6;0;0;LIGNES(G6:G35)-JOURSEM(B36;3);1))

EDIT: Grillé par Patrice (Hello)

Mais LIGNES(G6:G35) est plus pédagogique et plus résiliant que 32 !
PS : les raccourcis sont souvent mauvais conseillers !

[Pierre Fauconnier]

Hum... J'ai lu un peu trop vite. On prend toute la plage pour le dimanche, la plage - 1 ligne pour le lundi, -2 lignes pour le mardi, etc.

Dès lors, on part avec dimanche = 1 et on retranche 1 du jour de la semaine pour redimensionner la plage...

Code :

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

=SOMME(DECALER(G6;0;0;LIGNES(G6:G36)-(JOURSEM(B36)-1);1))

[batmat86]

Bonjour et merci grandement à vous, surtout avec les explications afin de pouvoir comprendre la formule et en effet cela est nettement plus léger comme solution que la mienne

Par contre, pour ma culture, pour le 32 Patrice, 31 à la limite je voyais avec 31 lignes dans la plage de données mais 32 ...

Enfin vraiment un énorme merci à vous

[Patrice740]

Re,

Par contre, pour ma culture, pour le 32 Patrice, 31 à la limite je voyais avec 31 lignes dans la plage de données mais 32 ...

Pour le dimanche il faut 31 cellules (de G6 à G36) hors JOURSEM(Date;1) = 1 pour le dimanche donc 32-1 = 31 cellules de hauteur
Pour le samedi il faut 25 cellules (de G6 à G30) hors JOURSEM(Date;1) = 7 pour le samedi donc 32-7 = 25 cellules de hauteur

Pour rester pédagogique :

Code :

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

=SOMME(DECALER($G$6;;0;1+LIGNES($G$6:$G$36)-JOURSEM(B35)))

[batmat86]

Bonjour et merci des précisions, cela me parait assez clair pour mon niveau.

Par contre, je pensais que cela été résolu et que j'allais pouvoir adapter ce type de formule, afin de de pouvoir combler à mes besoins, mais j'admets avoir été trop optimiste

Vos formules, permettent en effet d'avoir le bon calcul pour le mois en cours des sommes d'heures, donc c'est parfait.

Maintenant j'essai de faire en sorte que les heures exclues via le décalage de la plage de données, puisse être prise en compte dans le calcul du mois suivant.

Mais je m'aperçois que finalement, c'est peut être plus compliqué à faire ....


Je pense avoir trouvé la solution en faisant ceci, qui semble fonctionner, mais si vous pouviez me le confirmer, cela serait top. En tout cas, sans votre aide, je n'aurai pas réussi à faire un calcul si court, et je me serais retrouvé avec des formules imbuvables Donc merci

Code :

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

=SOMME(DECALER(G36;0;0;LIGNES(G36)-(JOURSEM(B36))))

Edit : Mince, en effet cela fonctionne sauf dans le cas, où le dernier jour du mois est un dimanche, donc soit je dois ajouter un SI pour vérifier si JOURSEM est un dimanche ... Je vais continuer à chercher une autre solution pour gérer le dimanche car je pense qu'il doit y avoir la possibilité d'éviter un SI supplémentaire

[Pierre Fauconnier]

Il faudrait peut-être reformuler la règle de A à Z. Ca permet souvent de faire l'économie d'une usine à gaz construite "au coup par coup".

[batmat86]

Bonsoir à vous,

Je cherche à faire le calcul de mes heures supplémentaires, car j'ai une période de référence de paie du 16 du mois au 15 du suivant pour mes heures travaillées classiques. Mais nos heures supplémentaires ne sont pas calculées sur la même période. Elles sont calculées par semaine complètes civiles.

Donc la première formule que vous avez aidé à faire permet bien de calculer mes heures sur la période de référence de paie. Par contre, du coup ce qui n'est pas pris du mois en cours car le 15 tombe rarement un dimanche sur une année complète, par conséquent, les heures supplémentaires non prises en compte via la formule que vous m'avez donné, doivent être ajouté dans le calcul des heures supplémentaires le mois suivant.

Pour cela au final j'étais arrivé à ce type de formule grâce à votre aide :

Code :

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

=SOMME(DECALER(G36;0;0;LIGNES(G36)-(JOURSEM(B36))))+SOMME(DECALER(V6;0;0;LIGNES(V6:V36)-(JOURSEM(Q36)-1);1))

Malheureusement, cela fonctionne parfaitement, si le 15 du mois est n'importe quel jour de la semaine hors dimanche. Car dans ce cas là, la formule que j'ai voulu faire en me basant sur la votre, pose souci pour les dimanche au 15.

Je ne sais pas si cela est assez clair ?

[Patrice740]

B
Je cherche à faire le calcul de mes heures supplémentaires, car j'ai une période de référence de paie du 16 du mois au 15 du suivant pour mes heures travaillées classiques. Mais nos heures supplémentaires ne sont pas calculées sur la même période. Elles sont calculées par semaine complètes civiles.
Donc la première formule que vous avez aidé à faire permet bien de calculer mes heures sur la période de référence de paie.

Ne manque-t'il pas les heures du mois précédent ?

Je pense qu'au lieu de faire une feuille par mois, il serait bien plus simple de partir sur une feuille pluriannuelle (un unique tableau de données) de laquelle il serait simple d'extraire un mois.

[batmat86]

Bonsoir, Patrice,

Les heures travaillées sont bien calculées par période de référence de façon mensuel du 16 au 15. Il n'y a que les heures supplémentaires, qui posent souci, étant par semaine complète calendaire, donc elle peuvent être faite pendant la période de référence, mais doit être prise en compte le mois suivant.

Je ne fais pas qu'un simple calcul d'heures, au final mon fichier me permet de calculer mon salaire au centimes prêt. J'ai du changer entièrement mon fichier, vu que nos périodes de référence et calcul des heures supplémentaires. Je cale uniquement sur les points mentionnés. Tout le reste calcul des heures travaillées, ticket restaurant, CP, arrêt maladie, heures majorées en soir, nuit, férié ou dimanche, j'ai pu adapter de mon ancien fichier sans souci .... Cela me permets de vérifier qu'il n'y a pas d'anomalie sur mon bulletin de paie mais aussi d'anticiper mes revenus ayant mes plannings à 3 semaines d'avance.

Et oui j'utilise une seule feuille par année.

Après cela semble complexe à tout décrire en français .... Si cela peut aider à la compréhension et à la structure de mon fichier, voici mon fichier dans l'état actuel de mes avancées ...

Si besoin de plus de détails, ne pas hésiter à me demander.

P.S : Désolé si mes formules ou structure n'est peut être pas très professionnel, mais n'étant qu'un novice, je fais selon mes capacités.

[Patrice740]

Bonsoir,

J'ai regardé ton fichier :
- il me semble qu'il y a une erreur dans le calcul des heures des jours fériés (N6:N36, AC6:AC36, ...) ! (voir 30 mai)
- le taux des jours fériés est 125% et le taux dimanche à 100%. Qu'en est-il des dimanches fériés : Pâques, Pentecôte, Autres(Toussaint 2019) ?

[batmat86]

Bonsoir Patrice,

Je viens de vérifier, je ne vois pas d'heures pour les heures fériées, après j'ai un calcul plus complexe, que les simples heures de la journée, car je travaille de nuit aussi des fois avec des horaires de 23h15-6h15. C'est pour cela que je dois le prendre en compte pour les heures fériées et de dimanche aussi d'ailleurs. Car même si sur mon planning il est noté 23h15-6h15 un samedi ou veille d'un férié, cela veut dire que j'aurai 6h15 de dimanche ou férié. Et la même si je suis en 23h15-6h15 un dimanche ou férié, je n'aurai que 45 min à compter sur ce jour là.

Je sais ce n'est pas courant et pas simple à comprendre et expliquer .... Après il est possible que j'ai fais quelques erreurs de formules, mais je n'en trouve pas sur les fériés, pour mes besoins en tout cas. Et aussi peut être possible que beaucoup de formules que j'utilise, sont des usines à gaz ....

Pour les dimanches oui, c'est bien 100% et 125% pour les fériés. Et pour les majorations, tout est cumulable, donc le dimanche 14 juillet, cela va être jackpot puisque dimanche férié ^^ et encore plus pour ceux avec des horaires de soir à 25% ou nuit à 35%, car là aussi c'est cumulable ....

En tout cas vraiment merci de l'aide

[Patrice740]

Re,

Pour les heures fériées, je comprends, c'est assez simple, mais puisque ça se cumule pourquoi n'as tu pas mis les dimanches de Pâques et de Pentecôte dans la liste des jours fériés ?
Par contre, dans le calcul du NET, le panier, les tickets resto et le transport ne sont pas pris en compte.

Voici une autre approche avec des tableaux structurés (et un filtre pour la saisie du mois):
Paie 2019-3.xlsx

[batmat86]

Bonjour,

Pour les dimanches de Pâques et de Pentecôte c’est simplement qu’ils ne sont pas fériés il n’y a que les lundis qui le sont.

https://inspection-du-travail.com/jours-feries/

Pour le Net, oui je le calcule, et c’est seulement dans la colonne salaire estimé que j’ajoute les tickets restaurant, transport, panier de nuit et le prélèvement à la source. Tout pourrait être intégré sur un seul calcul, je l’admets. Mais cela me permets de vérifier rapidement sur mon bulletin que les valeurs de chaque donnée ticket restaurant... est correct.

Je regarde ton fichier en rentrant du travail et je te tiens au courant

En tout cas merci du temps que tu m’accordes

Édit : ayant eu un peu de temps, je viens de regarder ton fichier. Et oui cela fait un moins usine à gaz que ce que j’ai fait ^^
Pour le calcul des heures supplémentaires dans ton fichier, il ne prends en compte que les heures supérieures à 7h quotidien. Cela semble logique étant au 35h hebdomadaire. Et tu ne peux pas deviner vu que je ne l’ai pas dis, mais nous pouvons être amené à faire des heures supplémentaires sur une journée initialement de repos, et ne faire que 2 à 5h sur cette journée. C’est pour cela que j’avais un calcul différent. Mais je peux adapter

Après faire un cumule hebdomadaire des heures supplémentaires est en effet un choix judicieux de ta part, cela évite les soucis que je rencontre et qui m’avait poussé à créer cette discussion.

Il faut aussi que je vois pour le numéro de la semaine, afin qu’il puisse correspondre au numéro de la semaine calendaire. Puisque nous n’avons que 52 ou 53 semaine par an selon les années, et on parle beaucoup en numéro de semaine lorsque l’on nous donne les horaires.

En tout cas, ton approche me plaît beaucoup, et je pense que je pourrais avoir nettement moins de modifications à faire chaque année..... quoi que si taux de prélèvement à la source, taux de csg ou notre taux horaire change, cela pourrait changer la mise en page de la feuille salaires. Mais bon, pas plus que je ne dois le faire actuellement tu me diras.

[Patrice740]

Pour le calcul des heures supplémentaires dans ton fichier, il ne prends en compte que les heures supérieures à 7h quotidien. Cela semble logique étant au 35h hebdomadaire. Et tu ne peux pas deviner vu que je ne l’ai pas dis, mais nous pouvons être amené à faire des heures supplémentaires sur une journée initialement de repos, et ne faire que 2 à 5h sur cette journée. C’est pour cela que j’avais un calcul différent.

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).

Il faut aussi que je vois pour le numéro de la semaine, afin qu’il puisse correspondre au numéro de la semaine calendaire

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)

La formule de calcul de la semaine calendaire est (en B2) =ENT((9+D2-MOD(5+D2;7)-DATE(ANNEE(3+D2-MOD(5+D2;7));1;))/7) qu'il faut adapter à une des 2 options ci-dessus.

[batmat86]

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).

Bonsoir, oui j'avais vu pour le 10 juin vu que tu l'avais mis en fond jaune, mais j'avais regardé trop vite en répondant, j'ai confondu la colonne Oui/non et celle des heures supplémentaires, au temps pour moi
Et pour le calcul générique, je ne peux malheureusement pas en faire et dois rester en manuel, puisqu'il peut arriver que je fasse 2 à 7h sur une journée initialement prévue en repos, comme cela s'est produit pour le 10 juin justement, même si la majorité est en lorsque je fais plus que 7h étant au 35h sur 5 jours. Et il y a la journée de solidarité aussi ou je demande à travailler 6 jours sur 7. Mais j'avoue que tu as peut être raison, à l'usage, il est plus fréquent que cela dépasse les 7h quotidien, donc autant le mettre en automatique, et ajouter manuellement les exceptions lorsqu'elles se produisent.

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)

La formule de calcul de la semaine calendaire est (en B2) =ENT((9+D2-MOD(5+D2;7)-DATE(ANNEE(3+D2-MOD(5+D2;7));1;))/7) qu'il faut adapter à une des 2 options ci-dessus.

La aussi merci de la réponse, je me suis demandé pourquoi prendre une formule si complexe, alors qu'un formule n°semaine existe sur excel, mais en regardant, il n'y a que 2 paramètres possibles et dans les 2 cas, il prend la semaine 1 au 1er janvier quelques soit le jour ce qui ne correspond pas à mes besoins ....

Je suis donc parti plus sur ta deuxième solution, car je ne me voyais pas comment prendre en compte l'année simplement avec le SOMME.SI.ENS. Je suis donc arrivé à cela :

Code :

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

=CONCATENER(ENT((9+[@Date]-MOD(5+[@Date];7)-DATE(ANNEE(3+[@Date]-MOD(5+[@Date];7));1;))/7);;" -";ANNEE([@Date]))

Par contre pas encore parfait, car OK cela fonctionne bien pour le cumul des heures supplémentaires, mais en début d'année en semaine 1, il peut arriver que cela soit l'année N-1 qui apparait puisque le numéro calendaire de la semaine 1 est souvent à cheval sur les 2 années ....

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, sinon cela serait trop beau ^^ Après j'avoue que sur le fichier à disposition, je n'avais pas encore fait la mise à page avec les grouper ... pour la partie calcul.

En tout cas, ton fichier est nettement plus modulaire que le mien pour les années à venir, je passais pas mal de temps à changer mes cellules nommées d'une année à l'autre ... Donc vraiment encore merci.

P.S : Egalement afin de comprendre un maximum les choses, plutôt que de prendre une solution, sans savoir pourquoi, je veux bien une explication sur le calcul de la date :

Code :

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

=DATEVAL("14/12/2018")+LIGNE()

Car d'après la description de la fonction LIGNE : Donne le numéro de ligne d’une référence. Car de base j'aurai mis la date en dur sur la 1ere ligne, et après un simple +1.

[Pierre Fauconnier]

Salut.

Pour info, NO.SEMAINE(MaDate;21) et NO.SEMAINE.ISO(MaDate) donnent bien le numéro européen de la semaine.

[batmat86]

Bonsoir Pierre,

Merci de l'information, en effet j'étais passé à côté du système 2 dans l'aide excel J'avais vu que le système 1 avec divers paramètres pour le choix du jour ....

Cela simplifie grandement la formule

EDIT : J'ai pu ainsi simplifier en mettant ceci :

Code :

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

=NO.SEMAINE.ISO([@Date])&" - "&ANNEE([@Date])

Mais bien sur cela pose souci sur les premiers jours de l'année ce qui est logique, puisque par exemple le lundi de la semaine 1 de 2019 est le 31 décembre 2018 et pour 2020, le lundi est le 30 décembre 2019 et mardi le 31.
Je pense qu'il faut ajouter un test, mais je ne vois pas lequel