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).

 

[sylvanu]

Ci joint un nouvel essai;
feuill1 comme avant
feuil2 avec les calculs à la fin des colonnes;

Essayez d'envoyez un morceau de fichier représentatif, ce sera plus simple.

 

[Amanite]

Sinon envoyer un fichier avec juste un petit morceau, comme l'équivalent du jpeg du post #1, j'adapterais ma formule à votre fichier.

Voilà le fîchier. Ce serait génial si vous arriviez à me mettre la bonne formule sous "Année" pour les 3 années.

EDIT : je précise que seule la 1ère partie (INCOME) du tableau est concernée

 

[Amanite]

ce ne serait pas plus lisible de mettre tous les totaux annuels complètement à gauche de toutes vous colonnes ?

Non, car votre proposition part de l'idée qu'on commence en 2020. Or, mon tableau doit être très flexible et permettre de commencer en 2021, voire plus tard

 

[sylvanu]

c'est un exemple;
Si en AJ1 de mon fichier vous mettez annee(D1) de votre fichier, c'est automatique.

 

[Amanite]

Essayez celle ci.

Oui, cette formule semble fonctionner :

=SOMME.SI($E2:U2;">=1/1/"&ANNEE(U2);$E6:U6)

 

[sylvanu]

Voilà, c'est fait.
Avec en I5 :

=SOMME.SI($E$2:H$2;">=1/1/"&ANNEE(H$2);$E6:H6)

il suffit de recopier la formule sur les lignes puis les colonnes sans rien modifier.
Le calcul de l'année est automatique.

 

[sylvanu]

=SOMME.SI($E2:U2;">=1/1/"&ANNEE(U2);$E6:U6)

ne marchera pas si vous tirez la formule en X et en Y, il manque des $

=SOMME.SI($E$2:U$2;">=1/1/"&ANNEE(U$2);$E6:U6)

Les 2 $ manquants permettent de tirer la formules sans changer les cellules.
Par ex les dates $E2:U2 deviendront $E2:U3 si vous tirez vers le bas.
Avec $E2:U$2 le N) de ligne est figé.

 

[Amanite]

J'ai intégré la formule ci-dessus et c'est bien ça, ça marche !!!

Merci mille fois Sylvanu.

 

[sylvanu]

Attention aux $ ! voir post #23.

 

[Amanite]

Les 2 $ manquants permettent de tirer la formules sans changer les cellules.
Par ex les dates $E2:U2 deviendront $E2:U3 si vous tirez vers le bas.
Avec $E2:U$2 le N) de ligne est figé.

C'est génial. Merci beaucoup !

 

[sylvanu]

Juste pour finir, ... et parce que je n'aime pas avoir tort.
Une récap en fin de tableau avec année initiale automatique basée sur votre cellule Start.
Sur ce, bonne après midi, et peut être à Bientôt.

 

[Amanite]

Bonjour.

Merci encore Sylvanu pour votre efficace et rapide conseil.

J'ai toutefois encore une (petite ?) question sur le même sujet : comment faire pour additionner, non plus les montants des différents mois de l'année, mais le nombre de mois écoulés sur la même plage ? C'est surtout pour la dernière année, dont on ne connaît pas à l'avance le nombre de mois puisqu'elle est tronquée. J'aurais besoin de connaître cette valeur afin de faire une extrapolation du chiffre d'affaires sur 12 mois (par exemple si on a CHF 10'000 de chiffre d'affaires en 5 mois, cela ferait une extrapolation de 10'000 / 5 * 12 = 24'000 par an. Sauf que je ne sais pas comment extraire cette valeur de 5 mois (dans mon exemple).

Merci d'avance

 

[sylvanu]

Bonsoir,
Avec une simple formule :

=NB.SI.ENS($E$2:$AL$2;">=1/1/"&AO2;$E$2:$AL$2;"<1/1/"&AP2)

 

[Amanite]

Merci encore Sylvanu, ça marche, comme d'hab