Série d’exercices corrigés sur les listes déroulantes avancées dans Excel

Créer une liste déroulante dans Excel où les options disponibles dépendent des choix faits dans une autre cellule ou des conditions spécifiées.

Exercice 1 : Créer une liste déroulante simple

Énoncé :

Dans une feuille Excel :

Insérez une liste déroulante dans la cellule B2 qui permet de choisir parmi les options suivantes : Pomme, Orange, Banane, Raisin.
La liste déroulante doit être fonctionnelle et n’autoriser que les choix proposés.

Correction :

Sélectionnez la cellule B2.
Allez dans Données > Validation des données.
Dans la fenêtre, configurez :
- Autoriser : Liste.
- Source : Tapez Pomme, Orange, Banane, Raisin.
Cliquez sur OK.
Testez la liste déroulante : seule une des options peut être choisie.

Exercice 2 : Liste déroulante dynamique avec plage variable

Énoncé :

Insérez une liste déroulante dans la cellule C2 qui récupère les données présentes dans la colonne A.
Si de nouvelles données sont ajoutées à la colonne A, elles doivent apparaître automatiquement dans la liste déroulante.

Correction :

Créer une table structurée :
- Sélectionnez les données dans la colonne A (par exemple A1:A5).
- Allez dans Insertion > Tableau.
- Cliquez sur OK.
Créer la liste déroulante :
- Sélectionnez la cellule C2.
- Allez dans Données > Validation des données.
- Dans Source, entrez : =Table1[Nom_de_la_Colonne]
- Cliquez sur OK.
Ajoutez de nouvelles données dans la colonne A. Vérifiez que la liste déroulante les intègre automatiquement.

Exercice 3 : Liste déroulante dépendante

Énoncé :

Dans la cellule D2, insérez une liste déroulante avec les options : Fruits, Légumes.
Dans la cellule E2, insérez une liste déroulante dépendante :
- Si Fruits est sélectionné dans D2, afficher Pomme, Orange, Banane.
- Si Légumes est sélectionné dans D2, afficher Carotte, Tomate, Courgette.

Correction :

Créer les données :
- En colonne A : Fruits, Légumes.
- En colonne B (Fruits) : Pomme, Orange, Banane.
- En colonne C (Légumes) : Carotte, Tomate, Courgette.
Nommer les plages :
- Sélectionnez les cellules contenant Pomme, Orange, Banane et donnez-leur le nom Fruits (via l’onglet Formules > Définir un nom).
- Sélectionnez les cellules contenant Carotte, Tomate, Courgette et donnez-leur le nom Légumes.
Créer la liste principale (D2) :
- Sélectionnez D2.
- Allez dans Données > Validation des données.
- Dans Source, entrez A1:A2.
Créer la liste dépendante (E2) :
- Sélectionnez E2.
- Allez dans Données > Validation des données.
- Dans Source, entrez : =INDIRECT(D2)
Testez :
- Sélectionnez une valeur dans D2 et vérifiez que les options correspondantes s’affichent dans E2.

Exercice 4 : Liste déroulante avec recherche avancée (VBA)

Énoncé :

Créez une zone de texte (ComboBox ActiveX) dans la cellule F2.
La liste déroulante doit permettre de rechercher parmi une liste de noms située en A1:A10.
Lorsque l’utilisateur commence à taper, la liste doit se filtrer automatiquement.

Correction :

Ajouter une ComboBox :
- Allez dans Développeur > Insertion > Contrôles ActiveX.
- Insérez une Zone de liste déroulante (ComboBox) dans la feuille.
Ajouter du code VBA :
- Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
- Cliquez sur la feuille contenant la ComboBox.
- Collez ce code :
Private Sub ComboBox1_Change() Dim ws As Worksheet Dim i As Range ComboBox1.Clear ' Remplir la ComboBox avec des filtres Set ws = ThisWorkbook.Sheets("Feuil1") For Each i In ws.Range("A1:A10") If InStr(1, i.Value, ComboBox1.Text, vbTextCompare) > 0 Then ComboBox1.AddItem i.Value End If Next i End Sub
Enregistrer et tester :
- Enregistrez le fichier au format .xlsm.
- Testez en tapant dans la ComboBox pour vérifier que la liste se filtre dynamiquement.

Exercice 5 : Liste déroulante conditionnelle avec couleurs

Énoncé :

Créez une liste déroulante dans la cellule G2 avec les options : Urgent, Normal, Faible.
Appliquez une couleur automatiquement :
- Urgent : Rouge.
- Normal : Orange.
- Faible : Vert.

Correction :

Créer la liste déroulante :
- Sélectionnez la cellule G2.
- Allez dans Données > Validation des données.
- Dans Source, entrez Urgent,Normal,Faible.
Appliquer la mise en forme conditionnelle :
- Sélectionnez G2.
- Allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle.
- Ajoutez les règles suivantes :
  - Urgent : Formule : =$G$2="Urgent" Couleur : Rouge.
  - Normal : Formule : =$G$2="Normal" Couleur : Orange.
  - Faible : Formule : =$G$2="Faible" Couleur : Vert.
