Créer des agrégations dynamiques de plage, Excel VBA

Créer des agrégations dynamiques de plage, Excel VBA

Voici un code détaillé en VBA pour créer des agrégations dynamiques de plage, tel que la somme, la moyenne et le comptage, avec une explication complète. Ce code permet de définir dynamiquement une plage de données et d’effectuer des calculs de synthèse, même si le nombre de lignes change. 

Exemple de code pour créer des agrégations de plages dynamiques avec VBA : 

Scénario : 

Vous avez un jeu de données dont le nombre de lignes peut varier, et vous souhaitez appliquer des agrégations (par exemple, la somme, la moyenne, le comptage) sur la plage dynamique. La plage dynamique s’ajustera en fonction de la quantité de données présentes. 

Code : 

Sub CreerAgrégationPlageDynamique() 
    Dim ws As Worksheet 
    Dim derniereLigne As Long 
    Dim plageDynamique As Range 
    Dim sommeResultat As Double 
    Dim moyenneResultat As Double 
    Dim comptageResultat As Long 
    ' Définir la feuille de calcul 
    Set ws = ThisWorkbook.Sheets("Feuille1") 
    ' Trouver la dernière ligne avec des données dans la colonne A (en supposant que les données commencent à partir de la ligne 1) 
    derniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    ' Définir la plage dynamique (en supposant que les données sont dans la colonne A, de la ligne 1 à la dernière ligne avec des données) 
    Set plageDynamique = ws.Range("A1:A" & derniereLigne) 
    ' Agrégations 
    sommeResultat = Application.WorksheetFunction.Sum(plageDynamique) 
    moyenneResultat = Application.WorksheetFunction.Average(plageDynamique) 
    comptageResultat = Application.WorksheetFunction.Count(plageDynamique)  
    ' Afficher les résultats 
    MsgBox "SOMME : " & sommeResultat & vbCrLf & _ 
           "MOYENNE : " & moyenneResultat & vbCrLf & _ 
           "NOMBRE : " & comptageResultat 
End Sub

Explication : 

1. Définir la feuille de calcul : La première ligne du code définit la feuille de calcul où se trouvent les données. Vous pouvez remplacer « Feuille1 » par le nom de la feuille réelle que vous utilisez. 

Set ws = ThisWorkbook.Sheets("Feuille1") 

2. Trouver la dernière ligne : Le code utilise ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row pour trouver la dernière ligne contenant des données dans la colonne A. Cela permet d’ajuster dynamiquement la plage en fonction de la quantité de données présentes. La méthode End(xlUp) simule l’action de maintenir la touche Ctrl enfoncée et d’appuyer sur la flèche du haut, permettant ainsi de trouver la première cellule non vide à partir du bas de la colonne. 

derniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 

3. Définir la plage dynamique : La plage est ensuite définie dynamiquement de la première ligne jusqu’à la dernière ligne contenant des données dans la colonne A. 

Set plageDynamique = ws.Range("A1:A" & derniereLigne) 

4. Effectuer les agrégations : Ensuite, des fonctions d’agrégation telles que SOMME, MOYENNE et NOMBRE sont appliquées à la plage dynamique. L’objet Application.WorksheetFunction permet d’appeler les fonctions natives d’Excel dans VBA. 

sommeResultat = Application.WorksheetFunction.Sum(plageDynamique) 
moyenneResultat = Application.WorksheetFunction.Average(plageDynamique) 
comptageResultat = Application.WorksheetFunction.Count(plageDynamique) 

5. Afficher les résultats : Enfin, les résultats des agrégations sont affichés dans une boîte de message. 

MsgBox "SOMME : " & sommeResultat & vbCrLf & _ 
       "MOYENNE : " & moyenneResultat & vbCrLf & _ 
       "NOMBRE : " & comptageResultat 

Personnalisation : 

• Changer de colonnes : Si vous souhaitez agrégater les données d’autres colonnes, vous pouvez modifier la définition de plageDynamique, par exemple, remplacez « A1:A » par « B1:B » pour la colonne B. 
• Agrégations multiples : Si vous souhaitez effectuer plusieurs agrégations (par exemple, la somme et la moyenne pour différentes plages), vous pouvez étendre le code de manière similaire en ajoutant d’autres appels à WorksheetFunction. 

Points clés : 

• Plage Dynamique : Le code utilise la méthode End(xlUp) pour trouver dynamiquement les limites de la plage, ce qui permet au code de fonctionner même si le nombre de lignes change. 
• Agrégations VBA : L’utilisation de Application.WorksheetFunction permet d’effectuer des agrégations telles que SOMME, MOYENNE, NOMBRE, etc., directement dans VBA. 
• Flexibilité : Cette approche en VBA est flexible, car vous pouvez modifier les références de colonnes, les types d’agrégation, ou même ajouter des conditions pour filtrer les données.