Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets : comment rendre des listes déroulantes interdépendantes avec des fonctions natives

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 […]

personnes ont consulté cet article

5 minutes

Rédigé par Jérémi PICCIONI - il y a 2 ans

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 :

NomDessert choisi
Jean Bono1 poire belle helene
Pierre KirouleBuche de noel
Alain Deloinune coupe colonnel
Marie Posaune poire Belle-héléne
Léa TitudeCoupe Colonel
Brigitte Etlecouvertbuche de noël
Fabrice DeniceBû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.

Liste dépendantes
  • 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.

Liste dépendantes - Google Sheets
Liste dépendantes - Google Sheets
Liste dépendantes - Google Sheets
  • 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.
Validation de données - Google Sheets
Validation des données - Google Sheets
  • 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 - Google Sheets
  • 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.

Indirect - Google Sheets
  • 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.
Validation de données - Google Sheets
  • 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.
Liste dépendantes - Google Sheets

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.
Fontion EQUIV - Google Sheets

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.
Fonction DECALER - Google Sheets

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.
Fonction TRANSPOSE - Google Sheets
SIERREUR
  • La fonction SIERREUR affiche un commentaire en cas d’erreur.
Fonctions SIERREUR - Google Sheets

Revenons à notre exemple de Jeanne :

Les formations doivent être classées par thème : Art, Maths, Littérature, Sport

Liste validation dépendante - Google Sheets

É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
  1. En plage de cellule vous renseignez A2:A afin que les listes déroulantes se déploient sur toute la colonne A “Matière”.
  2. 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.
Liste validation dépendante - Google Sheets
  • 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.

Liste validation dépendante - Google Sheets

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)

Liste validation dépendante - Google Sheets

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. 

Liste validation dépendante - Google Sheets

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.

Liste validation dépendante - Google Sheets

Vous obtiendrez les résultats suivants :

Liste validation dépendante - Google Sheets

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.

Rédacteur

S’abonner
Notification pour
guest
11 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Jean-Claude Friloux
Jean-Claude Friloux

Dommage que l’on ne puisse charger une feuille exemple !

Cet avis vous a été utile ?

Bonnissant
Bonnissant

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.

Cet avis vous a été utile ?

Carrez
Carrez

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.

Cet avis vous a été utile ?

Axel
Axel
Répondre à  Carrez
8 mois il y a

J’ai le même soucis, n’hésitez pas a me dire si vous avez trouvé une solution !

Cet avis vous a été utile ?

Salut Jean Claude, je demande à Jérémi de partager la feuille.

Cet avis vous a été utile ?

David
David
Répondre à  Thierry VANOFFE
4 mois il y a

Bonjour Thierry,
Jeremi est d’accord?

Cet avis vous a été utile ?

Virginie
Virginie

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

Cet avis vous a été utile ?

David
David

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.

Cet avis vous a été utile ?

David
David
Répondre à  David
4 mois il y a

EDIT:
oups, il faut taper =INDIRECT($A2)

Cet avis vous a été utile ?

Jérémy
Jérémy

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 ?

Cet avis vous a été utile ?

Jérémy
Jérémy
Répondre à  Jérémy
2 mois il y a

Pour la solution 2 pardon.

Cet avis vous a été utile ?