Comment mettre à jour automatiquement une plage de tableau croisé dynamique ?

La mise à jour d’un tableau croisé dynamique est pénible, n’est-ce pas ? Si vous utilisez fréquemment des tableaux croisés dynamiques dans votre travail, je suis sûr que vous pouvez comprendre.

Le fait est qu’à chaque fois que vous ajoutez de nouvelles données à la feuille source, vous devez mettre à jour la plage source du tableau croisé dynamique avant d’actualiser votre tableau croisé dynamique.

Imaginez maintenant que si vous ajoutez des données à votre feuille source tous les jours, vous devez mettre à jour la plage source tous les jours.

Et chaque fois que changer de plage de tableau croisé dynamique est un gâchis. Oui, c’est vrai, plus vous ajoutez de données fréquemment, plus vous devez mettre à jour la plage source.

Le fait est donc que vous avez besoin d’une méthode pour mettre à jour automatiquement la plage source lorsque vous ajoutez de nouvelles données.

REMARQUE : Les tableaux croisés dynamiques font partie des COMPÉTENCES EXCEL INTERMÉDIAIRES .

Appliquer le tableau pour avoir une plage de tableau croisé dynamique à mise à jour automatique

Il y a quelques jours, j’ai interrogé John Michaloudis sur son conseil de tableau croisé dynamique d’un million de dollars. Il dit : Mettez vos données sources dans un tableau. Croyez-moi, c’est un conseil à un million de dollars.

En appliquant un tableau dans les données source, vous n’avez pas besoin de modifier encore et encore la plage source de votre tableau croisé dynamique.

Chaque fois que vous ajoutez de nouvelles données, il met automatiquement à jour la plage du tableau croisé dynamique.

Convertir des données en tableau avant de créer un tableau croisé dynamique

Chaque fois avant de créer un tableau croisé dynamique, assurez-vous d’appliquer le tableau aux données source en suivant les étapes suivantes.

1. Sélectionnez l’une des cellules de vos données.
2. Utilisez la touche de raccourci Ctrl + T ou accédez à → Insérer un onglet → Tableaux → Tableau.
3. Vous obtiendrez une fenêtre contextuelle avec votre plage de données actuelle.
   
4. Cliquez sur OK.
5. Maintenant, pour créer un tableau croisé dynamique, sélectionnez n’importe quelle cellule de vos données. Accédez à → onglet Conception → Outils → Résumer avec un tableau croisé dynamique.
   
6. Cliquez sur OK.

Désormais, chaque fois que vous ajoutez de nouvelles données à votre feuille de données, la plage du tableau croisé dynamique est automatiquement mise à jour et il vous suffit d’actualiser votre tableau croisé dynamique.

Convertir des données en tableau après avoir créé un tableau croisé dynamique

Si vous avez déjà un tableau croisé dynamique dans votre feuille de calcul, vous pouvez utiliser les étapes suivantes pour convertir votre source de données en tableau.

1. Sélectionnez l’une des cellules de votre source de données.
2. Utilisez la touche de raccourci Ctrl + T ou accédez à → Insérer un onglet → Tableaux → Tableau.
3. Vous obtiendrez une fenêtre contextuelle avec votre plage de données actuelle.
4. Cliquez sur OK.
5. Maintenant, sélectionnez l’une des cellules de votre tableau croisé dynamique et accédez à → Analyser → Données → Modifier la source de données → Modifier la source de données (menu déroulant).
6. Vous obtiendrez une fenêtre contextuelle pour sélectionner à nouveau votre source de données ou vous pouvez également entrer le nom de la table dans l’entrée de plage.
   
7. Cliquez sur OK.

À partir de maintenant, chaque fois que vous ajouterez de nouvelles données dans votre feuille source, la plage du tableau croisé dynamique augmentera pour la mettre à jour automatiquement.

Créer une plage de tableau croisé dynamique dynamique avec la fonction OFFSET

L’autre meilleur moyen de mettre à jour automatiquement la plage du tableau croisé dynamique consiste à utiliser une plage dynamique.

La plage dynamique peut s’étendre automatiquement chaque fois que vous ajoutez de nouvelles données à votre feuille source. Voici les étapes pour créer une plage dynamique.

