calcul sur plusieurs onglets selon critères

[Ludion62]

Bonjour,

Je souhaite, dans un planning, calculer le nombre de jours estampillés selon plusieurs critères tout en tenant compte de la date courante. Pour compliquer la chose, mon fichier contient un onglet par mois ... (ci-joint un exemple du fichier)

A l'aide ! Je ne m'en sors pas

J'ai essayé plusieurs formules imbriquées (SOMMEPROD, DECALER, EQUIV, ...)sans succès.

Je serai très heureux que quelqu'un puisse me venir en aide.

Merci.

Ludion62

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Personne ne peut m'aider ?

Je suis en galère.

 

[JCGL]

Re : calcul sur plusieurs onglets selon critères

Bonjour à tous,

Un essai en C9 :

=SOMMEPROD((INDIRECT(MOIS(C$6)&"!$A$2:$A$10")=Recap!$C$4)*(INDIRECT(MOIS(C$6)&"!$E$1:$AI$1")>=Recap!C$6)*(INDIRECT(MOIS(C$6)&"!$E$1:$AI$1")<=Recap!C$7)*(INDIRECT(MOIS(C$6)&"!$D$2:$D$10")=Recap!$A$9)*(INDIRECT(MOIS(C$6)&"!$E$2:$AI$10")=Recap!$B9))

J'ai renommé les feuilles en 10, 11 et 12
J'ai ajouté une validation en A9

A+ à tous

 

[ROGER2327]

Re : calcul sur plusieurs onglets selon critères

Bonjour Ludion62

Pas sûr d'avoir compris (notamment, je n'ai pas tenus compte de la cellule A9 bien que son fond soit jaune).
Mais peut-être cette formule matricielle en C9, à valider par Ctrl + Maj + Entrée, puis à recopier à droite et en bas ?

[COLOR="DarkSlateGray"][B]=SOMME(($C$4=SECTEUR)*
($B9=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6)-1;nM;0))&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6)-1;nM;0))&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6)-1;nM;0))&"!$E$1:$AI$1")))+
SOMME(($C$4=SECTEUR)*
($B9=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6);nM;0))&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6);nM;0))&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6);nM;0))&"!$E$1:$AI$1")))+
SOMME(($C$4=SECTEUR)*
($B9=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6)+1;nM;0))&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6)+1;nM;0))&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(INDEX(mM;EQUIV(MOIS($C$6)+1;nM;0))&"!$E$1:$AI$1"))
)[/B][/COLOR]

