Créer un Tableau de bord personnalisé de reporting de données, Excel VBA
Voici un guide détaillé étape par étape pour créer un Tableau de bord personnalisé de reporting de données en utilisant Excel VBA.
Étape 1 : Préparer les données
Avant de créer un tableau de bord, vous devez avoir vos données bien organisées dans Excel. Ces données peuvent provenir de diverses sources comme des bases de données, des fichiers CSV ou des enregistrements internes.
Assurez-vous que vos données :
- Sont bien structurées (tables ou plages de données)
- Ont un format cohérent (dates, nombres, texte)
- Sont nettoyées (pas de données manquantes ou erronées)
Étape 2 : Concevoir la mise en page du tableau de bord
Dans cette étape, vous devez décider de la présentation de votre tableau de bord :
- Graphiques & Diagrammes : Pensez aux indicateurs clés que vous souhaitez suivre et afficher. Choisissez parmi des graphiques à barres, graphiques circulaires, graphiques linéaires, etc.
- Tableaux : Utilisez des Tableaux Croisés Dynamiques ou des tableaux simples pour afficher les données clés.
- Widgets : Vous pouvez vouloir des statistiques résumées, comme des totaux, des moyennes, ou utiliser la mise en forme conditionnelle pour mettre en évidence certains points de données.
Étape 3 : Ouvrir Excel et accéder à l’éditeur Visual Basic
1. Ouvrez votre classeur Excel où vous souhaitez créer le tableau de bord.
2. Appuyez sur Alt + F11 pour ouvrir l’éditeur Visual Basic pour Applications (VBA).
Étape 4 : Insérer un nouveau module
Dans l’éditeur VBA :
1. Dans le panneau de gauche, faites un clic droit sur VBAProject (Nom de votre classeur).
2. Choisissez Insérer → Module.
Cela crée un nouveau module dans lequel vous écrirez le code VBA pour automatiser la création du tableau de bord.
Étape 5 : Écrire le code VBA pour l’automatisation du tableau de bord
Voici un exemple de code VBA pour créer un tableau de bord personnalisé. Cela inclut une mise en page de base pour un tableau de bord et l’automatisation de la création d’un graphique à partir de vos données.
Explication du code :
-
- Création de graphique : Nous allons créer un graphique linéaire simple à partir de données dans un tableau ou une plage dans la feuille Excel.
- Données dynamiques : Le macro mettra à jour le graphique chaque fois que de nouvelles données sont ajoutées ou modifiées.
- Éléments du tableau de bord : Le macro ajoutera également des titres, des étiquettes et d’autres éléments pour donner un aspect professionnel à votre tableau de bord.
Sub CreateDashboard()
' Déclaration des variables
Dim ws As Worksheet
Dim dashboardSheet As Worksheet
Dim dataRange As Range
Dim chartObj As ChartObject
Dim chartDataRange As Range
' Définir les objets feuilles de travail
Set ws = ThisWorkbook.Sheets("Data") ' Supposons que les données se trouvent dans une feuille nommée "Data"
Set dashboardSheet = ThisWorkbook.Sheets("Dashboard") ' Feuille du tableau de bord
' Effacer l'ancien tableau de bord
dashboardSheet.Cells.Clear
' Créer une plage pour les données - ajustez cela en fonction de la structure de vos données
Set dataRange = ws.Range("A1:D10") ' Ajustez la plage de données selon votre besoin
' Créer un objet graphique
Set chartObj = dashboardSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
' Définir la source des données du graphique
Set chartDataRange = dataRange
chartObj.Chart.SetSourceData Source:=chartDataRange
' Changer le type de graphique en graphique linéaire
chartObj.Chart.ChartType = xlLine
' Personnaliser les titres du graphique et les étiquettes
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = "Tendances des ventes"
' Personnaliser les titres des axes X et Y
chartObj.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
chartObj.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Mois"
chartObj.Chart.Axes(xlValue, xlPrimary).HasTitle = True
chartObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Ventes"
' Ajouter une zone de texte avec une description
dashboardSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 50, 300, 30).TextFrame.Characters.Text = "Voici un tableau de bord des ventes"
' Formater le tableau de bord avec de la couleur et du style
dashboardSheet.Cells(1, 1).Font.Bold = True
dashboardSheet.Cells(1, 1).Font.Size = 14
dashboardSheet.Cells(1, 1).Interior.Color = RGB(220, 220, 220) ' Fond gris clair
' Vous pouvez aussi ajouter d'autres éléments comme des segments, des tableaux, etc. selon vos besoins
End Sub
Explication du code :
1. Configuration des feuilles de travail : Nous déclarons deux feuilles de travail : une pour les données (ws) et une pour le tableau de bord (dashboardSheet).
2. Effacer l’ancien tableau de bord : Nous effaçons tout le contenu de la feuille du tableau de bord avant de créer un nouveau tableau.
3. Plage de données : La dataRange est définie sur la plage contenant vos données. Adaptez-la selon la plage exacte dans votre feuille.
4. Création du graphique : Un graphique est ajouté à la feuille du tableau de bord, et sa source de données est définie sur la plage spécifiée (dataRange). Nous utilisons un graphique linéaire dans cet exemple.
5. Personnalisation : Des titres et des étiquettes d’axes sont ajoutés pour rendre le graphique plus informatif.
6. Zone de texte : Une zone de texte est ajoutée pour fournir une brève description ou un titre à votre tableau de bord.
7. Mise en forme : Quelques mises en forme sont appliquées pour améliorer l’apparence visuelle du tableau de bord.
Étape 6 : Exécuter la macro
Une fois que le code est écrit, vous pouvez exécuter la macro :
1. En appuyant sur F5 dans l’éditeur VBA.
2. Alternativement, retournez dans Excel et, dans l’onglet Développeur, vous pouvez assigner la macro à un bouton.
Exemple de sortie :
Après avoir exécuté la macro, vous aurez
- Un graphique linéaire affichant les tendances des données sélectionnées (par exemple, les ventes).
- Un tableau de bord formaté avec des titres, des étiquettes d’axes et une zone de texte avec une brève description.
- Le graphique sera automatiquement mis à jour si les données de la plage changent.
Améliorations supplémentaires :
Vous pouvez ajouter plus de graphiques et diagrammes, comme des graphiques à secteurs, des graphiques à barres, etc.
- Utilisez des Tableaux Croisés Dynamiques pour créer des rapports dynamiques à partir des données.
- Ajoutez des filtres ou des segments pour permettre aux utilisateurs d’interagir avec les données.
- Utilisez la mise en forme conditionnelle pour mettre en évidence des indicateurs clés, comme des ventes élevées ou des performances faibles.
En suivant ces étapes, vous pouvez automatiser la création de tableaux de bord personnalisés dans Excel en utilisant VBA, ce qui vous fera gagner du temps et garantira la cohérence dans vos rapports.