1. Accédez à → Onglet Formules → Noms définis → Gestionnaire de noms.
2. Une fois que vous avez cliqué sur le gestionnaire de noms, vous obtiendrez une fenêtre contextuelle.
   
3. Dans la fenêtre de votre gestionnaire de noms, cliquez sur nouveau pour créer une plage nommée.
4. Dans votre nouvelle fenêtre de nom, entrez
   1. Un nom pour votre nouvelle gamme. J’utilise le nom « SourceData ».
   2. Spécifiez l’étendue de la plage. Vous pouvez spécifier entre la feuille de calcul ou le classeur en cours.
   3. Ajoutez un commentaire pour décrire votre plage nommée. Entrez la formule ci-dessous dans la barre de saisie « Se référer à ».
      

      =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. À la fin, cliquez sur OK.

Maintenant, vous disposez d’une plage dynamique pour créer un tableau croisé dynamique.

Tout ce que vous avez à faire est simplement de créer un tableau croisé dynamique avec vos données source et ensuite de changer la source avec la plage nommée en utilisant la même méthode que celle que j’ai utilisée dans la première méthode de tables.

Une fois que vous avez ajouté de nouvelles données à votre feuille source, actualisez simplement votre tableau croisé dynamique.

Comment fonctionne cette formule ?

Dans la formule ci-dessus, j’ai utilisé la fonction de décalage pour créer une plage dynamique.

J’ai mentionné la cellule A1 comme point de départ, puis sans mentionner les lignes et les colonnes, j’ai spécifié la hauteur et la largeur de la plage en utilisant COUNTA.

COUNTA comptera les cellules avec les valeurs de la colonne A et de la ligne 1 et indiquera à offset d’étendre sa hauteur et sa largeur en conséquence.

La seule chose que vous devez faire attention, c’est qu’il n’y ait pas de cellule vide entre la colonne A et la ligne 1.

Mettre à jour le tableau croisé dynamique à l’aide d’un code VBA

La plupart des gens aiment utiliser les codes VBA . Voici donc le code à utiliser pour mettre à jour la plage du tableau croisé dynamique avec VBA.

Sub UpdatePivotTableRange()

Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long

'Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")
Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

'Enter in Pivot Table Name
PivotName = "PivotTable2"

'Defining Staring Point & Dynamic Range
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

'Change Pivot Table Data Source Range Address
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
Pivot_Sheet.PivotTables(PivotName).RefreshTable

'Complete Message
Pivot_Sheet.Activate
MsgBox "Your Pivot Table is now updated."

End Sub

Choses que vous devez changer avant de l’utiliser dans votre classeur.

1. Ligne13 : modifiez le nom de la feuille de calcul source.
2. Ligne14 : Modifiez le nom de la feuille de tableau croisé dynamique.
3. Ligne17 : Modifiez le nom du tableau croisé dynamique.

Si vous rencontrez toujours un problème lors de l’utilisation de ce code, veuillez m’écrire dans la zone de commentaire. Maintenant, laissez-moi vous montrer comment ce code fonctionne afin que vous puissiez facilement le modifier selon vos besoins.

Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")
Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

Dans la partie ci-dessus du code, nous avons spécifié les variables du tableau croisé dynamique et de la feuille de calcul des données source. Vous pouvez modifier le nom de la feuille de calcul à partir d’ici.

PivotName = "PivotTable2"

Dans la partie ci-dessus du code, entrez le nom du tableau croisé dynamique sur lequel vous souhaitez utiliser ce code.

Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

La partie ci-dessus du code créera une plage dynamique en utilisant la cellule A1 de la feuille de calcul de la source de données.

Il vérifiera la dernière colonne et la dernière ligne avec des données pour créer une plage dynamique. Chaque fois que vous exécuterez cette macro, elle créera une nouvelle plage dynamique.

'Ensure Pivot Table is Refreshed
 Pivot_Sheet.PivotTables(PivotName).RefreshTable
 
'Complete Message
 Pivot_Sheet.Activate
 MsgBox "Your Pivot Table is now updated."

La partie ci-dessus du code actualisera le tableau croisé dynamique et affichera un message à l’utilisateur indiquant que le tableau croisé dynamique est maintenant mis à jour.