Bonjour à tous,

J'ai un classeur avec lequel je suis mes entrées et dépenses mensuelles actuelle et prévisionnelles sur 2 ans.

A chaque ligne correspondant à une fin de mois (ex. ligne 129) j'ai en C129 Solde au:, en D129 30/03/2013 et en K129, L129, M129, ... Z129 j'ai entré les formules suivantes =SOUS.TOTAL(9;K$93:K128) etc., K$93 étant la ligne en dessous du précédent sous total. En fin du classeur soit dans la ligne 609 (en dessous du dernier sous total mensuel) la formule =SOUS.TOTAL(9;K$5:K608) me donne le total de tous les sous totaux pour chaque colonne. J'ai créée des macros pour insérer une nouvelle ligne pour chaque nouvelle dépense et tout fonctionne parfaitement bien. Mon problème est que si je dois effectuer un tri après le changement d'une date dans la colonne D et que la ligne concernée change de mois, toutes les références des sous totaux deviennent fausses. Y at-il une solution pour y remédier ?

Merci d'avance, bonne soirée.

Bonsoir,

Tu parles d'un tableau de 609 lignes. Et ensuite on voit apparaître des fiches et un tri de fiches. Deux sortes de choses entre lesquelles je ne vois aucun rapport établi, outre que les définitions sont manquantes (je ne connais pas d'objet fiche dans Excel). En l'état je ne comprends pas le problème.

A+

Bonsoir Ferrand et merci pour ta réponse. Excuse moi un vieux réflexe Works.wdb où une ligne était appelée " fiche". Donc à la place de fiches il faut lire lignes. Inserer des lignes ou trier des lignes d'après les dates de 1° D, 2° A et 3° B. Je joint une copie d'écran pour que ce soit plus clair, je ne joint pas le classeur car c'est plutôt personnel pour l'étaler sur un forum.

Exemple : si je modifie la date 1/03/2013 de D104, D105, D106, D107 par 1/04/2013, après avoir trié les colonnes D, A, B de la feuille, les lignes vont être déplacées en D130, D131, D132, D134 et le sous total va se trouver en $K125. Le sous total du mois suivant n'ayant pas changé reste =SOUS.TOTAL(9;K$130:K150) au lieu de démarrer à $K126, et tout va se repercuter jusqu'à la dernière ligne. En fait je peut inserer ou supprimer des lignes mais pas les trier si elles doivent être déplacées de periodes mensuelles.

J'espère avoir été plus clair dans ma requête.

http://cjoint.com/?CCAbYFXQcFB Avant le changement de date et le tri

http://cjoint.com/?CCAcyMjWlY9 Après le changement

A+

Bonjour,

