Créer un tableau de bord personnalisé de visualisation des données, Excel VBA

Créer un tableau de bord personnalisé de visualisation des données, Excel VBA

Créer un tableau de bord personnalisé de visualisation des données avec Excel VBA peut être un moyen puissant de rendre vos données plus interactives et dynamiques. Avec VBA (Visual Basic for Applications), vous pouvez créer des tableaux de bord qui ne se contentent pas de présenter des données sous forme de graphiques, mais qui permettent également une interaction avec l’utilisateur, de l’automatisation et une personnalisation avancée. Dans ce guide, je vais vous expliquer étape par étape comment créer un tableau de bord de visualisation des données à l’aide de VBA.
Guide détaillé pour créer un tableau de bord personnalisé avec VBA dans Excel
1. Configuration de l’environnement du tableau de bord
Avant de plonger dans le codage VBA, assurez-vous que vous avez la structure de données nécessaire dans votre classeur Excel. Cela servira de source pour les données que vous souhaitez visualiser.

• Feuille de données : Préparez une feuille de données (par exemple, nommée « Données ») avec des lignes de données et des colonnes pour chaque catégorie. Par exemple :

Date | Ventes | Dépenses | Région
01/01/2025 | 5000 | 2000 | Nord
02/01/2025 | 6000 | 2100 | Sud
03/01/2025 | 7000 | 2300 | Est

• Feuille de tableau de bord : Créez une autre feuille (par exemple, nommée « Tableau de bord ») où le tableau de bord sera effectivement construit. Cette feuille contiendra des graphiques, des tableaux et des boutons pour l’interaction avec l’utilisateur.

2. Écrire le code VBA pour automatiser le tableau de bord
Commençons à écrire le code VBA pour automatiser la création du tableau de bord.
Étape 1 : Ouvrir l’éditeur VBA

• Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
• Dans l’éditeur VBA, allez dans Insertion > Module pour ajouter un nouveau module.

Étape 2 : Définir la structure de base pour la création du tableau de bord
Nous allons maintenant écrire la structure de base du code VBA. L’objectif est de créer un graphique, d’automatiser la mise à jour des données et de mettre en place des contrôles interactifs.

Sub CreerTableauDeBord()
    Dim wsTableauDeBord As Worksheet
    Dim wsDonnees As Worksheet
    Dim graphique As ChartObject
    Dim plageDonnees As Range
    Dim derniereLigne As Long
    ' Définir les références aux feuilles de travail
    Set wsDonnees = ThisWorkbook.Sheets("Données")
    Set wsTableauDeBord = ThisWorkbook.Sheets("Tableau de bord")
    ' Effacer le contenu précédent du tableau de bord
    wsTableauDeBord.Cells.Clear
    ' Obtenir la dernière ligne de données
    derniereLigne = wsDonnees.Cells(wsDonnees.Rows.Count, "A").End(xlUp).Row
    ' Définir la plage de données pour le graphique
    Set plageDonnees = wsDonnees.Range("A1:D" & derniereLigne)
    ' Créer un graphique pour les ventes vs les dépenses
    Set graphique = wsTableauDeBord.ChartObjects.Add
    graphique.Chart.SetSourceData Source:=plageDonnees
    graphique.Chart.ChartType = xlLine ' Graphique en courbes
    ' Personnaliser l'apparence du graphique
    With graphique.Chart
        .HasTitle = True
        .ChartTitle.Text = "Ventes vs Dépenses"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Date"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Montant"
        .SeriesCollection(1).Name = "Ventes"
        .SeriesCollection(2).Name = "Dépenses"
    End With
    ' Créer un résumé des ventes et des dépenses totales
    wsTableauDeBord.Cells(1, 1).Value = "Ventes totales :"
    wsTableauDeBord.Cells(1, 2).Value = Application.WorksheetFunction.Sum(wsDonnees.Range("B2:B" & derniereLigne))
    wsTableauDeBord.Cells(2, 1).Value = "Dépenses totales :"
    wsTableauDeBord.Cells(2, 2).Value = Application.WorksheetFunction.Sum(wsDonnees.Range("C2:C" & derniereLigne))
End Sub

Explication du code
1. Définition des feuilles de travail :

• wsDonnees fait référence à la feuille où les données brutes sont stockées (dans ce cas, « Données »).
• wsTableauDeBord fait référence à la feuille où le tableau de bord sera créé (dans ce cas, « Tableau de bord »).

2. Effacer le tableau de bord précédent :

• wsTableauDeBord.Cells.Clear efface tout le contenu existant de la feuille du tableau de bord.

3. Obtenir la plage de données :

• derniereLigne est calculée pour déterminer combien de lignes de données sont présentes dans la feuille « Données ».
• plageDonnees est la plage qui contient les données réelles pour le graphique.

4. Créer un graphique :

• Un nouveau graphique est créé à l’aide de ChartObjects.Add, et la source de données est assignée avec SetSourceData.
• Le type de graphique est défini sur un graphique en courbes (xlLine), et des personnalisations sont appliquées au titre du graphique, aux titres des axes et aux noms des séries.

5. Calculs de résumé :

• En utilisant Application.WorksheetFunction.Sum, les ventes et les dépenses totales sont calculées et affichées sur la feuille du tableau de bord.

