XL 2016 Formules matricielles

[corsu2a]

Bonjour à tous
j'ai trouve un exemple de tableau qui permet de calculer des conges, un exemple très riche.
Dans le tuto le calcul des jours exclu les Week End et les jours fériés.
Avec cette formules
=SOMMEPROD(($C6:$AG6=GAUCHE(AH$5;3))*(JOURSEM($C$5:$AG$5;2)<5(NB.SI($AK$7:$AK$17;$C$5:$AG$5)=0))

Vous le verrez vous même exemple magnifique.

Je ne vais pas vous détallé la formule dans ce message, elle est expliquée dans le fichier joint.

En fait j'espère que vous allez pouvoir m'aider a modifier cette formule avec d'autres paramètres que les week end et jours féries.

Avec ce même tableau et sur la base de cette formule, je souhaiterais calculer les fréquences de travails suivantes.

A Du lundi au dimanche
B Du Lundi au Vendredi
C Du Vendredi au Dimanche
D Un Samedi
E Un Dimanche ou un jour férié
F Et pour finir, 1 nuit dans la semaine ( avoir car pas évident )

En fait je voudrais modifier la formule pour que :
A les jours soient compris entre jour 2 et 7 inclus
B entre jours 2 et 6
C entre jours 5 et 7
D = jour 6 ( ok =6 )
E = 7 ou Férié
F = 1 jour férié si il y a

Je reste à votre disposition si je n'ai pas été très clair.

Bien à vous

 

[corsu2a]

J'ai changé quelques trucs pour gagner en lisibilité et simplifié quelques formules.
Une proposition ci-joint.

Bonjour @Wayki
C'est magnifique !!! Un grand merci
cela fait plusieurs semaines que je bataille.
Je vais maintenant le développer sur 4 semaines et y ajouter toutes les fonctions.
Je te tiendrait au courant une fois le projet terminé.
Merci beaucoup

 

[corsu2a]

Bonjour @Wayki
j'ai présente le projet, top top, merci.
j'a maintenant d'autres précisions, que je n'avais pas avant sur la Nuit en SEM.
J'ai tout expliqué dans le fichier joint. Dis mois si c'est réalisable.
Merci encore

 

[Wayki]

Bonjour,
Ci-joint une proposition pour :
DIR = Nuit en semaine,
DIR N = Nuit seule,
DIR F = Jour férié.
A +

 

[corsu2a]

Bonjour,
Ci-joint une proposition pour :
DIR = Nuit en semaine,
DIR N = Nuit seule,
DIR F = Jour férié.
A +

Bonjour @Wayki
Les formules pour le calcul des jours féries et nuit seules mais des que je change le "F" pour mettre DIRF ça ne fonctionne plus car je suis obligé d'indiquer la fonction.
=SOMMEPROD(($B$5:$H$5=RECHERCHEH("N";$B$5:$H$5;1))*1) IDEALEMENT il faut mettre DIRN à la place de "N" mais des que je change ça me comptabilise aussi un jour férié. Pour les jours fériés on peut se passer de formules, je ferais un calcul de couleur (Noir) sur le planning. Mais pour DIR et DIRN, ce serait top de pourvoir les distinguer.
Est ce qu'il est possible aussi de de ne pas compter une cellule dans la Variable Lun_Dim, si la cellule = DIRJ car il n'a pas fait la nuit. Je pensais a une formule avec DIR ou DIRN alors 1. Tu en penses quoi. Je sais ça devient complique.......
Je pense que c'est juste un réglage
Merci
Bien à toi

 

[Wayki]

Salut,
Pas de soucis, je me suis dis quand j'ai construit la formule que dans le planning il n'y avait pas forcément que des dir.
Du coup, enlever la rechercheH et remplacer par la correspondance exacte CAD "DIR N" pour l'exemple.
Pour Lun_Dim, en rajoutant la condition <>"DIR J".
Je suis curieux de savoir comment après tu vas t'en sortir pour monter un planning facile d'utilisation
A +

 

[Wayki]

Ci joint le fichier modifié pour Lun_Dim et jour férié, je te laisse le soin de modifier le reste

 

[corsu2a]

Ci joint le fichier modifié pour Lun_Dim et jour férié, je te laisse le soin de modifier le reste

Bonjour @Wayki
Merci encore mille fois pour ton aide.
Je modifie les formules et je reviens vers toi .

 

[corsu2a]

Bonjour @Wayki
je met en PJ le fichier que j'ai réaliser et qui se rapproche de la réalité, il ne manque que les noms.
La partie de calcul sur la semaine fonctionne super bien Merci et bravo, je n'ai rien changé. Plusieurs colonnes sont masquées pour plus de lisibilité. tu trouveras a gauche du plannings a partir de B14, les stats par fonction et a droite à partir de la cellule AU14 notre fameux calcul de rémunérations les autres semaines.

J'ai changé les formules pour les Nuit en SEM : =NB.SI(AO15:AU15;"DIR.") bien sur DIR devient DIR.
J'ai aussi changé les formules pour les jours fériés, c'est a dire que je vais mettre tous les jours féries en couleur sur les 12 mois et ensuite j'ai mis cette formule : =SI(ESTVIDE(AQ15);"";"1") (AQ15 correspond a la cellule du jour férié dans l'exemple)

Enfin j'ai change le calcul des nuits seules par =NB.SI(AO15:AU15;"DIR N"), en nommant la fonction DIR N

J'ai maintenant un autre problème, certains agents polyvalents peuvent occuper plusieurs fonctions. L'exemple est dans le fichier en PJ.
Je n'ai pas de formule pour ça, je me demandais si on pouvait imbriquer cette formule
=SIERREUR(CHERCHE("*CGGR*";AO27)=1;FAUX). Sinon je rajouterais autant de colonnes que j'ai de fonction avec cette formule mais ça va me faire un énorme fichier avec XXXXX colonnes.
Tu trouveras les différentes fonctions dans le menu déroulant du planning mais un exemple me va très bien après j'adapte. Je ne sais pas si c'est possible.
Merci encore pour ton aide et j'espère que tu apprécies ce travail collaboratif.
A très vite, j'ai hâte de présenter le planning fini