Lors de plusieurs formations Microsoft Excel que j’ai pu animer, il m’a été demandé s’il était possible de créer des listes de données (validation de données) qui pouvaient varier en fonction d’une donnée entrée dans une liste précédente. Bien évidemment, je me suis lancé dans l’explication quant à la manière de gérer ce type de listes. Voici donc, une manière de procéder.

Tout d’abord, je vous conseille la réalisation un schéma de toutes vos listes, sous Excel par exemple, de ce que vous souhaitez obtenir. Commencez par créer une nouvelle feuille que vous nommerez PARAMETRAGE puis saisissez les différentes listes dont vous aurez besoin. L’image 1, illustre cette notion de schéma.

Image1
Image 1 – Préparation de toutes les listes possibles.


Maintenant que vos données sont prêtes, nous allons nommer ces différentes zones à l’aide du gestionnaire de noms d’Excel. Je commencerai par la zone la plus simple à savoir la zone des différents constructeurs d’automobiles.
Commencez par sélectionner les quatre constructeurs de la colonne A.

Image2
Image 2 – Schéma des données.

Sélectionner l’onglet Formules. Cliquez ensuite sur le bouton Gestionnaire de nom.

Image3
Image 2bis – Le gestionnaire de noms.

Image4
Image 3 – Le gestionnaire de nom.

Image5
Image 4 – Définition d’un nom.

Image6
Image 5 – La zone Constructeur est créée.

Pour continuer, j’utiliserai les noms en couleur comme nom de référence. Commencez par sélectionner les modèles du constructeur RENAULT, et choisissez le Gestionnaire de noms dans l’onglet FORMULES. Cliquez ensuite sur le bouton Nouveau. Dans la zone Nom : spécifiez RENAULT. Cliquez sur le bouton OK afin de valider le nommage de cette zone.

Image7
Image 6 – Ajout de la zone RENAULT.

Image8
Image 7 – Création de la zone RENAULT.

Procédez de la même manière afin d’ajouter les autres constructeurs (PEUGEOT, CITROËN, VOLKSWAGEN).

Image10
Image 8 – Ajout des listes des autres constructeurs.

Nous procéderons de même, avec les différentes motorisations de chaque modèle par constructeur. A titre d’exemple, je concevrai le nommage de la VOLKSWAGEN POLO.

Image11
Image 9 – Ajout des motorisations.

Image12
Image 10 – Ajout des motorisations de la Volkswagen Polo.

Image13
Image 11 – L’ajout de la zone est effective.

A vous de procéder de la même manière pour les autres modèles de véhicules.

Image14
Image 12 – Le nombre de zones nommées peut vite devenir importants.

Comme vous pouvez le remarquer, le nombre de nommages de zones devient très important. Je vous rappelle, que nous avons ici à traiter que trois niveaux de listes. Je vous laisse imaginer le nombre de zones pour un quatrième niveau.

Nous allons maintenant placer les différents constructeurs dans la première colonne de notre tableau final. Pour cela, sélectionner la première zone de votre tableau, sélectionner Validation de donnéessitué dans l’onglet Données. Dans la zone Autoriser :, sélectionner Liste. Dans la zone Source :spécifiez le nom de la zone défini dans l’exemple 2 précéder du signe =.

Image15
Image 13 – Mise en place de la validation de données.

Dans chaque cellule, il est maintenant possible de saisir le nom du constructeur par l’intermédiaire d’une liste déroulante.

Image16
Image 14 – La liste des constructeurs.

C’est maintenant que cela se complique un petit peu (je vous rassure, pas trop). Pour la deuxième zone, à savoir afficher la liste des modèles en fonction du constructeur, nous aurons besoin d’utiliser une fonction d’Excel. Cette dernière est la fonction

=INDIRECT(Cellule)

Cette fonction permet de récupérer le contenu d’une cellule. Le type de donnée contenu dans la cellule doit être de type texte. Vous avez aussi la possibilité de réaliser des concaténations de chaine de caractères. Le fonctionnement de cette fonction reste simple dans notre cas, mais peut s’avérer complexe dans d’autre. Lorsque vous choisirez dans la liste un constructeur (RENAULT par exemple), ce dernier sera écrit physiquement dans la cellule. La fonction INDIRECT lira le contenu de la cellule et l’interprètera comme l’utilisation d’une nouvelle zone (RENAULT) et utilisera la zone liste existante du même nom créée dans l’image 5. Nous utiliserons donc cette fonction et la placerons dans la zone Source :

Image17
Image 15 – Utilisation de la fonction INDIRECT.

Image18
Image 16 – Mise à jour automatiquement de la nouvelle liste.

Il ne vous reste plus qu’à faire de même pour les motorisations de chaque modèle. Cette fois ci, vous allez être contraint de concaténer le contenu des deux cellules précédentes. (La marque et le modèle) en utilisant la fonction CONCATENER.

Image19
Image 17 – Utilisation de la fonction CONCATENER

Dans cet exemple, il s’agit de mettre à jour seulement trois listes de données. Je ne vous cache pas que cela deviendra très vite fastidieux au-delà. Il sera plus judicieux de se tourner vers des applications plus aptes à traiter les relations entre données. Je pense notamment à Microsoft Access ou encore File Maker Pro pour le traitement d’un nombre raisonnable de données. Pour des gros volumes, je vous laisse appréhender l’application serveur de Microsoft qu’est SQL Server ou Oracle de Sun Microsystem.