Listes déroulantes en cascade dans Excel
Dans une liste en cascade, aussi appelée liste dépendante, le contenu d’une liste déroulante dépend du choix fait dans la précédente : on choisit une catégorie, puis seulement les formations de cette catégorie, puis le niveau proposé pour cette formation. Cette page présente la méthode INDIRECT avec des plages nommées, la gestion des espaces, la remise à zéro des choix devenus incohérents et une méthode sans noms multiples basée sur DECALER.
Le principe
La validation de la 2e colonne ne pointe pas vers une plage, mais vers un texte transformé en référence : =INDIRECT($B2). Si B2 contient « PAO », INDIRECT renvoie la plage nommée PAO. Il faut donc une plage nommée par valeur de la 1re liste, portant exactement le même nom. Pour une liste simple qui s’agrandit toute seule, voir notre page liste déroulante dynamique.
| Étape | Où | Quoi |
|---|---|---|
| 1. Préparer les listes | Feuille Listes | Une colonne par catégorie, le nom de la catégorie en en-tête |
| 2. Nommer en une fois | Formules › Créer à partir de la sélection | Sélectionner le bloc, cocher « Ligne du haut » : chaque colonne reçoit le nom de son en-tête |
| 3. 1re liste | B2:B50 | Validation › Liste, Source : =Categories |
| 4. 2e liste | C2:C50 | Validation › Liste, Source : =INDIRECT($B2) (B figé, ligne libre) |
$B2 et non $B$2 : sinon toutes les lignes dépendront de la catégorie de la ligne 2.Cascade à 3 niveaux avec INDIRECT
Le 3e niveau suit la même logique : une plage nommée par formation (Excel, Word, Power_BI…) contenant les niveaux proposés, et la source =INDIRECT(SUBSTITUE($C2;" ";"_")). SUBSTITUE est indispensable dès qu’une valeur contient un espace, car un nom défini ne peut pas en contenir.
=CategoriesINDIRECT("Power BI") renvoie #REF! et la liste du niveau reste vide.$B2 à chaque ligne.Gérer les choix devenus incohérents
La validation ne contrôle une cellule qu’au moment de la saisie. Si l’on change la catégorie en B2 après avoir choisi une formation, C2 et D2 gardent leur ancienne valeur. Trois parades, de la plus simple à la plus complète :
| Solution | Mise en place |
|---|---|
| Repérer ponctuellement | Données › Validation des données › Entourer les données non valides |
| Alerter en permanence | Mise en forme conditionnelle sur C2:C50, formule =ET(C2<>"";ESTNA(EQUIV(C2;INDIRECT($B2);0))), remplissage rouge |
| Effacer automatiquement | Macro événementielle dans le module de la feuille (classeur .xlsm) |
Private Sub Worksheet_Change(ByVal Target As Range)
' Efface Formation et Niveau quand la catégorie change
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("B2:B50")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1).Resize(1, 2).ClearContents
Application.EnableEvents = True
End If
End SubUne seule table avec DECALER + EQUIV
Avec beaucoup de catégories, créer un nom par catégorie devient lourd. Alternative : une table unique à deux colonnes (Catégorie, Formation) triée par catégorie. EQUIV trouve la première ligne de la catégorie, NB.SI compte ses lignes, et DECALER découpe le bloc correspondant :
=DECALER(Listes!$B$1;EQUIV($B2;Listes!$A:$A;0)-1;0;NB.SI(Listes!$A:$A;$B2);1)
=FILTRE(Listes!B2:B20;Listes!A2:A20=B2) dans une cellule d’aide (H2) et utilisez =$H$2# comme source : pratique pour une fiche d’une ligne, pas pour une colonne entière.Exercice de synthèse : bon de commande de formation en cascade
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Listes : sélectionnez A1:C5, Formules › Créer à partir de la sélection › Ligne du haut. Nommez E2:E4 « Categories ».
- Feuille Niveaux : même opération sur A1:I5. Excel remplace automatiquement l’espace de « Power BI » par un trait bas : le nom créé est Power_BI.
- Feuille Commande : B2:B11 liste
=Categories, C2:C11=INDIRECT($B2), D2:D11=INDIRECT(SUBSTITUE($C2;" ";"_")). - Mise en forme conditionnelle rouge sur C2:C11 pour les formations incohérentes.
- Colonne F : même cascade avec la table unique de la feuille Table et DECALER + EQUIV. Comparez avec la feuille Commande (corrigé).