Comment réaliser un tel rapprochement bancaire?

zombe

XLDnaute Occasionnel
Salut le forum
J'ai un fichier volumineux(plus de 15000 lignes) avec lequel je dois réaliser un rapprochement bancaire.
Je le fais manuellement mais c'est très fastidieux voila pourquoi je souhaite que vous m'aider à l'automatiser.
Il diffère légèrement des rapprochements courants (solder 2 montant identique qui se trouvent au débit et au crédit).
Dans mon cas, les montants au crédit constituent des cumuls et ceux au débit des détails.
Autrement dit, la somme d'un certain nombre de montant au débit à son cumul au crédit.
Comment réaliser un tel rapprochement?
J'ai essayé de trouver un critère pour faciliter mon aide.
Voici ce que j'ai trouvé:
Ma proposition consiste à sommer les montants du débit dans un ordre bien précis en faisant une comparaison simultanée avec les montants du crédit (commencer par le 1er montant en descendant).
le 1er résultat de la sommation qui correspondrait à un montant au crédit sera rattaché et on continue avec le reste des montant du débit.
Ex tiré de mon fichier:
la somme des 3 1er montant donne 980000. au crédit, on trouve 980000;donc les montants du débit déjà additionnés se soldent avec celui du crédit.on recommence la sommation avec le montant suivant 27705 en respectant la logique tiré du 1er résultat et ainsi de suite.
NB: - dans le fichier joint, j'ai essayé d'utiliser des couleurs pour me faire comprendre.
- au cas ou quelqu'un aurait une meilleure solution, suis preneur.
Merci pour tout
 

Pièces jointes

  • Autre Rapprochement.xlsx
    33.4 KB · Affichages: 530

R@chid

XLDnaute Barbatruc
Re : Comment réaliser un tel rapprochement bancaire?

ReBonsoir,
J'ai fait la première réponse @ base des couleurs des plages, mais tu dois colorer les plages manuellement, ce qui n'est pas bon...
tu peux faire mieux,

en H5,
Code:
=SI(D5=SOMME(D4;1);MAX(H$4:H4);MAX(H$4:H4)+1)
@ tirer vers le bas jusqu’à H414

en G415,
Code:
=SOMMEPROD((H$5:H$414=MOD(LIGNE(A6)-1;6)+1)*ABS(F$5:F$414))
@ tirer vers le bas jusqu’à G420

Voir PJ


Amicalement
 

Pièces jointes

  • Autre Rapprochement.xls
    136 KB · Affichages: 250

Dormeur74

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

Bonjour les courageux,

Je propose une macro qui ne tient pas compte des couleurs. Voici ce qu'elle fait :
- elle compte le nombre de libellés différents (il faut assurer en amont l'homogénéité des libellés).
- elle calcule la somme cumulée par libellé (je n'ai pas vu d'autre logique)
- elle reporte dans les cellules du bas les totaux cumulés.

Ce que je n'ai pas fait et que je ferai si tu en as vraiment besoin, c'est d'afficher dans la colonne des Crédits (G) les sommes cumulées ligne par ligne.

Contrainte : quelque soit le nombre de lignes dans le tableau, il est impératif que les lignes du bas soient toujours les mêmes.
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re : Comment réaliser un tel rapprochement bancaire?

Bonjour à tous,

Un essai par formule en H5 à copier vers le bas + une MFC qui cache les cellules numériques (police blanche sur fond blanc). Le sous-total en colonne H apparait sur la dernière ligne d'un bloc.
Code:
=SI(ESTNUM(H4); SI(ESTNUM(EQUIV(-SOMME(H4;F5);$G$415:$G$420;0));"--->  " & TEXTE(INDEX($G$415:$G$420;EQUIV(-SOMME(H4;F5);$G$415:$G$420;0));"# ##0");SOMME(H4;F5));F5)
 

Pièces jointes

  • Autre Rapprochement V1.xls
    92 KB · Affichages: 209
Dernière édition:

zombe

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

Salut à vous tous
D'abord je tiens à vous remercier pour vos multiples contributions.
R@chid: basée sur les couleurs sera dure à gerer.
Je n'ai pas utilisé les couleurs pour quelles soient un critère mais pour vous permettre de me comprendre facilement.
Mapomme:
ta solution tendait vers mes attentes mais par la suite j'ai constaté qu'il y'a certainement un risque.
J'ai pas pu bien tester mon doute :supposons que dans la colonne credit il y'ait 120000.
Que se passerait-il étant donné qu'il y'a 120000 au debit de la colonne debit?
Peut être que j'ai pas bien compris ta formule (merci de me l'expliquer si possible).
Dormeur74:
Suis d'accord avec ton critère. nous allons travailler pour harmoniser les libellés gage d'un bon fonctionnement de ta macro.
Je souhaite si possible qu'on maintienne la colonne crédit intact quite à faire les retraitements dans la colonne H.si vous me permettez, je vais utiliser un fichier (voir joint) pour illustrer mes attentes:
- je souhaite que les détails des montants au débit qui se solderaient avec le montant au crédit soient rattachés(lettrés) (voir exemple du fichier joint).
- l'objectif étant de supprimer à terme les montants qui se soldent pour ne laisser que ceux qui ne se soldent pas, il serait intéressant de prévoir un code pour la suppression.
Merci de revoir pour moi. je reste à votre disposition pour des informations complémentaires.
 

