Comment générer automatiquement un tableau croisé dynamique avec VBA

  • Auteur/autrice de la publication :
  • Dernière modification de la publication :février 21, 2023
  • Post category:top astuce
  • Temps de lecture :11 min de lecture

Les tableaux croisés dynamiques dans Excel : un outil incontournable pour comprendre et analyser les données

Les tableaux croisés dynamiques (ou pivot tables en anglais) sont des outils essentiels pour rendre les données plus faciles à comprendre et à analyser. Ils permettent de condenser et de regrouper les données de manière à en extraire des informations significatives. Les utilisateurs d’Excel ont largement adopté cet outil dans l’industrie des données.

Saviez-vous qu’il est possible d’automatiser la création de tableaux croisés dynamiques dans Excel et de les générer d’un simple clic ? Excel intègre bien le langage VBA et est devenu un excellent outil pour automatiser les tâches répétitives.

Comment automatiser la création de tableaux croisés dynamiques dans Excel

Pour automatiser la création de tableaux croisés dynamiques dans Excel avec VBA, ouvrez un nouveau fichier Excel et renommez les feuilles comme suit :

  • Première feuille : Macro
  • Deuxième feuille : Données

La feuille Macro contient le script de la macro, tandis que la feuille Données contient vos données. Sur la feuille Macro, vous pouvez insérer une forme de votre choix et y attribuer la macro. Faites un clic droit sur la forme, puis cliquez sur Attribuer Macro. Dans la boîte de dialogue qui s’ouvre, sélectionnez le nom de votre macro et cliquez sur OK. Cette étape permet d’attribuer la macro à la forme.

Pour ouvrir l’éditeur de code VBA d’Excel, appuyez sur Alt + F11. Une fois dans l’éditeur de code, faites un clic droit sur le nom du fichier, puis sélectionnez Insérer et Module. Il est important de se souvenir que vous devrez écrire tous les codes VBA dans un module avant de les exécuter.

1. Nom de module

Il est recommandé d’utiliser un nom de module qui correspond à l’objectif du code. Dans le cadre de cet exemple, vous pouvez définir le nom du module comme suit :

 sub pivot_demo() 

Le nom du module se termine par la commande End Sub, qui est la commande de fin d’un module :

 End Sub 

2. Déclarer les variables

Dans le module, commencez par déclarer des variables pour stocker certaines valeurs définies par l’utilisateur que vous utiliserez dans le script. Vous pouvez utiliser la déclaration Dim pour déclarer des variables, comme suit :

 Dim PSheet As Worksheet, DSheet As Worksheet Dim PvtCache As PivotCache Dim PvtTable As PivotTable Dim PvtRange As Range Dim Last_Row As Long, Last_Col As Long Dim sht1 as Variant 

Vous utiliserez ces variables pour les éléments suivants :

  • PSheet : la feuille de destination, où VBA créera le tableau croisé dynamique.
  • DSheet : la feuille de données.
  • PvtCache : un cache de tableau croisé dynamique qui contient le tableau croisé dynamique.
  • PvtTable : l’objet tableau croisé dynamique.
  • PvtRange : une plage de données pour le tableau croisé dynamique.
  • Last_Row et Last_Col : dernière ligne et dernière colonne populées dans la feuille de données (DSheet).
  • Sht1 : cette variable est de type variant.

3. Supprimer les avertissements et les messages

Les erreurs, avertissements et messages inutiles ralentissent vos codes VBA. En supprimant ces messages, vous pouvez considérablement accélérer le processus. Utilisez le code suivant :

 On Error Resume Next With Application .DisplayAlerts = False .ScreenUpdating = False End With 

Où :

  • On Error Resume Next : cette clause supprime toutes les erreurs d’exécution.
  • Application : Application fait référence à Excel.
  • DisplayAlerts : la propriété DisplayAlerts définit si les alertes sont affichées ou non.
  • ScreenUpdating : cette propriété définit si les modifications sont mises à jour en temps réel ou uniquement une fois que le code a fini de s’exécuter.

Lorsque ce code s’exécute, il supprime tous les alertes, avertissements et messages que Excel afficherait autrement. Vous pouvez désactiver les paramètres DisplayAlerts et ScreenUpdating en leur attribuant la valeur False.