C'est un peu lourd : vos découpages en mois et en semaines n'ayant pas nécessairement de frontière commune, il faut tenir compte des semaines empruntant des jours à deux mois.
(J'ai ajouté deux plages nommées : voir le classeur joint)​

ROGER2327
#2085
___
____________
Bonjour JCGL, je vois que nous avons presque compris la même chose... Sauf que je n'ai pas "compris" A9 ! C'est réparé plus bas.

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Merci à vous deux pour le temps que vous avez bien voulu accorder à la résolution de mon problème.

Je teste vos deux solutions et je reviens vers vous.

Merci encore.

Ludion62

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Rebonjour Messieurs,

Je reviens vers vous pour mes premiers tests :

- JCGL : le calcul n'est pas juste lorsque la semaine est à cheval sur 2 mois (comme l'a signalé d'ailleurs ROGER2327) puisque le calcul du mois se fait sur une seule cellule

- ROGER2327 : cela fonctionne parfaitement, mais la cellule A9 est à inclure dans la formule car elle peut être variable (NR ou R)

Je suis preneur pour les modifications.

Merci à vous deux.

 

[ROGER2327]

Re : calcul sur plusieurs onglets selon critères

Re...

Une fois qu'on a le principe, ça ne doit pas être si difficile d'ajouter une condition... J'en profite pour raccourcir un peu en factorisant ($C$4=SECTEUR).

[COLOR="DarkSlateGray"][B]
FORMULE ERRONÉE SUPPRIMÉE

VOIR LE MESSAGE #17[/B][/COLOR]

ROGER2327
#2086

 

[klin89]

Re : calcul sur plusieurs onglets selon critères

Bonjour à tous,
Bonjour Ludion62,

Fais une recherche sur le forum avec le pseudo de Boisgontier complétée par le mot "planning" ou "récap*", tu trouveras diverses versions que tu pourras décliner selon tes besoins.

Un exemple ici avec des récapitulatifs.

https://www.excel-downloads.com/threads/creation-dun-planning-mensuel-sur-une-annee.124415/

Klin89

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Re à tous,

Merci à ROGER2327, je vais tester dès demain. Désolé mais la formule est un peu longue pour moi et je ne suis pas tout ...

Merci à Klin89 pour l'info.

Merci à JCGL pour ta participation.

Je vous tiens informés de la suite.

Ludion62

 

[ROGER2327]

Re : calcul sur plusieurs onglets selon critères

Re...

Je viens de modifier la formule dans le message #7 pour qu'elle ne provoque pas d'erreur aux "bornes", c'est-à-dire lorsqu'on utilise des dates légèrement antérieures au premier mois ou postérieures au dernier mois utilisé.​

ROGER2327
#2088

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Bonsoir ROGER2327,

Après test de ta nouvelle formule, a priori il subsiste une petite erreur.
En effet, lorsque je passe sur la semaine 49 en B2, la formule me retourne 8 en D10 alors que le résultat devrait être 4.

Dans ta première formule postée, le problème des semaines à cheval sur 2 mois était résolu sauf qu'elle ne prenait pas en compte le champ variable en A9.

Là je sèche, je ne sais pas d'où peut provenir l'erreur de calcul.

As-tu une idée ?

Merci en tout cas pour le travail effectué : je t'ai fait couché tard hier soir ...

Ludion62

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Re ROGER2327,

J'ai modifié la formule comme suit :

=SOMME(($C$4=SECTEUR)*( SI(ESTERR(INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)-1;nM;0))&"!$A$1"));0; ($A$9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)-1;nM;0))&"!$D$2:$D$10"))* ($B9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)-1;nM;0))&"!$E$2:$AI$10"))* (C$6<=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)-1;nM;0))&"!$E$1:$AI$1"))* (C$7>=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)-1;nM;0))&"!$E$1:$AI$1")))+ SI(ESTERR(INDIRECT(INDEX(mM;EQUIV(MOIS(C$6);nM;0))&"!$A$1"));0; ($A$9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6);nM;0))&"!$D$2:$D$10"))* ($B9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6);nM;0))&"!$E$2:$AI$10"))* (C$6<=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6);nM;0))&"!$E$1:$AI$1"))* (C$7>=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6);nM;0))&"!$E$1:$AI$1")))+ SI(ESTERR(INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)+1;nM;0))&"!$A$1"));0; ($A$9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)+1;nM;0))&"!$D$2:$D$10"))* ($B9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)+1;nM;0))&"!$E$2:$AI$10"))* (C$6<=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)+1;nM;0))&"!$E$1:$AI$1"))* (C$7>=INDIRECT(INDEX(mM;EQUIV(MOIS(C$6)+1;nM;0))&"!$E$1:$AI$1"))) ))

Apparemment cela fonctionne !
Qu'en penses-tu ?

Ludion62

 

[exene]

Re : calcul sur plusieurs onglets selon critères

Bonsoir,

J'ai suivi la discussion et je suis particulièrement intrigué par la formule de ROGER .Je n'ai fait fait qu'une copie de la formule en essayant d'intégrer la variable en A9 (PT non remplaçable et PT remplaçable), pourrais tu tester et nous dire si le résultat est valide.

A+

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Bonsoir Exene,

Ta formule fonctionne très bien mais seulement de la S46 à la S50.
Pour les semaines antérieures et postérieures le résultat renvoyé est celui-ci : #REF!

As-tu regardé la formule que j'ai postée un peu plus tôt dans la journée ?

Ludion62

 

[exene]

Re : calcul sur plusieurs onglets selon critères

Bonjour,

Tu as vu juste, la formule ne fonctionne pas à partir de la semaine 51. Sauf erreur de ma part, il me semble que ta nouvelle formule et la formule originelle de ROGER fasse de même à la semaine 51. Peux tu vérifier ?.

A+