Tableau croisé dynamique Excel VBA

Le tableau croisé dynamique VBA vous aide à résumer les rapports d'un grand ensemble de données. Pivot peut être considéré comme une minuscule forme de l'ensemble de données. La vue rapide d'un grand ensemble de données est possible grâce à un tableau croisé dynamique. Un tableau croisé dynamique est un moyen simple de filtrer les données en conséquence. À partir des données disponibles, vous pouvez mettre en évidence les données comme vous le souhaitez. Un tableau croisé dynamique vous permet de combiner les énormes données d'analyser les données et de produire des rapports qui répondent à vos besoins commerciaux.

L'excel propose un tableau croisé dynamique intégré qui est facile à créer en appliquant un tableau croisé dynamique sur une fiche technique. Il est possible de générer un rapport automatiquement dans Excel une fois les données fournies. Les codes VBA vous aideront à créer un tableau croisé dynamique automatique.

Comment créer un tableau croisé dynamique à l'aide d'Excel VBA?

Les étapes courantes pour insérer un tableau croisé dynamique consistent à insérer d'abord un tableau croisé dynamique dans le menu Insertion, puis à sélectionner les tableaux que vous souhaitez transformer en tableau croisé dynamique. Le tableau sélectionné deviendra les données source et le tableau croisé dynamique sera créé en conséquence.

Pour créer un rapport pivot, vous devez sélectionner les champs des filtres, des valeurs, etc. De la même manière, ces étapes peuvent être automatisées à l'aide des codes VBA. Nous avons besoin d'une feuille de données Excel pour fonctionner avec un tableau croisé dynamique. Apprenons à créer un tableau croisé dynamique à travers les codes Excel VBA.

Vous pouvez télécharger ce modèle Excel de tableau croisé dynamique VBA ici - Modèle Excel de tableau croisé dynamique VBA

Étape 1 : le tableau croisé dynamique doit être considéré comme un objet de référence. Créez un objet pour le tableau croisé dynamique sur d'autres codes, cet objet sera utilisé pour pointer le tableau croisé dynamique. Nommez la variable pvtable. Déclarez une fonction et un objet de tableau croisé dynamique.

Code:

 Sub PivotTable () Dim pvtable As PivotTable End Sub

Étape 2: Pour conserver les fichiers de données, nous avons besoin d'un cache pivot pour déclarer la source des données. Avant de créer un tableau croisé dynamique, déclarez une variable de trésorerie pivot. Déclarez la variable pvcache pour le cache pivot.

Code:

 Dim pvcache As PivotCache

Étape 3: Les données se trouvent dans la feuille de calcul et doivent spécifier une plage pour atteindre la cellule dont vous avez besoin. Dans le tableau croisé dynamique, les données source sont réparties sous forme de lignes et de colonnes afin de pointer une plage particulière, nous avons besoin d'une variable. Définissez pvrange comme variable de plage.

Code:

 Dim pvrange As Range

Étape 4: Une feuille de calcul doit insérer le tableau croisé dynamique que vous devez créer. Déclarez une variable comme feuille de calcul. Définissez pvsheet comme feuille de calcul.

Code:

 Dim pvsheet As Worksheet

Étape 5: Vous avez besoin d'une variable similaire pour utiliser la feuille de données qui doit contenir les données que vous souhaitez tracer sous forme de tableau croisé dynamique. Ainsi, la variable de feuille de données est déclarée comme pdsheet.

Code:

 Dim pdsheet As Worksheet

Étape 6: Vous avez besoin de deux autres variables en tant que type de données long pour indiquer la dernière ligne et colonne utilisées pour la création du tableau croisé dynamique. Il peut s'agir de n'importe quelle ligne ou colonne, il existe donc des chances que le nombre de lignes et de colonnes dépasse la limite du type de données entier. Appelons-le plr et plc.

Code:

 Dim plr As Long Dim plc As Long

Étape 7: L'étape suivante consiste à supprimer s'il existe un tableau croisé dynamique déjà créé. Cela aidera à éviter les confusions sur la table sur laquelle les données source doivent être tracées. Supprimez donc la feuille de tableau croisé dynamique précédente et créez une nouvelle feuille pour insérer le tableau croisé dynamique.

Code:

 En cas d'erreur, reprendre Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Supprimer 'pour supprimer le tableau croisé dynamique existant dans la feuille de calcul Worksheets.Add After: = ActiveSheet' pour ajouter une nouvelle feuille de calcul ActiveSheet.Name = " pvsheet "'pour renommer la feuille de calcul en" pvsheet "en cas d'erreur GoTo 0

Étape 8: Attribuez une variable d'objet pour la feuille pivotante et la feuille de données respectivement aux variables pvsheet et pdsheet. Cela sera utilisé plus loin pour spécifier les feuilles de calcul.

Code:

 Set pvsheet = Worksheets ("pvsheet") Set pdsheet = Worksheets ("pdsheet")

Étape 9: Une fois les feuilles de calcul définies, l'élément suivant est la dernière ligne et colonne utilisée pour créer un rapport pivot. Recherchez la dernière ligne et colonne utilisée à l'aide des variables déclarées plr et plc.

Code:

 'deux variables pour trouver la dernière ligne et colonne utilisées dans pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column

Étape 10: Comme nous l'avons mentionné précédemment, les données de la feuille de calcul sont spécifiées par la plage de cellules. Vous devez définir la plage de pivot à l'étape suivante. Il est déjà déclaré comme variable pour utiliser la plage de pivot «pvrange».

Code:

 'initialisation de la plage de données du tableau croisé dynamique Définir pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)

Étant donné que la plage est définie à l'aide de la propriété resize des plages de cellules, elle redimensionnera la plage de valeurs ajuste la plage de pivot en conséquence. Ainsi, le pvrange s'ajustera en cas d'ajout ou de suppression de lignes ou de colonnes.