Vers la fin du code, vous pouvez réactiver DisplayAlerts et ScreenUpdating en leur attribuant à nouveau la valeur True. Cela permet de réactiver les alertes et de mettre à jour les modifications en temps réel une fois que le code a fini de s’exécuter :

 With Application .DisplayAlerts = True .ScreenUpdating = True End With 

4. Définir les feuilles

Définissez maintenant les feuilles de travail à utiliser dans le script :

 Set PSheet = ThisWorkbook.Sheets("Macro") Set DSheet = ThisWorkbook.Sheets("Données") 

Où :

  • ThisWorkbook : ThisWorkbook fait référence au classeur actif.
  • Sheets : Sheets est une collection qui contient toutes les feuilles de travail dans un classeur.

5. Définir la plage de données

Définissez maintenant la plage de données à utiliser pour le tableau croisé dynamique. Pour ce faire, vous pouvez utiliser les variables Last_Row et Last_Col que vous avez déclarées précédemment :

 Last_Row = DSheet.Cells(Rows.Count, 1).End(xlUp).Row Last_Col = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PvtRange = DSheet.Cells(1, 1).Resize(Last_Row, Last_Col) 

Où :

  • Cells : Cells est une collection qui contient toutes les cellules d’une feuille de travail.
  • Rows.Count : Rows.Count renvoie le nombre de lignes dans la feuille de travail.
  • Columns.Count : Columns.Count renvoie le nombre de colonnes dans la feuille de travail.
  • End : End est une méthode qui permet de localiser la dernière cellule peuplée dans une plage de données.
  • xlUp : xlUp définit la direction vers laquelle chercher la dernière cellule peuplée (vers le haut).
  • xlToLeft : xlToLeft définit la direction vers laquelle chercher la dernière cellule peuplée (vers la gauche).
  • Resize : Resize est une méthode qui permet de redimensionner une plage de données.

6. Créer le cache de tableau croisé dynamique

Utilisez la méthode CreatePivotCache pour créer le cache de tableau croisé dynamique :

 Set PvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange) 

Où :

  • PivotCaches : PivotCaches est une collection qui contient tous les caches de tableaux croisés dynamiques dans un classeur.
  • Create : Create est une méthode qui permet de créer un cache de tableau croisé dynamique.
  • SourceType : SourceType définit le type de données utilisées pour alimenter le cache de tableau croisé dynamique (xlDatabase indique que les données proviennent d’une base de données).
  • SourceData : SourceData définit la plage de données utilisées pour alimenter le cache de tableau croisé dynamique.

7. Créer le tableau croisé dynamique

Nous arrivons dans l’étape la plus décisive; il maintenant temps de créer le tableau croisé dynamique pour cela rien de plus simple, suivez le guide :

Utilisez la méthode CreatePivotTable pour créer le tableau croisé dynamique :

 Set PvtTable = PSheet.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=PSheet.Cells(2, 2), TableName:="PvtTable") 

Où :

  • PivotTables : PivotTables est une collection qui contient tous les tableaux croisés dynamiques dans une feuille de travail.
  • Add : Add est une méthode qui permet de créer un tableau croisé dynamique.
  • PivotCache : PivotCache définit le cache de tableau croisé dynamique à utiliser.
  • TableDestination : TableDestination définit l’emplacement où le tableau croisé dynamique sera inséré dans la feuille de travail.
  • TableName : TableName définit le nomdu tableau croisé dynamique.

8. Ajouter et formater les champs

Pour ajouter et formater les champs, vous pouvez utiliser les méthodes AddFields et PivotFields :

 With PvtTable.PivotFields("Région") .Orientation = xlRowField .Position = 1 End With With PvtTable.PivotFields("Sous-catégorie") .Orientation = xlRowField .Position = 2 End With With PvtTable.PivotFields("État") .Orientation = xlColumnField .Position = 1 End With With PvtTable.PivotFields("Ventes") .Orientation = xlDataField .Function = xlSum .Name = "Total des ventes" .NumberFormat = "$#,##0" End With 

