Aide sur une Formule pour faire un calcul par mois

[Tophe2]

Bonjour le Forum,

J'avais ouvert une discussion pour un Récap par mois j'ai eu l'aide du Forum notamment JHA qui fonctionne bien cependant la formule comptabilise les semaines de l'année j'ai poser une question pour un calcul par mois mais malheureusement je n'ai pas eu de réponse.

Je me permets d'ouvrir une nouvelle discussion pour avoir votre aide, je souhaiterai pouvoir comptabiliser les mois (onglet Récap Jours) je m'explique par exemple la semaine 5 de 2017 il y a 2 jours en Janvier et 5 jours en février comment puis je procéder pour avoir un calcul par mois et pour chaque jours de la semaine tous les lundi de Janvier ..... afin d'avoir un récap annuel et analyser les chiffres.

J'ai plusieurs tableaux à faire et cela m'aiderai beaucoup de pouvoir adapter la formule ou connaitre la marche à suivre pour faire ce calcul (doit on passer par une macro ?).

je vous glisse le fichier en pièce jointe.

merci pour votre aide et bonne journée
Christophe.

 

[chris]

Bonjour

Voir ma réponse dans ton fil précédent
https://www.excel-downloads.com/threads/probleme-tableau-annuel-avec-recap-par-mois.20015610/

que tu aurais du remonter plutôt que faire un nouveau fil.

 

[JHA]

Bonjour à tous,
Bonjour Chris🙂,

Un essai avec ce que je comprends mais pas sur du tout du résultat.

JHA

 

[Tophe2]

Bonjour JHA et Chris,

Pourrais tu m'expliquer la formule pour la reproduire en fonction des besoins sur les différents calculs que j'ai besoin (modification des chiffres ou zone) pour reproduire cela sur d'autre critère et Mois ?? et encore une fois MERCI pour ton aide précieuse. je regarde tout ça avec l'ensemble des calculs et il en a !!!!!

Chris, merci pour l'information mais d'où vient le pb pour avoir le bon résultat j'ai regardé la formule et le résultat et ça semblait bon je voudrais comprendre !! n'ayant pas eu de réponse j'ai préféré "peut être à tort" ouvrir un nouveau post.....

merci pour votre réponse et votre aide Chris et JHA car dans mon quotidien cela m'aide ÉNORMÉMENT bon 1er MAI

et bonne journée
Christophe.

 

[chris]

Bonjour

Comme expliqué sur l'autre post on ne peut faire une moyenne de moyenne.

Tous tes taux hebdomadaires sont faux dans les onglets S1 à S52.

Questions :

• Pourquoi un format xls alors que tu as 2007 ?
• Pourquoi ne pas adopter la démarche inverse : un seul tableau avec les dates des semaines 1 à 52 et toutes les statistiques par TCD ? Ce serait plus simple et surtout plus exact...

 

[Tophe2]

Bonjour Chris,

Quelle serait la démarche pour avoir une moyenne exacte ? "somme(d5:d11)/somme(c5:c11)"

En me lançant dans cet outils il est vrai que j'ai enregistré le fichier en XLS qu'elle serait l'avantage de passer sur un autre format car c'est réalisable effectivement ?

Tu as raison sur la démarche inverse mais je pensais que ça serait plus simple pour l'utilisation d'avoir 1 onglet par semaine en passant par 1 onglet pour toute l'année ça me semble plus complexe et peut être moins lisible aurais tu une piste ?

oui c'est vrai pour le TCD mais je voulais avancer petit à petit et l'aide de JHA pour les formules et le reste m'a permis d'avancer tout en comprenant ses propositions pour les adapter.

Et je ne comprends pas pour l'instant la dernière formule de JHA pour la reproduire sur les autres mois et autres critères en tout cas ce qu'il faut modifier pour la reproduire !!

Merci pour tes réponses
Bonne Journée
Christophe.

 

[chris]

Re

...Quelle serait la démarche pour avoir une moyenne exacte ? "somme(d5:d11)/somme(c5:c11)"

Oui pour l'onglet, mais on ne peut ensuite utiliser ces moyennes pour en calculer d'autres (hebdo, mensuelles ou annuelles) : il faut toujours partir des lits pour calculer des moyennes.

