- C'est quoi un cache de données ?
- La situation classique lors de la création de plusieurs tableaux dynamiques
- Comment forcer la génération d'un nouveau cache à la création d'un nouveau tableau dynamique ?
- Cas d'utilisation d'un tableau structuré comme source de données
- Cas d'utilisation du modèle de données comme source de données
- Quelques utilitaires pour la gestion du cache de données (VBA)
La mise à jour des tableaux dynamiques peut sembler parfois mystérieuse. En mettant à jour un tableau, cela provoque aussi la mise à jour certains autres tableaux mais pas d'autres !
Ce curieux phénomène est lié à la notion de cache de données.
(Je ne trouve pas pertinent le terme "croisé" , je vais donc l'oublier 😊)
C'est quoi un cache de données ?
Une sorte de copie des données sur laquelle est basé réellement le tableau dynamique.
Cela explique pourquoi, en modifiant les données de départ, le tableau dynamique ne se met pas à jour. On n'a pas, ainsi, modifié les données que le tableau dynamique utilise réellement !
Via la commande "Actualiser", on va provoquer la mise à jour de cette copie et par effet 'domino' la mise à jour de tous les tableaux dynamiques qui utilisent cette copie.
La situation classique lors de la création de plusieurs tableaux dynamiques
En créant "normalement" les tableaux dynamiques à partir de la même plage, une seule copie (cache) est créée par Excel.
Ainsi tous les tableaux utilisent tous la même copie (les même données) et donc quand on en actualise un, les autres se mettront à jour en même temps.
Nombreuses contraintes générées pas cette organisation
- La mise à jour en cascade n'est peut-être pas souhaitée dans certain cas.
- Des outils comme les Champs calculés et les Éléments calculés ajoutent des données dans cette copie. Ces données supplémentaires sont donc disponibles pour tous les tableaux utilisant la même cache ce qui peut être intéressant mais peut aussi être gênant.
- On peut préciser l'ordre de calcul des Éléments calculés, ce qui peut en changer le résultat. Ce paramétrage concerne le cache et donc tous les tableaux liés ce qui là aussi peut être gênant.
- Les Éléments calculés sont incompatibles avec certaines fonctions de synthèse (Moyenne, Ecart-type, Ecart-typeP, Var et VarP) et inversement. Un tableau dynamique peut donc interdire l'utilisation des Éléments calculés ou de ces fonctions de synthèse dans les autres tableaux dynamiques liés au même cache.
- Excel affichera alors un étrange message d'erreur semblant complètement incompréhensible et illogique pour l'utilisateur (message variable suivant les versions et le sens de la manipulation) !
Exemple :
Je veux valider un Élément calculé dans ce tableau et voilà ce que j'ai comme erreur !
Mais pourquoi Excel m'indique des fonctions de synthèse du tableau alors que je valide un Élément calculé ? De plus mon tableau utilise la fonction Somme et non une des fonctions citées dans le message !
- Excel affichera alors un étrange message d'erreur semblant complètement incompréhensible et illogique pour l'utilisateur (message variable suivant les versions et le sens de la manipulation) !
Il faut deviner/savoir que dans ce classeur Excel, il existe quelque part un autre tableau dynamique partageant le même cache et qui lui, utilise une de ces fonctions incompatibles.
- Les Éléments calculés sont aussi incompatibles avec la présence de plusieurs champs en zone Valeur du tableau dynamique.
Exemple :
On veux valider un Élément calculé dans ce tableau et Excel m'affiche un étonnant message d'erreur !
Mais je n'ai qu'un champ dans CE tableau alors Excel est-il fou ?
(Il faut, là aussi, comprendre que c'est dans un autre tableau dynamique du classeur que l'on a bien plusieurs champs en zone Valeurs !)
- La fonctionnalité de groupement des données se fait aussi au niveau du cache de données donc on ne peut avoir des groupements différents pour un même champ dans différents tableaux.
Exemple : Je regroupe les salaires en tranche de 500 €, ce regroupement se fera sur tous les tableaux dynamiques (utilisant ce champ bien évidement et partageant le même cache).
Je m'arrête là !
La solution pour se libérer de ces contraintes
Utiliser un nouveau cache de donnée tous neuf !
Ce n'est pas une solution magique ! Elle comporte aussi ses propres inconvénients.
- Ce cache et les tableaux qui y sont liés ne s'actualiseront pas avec les autres sauf à utiliser la commande Actualiser tout.
- Les Champs calculés, les Éléments calculés, les groupements devront être refait si nécessaires.
- Le fichier Excel sera plus gros, prendra plus de place en mémoire vive et sera donc plus lent à se mettre à jour.
Exemple d'illustration avec un fichier ou l'on va utiliser environ 100 Ko de données environs.
| Contenus du fichier | Tailles en Ko | Différences |
|---|---|---|
| Vierge/Vide/sans données | 8 | |
| Avec les données du test | 109 | + 101 |
| Données et 1 tableau dynamique | 156 | + 47 |
| Données et 2 tableaux dynamiques liés (même cache) | 158 | + 2 |
| Données et 2 tableaux dynamiques liés (même cache) et un tableau non lié | 205 | + 47 |
On voit bien ici que les caches (environ 45 Ko), les tableaux (très simples d'environ 2 Ko) et l'évolution de la taille du fichier.
Comment forcer la génération d'un nouveau cache à la création d'un nouveau tableau dynamique ?
Méthode 1 : L'officielle
Il faut utiliser l'Assistant Tableau croisé dynamique (qui rappellera des souvenirs aux anciens utilisateurs d'Excel) qui n'est plus, par défaut, présent dans l'interface Excel.
On va ajouter cet outil à la barre d'outils Accès rapide par exemple ou à un ruban.
En validant à l'étape 2 de l'assistant la plage de donnée à utiliser, la fenêtre suivante apparait :
- Si on répond OUI, Excel nous demande de sélectionner le tableau dont on doit utiliser le cache de données.
À nous de deviner/savoir quels tableaux partagent le même cache (pas top) !
- Si on répond NON, Excel génèrera un nouveau cache pour ce tableau dynamique.
Méthode 2 : Celle que j'aimais bien utiliser
Elle me semble plus simple et permet de maitriser quel cache utiliser et ainsi savoir quels Éléments calculés/Champs calculés/Groupement seront disponibles ainsi que les conditions de mise à jour.
Pour cela, il suffit d'utiliser la notion de nommage des plages (ruban Formules) et d'utiliser ces noms pour générer les tableaux dynamiques.
- Les tableaux basés sur le même nom partageront le même cache (les même éléments calculés/champs calculés/groupements/mise à jour).
- Si on veut qu'Excel génère un nouveau cache, il suffit d'utiliser un nouveau nom.
- Les plages nommées permettent donc de facilement organiser l'utilisation des caches des tableaux.
Exemple de stratégie d'utilisation :
- Je génère les noms Groupe1, Etiquette1, Regroup500 basés sur la même plage (source de données).
- À partir de ces noms, je vais créer différents tableaux dynamiques.
- La plage Etiquette1 servira à faire les tableaux dynamiques avec des étiquettes.
- La plage Regroup500 servira à faire les tableaux dynamiques avec des regroupements par tranche de 500 sur un champ particulier.
- La plage Groupe1 servira à faire les autres tableaux dynamiques.
Rien ne m'empêchera par la suite de générer d'autre groupe comme Regroup1000 ou Groupe2 si nécessaire.
Cas d'utilisation d'un tableau structuré comme source de données
Quand on crée un tableau structuré (Ruban Insertion>Tableau), un nom lui est automatiquement affecté.
Ici la méthode 2 ne fonctionne plus ! Excel va s'entêter à remplacer le nouveau nom par le nom du tableau structuré après la validation du tableau dynamique (même en prenant bien soin que le nouveau nom référence bien la plage et non le nom du tableau structuré).
La méthode 1 fonctionne toujours (L'assistant ignore même les précédentes créations de tableaux dynamiques basés sur le nom du tableau structuré).
Cas d'utilisation du modèle de données comme source de données
Vu que dans ce cas on ne peut plus accéder aux éléments calculés, champs calculés et groupements, il n'y a pas vraiment de raison de générer un nouveau cache (on passe par PowerPivot pour obtenir des fonctionnalités équivalentes).
Remarque : En créant un nouveau tableau dynamique à partir de la plage et non du modèle de données, on se retrouve dans le même cas précédemment expliqué.
Quelques utilitaires pour la gestion du cache de données (VBA)
Identification du cache de données
Function CacheIndexTCD(rngTCD As Range) As Long
CacheIndexTCD = rngTCD.PivotTable.CacheIndex
End FunctionNuméros (Index) du cache du tableau dynamique en A3 : = CacheIndexTCD(A3)
Liste des caches et des tableaux/graphiques dynamiques liés du classeur actif
Génération d'une nouvelle feuille dans le classeur actif contenant des informations sur les tableaux/graphiques dynamiques.
Sub InfosCacheTCD()
' Objectif : Génération d'une nouvelle feuille dans le classeur actif contenant des informations
' sur les tableaux/graphiques dynamiques.
' Utilisation : Activer le classeur puis exécuter la macro.
' Retour : Une nouvelle feuille contenant les informations.
' Auteur : Arnaud (www.1forme.fr).
' Licence : CC-BY-NC-SA (Vous pouvez diffuser/partager/modifier cette macro dans les même conditions,
' seulement à titre personnel et citant l'auteur/site d'origine.
Dim objCache As PivotCache
Dim objFeuille ' La collection Sheets peut contenir des objets Chart ou Worksheet
Dim objTCD As PivotTable
Dim objGraphInteg As ChartObject
Dim objGraphFeuille As Chart
Dim tabResultat()
ReDim tabResultat(1 To ActiveWorkbook.PivotCaches.Count, 11)
TabQueryType = Array("1-Source de données ODBC", "2-Jeux d'enregistrement DAO", "3-Non référencé", "4-Page web", _
"5-Requête OLE DB", "6-Fichier texte", "7-Requête de jeux d'enregistrement ADO", "8-Non référencé")
TabSourceType = Array("1-Liste ou base de données Excel", "2-Données externes", _
"3-Plages de consolidation multiples", "4-Données de scénarios", _
"5-Même source qu'un autre rapport de tableau croisé dynamique", "6-Non référencé")
For Each objCache In ActiveWorkbook.PivotCaches
tabResultat(objCache.Index, 0) = objCache.Index
tabResultat(objCache.Index, 1) = objCache.RefreshDate
tabResultat(objCache.Index, 2) = objCache.RefreshName
On Error Resume Next
tabResultat(objCache.Index, 3) = TabQueryType(Application.Min(objCache.QueryType, 8) - 1)
If Not IsError(TryGetProperty(objCache, "Sql")) Then
tabResultat(objCache.Index, 4) = objCache.Sql
End If
If Not IsError(TryGetProperty(objCache, "SourceData")) Then
tabResultat(objCache.Index, 5) = objCache.SourceData
End If
If Not IsError(TryGetProperty(objCache, "CommandText")) Then
If objCache.CommandText = "Model" Then _
tabResultat(objCache.Index, 5) = tabResultat(objCache.Index, 5) & "Modèle de données"
End If
tabResultat(objCache.Index, 6) = objCache.SourceDataFile
On Error GoTo 0
tabResultat(objCache.Index, 7) = TabSourceType( _
IIf(objCache.SourceType = xlPivotTable, 5, Application.Min(objCache.SourceType, 6)) - 1)
Next
For Each objFeuille In ActiveWorkbook.Sheets
' La propriété PivotTables de l'objet Workbook ne retourne pas tous les objets PivotTable du
' classeur mais uniquement ceux associés à des graphiques croisés dynamiques découplés !
' Il faut parcourir les PivotTables des objets Worksheet.
If TypeName(objFeuille) = "Worksheet" Then
' Parcours des PivotTables de la feuille
For Each objTCD In objFeuille.PivotTables
tabResultat(objTCD.PivotCache.Index, 8) = tabResultat(objTCD.PivotCache.Index, 8) + 1
tabResultat(objTCD.PivotCache.Index, 9) = tabResultat(objTCD.PivotCache.Index, 9) & _
IIf(Len(tabResultat(objTCD.PivotCache.Index, 9)) <> 0, ", ", "") & _
objTCD.Name
Next objTCD
' Parcours des graphiques intégrés de la feuille
For Each objGraphInteg In objFeuille.ChartObjects
If Not objGraphInteg.Chart.PivotLayout Is Nothing Then
' C'est un GCD
tabResultat(objGraphInteg.Chart.PivotLayout.PivotCache.Index, 10) = tabResultat(objGraphInteg.Chart.PivotLayout.PivotCache.Index, 10) + 1
tabResultat(objGraphInteg.Chart.PivotLayout.PivotCache.Index, 11) = _
tabResultat(objGraphInteg.Chart.PivotLayout.PivotCache.Index, 11) & _
IIf(Len(tabResultat(objGraphInteg.Chart.PivotLayout.PivotCache.Index, 11)) <> 0, ", ", "") & _
objGraphInteg.Name & "(" & IIf(TypeName(objGraphInteg.Chart.PivotLayout.PivotTable.Parent) = "Workbook", _
"Graph non lié à TCD", objGraphInteg.Chart.PivotLayout.PivotTable.Name) & ")"
End If
Next objGraphInteg
ElseIf TypeName(objFeuille) = "Chart" Then
' Feuille graphique
If Not objFeuille.PivotLayout Is Nothing Then
tabResultat(objFeuille.PivotLayout.PivotCache.Index, 10) = tabResultat(objFeuille.PivotLayout.PivotCache.Index, 10) + 1
tabResultat(objFeuille.PivotLayout.PivotCache.Index, 11) = _
tabResultat(objFeuille.PivotLayout.PivotCache.Index, 11) & _
IIf(Len(tabResultat(objFeuille.PivotLayout.PivotCache.Index, 11)) <> 0, ", ", "") & _
objFeuille.Name & "(" & objFeuille.PivotLayout.PivotTable.Name & ")"
End If
End If
Next
Worksheets.Add
TabEtiquette = Array("N° Cache", "Date Maj", "Qui a maj", "Type de requête", "Requête", "Donnée source", _
"Fichier de données", "Type de source", "Nbre de TCD", "Noms des TCD", "Nbre de GCD", "Noms des GCD (TCD lié)")
[A1:L1] = TabEtiquette
Range("A2:L" & ActiveWorkbook.PivotCaches.Count + 1) = tabResultat
Columns("A:L").AutoFit
End Sub
Function TryGetProperty(obj As Object, strNomProp As String) As Variant
On Error GoTo GestionErreur
TryGetProperty = CallByName(obj, strNomProp, VbGet)
Exit Function
GestionErreur:
TryGetProperty = CVErr(3000) ' Retourner une erreur spécifique ou une valeur par défaut
End FunctionRésultat :
Création d'un nouveau cache pour un tableau dynamique existant
Attention : "Perte" des éléments calculés, champs calculés et groupements présent dans le précédent cache !
Sub RendreTCDIndependant()
' Objectif : Création d'un nouveau cache pour un tableau dynamique existant
' Utilisation : Sélectionner une cellule du tableau croisé dynamique puis exécuter la macro.
' Auteur : Arnaud (www.1forme.fr).
' Licence : CC-BY-NC-SA (Vous pouvez diffuser/partager/modifier cette macro dans les même conditions,
' seulement à titre personnel et citant l'auteur/site d'origine.
Dim objTCD As PivotTable
Dim tabSouceData
Dim strAdresseA1 As String
Dim objCache As PivotCache
Dim strSourceData As String
Set objTCD = ActiveCell.PivotTable
If objTCD Is Nothing Then
MsgBox "La cellule active n'est pas une cellule de tableau croisé dynamique !", vbCritical, "Exécution impossible"
Else
' Traitement de SourceData
tabSouceData = Split(objTCD.SourceData, "!") ' Séparer la feuille et la plage
' Convertir la plage en notation A1
strAdresseA1 = Application.ConvertFormula(Formula:=Replace(tabSouceData(1), "L", "R"), _
fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1)
' Retourner l'adresse complète avec le nom de la feuille
strAdresseA1 = tabSouceData(0) & "!" & strAdresseA1
Set objCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=ActiveSheet.Range(strAdresseA1))
objTCD.ChangePivotCache objCache ' C'est à ce moment que le cache est vraiment généré
objTCD.RefreshTable
MsgBox "Action réalisée", vbInformation, "Fin d'exécution"
End If
Set objTCD = Nothing
Set objCache = Nothing
End SubRemplacement du cache d'un tableau dynamique par celui d'un autre tableau dynamique
Sub ChangeTCDCache()
' Objectif : Affecter un cache existant à un tableau dynamique existant
' Utilisation : Sélectionner une cellule du tableau croisé dynamique puis exécuter la macro.
' Auteur : Arnaud (www.1forme.fr).
' Licence : CC-BY-NC-SA (Vous pouvez diffuser/partager/modifier cette macro dans les même conditions,
' seulement à titre personnel et citant l'auteur/site d'origine.
Dim varReponse As Variant
varReponse = InputBox("N° du cache données ou nom du tableau croisé dynamique" & _
" lié à utiliser :", "Changement de cache de données")
If varReponse <> "" Then
If Not IsNumeric(varReponse) Then
' Test d'existence (la collection ActiveWorkbook.PivotTables n'est pas utilisable, il
' faut trouver la feuille contenant le TCD
Dim objTCD As PivotTable
Dim objFeuille As Worksheet
Dim bolTrouve As Boolean
For Each objFeuille In ActiveWorkbook.Worksheets
For Each objTCD In objFeuille.PivotTables
If objTCD.Name = varReponse Then
bolTrouve = True
Exit For
End If
Next objTCD
If bolTrouve Then Exit For
Next objFeuille
If bolTrouve Then
varReponse = objTCD.CacheIndex
Else
MsgBox "Le tableau croisé dynamique '" & varReponse & "' n'a pas été trouvé !", vbCritical, "Exécution impossible"
Exit Sub
End If
End If
ActiveCell.PivotTable.CacheIndex = CInt(varReponse)
ActiveCell.PivotTable.RefreshTable
Else
MsgBox "Aucune réponse !", vbCritical, "Exécution impossible"
End If
End SubMise à jour des graphiques croisés dynamiques indépendants
Afin de répondre au problème de mise à jour de ces graphiques. qui ne se fait pas, même en utilisant la commande Actualiser tout !
Sub MajGraphIndependants()
Dim objTcd As PivotTable
ActiveWorkbook.RefreshAll
For Each objTcd In ActiveWorkbook.PivotTables
objTcd.PivotCache.Refresh
objTcd.Update
Next
End SubMerci pour votre attention bienveillante.