XL 2021 Somme Mois par années

[Phoenix23]

Bonjour à tous.
Dans le fichier ci-joint, je souhaiterai faire la somme de mois pour une année donnée.
Exemple dans la feuille tableau.
Merci d'avance.
Cordialement

 

[JHA]

Bonjour à tous,

Pas sur d'avoir compris la demande.

A essayer avec l'année à chercher en "F1"

=SOMME(DECALER($A$1;1;EQUIV($F$1;$B$1:$D$1;0);12))

JHA

 

[Phoenix23]

Bonjour JHA
Merci de votre intervention
Dans la solution proposée cela revient si je ne me trompe pas à faire la somme de la colonne B du tableau.
En fait si cela est possible ce que je recherche dan ma feuille "Tableau" c'est par rapport à la feuille "Changes" la colonne "A" faire la somme des cellules ayant pour critères : Novembre 2024; Décembre 2024; Mai 2025.
Si toutefois cela est possible bien entendu.
Cordialement.

 

[JHA]

Bonjour à tous,

Pour avoir les résultat du tableau
En "B2"

=SOMMEPROD((MOIS(Changes!$A$2:$A$5)=MOIS(1&Tableau!$A2))*(ANNEE(Changes!$A$2:$A$5)=Tableau!B$1))

Copier vers le bas et la droite

JHA

 

[Phoenix23]

Merci de votre aide précieuse JHA
C'est ce que je recherchais.
Bonne journée.
Cordialement.

 

[Phoenix23]

Bonjour JHA ,
je reviens vers vous pour un conseil.
Toutes les explications se trouve dans la feuille essai du fichier ci-joint.
Merci d'avance

 

[TooFatBoy]

Hi,

je reviens vers vous pour un conseil.

Conseil : mettre la question et ses explications au moins dans le message sur le forum.

 

[JHA]

Bonjour à tous,

Par formule cela devient "galère" et je pense qu'une version VBA serait plus pratique, malheureusement je ne sais pas faire.
Je te joins une formule à rallonge pour remplir le tableau de droite de l'onglet "Essai".

=SIERREUR(SI(MOD(COLONNE();2)=0;INDEX(DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A36");;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B36:$DZ36");0)+1;;DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");-1;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)));EQUIV(RECHERCHE("zzzz";$L$2:L$2);DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)+1;;DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");-1;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)));0));INDEX(DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A36");;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B36:$DZ36");0)+1;;DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");-1;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)));EQUIV(RECHERCHE("zzzz";$L$2:L$2);DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)+1;;DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");-1;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)));0)+1));"-")

JHA

 

[gosselien]

Bonjour,
Pour moi la méthode de présentation n'est pas la plus facile pour une compilation future...
Pourquoi ne pas avoir écrit toutes les données dans un seul classeur et travailler avec filtre ou tcd ?

 

[Phoenix23]

Hi,


Conseil : mettre la question et ses explications au moins dans le message sur le forum.

Bonjour TooFaBoy.
Désolé cela ne se reproduira plus.

 

[Phoenix23]

Bonjour,
Pour moi la méthode de présentation n'est pas la plus facile pour une compilation future...
Pourquoi ne pas avoir écrit toutes les données dans un seul classeur et travailler avec filtre ou tcd ?

Bonjour gosselien.
Merci de votre réponse.
Je me rends bien compte que la présentation n'est pas des plus pratique, pour ma défense, je suis amateur et ne connais rien aux filtres ou autres TCD.

 

[Phoenix23]

Bonjour à tous,

Par formule cela devient "galère" et je pense qu'une version VBA serait plus pratique, malheureusement je ne sais pas faire.
Je te joins une formule à rallonge pour remplir le tableau de droite de l'onglet "Essai".

=SIERREUR(SI(MOD(COLONNE();2)=0;INDEX(DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A36");;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B36:$DZ36");0)+1;;DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");-1;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)));EQUIV(RECHERCHE("zzzz";$L$2:L$2);DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)+1;;DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");-1;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)));0));INDEX(DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A36");;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B36:$DZ36");0)+1;;DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");-1;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)));EQUIV(RECHERCHE("zzzz";$L$2:L$2);DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)+1;;DECALER(INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!A3");-1;EQUIV($K4;INDIRECT(RECHERCHE(9^9;$L$1:L$1)&"!$B3:$DZ3");0)));0)+1));"-")

JHA