3. Ajouter des contrôles interactifs
L’une des fonctionnalités puissantes de VBA dans Excel est la possibilité d’ajouter des contrôles interactifs comme des boutons, des listes déroulantes et des champs de saisie. Ajoutons un bouton pour actualiser les données du tableau de bord.
Étape 1 : Ajouter un bouton à la feuille du tableau de bord
1. Allez sur la feuille « Tableau de bord » dans Excel.
2. Cliquez sur l’onglet « Développeur » (s’il n’est pas visible, activez-le dans les options Excel).
3. Cliquez sur « Insérer » et choisissez un bouton (Contrôle de formulaire).
4. Dessinez le bouton sur la feuille.
Étape 2 : Assigner le code VBA au bouton
1. Faites un clic droit sur le bouton et choisissez « Assigner une macro ».
2. Sélectionnez la macro CreerTableauDeBord.
Désormais, lorsque l’utilisateur cliquera sur le bouton, le tableau de bord sera actualisé et affichera les données mises à jour.
4. Ajouter des filtres et de l’interactivité
Ajoutons une zone de liste déroulante pour permettre à l’utilisateur de filtrer les données en fonction d’une région spécifique (par exemple, Nord, Sud, Est). Cela permettra à l’utilisateur de visualiser les données par région sur le tableau de bord.
Étape 1 : Ajouter une zone de liste déroulante pour le filtre de région
1. Allez dans l’onglet « Développeur » et cliquez sur « Insérer » > « Zone de liste déroulante » (Contrôle ActiveX).
2. Placez la zone de liste déroulante sur la feuille du tableau de bord.
3. Faites un clic droit sur la zone de liste déroulante et sélectionnez « Propriétés ».
4. Définissez les propriétés :

• Nom : cmbRegion
• Plage de liste : Données!D2:D (en supposant que la colonne des régions se trouve dans la colonne D de la feuille « Données »).

Étape 2 : Écrire le code VBA pour filtrer les données selon la région sélectionnée
Modifiez la macro CreerTableauDeBord pour inclure le filtrage en fonction de la région sélectionnée dans la zone de liste déroulante.

Sub CreerTableauDeBord()
    Dim wsTableauDeBord As Worksheet
    Dim wsDonnees As Worksheet
    Dim graphique As ChartObject
    Dim plageDonnees As Range
    Dim derniereLigne As Long
    Dim regionSelectionnee As String
    ' Définir les références aux feuilles de travail
    Set wsDonnees = ThisWorkbook.Sheets("Données")
    Set wsTableauDeBord = ThisWorkbook.Sheets("Tableau de bord")
    ' Effacer le contenu précédent du tableau de bord
    wsTableauDeBord.Cells.Clear
    ' Obtenir la dernière ligne de données
    derniereLigne = wsDonnees.Cells(wsDonnees.Rows.Count, "A").End(xlUp).Row
    ' Obtenir la région sélectionnée dans la liste déroulante
    regionSelectionnee = wsTableauDeBord.Shapes("cmbRegion").ControlFormat.Value
    ' Filtrer les données selon la région sélectionnée
    If regionSelectionnee <> "" Then
        wsDonnees.Rows.Hidden = False
        For i = 2 To derniereLigne
            If wsDonnees.Cells(i, 4).Value <> regionSelectionnee Then
                wsDonnees.Rows(i).Hidden = True
            End If
        Next i
    End If
    ' Définir la plage de données pour le graphique
    Set plageDonnees = wsDonnees.Range("A1:D" & derniereLigne)
    ' Créer un graphique pour les ventes vs les dépenses
    Set graphique = wsTableauDeBord.ChartObjects.Add
    graphique.Chart.SetSourceData Source:=plageDonnees
    graphique.Chart.ChartType = xlLine
    ' Personnaliser l'apparence du graphique
    With graphique.Chart
        .HasTitle = True
        .ChartTitle.Text = "Ventes vs Dépenses"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Date"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Montant"
        .SeriesCollection(1).Name = "Ventes"
        .SeriesCollection(2).Name = "Dépenses"
    End With
    ' Créer un résumé des ventes et des dépenses totales
    wsTableauDeBord.Cells(1, 1).Value = "Ventes totales :"
    wsTableauDeBord.Cells(1, 2).Value = Application.WorksheetFunction.Sum(wsDonnees.Range("B2:B" & derniereLigne))
    wsTableauDeBord.Cells(2, 1).Value = "Dépenses totales :"
    wsTableauDeBord.Cells(2, 2).Value = Application.WorksheetFunction.Sum(wsDonnees.Range("C2:C" & derniereLigne))
End Sub

Explication du code de filtrage

• Valeur de la zone de liste déroulante :
  La région sélectionnée est capturée à l’aide de wsTableauDeBord.Shapes(« cmbRegion« ).ControlFormat.Value.
• Filtrage des données :
  Si une région est sélectionnée, le code masque les lignes qui ne correspondent pas à la région sélectionnée en itérant sur toutes les lignes et en comparant la valeur de la colonne D (la colonne des régions).

5. Conclusion
Vous avez maintenant créé un tableau de bord de visualisation des données personnalisé dans Excel avec VBA, comprenant des graphiques dynamiques et interactifs. Les utilisateurs peuvent actualiser les données, sélectionner différentes régions et voir les données de ventes et de dépenses visualisées. Vous pouvez également améliorer ce tableau de bord en ajoutant plus d’interactivité (par exemple, des segments, plus de graphiques, un filtrage avancé) et en incorporant d’autres ensembles de données.