Comment mettre à jour dynamiquement Tableau croisé dynamique source de données dans Excel Range
Actuellement, nous pouvons modifier ou mettre à jour dynamiquement les tableaux croisés dynamiques en utilisant link: / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables] ou `link: / excel-range-name-dynamic- named-ranges-in-excel [Plages nommées dynamiques]. Mais ces techniques ne sont pas infaillibles. Comme vous devrez toujours actualiser le tableau croisé dynamique manuellement. Si vous avez des données volumineuses contenant des milliers de lignes et de colonnes, les tableaux Excel ne vous aideront pas beaucoup. Au lieu de cela, cela alourdira votre fichier. Donc, le seul moyen qui reste est VBA.
Dans cet article, nous allons apprendre comment nous pouvons faire en sorte que notre tableau croisé dynamique modifie automatiquement la source de données. En d’autres termes, nous automatiserons le processus manuel de changement de source de données pour inclure dynamiquement de nouvelles lignes et colonnes ajoutées aux tables source et refléter instantanément le changement dans le tableau croisé dynamique.
Ecrire du code dans la feuille de données source Puisque nous voulons que cela soit complètement automatique, nous utiliserons des modules de feuille pour écrire du code au lieu d’un module de base. Cela nous permettra d’utiliser //events-in-vba/the-worksheet-events-in-excel-vba.html[événements de feuille de travail] `.
Si les données source et les tableaux croisés dynamiques se trouvent dans des feuilles différentes, nous écrirons le code VBA pour modifier la source de données du tableau croisé dynamique dans l’objet de feuille qui contient les données source (pas celui qui contient le tableau croisé dynamique). Appuyez sur CTRL + F11 pour ouvrir l’éditeur VB. Accédez à présent à l’explorateur de projet et recherchez la feuille contenant les données source. Double-cliquez dessus.
Une nouvelle zone de codage s’ouvrira. Vous ne verrez peut-être aucun changement, mais vous avez maintenant accès aux événements de la feuille de calcul.
Cliquez sur le menu déroulant de gauche et sélectionnez la feuille de calcul. Dans le menu déroulant de gauche, sélectionnez désactiver. Vous verrez un sous vide écrit sur le nom de la zone de code worksheet_deativate. Notre code pour modifier dynamiquement les données sources et actualiser le tableau croisé dynamique ira dans ce bloc de code. Ce code s’exécutera chaque fois que vous passerez de la feuille de données à une autre feuille. Vous pouvez lire sur tous les événements de feuille de calcul link: / events-in-vba-the-worksheet-events-in-excel-vba [here].
Nous sommes maintenant prêts à implémenter le code.
Code source pour mettre à jour le tableau croisé dynamique dynamiquement avec une nouvelle plage Pour expliquer comment cela fonctionne, j’ai un classeur. Ce classeur contient deux feuilles. Sheet1 contient les données source qui peuvent changer. Sheet2 contient le tableau croisé dynamique qui dépend des données source de sheet2.
Maintenant, j’ai écrit ce code dans la zone de codage de sheet1. J’utilise l’événement Worksheet_Deactivate, afin que ce code s’exécute pour mettre à jour le tableau croisé dynamique chaque fois que nous passons de la feuille de données source.
Private Sub Worksheet_Deactivate()
Dim pt As PivotTable
Dim pc As PivotCache
Dim source_data As Range
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
lstcol = Cells(1, Columns.Count).End(xlToLeft).Column
Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Set pt = Sheet2.PivotTables("PivotTable1")
pt.ChangePivotCache pc
End Sub
Si vous avez un classeur similaire, vous pouvez directement copier ces données.J’ai expliqué que ce code fonctionne ci-dessous.
Vous pouvez voir l’effet de ce code en gif ci-dessous.
Comment ce code modifie-t-il automatiquement les données sources et met-il à jour les tableaux croisés dynamiques? Tout d’abord, nous avons utilisé un événement worksheet_deactivate. Cet événement se déclenche uniquement lorsque la feuille contenant le code est commutée ou désactivée. Voici donc comment le code s’exécute automatiquement.
Maintenant, pour changer les données source du tableau croisé dynamique, nous changeons les données dans le cache pivot. Un tableau croisé dynamique est créé à l’aide du cache pivot. Le cache du pivot contient les anciennes données source jusqu’à ce que le tableau croisé dynamique ne soit pas actualisé manuellement ou que la plage de données source soit modifiée manuellement.
Nous avons créé des références de tableaux croisés dynamiques nom pt, cache pivot nommé pc et une plage nommée source_data. Les données source contiendront toutes les données. Pour obtenir dynamiquement la table entière comme plage de données, nous déterminons la dernière ligne et la dernière colonne.
lstrow = Cellules (Rows.Count, 1) .End (xlUp) .Row
lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column
En utilisant ces deux nombres, nous définissons la source_data. Nous sommes convaincus que la plage de données source commencera toujours à partir de A1.
Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))
Maintenant, nous avons les données sources qui sont dynamiques. Nous devons juste l’utiliser dans le tableau croisé dynamique.
Nous stockons ces données dans le cache pivot car nous savons que le cache pivot stocke toutes les données.
Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Ensuite, nous définissons le tableau croisé dynamique que nous voulons mettre à jour. Puisque nous voulons mettre à jour le tableau croisé dynamique1 (nom du tableau croisé dynamique. Vous pouvez vérifier le nom du tableau croisé dynamique dans l’onglet d’analyse tout en sélectionnant le tableau croisé dynamique.) Sur la feuille Sheet1, nous définissons pt comme indiqué ci-dessous.
Set pt = Sheet2.PivotTables (« PivotTable1 »)
Maintenant, nous utilisons simplement ce cache pivot pour mettre à jour le tableau croisé dynamique. Nous utilisons la méthode changePivotCache de l’objet pt.
pt.ChangePivotCache pc
Et nous avons automatisé notre tableau croisé dynamique. Cela mettra automatiquement à jour votre tableau croisé dynamique. Si vous avez plusieurs tables avec la même source de données, utilisez simplement le même cache dans chaque objet de tableau croisé dynamique.
Alors oui les gars, voici comment vous pouvez modifier dynamiquement la plage de sources de données dans Excel. J’espère avoir été suffisamment explicatif. Si vous avez des questions concernant cet article, faites-le moi savoir dans la section commentaires ci-dessous.
Articles liés:
link: / custom-functions-in-vba-how-to-auto-refresh-pivot-tables-using-vba-excel [Comment actualiser automatiquement les tableaux croisés dynamiques à l’aide de VBA]: Pour actualiser automatiquement vos tableaux croisés dynamiques, vous pouvez utiliser Événements VBA. Utilisez cette simple ligne de code pour mettre à jour automatiquement votre tableau croisé dynamique. Vous pouvez utiliser l’une des 3 méthodes d’actualisation automatique des tableaux croisés dynamiques.
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Exécuter une macro si un changement est effectué sur la feuille dans la plage spécifiée]: * Dans vos pratiques VBA, vous obtenir la nécessité d’exécuter des macros lorsqu’une certaine plage ou cellule change. Dans ce cas, pour exécuter des macros lorsqu’une modification est apportée à une plage cible, nous utilisons l’événement change.
lien: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Exécuter la macro quand une modification est faite sur la feuille] | Donc, pour exécuter votre macro chaque fois que la feuille est mise à jour, nous utilisons les événements de feuille de travail de VBA.
lien: / events-in-vba-simple-vba-code-to-highlight-current-row-and-column-using [code VBA le plus simple pour mettre en évidence la ligne et la colonne actuelles en utilisant] | Utilisez ce petit extrait de code VBA pour mettre en évidence la ligne et la colonne actuelles de la feuille.
lien: / events-in-vba-the-worksheet-events-in-excel-vba [The Worksheet Events in Excel VBA] | Les événements de feuille de calcul sont vraiment utiles lorsque vous souhaitez que vos macros s’exécutent lorsqu’un événement spécifié se produit sur la feuille.
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité] | Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.link: / formulas-and-functions-introduction-of-vlookup-function [La fonction RECHERCHEV dans Excel] `| C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs à partir de différentes plages et feuilles. `lien: / tips-countif-in-microsoft-excel [COUNTIF dans Excel 2016] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous n’avez pas besoin de filtrer vos données pour compter une valeur spécifique.
La fonction Countif est indispensable pour préparer votre tableau de bord.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel] | C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.