Créer une liste déroulante dépendante

Comment afficher une partie d'un tableau avec une liste déroulante dépendante

Nous allons voir comment créer une zone de liste déroulante dépendante permettant d'afficher une partie d'un tableau. Cette méthode est souvent utilisée dans la mise en place de tableau de bord.

Résultat à obtenir : 

Pour y arriver nous allons avoir besoin des outils ci-dessous :

• La fonction SI()
• La fonction SIERREUR()
• La fonction INDEX()
• La fonction NB.SI()
• La fonction EQUIV()
• La fonction DECALER()
• La fonction LIGNE()
• Nommer une plage de manière dynamique
• Mise en forme conditionnelle

Prenons l'exemple d'un commercial qui classe ses clients par zone géographique. Il faut d’abord avoir une plage de cellules dans laquelle nous allons inscrire la liste des zones des clients. Par exemple, sur la plage de cellules L1:L6 nous mettons cette liste :

Nous allons insérer la barre de liste déroulante :

• Dans le ruban, Onglet Développeur/Groupe Contrôle/Insérer/Zone de liste déroulante

• Cliquer quelque part sur la feuille pour positionner cette zone de liste déroulante

Enregistrons maintenant les paramètres de cette zone de liste déroulante :

• Cliquer avec le bouton droit de la souris sur la zone de liste déroulante
• Cliquer sur Format du contrôle
• Remplir la nouvelle boite de dialogue comme ci-dessous :

Votre zone de liste déroulante est maintenant remplie par la liste des zones de clients. Maintenant nous allons faire  apparaître pour chaque choix, la liste des clients et leurs montants.

Le procédé sera de créer une plage nommée dynamique qui dépendra du choix fait dans la zone de liste déroulante.

• Etape1 : retrouver le choix fait dans la liste.  En N2 écrire =INDEX($L$2:$L$6;A1)
• Etape2 : créer un nom dynamique qui variera selon la valeur en N2

Dans le ruban, Onglet Formules/Groupe Nom définis/Définir un nom - dans la boite de dialogue écrire pour Nom : Nom_choisi
- et pour fait référence à, écrire la formule :

=DECALER(Feuil1!$C$2;EQUIV(Feuil1!$N$2;Feuil1!$C$3:$C$19;0);1;NB.SI(Feuil1!$C$3:$C$19;Feuil1!$N$2);2)

Cette formule permet de retrouver la partie du tableau choisie dans la liste déroulante. 

Quelques explications:

• Feuil1!$C$2: cellule à partir de laquelle on décale
• EQUIV(Feuil1!$N$2;Feuil1!$C$3:$C$19;0): permet de retrouver la position de l’élément choisi dans la liste déroulante. On décalera donc C2 de ce nombre de ligne
• 1: je décale C2 d’une colonne et du nombre de ligne retrouvé ci-dessus
• NB.SI(Feuil1!$C$3:$C$19;Feuil1!$N$2): le nombre de ligne que la nouvelle plage de cellule doit avoir qui correspond au nombre de fois qu’on compte l’élément choisi dans la zone
• 2: le nombre de colonne que la nouvelle plage contient.

Nous avons donc créé un nom qui correspond à la plage avec les colonnes Client et CA et qui varie selon le choix dans la zone de liste déroulante. En d’autres termes si je choisi dans ma zone de liste déroulante Zone Client Nord, alors la plage nommée sera C6:E10.

Nous allons maintenant remplir le tableau résultat :

• En H5 écrire : =SIERREUR(INDEX(Nom_choisi;LIGNE()-4;1);" ")
• Copier cette formule jusqu’en H15;
• En I5 écrire : =SIERREUR(INDEX(Nom_choisi;LIGNE()-4;2);" ")
• Copier cette formule jusqu’en I15;
• En G5 écrire : 1
• En G6 écrire: =SI(H6=" ";" ";G5+1) 
• Copier cette formule jusqu’en G15

Pour la mise en forme conditionnelle du tableau résultat, elle est assez simple :

• Sélectionner le tableau: G5:I15
• Dans le ruban, Onglet Accueil/Groupe Style/Mise en forme conditionnelle/ Nouvelle règles….
• Remplir la boite de dialogue comme ci-dessous:

• Cliquer sur Format pour choisir le format du tableau que vous désirez
• Valider par OK

Votre tableau est enfin prêt :