Cache Pivot dans Excel - Qu'est-ce que c'est et comment l'utiliser au mieux

Si vous travaillez avec des tableaux croisés dynamiques Excel, le cache croisé dynamique est quelque chose que vous devez absolument connaître.

Qu'est-ce que le cache de pivot ?

Le cache croisé dynamique est quelque chose qui est généré automatiquement lorsque vous créez un tableau croisé dynamique.

C'est un objet qui contient une réplique de la source de données. Bien que vous ne puissiez pas le voir, il fait partie du classeur et est connecté au tableau croisé dynamique. Lorsque vous apportez des modifications au tableau croisé dynamique, il n'utilise pas la source de données, mais plutôt le cache croisé dynamique.

La raison pour laquelle un cache pivot est généré est d'optimiser le fonctionnement du tableau croisé dynamique. Même lorsque vous avez des milliers de lignes de données, un tableau croisé dynamique est très rapide pour le résumer. Vous pouvez faire glisser et déposer des éléments dans les zones lignes/colonnes/valeurs/filtres et les résultats seront instantanément mis à jour.

Pivot Cache permet ce fonctionnement rapide d'un tableau croisé dynamique.

Alors que vous pensez être directement lié aux données sources, en réalité, vous accédez au cache pivot (et non aux données sources) lorsque vous effectuez des modifications dans le tableau croisé dynamique.

C'est également la raison pour laquelle vous devez actualiser le tableau croisé dynamique pour refléter les modifications apportées à l'ensemble de données.

Effets secondaires du cache de pivot

Un inconvénient du cache de pivot est qu'il augmente la taille de votre classeur.

Puisqu'il s'agit d'une réplique des données source, lorsque vous créez un tableau croisé dynamique, une copie de ces données est stockée dans le cache pivot.

Lorsque vous utilisez des ensembles de données volumineux pour créer un tableau croisé dynamique, la taille du fichier de classeur augmente considérablement.

Partage du cache pivot

À partir d'Excel 2007, si vous disposez déjà d'un tableau croisé dynamique et que vous créez un tableau croisé dynamique supplémentaire en utilisant les mêmes données source, Excel partage automatiquement le cache pivot (ce qui signifie que les deux tableaux croisés dynamiques utilisent le même cache pivot). Ceci est utile car cela évite la duplication du cache pivot et, à son tour, réduit l'utilisation de la mémoire et la taille du fichier.

Limitations du cache de pivot partagé

Bien qu'un cache pivot partagé améliore le fonctionnement du tableau croisé dynamique et l'utilisation de la mémoire, il souffre des limitations suivantes :

• Lorsque vous actualisez un tableau croisé dynamique, tous les tableaux croisés dynamiques liés au même cache sont actualisés.
• Lorsque vous regroupez des champs dans l'un des tableaux croisés dynamiques, il est appliqué à tous les tableaux croisés dynamiques utilisant le même cache de pivot. Par exemple, si vous regroupez les dates par mois, ce changement sera reflété dans tous les tableaux croisés dynamiques.
• Lorsque vous insérez un champ/élément calculé dans l'un des tableaux croisés dynamiques, il apparaît dans tous les tableaux croisés dynamiques qui partagent le cache de pivot.

Le moyen de contourner ces limitations consiste à forcer Excel à créer un cache de pivot séparé pour différents tableaux croisés dynamiques (tout en utilisant la même source de données).

Remarque : si vous utilisez différentes sources de données pour différents tableaux croisés dynamiques, Excel générera automatiquement des caches croisés dynamiques distincts pour celui-ci.

Création d'un cache de pivot en double (avec la même source de données)

Voici 3 façons de créer un cache de pivot en double tout en créant des tableaux croisés dynamiques à partir de la même source de données :

#1 Utiliser des noms de table différents

• Cliquez n'importe où dans la source de données et accédez à Insérer -> Tableau (ou vous pouvez utiliser le raccourci clavier - Ctrl + T).
• Dans la boîte de dialogue Créer une table, cliquez sur OK. Il va créer une table avec le nom Table1.
• Avec n'importe quelle cellule sélectionnée dans le tableau, accédez à Insertion -> Tableau croisé dynamique.
• Dans la boîte de dialogue Créer un tableau croisé dynamique, vous remarquerez que dans le champ Table/Plage se trouve le nom de la table. Cliquez sur OK.
  • Cela créera le premier tableau croisé dynamique.
• Accédez à la source de données (table), sélectionnez n'importe quelle cellule et accédez à Conception des outils de table -> Outils -> Convertir en plage. Il affichera une invite vous demandant si vous souhaitez convertir le tableau en plage normale. Cliquez sur Oui. Cela convertira le tableau en données tabulaires régulières.

Répétez maintenant les étapes ci-dessus et modifiez simplement le nom de la table (de Table1 à Table2 ou ce que vous voulez). Vous pouvez le modifier en saisissant le nom dans le champ sous Nom de la table dans l'onglet Conception des outils de table.

Bien que les deux tables (Table1 et Table2) fassent référence à la même source de données, cette méthode garantit que deux caches de pivot distincts sont générés pour chaque table.

#2 Utilisation de l'ancien assistant de tableau croisé dynamique

Utilisez ces étapes lorsque vous souhaitez créer un tableau croisé dynamique supplémentaire avec un cache de pivot distinct tout en utilisant la même source de données.

• Sélectionnez n'importe quelle cellule dans les données et appuyez sur ALT + D + P.
  • Cela ouvrira l'assistant de tableau croisé dynamique et de graphique croisé dynamique.