...il est vrai que j'ai enregistré le fichier en XLS qu'elle serait l'avantage de passer sur un autre format ....

Le poids est réduit en xlsx mais surtout tu évites de perdre les nouvelles fonctionnalités non compatibles avec l'ancien format xls.

...je ne comprends pas pour l'instant la dernière formule de JHA pour la reproduire sur les autres mois...

Mais comme dit plus haut ces formules utilisant des moyennes, même si tu rectifies les onglets, le calcul sera faux...

 

[Tophe2]

Bonjour Le Forum,
Bonjour Chris,
Bonjour JHA,

Je comprends bien la situation et les points que tu abordes concernant le fichier, il faut que je vois comment je peux faire, si tu as une piste de départ je prends (je veux bien apprendre à pécher en fonction de mes possibilités)

j'aurai aimé comprendre la formule de JHA pour éventuellement l'adapter en fonction des situation que je peux rencontrer et surtout comprendre les zones à modifier (en fonction des mois et des critères) !!.

est il possible de m'aider sur ce point (ci dessous la formule matricielle qui se trouve dans le dernier fichier envoyé cellule C38).

=SOMMEPROD(SOMME.SI(INDIRECT("'S"&LIGNE(INDIRECT("$1:$"&52))&"'!B"&COLONNE()+2);"<="&FIN.MOIS(1&$C$3;0);INDIRECT("'S"&LIGNE(INDIRECT("$1:$"&52))&"'!h"&LIGNE(DECALER('S1'!$A$4;EQUIV('Récap Jours'!$A5;'S1'!$A$5:$A$88;0)😉)+(COLONNES($A:A)-1))))/INDEX(Mois!$C$15:$N$21;EQUIV('Récap Jours'!C$4;NomJours;0);EQUIV(MOIS(1&'Récap Jours'!$C$3);MOIS(Mois!$C$13:$N$13);0))

merci par avance
bonne journée
Christophe.

 

[chris]

Bonjour

Schématiquement la formule est basée sur une fonction SOMME.SI

dont la plage de critère est obtenue avec

INDIRECT("'S"&LIGNE(INDIRECT("$1:$"&52))&"'!B"&COLONNE()+2)

qui donne grosso modo S1:S52!B5
et on ne borne la période que sur un côté ce qui est insuffisant avec

"<="&FIN.MOIS(1&$C$3;0)

la plage à additionner est obtenue avec

INDIRECT("'S"&LIGNE(INDIRECT("$1:$"&52))&"'!h"&LIGNE(DECALER('S1'!$A$4;EQUIV('Récap Jours'!$A5;'S1'!$A$5:$A$88;0);))+(COLONNES($A:A)-1))

qui donne grosso modo S1:S52!H5

donc on additionne les moyennes.

Il faut

• raisonner sur l'ensemble des occupations (colonne D)/l'ensemble des disponibilités (colonne C)
• borner la période des 2 côtés (début et fin de mois)
• revoir le calcul des référence au mois et au jour de la semaine pour que la formule soit copiable dans les deux sens
• ...

et donc bien alourdir la formule.

Le problème est que pour CHAQUE moyenne il faudrait une formule différente :

• on ne peut faire la moyenne des moyennes des lundis du mois pour une moyenne annuelle du lundi
• ni celle des divers jours pour la moyenne Hebdomaire
• ni celles des services en utilisant les moyennes des lundis ou des semaines ou de l'année par service
• etc, etc

avec des formules matricielles, que tu ne maîtrises pas, et sur 52 onglets il y a peu de chance que cela fonctionne correctement.

Alors qu'un TCD, basé sur une source bien pensée, fait tout cela naturellement et sans erreur de moyenne...

 

[Tophe2]

Bonjour Chris,

je comprends tout ça et effectivement je ne maitrise pas ce genre de formule, aurais tu une base de départ à me proposer que je pourrais reproduire en fonction des différents critères que j'aurai besoin par jour, semaine, mois (tous les lundis de janvier, de l'année.....).

Merci encore pour ton aide
bonne Journée
Christophe.

 

[Tophe2]

Bonjour le Forum,

je recherche toujours un peu d'aide pour réaliser les différents totaux.

merci par avance
bonne journée
Christophe.