Tutoriels > Tutoriels & Astuces Excel > Tableaux Croisés Dyn. > TCD - Source de données dynamique

TCD - Source de données dynamique

Publié par Mth le 17-10-2010 (48528 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:

 

Synthèse des données d'une base

 

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":

 

TCD - Modifier la source des 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":

 

TCD - Modifier la source des données

 

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.


Lettre A 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

 

Excel : Nommer une Zone

 

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:

 

Excel - Gestionnaire de noms

 

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:

 

Excel - Zone Nommée en surbrillance

 

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):

 

7zonezonetcd_794 
Pour tester, ajoutons à notre base une ligne supplémentaire en ligne 37:

 

Saisie d'une nouvelle ligne dans un Tableau

 

Un clic droit sur n'importe quelle cellule du TCD permet d'afficher le menu contextuel et de cliquer sur "Actualiser"

Actualiser le TCD

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.


 
Information  

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.


 Lettre B 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:

 

Excel Mettre sous forme de Tableau


Choisissez votre mise en forme: 

Excel Mise en forme d'un Tableau

Une fenêtre vous invite alors à confirmer l'emplacement:

Excel Plage de données d'un Tableau
(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:

Tableau Excel

 

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:

 

Excel Liste des noms

 

Il est également visible via le Gestionnaire de noms avec ses références:

Excel Gestionnaire de noms

Comme vu précédemment, il suffit maintenant d'indiquer ce Tableau1 comme source du TCD: 

Excel Source du Tableau Croisé Dynamique

Pour tester, ajoutons comme précédemment une ligne ou une colonne à notre Tableau1:

Excel Ajout d'une ligne à un tableau

 

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:

 

Excel Gestionnaire de noms

 
Actualisons le TCD; il a intégré automatiquement la nouvelle plage du Tableau 1:

Actualisation du TCD
 
Trait bleu
 
Si vous avez la moindre question, les forums sont toujours à votre disposition pour vous aider.

Pour toutes vos questions ou si vous rencontrez des difficultés, n'hésitez pas à rejoindre nos Forums de Discussions !

Tags Tutoriel   TCD   Dynamique   Données  

 

Autres articles dans cette catégorie Publié le Vues
TCD - Les Options d'un Tableau Croisé Dynamique 30-12-2012 44587
TCD - Trier un Tableau Croisé Dynamique 30-12-2012 31186
TCD - Les Filtres: Textes, Dates ou Valeurs 03-09-2012 23994
TCD - Les Filtres : Généralités 03-09-2012 16549
TCD - Les Filtres : Le filtre du Rapport 03-09-2012 12176
TCD - Extraire les données avec LIREDONNEESTABCROISDYNAMIQUE() 02-07-2012 52980
TCD - Grouper des champs Date dans un Tableau Croisé Dynamique 18-03-2012 47060
TCD - Grouper des champs numériques dans un Tableau Croisé Dynamique 18-03-2012 21426
TCD - Grouper ou Dégrouper les éléments d'un Tableau Croisé dynamique 18-03-2012 11904
TCD - Effectuer deux calculs différents sur un même champ 03-01-2012 13993
TCD - Actualiser un TCD 16-04-2011 27620
TCD - Source de données dynamique 17-10-2010 48529
TCD - Faire glisser / déplacer les champs 20-06-2010 20570
TCD - Créer un élément calculé 25-05-2010 29078
TCD - Intégrer un champ calculé 14-05-2010 103384
Mon Premier Tableau Croisé Dynamique avec Excel 2007 28-03-2010 77548
Comment reconstituer une base à partir d'un TCD ? 12-12-2009 10225
Passeport Pourcentages 03-09-2007 7331