• À l'étape 1 sur 3, cliquez sur Suivant.
• À l'étape 2 sur 3, assurez-vous que la plage de données est correcte et cliquez sur Suivant.
• Excel affiche une invite qui dit essentiellement de cliquer sur Oui pour créer un cache de pivot partagé et Non pour créer un cache de pivot séparé.
• Cliquez sur Non.
• À l'étape 3 de l'assistant, sélectionnez si vous voulez le tableau croisé dynamique dans une nouvelle feuille de calcul ou la même feuille de calcul, puis cliquez sur Terminer.

Remarque : Assurez-vous que les données ne sont pas un tableau Excel.

Compter le nombre de caches pivots

Vous voudrez peut-être compter le nombre de caches de pivot juste pour éviter plusieurs caches de pivot provenant de la même source de données.

Voici un moyen rapide de le compter :

• Appuyez sur ALT + F11 pour ouvrir l'éditeur VB (ou accédez à l'onglet Développeur -> Visual Basic).
• Dans le menu Visual Basic Editor, cliquez sur Afficher et sélectionnez Fenêtre immédiate (ou appuyez sur Ctrl + G). Cela rendra la fenêtre d'exécution visible.
• Dans la fenêtre Exécution, collez le code suivant et appuyez sur Entrée :

  ?ActiveWorkbook.PivotCaches.Count

Il affichera instantanément le nombre de caches de pivot dans le classeur.

Amélioration des performances lors de l'utilisation des tableaux croisés dynamiques

Vous pouvez faire plusieurs choses pour améliorer les performances des classeurs (taille du fichier et utilisation de la mémoire) lorsque vous travaillez avec des tableaux croisés dynamiques :

#1 Supprimer les données sources

Vous pouvez supprimer les données source et utiliser uniquement le cache de pivot. Vous pourrez toujours tout faire en utilisant le cache pivot car il contient un instantané des données d'origine. Mais puisque vous avez supprimé les données source, la taille de votre fichier de classeur serait réduite.

Si vous souhaitez récupérer les données source, double-cliquez simplement sur l'intersection des totaux généraux pour ce tableau croisé dynamique. Il créera une nouvelle feuille de calcul et affichera toutes les données utilisées pour créer ce tableau croisé dynamique.

#2 Ne pas enregistrer les données dans le cache de pivot

Lorsque vous enregistrez un fichier avec un tableau croisé dynamique et des données source, il enregistre également le cache de pivot qui contient une copie des données source. Cela signifie que vous enregistrez les données source à deux endroits : dans la feuille de calcul contenant les données et dans le cache de pivot.

Il existe une option pour ne pas enregistrer les données dans le cache et le fermer. Cela entraînera une taille de fichier inférieure.

Pour faire ça:

• Sélectionnez n'importe quelle cellule du tableau croisé dynamique.
• Allez dans Analyser -> Tableau croisé dynamique -> Options.
• Dans la boîte de dialogue Options du tableau croisé dynamique, accédez à l'onglet Données.
• Décochez l'option - Enregistrer les données source avec un fichier.
• Cochez l'option - Actualiser les données lors de l'ouverture du fichier.
  • Si vous ne cochez pas cette option, lorsque vous ouvrez le classeur Excel, il ne rafraîchira pas les données et vous ne pourrez pas utiliser les fonctionnalités du tableau croisé dynamique. Pour que cela fonctionne, vous devrez actualiser manuellement le tableau croisé dynamique.

Lorsque vous faites cela, Excel n'enregistrera pas les données dans le cache pivot, mais il les actualisera lorsque vous ouvrirez le classeur Excel la prochaine fois. Vos données peuvent se trouver dans le même classeur, un autre classeur ou une base de données externe. Lorsque vous ouvrez le fichier, il actualise les données et le cache de pivot est recréé.

Bien que cela puisse entraîner une réduction de la taille du fichier, l'ouverture du fichier peut prendre un peu plus de temps (car Excel recrée le cache).

Voir également: Enregistrement des données source avec le tableau croisé dynamique.

Remarque : Si vous utilisez cette option, assurez-vous que la source de données est intacte. Si vous supprimez les données source (du classeur ou de toute source de données externe), vous ne pourrez pas recréer le cache pivot.

#3 Partage du cache pivot pour de meilleures performances

Si par accident (ou intentionnellement) vous vous retrouvez dans une situation où vous avez un cache de pivot en double et que vous souhaitez supprimer le doublon et partager le cache de pivot, voici les étapes à suivre :

• Supprimez l'un des tableaux croisés dynamiques dont vous souhaitez supprimer le cache. Pour ce faire, sélectionnez le tableau croisé dynamique et accédez à Accueil -> Effacer -> Effacer tout.
• Maintenant, copiez simplement le tableau croisé dynamique que vous souhaitez dupliquer et collez-le (soit dans la même feuille de calcul, soit dans une feuille de calcul distincte).
  • Il est recommandé de le coller dans des feuilles de calcul distinctes afin qu'il ne chevauche pas l'autre tableau croisé dynamique lorsque vous le développez. Cependant, je le copie parfois côte à côte pour comparer différentes vues. Ce copier-coller du tableau croisé dynamique permet de s'assurer que le cache de pivot est partagé.

• Aide Microsoft - Annuler le partage d'un cache de données entre les rapports de tableau croisé dynamique.

Autres didacticiels de tableau croisé dynamique que vous pourriez aimer :

• Préparation des données source pour le tableau croisé dynamique.
• Comment regrouper des dates dans des tableaux croisés dynamiques dans Excel.
• Comment regrouper des nombres dans un tableau croisé dynamique dans Excel.
• Comment actualiser le tableau croisé dynamique dans Excel.
• Utilisation des trancheurs dans le tableau croisé dynamique Excel.
• Comment ajouter et utiliser un champ calculé de tableau croisé dynamique Excel
• Comment appliquer une mise en forme conditionnelle dans un tableau croisé dynamique dans Excel