Il existe plusieurs situations où créer une liste déroulante dans Excel via la fonctionnalité de validation de données est intéressante, notamment, lorsqu’on souhaite qu’un usager choisisse une valeur parmi une liste de valeurs permises. Pour obtenir d’autres exemples de l’usage de la validation de données et pour savoir comment l’utiliser, vous pouvez relire l’article Astuce Excel: Validation de données (Data validation). L’article suivant se concentre sur les listes déroulantes créées à partir de la fonctionnalité de validation de données et indique comment procéder lorsque les données servant à créer la liste sont dans la même feuille que la liste elle-même, lorsqu’elles ne sont pas dans la même feuille, lorsqu’elles sont mises sous forme de tableau et lorsqu’elles ne le sont pas.
1 – Liste de validation de données dans la même feuille que les données sources
Cette section traite de la création de listes de données via la fonctionnalité de validation de données pour des listes dont les données sources appartiennent à la même feuille.
1.1 Les données sources ne sont pas mises sous forme de tableau
Les premiers exemples sont créés à partir de données sources qui ne sont pas mises sous forme de tableau. Pour savoir ce qui signifie mettre les données sous forme de tableau, vous pouvez relire l’article suivant: Découvrez la magie des tableaux Excel.
D’abord, vous pouvez créer votre liste en sélectionnant les cellules, B6 à B9, tel qu’illustré ci-dessous:
Le problème avec cette solution toutefois, c’est que lorsque vous devrez ajouter un produit supplémentaire dans vos données sources (supposons le Produit E), votre liste de validation n’en tiendra pas compte. Elle ne s’allongera pas en fonction des nouveaux items ajoutés à la source.
Une autre option est de créer un champ nommé pour les cellules B6 à B9 et de référer au champ nommé dans votre menu de validation de données.
Ci-dessous, vous pouvez constater que le champ nommé ProduitsSansTableau réfère aux cellules B6 à B9 et ce faisant, là aussi, si vous ajoutez le Produit E à vos données sources, celui-ci ne sera pas considéré dans votre liste déroulante.
Pour en savoir davantage sur les champs nommés, vous pouvez relire l’article Astuce Excel: Nommer une plage de cellules.
Finalement, vous pouvez utiliser une fonction Decaler/Offset pour créer une plage de données dynamique. Pour revoir comment utiliser la fonction Decaler/Offset afin de créer une plage de données dynamique, vous pouvez relire l’article suivant: Excel: Fonction Decaler (Offset).
Ce faisant, lorsque vous ajouterez le Produit E à votre source de données, celui-ci apparaîtra dans votre liste déroulante (voir image suivante):
1.2 Les données sources sont mises sous forme de tableau
L’exemple suivant a été créé à partir de données sources qui sont mises sous forme de tableau.
Si vos données sources sont dans la même feuille que votre liste de validation, lorsque vous créerez votre liste de validation, vous verrez des références aux cellules des items sources, dans notre exemple, une référence aux cellules B6 à B9. Toutefois, lorsque vous ajouterez un nouvel item dans vos données sources, la référence sera automatiquement modifiée pour aller chercher le nouvel item dans la cellule B10. Ainsi, votre liste déroulante sera toujours mise à jour correctement. Pour cette raison, il est largement préférable de créer vos données sources sous forme de tableau.
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
2 – Liste de validation de données dans une autre feuille que les données sources
Cette section traite de la création de listes de données via la fonctionnalité de validation de données pour des listes dont les données sources proviennent d’une autre feuille.
2.1 Les données sources ne sont pas mises sous forme de tableau
D’abord, vous pouvez créer votre liste en sélectionnant les cellules de la source de données mais comme c’était le cas lorsque les données sources résidaient dans la même feuille que la liste, celle-ci ne s’allongera pas lorsque de nouvelles données seront ajoutées à la source.
Ensuite, vous pouvez également référer à une plage de champ nommé mais vous rencontrez la même limite. La liste ne s’allongera pas en fonction des nouveaux items.
Pour voir la liste s’allonger en fonction des nouveaux items, vous devrez utiliser la fonctoin DECALER/OFFSET, comme c’était le cas lorsque les données non mises sous forme de tableau étaient dans la même feuille que la liste.
2.2 Les données sources sont mises sous forme de tableau
Si vos données sources sont mises sous forme de tableau et que vous créez une liste de validation dans un autre feuille, vous rencontrerez les mêmes limitations qu’en 2.1 si vous référez à une sélection de cellules, comme B6 à B9. Comme en 2.1 également, vous pourrez créer une fonction DECALER/OFFSET pour vous assurer que votre liste s’allongera en fonction des nouveaux items ajoutés dans vos données sources. Toutefois, il y a une façon beaucoup plus simple et directe à utiliser lorsque vos données sources sont mises sous forme de tableau et que votre liste est créée dans une autre feuille. Il s’agit de créer un champ nommé à partir des cellules B6 à B9 et de faire pointer la validation de données sur ce champ nommé, tel qu’illustré ci-bas:
Le champ nommé LesProduits réfère aux produits du tableau. Lorsqu’un nouveau produit est ajouté, le champ nommé l’inclut. C’est ce qui permet de le voir apparaître dans la liste de validation.
Une autre option est d’utiliser une fonction INDIRECT. Dans l’exemple ci-dessous, la fonction INDIRECT est utilisée sur la tableau de données Tableau3, sur la colonne de données Produits. Notez bien l’usage des guillemets et des crochets. Pour un rappel de l’usage de la fonction INDIRECT, vous pouvez relire l’article Excel: La fonction Indirect pour créer des sommaires exécutifs en un clic de souris.
Fichier d’accompagnement
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formations complémentaires
Si les validations de données et les champs nommés vous intéressent, jetez un coup d’oeil à notre formation Excel – Mise à niveau.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
Si la fonction DECALER/OFFSET vous intéresse, jetez un coup d’oeil à notre formation Excel – Modélisation financière (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi cette formation :
Bonjour,
Bravo comme toujoursd, Très bon tuto mais … une conjugaison qui semble être erronée.
“Si vos données sources sont dans la même feuille que votre liste de validation, lorsque vous créerez (créerai) votre liste de validation, vous verrez des références…”
Bien cordialement
G.D
Merci. Le tout a été corrigé. Au plaisir!
Je voulais savoir comment on peux changer une cellule de la forme: des chiffre et des lettre avec des tirets
XXX-0000X-00 et le mettre sans les tirets XXX0000X00 ?
Merci LL
Bonjour,
Pourriez-vous svp poser votre question dans notre forum, puisqu’elle n’est pas en lien avec cet article?
Merci,
Sophie
Bonjour Sophie, comme toujours une excellente source d’inspiration. Cependant, je crois qu’il y a une coquille dans le titre de votre point 2.2,qui traite du cas où les données sources sont sous forme de tableau. Bien cordialement.
Bonjour Jérôme,
Merci pour le commentaire. C’est corrigé!
Au plaisir,
Sophie
Bonjour Sophie
Bravo pour ta présentation.
J’ai utilisé la stratégie de validation des données, pour m’assurer que l’utilisateur de ma feuille (liste des membres d’une association) saisisse, par exemple les noms exactes d’une province, d’une ville, du secteur ou de l’arrondissement, de la rue, etc.
J’avais utilisé des champs nommés sur une seule et même feuille intitulé « Champs de validation ». Toutefois j’ai réalisé que si les champs nommés n’étaient pas placés en escalier cela créait des conflits entre les champs nommés, lors de l’insertion (ajout) de nom de ville ou autres noms.
Je crois que la stratégie de la création d’une feuille distincte pour chaque type de validation est plus simple, mais augmente de façon importante la taille du fichier Excel.
Est-il possible d’utiliser les données sources d’un autre classeur?
Bonjour Johanne,
Il est effectivement possible d’utiliser les données d’un autre classeur. Toutefois, les deux classeurs devront être ouverts afin de pouvoir utiliser les listes déroulantes. Sinon, il faut le faire via VBA ou Power Query, comme j’en fais la démonstrations d’en l’article suivant: https://www.lecfomasque.com/excel-creer-des-listes-deroulantes-dynamiques-et-sans-doublons/.
Au plaisir,
Sophie
Bonjour Sophie,
J’ai bien suivi votre lien. Je cherche à importer des listes depuis un autre fichier excel (fermé) grace à une macro mais ne trouve pas comment faire. J’ai bien pensé à créer une macro qui copie/colle la feuille du classeur contenant ces listes, puis recrée ces mêmes listes mais j’imagine qu’il doit y avoir plus simple. auriez-vous un cours/tuto/conseil?
Cordialement
Bonjour,
J’ai bien suivi votre lien. Je cherche à importer des listes depuis un autre fichier excel (fermé) via une macro mais ne trouve pas la solution. Aucun bout de code sur le net, et les fonction copier/coller ne marchent pas pour ce genre d’objet. Auriez vous une solution?
J’ai bien pensé importer seulement les données, puis que la macro recrée les listes mais c’est faire bien compliqué pour une chose “toute simple”.
Cordialement
Bonjour Vincent,
Le plus facile et le plus optimal est sans aucun doute via Power Query. J’ai un article qui crée des listes dans Excel à partir d’une source de données externe: https://www.lecfomasque.com/excel-creer-des-listes-deroulantes-dynamiques-et-sans-doublons/. Dans cet article, on doit créer les listes à partir de la base de données. Si vos listes sont déjà créées dans l’autre fichier Excel, c’est encore plus simple…
Au plaisir,
Sophie
Bonjour Sophie,
Malheuresement cet Add-in n’est pas disponible dans le menu file>option>add-ins>COM add-ins sur le poste sur lequel je travaille… (W10 et excel 2016 pourtant!) De plus comme le fichier sera par la suite utilisé par beaucoup de collègues il me semblait plus simple que ce soit une macro qui se lance pour importer ses listes, qu’ils n’aient besoin de rien d’autre que de cliquer sur un bouton.
Bien cordialement,
Vincent
Bonjour, Pour aller plus loin, si je reprends votre exemple, je souhaiterai proposer dans mon tableau une liste de choix selon un référentiel d’une autre page. Imaginons que mes produits soient associés à des références. Pour un produit j’ai plusieurs références. je dispose d’un coté mes produits et références d’un côté et mon listing de commandes de l’autre coté. je sais ce qui a été commandé ie quel produit a été mouvementé et j’ignore pour quelle référence. je souhaiterai donc que les références apparaissent dans ma liste déroulante mais selon le produit mouvementé de la ligne de commande. j’ai essayé d’insérer une formule (recherchev) dans la liste de choix… Merci pour votre aide
Bonjour,
Je ne suis pas certaine de comprendre votre question mais si je la comprends bien, vous cherchez à créer des listes déroulantes dépendantes.
Le cas échéant, vous pourriez utiliser la technique suivante: https://www.lecfomasque.com/excel-listes-deroulantes-dependantes/.
Vous pourriez aussi créer une première liste avec une validation de données et une deuxième liste (la liste dépendante) avec une validation de données comprenant une formule qui rapporte une plage de données (via une fonction index).
Si ce n’est pas ce que vous cherchez comme réponse ou si vous ne comprenez pas bien mes réponses, veuillez svp poser votre question dans notre forum et y joindre votre fichier afin que nous puissions y insérer les formules nécessaires.
Forum: https://www.lecfomasque.com/forum/.
Merci,
Sophie
Bonjour,
Dans mon fichier j’utilise une liste de validation des données qui est issue d’un tableau (ça me permet de mettre à jour la liste simplement sans formule).
Par contre mon fichier évolue et maintenant pour compliquer le tout mon j’ai besoin d’avoir des listes déroulantes indépendantes sans doublons et qui s’alimentent toutes seules en fonction de l’évolution de ma base de donnée. (je ne sais pas si je suis clair).
En gros dans ma base de donnée qui évolue et se modifie régulièrement j’ai:
A blanc
A vert
B blanc
B noir
C bleu
Je voudrais choisir dans une cellule le type “A” par exemple (une liste qui me propose A B ou C)
Puis dans une autre cellule la couleur (comme j’ai choisi A je dois avoir le choix entre blanc ou vert)
… et ainsi de suite pour à la fin ressortir le prix du type A de couleur blanc par exemple (prix qui est dans ma base de donnée bien sur).
Thomas
Bonjour,
J’ai publié un article par le passé qui répond à cette question. Le voici: https://www.lecfomasque.com/excel-creer-des-listes-deroulantes-dynamiques-et-sans-doublons/.
Au plaisir,
Sophie
Bonjour,
Peut-on créer une liste déroulante dynamique dont les valeurs proviennent des résultats d’un tableau croisé dynamique ?
Merci,
Eric
Bonjour,
Tout est possible… je ne comprendrais toutefois pas l’utilité ni la pertinence…
Pouvez-vous expliquer votre cas?
Merci.
Sophie
Bonjour,
Je suis dans ce cas.
J’ai un tableau de données regroupant des noms et des qualités associés aux noms (A, B, C …).
Mon tableau dynamique croisé permet de récupérer la liste des noms ayant la qualité B.
Cela me permet ensuite dans un autre tableau de gestion de charge de mettre un liste déroulante des noms récupérés dans la colonne “Nom métier B”.
Les formules avec la syntaxe tableau ne fonctionne pas. Je trouve dommage d’utiliser la syntaxe DECALER.
Merci pour vos tutos et d’avance pour votre réponse.
Eric
Bonjour,
Je vous déconseille fortement de créer une liste à partir des champs d’un tableau croisé dynamique. Ce serait aller à l’envers des bonnes pratiques dans Excel. Le tableau croisé dynamique est par définition, dynamique, et un usager peut en changer la forme et la contenu aisément, ce qui peut avoir un impact significatif sur vos listes créées à partir de ce tableau croisé dynamique. Le tableau croisé est généralement un outil de présentation ou une interface intermédiaire entre une base de données et un graphique.
Alors, je vous propose d’utiliser la technique décrite dans cet article ou alors, si vous utilisez une version Excel qui a déjà accès aux fonctions matricielles dynamiques, je vous conseille la fonction UNIQUE, à utiliser non pas sur votre tableau croisé dynamique mais plutôt sur la base de données qui a servi à alimenter votre tableau croisé dynamique.
Voici un lien vers une vidéo qui présente les fonctions matricielles dynamiques https://www.lecfomasque.com/excel-decouvrez-les-dernieres-nouveautes/.
Au plaisir,
Sophie
En complément voici une liste dynamique des cases, (contenu) qui peut s’associer avec la liste dynamique
https://www.youtube.com/watch?v=aIMCQoWbwEI
avec un fichier en exemple
http://91.121.171.213/link/Liste_validation_contenu_dynamique.xlsm
Bon courage
David
Bonjour,
Super Tuto surtout sur la partie tableau.
Je souhaitais savoir comment je pouvais activer la saisie automatique dans une liste déroulante sur la base d’un objet tableau. J’ai essayer avec Decaler et NBVAL et rien n’y fait, cela ne veut pas fonctionner.
Merci pour votre aide.
Patrice.
Bonjour Patrice,
Je ne suis pas certaine de comprendre votre question… Normalement, la saisie automatique va fonctionner seulement lorsque la valeur de la liste aura été inscrite au moins une fois dans une des lignes du tableau. Tant qu’elle n’aura pas été inscrite dans une des lignes du tableau, elle ne s’activera pas.
J’espère avoir bien compris votre question.
Sophie
Bonjour Sophie, Merci pour cette Présentation.
J’ai une question en Excel 2016, quand je fais une validation de données d’une cellule ex: B2, mais quand je fais copier coller une valeur non valide d’une autre cellule ex: D5 dans B2, la validation s’écrase.
Comment je pourrais sans utilisation de VBA soit :
1- Interdire à entrer des valeurs invalides même par copier/coller.
2- empêcher Copier/coller dans cette feuille.
Merci d’avance,
Bonjour,
dans votre exemple si je modifie le nom du produit E dans la colonne B alors que je pointe ce même produit dans la cellule ou se trouve la liste de valeur en colonne E, je souhaiterai qu’il soit modifié en même temps et que je ne sois pas obligé de le re sélectionner pour qu’il soit bien pointé.
En le renommant je perd le focus sur ce produit alors que je le pointais…
Avez vous une solution pour moi ?
En vous remerciant par avance.
Bonjour,
Si vous renommez le produit E, vous allez voir le nouveau nom de produit E dans la liste de validation automatiquement. Par contre, la valeur qui existe déjà dans une cellule ne se modifiera pas d’elle même… il s’agit d’une entrée de données. Vous pourriez minimalement faire un Chercher/Remplacer.
Au plaisir,
Sophie
Bonjour Sophie, merci pour ce super tuto. J’ai appliqué la solution dynamique avec formule =DECALER mais ma liste déroulante est vide. J’ai une feuille “Commandes” et une feuille “Clients”. Je souhaite que la liste affiche les données de la feuille “Clients” qui sont dans la colonne A. Ma formule est donc la suivante : =DECALER(Clients!$A$2;1;0;NBVAL(Clients!$A:$A)-1;1). Si j’intègre cette formule dans une cellule de la feuille “Commandes”, ça marche. Par contre dès que je mets cette formule dans Format de contrôle > Plage d’entrées, puis que je clique OK pour valider, ma liste est vide et si j’ouvre à nouveau Format de contrôle > Plage d’entrées, le champ est vide (n’a pas gardé la formule précédente). Pourriez-vous m’aider à résoudre ce problème ? D’avance merci beaucoup pour votre aide.
Bonjour,
Je vous conseillerais de poser votre question sur notre forum https://www.lecfomasque.com/forum/ et d’y insérer votre fichier pour qu’on puisse vous fournir la solution. Cela dit, cet article porte sur la création de liste de validation via le menu DONNÉES/VALIDATION DE DONNÉES et non via le menu DÉVELOPPEUR/CONTRÔLE DE FORMULAIRE.
Sophie
Bonjour,
En utilisant cette formule ci-dessous afin que ma liste déroulante soit dynamique, les résultats de la liste sont vides.
=DECALER(Menu!$C$2;0;0;NBVAL(Menu!$C:$C);1)
Lorsque je clique sur la formule pour qu’elle m’indique la plage sélectionnée, les bonnes données sont bien encadrées et valides.
Savez-vous pourquoi le résultat retourné est vide?
Merci et bonne journée
Bonjour,
Difficile de vous répondre sans voir votre fichier. Vous pourriez utiliser les techniques présentées dans cet article ou partager votre fichier sur un de nos forums https://www.lecfomasque.com/forums.
Au plaisir,
Sophie
Bonjour,
Votre tuto m’a appris que l’un pouvait mettre une formule dans la liste de choix. Cela ouvre des horizons… Une question sur la validation des données avec une liste: est il possible que cette liste ne soit qu’un support pour le choix mais que l’on puisse entrer manuellement un élément n’étant pas dans la liste ?
Merci.
JM
Bonjour,
Oui c’est possible.
Dans la fenêtre Validation des données, vous avez trois onglets: Options, Message de saisie et Alerte d’erreur.
Si vous allez dans Alerte d’erreur, vous pouvez sélectionnez dans le menu déroulant Style l’option Avertissement, qui vous permet d’indiquer un message lorsque l’information saisie ne fait pas partie des choix de la liste, mais la personne pourra choisir d’entrer tout de même l’information.
Au plaisir,
Kim
Merci pour vos explications précieuses.
J’utilisais F3 dans l’utilisation de validation de données pour avoir une liste des tableaux disponibles pour la source.
À mon travail, nous utilisons Office 365, version la plus récente.
Je n’ai plus accès à ma liste de tableaux avec le raccourci F3. Est-ce qu’il y a un changement à faire ?
Merci,
Bonjour, avec la touche F3, on accède à la liste de champs nommés, mais non aux tableaux effectivement.
S’il y a une autre touche de raccourci qui permet d’accéder aux tableaux, je ne la connais pas. Vous pouvez peut-être poser votre question sur notre forum. Un membre de notre communauté pourra peut-être aider.
https://www.lecfomasque.com/forums/
Au plaisir,
Kim