Fonction Sommeprod et Index - Gestion de planning congés - Sans VBA Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'427
Appréciations reçues : 497
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 15 décembre 2018, 18:35

Où sont indiqués les jours de travail par personne ?

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
C
CharlieB
Jeune membre
Jeune membre
Messages : 35
Inscrit le : 16 août 2018
Version d'Excel : 2016FR

Message par CharlieB » 15 décembre 2018, 22:44

Dans les formules Formules Team 1,2 et Astreinte dans la colonne Fin :

Sylviane
=SERIE.JOUR.OUVRE.INTL(B3;C3;"0000111";Jours_feries)
111 = Vendredi, Samedi, Dimanche

Les autres
=SERIE.JOUR.OUVRE.INTL(H3;I3;"0000011";Jours_feries)
11 = Samedi, Dimanche

La problématique de cette formule c'est qu'elle me donne le jour de reprise et non le dernier jour de congés :(.
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'427
Appréciations reçues : 497
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 16 décembre 2018, 05:10

CharlieB a écrit :
15 décembre 2018, 22:44
La problématique de cette formule c'est qu'elle me donne le jour de reprise et non le dernier jour de congés :(.
pas très gênant quand même !

voici la correction
j'y ai mis toutes les formules du planning
=SIERREUR(SI(ET(C$6<INDEX(Fin_1_SD;EQUIV(C$6;Debut_1_SD;1));JOURSEM(C$6;2)<=4);1;0);0)
le 4 est à adapter (ainsi que Debut et Fin) selon la personne

https://www.cjoint.com/c/HLqekkVG0jw

rappel : les dates d'absence doivent êtres triées

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'427
Appréciations reçues : 497
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 16 décembre 2018, 10:52

Je viens d'ajouter la condition sur les jours fériés
=SI(ET(NB.SI(Jours_feries;C$6)=0;C$6<INDEX(Fin_1_SD;EQUIV(C$6;Debut_1_SD;1));JOURSEM(C$6;2)<=4);1;0)
J'ai aussi enlevé le SIERREUR en ajoutant fictivement le 1er janvier 2018 aux absences ...
https://www.cjoint.com/c/HLqjZ4lDTTw

rappel : les dates d'absence doivent êtres triées

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
C
CharlieB
Jeune membre
Jeune membre
Messages : 35
Inscrit le : 16 août 2018
Version d'Excel : 2016FR

Message par CharlieB » 16 décembre 2018, 12:21

Steelson,

Chapeau l'ami :).

Pourriez-vous s'il vous plaît m'expliquer la différence entre SI.ERREUR et l'ajout fictif du 1er janvier 2018 ?

Je vais également abuser de vos talents car j'ai abandonné ma recherche pour que la période totale des jours de paie (en violet) soit tous colorés. Je ne suis arrivée qu'à indiqué le 1er et le dernier jour. J'ai ouvert un post à ce sujet malheureusement les solutions proposées ne sont pas adaptées à mon projet. Peut-être que vous seriez comment faire :oops:. Dans le cas où ma demande serait abusive, ce n'est pas grave car j'ai le début et la fin de période.
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'427
Appréciations reçues : 497
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 16 décembre 2018, 13:45

CharlieB a écrit :
16 décembre 2018, 12:21
j'ai abandonné ma recherche pour que la période totale des jours de paie (en violet) soit tous colorés. Je ne suis arrivée qu'à indiqué le 1er et le dernier jour. J'ai ouvert un post à ce sujet malheureusement les solutions proposées ne sont pas adaptées à mon projet. Peut-être que vous seriez comment faire :oops:. Dans le cas où ma demande serait abusive, ce n'est pas grave car j'ai le début et la fin de période.
Pas sur d'avoir bien compris ... si c'est un challenge, je suis preneur.

Pour ce qui est de la formule, voici les explications :
=SI(ET(NB.SI(Jours_feries;C$6)=0;C$6<INDEX(Fin_1_SD;EQUIV(C$6;Debut_1_SD;1));JOURSEM(C$6;2)<=4);1;0)
Il y a 3 conditions pour avoir un 1 ...

NB.SI(Jours_feries;C$6)=0 ____________ cela ne doit pas être un jour ouvré, donc je teste avec NB.SI qui vaudrait 1 si c'était un jour ouvré

C$6<INDEX(Fin_1_SD;EQUIV(C$6;Debut_1_SD;1)) _________ voir ci-dessous, c'est le cœur de la formule !

JOURSEM(C$6;2)<=4 ___________ le jour de la semaine doit être inférieur à 4 (vendredi, samedi, dimanche exclus) ou 5 (samedi, dimanche exclus) selon la personne

On va décomposer la second formule ...
C$6<
INDEX(Fin_1_SD;
EQUIV(C$6;Debut_1_SD;1)
)
j'y arrive ...

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'427
Appréciations reçues : 497
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 16 décembre 2018, 13:51

C$6<
INDEX(Fin_1_SD;
EQUIV(C$6;Debut_1_SD;1)
)
Prenons EQUIV( __ ; __ ; 1 ) ... cela va donner la ligne où se trouve la date de début d'absence immédiatement inférieure ou égale à C6.
... C'est pour cela qu'il est important de trier sur les dates.
... Et c'est pour cela que j'ai ajouté une date ancienne (j'ai mis le 1/1/2018 mais j'aurais pu mettre autrechose) car sinon j'avais une erreur de calcul quand il n'y a pas encore eu une première absence avant cette date C6 (d'où le SIERREUR que j'ai supprimé car ce n'est pas "propre")

Avec la fonction INDEX et la ligne trouvée ci-avant, je peux prendre la valeur de fin d'absence

Et je compare C6 avec cette valeur de fin (ou plutôt de reprise comme tu le signales)
Modifié en dernier par Steelson le 16 décembre 2018, 14:00, modifié 1 fois.

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'427
Appréciations reçues : 497
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 16 décembre 2018, 13:57

J'aurais voulu aller plus loin dans le paramétrage sur 2 points :
- ton système de nommage de plage est intéressant (Fin_1_SD et Debut_1_SD par exemple) mais il est quand même fastidieux à mettre en place
- la déclaration des jours ouvres via la formule SERIE.JOUR.OUVRE.INTL(B3;C3;"0000111";Jours_feries) par exemple, car cela implique de bien synchroniser cette formule, la MFC et la formule que je t'ai proposée
Mais le mieux est l'ennemi du bien, autant en rester là !

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message