Pièces jointes

  • RAPPROCHEMENT AOUT 2012.xlsm
    87.2 KB · Affichages: 243

R@chid

XLDnaute Barbatruc
Re : Comment réaliser un tel rapprochement bancaire?

Bonsoir @ tous,
Salut zombe,
R@chid: basée sur les couleurs sera dure à gerer.
Je n'ai pas utilisé les couleurs pour quelles soient un critère mais pour vous permettre de me comprendre facilement.
Oui était dans ma première réponse, mais as-tu pris le temps pour voir la 2eme???
Amicalement
 

zombe

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

Salut R@chid
Merci pour ta réaction.
Je viens de le regarder.
Peut-tu m'expliquer ta logique(critère) et ta formule si pas trop demandé?
Aussi est-ce possible pour vous de la traduire par macro?
Cdlt
 

Dormeur74

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

Bonjour tout le monde.
Il pleut sur la Haute-Savoie. Un temps de 11 novembre favorable à Excel.

Si la chose est encore possible, je t'invite à indexer tes libellés dans une autre feuille (ici de 1 à 6) et à mettre des index dans ton tableau principal au lieu des libellés. C'est moins lisible, mais ça règle définitivement le problème de l'homogénéité des libellés. Pour le reste, je regarderai demain matin.
 

zombe

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

Salut Dormeur74 et le forum
Merci pour ta contribution.
Si la chose est encore possible, je t'invite à indexer tes libellés dans une autre feuille (ici de 1 à 6) et à mettre des index dans ton tableau principal au lieu des libellés
Est-ce que cela ne necessitera pas pas un travail manuel de celui qui doit faire le rapprochement?
Si j'ai bien compris ta proposition, cela reviendrai à copier un des libellés de meme nature sur une autre feuille.
ce qui ne sera pas facile avec des données de 15000 voir 20000 lignes (sinon plus).
N'est-il pas possible de garder les libellés sur la feuille initiale et dans la colonne H par exemple, on lettre tous les libellés de même nature (voir exemple du fichier joint).
Le tableau joint illustre parfaitement ce que je souhaite:vous constaterez que les montants au débit qui doivent se solder avec leur contrepartie au crédit ont été lettré.
Je pars du principe que la somme des montants au débit et ayant la même nature de libellés a sa contrepartie au credit.Aussi, il doit y avoir égalité entre total débit et crédit. Si cette règle est vérifiée, on lettre sinon pas de lettrage.
N.B:telles est mon cheminement mais je sais aussi que plusieurs cheminement peuvent aboutir au même résultat donc...
Merci encore pour vos riches contributions.
 

Pièces jointes

  • Rapprochement Type.xlsm
    35.2 KB · Affichages: 222

Dormeur74

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

Il est possible, avec une macro de rien du tout, de faire une "jointure" (1) entre deux feuilles lorsqu'elles ont un champ en commun. Dans l'exemple que je t'ai donné ce matin, le champ "LIBELLE" est devenu le champ "ID_LIBELLE". Tu n'as plus, dans l'ancienne colonne "LIBELLE", qu'une référence à la feuille "LIBELLES". Toute valeur non indexée est très facile à détecter. Cette technique te garantie une intégrité référentielle sur le champ clé que sont les libellés.

Ne pas considérer qu'un tableau de plus de 20000 lignes est énorme : juste quelques secondes de traitement.

(1) terme qui se situe au coeur des SGBD (Système de Gestion de Base de Données)
 

zombe

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

Salut Dormeur74
Merci pour votre réactivité.
Puisque vous me rassuré, je suis partant pour vos solutions.
Faites ce que vous pensez être bien pour moi étant donné que vous avez compris mon besoin.
Ne pas considérer qu'un tableau de plus de 20000 lignes est énorme : juste quelques secondes de traitement.

(1) terme qui se situe au coeur des SGBD (Système de Gestion de Base de Données)
Je suis votre élève et suis prêt à apprendre (SGBD).
 

Dormeur74

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

zombe, si vous étiez mon élève, il y a longtemps que vous seriez sous Oracle ou autre chose de plus sérieux.
A la lecture des libellés, j'ai eu froid dans le dos, car je n'ai pas l'impression qu'on est en droit de bricoler au niveau où vous oeuvrez.

Otez-moi d'un doute : quelle température fait-il au BF aujourd'hui ? De votre réponse dépendra la suite de ma collaboration.
 

zombe

XLDnaute Occasionnel
Re : Comment réaliser un tel rapprochement bancaire?

Salut Dormeur74
Ne m'abandonnez surtout pas en cours de route car je me perdre totalement.
J'ai utilisé "élève" pour dire que je souhaite apprendre.
Oracle ca viendra un jour.
A la lecture des libellés, j'ai eu froid dans le dos
Pourquoi vous dites cela?
AU BF il fait 36°.
Merci pour votre soutien
 

Discussions similaires

  • Résolu(e)
Microsoft 365 Lettrage
Réponses
9
Affichages
1 K

Statistiques des forums

Discussions
311 720
Messages
2 081 923
Membres
101 840
dernier inscrit
SamynoT