Automatiser les processus de rapprochement des données, Excel VBA
L’automatisation du processus de rapprochement des données (reconciliation) dans Excel peut être réalisée en utilisant VBA (Visual Basic for Applications), ce qui permet de réduire le temps et les erreurs dans la comparaison de deux ensembles de données.
Objectif :
Le but de cette automation est de comparer deux listes de données (par exemple des relevés bancaires et des écritures comptables) et de mettre en évidence les correspondances et les divergences.
Voici un exemple détaillé de code VBA qui peut être utilisé pour automatiser ce processus :
Étapes :
1. Chargement des données :
Nous avons deux ensembles de données : une liste de transactions dans la colonne A et une autre liste dans la colonne B.
2. Rapprochement :
Comparer chaque élément de la colonne A avec les éléments de la colonne B.
3. Mise en évidence des correspondances et des divergences :
Si une valeur dans A est présente dans B, mettre la cellule correspondante en vert (match trouvé).
Si une valeur dans A n’est pas trouvée dans B, la mettre en rouge (pas de match).
4. Options supplémentaires :
Vous pouvez également ajouter une fonctionnalité pour les doublons ou la gestion de certaines erreurs.
Code VBA :
Sub RapprochementDonnees()
Dim ws As Worksheet
Dim rangeA As Range, rangeB As Range
Dim cellA As Range, cellB As Range
Dim matchFound As Boolean
' Définir la feuille de travail active (ici, la feuille "Feuil1")
Set ws = ThisWorkbook.Sheets("Feuil1")
' Définir les plages des données (par exemple, A2:A100 et B2:B100)
Set rangeA = ws.Range("A2:A100")
Set rangeB = ws.Range("B2:B100")
' Initialiser la couleur de fond des cellules
rangeA.Interior.ColorIndex = -4142 ' Effacer les couleurs existantes
' Parcourir chaque cellule dans la plage A
For Each cellA In rangeA
matchFound = False ' Initialiser le flag pour vérifier si une correspondance est trouvée
' Comparer avec chaque cellule dans la plage B
For Each cellB In rangeB
If cellA.Value = cellB.Value Then
matchFound = True
Exit For ' Une fois la correspondance trouvée, on sort de la boucle
End If
Next cellB
' Si une correspondance a été trouvée
If matchFound Then
cellA.Interior.Color = RGB(144, 238, 144) ' Vert clair pour une correspondance
Else
cellA.Interior.Color = RGB(255, 99, 71) ' Rouge clair pour une non-correspondance
End If
Next cellA
MsgBox "Rapprochement terminé !", vbInformation
End Sub
Explication détaillée :
1. Définition des variables :
ws: représente la feuille active où se trouvent vos données.
rangeA et rangeB: représentent les plages des colonnes contenant les données à comparer.
cellA et cellB: permettent de parcourir chaque cellule dans les plages rangeA et rangeB.
matchFound: un indicateur pour vérifier si une correspondance est trouvée entre les valeurs.
2. Définition de la feuille et des plages :
Set ws = ThisWorkbook.Sheets(« Feuil1 ») définit la feuille sur laquelle le code va s’exécuter. Vous pouvez modifier le nom de la feuille ici.
Set rangeA = ws.Range(« A2:A100 ») définit la plage de cellules de la colonne A où les transactions ou les valeurs à rapprocher sont stockées. Vous pouvez ajuster la plage selon vos besoins.
3. Boucles de comparaison :
La première boucle For Each cellA In rangeA parcourt chaque cellule de la colonne A.
La deuxième boucle For Each cellB In rangeB parcourt chaque cellule de la colonne B et compare chaque valeur avec celle de cellA.
4. Gestion des correspondances :
Si une valeur dans la colonne A est trouvée dans la colonne B, la cellule de A est colorée en vert (RGB(144, 238, 144)), ce qui indique une correspondance.
Si aucune correspondance n’est trouvée, la cellule de A est colorée en rouge (RGB(255, 99, 71)).
5. Fin du processus :
Un message MsgBox est affiché une fois que le rapprochement est terminé pour informer l’utilisateur.
Personnalisation :
• Plages de données : Vous pouvez ajuster les plages de cellules en modifiant les valeurs de rangeA et rangeB.
• Couleurs : Vous pouvez personnaliser les couleurs de fond des cellules selon vos préférences (les codes RGB peuvent être modifiés).
• Taille des plages : Si vous avez plus de données à comparer, vous pouvez étendre les plages A2:A100 et B2:B100 en fonction de la quantité de données que vous avez.
Exemple de tableau :
Colonne A (Transactions) Colonne B (Écritures comptables)
100 100
200 300
300 400
500 600
Après l’exécution du code, les valeurs de la colonne A qui ont une correspondance dans la colonne B seront en vert, tandis que celles qui n’ont pas de correspondance seront en rouge.
Améliorations possibles :
1. Ajouter des fonctionnalités pour gérer les doublons ou les écarts.
2. Créer des rapports détaillant les correspondances et non-correspondances.
3. Intégrer la possibilité de filtrer les résultats.
Cela vous permet d’automatiser le rapprochement des données et de gagner du temps dans vos processus comptables ou financiers.