Traitement de données Excel

Salut tout le monde,

J'ai un petit souci depuis ces derniers temps. En faite, j'ai un fichier excel qui comporte les mouvements d'un compte. Pour ça, j'ai des montants aux débits tout comme aux crédits. Bref, je voulais supprimer les montants qui s'annulent( par ex -12 000 et 12 000 ) dans ma base de données. Vous trouvez ci-joint le fichier

Merci pour votre aide

Bonjour,

Que des mouvements s'annulent ou pas il n'y a jamais lieu de supprimer ceux qui s'annulerait !

Pour avoir la résultante, il te suffit de les additionner...

Bonjour Ibrahimos, le forum,

je te retourne ton fichier modifié :

Ctrl d ➯ travail effectué

⚠ vérifie très soigneusement tous les résultats !

Alt F11 pour voir le code VBA, puis revenir sur Excel

si besoin, tu peux demander une adaptation.

merci de me dire si ça te convient.

Edit :

[b]attention : le fichier ci-dessus contient un bug,

que j'ai corrigé dans le fichier de ce post :[/b]

https://forum.excel-pratique.com/viewtopic.php?p=682196#p682196

dhany

bonjour

salut MFerrand, dhany

comme MFerrand : une gestion de compte (ou de n'importe quoi d'autre) se fait en CONSERVANT les mouvements. La simple somme de la colonne rétablit tout

ne pas craindre d'avoir au fil des années des 100 000 lignes. Excel est fait pour ça.

Bonjour jmd, MFerrand, Ibrahimos, le forum,

Ibrahimos a écrit :

Bref, je voulais supprimer les montants qui s'annulent (par ex -12 000 et 12 000) dans ma base de données.

j'avais bien lu le message de MFerrand, mais j'ai quand même réalisé ce qu'a demandé Ibrahimos, au cas où ; par exemple :

* si c'est un travail demandé par un maître de stage ou par un patron, faut bien qu'Ibrahimos exécute les consignes, pas vrai ?

* si c'est un travail extra-comptable et / ou personnel (= non professionnel), qui n'exige pas la plus grande rigueur comptable !

* si le but-même de l'opération est de réduire la taille du fichier, c'est pas de conserver 100 000 lignes qui va aider, non ?

(même si Excel est fait pour ça) ; et ne pas oublier qu'un fichier de taille plus légère a tous ces avantages : il faut moins de place

pour le stocker, et c'est plus rapide pour le sauvegarder, le lire, et l'envoyer par mail.

pour moi, c'est déjà trois raisons suffisantes (mais y'en a peut-être d'autres, qui sait ?)

bon, j'avoue, y'a aussi une autre raison : le programme VBA était très intéressant à faire ! j'ai succombé à la tentation ! j'sais bien qu'ma punition s'ra d'aller en enfer pour l'éternité, surtout qu'j'ai commis l'abominable crime de faire du VBA, et pas du pur Excel !

dhany

re à tous

question : comment savoir si par exemple on n'a qu'un seul +999 et 2 lignes avec -999 laquelle des 2 lignes supprimer ?

(en supposant que chaque ligne ait d'autres données dans les colonnes adjacentes)

comme mon code VBA a prévu ceci, voici une double réponse :

1) la lecture des nombres se fait de haut en bas ; pour un +999 rencontré, il va supprimer le premier -999 rencontré en dessous.

2) si la ligne a des données adjacentes, c'est exactement pour ça qu'j'ai utilisé Cells(i, 1).Delete xlUp

et pas : Rows(i).Delete ; une précaution bien utile, pas vrai ?

dhany

re

je comprends la logique du 1

mais je suis bien incapable de comprendre le code du 2

* Rows(i).Delete supprime la ligne i entière ; c'est comme quand tu supprimes manuellement une ligne dans Excel

* Cells(i, 1).Delete xlUp est l'équivalent VBA de : onglet Accueil, groupe Cellules, Supprimer, « Supprimer les cellules... »

➯ fenêtre "Supprimer" ; « ◉ Décaler les cellules vers le haut »

bien sûr, en VBA, c'est mieux d'connaître un peu l'anglais : Rows = lignes ; Delete = supprimer ; Cells = cellules ; Up = haut ; le xl qui est collé devant est car c'est une constante Excel, xl étant l'abréviation de Excel ; si tu sais déjà l'anglais, ce paragraphe est pour d'autres lecteurs non anglophones.