Une idée à essayer : tu encadres chaque ligne sous.total par 2 lignes qui serviront de "balises" (et également 1 au début de ton tableau.

Tu fais totaliser tes formules entre 2 balises. Pour stabiliser ces balises, tu dates ta ligne sous.total, par exemple 31/03 22:00, la balise qui précède : 31/03 21:00, celle qui suit : 31/03 23:00. Celle qui débute le tableau, du 31/12/année préc 23:00. Les lignes opérations comportant une date sans heure (soit 00:00), les balises et sous.totaux resteront ordonnées et contiguës lors des tris.

Les formules de sous.totaux doivent alors couvrir une plage comprises entre 2 balises entre lesquelles se trouvent toutes les opérations du mois totalisé (en références absolues pour les lignes).

Normalement, lorsque des lignes basculeront d'un mois sur l'autre lors de tri, la formule restera cadrée sur ses 2 balises...

Tiens-moi au courant si ça fonctionne ! (Sinon il faudra se rabattre sur l'utilisation de SOMMEPROD, mais logiquement ça devrait fonctionner [pas le temps de bâtir une feuille pour tester]).

Merci pour tes conseil toujours aussi professionnel. Je te tiens au courant. Tu m'as déjà bien dépanné pour des problèmes de modification des axes d'un graphique inclu dans une macro, et j'ai pris beaucoup de plaisir à analyser tes formules pour les comprendre dans "Concours de pétanque" récement.

Bonnes journée et A+

Et ta santé, ça boum ?

Bonsoir,

Je n'ai pas réussi à faire fonctionner tes suggestions. Je te joint un fichier expurgé de mon problème.

Merci d'étudier une possibilité de le résoudre.

http://cjoint.com/?CCArEUKR2H3

Eh bien moi non plus ! Les déplacements lors d'un tri n'ont pas les mêmes effets que l'insertion ou la suppression de lignes...

Faudra donc faire autrement.

Je n'ai pas bien vu comment tu opérais ton tri (avec 5 lignes d'entête) (et pas de macro).

Et j'ai eu quelques alertes de références circulaires en ouvrant ton fichier.

Alors au niveau du tri il y a un mystère que je n'ai pas réussi à solutionner. Le principe je selecte avec la souris la ligne 5 dans les en-tête de ligne et j'étend le selection vers le bas (Ctrl+Maj+Bas), et je lance le tri personalisé :

1° Colonne D, Valeurs, Du plus ancien au plus recent

2° Colonne A, Valeurs, De A à Z

3° Colonne B, Valeurs, Du plus ancien au plus récent, et OK.

Dans tous les classeurs précédents (2009, 2010, 2011, 2012) la selection était comme mémorisée à partir de la ligne 5 jusqu'à la dernière ligne en lançant le tri personnalisé et là ça ne le fait plus pour ce classeur, c'est depuis la ligne 1 que se fait la sélection. Même si tous les classeurs sont ouverts dans la même session Excel le résultat est le même. Je n'ai jamais compris pourquoi.

C'est bien ce que j'ai vu, ça m'a piégé la première fois. Le fait d'avoir 4 lignes d'en-tête ne permet pas aux automatismes d'Excel de jouer. Il me semble que pour stabiliser et éviter d'avoir à sélectionner tu auras intérêt à penser à bâtir une macro de tri. Un tri d'une plage nommée dynamique et tu n'as plus rien à modifier.

En ce qui concerne les références circulaires apparues, c'est un effet de chevauchement dû à un tri qui a fait qu'une formule incluait la cellule où elle était dans le calcul. Cela va donc disparaître.

Pour ton problème, j'avais d'abord pensé à SOMMEPROD, mais le calcul dans la colonne même allongeait les formules et il aurait fallu diviser le total général par 2 ou adopter une autre façon... J'ai donc trouvé un moyen de conserver la fonction SOUS.TOTAL qui semble la mieux adaptée.

Voilà la formule de sous-total mensuel, unique, qui se recopie sur tous les sous-totaux mensuels pour les 12 mois :

=SOUS.TOTAL(9;DECALER(INDIRECT(INDEX(Plage;MOIS($D41)));;COLONNE()-1))

Plage est une plage nommée qui devra se trouver sur une autre feuille, elle comprend une colonne et 12 lignes où se calculent pour chaque mois une référence de plage mensuelle en col. A. La détermination des lignes du mois est faite avec EQUIV en déterminant les lignes où se trouvent Z en col.A (lignes de sous-totaux) [en partant de la fin de l'en-tête ligne 4]. Cela s'ajustera donc aux modifications liées aux ajouts de lignes et aux déplacements dus aux tris, qui modifieront la position des Z.

Dans la formule ci-dessus qui se trouve en I41, MOIS($D41) va renvoyer 1 (il y a la date du 31/01 en D41), la fonction INDEX va renvoyer le contenu de la première ligne de Plage qui sera : A5:A40 (le mois de janvier commence juste après la ligne d'en-tête (4) et se termine juste avant la ligne sous-total (Z en A41)), référence qui peut être utilisée avec INDIRECT dans une fonction DECALER, pour la décaler du nombre de colonnes nécessaires qui sera toujours égal au numéro de la colonne de calcul du sous-total -1.

Détail sur les autres formules (composition des références) dans le classeur : http://cjoint.com/?CCBbosGpyft

C'est absolument génial! Exactement ce qu'il me fallait, et bien expliqué et bien détaillé ! Me reste plus qu'à décortiquer les fonctions de chaque formules pour bien en comrendre le fonctionnemt. Qui sait après je pourrai peut-etre réaliser de telles prouesses tout seul!

Merci encore Ferrand et A+

J'ai crié victoire un peu trop vite, c'était sans compter sur mon incompétance!

J'ai voulu apporter tes modifications dans mon classeur d'origine BNP2013.xlsm. J'ai donc reconstité la Feuil2 en entrant les chiffres des Mois 1 à 12, le 4 en B et copié les formules B 3 à B14, puis C3 à C14 et j'obtiens les bons calculs se rapportant à ma Feuil1.

J'ai ensuite copié la formule de I41 dans K54 (j'ai 2 colonnes de plus que j'avais supprimmées dans le fichier démo) qui est la première ligne de sous-totalde la fin de mois Z de Janvier. Sous-total correct. Je l'ai recopiée à la ligne 93 du sous-total suivant. Dans la formule (identique à la tienne) j'ai bien ...;MOIS($D93)))... mais l'addition ne se fait que pour K55 à K68 qui correspond à la Feuil2 de TON CLASSEUR et non pas à la Feuil2 de MON CLASSEUR qui est A55:A92. Je n'arrive pas à trouver la solution même en renommant "Plage" de C1 par ex. "Zone", j'ai à ce moment là #NOM?.

S.O.S !

Dans le tableau que tu as repris (c'est lui qui est déterminant), les indications de mois sont purement informatives (pour qu'on puisse lire sans se demander à chaque fois qu'on veut vérifier quelque chose). Dans la colonne à côté (B si tu as mis les mois en A), à la ligne qui précède des 12 mois tu mets le numéro de la dernière ligne de l'en-tête (4 si ton classeur actif correspond au modèle, ce nombre ne sert qu'à indiquer que le premier mois commence ligne suivante, donc 5 dans ce cas).

A la ligne suivante, correspondant à janvier, tu construis la formule qui va chercher les "Z" qui indiqueront la ligne où se trouve un sous-total mensuel. On va chercher avec EQUIV, fonction qui renvoie le rang d'une valeur dans une liste, soit dans une plage le rang de la ligne à partir du début de la plage. La syntaxe est : EQUIV(valeurcherchée;plagederecherche;type[valeurexacte ou approchée]).

On cherche "Z", pas de problème à ce sujet ; on veut une correspondance exacte, donc le 3e argument sera : 0. Reste le 2e argument, l'indication de la plage. La recherche se fait dans la colonne A, elle débute à la première ligne du mois, donc 5 (mais on l'indiquera à partir de la cellule du dessus en ajoutant 1, pour que la formule puisse être étendue pour le calcul des 11 autres "Z"), on prend une extension suffisante pour être sûr que ton tableau reste compris dedans (j'ai mis A9000 ! la formule sera donc valide tant que ton tableau ne dépasse pas 9000 lignes !!). Comme on construit l'indication de la plage, cela impose l'utilisation de la fonction INDIRECT. Tu indiques la plage comme tu l'écris : "NomdelaFeuille!A" un & pour concaténer le numéro de ligne de début : B2+1 [si on est en B3, ce qui donnera 5], un autre &, et la fin : ":A9000".

EQUIV va donner le rang de "Z" dans cette plage à partir de la ligne 5, pour rétablir le numéro de ligne dans la feuille il faut donc rajouter le nombre de lignes qui précède la plage (soit: B2 = 4,au départ, et pour les autres mois ce sera le résultat précédent trouvé se trouvant à la ligne précédent). On peut tirer cette formule pour les 12 mois, elle donnera les 12 résultats cherchés.

Il est facile de vérifier dans ton tableau que ces résultats correspondent bien tous aux "Z" (= lignes de sous-totaux), ça garantit en principe que ta formule de sous-total sera bonne.

La colonne à côté (C donc) est simple, tu construit une chaîne constituant la référence de plage mensuelle sur une colonne en te servant des lignes trouvées dans la colonne à côté (B) : si tu es en C3, ta plage commencera à la ligne qui suit la valeur de B2 (B2+1) et se terminera à la ligne qui précède celle trouvée en B3 (B3-1). Même chose pour les 12 mois, on peut étendre la formule bâtie en C3. La plage de 12 lignes (sur 1 colonne) contenant les références des 12 plage mensuelles (Axx:Ayy), doit être nommée pour faciliter son utilisation.

Si ceci est bon, et c'est facilement vérifiable, la formule de SOUS.TOTAL donnera le bon résultat (étant entendu que la date en colonne D est bien incluse dans le mois, puisque c'est à partir du mois de cette date qu'on va chercher la plage à additionner dans la colonne qu'on vient de construire et nommer).

Tu me dis (si je suis bien) que le "Z" de janvier se trouve en A54 et celui de février en A93. Dans ce cas, sur le tableau qu'on vient de détailler tu dois avoir 54 en B3 et 93 en B4, et corrélativement: C3 affiche A5:A53 et C4 : A55:A92.

Si ce n'est pas le cas, c'est au niveau des formules qu'on vient d'examiner qu'il faut regarder.

Merci Ferrand pour tes explication mais n'arrivant pas à solutionner le problème je t'ai envoyer par message perso mon classeur complet et confidentiel avec les explications des disfontionnements. Après avoir inclu le lien pour ouvrir mon classeur, j'ai voulu vérifier s'il fonctionnait bien. J'ai donc cliqué dessus, l'ai importé et à l'ouverture j'ai eu le message:

"Ce classeur contient une ou plusieurs liaisons qui ne peuvent pas être mises à jour". J'ai donc cliqué sur "Modifier les liaisons" et les ai faites correspondre avec MON classeur et maintenant tout est conforme. En fait les liaisons se faisaient toujours avec la Feuil2 de ton classeur modifié.

Merci encore pour tous tes effort et tous tes conseils

tu as trouvé la solution avant que je réponde ! Ça m'a l'air d'être un effet pervers du transit par l'hébergeur.

A+

Nouveau soucis!

Au premier post de ma demande j'indiquais que j'avais un fichier banque prévisionnel sur 2 ans. C'est là qu'est le hic ! En fin Janvier 2014 (31/01/2014) le sous-total me donne à nouveau le résultat de Janvier 2013 (31/01/2013) et ainsi de suite pour les sous-totaux suivants. J'ai bien essayé dans la Feuil2 de prolonger de 1 à 24 dans la colonne A et de recopier les formules jusqu'à la ligne 26, je pense que cela vient de ;MOIS(... dans la formule.

C'est bien ça !

Faut trouver un autre moyen pour produire 1 ou 13, 2 ou 14, ... selon l'année.

Je regarde si j'ai une idée

Remplace MOIS($D54) par NB.SI($A$1:$A54;"Z") dans la première formule. Elle sera recopiable sur les 24 mois.

N'oublie pas de prolonger le tableau de Feuil2 jusqu'à 24 et d'ajuster le nom Plage à 24 lignes au lieu de 12.

=SOUS.TOTAL(9;DECALER(INDIRECT(INDEX(Plage;NB.SI($A$1:$A54;"Z")));;COLONNE()-1))

J' bien collé ta formule en K54 puis l'ai recopiée dans toutes les lignes sous-total, mais à partir de la ligne 361 de fin de mois de 31/01/2014 j'ai le message #REF!

J'ai bien mis à jour la Feuil2, copie d'écran ci-joint

http://cjoint.com/?CCCrUIUu65N

Je viens de voir ton message, j'ai donc ouvert Formules, Gestionnaire de noms, Plage, Modifier et j' ai modifié la référence jusqu'à ...:$C$26, et ... ça marche !!!