Discussion :

[PeaceMaker]

Bonjour,

je planche sur une macro en VBA qui me permettrait de cumuler des soldes de comptes selon des critères. L'idée était de cumuler les soldes d'un compte sur plusieurs périodes jusqu'à ce que l'on arrive à une période spécifique qui est la date de clotûre comptable, où là, certains comptes repartent à zéro et on recumule les périodes suivantes jusqu'à la prochaine clôture.

j'avais pensé à un SOMME.SI.ENS mais c'est pas possible dans ce cas.
trop compliqué.

Je penche de mon côté sur un code VBA mais pas très concluant.
j'avais pensé à définir des zones dans ma table, chacune de ces zones représentant un exercice comptable avec des soldes à cumuler (dans ce cas-ci, la fonction SOMME.SI.ENS est utilisable, zone par zone). Mais c'est compliqué et une fois passé la déclaration et le set des variables j'ai du mal à progresser.

je vous ai mis un fichier exemple, très basique pour que vous compreniez rapidement la logique de mon calcul et puissiez plancher sur un code. le fichier est TRES TRES CLAIR.

On a 2 tableaux avec des dates et des comptes.
Partons du principe que les 2 tableaux sont toujours les mêmes.
je veux simplement que dans le second tableau, j'obtienne un cumulé des soldes du premier tableau le résultat à obtenir est la colonne G.

La colonne rang exercice permet de faire ressortir le numéro d'exercice.

il me semble que la solution réside dans un code VBA qui crée une sélection qui part de la première ligne du tableau, l'étend en ajoutant la ligne suivante et fait une SOMME.SI.ENS avec cette sélection qui sert de plages de critères et, dès que le rang de l'exercice passe de 1 à 2 (ou que la condition VRAI/FAUX change) la sélection repart de zéro en partant de cette ligne et s'agrandissant de nouveau au fur et à mesure que l'on descend.

De cette facon, la SOMME.SI.ENS cumulera les soldes situés sur les lignes antérieures (qui représente des dates passées) et repartira de zéro en créant une nouvelle zone de sélection lorsque l'on change d'exercice comptable.

Petite difficulté supplémentaire : le cumul doit repartir à zéro à chaque exercice uniquement s'il s'agit d'un compte de CR. pour les comptes de Bilan, je veux simplement un cumulé des soldes sans repartir à zéro au début de chaque exercice comptable.

J'espère être clair.
Si vous avez des questions, hésitez pas.
Merci beaucoup de votre aide.

[mercatog]

Bonjour
Sans vba, formule peut être à améliorer

Pour la clarté, renomme tes plages de cellules comme ceci:
Dates: la plage des dates
Comptes: la plage des comptes
Solde: la plage des soldes
Cloture: la cellule comportant la date de cloture
J'ai supposé que la classe CR commence par un 6 dans son compte

Avec la formule suivante en D3 (que tu tire vers le bas ensuite)

Code :

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

=SI(ET(GAUCHE(B3;1)="6";A3>CLOTURE);SOMMEPROD((Comptes=B3)*(Dates>CLOTURE)*(Dates<=A3)*Solde);SOMMEPROD((Comptes=B3)*(Dates<=A3)*Solde))

[PeaceMaker]

Merci pour cette base Mercatog.

Dans mon fichier exemple, j'ai simplifier au maximun le problème pour que cela soit compréhensible très rapidement et que cela économise du temps aux personnes qui pourrait m'aider.

En revanche, mon fichier est plus compliqué et les datas sont surtout bien plus nombreuses, je peux avoir jusqu'à 5 dates de clôture, et je pense intégrer un "échelon" de plus en intégrant des sociétés (tu auras donc des sociétés, des dates (avec jusqu'à 5 clôtures) et des comptes).

Je ne suis pas sur qu'une fonction SI puisse me permettre de gérer autant de cas.

Mais je vais essayer de plancher sur ton code pour arriver à ce que je veux faire, quitte à utiliser des cellules intermédiaires pour reporter des résultats intermédiaires, d'une longue formule que je ferais en plusieurs étapes.

Merci de ton aide!

[mercatog]

Dans ce cas, il nous faut un fichier exemple fidèle à l'original pour pouvoir essayer de donner une réponse qui répondrait au mieux.

[PeaceMaker]

J'ai pensé à ajouter une colonne qui ferait ressortir sur chaque ligne la date de clôture à retenir (colonne L), mais du coup je n'arrive pas à modifier la formule en conséquence pour que ca colle.

si tu as une idée
je t'ai joint le fichier exemple modifié

[PeaceMaker]

je vais essayer de me débrouiller avec ça et si je treouve pas, je re-posterai avec cette fois-ci un fichier plus complet et au plus près de la réalité.

