| Le fichier utilisé dans ces notes est disponible au format XLS (Tcd2Listes.xls 71K). |
Il arrive parfois de travailler avec des tableaux croisés dynamiques (TCD) complexes et pour lesquels on doit changer la source de données. Ces sources sont toutes structurées de la même manière (titres de colonnes identiques et même emplacement dans les différentes feuilles).
La solution proposée ici est basée sur l'utilisation des noms dynamiques.
Feuille Données 1
Feuille Données 2
Feuille Tcd 1
Pour construire la feuille "Tcd 1" qui contiendra le tableau croisé dynamique, on indique en A2 le nom de la feuille à
laquelle on fait référence soit "Données 1" soit "Données 2".
(Pour créer la liste des noms de feuilles, on se sert de
la validation des données : Limiter la saisie aux données d'une liste).
On utilise la fonction DECALER pour créer le nom "Liste" (Définir… / Nom / Insertion) qui fait référence à la liste définie à partir de la cellule A2 :
=DECALER(INDIRECT("'"&!$A$2&"'!A1");4;0;NBVAL(INDIRECT("'"&!$A$2&"'!A:A"));4)Qui devient, si on évalue l'instruction INDIRECT :
=DECALER('Données 1'!A1;4;0;NBVAL('Données 1'!A:A);4)L'utilisation des apostrophes (" ' ") permet de gérer les noms de feuilles comportant des espaces (le " & " permet de concaténer deux chaînes de caractères).
C'est le même genre de formule qu'on utilise en B2 pour connaître l'année des mouvements de la liste :
=ANNEE(INDIRECT("'"&A2&"'!A6"))Qui devient, si on évalue l'instruction INDIRECT :
=ANNEE('Données 1'!A6)
Lors de la construction du TCD (Rapport de tableau croisé dynamique… / Données), il faut indiquer "Liste" comme source de données.
Enfin, il ne faut pas oublier d'actualiser le TCD (Sélectionner A5, puis Actualiser les données / Données) lorsqu'on modifie le contenu de la cellule A2.
Ainsi, dans notre exemple (feuille "Tcd 1"), on définit la plage "Liste" comme étant :
On pouvait également écrire:
Dans son utilisation la plus simple, la fonction INDIRECT permet de placer l'adresse d'une cellule dans une autre. La puissance de la fonction INDIRECT, c'est qu'elle peut transformer n'importe quelle valeur en référence. Lorsqu'une formule fait référence à une cellule, la référence à la cellule est mise à jour si :
Fonction DECALER
Cette fonction renvoie une référence à une cellule ou à une plage de cellules qui sera décalée d'un nombre déterminé de
lignes et de colonnes par rapport à une référence donnée. On peut spécifier le nombre de lignes (hauteur) et de colonnes
(largeur) de la référence à renvoyer.
=DECALER(référence;lignes;colonnes;hauteur;largeur)
=DECALER(INDIRECT("'"&!$A$2&"'!A1");4;0;NBVAL(INDIRECT("'"&!$A$2&"'!A:A"));4)
Qui devient, si on évalue l'instruction INDIRECT :
=DECALER('Données 1'!A1;4;0;NBVAL('Données 1'!A:A);4)
c'est-à-dire qu'on définit une plage de cellules qui commence 4 lignes plus bas (4;0;) que la cellule A1
(de la feuille "Données 1" ou "Données 2") et qui a une hauteur égale au nombre de cellules de la colonne A
(NBVAL(A:A)) et une largeur de 4 colonnes. Donc la plage qui sera renvoyée va varier en fonction des données de
la colonne A de la feuille "Données 1" ou "Données 2".
NBVAL compte le nombre de cellules contenant des données dans une plage.
=DECALER(INDIRECT("'"&!$A$2&"'!A5");0;0;NBVAL(INDIRECT("'"&!$A$2&"'!A:A"));4).
Fonction INDIRECT
La fonction INDIRECT renvoie la référence spécifiée par une chaîne de caractères. Les références sont immédiatement
évaluées afin d'afficher leur contenu. On utilise la fonction INDIRECT lorsqu'on veut modifier la référence à une
cellule à l'intérieur d'une formule sans modifier la formule à proprement parler.
=INDIRECT(réf_texte;A1)
Par exemple, si la cellule D3 contient la valeur "C5",
=INDIRECT(D3)
renvoie "30" (la valeur dans C3). Remarquons que si on indique : =INDIRECT("D3") (au lieu de =INDIRECT(D3)),
on reçoit "C5", plutôt que la valeur de C5.
Si on modifie D3, le résultat de la fonction =INDIRECT(…) sera modifié en conséquence.
Par exemple, la formule =SOMME(C3:C10) additionne les valeurs de la plage "C3:C10". Supposons qu'on veuille modifier
dynamiquement la plage à additionner (sans devoir changer la formule), on utilisera la fonction INDIRECT.
Ainsi, dans notre exemple, la formule =SOMME(INDIRECT(D3&":"&E3)) renvoie "150" c'est-à-dire, la somme des cellules C5 à C8.
Si on modifie D3 ou (et) E3, le résultat de la fonction =SOMME(INDIRECT(D3&":"&E3)) sera également modifié.
- la cellule concernée est supprimée à l'aide de la commande Couper ou
- la cellule est déplacée suite à l'insertion ou à la suppression de lignes ou de colonnes.
Si on souhaite que la formule fasse toujours référence à la même cellule, même si la ligne au-dessus de la cellule est
supprimée ou si la cellule est déplacée, on utilise la fonction INDIRECT.
Par exemple, pour faire référence à la cellule A10, utilisez la syntaxe suivante : =INDIRECT("A10").
| Les tableaux croisés dynamiques |