Où :

  • AddFields : AddFields est une méthode qui permet d’ajouter des champs au tableau croisé dynamique.
  • PivotFields : PivotFields est une collection qui contient tous les champs d’un tableau croisé dynamique.
  • Orientation : Orientation définit l’orientation du champ dans le tableau croisé dynamique (xlRowField pour une ligne, xlColumnField pour une colonne, xlPageField pour une page et xlDataField pour des données).
  • Position : Position définit l’ordre d’affichage du champ dans le tableau croisé dynamique.
  • Function : Function définit la fonction à utiliser pour calculer les données du champ (xlSum pour la somme, xlCount pour le nombre, etc.).
  • Name : Name définit le nom du champ dans le tableau croisé dynamique.
  • NumberFormat : NumberFormat définit le format des données du champ dans le tableau croisé dynamique.

9. Ajuster le format du tableau croisé dynamique

Pour ajuster le format du tableau croisé dynamique, vous pouvez utiliser les propriétés TableStyle et ColumnGrand :

 With PvtTable .TableStyle = "TableStyleLight9" .ColumnGrand = False .RowGrand = False End With 

Où :

  • TableStyle : TableStyle définit le style du tableau croisé dynamique.
  • ColumnGrand : ColumnGrand définit si les totaux de colonne sont affichés ou non. RowGrand : RowGrand définit si les totaux de ligne sont affichés ou non.

10. Afficher les valeurs en tant que pourcentage

Vous pouvez afficher les valeurs en tant que pourcentage en utilisant la méthode ShowAsCalculation :

 With PvtTable.PivotFields("Total des ventes") .ShowAsCalculation = xlPercentOfTotal .NumberFormat = "0.00%" End With 

Où :

  • ShowAsCalculation : ShowAsCalculation définit comment les données du champ sont calculées et affichées (xlPercentOfTotal pour afficher les valeurs en tant que pourcentage du total).

11. Trier les données

Vous pouvez trier les données en utilisant la méthode AutoSort :

 With PvtTable.PivotFields("Région") .AutoSort xlDescending, "Total des ventes" End With 

Où :

  • AutoSort : AutoSort est une méthode qui permet de trier les données d’un champ.
  • xlDescending : xlDescending définit l’ordre de tri (décroissant).

12. Masquer les champs inutilisés

Vous pouvez masquer les champs inutilisés en utilisant la propriété ShowAllItems :

 PvtTable.ShowAllItems = False 

13. Exécuter le code

Pour exécuter le code, appuyez sur F5 ou cliquez sur l’icône Exécuter du ruban. Le tableau croisé dynamique sera automatiquement créé et mis en forme :

Tableau croisé dynamique créé et mis en forme par le script VBA dans Excel

Vous pouvez également exécuter le code en cliquant sur la forme que vous avez assignée à la macro au début de cet article.

Et voilà ! Vous savez maintenant comment créer et mettre en forme automatiquement un tableau croisé dynamique dans MS Excel en utilisant VBA.

Vous en avez assez de passer des heures à effectuer des tâches répétitives ? Faites appel à notre service de prestation VBA

Vous avez vu à quel point il est facile de créer et de mettre en forme automatiquement un tableau croisé dynamique dans MS Excel grâce à VBA ? Imaginez ce que notre équipe de professionnels peut accomplir pour votre entreprise !

Notre site de prestation de service VBA propose des solutions sur mesure pour automatiser vos tâches répétitives et vous faire gagner du temps et de l’efficacité. Nous proposons également des formations pour vous apprendre à utiliser VBA de manière autonome.

N’hésitez pas à nous contacter pour en savoir plus et inscrivez-vous dès maintenant pour recevoir nos offres et actualités. Remplissez simplement le formulaire ci-dessous pour vous inscrire :

Vbalabs

Bonjour, je m'appelle Victorien, j'ai 24 ans et je suis Ingénieur / Expert de Microsoft Excel. Passionné par les données et l'analyse, j'aime créer des solutions efficaces et innovantes pour résoudre des problèmes complexes. Vous pouvez me faire confiance pour vos besoins en matière de gestion et d'analyse de données avec Excel. Je suis là pour vous aider à tirer le meilleur parti de cet outil puissant.

Laisser un commentaire