Merci!

[mercatog]

La formule que tu as utilisée est bonne, mais tu as comparé avec la colonne G qui prends toujours 31/03/2009 comme étant la date de clôture.

Refais le test

[PeaceMaker]

Je ne vois vraiment pas...

Je t'ai refait mon fichier excel, avec un nouvel onglet (exemple 2) et plus de data.

Il y a :
2 sociétés
3 dates de clôture + 1 mois en cours (qui doit être considéré comme un nouvel exercice en cours)
4 comptes

Il faut simplement un moyen de gérer cela pour un nombre beaucoup plus important de sociétés (jusqu'à disons une dizaine), de clôtures (jusqu'à 4 exercices + 1 exercice en cours) et un nombre bcp plus grand de comptes (sans limite ici quasiment )

Je t'ai rajouté 2 colonnes qui te permettent de voir le rang de l'exercice et la date de clôture à retenir pour cette ligne. je ne sais pas si ca peut t'aider. je pensais que ca m'aiderait mais finalement je galère toujours autant.

Merci de ton aide!

[mercatog]

J'ai supposé que les dates de clôtures sont les fins des trimestres. (on peux adapter)
A partir du tableau initial (colonnes A), j'aurai besoin d'une colonne supplémentaires, disons N

En N8:

Code :

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

=SI(K8="CR";ENT((MOIS(B8)+2)/3);0)

Qui donne le n° de trimestre pour les classes CR et 0 pour les autres

Je renomme toutes les plages comme décrits précédemment
Et en P8:

Code :

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

=SOMMEPROD((Société=A8)*(Comptes=C8)*(ANNEE(Dates)=ANNEE(B8)*(Dates<=B8))*(TRM=N8)*Solde)

Avec TRM nom donnée à la colonne N des trimestres.

[PeaceMaker]

zut j'ai oublié de te précise ca.
oui en fait dans le fichier il s'agit de trimestre parce que j'ai voulu limiter les datas mais dans la réalité il s'agit d'année (pas forcément civile). Tous les 12 mois, qui est la durée légale d'un exercice comptable en fait.

Je regarde ceci en fin d'aprèm.
Merci de ton coup de main

[mercatog]

ça reste la même logique, le but est de créer une formule de regroupement pour la Classe CR et un groupement unique pour la classe Bilan (pour mon exemple précédent(1, 2, 3 et 4 pour la classe CR et 0 pour la classe Bilan)

Si les dates de clôtures sont aléatoires, ça restera un peu tordu

[PeaceMaker]

J'ai finalement réussi à gérer le problème des dates de clôtures en fin d'année et même aléatoires. Ca cadre parfaitement, mais je n'ai pour l'instant testé qu'avec une seule société. Je pense pas qu'il devrait y avoir de problème mais dans le doute je laisse le topic ouvert.

Je vais tester la résistance du modèle dans tous les sens et clore le topic d'ici après-demain, une fois que je serais sur que c'est tout bon.

merci de ton aide Mercatog!

[mercatog]

Bonsoir
Et pour profiter à d'autres éventuels forumistes, il serait judicieux d'expliciter le résultat final que tu as trouvé.

[PeaceMaker]

J'ai testé avec de nouvelles datas sur un groupe (avec 2 sociétés et 3 dates de clôture + 1 exercice en cours) et cela fonctionne merveilleusement bien.

Je rejoins le fichier exemple, avec en colonne sourlignée jaune la bonne formule à utiliser. Il n'y a qu'à remplir dans des cellules spécifiques les dates de clôture que l'on retient et la formule met les résultats à jour.

[mercatog]

le cumul doit repartir à zéro à chaque exercice uniquement s'il s'agit d'un compte de CR

Et si tu as plus de 3 dates de clôture?
En plus, tu as écris en dur 31/12/2009 en colonne M.

Peut être il faudra réfléchir sur ceci

[PeaceMaker]

En fait nan pour l'histoire du décompte qui repart à zéro en début de chaque exercice, cela se fait également pour les comptes de bilan

(j'avais oublié que sur mes datas de départ, des écritures de reprise des soldes de bilan des exercices précédents était généré - appelé les A-nouveaux - et, de ce fait, je les aurais compté en double à chaque cumul).

Pour ce qui est de la colonne M, on n'en a pas besoin en fait. La colonne N suffit pour obtenir le rang de l'exercice. S'il y a plus d'exercices suffit de recalibrer la formule en colonne N pour qu'elle prenne en compte plus de dates de clôture.

Pour ma part, je n'en aurais que 5 maximum dans tous les cas, donc je n'ai pas réfléchi à un moyen d'automatiser davantage la formule en colonne N pour qu'elle prenne en compte une possibilité infinie de date de clôture.