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
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, 11:03

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

Fichier : https://www.cjoint.com/c/HLpjZfjHSJi

Voici mes sources d'inspiration

http://www.activassistante.com/visualis ... f-excel/NB: Indexer SOMMEPROD avec SourceINDEX($E$16:$Y$27;C&6:AG&19)

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.

https://www.excel-downloads.com/threads ... ng.222627/

Seriez-vous me trouver une solution ?
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'931
Appréciations reçues : 539
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 15 décembre 2018, 13:42

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)
Modifié en dernier par Steelson le 15 décembre 2018, 13:55, 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'931
Appréciations reçues : 539
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 15 décembre 2018, 13:55

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

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'931
Appréciations reçues : 539
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 15 décembre 2018, 14:03

=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

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'931
Appréciations reçues : 539
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 15 décembre 2018, 14:05

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 !

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'931
Appréciations reçues : 539
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 15 décembre 2018, 14:11

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

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'931
Appréciations reçues : 539
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 15 décembre 2018, 14:16

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

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, 14:45

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.
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'931
Appréciations reçues : 539
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR

Message par Steelson » 15 décembre 2018, 16:53

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

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, 17:25

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 ! ::D Bravo !!!
sylviane.JPG
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message