Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube 
Google Sheets : comment rendre des listes déroulantes interdépendantes avec des fonctions natives
Qu’est-ce qu’une liste déroulante ? Dans les feuilles de calcul, les listes déroulantes sont là pour nous permettre de sélectionner rapidement un élément prédéterminé d’une liste d’éléments saisis manuellement auparavant ou faisant référence à une […]
Ce que vous allez découvrir
- Qu'est-ce qu'une liste déroulante ?
- Première solution : simple à mettre en place mais applicable sur une ligne seulement
- Deuxième exemple : plus complexe mais applicable sur des colonnes entières
- Conclusion
Google Sheets : comment rendre des listes déroulantes interdépendantes avec des fonctions natives
Qu’est-ce qu’une liste déroulante ?
Dans les feuilles de calcul, les listes déroulantes sont là pour nous permettre de sélectionner rapidement un élément prédéterminé d’une liste d’éléments saisis manuellement auparavant ou faisant référence à une plage de données de la feuille de calcul Google Sheets.
Les avantage sont multiples :
1- Gain de temps : puisque nous n’avons pas à saisir manuellement les mêmes éléments encore et encore.
2- Efficacité : car la base de données ainsi générée ne sera jamais polluée par des éléments ne faisant pas partie de ce qu’il est possible de choisir et/ou mal saisis.
Vous partagez une feuille de calcul dans laquelle vos collaborateurs doivent choisir leur dessert pour le repas de Noël organisé pas le C.E entre :
- Une bûche de Noël
- Une poire Belle-Hélène
- Une coupe colonel
Attendez-vous à obtenir une résultat à peu près comme cela :
| Nom | Dessert choisi |
| Jean Bono | 1 poire belle helene |
| Pierre Kiroule | Buche de noel |
| Alain Deloin | une coupe colonnel |
| Marie Posa | une poire Belle-héléne |
| Léa Titude | Coupe Colonel |
| Brigitte Etlecouvert | buche de noël |
| Fabrice Denice | Bûche de Noel |
Ici, le tableau est très petit, mais imaginez le résultat pour l’organisation d’un repas de 200 collaborateurs !
Remarquez que dans cet exemple, il n’y a pas deux valeurs identiques pour désigner la même chose.
Maintenant, bon courage pour analyser tout cela grâce à un tableau croisé dynamique pour passer la commande auprès de votre traiteur ! Vous allez passer beaucoup de temps à nettoyer votre base de données avant de pouvoir en faire une synthèse.
L’utilisation des listes déroulantes dans ce cas vous sera très précieuse et vous fera gagner un temps considérable car vous n’aurez pas ce problème. Voici ici un rappel sur l’utilisation des validations des données pour créer une liste déroulante.
Regardons à présent comment réaliser des listes déroulantes interdépendantes où le choix de l’élément de la première liste conditionne les éléments de la deuxième liste.
Première solution : simple à mettre en place mais applicable sur une ligne seulement
Marion est propriétaire de plusieurs magasins répartis dans trois villes de France. Elle souhaite afficher les magasins en fonction de la ville sélectionnée dans la première colonne. La liste déroulante à afficher dans la deuxième colonne est donc dépendante du choix de la ville sélectionnée dans la première.
- Pour créer des listes dépendantes, elle doit d’abord lister les magasins qu’elle gère dans chaque ville dans un onglet qu’elle nomme DATA, comme montré dans les images suivantes.
- Ensuite, elle doit nommer les plages de données avec le nom de la ville correspondant à la liste des magasins associée. Pour cela, elle sélectionne chaque plage de magasins et la nomme avec le nom de la ville correspondante.
Remarque : il n’est pas possible de mettre des espaces dans le nom des plages nommées.
- Elle crée ensuite un nouvel onglet qu’elle appelle « Listes déroulantes » où elle pourra implémenter ces listes déroulantes. En en-tête, elle fera une colonne « Ville » en A1 et une colonne « Magasin » en B1.
- Puis, en A2 elle va mettre en place la première liste déroulante en faisant un clic droit : Données > Validation de données, sélectionnant une liste à partir d’une plage et en sélectionnant la plage A1:C1 de l’onglet DATA.
- La première liste déroulante des villes que Marion gère est alors disponible en A2 de l’onglet Listes Déroulantes.
- Elle revient ensuite sur l’onglet DATA et elle utilise la fonction INDIRECT en cellule E2 pour afficher la liste des villes en fonction des plages nommées réalisées précédemment. Le nom de la plage nommée sera affiché en cellule A2 de l’onglet Listes déroulantes. Ce sera donc soit Paris, Lyon ou Marseille.
Afin de mieux comprendre ce qui se passe ici faisons un petit détour sur la fonction INDIRECT.
INDIRECT permet d’utiliser la valeur d’une cellule comme référence.
Exemple en image :
- Indirect(C1) prendra la valeur “A1” en référence.
- Indirect(D1) prendra la valeur “B1” en référence.
- Dans ce cas =INDIRECT(C1)+INDIRECT(D1) revient donc à dire =A1+B1 soit =2+3=5
Les valeurs affichées dans cette liste correspondront donc au nom des villes comprises dans la plage nommée sélectionnée. La liste créée en cellule E2 de l’onglet DATA est donc dynamique et change en fonction du nom de la ville sélectionnée en cellule A2 de l’onglet Listes déroulantes.
- Il ne reste plus qu’à créer une liste déroulante en fonction de la plage E2:E de l’onglet DATA afin d’afficher la liste des magasins associée à chaque ville.
- Désormais, lorsque l’on choisit une ville dans la cellule A1 de l’onglet Listes déroulantes, la liste des magasins change également dans la cellule B2.
Deuxième exemple : plus complexe mais applicable sur des colonnes entières
Jeanne réalise des formations qui doivent être classées par thème.
Comment réaliser des listes déroulantes dépendantes applicables sur une colonne entière ?
Vous devez utiliser une formule avec la combinaison de quatre fonctions.
Explication de chacune des fonctions :
EQUIV
- « EQUIV » renvoie la position relative d’un élément qui correspond à une valeur spécifiée.
Cette fonction prend trois arguments :
- Le premier est la valeur recherchée. Ici “Voiture”
- Le deuxième est la plage où l’on cherche cette valeur. Ici B2:B7
- 0 signifie que l’on recherche une correspondance exacte
Dans cet exemple, Equiv affiche la valeur « 4 » car “Voiture” est en quatrième position dans la plage.
Attention : Equiv ne fonctionne que sur une plage correspondant à une seule colonne ou une seule ligne.
DECALER
- La fonction DECALER renvoie la valeur de la cellule qui se trouve à n lignes vers le bas et n colonnes vers la droite d’une cellule de référence.
Elle fonctionne également avec des valeurs négatives (-1 ligne : une ligne vers le haut ; -1 colonne : une colonne vers la gauche).
Question : quelle serait la valeur renvoyée pour =DECALER(C4;-1;-1) ?
Roulement de tambour… et oui vous l’avez deviné : « 2019 » ! puisque le résultat renvoyé sera la valeur de la cellule se trouvant une ligne au dessus et une colonne à gauche de la cellule de référence.
TRANSPOSE
- La fonction TRANSPOSE transpose les lignes en colonnes et les colonnes en lignes.
SIERREUR
- La fonction SIERREUR affiche un commentaire en cas d’erreur.
Revenons à notre exemple de Jeanne :
Les formations doivent être classées par thème : Art, Maths, Littérature, Sport
Étapes à suivre :
- Créez un nouvel onglet que l’on nommera Choix et nommez les en-têtes des colonnes. En A1 : « Matière » et B1 « Choix »
- Créez ensuite une liste déroulante sur la plage référençant les différents thèmes, soit ici A1:D1 de l’onglet Choix
- En plage de cellule vous renseignez A2:A afin que les listes déroulantes se déploient sur toute la colonne A “Matière”.
- En critère, il faut aller chercher les valeurs des différents thèmes se trouvant en première ligne de l’onglet Choix soit A1:D1. Pour cela, cliquez sur l’icône du tableau dans la case Critères de l’interface de validation des données.
- Revenez à l’onglet « Listes« . Vous allez créer une liste dynamique horizontale sur toute la colonne F en partant de la cellule F2.
Pour cela, vous allez utiliser une combinaison des fonctions vues précédemment :
« EQUIV » affiche l’emplacement de la valeur recherchée se trouvant dans une plage de données : EQUIV(Choix!A2;$1:$1;0)
La fonction recherche en ligne 1 de l’onglet Choix le thème affiché par la première liste déroulante et renvoie le numéro de colonne de la ligne 1 de l’onglet Choix. Donc pour “Art” cela sera égale à 1, “Math” sera égal à 2, “Littérature” à 3, etc.
La formule DECALER va vous aider à aller chercher les valeurs d’une plage en fonction d’une plage de référence, ici A2:A.
Pour rappel : DECALER (plage de référence; nombre de ligne de décalage; nombre de colonne de décalage).
- Plage de référence : vous prendrez comme plage de référence A1:A pour avoir toutes les valeurs de la colonne.
- Nombre de lignes de décalage : comme vous ne souhaitez pas décaler verticalement la zone, vous indiquerez 0 en décalage de ligne.
- Nombre de colonnes de décalage : vous utiliserez la valeur de la formule EQUIV pour indiquer de combien de colonnes vous souhaitez décaler votre plage.
Enfin, vous allez retrancher 1 pour retrouver la bonne colonne sans être décalé d’une colonne de trop.
Vous obtenez donc la formule suivante : DECALER(A$2:A;0;EQUIV(Choix!A2;$1:$1;0)-1)
La formule renvoie verticalement la liste des éléments de chaque thème en fonction du choix de la première liste déroulante de l’onglet Listes.
Vous souhaitez à présent afficher les listes horizontalement afin qu’elles aient de la place pour se déployer et qu’elles n’empiètent pas sur les lignes au dessus.
Pour cela, vous allez placer la formule obtenue précédemment dans la fonction TRANSPOSE :
=(TRANSPOSE(DECALER(A$2:A;0;EQUIV(Choix!A6;$1:$1;0)-1))
Enfin, utilisez SIERREUR, pour afficher un texte choisi au lieu de #N/A qui s’afficherait lorsque aucune valeur n’est choisie dans la première liste déroulante :
= SIERREUR (TRANSPOSE(DECALER(A$2:A;0;EQUIV(Choix!A2;$1:$1;0) -1));"Choisir une matière")
À ce stade, les listes déroulantes interdépendantes fonctionnent pour la première ligne.
Il ne vous reste plus qu’à la dupliquer sur toute la colonne F de l’onglet Choix pour que cela fonctionne pour toutes vos lignes de l’onglet Listes. Pour cela, sélectionnez la cellule contenant la formule et glissez-la vers le bas pour qu’elle soit appliquée à toutes les cellules.
Vous obtiendrez les résultats suivants :
Conclusion
Vous êtes à présent capable de créer une liste interdépendante sur une ligne grâce aux plages nommées et la fonction INDIRECT.
Si vous devez déployer les listes interdépendantes sur toute une colonne, il faudra faire appel à la combinaison EQUIV, DECALER, TRANSPOSE et SIERREUR. Un peu plus long à mettre en place, mais très efficace.
Dans l’article suivant, voyez comment mettre en place des listes interdépendantes en cascade sans limite de niveau à l’aide d’un script.
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets- Articles connexes
- Plus de l'auteur
5
Note du cours
(1)
(0)
(0)
(0)
(0)
Dommage que l’on ne puisse charger une feuille exemple !
Bonjour,
Je me suis ajouté « ARRAY_CONSTRAIN » en tête de formule dans le cas de 3 /4 cascades ca aide a rester sur une ligne.
Ca rajoute un nombre pas négligeable de colonne.
Bonjour,
Merci pour l’astuce c’est parfait.
De mon côté, j’ai voulu ensuite trier mon tableau (par date) et du coup la correspondance entre les onglets Choix et Liste n’est plus bonne.
Pour essayer d’être plus clair, ma cellule E6 se retrouve après tri en E10 mais ma liste déroulante secondaire va chercher la valeur correspond à E6 dans l’onglet Liste.
Est-ce qu’il y a une solution pour conserver les correspondances lors d’un tri de plage ?
Merci pour votre aide.
Bonne journée.
J’ai le même soucis, n’hésitez pas a me dire si vous avez trouvé une solution !
Salut Jean Claude, je demande à Jérémi de partager la feuille.
Bonjour Thierry,
Jeremi est d’accord?
Bonjour, je suis sur la dernière étape de la 2è solution
Je n’arrive pas à faire le lien entre les colonnes matières et choix (j’ai bien créé ma plage pour la validation)
Est-ce dynamique ou dois je créer une nouvelle validation pour chaque cellule
Merci
Bonjour,
Merci pour tous vos tutos sur google sheet, ça m’aide beaucoup pour ma conversion depuis excel – désolé pour le gros mot 😉 – j’apprécie la puissance de sheet et sa simplicité (si j’ose dire) dans plein de situation, MAIS…
sauf erreur de ma part, dans ce cas précis, la manip’ sur excel est beaucoup plus simple, je ne suis pas expert mais je pense que c’est dû à la gestion assez basique des plages nommées par Sheet.Avec l' »autre tableur », la première étape est la même: dans l’onglet « Choix » validation de données pour la colonne « matières » (A) proposant donc Art, Maths, Littérature ou Sport sur le nombre de lignes souhaitées. Ensuite, dans l’onglet « Listes », nommez les plages de « choix » avec le nom de la matière correspondante (exemple: la plage « Maths » comprend donc B2 et B3). Retournez dans l’onglet « Choix », choisissez « Maths » dans le menu déroulant de A2, sélectionnez la cellule B2, cliquez sur validation de données et tapez « =INDIRECT($A1) »dans la case « source » puis OK.
Le menu vous proposera « Algebre » ou « Geometrie ».
Copiez la cellule B2, sélectionnez toute la colonne B (sauf B1) ou le nombre de lignes souhaitées dans la colonne B et faites un collage spécial « validation ».C’est nettement plus rapide à faire qu’à décrire mais vraiment, sur tout ce qui est gestion des noms (plages nommées) excel a encore un bon coup d’avance sur Sheet, pourvu que ça ne dure pas.Merci de m’avoir lu.
EDIT:
oups, il faut taper =INDIRECT($A2)
Bonjour,
Pour la solution, lorsque j’insert une ligne (parce exemple entre la ligne 4 et la ligne 5), plus rien ne fonctionne.
J’ai un besoin dans mon cas, où sur un fichier partagé, on aura le réflexe d’insérer des lignes un peu partout…
Avez-vous une solution ?
Pour la solution 2 pardon.
Bonjour,
On peut remplacer le SIERREUR(TRANSPOSE(DECALER … ; EQUIV(…)) par
=TRANSPOSE(FILTER( ))
Plus simple à mon sens, même résultat garanti !