Fonction Sommeprod et Index - Gestion de planning congés - Sans VBA

Bonjour Chers & Chères Expert-e-s Excel,

Je me permets de solliciter votre aide car je travaille sur un projet de planning (dynamique) de congés avec 3 équipes. Je cale complétement sur cette phase finale et sans laquelle le projet est nul.

Je dois indexée ma formule sommeprod avec les sources de congés pour que la formule suive le calendrier.

J'essaie de "toper" (chiffre "1"+ couleur identique) les jours de congés qui se trouvent dans les feuilles "Team1", "Team2" et "Team Astreinte" (je tente de simplifier la gestion des demandes de congés avec la feuille Teams).

Le calcul devant permettre de mettre des alertes.

Les conditions :

> Teams 1 & 2 => 50% des effectifs doivent être présents dans chacune d'entre elle.

*Sylviane travaille du Lundi au Jeudi

+ Alerte dans la feuille "Calendrier" en C21 et C23

> Team Astreinte Paie

Ces trois personnes doivent être présentes pendant les jours de paie et le jour de la vérification (MFC violet foncé - la zone commence au 1er barre et se termine au second - toute cette plage ne peut être demandée en congés & rose foncé pour le jour de vérification).

Il faut donc que les jours de congés demandés par cette équipe ne puissent pas être validés > mettre une alerte (colonne E)

+ MFC dans le planning

+ Alerte dans la feuille "Calendrier" en C25

Voici mes sources d'inspiration

TEST=(INDEX($E$16:$Y$27;C&6:AG&19 ;SI(C$6=0;"";SI(ET(C$6>=Debut_1_SD;C$6<=Fin_1_SD);1;""))) => Feuil Team 1

Source d'inspiration :

Fonction sommeprod et index, gestion de planning - sans VBA - en utilisant la formule bien alambiquée suivante :

=SIERREUR(INDEX(INDIRECT(ADRESSE(2;EQUIV(MAX($E$1;Planning!$D$2);Planning!$2:$2;0);1;1;"Planning")&":"&ADRESSE(2;EQUIV($G$1;Planning!$2:$2;0);1;1));1;EQUIV("CP";INDIRECT(ADRESSE(EQUIV(B$3;Planning!$A:$A;0);EQUIV(MAX($E$1;Planning!$D$2);Planning!$2:$2);1;1;"Planning")&":"&ADRESSE(EQUIV(B$3;Planning!$A:$A;0);EQUIV($G$1;Planning!$2:$2);1;1));0));"").

Je ne comprends pas la formule mais je suppose que ca correspond un peu vers quoi je tends.

Seriez-vous me trouver une solution ?

Bonjour,

je regarde le fichier,

un,e première remarque :

NO.SEMAINE(C6)

à éviter absolument ! sinon certaines années ce sera faux ...

Remplace par

NO.SEMAINE.ISO(C6)

je ne vois pas l'intérêt de cette formule

=SI(SOMME(C11:C14)=0;"";SOMME(C11:C14))

ceci suffit

SOMME(C11:C14)=0

et permet surtout de faire ensuite des calculs

nota : il existe une option qui permet de ne pas afficher les valeurs nulles

=SI(C$6=0;"";SI(ET(C$6>=Debut_1_SD;C$6<=Fin_1_SD);1;""))

Debut_1_SD étant une matrice, tu ne peux pas comparer directement, il faut rechercher la ligne et comparer ensuite à la valeur de fin

Une question Charlie ... cela sort d'où ce truc on ne peut plus alambiqué ? est-ce un exercice de style ?

Je vais quand même poursuivre, mais ce n'est pas du tout la méthode la plus simple !

Pour les périodes de congés, il faut absolument les mettre dans l'ordre des dates.

Ouf !

J'ai juste fait Sylviane D*

=SIERREUR(SI(C6<INDEX(Fin_1_SD;EQUIV(C6;Debut_1_SD;1));1;0);0)

Il n'y a pas à utiliser de SOMMEPROD ou de formules longue comme mon bras !

J'ai aussi simplifié quelques formules et mis en place l'option sur les valeurs 0 (non affichées)

J'ai mis dans l'ordre des dates ses absences.

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

Wow Steelson,

Vous êtes sacrément calé !!!

Je vais essayer de reproduire vos formules . Aujourd'hui, je n'ai pas le temps de le faire. Je vois cela demain.

Un grand merci pour le temps que vous avez consacré à mon problème et à mon projet. Je vous en suis vraiment reconnaissante.

Excellent weekend.

A ta disposition si tu as la moindre difficulté...

Steelson,

Je viens de regarder (en vitesse) les congés de Sylviane. Par exemple en février 2019, elle prend 4 jours. Le calcul prend les weekends et le vendredi (Sylviane ne travaille pas le vendredi) dans le calcul alors qu'ils ne doivent pas être pris en compte .

C'est déjà une belle avancée pour moi car des chiffres apparaissent ! Bravo !!!

sylviane

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

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 .

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

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

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

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

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)

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à !
Rechercher des sujets similaires à "fonction sommeprod index gestion planning conges vba"