Cette page a pour objectif de présenter la fonction DECALER ainsi que quelques unes de ses puissantes applications !
Explication interactive de la fonction
Ce fichier illustre de manière interactive le fonctionnement de
cette fonction un peu complexe, mais dont les applications sont "magiques"...
Pour utiliser ce fichier, il suffit de changer
les valeurs des cellules jaunes et d'observer les conséquences sur la zone grise du tableau.
Bon amusement !
Application No 1 : image et menu déroulant
Le principe utilisé dans cet exemple est d'associer une image
à un nom défini à l'aide d'une formule DECALER.
Cette formule prenant en compte le résultat du choix d'une liste déroulante, ainsi que
la plage de cellules contenant la liste des images.
Pour des explications plus claires ;-), voyez le fichier Exemple...et/ou suivez la procédure ci-dessous :
1. Ajout des images.
Dans une nouvelle feuille, augmenter la taille d'une plage de cellules (hauteur et largeur), de façon à pouvoir accueillir la base de dessins. Il est nécessaire que ces cellules soient toutes de la même hauteur et largeur.
Insérer ensuite les dessins, (menu Insertion, puis choisir Images, Images de la bibliothèque) en les ajustant aux tailles de cellules. Pour cela, lorsque vous redimensionnez le dessin, maintenez la touche ALT enfoncée. Ceci permettra d'ajuster la taille exactement à la hauteur ou la largeur de la cellule.
Il suffit ensuite de positionner chaque image sur une cellule différente. Ceci donne le résultat suivant :
2. Ajout du menu déroulant.
Créer tout d'abord une liste verticale de noms, correspondant aux images; l'ordre des noms doit être le même que celui des images.
Identifier une cellule qui servira à recevoir le numéro de ligne du menu déroulant.
Dessiner le menu déroulant (bouton Zone de liste modifiable), en affichant la barre d'outils Formulaire (Menu Affichage, puis Barres d'outils, Formulaires).
Finalement, associer le menu déroulant à la liste de noms et à la cellule recevant le résultat : bouton droit de la souris sur le menu déroulant, choisir Format de contrôle..., puis dans l'onglet Contrôle, définir la plage d'entrée et la cellule liée.
En choisissant un des items du menu déroulant, la cellule liée doit maintenant se mettre à jour.
Nous allons utiliser cette dernière pour alimenter un nom défini à l'aide d'une formule DECALER.
3. Définition du nom "Image" à l'aide d'une formule DECALER
Définir le nom Image (Menu Insertion, Nom, Définir), de la façon suivante :
Ceci permettra d'associer le nom à la référence de la cellule sélectionnée.
Alternative :
On peut aussi utiliser la fonction INDEX pour définir le nom.
Ceci ne s'applique cependant que pour des cellules uniques.
Merci à Stéphane pour l'astuce.
4. Ajout d'une image associée au nom "Image".
Copier-coller une des images.
Puis, en ayant sélectionné cette image, aller dans la barre de formule et entrer "=Image".
Ceci aura pour résultat d'associer le nom et l'image.
Ainsi, en choisissant un autre item du menu déroulant, la cellule liée va se mettre à jour, ce qui va
se répercuter sur le nom image, puis finalement sur l'image en elle-même.
On peut aussi utiliser cette technique en lien avec un graphique :
Application No 2 : graphique ajustable aux données
Il s'agit ici de construire un graphique ajustable aux nouvelles données que l'on rentrera par la suite.
Par exemple, on veut suivre mensuellement l'évolution de plusieurs indicateurs. 1. Définition du tableau et des noms attachés
Nous construisons dans un premier temps le tableau qui alimentera le graphique :
Puis, nous définissons les noms qui s'y attachent (menu Insertion Noms Définir...) :
Titre :
=DECALER('Graphique ajustable aux données'!$B$4;0;0;1;NBVAL('Graphique ajustable aux données'!$4:$4))
Var1 :
=DECALER('Graphique ajustable aux données'!$B$5;0;0;1;NBVAL('Graphique ajustable aux données'!$5:$5)-1)
Var2 :
=DECALER('Graphique ajustable aux données'!$B$6;0;0;1;NBVAL('Graphique ajustable aux données'!$6:$6)-1)
Var3 :
=DECALER('Graphique ajustable aux données'!$B$7;0;0;1;NBVAL('Graphique ajustable aux données'!$7:$7)-1)
On notera que la fonction NBVAL permet de renvoyer le nombre de cellules non vides de la plage
indiquée dans la fonction.
En l'occurence, on renvoie ici le nombre de valeurs pour chacune des lignes du tableau.
Ceci permet ensuite de définir avec précision le contenu des noms.
2. Construction du graphique
Il suffira ensuite de construire le graphique à partir des noms que nous avons définis :
La présence du nom de fichier est nécessaire pour le bon fonctionnement
de l'exemple.
L'ajout des noms peut aussi se faire directement sur les formules du graphique. Pour cela, sélectionner
la série, et changer les intitulés pour utiliser les noms définis précédemment :
Exemple pour la série 1 : =SERIE(;'decal-2b.xls'!titre;'decal-2b.xls'!var1;1)
Là encore, il est important d'indiquer le nom du fichier.
Eric Renaud propose quelques variantes sur son site (section des graphiques) : http://ericrenaud.free.fr/
En voici une autre, basée sur l'interactivité des menus déroulants :
3. Graphique interactif ajustable
A partir de l'exemple précédent, ajouter une zone de liste modifiable
(cf. la section graphique interactif pour plus de détails),
et la lier à la plage de données A5:A7, ainsi qu'à la cellule F2.
Redéfinir ensuite le nom "Var1", de façon à prendre en considération
la valeur de la cellule F2 :
=DECALER('Graphique ajustable aux données'!$B$5;'Graphique ajustable aux données'!$F$2-1;0;1
;NBVAL('Graphique ajustable aux données'!$5:$5)-1)
Enfin, on peut enlever les deux autres séries du graphique, ainsi que les noms correspondants.
Le graphique va maintenant s'ajuster au choix du menu déroulant. Si vous ajoutez de nouvelles
données en ligne, celles-ci vont être automatiquement prises en compte.
Application No 3 : plage pour un TCD.
1. Définition du nom
On peut utiliser un nom défini avec l'aide de la fonction DECALER pour définir une base de données alimentant un tableau croisé dynamique.
Ceci est particulièrement utile lorsque l'on ajoutera des lignes ou des colonnes, le tableau reprendra automatiquement les bonnes données.
Soit le tableau suivant :
Le nom BASE_TCD sera défini (Insertion - Noms - Définir...) avec la formule suivante :
=DECALER(Base!$A$1;0;0;NBVAL(Base!$A:$A);NBVAL(Base!$1:$1))
Ainsi, le nom prend en compte le nombre d'éléments de la première colonne, ainsi que le nombre de données en ligne.
Il est important que la première colonne ne comporte pas de cellules vides, sinon la plage ne prendra pas en compte les dernières lignes. Pour éviter cela, définissez une clef primaire* sur la première colonne.
* Clef primaire : Il s'agit en général d'une séquence de chiffres que l'on incrémente pour chaque nouvel enregistrement.
On pourra prendre par exemple le numéro de ligne - 1, à l'aide de la formule =ligne()-1.
2. Création du tableau
La création du tableau reste classique : Données - rapport de tableau croisé dynamique...
La seule différence réside dans le fait qu'il faut indiquer à l'étape 2 "BASE_TCD" comme source de données :
Bon courage !
Si ces informations vous ont été utiles, ou si vous voyez des points à améliorer, n'hésitez pas à me contacter.
Copyright - Gaëtan Mourmant - Tous droits réservés.
