XL 2019 ? planning extraire des données avec un roulement d'une semaine sur deux

[Patchinette]

Bonjour à tous et toutes,

Je me décide à vous demander de l'aide, j'ai passé beaucoup de temps, mais je n'y arrive pas toute seule ! Si l'un d'entre vous pouvait m'aider je vous en serai très reconnaissante.

J'ai construit un planning, qui extrait mes horaires de travail (je travaille en alternance d'horaire sur 2 semaines).

Ma formule actuelle, teste si les semaines sont paires ou non, et j'extrais les données avec une fonction index/equiv/equiv. Elle fonctionne parfaitement pour 2 de mes employeurs (Barichon et Dormichon), car effectivement je travaille en semaine paire pour l'un et en semaine impaire pour l'autre.

En ce qui concerne mon premier employeur (Tartanpion) j'appliquais la même formule, sauf qu'à chaque fois que 2 semaines impaires se suivent (sem 53 et sem 1), toute l'extraction de mes heures devient fausse.

J'aurais aimé trouver une formule qui ne tient pas compte du système impair et pair. Mais seulement de l'alternance d'une semaine sur l'autre et ce à partir d'une date donnée.

J'ai tenté en cellule A39 de tester si mon horaire 8 jours plus tôt était vide avec cette formule :
=NON(ESTVIDE(ADRESSE((EQUIV(AUJOURDHUI()-JOURSEM(AUJOURDHUI())-2-SI(JOURSEM(AUJOURDHUI())=1;7;0);C10:C40;0)+9);4))).
Mais lorsque je l'étends et que je vérfie, je constate que la formule teste toujours la même cellule.

Je ne sais pas si j'applique le bon raisonnement. Depuis des jours je lis plein de trucs, je ne sais plus si je dois tester si l'année à 53 semaines, et comment faire ça, ou si c'est mieux de pouvoir extraire mes horaires une semaines sur deux en alternance d'horaires et surtout comment faire. Je suis perdue.

Je vous remercie d'avance, si vous pouviez m'aider à trouver une formule qui pourrait fonctionner pour chacun de mes emplois.

Patchinette

 

[JHA]

Bonjour à tous,

J'ai commencé à travailler sur ton fichier pour "Tartanpion" seulement.
Je vois qu'il y a des soucis de résultat. Dis moi si je suis dans le vrai avant de continuer.

Edit: changement de fichier

JHA

 

[Patchinette]

