recherche formule matricielle

[fenec]

Bonjour le forum

Cela fait deux jours que je cherche en vain une formule qui me donnerait en "L13" le nombre réel de congés pris.

J’y suis parvenu pour le prévisionnelle en "I13" mais la je bloque.
Voici un petit exemple du résultat attendu :

Actuellement dans mon calendrier j’ai 2 CP ce qui me donne bien en "I13" 5 jours
Une journée posée valant 2.5 jours.
Par contre en "L13"le résultat souhaité est 2.5 puisque la journée posée est en mars donc pas encore prise réellement.
Pour essayer d’être encore un peu plus précis, il faudrait que la formule me donne le nombre de congés pris à la date d’aujourd’hui.

En espérant avoir été assez clair…

Cordialement,

Philippe.

 

[CISCO]

Re : recherche formule matricielle

Bonsoir

Dans ton tableau, où est-ce qu'on voit ces 2 CP ?

@ plus

 

[Victor21]

Re : recherche formule matricielle

Bonsoir fenec, CISCO

Dans ton tableau, où est-ce qu'on voit ces 2 CP ?

Le 6 septembre et le 5 mars.
Un p'tit coup d'mou, CISCO

 

[fenec]

Re : recherche formule matricielle

Bonjour le forum, CISCO, Victor21

En complément de la réponse de Victor21 dans l'onglet Calendrier

A+

 

[CISCO]

Re : recherche formule matricielle

Bonjour

@ Victor21 : Je n'avais même pas ouvert la feuille "calendrier" et puis j'aime bien que le besoin soit correctement détaillé.

@ plus

P.S : Bonjour et OK fenec.

 

[job75]

Re : recherche formule matricielle

Bonjour fenec, CISCO, Patrick,

Un peu de ménage était nécessaire, il faut définir les noms ref matref matdat matjour pour y voir plus clair.

Formule en I12 à tirer vers le bas :

=SI(OU($B$3={"SD";"1 - SD";"2 - SD"});SOMMEPROD(matref*SIGNE(matjour)*(1+1,5*(matjour>5))))+SI(OU($B$3={"1 - 3x8";"2 - 3x8";"3 - 3x8"});SOMMEPROD(matref*SIGNE(matjour)*(matjour<6)))+SI(OU($B$3={"1 - 5x8";"2 - 5x8";"3 - 5x8";"4 - 5x8";"5 - 5x8"});SOMMEPROD(matref*SIGNE(matjour)))

Maintenant il suffit de compléter pour obtenir cette formule en L12, à tirer vers le bas :

=SI(OU($B$3={"SD";"1 - SD";"2 - SD"});SOMMEPROD((matdat<=C$6)*matref*SIGNE(matjour)*(1+1,5*(matjour>5))))+SI(OU($B$3={"1 - 3x8";"2 - 3x8";"3 - 3x8"});SOMMEPROD((matdat<=C$6)*matref*SIGNE(matjour)*(matjour<6)))+SI(OU($B$3={"1 - 5x8";"2 - 5x8";"3 - 5x8";"4 - 5x8";"5 - 5x8"});SOMMEPROD((matdat<=C$6)*matref*SIGNE(matjour)))

Avec SOMMEPROD plus besoin de validation matricielle.

Fichier joint.

 

[job75]

Re : recherche formule matricielle

Re,

Pour définir le nom ref on peut utiliser NB.SI, c'est un peu plus simple :

=SI(NB.SI('Etat de Congés'!$C12;"C*P*");"CP";SI(NB.SI('Etat de Congés'!$C12;"RCHS*");"RCHS";'Etat de Congés'!$C12))

Fichier (2).

A+

 

[job75]

Re : recherche formule matricielle

Re,

Le ménage n'était pas fini, formule avec un seul SOMMEPROD en I12 :

=SOMMEPROD(matref*SIGNE(matjour)*(OU($B$3={"SD";"1 - SD";"2 - SD"})*(1+1,5*(matjour>5))+OU($B$3={"1 - 3x8";"2 - 3x8";"3 - 3x8"})*(matjour<6)+OU($B$3={"1 - 5x8";"2 - 5x8";"3 - 5x8";"4 - 5x8";"5 - 5x8"})))

Et en L12 :