Testez la liste : les couleurs changent selon la sélection.

Résumé

Exercice	Objectif
Exercice 1	Créer une liste déroulante simple.
Exercice 2	Dynamiser une liste déroulante avec une plage.
Exercice 3	Créer une liste déroulante dépendante.
Exercice 4	Ajouter une recherche dynamique avec VBA.
Exercice 5	Appliquer des couleurs conditionnelles.

Automatiser une liste conditionnelle dans Excel permet de rendre les listes déroulantes dynamiques, en fonction des données ou des conditions. Voici un guide étape par étape pour créer une liste conditionnelle automatisée.

Exercices_Listes_Deroulantes Télécharger

1. Exemple de Scénario : Catégories et Sous-catégories

Vous voulez :

Une liste déroulante Catégories (par exemple, Fruits, Légumes).
Une liste déroulante Sous-catégories qui change dynamiquement selon la catégorie sélectionnée :
- Fruits : Pomme, Orange, Banane.
- Légumes : Carotte, Tomate, Courgette.

2. Étapes pour Automatiser la Liste Conditionnelle

Étape 1 : Préparer les Données

Organisez vos données comme suit :
- Colonne A : Fruits, Légumes.
- Colonne B (Fruits) : Pomme, Orange, Banane.
- Colonne C (Légumes) : Carotte, Tomate, Courgette.

Étape 2 : Nommer les Plages

Sélectionnez les cellules contenant les sous-catégories :
- Pour Fruits (Colonne B) : Sélectionnez les cellules B2:B4.
- Allez dans l’onglet Formules > Définir un nom.
- Donnez comme nom : Fruits.
Répétez pour les Légumes (Colonne C) et nommez la plage : Légumes.

Étape 3 : Créer la Liste Principale (Catégories)

Sélectionnez une cellule pour la liste principale (par exemple E2).
Allez dans Données > Validation des données.
Dans Source, entrez les catégories : =A2:A3

Étape 4 : Créer la Liste Conditionnelle (Sous-catégories)

Sélectionnez une cellule pour la sous-catégorie (par exemple F2).
Allez dans Données > Validation des données.
Dans Source, utilisez la fonction INDIRECT pour référencer dynamiquement la plage nommée : =INDIRECT(E2)

3. Automatiser la Liste avec Plages Dynamiques

Si les catégories ou sous-catégories changent fréquemment, utilisez des plages dynamiques basées sur des tables structurées.

Étape 1 : Convertir les Données en Table

Sélectionnez les données (colonnes A, B, C).
Allez dans Insertion > Tableau et cliquez sur OK.
Chaque plage devient automatiquement dynamique.

Étape 2 : Utiliser une Plage Nommée Dynamique

Allez dans l’onglet Formules > Gestionnaire de noms.
Créez un nom dynamique basé sur une formule :
- Exemple pour la plage des Fruits : =DECALER(Table1[Fruits],0,0,NON.VIDE(Table1[Fruits]),1)

Étape 3 : Ajuster les Listes Déroulantes

Dans la validation des données, utilisez les noms de plages dynamiques (par exemple, =Fruits).

4. Ajouter une Mise à Jour Automatique avec VBA (Optionnel)

Si vous voulez que les listes déroulantes soient mises à jour automatiquement à chaque modification des données, vous pouvez utiliser VBA.

Code VBA : Mise à jour automatique

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("E2")) Is Nothing Then
        Dim ws As Worksheet
        Dim subCategoryRange As Range
        Set ws = ThisWorkbook.Sheets("Feuil1")
        
        ' Effacer la liste déroulante précédente
        ws.Range("F2").Validation.Delete
        
        ' Définir la plage des sous-catégories
        Set subCategoryRange = ws.Range(Target.Value & "List")
        
        ' Créer une nouvelle validation des données
        With ws.Range("F2").Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=" & Target.Value & "List"
            .IgnoreBlank = True
            .InCellDropdown = True
        End With
    End If
End Sub

Explication :

Ce code détecte les modifications dans la cellule de la catégorie principale (E2).
Il ajuste automatiquement la liste déroulante des sous-catégories (F2).

5. Résumé des Étapes

Étape	Action
1. Préparer les données	Organisez les catégories et sous-catégories en colonnes distinctes.
2. Nommer les plages	Nommez chaque plage de sous-catégories correspondant à une catégorie.
3. Créer la liste principale	Ajoutez une liste déroulante pour les catégories (validation des données).
4. Lier les sous-catégories	Utilisez `INDIRECT` pour rendre la liste déroulante des sous-catégories dynamique.
5. Plages dynamiques	Convertissez les données en table pour qu’elles s’adaptent automatiquement.
6. (Optionnel) VBA	Automatisez complètement la mise à jour des listes déroulantes.