TCD - Source de données dynamique
Publié par Mth le 17-10-2010 (133319 lectures)
Il est très utile de pouvoir définir la source des données d'un TCD de façon dynamique, afin d'intégrer automatiquement tout ajout de lignes ou de colonnes sans avoir à redéfinir la zone manuellement à chaque modification.
Prenons l'exemple d'une situation courante, à partir d'une base de données vous avez réalisé un tableau croisé dynamique afin de synthétiser le chiffre d'affaires par gamme de produits:
Vos données évoluent, vous êtes amené à ajouter des enregistrements à votre base; vous devez donc mettre à jour la source des données de votre TCD afin qu'il intègre vos dernières données.
Une première solution, un peu fastidieuse, consiste à modifier "à la main" la plage de données.
En sélectionnant votre TCD, vous accédez au menu Options, puis cliquez sur "Changer la source de données":
Vous modifiez alors la plage en saisissant les nouvelles coordonnées, ou en sélectionnant toute la plage avec la souris afin qu'elle s'inscrive sur la ligne "Tableau/Plage":
Une meilleure solution consiste à faire en sorte que la plage de données soit dynamique, pour de tenir compte automatiquement des enregistrements ajoutés ou retirés.
Pour mettre en place cette plage dynamique qui tienne compte d'un nombre de colonnes ou de lignes variable, deux solutions: Nommer une zone dynamique à l'aide des fonctions DECALER() et NBVAL(), ou utiliser l'outil TABLEAU d'Excel.
Nommer la zone source à l'aide des fonctions DECALER() et NBVAL()
Cette méthode est expliquée et accompagnée d'un fichier exemple dans l'article Nommer une plage dynamique.
Adaptée au cas présent, prenons l'exemple d'un fichier où l'onglet "Base" est réservé aux données, la plage des données débute en cellule A1 et elle doit s'adapter aux futures lignes ou colonnes ajoutées.
Dans l'onglet "Formules" cliquez sur "Définir un nom"; notre nouveau nom sera "ZoneTCD" et la formule permettant de définir cette zone dynamiquement:
=DECALER(Base!$A$1;;;NBVAL(Base!$A:$A);NBVAL(Base!$1:$1))
*Voir sur ce lien l'utilisation des fonctions NBVAL() et DECALER() permettant de définir facilement une zone nommée dynamique
Si par exemple le nombre de colonne est fixe à 10, et que seules les lignes varient, on peut aussi indiquer cette formule:
=DECALER(Base!$A$1;;;NBVAL(Base!$A:$A);10)
Les données à partir de la onzième colonne ne seront pas prises en compte.
En cliquant sur le Gestionnaire de noms vous voyez bien apparaître la nouvelle zone nommée "ZoneTCD" ainsi que sa formule de calcul:
En cas de doute vous pouvez même vérifier l'étendue de cette zone; déposez le curseur de la souris sur la ligne "Fait référence à" (surtout, sans rien toucher car cela modifierait la formule à votre insu); vous voyez alors la zone entourée de pointillés:
Une fois la zone nommée dynamique mise en place, il suffit de l'indiquer comme source du TCD en saisissant =ZoneTCD sur la ligne Tableau/Plage (ne pas oublier le signe égal):
Pour tester, ajoutons à notre base une ligne supplémentaire en ligne 37:
Un clic droit sur n'importe quelle cellule du TCD permet d'afficher le menu contextuel et de cliquer sur "Actualiser"
Le tableau a tenu compte de cette nouvelle ligne, il en sera de même pour toute ligne ajoutée grâce à la zone nommée dynamique utilisée comme source de données.
Comme précisé dans l'article traitant des zones dynamiques, la base de données ne doit pas contenir de cellules vides dans la première ligne d'en-tête, sans quoi le calcul sera faussé, par ailleurs un TCD n'accepte pas de d'en-tête de champ à vide.
De même la colonne A étant utilisée pour compter le nombre de lignes, elle ne doit pas contenir de cellule vide
L'onglet doit être réservé à la base de données, il ne faut pas ajouter d'autres tableaux dans le même onglet sous peine de fausser les calculs de la zone source.
Utiliser l'outil Tableau
Sélectionnez la zone de données, ou ne sélectionnez qu'une seule cellule pour laisser Excel la déterminer automatiquement, puis cliquez sur "Mettre sous forme de tableau" dans l'onglet Accueil:
Choisissez votre mise en forme:
Une fenêtre vous invite alors à confirmer l'emplacement:
(Laisser la case "Mon tableau comporte des en-têtes cochée, afin qu'Excel ne crée pas une ligne d'en-tête inutile dans cet exemple)
Votre nouveau tableau a été créé avec sa mise en forme:
Un nom lui a été automatiquement attribué, que vous pouvez retrouver dans la liste des noms en cliquant sur la petite flèche comme ci-dessous où son nom "Tableau1" apparaît:
Il est également visible via le Gestionnaire de noms avec ses références:
Comme vu précédemment, il suffit maintenant d'indiquer ce Tableau1 comme source du TCD:
Pour tester, ajoutons comme précédemment une ligne ou une colonne à notre Tableau1:
La ligne a été automatiquement "intégrée" au TABLEAU, on observe que la mise en forme est respectée; dans le gestionnaire de nom la ligne a été ajoutée sans autre intervention de notre part:
Actualisons le TCD; il a intégré automatiquement la nouvelle plage du Tableau 1:
Si vous avez la moindre question, les forums sont toujours à votre disposition pour vous aider.
Didier FOURGEOT