Microsoft 365 SOMME d'une plage variable

[Amanite]

Bonjour.

Je ne parviens pas à résoudre le problème suivant :

• J'aimerais additionner les valeurs de tous les mois précédant le total, lequel se trouve après "Décembre"
• Le 1er problème est que le nombre de mois précédant peut varier la première année. On peut en effet décider quand commence le projet : ça peut être en janvier, mais ça peut aussi être en juillet et du coup le total de la 1ère année ne doit additionner que 6 cellules au lieu de 12. La 2ème année additionnera 12 cellules. La 3ème sera à nouveau tronquée car comme le projet est sur 3 ans (donc 36 colonnes), la 3ème année se terminera dans mon exemple en juin si la 1ère a commencé en juillet)
• Le second problème est que la colonne qui additionne le résultat (parfois tronqué) d'une année bouge elle aussi : en effet, si le projet débute en novembre 2020, la colonne additionnant l'année 2020 sera en 3ème position (après novembre et décembre), mais si on débute en février 2020, elle sera en 12ème position, décalant d'autant toutes les plages mentionnées au point précédant.

Ci-joint une saisie d'écran : dans cet exemple, on a décidé de commencer le projet en septembre 2020 (je saisis le mois de départ dans une cellule non-visible sur mon image, et les 36 + 3 colonnes fixes se remplissent avec les bons mois en entête. Excel est instruit d'écrire "Annuel" juste après tous les mois de décembre, puis de reprendre la nomenclature à janvier de l'année suivante.

Mais du coup le premier calcul annuel se fait après 4 cellules seulement. Pour 2021, il faut additionner les 12 mois, mais en 2022 il ne restera que 8 mois à additionner (mon image est tronquée après février 22 pour limiter sa largeur). En d'autres termes, les colonnes des totaux fluctuent en fonction de la date de départ du projet

J'ai pour l'instant laissé les cellules des totaux en blanc avec une condition basée sur la vérification du mot "annuel" dans la liste des dates. Comment faire pour y mettre l'addition des seuls mois écoulés dans l'année en question ?

Merci d'avance pour vos conseils (étant précisé que si je peux éviter le VBA je préfère car pas très doué dans ce langage).

 

[JHA]

Bonjour à tous,

Sans fichier joint, tu n'auras pas beaucoup de réponses car on ne veut pas tout se retaper.
Une image ne nous sert pas à grand chose pour te trouver une solution, en plus on ne voit que des chiffres donc pas de soucis pour annexer le fichier.

Avec la fonction decaler() dans ton fichier, peut-être....

JHA

 

[sylvanu]

Bonjour Amanite, JHA,
Il me semble qu'un simple Somme.si est suffisant :

=SOMME.SI($A1:Q1;">=1/1/2021";$A2:Q2)

Il fait la somme pour toutes les dates supérieur au 01/01/2021. la colonne A reste fixe depuis le début de la ligne.
Comme les dates postérieures sont après la cellule de calcul, elles ne sont pas pris en compte. ( ce qui évite un somme.si.ens.

 

[Amanite]

Bonjour,

Merci pour ton conseil.

Ci-joint donc l'un des onglets de ce (gros) fichier.

 

[sylvanu]

Bonjour,

Ci-joint donc l'un des onglets de ce (gros) fichier.

 

[Amanite]

Merci pour ta suggestion Silvanu.

Le problème est que ta proposition suppose que l'on connaisse la bonne date au bon emplacement. Ainsi, dans ton exemple, tu poses de manière fixe le 1/1/2021 à une cellule fixe.

Or, dans mon cas, on ignore dans quelle cellule devra se faire l'addition, et surtout quelle sera la date que l'on doit comparer avec l'opération >=

 

[sylvanu]

Re,
Je pense que vous n'avez pas compris mon fichier,
A la cellule "annuel" il additionne toutes les valeurs de l'année en cours. Donc si ça démarre en Juillet il additionnera 6 mois puisqu'il additionne toutes les valeurs de la même année.
C'est ce que j'ai compris de :

J'aimerais additionner les valeurs de tous les mois précédant le total, lequel se trouve après "Décembre"

Ci joint plusieurs exemples.
De ce j'ai compris, Lorsqu'il y a "Annuel" on doit faire la somme de l'année en cours, qu'elle soit ou non complète.
Lorsqu'on fait :

=SOMME.SI($A1:D1;">=1/1/2020";$A2:D2)

On fait l'addition de toutes les cellules qui correspondent à un mois de 2020.
Si ce n'est pas ce que vous désirez alors précisez où se trouve mon erreur, je rectifierais.

Addon: on peut simplifier la formule avec :

=SOMME.SI($A1:D1;">=1/1/"&ANNEE(D1);$A2:D2)

on récupère l'année de la cellule précédente de "annuel", la formule est donc automatique.

 

[Amanite]

De plus, je n'arrive pas à reproduire ta formule dans un format qui n'écrit pas une date fixe comme toi :

SOMME.SI($A1:D1;">=1/1/2020";$A2:D2)

où je tente de remplacer la date fixe par une date variable, comme suit :

SOMME.SI($A1:D1;"<DATE(ANNEE(D1);MOIS(D1)+1;JOUR(D1))";$A2:D2)

par exemple pour comparer avec la date du mois suivant, mais la formule me renvoie 0.

 

[sylvanu]

Voir ma PJ, la date est automatique, je fais "1/1/" en dur pour le 1er janvier et annee( cellule précédente ) pour récupérer l'année.

 

[Amanite]

Nos messages se sont croisés. Je crois comprendre la confusion : vous vous basez uniquement sur l'année 2020, alors mon projet est sur 3 ans, et il y a au moins 3 colonnes de totaux : une pour l'année en cours (2020, potentiellement incomplète), une pour l'année 2021 (complète) et une pour l'année 2022, potentiellement incomplète aussi si l'année 2020 a commencé après janvier puisque je n'ai que 39 colonnes (3x12 + les 3 totaux).

Comment faire alors pour que la formule comprennent où et quand elle doit changer d'année ?

 

[Amanite]

J'ai un message d'erreur en ouvrant votre seconde pièce jointe

 

[sylvanu]

Essayez celle ci.
Sinon envoyer un fichier avec juste un petit morceau, comme l'équivalent du jpeg du post #1, j'adapterais ma formule à votre fichier.
Addon : Sur cette pièce le calcul de l'année est automatique. C'est simple puisque "Annuel" suit simplement le mois de décembre.

 

[sylvanu]

Question peut être stupide, ce ne serait pas plus lisible de mettre tous les totaux annuels complètement à gauche de toutes vous colonnes ?
C'est faisable avec un Somme.Si.Ens.

 

[Amanite]

Je viens d'essayer votre formule écrite ainsi :

=SOMME.SI($A1:D1;">=1/1";$A2:D2)

Elle marche en 2020, mais en 2021 elle additionne 2021 ET 2020 (et pas seulement les mois de 2021)

Je suis désolé si je comprends pas votre solution ? Je n'arrive pas à ouvrir votre 2ème pièce jointe, donc je ne vois pas où vous mettez "1/1/ en dur" ?

 

[sylvanu]

">=1/1" signifie supérieur au 1er janvier de xxx