Comment purger les anciennes valeurs qui restent dans un champ d’un tableau croisé dynamique ?

Dans ce tutoriel, je vais vous montrer comment il est possible de supprimer tous les résidus de champ qui peuvent se trouver à l'intérieur d'un tableau croisé dynamique.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Comment purger les anciennes données d'un TCD ?

Ce tutoriel est la troisième et dernière partie de la série consacrée aux questions-réponses relatives à ma formation sur la découverte des tableaux croisés dynamiques d’Excel. Bien entendu, si après avoir suivi la formation vous avez encore des questions, n'hésitez pas à m'en faire part, j'y répondrai avec plaisir, mais uniquement dans la formation.

Nous allons donc réponde à la question posée par Benjamin qui me dit qu’après avoir modifié des valeurs de la base qui sert de source au tableau croisé dynamique, les anciennes valeurs sont toujours présentes au niveau des filtres de données.

Il souhaite donc savoir pourquoi celles-ci sont toujours présentes et comment les retirer facilement.

Pour répondre cette question, nous disposons ici d’une mini base de données dans laquelle nous allons retrouver le montant du chiffre d'affaires réalisé par une entreprise dans différents pays, au cours des années 2017 à 2021 :

Ces données sont ensuite synthétisées au sein de trois tableaux croisés dynamiques :

En fonction :

• Du pays,
• De l’année
• Du pays et de l’année

Si nous déroulons le menu de sélection des données du premier tableau, nous y retrouvons effectivement les pays tels que présentés dans la base avec les lettres A, B et C :

Maintenant, imaginons que nous modifions la manière dont sont présentées les données, en changeant les lettres par des numéros :

Lorsque nous allons actualiser le tableau croisé dynamique, Excel va bien modifier les valeurs pour faire apparaître les nouvelles valeurs de ces champs :

En revanche, en déroulant le menu des filtres, nous allons retrouver les nouveaux champs, mais également les anciens :

2. Méthode 1 : Purger les anciennes valeurs à la main

La première méthode que nous allons découvrir est la méthode manuelle, que nous pouvons utiliser sur l'ensemble des versions d'Excel, y compris les plus anciennes.

Cette méthode va tout simplement consister à désactiver le nom du champ qui pose un problème, puis à actualiser le tableau croisé dynamique.

Seulement, attention de bien désactiver le champ pays dans tous les tableaux croisés dynamiques dans lequel nous retrouvons le champ « Pays » :

Donc dans notre exemple, le premier et le troisième tableau croisé dynamique :

Puis, nous pouvons actualiser l’un de ces tableaux, avant de réafficher ces champs pour constater que les anciennes valeurs ont bien disparu :

Comme vous pouvez le voir ici aussi les anciennes valeurs ont bien disparu alors cette méthode a le mérite de bien fonctionner, mais comme vous pouvez le constater s'il y a un nombre important de tableaux croisés dynamiques qui reposent sur une même base source, il faudra alors supprimer l'ensemble des champs ce qui pourra demander un certain temps.

D’autant plus que cela pourrait également casser la mise en forme du tableau croisé dynamique ou alors les formules qui ont pu être mises en place pour extraire des données.

3. Purger les anciennes valeurs automatiquement

C’est pourquoi Excel a prévu sur les versions un petit peu plus récentes la possibilité de modifier directement ce comportement.

En effet, il faut savoir que ce comportement est en fait une fonctionnalité qui vise à pouvoir récupérer facilement des valeurs lorsque celle-ci ne figure plus dans une base de données, mais que nous pouvons imaginer que dans le futur elles puissent réapparaître.

Cela permettra donc de conserver les espaces en mémoire qui sont alloués à ces valeurs pour pouvoir les réutiliser simplement à la place des anciennes.

Si nous voulons les supprimer de manière définitive, nous pouvons sélectionner l'un des tableaux croisés dynamiques, puis effectuer un clic droit et choisir d’afficher les « Options du tableau croisé dynamique ».

Ensuite, nous allons nous rendre sur l'onglet « Données » et dérouler le menu « Nombre d'éléments à retenir par champ ».

Celui-ci est placé par défaut sur « Automatique », et il suffit de sélectionner « Aucun » pour que les valeurs supprimées disparaissent définitivement du cache du tableau croisé dynamique.

Pour nous en rendre compte nous allons à nouveau renommée les noms de pays afin de ne conserver ce dernier, sans suffixe :

Après actualisation, nous pourrons constater que les anciennes valeurs ont bien été purgées de la liste des filtres :

4. Méthode 2.5 : Purger les anciennes valeurs automatiquement en masse

Il existe également une version alternative de cette méthode qui consiste à passer par le développement d’une petite macro-commande en VBA.

Celle-ci permettra d’automatiser la modification du paramètre que nous avons découvert dans la partie précédente sur l’ensemble des tableaux croisés dynamiques, ce qui pourra s’avérer très intéressant dans de nombreux cas.

Pour cela, nous utiliserons la macro-commande suivante, insérée dans un nouveau module :

Sub purgerListeChamps()
    Dim feuille As Worksheet, tcd As PivotTable
    
    For Each feuille In ActiveWorkbook.Worksheets
        For Each tcd In feuille.PivotTables
            tcd.PivotCache.MissingItemsLimit =  xlMissingItemsNone
            tcd.PivotCache.Refresh
        Next
    Next
End Sub

Pour en savoir plus, je vous laisse découvrir les détails de cette méthode dans la vidéo de ce tutoriel.