Discussion :

[sardaucar]

Bonjour à tous !

J'essaie en ce moment de calculer une somme en fonction d'un paramètre qui est le numéro de la semaine associé à la cellule. Le problème est qu'il y a 365 jours et 53 paramètres à tester, je suis donc un peu perdu sur le moyen à mettre en place pour arriver à mes fins. Pour comprendre voici les screens des objets :


Voici le tableau pour le mois de janvier, dans la première ligne le numéro de la semaine correspondante à tester, et en vert les chiffres à additioner en fonction du numéro de la semaine.



Et voici le résultat attendu, donc par exemple pour 1 je devrais trouver 14, pour la 2 35, etc...

J'ai bien essayé avec SI, SOMME.SI, SOMME.SI.ENS, mais sans succès.

Il doit me manquer une info :/

[Menhir]

Pour te donner la formule exacte, il faudrait que tu indiques les numéros de ligne et colonne de tes deux tableaux et, éventuellement, les noms d'onglets s'ils se trouvent dans des onglets différents.
Autres question : ce qui se trouve ne entête de ton premier tableau (les dates), est-ce de vraies dates (des valeurs) ou du texte ? Par exemple, si tu utilises la fonction SEMAINE() appliquée à une de ces cellule, est-ce que ça te renvoie une valeur ou une erreur ?

[sardaucar]

Salut Menhir et merci d'avoir pris le temps de me lire !

J'ai finalement résolu mon problème actuel en utilisant la formule suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI('Prévisionnel 2015'!$E$1:$NE$1;DECALER('Prévisionnel 2015'!V$1;;(COLONNE()-5)*6);'Prévisionnel 2015'!$E6:$NE6)

Mais en fait je me suis aperçu que ça ne réglais pas la problèmatique car suivant les années mes semaines se décalent :/. En fait je dois me servir des jours de la semaine !

Donc dans la feuille prévisionnel 2015 mes valeurs sont de e6 à NE70, ma ligne de dates est de e3 à NE3, et ce sont de vraies dates. La fonction semaine m'a donc renvoyé le numéro; et la fonction jour le numéro du jour. Alors peut-être en utilisant le numéro du jour pour additionner les dates de 1 à 7 ? Mais je ne sais pas comment faire ^^'.

[78chris]

Bonjour

Tes numéros de semaine sont à l'américaine, ce qui n'est pas correct en France d'où sans doute le décalage.

Par ailleurs évite les cellules fusionnées et utilise pour les numéros la ligne semaine au dessus de ta synthèse.

[sardaucar]

Dacodac je comprends pour la semaine !

Voici ce que j'essaie de faire pour les jours :



L'idée est de trouver la formule en B10, B11, B12, etc sur 365 jours de manière à ce que d'une année sur l'autre elle puisse encore fonctionner (sinon je ferais une simple somme^^').

[78chris]

Re


J'ai du mal à suivre :

• pourquoi utilises-tu la norme anglo saxone tant pour les semaines que pour les jours ?
• Ton tableau commence tantôt au 1er janvier, tantôt au 1er octobre... et la synthèse change de sens.

Dans ces conditions comment te donner une bonne formule ?

[sardaucar]

En fait je ne mets pas tout pour ne pas surcharger les exemples, my bad ! Du coup je détailles :

Mon tableau a 365 colonnes, correspondant aux jours de chaque année. Pour les besoins du calcul je dois faire commencer mes semaines un lundi et finir un dimanche. Au début j'ai voulu passer par le chiffre de la semaine que me donne Excel mais je me suis aperçu, non seulement que ça fonctionnait pas, mais qu'en plus ça ne me donnerais pas ce que je voulais.

Donc j'ai pensé extraire le numéro du jour de la semaine qu'Excel attribue automatiquement avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=JOURSEM()

Puis me servir de cette référence pour établir ma plage de calcul, qui doit aller du lundi au dimanche, soit de 1 à 7, puis de 1 à 7, etc...

Pour la norme je ne connaissais pas son existence jusqu'à aujourd'hui donc je peux paraître approximatif, et pour la synthèse elle devra être plutôt par colonne que par ligne, comme dans mon premier screen, le suivant était pour simplifier.

Ne jamais trop simplifier donc ^^'.

[78chris]

Re

Ci-joins un tableau : j'ai évité les fioritures et fusions de cellules....

[sardaucar]

Super c'est exactement ce que je cherchais .

Par contre j'ai recopié bêtement la formule sans la comprendre, si tu avais la gentillesse de m'expliquer comment elle marche .

Merci beaucoup en tout cas !

[78chris]

Bonjour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(B$16<>"";SOMMEPROD(($B$5:$NC$5>=$B$5+8-JOURSEM($B$5;2)+(7*(B$16-2)))*($B$5:$NC$5<=$B$5+7-JOURSEM($B$5;2)+(7*(B$16-1)));$B6:$NC6);"")

Le SI permet de ne mettre la formule que s'il y a un n° de semaine au-dessus (le N° de semaine ne s'affiche que les lundis et le 1er janvier)

SOMMEPROD permet de faire l'équivalent d'un SOMME.SI mais avec plus de latitude sur les formules imbriquées.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

($B$5:$NC$5>=$B$5+8-JOURSEM($B$5;2)+(7*(B$16-2))

est la première condition : on vérifie si la période en ligne 5 (basée sur l'ensemble des dates du 1er janvier au 31/12) débute le premier jour de l'année $B$5 ou le lundi (grâce au calcul de la fin de la 1ère semaine $B$5+8-JOURSEM($B$5;2) ), ceci décalé de 7 jours en fonction du numéro de semaine en ligne 16 (7*(B$16-2).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

($B$5:$NC$5<=$B$5+7-JOURSEM($B$5;2)+(7*(B$16-1))

est la seconde condition : on vérifie si la période se termine un dimanche en décalant de 7 jours en fonction du numéro de la semaine en ligne 16

Si ces deux conditions sont réunies, la somme porte sur la plage en ligne 6.

Après avoir posté, je me suis dit que j'aurais pu faire la même chose en utilisant des numéros de colonnes plutôt que les numéros de semaines mais comme tu prévoyais le numéro de semaine...

On pourrait simplifier un peu avec une ou deux formules nommées pour le 1er dimanche par exemple...

[sardaucar]

Impeccable, plus qu'à comprendre tout ça et à trouver comment se débarasser des espaces vides entre les résultats afin d'avoir 53 colonnes et non 365 ^^'.

[danixdb]

Bonjour Sardaucar, 78Chris, Le forum

Suite à la proposition faite par 78Chris qui a retenu ton attention, et pour éviter les colonnes vides entre les semaines, je propose de travailler à partir des semaines selon la norme ISO et de l'année que l'on pourrait mettre en ligne 4 (ligne à masquer pour utilisation).
Ensuite dans le tableau recap, on commencera au 1er janvier de l'année, date que l'on incrémentera de 7 jours pour avoir la semaine suivante. Là aussi on calculera l'année/semaine et la fonction proposée par 78Chris (sommeprod) en la modulant un peu devrait répondre à ta dernière demande.
Voir exemple ci-joint, à adapter éventuellement par rapport à ton tableau de synthèse.

[Nicolas751]

Je te conseillerais de faire un nouveau tableau a droite de celui la ou tu sommes les jours en semaine dans chaque colonne (de 1 à 52)
et après tu peux sommer la colonne de la semaine en question
j'ai fait pareil pour les semaines sommées en mois

Cordialement,