Bonjour JHA.
Je te remercie beaucoup de prendre en considération mon problème. Je n'ai vu ton message qu'en fin de matinée, d'ailleurs je ne pensais pas qu'on me répondrait si vite. C'est vraiment gentil.
J'ai vu ce que tu as produit (bravo). Alors je n'ai pas compris toute la formule (je suis loin d'être à un bon niveau Cxcel), mais ça m'a bien l'air de fonctionner et de donner le résultat escompté.
Je ne pensais pas que ce serait si compliqué ! Je crois comprendre que ta formule faire référence à une base de données (data) qui calcul le nombre de semaine dans une année.
Je n'ai pas vu où sont "les soucis de résultat" dont tu parles.
J'espère que ça ne pas pas te prendre trop de temps. Mais en tout cas je te remercie énormément.

Patchinette

 

[JHA]

Bonjour à tous,

A vérifier si pas de soucis entre semaine 1 et 2.
La table de correspondance de l'onglet "Datas" permet de ne pas trop se casser la tête sur les formules.
Je pense que si tu dois rajouter des noms, cela sera plus facile pour toi.

JHA

 

[Patchinette]

Bonjour JHA et à tous

Semaine 30/12 - 3/01/2020 j'ai travaillé en semaine 1, alors la formule affiche la semaine 2

Semaine 04/01/2021 - 10/01/2021 je travaillerai en semaine 2, alors la formule affiche effectivement la semaine 2, ce qui laisse à penser qu'il y a un problème effectivement.

Merci

 

[JHA]

Bonjour à tous,

Petite modification à vérifier

Edit: changement de fichier le 02/05 à 10h00

JHA

 

[Patchinette]

Merci beaucoup cette fois ça fonctionne parfaitement !

 

[Patchinette]

Bonjour à tous, et JHA

Je me permets de revenir vers toi.
J'ai constaté que si pour Tartanpion ça à l'air de bien fonctionné, pour ce qui est de Barichon ça n'est pas le cas (j'avais du mal t'exprimer le problème, tu m'excuseras).
Le problème est que, pour Barichon je travaille en semaine impaire et donc comme la semaine 53 (2020) est une semaine impaire et la semaine qui suit, semaine 1 (2021) est aussi une semaine impaire, il y a un décalage qui à lieu aussi pour Dormichon, du fait de l'alternance de ses deux emplois (Barichon en semaine impaire, Dormichon en semaine paire).
Penses-tu pouvoir corriger ta formule en ce sens ? Tu pourrais garder la première partie de ta formule relative aux DATA qui teste les horaires de Tartanpion et compléter la formule avec une autre partie qui testerait si les semaines sont impaires ou paires pour Barichon et Dormichon? (J'espère que je suis claire !)
Aurais-tu, aussi, la gentillesse d'étendre ton fichier DATA à partir de 2018, au lieu de 2020 ? En effet le fichier que j'ai publié n'est qu'une petite partie de mon tableau, et j'aimerai bien garder l'antériorité de mes données (Congés, abs, etc.) J'ai bien essayé de le faire, mais quand j'ai vu les formules matricielles à la fin, j'avoue que j'ai eu peur de continuer, étant donné que je ne comprends pas ce que calculent les formules !
J'espère que tu pourras une nouvelle fois me rendre service, je pense qu'après je serai en mesure de me débrouiller seule.
Avec mes remerciements anticipés.
Bonne journée,
Et bon 8 mai à tous
Patchinette

 

[JHA]

Bonjour à tous,

A vérifier

JHA

 

[JHA]

Bonjour à tous,

J'ai inversé le résultat de la fonction mod()

SI(ET($G$3=$P$6;MOD(NO.SEMAINE.ISO(C10);2)=1);INDEX($Q$6:$W$6;EQUIV(TEXTE(JOURSEM(C10);"jjj");F1JRSEM1;0))

SI(ET($G$3=$P$7;MOD(NO.SEMAINE.ISO(C10);2)=0);INDEX($X$7:$AD$7;EQUIV(TEXTE(JOURSEM(C10);"jjj");F1JRSEM2;0))

Il est vrai que je n'ai pas fait attention donc si impair (=1) on prend les données F1JRSEM1 et inversement pour la troisième personne si pair (=0) on prend les données F1JRSEM2 .

JHA

 

[Patchinette]

D'après ce que j'ai pu en voir en tout k sur le passage 2020 et 2021 ça fonctionne très bien pour les 3 employeurs. Je testerai un peu plus loin demain.
Je te remercie énormément pour tout ce travail. Grace à toi je pense pourvoir mettre mon planning à jour. Enfin j'espère être à la hauteur, car je vais devoir compléter ta formule avec d'autres paramètres.

Un grand, grand merci. ;-) à toi et merci à tous les autres.
Très bonne soirée.
Patchinette

 

[Patchinette]

Bonsoir JHA et tout le monde,

Encore un grand merci pour ton aide !

Si je peux encore abuser, pourrais-tu m'aider à adapter un bout de ma formule à la tienne ? J'ai beaucoup de mal à me situer dans les formules, et à mettre les ")".

1) En premier, je voudrais un "sierreur" car à la fin des mois qui finissent en moins de "31", j'ai une erreur "Na", ce qui m'empêche de faire mes totaux

SIERREUR(---------------> et que le sierreur retourne ""

2) puis je j'aimerais que tu puisses mettre ce bout de formule qui me permet de gérer les absences (si le 1er caractère de gauche est un "C", c'est un congé, ça n'affiche pas d'heure dans le planning:

SI(GAUCHE(RECHERCHEV(C10;INDIRECT($G$3);2;FAUX);1)= "C";"";

3) et pour finir, ta formule que j'ai adapté à mes plages (j'ai vérifié, en l'état, elle fonctionne) :

=SI($G$3=DONNES!$A$5;SI(ET($G$3=DONNES!$A$5;MOD(DECALER(Datas!$A$1;EQUIV(C10;DECALER(Datas!$A$1;1;EQUIV(C$6;Datas!$A$1:$K$1;0)-1;375);0);22+(C$6-2018));2)=0);INDEX(DONNES!$I$5:$O$5;EQUIV(TEXTE(JOURSEM(C10);"jjj");plHRSjour2;0));INDEX(DONNES!$B$5:$H$5;EQUIV(TEXTE(JOURSEM(C10);"jjj");plHRSjour1;0)));
SI(ET($G$3=DONNES!$A$6;MOD(NO.SEMAINE.ISO(C10);2)=1);INDEX(DONNES!$B$6:$H$6;EQUIV(TEXTE(JOURSEM(C10);"jjj");plHRSjour1;0));
SI(ET($G$3=DONNES!$A$7;MOD(NO.SEMAINE.ISO(C10);2)=0);INDEX(DONNES!$I$7:$O$7;EQUIV(TEXTE(JOURSEM(C10);"jjj");plHRSjour2;0));0)))

Pour toi c'est certainement rien, mais moi je n'y arrive pas, je planche depuis cet après-midi !

Alors si tu pouvais me raccrocher tout ça, vraiment tu me sauverais la mise !

J'attends avec impatiente ton retour,
Et encore merci pour tout
Patchinette

 

[JHA]

Bonjour à tous,

Pour le sierreur, c'est tout simple:
=sierreur(ta formule complète;"")
Pour le "c" et SI(GAUCHE(RECHERCHEV(C10;INDIRECT($G$3);2;FAUX);1)= "C";"";
je ne peux pas t'aider sans connaître de quoi il en retourne.

J'ai mis sur le fichier les deux formules sierreur() sur les trois colonnes et pour le "c" sur la formule de la colonne "D" en jaune.
Tu peux essayer de remplacer "SI(B10="c";"";" par "SI(GAUCHE(RECHERCHEV(C10;INDIRECT($G$3);2;FAUX);1)= "C";""; " mais je ne te promets rien.

Pour te donner plus de réponse, il faudrait annexer un extrait de fichier avec les onglets interressés "INDIRECT($G$3)"??? plage des données Tartanpion, Barichon et Dormichon

JHA

 

[Patchinette]

Merci pour ta réponse,

Voilà ce que je suis arrivée à faire :
=SIERREUR(SI(GAUCHE(RECHERCHEV(C10;INDIRECT($G$3);2;FAUX);1)= "C";"";SI($G$3=DONNES!$A$5;SI(ET($G$3=DONNES!$A$5;MOD(DECALER(Datas!$A$1;EQUIV(C10;DECALER(Datas!$A$1;1;EQUIV(C$6;Datas!$A$1:$K$1;0)-1;375);0);22+(C$6-2018));2)=0);INDEX(DONNES!$I$5:$O$5;EQUIV(TEXTE(JOURSEM(C10);"jjj");plHRSjour2;0));INDEX(DONNES!$B$5:$H$5;EQUIV(TEXTE(JOURSEM(C10);"jjj");plHRSjour1;0)));
SI(ET($G$3=DONNES!$A$6;MOD(NO.SEMAINE.ISO(C10);2)=1);INDEX(DONNES!$B$6:$H$6;EQUIV(TEXTE(JOURSEM(C10);"jjj");plHRSjour1;0));
SI(ET($G$3=DONNES!$A$7;MOD(NO.SEMAINE.ISO(C10);2)=0);INDEX(DONNES!$I$7:$O$7;EQUIV(TEXTE(JOURSEM(C10);"jjj");plHRSjour2;0));0))));"")
Mais cela ne fonctionne pas, car Gauche renvoie "NA", dans l'examinateur de formule, je n'arrive pas à aller voir plus loin que ça.


Si celà peut aider ma première formule qui fonctionnait tant qu'il n'y avait pas de changement à cause de la semaine 53 était celle-là :
=SIERREUR(SIERREUR(SI(GAUCHE(RECHERCHEV(E10;INDIRECT($G$3);2;FAUX);1)= "C";"";SI(EST.IMPAIR(NO.SEMAINE(E10;21));INDEX(plHRS1;EQUIV($G$3;plHRSNOM;0);EQUIV((TEXTE($E10;"jjj"));plHRSjour1;0));INDEX(plHRS2;EQUIV($G$3;plHRSNOM;0);EQUIV((TEXTE($E10;"jjj"));plHRSjour2;0))));SI(EST.IMPAIR(NO.SEMAINE(E10;21));INDEX(plHRS1;EQUIV($G$3;plHRSNOM;0);EQUIV((TEXTE($E10;"jjj"));plHRSjour1;0));INDEX(plHRS2;EQUIV($G$3;plHRSNOM;0);EQUIV((TEXTE($E10;"jjj"));plHRSjour2;0))));"")

J'ai copié dans le fichier les plages qui correspondantes à "indirect de G3", après je ne sais pas si c'est bien ce qu'il te fallait pour m'aider. J'espère que tu pourras.....
Merci pour ta patience

 

[Patchinette]

Je t'écris vite afin que tu ne perdes pas de temps pour rien. Je crois que j'ai trouvé "the formule" complète. Je vérifie demain, car j'ai les yeux qui me sorte de la tête.
Si tu n'as pas de nouvelle de moi. C'est que tout ira bien. Et je te remercie ENORMEMENT, vraiment, heureusement pour moi qu'il existe des gens comme vous qui donne et leur savoir, et leur temps !!! Merci beaucoup à toi, et à tous !
Trop contente.
Bon we, bon déconfinement, et prenez soin de vous !