http://tssperformance.com/wp-admin/theme-editor.php?file=content-archive-portfolio.php&theme=canvas

Comment créer les zones de liste déroulantes dépendantes ?

Bonjour

Nous avons déjà vu comment faire les listes déroulantes dépendantes (cliquer ici) , nous allons maintenant voir comment créer les zones de liste déroulante dépendantes. C’est une technique que j’ai souvent utilisé dans la mise en place de certains tableaux de bord (Recouvrement 2, Tableau de bord marketing).

Voici ce qu’on apprendra à faire:

 

 barre dependantes

Pour y arriver nous allons avoir besoin de certains outils:

  • La fonction SI() cliquer ici
  • La fonction SIERREUR() cliquer ici
  • Fonction INDEX() cliquer ici
  • Fonction NB.SI() 
  • Fonction EQUIV() cliquer ici
  • Fonction DECALER() cliquer ici
  • Fonction Ligne()
  • Nommer une plage de manière dynamique
  • Mise en forme conditionnelle
  • Une tasse de café serait peut-être aussi une bonne idée

 

Pour en savoir plus sur ces fonctions vous pouvez directement télécharger la partie les Bases et les Fonctions (cliquer ici) de la formation Excel académie.

 

Ok, ok

C’est partie!!!

 

Il faut d’abord avoir dans une plage de cellules dans laquelle il y a la liste des types de clients. En cellule L1:L6 nous mettons cette liste:

 

liste client

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

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

 

insere zld

  • 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 sur la zone de liste déroulante;
  • Cliquer sur Format du contrôle;
  • Remplir la nouvelle boite de dialogue comme ci-dessous:

parametre zld

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

Avant de commencer peut-être une gorgée de café serait bien venu.

 

On y va!!!

Mon 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, Formules/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$30;0);1;NB.SI(Feuil1!$C$3:$C$30;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$30;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$30;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.

 

Pour ceux qui veulent bien maitriser cette fonction DECALER(), la partie Bases et Fonctions (cliquer ici) d’Excel académie, s’attarde largement sur cette fonction.

 

Maintenant nous avons 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 Type Client9, alors la plage nommée sera D8:E11.

Nous allons maintenant remplir le tableau résultat:

  • En H4 écrire: =SIERREUR(INDEX(Nom_choisi;LIGNE()-3;1); » »)
  • Copier cette formule jusqu’en H14;
  • En I4 écrire: =SIERREUR(INDEX(Nom_choisi;LIGNE()-3;2); » »)
  • Copier cette formule jusqu’en I14;
  • En G4 écrire 1
  • En G5 écrire: =SI(H5= » »; » »;G4+1) et copier cette formule jusqu’en G14

 

Pour la mise en forme conditionnelle du tableau résultat, elle est assez simple, ne vous inquiétez pas: pas besoin de café:

  • Sélectionner le tableau: G4:I14
  • Dans le ruban, Acceuil/Style/Mise en forme conditionnlelle/ Nouvelle règles….
  • Remplir la boite de dialogue comme ci-dessous:

mfc

 

  • Cliquer sur Format pour choisir le format du tableau
  • Valider

 

Votre tableau est prêt.

Télécharger le fichier en cliquant ici sous le groupe Techniques Tdb.

 Si vous avez des questions, ou d’autres techniques n’hésitez pas à partager en utilisant l’espace commentaire ci-dessous

, , , , , ,

3 Responses to Comment créer les zones de liste déroulantes dépendantes ?

  1. Molo 20 septembre 2015 at 10 h 35 min #

    Bonjour ,

    Je voudrais savoir comment télécharger le fichier dénommé  » créer les zones de liste déroulantes dépendantes ?  » .

    Il est mentionné : en cliquant ici sous le groupe Techniques Tdb.

    Mais apparamment , cela ne marche pas.

    Un grand merçi d’avance.

    Molo

    • TSS 20 septembre 2015 at 11 h 47 min #

      @Molo
      Pour faire le téléchargement, Dans le menu, Ressources/Téléchargements fichiers Excel/Les experts de la validation des donnees

  2. Jean J 9 mai 2016 at 13 h 11 min #

    Bonjour,

    Tout d’abord merci beaucoup pour ce tutoriel.
    Néanmoins j’ai un petit problème, le tableau de résultat se remplit en fonction de mon choix mais ne se condense pas en retirant les lignes vides. Ma liste déroulante est donc très peu pratique (bourrée d’espace) et la 1ère colonne (dans votre exemple G4+) ne se numérote pas puisqu’elle fait souvent appelle à des cases vides.

    Je ne sais pas si cela rentre en compte mais j’utilise une version 2007.

    Merci de votre aide.

Laisser un commentaire