Bonjour JHA;
Ah oui.....
Je suis impressionné.
Effectivement, là ca devient compliqué, trop pour moi !
J'ai bien conscience que ma présentation actuelle n'est pas de la plus esthétique et pratique, mais pour le coup je vais humblement rester à mon niveau (pas élevé) et me cantonner à ce que je que je sais faire, "jouer à la balle".
Toujours est il que je vous remercie de la solution proposée.
Bon Dimanche.

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @Phoenix23 et @JHA
Bon tout le boulot est fait ou presque, mais je me suis penché sur les deux questions en utilisant les fonctions matricielles dynamiques d'Excel2021.

Pour la première question, fichier "Somme si Mois, Années AtTheOne.xlsx"

• les données dans la feuille "Changes" sous forme de tableau structuré nommé "tb_Dates"
• une seule formule dans la feuille "Tableau" en $B$2 :
  

  =NB.SI.ENS(tb_Dates;">="&MOIS.DECALER($A$2:$A$13&" " &B$1:D$1;0);tb_Dates;"<="&FIN.MOIS($A$2:$A$13&" " &B$1:D$1;0))

  elle s'étend automatiquement

Pour la deuxième question, fichier "ModeTransport AtTheOne.xlsx"

• une feuille "Tables" avec les mois, les années, les noms, les moyens de transport sous forme de tableaux structurés ( "Mois", "Années", "Noms", "Transport")
  
  
• Une feuille "BdD" avec les données pour chaque jour, chaque nom et chaque moyen de transport.
  Elle s'adapte en cas d'évolution des tables ci-dessus et contient 3 formules
  • formule pour les noms :

    =INDEX(Noms;ENT((SEQUENCE(1;NbCol)-1)/NBVAL(Transport))+1)

  • formule pour les moyens de transport :

    =INDEX(Transport;MOD(SEQUENCE(1;COLONNES(B1#))-1;NBVAL(Transport))+1)

  • formule pour les dates :

    =SEQUENCE(DATE(MAX(Années);12;31)-DATE(MIN(Années);1;1)+1;1;DATE(MIN(Années);1;1))

• Une feuille "Tableau" récapitulative avec 5 formules qui s'étendent automatiquement
  • formule pour les entêtes des années

    =INDEX(Années;ENT(SEQUENCE(1;LIGNES(Années)*NbCol;0)/NbCol)+1)

  • formule pour les entêtes des noms

    =SIERREUR(INDEX(Noms;ENT(MOD(SEQUENCE(1;COLONNES($B$3#);0);NbCol)/2)+1);"Tous")

  • formule pour les entêtes des mode de transport

    =INDEX(ModeTransport;MOD(SEQUENCE(1;COLONNES($B$3#);0);2)+1)

  • formule pour les entêtes des mois

    =Mois

  • formule pour ramener les données de BdD

    =NB.SI.ENS(ListeDates#;">="&MOIS.DECALER($A$6:$A$17&" "&$B$3#;0);ListeDates#;"<="&FIN.MOIS($A$6:$A$17&" "&$B$3#;0);DECALER(ListeDates#;0;(EQUIV($B$4#;Noms;0)-1)*2+EQUIV($B$5#;Transport;0));">0")

• 3 noms définis pour simplifier les formules
  • ModeTransport fait référence au TS "Transport" (nécessaire pour les MFC)
  • "ListeDates" fait référence à la cellule BdD!$A$3 (formule des dates)
  • "Nbcol" contient la formule =NBVAL(Noms)*NBVAL(ModeTransport) qui détermine le nombre de colonnes dans la feuille "Tableau".

Toutes ces formules s'étendent automatiquement en fonction du contenu des tables, par contre si tu venais à modifier ces tables les données de la feuille BdD déjà saisie devraient être reprise.
Par exemple si tu ajoutes le mode de transport "Bus" aulieu d'un pas de 2 on a un pas de 3, les entêtes s'adaptent automatiquement mais pas les données ... Sans macro il n'y a pas de miracle !

Regarde ce que cela donne dans les pièces jointes et dis moi ce que tu en penses.

EDIT : Homogénisation des formules

 

[Phoenix23]

Bonjour AtTheOne.
Merci pour la solution apportée.
De loin votre présentation est bcp plus claire que la mienne.
Je me permets une petite question, dans votre tableau BdD, suis je obligé de conserver les dates qui dans les colonnes de B à I non aucune valeurs :
Exemple du 1 janvier 2016 au 17 Février 2016, car en fait se sont des lignes qui ne me servent à rien puisqu'elles sont vides.
Si cela n'est pas possible ce n'est pas grave votre proposition est plus limpide que ce que j'avais créé.
Merci.
Bonne journée.

​

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @Phoenix23,
Je suis sur mon téléphone, je regarderai ce soir, car c'est une formule qui génère les dates par année entière, et le tableau utilise cette formule pour déterminer la plage à prendre en compte.
À ce soir