Automatiser les tâches de synthèse des données, Excel VBA

Automatiser les tâches de synthèse des données, Excel VBA
Automatiser les tâches de synthèse des données dans Excel à l’aide de VBA (Visual Basic for Applications) est une excellente façon de gagner du temps et d’améliorer l’efficacité. Le but est de centraliser les données, de les résumer ou de les analyser selon des critères spécifiques, et de générer des rapports ou des graphiques automatiquement.
Exemple : Synthèse des Données avec VBA
Supposons que vous ayez une feuille Excel avec des données de ventes, et vous souhaitez créer une synthèse qui calcule les totaux des ventes par produit, par région, et par mois.
Voici un exemple de code VBA détaillé pour réaliser cette automatisation :
1. Préparation des Données
Imaginons que vous avez une feuille de données structurée comme suit (nommée « Données ») :
Date            Produit  Région  Ventes
01/01/2024  A          Nord     100
02/01/2024  B          Sud       150
03/01/2024  A          Est        200
…               …         …          …
2. Objectif
L’objectif est de créer une synthèse automatique qui :
• Calcule les totaux des ventes par produit.
• Calcule les totaux des ventes par région.
• Calcule les totaux des ventes par mois.
Nous allons créer un code VBA pour générer cette synthèse dans une nouvelle feuille.
3. Code VBA pour Automatiser la Synthèse des Données
Voici le code VBA détaillé :

Sub SyntheseVentes()
' Déclarations des variables
Dim wsSource As Worksheet
Dim wsSynthese As Worksheet
Dim lastRow As Long
Dim i As Long
Dim dictProduits As Object
Dim dictRegions As Object
Dim dictMois As Object
Dim dateMois As String
Dim produit As String
Dim region As String
Dim ventes As Double
' Initialisation des dictionnaires pour les calculs
Set dictProduits = CreateObject("Scripting.Dictionary")
Set dictRegions = CreateObject("Scripting.Dictionary")
Set dictMois = CreateObject("Scripting.Dictionary")
' Références des feuilles
Set wsSource = ThisWorkbook.Sheets("Données")
On Error Resume Next
Set wsSynthese = ThisWorkbook.Sheets("Synthese")
On Error GoTo 0
' Si la feuille Synthese existe déjà, on la supprime
If Not wsSynthese Is Nothing Then
Application.DisplayAlerts = False
wsSynthese.Delete
Application.DisplayAlerts = True
End If
' Création de la feuille Synthese
Set wsSynthese = ThisWorkbook.Sheets.Add
wsSynthese.Name = "Synthese"
' Trouver la dernière ligne de données
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
' Parcours des données et remplissage des dictionnaires
For i = 2 To lastRow ' On commence à la ligne 2 pour ignorer l'en-tête
dateMois = Format(wsSource.Cells(i, 1).Value, "yyyy-mm") ' Extraire l'année et le mois
produit = wsSource.Cells(i, 2).Value
region = wsSource.Cells(i, 3).Value
ventes = wsSource.Cells(i, 4).Value
' Mise à jour du dictionnaire pour les produits
If Not dictProduits.Exists(produit) Then
dictProduits.Add produit, 0
End If
dictProduits(produit) = dictProduits(produit) + ventes
' Mise à jour du dictionnaire pour les régions
If Not dictRegions.Exists(region) Then
dictRegions.Add region, 0
End If
dictRegions(region) = dictRegions(region) + ventes
' Mise à jour du dictionnaire pour les mois
If Not dictMois.Exists(dateMois) Then
dictMois.Add dateMois, 0
End If
dictMois(dateMois) = dictMois(dateMois) + ventes
Next i
' Titrage des colonnes dans la feuille Synthese
wsSynthese.Cells(1, 1).Value = "Critère"
wsSynthese.Cells(1, 2).Value = "Total des Ventes"
' Remplissage des résultats par Produit
wsSynthese.Cells(2, 1).Value = "Par Produit"
wsSynthese.Cells(3, 1).Value = "Produit"
wsSynthese.Cells(3, 2).Value = "Total des Ventes"
i = 4
For Each Key In dictProduits.Keys
wsSynthese.Cells(i, 1).Value = Key
wsSynthese.Cells(i, 2).Value = dictProduits(Key)
i = i + 1
Next Key
' Ajouter une ligne vide entre les sections
i = i + 1
' Remplissage des résultats par Région
wsSynthese.Cells(i, 1).Value = "Par Région"
wsSynthese.Cells(i + 1, 1).Value = "Région"
wsSynthese.Cells(i + 1, 2).Value = "Total des Ventes"
i = i + 2
For Each Key In dictRegions.Keys
wsSynthese.Cells(i, 1).Value = Key
wsSynthese.Cells(i, 2).Value = dictRegions(Key)
i = i + 1
Next Key
' Ajouter une ligne vide entre les sections
i = i + 1
' Remplissage des résultats par Mois
wsSynthese.Cells(i, 1).Value = "Par Mois"
wsSynthese.Cells(i + 1, 1).Value = "Mois"
wsSynthese.Cells(i + 1, 2).Value = "Total des Ventes"
i = i + 2
For Each Key In dictMois.Keys
wsSynthese.Cells(i, 1).Value = Key
wsSynthese.Cells(i, 2).Value = dictMois(Key)
i = i + 1
Next Key
' Mise en forme
wsSynthese.Columns("A:B").AutoFit
wsSynthese.Cells(1, 1).Font.Bold = True
wsSynthese.Cells(1, 2).Font.Bold = True
MsgBox "Synthèse terminée !", vbInformation
End Sub

Explications du Code
1. Déclarations des Variables
wsSource : référence à la feuille contenant les données (« Données »).
wsSynthese : référence à la feuille où la synthèse sera générée.
dictProduits, dictRegions, dictMois : dictionnaires utilisés pour accumuler les ventes par produit, région et mois.
lastRow : pour trouver la dernière ligne de données dans la feuille source.
2. Initialisation des Dictionnaires
Nous utilisons des dictionnaires pour accumuler les ventes par produit, région et mois. Un dictionnaire est parfait pour ce type de calcul car il permet d’ajouter des éléments et de les mettre à jour facilement.
3. Parcours des Données
La boucle For i = 2 To lastRow permet de parcourir toutes les lignes de la feuille « Données » en ignorant la première ligne (l’en-tête).
Pour chaque ligne, nous extrayons la date, le produit, la région, et les ventes, puis mettons à jour les dictionnaires en ajoutant les ventes correspondantes.
4. Création de la Synthèse
Après avoir accumulé les données dans les dictionnaires, nous générons une nouvelle feuille « Synthese » et y insérons les totaux par produit, région et mois.
5. Mise en Forme
La fonction AutoFit est utilisée pour ajuster automatiquement la largeur des colonnes.
Les titres des colonnes sont mis en gras.
Utilisation
Pour utiliser ce code VBA :
1. Ouvrez votre fichier Excel.
2. Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
3. Allez dans Insertion > Module et collez le code.
4. Appuyez sur F5 ou allez dans Excel et exécutez la macro depuis le menu Développeur > Macros.
Cela générera une synthèse complète des ventes par produit, région, et mois sur une nouvelle feuille. Vous pouvez ajuster ce code selon vos besoins spécifiques.