=SOMMEPROD((matdat<C$6)*matref*SIGNE(matjour)*(OU($B$3={"SD";"1 - SD";"2 - SD"})*(1+1,5*(matjour>5))+OU($B$3={"1 - 3x8";"2 - 3x8";"3 - 3x8"})*(matjour<6)+OU($B$3={"1 - 5x8";"2 - 5x8";"3 - 5x8";"4 - 5x8";"5 - 5x8"})))

Fichier (3).

A+

 

[job75]

Re : recherche formule matricielle

Re,

Allez encore un petit coup de karcher, les 2 formules se simplifient avec le nom défini choix :

=SOMMEPROD(matref*SIGNE(matjour)*((choix>10)*(1+1,5*(matjour>5))+(choix<4)*(matjour<6)+(choix>4)*(choix<10)))

=SOMMEPROD((matdat<=C$6)*matref*SIGNE(matjour)*((choix>10)*(1+1,5*(matjour>5))+(choix<4)*(matjour<6)+(choix>4)*(choix<10)))

Fichier (4).

Edit : la définition de choix tient compte du cas ou B3 est vide.

A+

 

[job75]

Re : recherche formule matricielle

Re,

Fichier (4 bis) avec une définition plus simple du nom choix :

=EQUIV(T('Etat de Congés'!$B$3);""&Equipes;0)

C'est du pinaillage mais c'est instructif non ?

A+

 

[fenec]

Re : recherche formule matricielle

Bonsoir le forum, CISCO, Vicor21, Job 75

Houlà que de proposition Job75

Je vais voir tout cela et vous tiens au courant
Mais une question quand même à froid :
Pourriez- vous me détailler un peu votre ménage sur les noms à définir afin que j’essaie de comprendre votre raisonnement car ma formule de départ me parait bien loin.
Ref correspond à :
Matref à :
Matdat à :
Matjour à :

Dans votre dernier post vous dites :

C'est du pinaillage mais c'est instructif non ?

Là, je vous réponds que si je savais pinailler comme vous je ne pense pas que je demanderais de l’aide mais le contraire comme vous.

PS : Me réserve le droit de revenir vers vous si besoin, dans tout les cas vous tiendrais au courant.

Cordialement,

Philippe.

 

[job75]

Re : recherche formule matricielle

Re,

Juste une remarque fenec sur votre formule en I13 (fichier du post #1).

Avec la condition OU($B$3={"SD";"1 - SD";"2 - SD"}) vous faites 2 sommes :

- la 1ère avec JOURSEM(...)>5

- la 2ème avec JOURSEM(...)<5

Dans ce cas le 5ème jour (vendredi) ne serait pas compté...

J'ai supposé qu'il s'agissait d'une erreur et que vous vouliez dire JOURSEM(...)<6 ou JOURSEM(...)<=5

Merci de confirmer quand même.

A+

 

[job75]

Re : recherche formule matricielle

Re,

Houlà que de proposition Job75

Seule la dernière est à retenir, les autres montrent les diverses étapes de mon raisonnement.

Merci de répondre à mon post #12.

Bonne soirée, je vais devoir m'absenter.

A+

 

[fenec]

Re : recherche formule matricielle

Bonjour le forum, Job75

Pour répondre au post #12 il s'agit effectivement d'une erreur de ma part.
Vous avez parfaitement compris qu'il fallait comprendre JOURSEM(...)<6 ou JOURSEM(...)<=5

Je vois pour mettre en place dans mon fichier final et reviens en cas de problème

Cordialement,

Philippe.

 

[job75]

Re : recherche formule matricielle

Bonjour fenec, le forum,

Dans la feuille "Calendrier", avec le nom choix et la fonction INDIRECT on allège considérablement les formules.

En C3, à copier sur C3:C33 puis F3:F33 etc :

=""&SI((B3="")+(choix=4);"";SI(choix<4;INDEX(INDIRECT("Cycle_"&choix&"_3x8");MOD(B3-Dép_3X8;Durée_3X8)+1);SI(choix<10;INDEX(INDIRECT("Cycle_"&choix-4&"_5x8");MOD(B3-Dép_5X8;Durée_5X8)+1);INDEX(SI(choix=11;Cycle_SD;INDIRECT("Cycle_"&choix-11&"_SD"));MOD(B3-Dép_SD;Durée_SD)+1))))

Pas de problème d'imbrication sur Excel 2003 et versions antérieures.

Fichier (5).

Edit : j'ai modifié la formule après la remarque de CISCO du post #17.

A+