remarque : en VBA, toutes les constantes Excel commencent par xl

dhany

Merci Dhany

j'ai vu ton travaille mais il y a un petit problème à savoir si on fait la somme des montants du données initiale ça nous donne pas exactement la meme valeurs après réduction.

Le but de se travaille c'est de réduire les lignes sans pour autant bouger la somme des montants. je veux juste conserver les opérations n'ont pas été soldé. Ce dernier réduit les données pour avoir des données précis sur les mouvements d'un compte.

Dhany, est ce que tu pourrais adapter ce contraint sur VBA

Bonjour Ibrahimos,

merci pour ton info ; y'avait tellement de données qu'j'avais pas pu vérifier en détail tous les résultats !

j'ai corrigé le bug, et maintenant, la somme avant et après réduction est bien la même : -1 357 954 906 € ; avec des montants aussi gros, tu dois être dans le secteur des banques, des assurances, ou des industries pétrolières, non ? t'as trouvé de l'or noir ?

voici la nouvelle version du fichier :

même utilisation : faire Ctrl d

le nombre de lignes passe de 818 à 86 (donc de 817 montants à 85 montants, car entête A1 non compris).

si ton problème est réglé, merci de passer le sujet en résolu : il suffit de cliquer sur le bouton qui est près du bouton (dans le coin haut droit du post qui a réglé ton exo) ; ça permet aussi de clôturer le fil de la discussion.

si tu as besoin d'une autre adaptation ou d'une info supplémentaire, n'hésite pas à demander.

dhany

Merci dhany

Comme tu l as je travaille dans une banque.

Le fichier que je t'ai envoyé n est pas exhaustive c est à dire j ai enleve les libellés de chaque opération donc ma question est celle ci:

Est ce que ton vba est adapté juste sur ce données précédentes? Rappelons que le classeur contient qu'une seule colonne.

Il y a d autres colonnes qui n ont pas été intégré qui décrit les détails des écritures. Donc j'aimerai savoir si je pourrais utiliser ce vba sur données complètes qui détaille les écritures d un compte.

re

salut au passage dhany. Faut rester calme

ma suggestion est encore un TCD tout simple

ne pas supprimer de lignes (supprimer des mouvements est une idée étrange pour une banque)

et on peut faire des états par compte ou libellés, par mois, années...

et des totaux cumulés en fonction du calendrier

etc