Étape 11: Il est temps de définir le cache pivot qui est la source du tableau croisé dynamique. Utilisez l'objet pvcache pour définir le cache source.

Code:

 'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)

Étape 12: Ici, le rapport des ventes pour différents produits sera converti en tableau croisé dynamique grâce à cela. Créez un tableau croisé dynamique vide auquel vous pouvez ajouter l'ensemble de données plus loin.

Code:

 'nouveau tableau croisé dynamique vierge Définir pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")

Étape 13: Une fois le pivot inséré, vous devez spécifier les différents champs que vous souhaitez insérer dans le tableau croisé dynamique. Insérez donc le champ de la première ligne. Ici, la première ligne commence par le produit.

Code:

 'Insérer un produit dans une ligne classée avec pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End With

Étape 14: La prochaine consiste à spécifier le deuxième champ que vous souhaitez insérer dans le tableau croisé dynamique. De la même manière, insérez la rue de champ de la deuxième ligne dans le tableau croisé dynamique.

Code:

 'Insérer la rue dans la ligne classée et position 2 avec pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End With

Étape 15: Insérez le champ suivant dans le tableau croisé dynamique et le suivant est une ville. Donnez le code pour insérer le champ ville.

Code:

 'Insérer une ville dans la colonne classée avec pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 Fin avec

Étape 16: Jusqu'à présent, les données insérées étaient de type texte. Nous devons maintenant insérer le nombre de ventes dans le tableau croisé dynamique.

Code:

 'Insérer la colonne Sales dans le champ de données With pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End With

Étape 17: Vous avez inséré les champs qui doivent créer un tableau croisé dynamique. Et le tableau croisé dynamique est presque terminé maintenant, vous pouvez définir le format du tableau croisé dynamique. Cela spécifiera le type de table à travers le style de table. La disposition de l'axe des lignes est également définie comme vous le souhaitez.

Code:

 'définit le format de tableau croisé dynamique pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"

Étape 18: Pour afficher les éléments de valeurs déposés sous forme de tableau, ajoutez le code ci-dessous en bas.

Code:

 'pour afficher le tableau croisé dynamique sous forme de tableau pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True

Étape 19: Appuyez sur le bouton Exécuter ou appuyez sur F5 pour exécuter le code. Cela produira un tableau croisé dynamique à partir de la feuille de source de données. Les données fournies dans les lignes et les colonnes seront modifiées dans le tableau croisé dynamique. Le tableau croisé dynamique sera visible sur la feuille de calcul du tableau croisé dynamique.

Vérifiez la sortie et vous pouvez voir que la source de données est convertie dans le tableau croisé dynamique comme ci-dessous, les colonnes mentionnées sont converties dans les formulaires de filtre.

Les champs de pivot sont visibles sur le côté droit. Vous pouvez apporter des modifications en fonction de vos besoins en matière de présentation des données.

Pour votre référence, j'ai donné le code ci-dessous.

Code:

 Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Worksheet Dim pdsheet As Worksheet Dim plr As Long Dim plc As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet ") .Supprimez 'pour supprimer le tableau croisé dynamique existant dans la feuille de calcul Worksheets.Add After: = ActiveSheet' pour ajouter une nouvelle feuille de calcul ActiveSheet.Name =" pvsheet "'pour renommer la feuille de calcul en" pvsheet "En cas d'erreur GoTo 0 Définissez pvsheet = Worksheets ("pvsheet") Définissez deux variables pdsheet = Worksheets ("pdsheet") pour rechercher la dernière ligne et colonne utilisées dans pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Cells (1, Columns.Count) .End (xlToLeft) .Column 'initializing pivot table range data Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nouveau tableau croisé dynamique vierge Définir pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Insert Product to Row Filed With pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End With' Insert Street to Ligne classée et position 2 avec pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Fin avec 'Insérer la ville dans la colonne classée avec pvsheet.PivotTables ("Sales_Report"). PivotFields (" Ville ") .Orientation = xlColumnField .Position = 1 Fin avec 'Insérer la colonne Ventes dans le champ de données Avec pvsheet.PivotTables (" Sales_Report "). format Tableau croisé dynamique pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" "pour afficher le tableau croisé dynamique sous la forme d'un tableau pvsheet.PivotTables (" Sales_RayAtLab_RayAxLab_RayAx_TayAx_Lay_Rayout_Lay_Rayout_Lay_Rayout_Lay_Rayout_Layout_Réservoir_liv. .DisplayAlerts = True Application.ScreenUpdating = True End Sub

Choses dont il faut se rappeler

  • Spécifiez la feuille source où les données doivent être fournies pour un tableau croisé dynamique.
  • La ligne et la colonne de départ doivent être mentionnées correctement.
  • Utilisez les objets nécessaires pour localiser le cache pivot, la plage, le début et la fin des lignes.
  • Organisez la source de données avec le format approprié car il s'agit du processus d'automatisation.

Articles recommandés

Ceci est un guide du tableau croisé dynamique VBA. Nous discutons ici comment créer un tableau croisé dynamique à l'aide de codes VBA dans Excel avec un exemple et un modèle Excel téléchargeable. Vous pouvez également consulter les articles suivants pour en savoir plus -

  1. Comment supprimer un tableau croisé dynamique dans Excel?
  2. Utilisation de VBA dans PowerPoint
  3. Tableau croisé dynamique avec plusieurs feuilles
  4. Comment créer un trancheur de tableau croisé dynamique?
  5. Actualiser le tableau croisé dynamique dans VBA
  6. VBA en cas de reprise d'erreur après (exemples)
  7. Mise à jour d'écran Excel VBA

Catégorie:

VBA