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

Bonjour Exene,

A priori, après plusieurs tests, la formule fonctionne même au-delà de la semaine 51.

Ludion62

 

[ROGER2327]

Re : calcul sur plusieurs onglets selon critères

Re...

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

J'en pense que c'est parfaitement illisible et que je n'ai pas envie de me taper un découpage à peu près rationnel. En copiant/collant la formule, j'obtiens #REF!, certainement à cause de la présence d'espaces indésirables.

(...) lorsque je passe sur la semaine 49 en B2, la formule me retourne 8 en D10 alors que le résultat devrait être 4 (...)

Exact ! Il y a une erreur dans la formule, qui doit être :

[SIZE="3"][COLOR="DarkSlateGray"][B]=SOMME(($C$4=SECTEUR)*(
SI(ESTERR(INDIRECT(INDEX(mM;EQUIV(MOIS(C$7)-1;nM;0))&"!$A$1"));0;
($A$9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$7)-1;nM;0))&"!$D$2:$D$10"))*
($B9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$7)-1;nM;0))&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(INDEX(mM;EQUIV(MOIS(C$7)-1;nM;0))&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(INDEX(mM;EQUIV(MOIS(C$7)-1;nM;0))&"!$E$1:$AI$1")))+
SI(ESTERR(INDIRECT(INDEX(mM;EQUIV(MOIS(C$[COLOR="Red"]6[/COLOR]);nM;0))&"!$A$1"));0;
($A$9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$[COLOR="Red"]6[/COLOR]);nM;0))&"!$D$2:$D$10"))*
($B9=INDIRECT(INDEX(mM;EQUIV(MOIS(C$[COLOR="Red"]6[/COLOR]);nM;0))&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(INDEX(mM;EQUIV(MOIS(C$[COLOR="Red"]6[/COLOR]);nM;0))&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(INDEX(mM;EQUIV(MOIS(C$[COLOR="Red"]6[/COLOR]);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")))
))[/B][/COLOR][/SIZE]

Pour tester, j'ai ajouté les mois de Mai et Juin, en prenant les données d'Octobre et de Novembre. Ça permet de voir l'effet d'un décalage des semaines sur les même données, et ça a l'air de coller. Voyez par exemple pour les semaines 23 et 45, paramètres NR et DIRECTION ou GESTION.
Par ailleurs, je ne constate pas d'erreur avec les semaines 50 et suivantes.

Mais tout cela est assez compliqué : il y a peut-être encore du chagrin à venir...
​

Bon courage.
ROGER2327
#2093

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Re ROGER2327,

Effectivement la formule que j'ai postée est illisible mais c'est la même que celle que tu as remise : j'ai fait la même modification que toi en remplaçant 7 par 6.

J'ai testé à plus grande échelle et a priori je n'ai pas de bug et les résultats sont corrects.

Je vous remercie donc tous pour votre contribution

A bientôt.

Ludion62

 

[Monique]

Re : calcul sur plusieurs onglets selon critères

Bonjour,

Je suis en retard ?
En doublant les critères du SommeProd de JCGL
2ème série de critères = Mois(C$6)+1
et en ajoutant un onglet 13 (une feuille vide)

 

[Ludion62]

Re : calcul sur plusieurs onglets selon critères

Bonjour Monique,

Excellent ! J'ai bien l'impression que ça fonctionne, et c'est bien moins lourd que ma précédente formule.
Je testerai lundi à plus grande échelle.

Merci encore pour votre implication.

Ludion62

 

[JCGL]

Re : calcul sur plusieurs onglets selon critères

Bonjour à tous,

Excellent ! J'ai bien l'impression que ça fonctionne

Le jour où tu trouveras une formule de Monique qui ne fonctionne pas :

• Ouvre une discussion sur le Salon XLD
• Vérifie l'énoncé de ta demande
• Monique a du abuser de Kig ha farz (Ah mon séjour à Molène)

A+ à tous

 

[Brigitte]

Re : calcul sur plusieurs onglets selon critères

Ouarf... Bien vu (merci mes lunetteS), JC... J'adore :

Le jour où tu trouveras une formule de Monique qui ne fonctionne pas :

4/5 ans que je la connais, jamais croisé la moindre formule ne fonctionnant pas non plus. Et pourtant comme bêta testeuse, je me pose là.

Monique, si tu passes par là, viens sourire avec nous.


ET Roger, tu crois qu'il marche à quoi, lui, quand je vois ses formules dans ce fil... C abracadabresque... Vous me laissez PANTOISE les enfants !!!

 

[JCGL]

Re : calcul sur plusieurs onglets selon critères

Bonjour à tous,

Pour Roger, je pense que tout est "Kapital"...
Et j'en suis ravi

A+ à tous

 

[ROGER2327]

Re : calcul sur plusieurs onglets selon critères

Re...

Bonjour Monique,

Excellent ! J'ai bien l'impression que ça fonctionne, et c'est bien moins lourd que ma précédente formule.
Je testerai lundi à plus grande échelle.

Merci encore pour votre implication.

Ludion62

Je constate que vous avez changé de problème : vos feuilles ne sont plus nommées "Octobre", "Novembre", "Décembre" mais "10", "11", "12". Le problème est plus simple et la formule :

[COLOR="DarkSlateGray"][B]=SOMME(($C$4=SECTEUR)*(
SI(ESTERR(INDIRECT(MOIS(C$7)-1&"!$A$1"));0;
($A$9=INDIRECT(MOIS(C$7)-1&"!$D$2:$D$10"))*
($B9=INDIRECT(MOIS(C$7)-1&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(MOIS(C$7)-1&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(MOIS(C$7)-1&"!$E$1:$AI$1")))+
SI(ESTERR(INDIRECT(MOIS(C$6)&"!$A$1"));0;
($A$9=INDIRECT(MOIS(C$6)&"!$D$2:$D$10"))*
($B9=INDIRECT(MOIS(C$6)&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(MOIS(C$6)&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(MOIS(C$6)&"!$E$1:$AI$1")))+
SI(ESTERR(INDIRECT(MOIS(C$6)+1&"!$A$1"));0;
($A$9=INDIRECT(MOIS(C$6)+1&"!$D$2:$D$10"))*
($B9=INDIRECT(MOIS(C$6)+1&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(MOIS(C$6)+1&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(MOIS(C$6)+1&"!$E$1:$AI$1")))
))[/B][/COLOR]

est suffisante (662 caractères au lieu de 992).
Plus besoin des deux plages nommées que j'avais ajoutées précédemment. Pas besoin d'ajouter une feuille nommée "13".
On peut encore la simplifier si (contrairement à ce que j'avais compris) la semaine 41 ne vous intéresse pas. Elle couvre pourtant une partie du mois d'octobre.
Votre formule moins lourde présente aussi la particularité de ne pas tenir compte du lundi 30 novembre 2009 dans la semaine 2009-W49 (et plus généralement de ne pas tenir compte du début des semaines à cheval sur deux mois). Dans ce cas,

[COLOR="DarkSlateGray"][B]=SOMME(($C$4=SECTEUR)*(
SI(ESTERR(INDIRECT(MOIS(C$6)&"!$A$1"));0;
($A$9=INDIRECT(MOIS(C$6)&"!$D$2:$D$10"))*
($B9=INDIRECT(MOIS(C$6)&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(MOIS(C$6)&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(MOIS(C$6)&"!$E$1:$AI$1")))+
SI(ESTERR(INDIRECT(MOIS(C$6)+1&"!$A$1"));0;
($A$9=INDIRECT(MOIS(C$6)+1&"!$D$2:$D$10"))*
($B9=INDIRECT(MOIS(C$6)+1&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(MOIS(C$6)+1&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(MOIS(C$6)+1&"!$E$1:$AI$1")))
))[/B][/COLOR]

doit suffire (446 caractères). C'est à peine plus lourd que votre nouvelle formule, sans toutefois nécessiter l'ajout de feuilles.
Si de plus la semaine 41 ne vous intéresse pas et que vous disposez d'une feuille nommée "13", simplifiez encore :

[COLOR="DarkSlateGray"][B]=SOMME(($C$4=SECTEUR)*(
($A$9=INDIRECT(MOIS(C$6)&"!$D$2:$D$10"))*
($B9=INDIRECT(MOIS(C$6)&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(MOIS(C$6)&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(MOIS(C$6)&"!$E$1:$AI$1"))+
($A$9=INDIRECT(MOIS(C$6)+1&"!$D$2:$D$10"))*
($B9=INDIRECT(MOIS(C$6)+1&"!$E$2:$AI$10"))*
(C$6<=INDIRECT(MOIS(C$6)+1&"!$E$1:$AI$1"))*
(C$7>=INDIRECT(MOIS(C$6)+1&"!$E$1:$AI$1"))
))[/B][/COLOR]

C'est un poil plus court que votre nouvelle formule (360 caractères contre 395, mais ça ne fait pas tout à fait la même chose).
En fait, je crois que je n'ai pas vraiment compris vos besoins. Désolé.​

ROGER2327
#2110