Automatiser les processus d’analyse statistique, Excel VBA

Automatiser les processus d’analyse statistique, Excel VBA

Automatiser les processus d’analyse statistique dans Excel avec VBA peut permettre d’effectuer des calculs complexes rapidement et de générer des rapports de manière répétée sans avoir à entrer manuellement des formules à chaque fois. Je vais vous fournir un exemple détaillé de code VBA qui effectue des analyses statistiques de base telles que la moyenne, l’écart type, la variance, les quartiles et génère un rapport automatisé. Le code sera structuré pour travailler avec des données dans une plage spécifique de cellules. 

Objectif

L’objectif de ce code est d’automatiser l’analyse des données statistiques suivantes : 

• Moyenne 

• Écart type 

• Variance 

• Médiane 

• Quartiles (Q1, Q2, Q3) 

• Min / Max 

Le rapport sera ensuite généré dans une nouvelle feuille. 

Code VBA : Automatiser l’analyse statistique

Étape 1: Créer un module VBA 

1. Ouvrez Excel et accédez à l’éditeur VBA en appuyant sur Alt + F11. 

      2. Dans le menu « Insertion », sélectionnez « Module » pour créer un nouveau module. 

      3. Collez le code suivant dans ce module. 

Sub AnalyseStatistiqueAutomatique() 
    ' Déclaration des variables 
    Dim wsData As Worksheet 
    Dim wsRapport As Worksheet 
    Dim PlageData As Range 
    Dim DerniereLigne As Long 
    Dim Cellule As Range 
    ' Assigner la feuille de données et créer une nouvelle feuille pour le rapport 
    Set wsData = ThisWorkbook.Sheets("Données") ' Remplacez "Données" par le nom de votre feuille contenant les données 
    Set wsRapport = ThisWorkbook.Sheets.Add 
    wsRapport.Name = "Rapport_Statistique" 
    ' Déterminer la dernière ligne avec des données dans la colonne A 
    DerniereLigne = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row 
    ' Définir la plage de données pour l'analyse (exemple : de A2 à A100) 
    Set PlageData = wsData.Range("A2:A" & DerniereLigne) ' Ajustez la plage si nécessaire 
    ' Ajouter un en-tête au rapport 
    wsRapport.Cells(1, 1).Value = "Analyse Statistique des Données" 
    wsRapport.Cells(2, 1).Value = "Métrique" 
    wsRapport.Cells(2, 2).Value = "Valeur" 
    ' Calcul des statistiques et insertion dans le rapport 
    wsRapport.Cells(3, 1).Value = "Moyenne" 
    wsRapport.Cells(3, 2).Value = WorksheetFunction.Average(PlageData) 
    wsRapport.Cells(4, 1).Value = "Écart Type" 
    wsRapport.Cells(4, 2).Value = WorksheetFunction.StDev(PlageData) 
    wsRapport.Cells(5, 1).Value = "Variance" 
    wsRapport.Cells(5, 2).Value = WorksheetFunction.Var(PlageData) 
    wsRapport.Cells(6, 1).Value = "Médiane" 
    wsRapport.Cells(6, 2).Value = WorksheetFunction.Median(PlageData) 
    wsRapport.Cells(7, 1).Value = "Quartile 1 (Q1)" 
    wsRapport.Cells(7, 2).Value = WorksheetFunction.Quartile(PlageData, 1) 
    wsRapport.Cells(8, 1).Value = "Quartile 2 (Q2)" 
    wsRapport.Cells(8, 2).Value = WorksheetFunction.Quartile(PlageData, 2) 
    wsRapport.Cells(9, 1).Value = "Quartile 3 (Q3)" 
    wsRapport.Cells(9, 2).Value = WorksheetFunction.Quartile(PlageData, 3) 
    wsRapport.Cells(10, 1).Value = "Valeur Min" 
    wsRapport.Cells(10, 2).Value = WorksheetFunction.Min(PlageData) 
    wsRapport.Cells(11, 1).Value = "Valeur Max" 
    wsRapport.Cells(11, 2).Value = WorksheetFunction.Max(PlageData) 
    ' Mise en forme du rapport 
    wsRapport.Rows(1).Font.Bold = True 
    wsRapport.Rows(2).Font.Bold = True 
    wsRapport.Columns("A:B").AutoFit 
    wsRapport.Range("A1:B11").Borders(xlEdgeBottom).LineStyle = xlContinuous 
    ' Message d'information 
    MsgBox "L'analyse statistique a été générée avec succès dans la feuille " & wsRapport.Name, vbInformation 
End Sub

Explication du Code

        1.  Déclaration des variables : 

• wsData : Fait référence à la feuille de données contenant les valeurs à analyser. 

• wsRapport : Fait référence à la feuille où les résultats de l’analyse seront affichés. 

• PlageData : La plage de cellules qui contient les données pour l’analyse statistique. 

• DerniereLigne : Utilisé pour déterminer la dernière ligne contenant des données dans la colonne A (ajustez si nécessaire). 

       2. Assigner les feuilles de travail : 

• Le code fait référence à la feuille « Données » pour les données source, et crée une nouvelle feuille « Rapport_Statistique » pour afficher les résultats. 

     3. Détermination de la plage de données : 

• PlageData est définie comme la plage de cellules contenant les données (de A2 à la dernière ligne de la colonne A).

      4. Calcul des statistiques : 

• Le code utilise la fonction WorksheetFunction pour effectuer les calculs suivants : 

• Moyenne avec Average 

• Écart-type avec StDev 

• Variance avec Var 

• Médiane avec Median 

• Quartiles avec Quartile 

• Min et Max avec Min et Max 

Insertion des résultats dans la feuille de rapport : 

• Chaque statistique est insérée dans les cellules correspondantes sur la nouvelle feuille.

Mise en forme du rapport : 

• Le rapport est mis en forme avec des bordures et un ajustement automatique des colonnes pour la lisibilité. 

• Les en-têtes sont mis en gras pour les rendre plus visibles.

 Message d’information : 

• Une fois l’analyse terminée, un message s’affiche pour confirmer la création du rapport.

Utilisation du code

1. Assurez-vous que vos données sont dans la feuille « Données », avec les valeurs que vous souhaitez analyser dans la colonne A (ou ajustez la plage de données dans le code si nécessaire). 

1. Allez dans l’éditeur VBA (Alt + F11), créez un module, et collez le code. 

1. Exécutez le code avec F5 ou via le menu « Exécuter » de l’éditeur VBA. 

1. Un rapport sera généré dans une nouvelle feuille avec les résultats de l’analyse statistique. 

Conclusion

Ce code VBA simplifie l’analyse statistique des données en automatisant les calculs et en générant rapidement un rapport propre dans une nouvelle feuille. Vous pouvez étendre ce code pour inclure des analyses plus complexes, des graphiques ou d’autres mesures statistiques selon vos besoins.