si tu as 250 000 lignes ça ira encore (je n'ai pas essayé plus)

@Ibrahimos

je pense que ta colonne "Libellé" est à gauche de la colonne des montants, car d'habitude, c'est plutôt comme ça. si c'est bien cela, ça gêne pas ; mais quelle est cette colonne des montants ? dans le fichier exemple, c'est la colonne A ; et dans ton fichier réel, quelle est donc la colonne "Montant" ? la colonne qui est juste à droite de cette colonne, est-elle vide ou utilisée ?

il faudra sûrement faire une adaptation, mais ça sera bien plus simple si tu peux envoyer un fichier représentatif de ton vrai fichier ; ça signifie que toutes les colonnes doivent y être, et en même position ; la ligne d'entêtes, et donc aussi la 1ère ligne de données doivent être en même position ; les données doivent être non confidentielles, donc fictives ; par exemple, si tu mets que ton salaire mensuel est de 8 000 000 €, je saurai que tu as mis une donnée réelle ; alors invente un salaire fictif de 800 000 € seulement.

attention : le code VBA du fichier exemple est pour une colonne "Montant" en colonne A et une colonne vide juste à droite ; note bien que dans ton vrai fichier, cette 2ème condition est vraie si ta colonne "Montant" est la dernière colonne du tableau ; mais y'a probablement à droite une colonne pour le solde après chaque opération, ou pour le montant dans une autre devise que l'Euro ; si oui, l'adaptation à faire devra en tenir compte.

à te lire pour la suite.

dhany

Bonjour Ibrahimos,

j'espère que tu vas bien ; ça fait longtemps que tu n'es plus venu sur le forum :

si tu reviens sur ce sujet, j'ai une chose importante à te dire en MP.

dhany

Bonsoir Ibrahimos, le forum,

le fichier que tu m'as fourni en MP contenant des infos confidentielles, je mets ci-dessous uniquement mon code VBA correspondant.

* le code VBA peut servir pour n'importe quel fichier avec une colonne "Montant" ; entête en ligne 1 ; 1er montant en ligne 2 ; montants positifs précédant le montant négatif auquel ils correspondent (voir plus loin).

* la colonne des montants est celle indiquée par la constante CM, ici : "H" ; si les montants sont dans une autre colonne, il suffit de modifier cette seule ligne, comme l'indique le commentaire vba.

* il faut supprimer les montants qui s'annulent (par exemple -12 000 et 12 000) ; attention : pour un montant négatif donné, il peut y avoir plusieurs montants positifs ; ces montants positifs sont censés être au-dessus du montant négatif correspondant.

* attention : l'algorithme de recherche de correspondance est basé sur la position des montants et non pas sur la recherche de combinaisons multiples (cette 2ème façon, non implémentée ici, s'apparente au jeu « Le compte est bon », avec l'opération addition uniquement).

Option Explicit

Sub Job()
  Const CM$ = "H" 'CM : colonne des montants ; autre exemple : Const CM$ = "AD"
  Dim dlig&: dlig = Cells(Rows.Count, CM).End(xlUp).Row: If dlig < 3 Then Exit Sub
  Dim Tbl, tot@, m0@, mn@, mp@, flg As Byte, lg0&, lg1&, lg2&, lg3&, n%, i&
  Tbl = Cells(1, CM).Resize(dlig, 2): For i = 2 To dlig: Tbl(i, 2) = 0: Next i
  'boucle For : à partir de 3, pour ignorer un montant négatif en ligne 2 ;
  'mais un montant positif en ligne 2 sera quand même pris en compte
  For lg0 = 3 To dlig
    m0 = Tbl(lg0, 1)
    If m0 < 0 And Tbl(lg0, 2) = 0 Then
      'lg3 : ligne du montant négatif, et limite maxi pour lg1 & lg2 ; la limite maxi pour
      'lg2 sera aussi selon le cumul des montants positifs, qui ne peut être > à mp !
      lg3 = lg0: mn = m0: mp = -m0: lg1 = 2
      Do
        flg = 0: tot = 0
        Do 'calc lg1
          m0 = Tbl(lg1, 1)
          If m0 > 0 And Tbl(lg1, 2) = 0 Then tot = m0: Exit Do
          lg1 = lg1 + 1
        Loop Until lg1 = lg3 Or lg1 = dlig
        If tot > 0 Then
          lg2 = lg1
          Do 'calc lg2
            If tot = mp Then 'trouvé !  :)
              flg = 1: n = n + 1: Tbl(lg3, 2) = n
              For i = lg1 To lg2
                m0 = Tbl(i, 1)
                If m0 > 0 And Tbl(i, 2) = 0 Then Tbl(i, 2) = n
              Next i
              Exit Do
            End If
            lg2 = lg2 + 1: If lg2 > dlig Then Exit Do
            m0 = Tbl(lg2, 1)
            If m0 > 0 And Tbl(lg2, 2) = 0 Then tot = tot + m0
          Loop Until lg2 = lg3
          If flg = 1 Then Exit Do
        End If
        lg1 = lg1 + 1
      Loop Until lg1 = lg3 Or lg1 > dlig
    End If
  Next lg0
  Application.ScreenUpdating = 0
  For i = dlig To 2 Step -1
    'If Tbl(i, 2) > 0 Then Cells(i, CM).Interior.ColorIndex = 3
    If Tbl(i, 2) > 0 Then Rows(i).Delete
  Next i
End Sub

* pour la dernière boucle située en fin de sub, mettre au choix l'une des 2 lignes de code VBA ; actuellement, c'est la 2ème ligne (active) qui supprime les lignes ; autre possibilité : mettre sur fond rouge les montants qui ont fait l'objet d'une correspondance ; pour cela, mettre la 2ème ligne en commentaire et activer la ligne du dessus (enlever l'apostrophe en début de ligne).

* pour ceux qui ont des notions de débogage : mettre un point d'arrêt sur la ligne Application.ScreenUpdating = 0 ; ajouter un espion pour la variable Tbl ; exécuter la macro, puis vérifier les valeurs successives de Tbl ; il s'agit de développer les Tbl(n) successifs jusqu'à trouver un montant négatif ; regarder la valeur du Tbl(n,2) correspondant, par exemple 6 ; c'est donc la correspondance n° 6 ; regarder alors plus haut les montants positifs Tbl(n,1) pour lesquels Tbl(n,2) = 6 ; faire de même avec le montant négatif suivant.

dhany